$filePath = "C:\edbmigration\nagold_bp\mtk_nagold_bp_ddl.sql"

Copy-Item -Path "C:\edbmigration\nagold_bp\mtk_nagold_bp_ddl_og.sql" -Destination $filePath -Force

$re_comments = '^\s*(.+<\!)?--'
$re_create_table = '^\s*CREATE TABLE'
$re_create_view = '^\s*CREATE OR REPLACE'

$re_match_TEXT = '\bTEXT\b'
$re_match_TEXT_comment = '-- Text'
$re_match_PIN = '\bPIN\b([^"]|$)'
$re_match_assign = ':='

$re_CUST_QRY_DATA = '\bCUST_QRY_DATA\b'
$re_LETTERS_DATA = '\bLETTERS_DATA\b'
$re_LABELS_DATA = '\bLABELS_DATA\b'
#$re_1 = '\bSECRET\b'
$re_2 = '\bPRIMARYDI\b'
$re_3 = '\bBRANDNAME\b'
$re_4 = '\bVERSIONMODELNUMBER\b'
$re_5 = '\bCOMPANYNAME\b'
$re_6 = '\bLABELEDCONTAINSNRL\b'
$re_7 = '\bLABELEDNONRL\b'
$re_8 = '\bMRISAFETYSTATUS\b'
$re_9 = '\bGMDNPTNAME\b'
$re_11 = '\bPARAMETERS\b'
$re_12 = '\bDATADICTIONARY\b'
$re_13 = '"body" '
$re_14 = 'RESPONSE CLOB'
$re_15 = 'URL CLOB'
$re_16 = '\bSELECTPTQUERY\b'
$re_17 = '\bBUFFER\b'
$re_18 = '([^''"])\bEDI\b'
$re_19 = '\bFILECONTENT\b'
$re_20 = '\bRTFDESCRIPTION\b'
$re_22 = '([^"])\bUID\b'
$re_CQM = '\bCQM\b'
$re_CQMCHAR = 'CQM CHAR'
$re_CQMKEY = 'KEY \(CQM\)'
$re_CQMSEL = 'SELECT CQM'
$re_CQMWHERE = 'WHERE CQM IN'
$re_CQMCOMMENT = 'CQMVALSET\.CQM'
$re_24 = '\bNQF\b'
$re_25 = 'LANGCD CHAR'
$re_26 = '\(LANGCD\)'
$re_LANGCDCOMMENT = 'LANGCD IS'
$re_27 = '\bCOLOR\b'
$re_28 = '\bATTACH\b'
$re_29 = '\bPRINTCONTENT\b'
$re_30 = '([^"=])\bNPI\b([^"]|$)'
$re_31 = '\bERRORID\b'
$re_32 = '\bAPPLICATION\b'
$re_33 = '\bHOST\b'
$re_34 = '"type"'
$re_35 = '\bSOURCE\b'
$re_36 = 'MESSAGE NVARCHAR2'
$re_37 = 'ELMAH\$ERROR\.MESSAGE'
$re_38 = 'USERNAME NVARCHAR2'
$re_39 = 'ELMAH\$ERROR\.USERNAME'
$re_40 = '\bSTATUSCODE\b'
$re_41 = '\bTIMEUTC\b'
$re_42 = '\bSEQUENCENUMBER\b'
$re_43 = '\bALLXML\b'
$re_44 = '\bIMAGE\b'
$re_45 = 'EHR VARCHAR2'
$re_EHRCOMMENT = 'EHR IS'
$re_46 = '\bTIN\b'
$re_47 = '\bIAGREEDID\b'
$re_IUSER = 'IUSER IS'
$re_48 = 'IUSER NUMBER'
$re_49 = '\bDATEAGREED\b'
$re_50 = '\bTIMEAGREED\b'
$re_51 = '\bCIF\b'
$re_52 = '(\s)ID CHAR'
$re_ID = '\.ID IS'
$re_53 = 'RECORDID CLOB'
$re_54 = '\bRECORDCONTENT\b'
$re_55 = '\bTABLENAME\b'
$re_56 = '\bOLDID\b'
$re_57 = '\bNEWID\b'
$re_58 = '\bRTFTEXT\b'
$re_59 = '([^_"])\bPAFILESID\b'
$re_60 = '\bFILEBLOB\b'
$re_61 = '\bFILEMIME\b'
$re_62 = '\bCREATEDATE\b'
$re_63 = '\bFILENAME\b'
$re_64 = '\bDIMENSION\b'
$re_65 = '\bPANAVID\b([^"]|$)'
$re_66 = '\bPANAVNAME\b'
$re_67 = '\bPANAVACTIVE\b'
$re_68 = '\bPANAVAUTH\b'
$re_69 = '\bPANAVPRIORITY\b'
$re_70 = '\bPAPARENTID\b'
$re_71 = '\bPANAVHIDERES\b'
$re_72 = '\bPANAVFIRSTACCESS\b'
$re_73 = '\bPANAVCONTENTID\b([^"]|$)'
$re_74 = '\bPANAVCONTENTNAME\b'
$re_75 = '\bPANAVCONTENTTYPE\b'
$re_76 = 'PANAVCONTENT,'
$re_77 = 'PANAVCONTENT CLOB'
$re_78 = '\bPATEMPLATEID\b([^"]|$)'
$re_79 = '\bPATEMPLATEHTML\b'
$re_80 = '\bPATEMPLATECSS\b'
$re_81 = '\bPATEMPLATEJS\b'
$re_82 = '\bPATEMPLATENAME\b'
$re_83 = '\bPATEMPLATELOGICCSS\b'
$re_84 = 'NB NUMBER'
$re_85 = '^(\s*)PICTURE'
$re_86 = '\bANSTEXT\b'
$re_87 = '\bONEDAY\b'
$re_88 = 'CDA CHAR'
$re_CDAIS = 'CDA IS'
$re_89 = '\bCDA\b,'
$re_90 = '\bSIGNATURE\b'
$re_91 = '\bNDCID\b([^"]|$)'
$re_92 = '\bEKGDATA\b'
$re_93 = '\bRECONCILCONTENT\b'
$re_94 = '(\s)BMI NUMBER'
$re_BMIIS = '\.BMI IS'
$re_95 = 'NOTE CLOB'
$re_96 = 'DATA BLOB'
$re_97 = '\bSNAPID\b'
$re_98 = '\bRXCUI\b'
$re_99 = '\bLAT\b'
$re_100 = '\bTS\b VARCHAR2'
$re_101 = '\bLUI\b'
$re_102 = '\bSTT\b'
$re_103 = '\bSUI\b'
$re_104 = '\bISPREF\b'
$re_105 = '\bRXAUI\b'
$re_106 = '\bSAUI\b'
$re_107 = '\bSCUI\b'
$re_108 = '\bSDUI\b'
$re_109 = '\bSAB\b'
$re_110 = '\bTTY\b'
$re_111 = '(\s)CODE VARCHAR2'
$re_112 = '\bSTR\b'
$re_113 = '\bSRL\b'
$re_114 = '\bSUPPRESS\b'
$re_115 = '\bCVF\b'
$re_116 = '\bNDC\b'
$re_117 = '\bREL\b'
$re_118 = '\bRELA\b'
$re_119 = '\bRUI\b'
$re_120 = '\bSRUI\b'
$re_121 = 'SL VARCHAR2'
$re_122 = '\bDIR\b'
$re_123 = '\bRG\b'
$re_124 = '\bSTYPE\b'
$re_125 = '\bATUI\b'
$re_126 = '\bSATUI\b'
$re_127 = '\bATN\b'
$re_128 = '\bATV\b'
$re_129 = '\bRIGHTS\b'
$re_130 = '\bINSTRUCTION\b'
$re_131 = 'PA VARCHAR2'
$re_PAIS = '\.PA IS'
$re_132 = '\bFMX\b'
$re_elmaherror = '(elmah\$error)\.(\w+)(%TYPE)'
$re_ALLXML = 'allxml'
$re_match_LOINC = '\bLOINC\b'
$re_match_LOINC_tab = 'LOINC L|LOINC%TYPE|\.LOINC\.'
$re_match_LOINC_col = '([^''"=])\bLOINC\b'
$re_match_LOINC_COMMENT = '\.LOINC IS'
#$re_match_LOINC_ind = 'CREATE UNIQUE INDEX LOINC_PRIMARY ON AUTONUNIT20252\.LOINC \(LOINC\)'
$re_match_LOINC_type = 'LOINC\.LOINC%TYPE'
$re_match_NPI2 = '\bNPI2\b([^"]|$)'
$re_match_ECO2 = '\bECO2\b([^"]|$)'
$re_match_ISO639_1 = '\bISO639_1\b'
$re_match_ISO639_2B = '\bISO639_2B\b'
$re_match_GLOBAL_TEMP = 'CREATE GLOBAL TEMPORARY'

