$filePath = "C:\edbmigration\_gen_autodevtmp1_ddls_250721085703.sql"

$re_DBMS_LOB = 'DBMS_LOB\.createtemporary'
$re_TODATE_SYSDATE = 'DEFAULT TO_DATE\(sysdate\)'
$re_PATIENT_TYPE = 'pPatient IN INTEGER'
$re_plsqlunit = '\$\$PLSQL_UNIT'
$re_DUP_UNIT_NAME = '^(( *CREATE ((\w+) )+)"(\w+)"\."(\w+)" (?!is|as|after))\w+$'
$re_GET_GOLIVE_DATE = '(PE_CUSTOM_PKG.GET_GOLIVE_DATE)([^(]|$)'
$re_DEPT_AR_sig =  ' \(DEPT_AR_Cursor IN OUT Department IN CHAR,'
$re_vsession = 'v\$session'
$re_vinstance = 'v\$instance'
# not supported
$re_remainder = '\bremainder\b\('
#reserved word
$re_offset = '\boffset\b'
$re_utl_i18_1 = "rawDataIn := UTL_I18N\.STRING_TO_RAW \(cData, 'WE8ISO8859P1'\);"
$re_utl_i18_2 = "rawDataKey :=\s+UTL_I18N\.STRING_TO_RAW \(cSecretKey, 'WE8ISO8859P1'\);"
$re_utl_i18_3 = "cResult := UTL_I18N\.RAW_TO_CHAR \(rawDataOut2, 'WE8ISO8859P1'\);"

$utf8NoBom = New-Object System.Text.UTF8Encoding $false
$content = [System.IO.File]::ReadAllText($filePath, [System.Text.Encoding]::GetEncoding(1252))
[System.IO.File]::WriteAllText($filePath, $content, $utf8NoBom)

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

foreach ($line in [System.IO.File]::ReadLines($filePath)) {
    $newLine = if ($line -match $re_TODATE_SYSDATE) {
$line -replace $re_TODATE_SYSDATE, 'DEFAULT CURRENT_DATE'
	} elseif ($line -match $re_PATIENT_TYPE) {
$line -replace $re_PATIENT_TYPE, 'pPatient IN NUMBER(10,0)'
	} elseif ($line -match $re_plsqlunit) {
$line -replace $re_plsqlunit, "'`$`$`$PLSQL_UNIT'"
	} elseif ($line -match $re_DUP_UNIT_NAME) {
$line -replace $re_DUP_UNIT_NAME, '$1'
	} elseif ($line -match $re_GET_GOLIVE_DATE) {
$line -replace $re_GET_GOLIVE_DATE, '$1()$2'
	} elseif ($line -match $re_DBMS_LOB) {
$line -replace $re_DBMS_LOB, 'DBMS_LOB_S.createtemporary'
	} elseif ($line -match $re_vinstance) {
$line -replace $re_vinstance, 'V_INSTANCE'
	} elseif ($line -match $re_vsession) {
$line -replace $re_vsession, 'V_SESSION'
	} elseif ($line -match $re_remainder) {
$line -replace $re_remainder, 'mod('
	} elseif ($line -match $re_offset) {
$line -replace $re_offset, 'n_offset'
	} elseif ($line -match $re_utl_i18_1) {
$line -replace $re_utl_i18_1, 'rawDataIn := ULT_RAW.CAST_TO_RAW (cData);'
	} elseif ($line -match $re_utl_i18_2) {
$line -replace $re_utl_i18_2, 'rawDataKey := ULT_RAW.CAST_TO_RAW (cSecretKey);'
	} elseif ($line -match $re_utl_i18_3) {
$line -replace $re_utl_i18_3, 'cResult := ULT_RAW.CAST_TO_VARCHAR2 (rawDataOut2);'
	} elseif ($line -match $re_DEPT_AR_sig) {
$line -replace $re_DEPT_AR_sig, ' (DEPT_AR_Cursor IN OUT DEPT_PKG.DEPT_AR_Type,  Department IN CHAR,'
	}
	else {
$line
	}
    [void]$sb.AppendLine($newLine)
}
[System.IO.File]::WriteAllText($filePath, $sb.ToString())