$sb = [System.Text.StringBuilder]::new()

[void]$sb.AppendLine("set client_encoding = 'UTF8';")

foreach ($line in [System.IO.File]::ReadLines($filePath)) {
    $newLine = if($line -cmatch $re_elmaherror) {
		if ($line -cnotmatch $re_ALLXML) {
[regex]::Replace($line, $re_elmaherror, { '{0}."{1}"{2}' -f $args[0].Groups[1].Value.ToUpper(), $args[0].Groups[2].Value.ToUpper(), $args[0].Groups[3] })
		}
		else {
$line
		}
    } elseif($line -cmatch $re_match_GLOBAL_TEMP) { 
$line -creplace $re_match_GLOBAL_TEMP, 'CREATE TEMPORARY'
    } elseif($line -cmatch $re_CQM) {
$line 	-creplace $re_CQMCHAR, '"CQM" CHAR' `
		-creplace $re_CQMKEY, 'KEY ("CQM")' `
		-creplace $re_CQMSEL, 'SELECT "CQM"' `
		-creplace $re_CQMCOMMENT, 'CQMVALSET."CQM"' `
		-creplace $re_CQMWHERE, 'WHERE "CQM" IN'
	}
    elseif ($line -match $re_comments -or $line -cmatch $re_create_table -or $line -cmatch $re_create_view) {
$line
    }
	elseif ($line -cmatch $re_match_LOINC) {
		if ($line -notmatch $re_match_LOINC_tab) {
$line -creplace $re_match_LOINC_col, '$1"LOINC"'
		}
		else {
#$line  -creplace $re_match_LOINC_ind, 'CREATE UNIQUE INDEX LOINC_PRIMARY ON AUTONUNIT20252.LOINC ("LOINC")' `
$line	-creplace $re_match_LOINC_type, 'LOINC."LOINC"%TYPE' `
	  -creplace $re_match_LOINC_COMMENT, '."LOINC" IS'
		}
	}
	elseif ($line -cmatch $re_match_PIN) {
		if ($line -notmatch $re_match_assign) {
$line -creplace $re_match_PIN, '"PIN"$1'
		}
		else {
$line
		}
	}
	else {
$line   -creplace $re_2, '"PRIMARYDI"' `
        -creplace $re_3, '"BRANDNAME"' `
        -creplace $re_4, '"VERSIONMODELNUMBER"' `
        -creplace $re_5, '"COMPANYNAME"' `
        -creplace $re_6, '"LABELEDCONTAINSNRL"' `
        -creplace $re_7, '"LABELEDNONRL"' `
        -creplace $re_8, '"MRISAFETYSTATUS"' `
        -creplace $re_9, '"GMDNPTNAME"' `
        -creplace $re_18, '$1"EDI"' `
        -creplace $re_22, '$1"UID"' `
        -creplace $re_24, '"NQF"' `
        -creplace $re_25, '"LANGCD" CHAR' `
        -creplace $re_26, '("LANGCD")' `
        -creplace $re_LANGCDCOMMENT, '"LANGCD" IS' `
        -creplace $re_30, '$1"NPI"$2' `
        -creplace $re_31, '"ERRORID"' `
        -creplace $re_32, '"APPLICATION"' `
        -creplace $re_33, '"HOST"' `
        -creplace $re_34, '"TYPE"' `
        -creplace $re_35, '"SOURCE"' `
        -creplace $re_36, '"MESSAGE" NVARCHAR2' `
        -creplace $re_37, 'ELMAH$ERROR."MESSAGE"' `
        -creplace $re_38, '"USERNAME" NVARCHAR2' `
        -creplace $re_39, 'ELMAH$ERROR."USERNAME"' `
        -creplace $re_40, '"STATUSCODE"' `
        -creplace $re_41, '"TIMEUTC"' `
        -creplace $re_42, '"SEQUENCENUMBER"' `
        -creplace $re_45, '"EHR" VARCHAR2' `
        -creplace $re_EHRCOMMENT, '"EHR" IS' `
        -creplace $re_46, '"TIN"' `
        -creplace $re_47, '"IAGREEDID"' `
		-creplace $re_IUSER, '"IUSER" IS' `
        -creplace $re_48, '"IUSER" NUMBER' `
        -creplace $re_49, '"DATEAGREED"' `
        -creplace $re_50, '"TIMEAGREED"' `
        -creplace $re_51, '"CIF"' `
        -creplace $re_52, '$1"ID" CHAR' `
		-creplace $re_ID, '."ID" IS' `
        -creplace $re_55, '"TABLENAME"' `
        -creplace $re_56, '"OLDID"' `
        -creplace $re_57, '"NEWID"' `
        -creplace $re_59, '$1"PAFILESID"' `
        -creplace $re_61, '"FILEMIME"' `
        -creplace $re_62, '"CREATEDATE"' `
        -creplace $re_63, '"FILENAME"' `
        -creplace $re_64, '"DIMENSION"' `
        -creplace $re_65, '"PANAVID"$1' `
        -creplace $re_66, '"PANAVNAME"' `
        -creplace $re_67, '"PANAVACTIVE"' `
        -creplace $re_68, '"PANAVAUTH"' `
        -creplace $re_69, '"PANAVPRIORITY"' `
        -creplace $re_70, '"PAPARENTID"' `
        -creplace $re_71, '"PANAVHIDERES"' `
        -creplace $re_72, '"PANAVFIRSTACCESS"' `
        -creplace $re_73, '"PANAVCONTENTID"$1' `
        -creplace $re_74, '"PANAVCONTENTNAME"' `
        -creplace $re_75, '"PANAVCONTENTTYPE"' `
        -creplace $re_78, '"PATEMPLATEID"$1' `
        -creplace $re_82, '"PATEMPLATENAME"' `
        -creplace $re_84, '"NB" NUMBER' `
        -creplace $re_88, '"CDA" CHAR' `
        -creplace $re_89, '"CDA",' `
        -creplace $re_CDAIS, '"CDA" IS' `
        -creplace $re_91, '"NDCID"$1' `
        -creplace $re_94, '$1"BMI" NUMBER' `
        -creplace $re_BMIIS, '."BMI" IS' `
        -creplace $re_97, '"SNAPID"' `
        -creplace $re_98, '"RXCUI"' `
        -creplace $re_99, '"LAT"' `
        -creplace $re_100, '"TS" VARCHAR2' `
        -creplace $re_101, '"LUI"' `
        -creplace $re_102, '"STT"' `
        -creplace $re_103, '"SUI"' `
        -creplace $re_104, '"ISPREF"' `
        -creplace $re_105, '"RXAUI"' `
        -creplace $re_106, '"SAUI"' `
        -creplace $re_107, '"SCUI"' `
        -creplace $re_108, '"SDUI"' `
        -creplace $re_109, '"SAB"' `
        -creplace $re_110, '"TTY"' `
        -creplace $re_111, '$1"CODE" VARCHAR2' `
        -creplace $re_112, '"STR"' `
        -creplace $re_113, '"SRL"' `
        -creplace $re_114, '"SUPPRESS"' `
        -creplace $re_115, '"CVF"' `
        -creplace $re_116, '"NDC"' `
        -creplace $re_117, '"REL"' `
        -creplace $re_118, '"RELA"' `
        -creplace $re_119, '"RUI"' `
        -creplace $re_120, '"SRUI"' `
        -creplace $re_121, '"SL" VARCHAR2' `
        -creplace $re_122, '"DIR"' `
        -creplace $re_123, '"RG"' `
        -creplace $re_124, '"STYPE"' `
        -creplace $re_125, '"ATUI"' `
        -creplace $re_126, '"SATUI"' `
        -creplace $re_127, '"ATN"' `
        -creplace $re_128, '"ATV"' `
        -creplace $re_131, '"PA" VARCHAR2' `
        -creplace $re_PAIS, '."PA" IS' `
        -creplace $re_match_NPI2, '"NPI2"$1' `
        -creplace $re_match_ECO2, '"ECO2"$1' `
        -creplace $re_match_ISO639_1, '"ISO639_1"' `
        -creplace $re_match_ISO639_2B, '"ISO639_2B"' `
        -creplace $re_132, '"FMX"'
    }
    [void]$sb.AppendLine($newLine)
}
[System.IO.File]::WriteAllText($filePath, $sb.ToString())

