# Generate S1-S4 FULL UAT MySQL write script from dopdemorq extract.
# Usage: .\Generate-S1S4FullUatSql.ps1 -SqlServerPassword '<sa-password>'
param(
    [string]$SqlServer = '123.60.180.165',
    [string]$SqlUser = 'sa',
    [string]$SqlPassword,
    [string]$SqlDatabase = 'dopdemorq',
    [string]$BillListFile = "$PSScriptRoot\S1S4_FULL_bill_list.txt",
    [string]$OutSql = "$PSScriptRoot\..\S1S4_UAT_FULL_execute_write.sql",
    [string]$OutTsv = "$PSScriptRoot\..\_extract_full.tsv",
    [long]$IdBase = 9106000500000001,
    [string]$BatchNo = 'S1S4_UAT_FULL_20260605_V1'
)

$ErrorActionPreference = 'Stop'
if (-not $SqlPassword) { throw 'SqlPassword is required (do not commit to git).' }

function Escape-Sql([string]$s) {
    if ($null -eq $s) { return '' }
    return ($s -replace "'", "''")
}

$bills = Get-Content $BillListFile | Where-Object { $_.Trim() -ne '' } | ForEach-Object { $_.Trim() }
$billValues = ($bills | ForEach-Object { "(N'$(Escape-Sql $_)')" }) -join ",`n"

$template = Get-Content "$PSScriptRoot\extract_full_from_dopdemorq.sql" -Raw
$extractSql = $template -replace "INSERT INTO @bills\(bill_no\) VALUES \(N'__PLACEHOLDER__'\);", "INSERT INTO @bills(bill_no) VALUES $billValues;"
$extractFile = Join-Path $env:TEMP "s1s4_full_extract.sql"
Set-Content -Path $extractFile -Value $extractSql -Encoding UTF8

$sqlcmd = 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE'
& $sqlcmd -S $SqlServer -U $SqlUser -P $SqlPassword -d $SqlDatabase -i $extractFile -W -h-1 -s"|" -o $OutTsv
if ($LASTEXITCODE -ne 0) { throw "sqlcmd failed with exit $LASTEXITCODE" }

$soRows = @(); $lineRows = @(); $woRows = @()
Get-Content $OutTsv | ForEach-Object {
    $line = $_.Trim()
    if ($line -eq '' -or $line -match '^-+$' -or $line -match 'rows affected') { return }
    $p = $line -split '\|'
    if ($p.Count -lt 2) { return }
    switch ($p[0]) {
        'SO' { $soRows += [pscustomobject]@{ bill_no=$p[1]; src_id=$p[2]; custom_no=$p[3]; custom_name=$p[4]; order_date=$p[5]; urgent=[int]$p[6] } }
        'LINE' { $lineRows += [pscustomobject]@{ bill_no=$p[1]; src_entry=$p[2]; entry_seq=[int]$p[3]; item_number=$p[4]; qty=$p[5]; plan_date=$p[6] } }
        'WO' { $woRows += [pscustomobject]@{ bill_no=$p[1]; src_entry=$p[2]; morder_no=$p[3]; product_code=$p[4]; need_number=$p[5] } }
    }
}

$soByBill = @{}
$idx = 0
foreach ($so in ($soRows | Sort-Object bill_no)) {
    if ($soByBill.ContainsKey($so.bill_no)) { continue }
    $idx++
    $soByBill[$so.bill_no] = [pscustomobject]@{
        bill_no = $so.bill_no
        target_id = $IdBase + $idx
        custom_no = if ($so.custom_no) { $so.custom_no } else { 'CUST0005' }
        custom_name = if ($so.custom_name) { $so.custom_name } else { 'WKQXGFYXGE' }
        order_date = if ($so.order_date -and $so.order_date -ne 'NULL') { $so.order_date } else { '2025-06-01 00:00:00.000' }
        delivery_date = if ($so.order_date -and $so.order_date -ne 'NULL') { $so.order_date } else { '2025-06-15 00:00:00.000' }
        urgent = $so.urgent
        po_seq = $idx
    }
}

$missing = $bills | Where-Object { -not $soByBill.ContainsKey($_) }
Write-Host "Extracted orders: $($soByBill.Count) / $($bills.Count)"
if ($missing.Count -gt 0) { Write-Host "Missing in dopdemorq: $($missing -join ', ')" }

$lineIdx = 0; $moIdx = 0; $linesOut = @(); $mosOut = @(); $prsOut = @(); $poLinesOut = @()
$woByEntry = @{}
foreach ($w in $woRows) { $woByEntry[$w.src_entry] = $w }

foreach ($bill in ($soByBill.Keys | Sort-Object)) {
    $so = $soByBill[$bill]
    $lines = $lineRows | Where-Object { $_.bill_no -eq $bill } | Sort-Object entry_seq, src_entry
    $poNo = ('PO-UAT-FULL-20260605-{0:D3}' -f $so.po_seq)
    $prSeq = 0
    $poLineNo = 0
    foreach ($ln in $lines) {
        $lineIdx++
        $targetLine = $IdBase + 10000 + $lineIdx
        $planDt = if ($ln.plan_date -and $ln.plan_date -ne 'NULL') { $ln.plan_date } else { $so.order_date }
        $linesOut += "($targetLine,'$(Escape-Sql $ln.src_entry)','$($so.bill_no)',$($ln.entry_seq),'$(Escape-Sql $ln.item_number)',$($ln.qty),'$planDt')"
        $wo = $woByEntry[$ln.src_entry]
        if ($wo) {
            $moIdx++
            $targetMo = $IdBase + 20000 + $moIdx
            $targetMe = $IdBase + 40000 + $moIdx
            $mosOut += "($targetMo,$targetMe,'$(Escape-Sql $ln.src_entry)','$($so.bill_no)','$(Escape-Sql $wo.morder_no)','$(Escape-Sql $wo.product_code)',$($wo.need_number))"
            $prSeq++
            $prBill = 'PR-UAT-FULL-20260605-{0:D3}' -f $prSeq
            $prsOut += "($($IdBase + 50000 + $moIdx),'$prBill','$(Escape-Sql $wo.morder_no)','$($so.bill_no)','$(Escape-Sql $wo.product_code)',$($wo.need_number),$targetMo)"
            $poLineNo++
            $poLinesOut += "('$poNo',$poLineNo,'$($so.bill_no)','$(Escape-Sql $wo.morder_no)','$(Escape-Sql $ln.item_number)',$($ln.qty))"
        }
    }
    if ($poLineNo -eq 0 -and $lines.Count -gt 0) {
        $ln = $lines[0]
        $moIdx++
        $synMo = "UAT-MO-$($so.po_seq.ToString('D4'))"
        $targetMo = $IdBase + 20000 + $moIdx
        $targetMe = $IdBase + 40000 + $moIdx
        $mosOut += "($targetMo,$targetMe,'$(Escape-Sql $ln.src_entry)','$($so.bill_no)','$synMo','$(Escape-Sql $ln.item_number)',$($ln.qty))"
        $prBill = 'PR-UAT-FULL-20260605-001'
        $prsOut += "($($IdBase + 50000 + $moIdx),'$prBill','$synMo','$($so.bill_no)','$(Escape-Sql $ln.item_number)',$($ln.qty),$targetMo)"
        $poLinesOut += "('$poNo',1,'$($so.bill_no)','$synMo','$(Escape-Sql $ln.item_number)',$($ln.qty))"
    }
}

$soIns = ($soByBill.Values | Sort-Object target_id | ForEach-Object {
    "('$($_.bill_no)',$($_.target_id),'$(Escape-Sql $_.custom_no)','$(Escape-Sql $_.custom_name)','$($_.order_date)','$($_.delivery_date)',$($_.urgent))"
}) -join ",`n"

$shipIns = ($soByBill.Values | Sort-Object po_seq | ForEach-Object {
    $shipId = $IdBase + 60000 + $_.po_seq
    $poNo = ('PO-UAT-FULL-20260605-{0:D3}' -f $_.po_seq)
    $shNo = ('SH-UAT-FULL-20260605-{0:D3}' -f $_.po_seq)
    "($shipId,'$poNo','$shNo')"
}) -join ",`n"

$header = @"
-- S1-S4 FULL UAT write (AIDOP tenant 797403760988229)
-- Batch: $BatchNo
-- Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
-- Orders: $($soByBill.Count)

SET @tenant_id     := 797403760988229;
SET @batch_no      := '$BatchNo';
SET @operator_id   := 1300000000111;
SET @operator_name := 'UAT数据导入';
SET @now           := NOW();
SET @factory_id    := 797403760988229;
SET @id_base       := $IdBase;

DROP TEMPORARY TABLE IF EXISTS tmp_s1_uat_so;
CREATE TEMPORARY TABLE tmp_s1_uat_so (
    bill_no VARCHAR(64) NOT NULL PRIMARY KEY,
    target_id BIGINT NOT NULL,
    custom_no VARCHAR(64) NULL,
    custom_name VARCHAR(128) NULL,
    order_date DATETIME NULL,
    delivery_date DATETIME NULL,
    urgent INT DEFAULT 0
);
INSERT INTO tmp_s1_uat_so VALUES
$soIns;

DROP TEMPORARY TABLE IF EXISTS tmp_s1_uat_so_line;
CREATE TEMPORARY TABLE tmp_s1_uat_so_line (
    target_id BIGINT NOT NULL PRIMARY KEY,
    source_entry VARCHAR(32) NOT NULL,
    bill_no VARCHAR(64) NOT NULL,
    entry_seq INT NOT NULL,
    item_number VARCHAR(64) NULL,
    qty DECIMAL(18,4) NULL,
    plan_date DATETIME NULL
);
INSERT INTO tmp_s1_uat_so_line VALUES
$($linesOut -join ",`n");

DROP TEMPORARY TABLE IF EXISTS tmp_s2_uat_morder;
CREATE TEMPORARY TABLE tmp_s2_uat_morder (
    target_mo_id BIGINT NOT NULL PRIMARY KEY,
    target_me_id BIGINT NOT NULL,
    source_entry VARCHAR(32) NOT NULL,
    bill_no VARCHAR(64) NOT NULL,
    morder_no VARCHAR(128) NOT NULL,
    product_code VARCHAR(128) NULL,
    need_number DECIMAL(23,10) NULL
);
INSERT INTO tmp_s2_uat_morder VALUES
$($mosOut -join ",`n");

DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_pr;
CREATE TEMPORARY TABLE tmp_s3_uat_pr (
    voucher BIGINT NOT NULL PRIMARY KEY,
    pr_billno VARCHAR(64) NOT NULL,
    morder_no VARCHAR(128) NOT NULL,
    bill_no VARCHAR(64) NOT NULL,
    icitem_code VARCHAR(64) NULL,
    pr_rqty DECIMAL(18,4) NULL,
    mo_id BIGINT NOT NULL
);
INSERT INTO tmp_s3_uat_pr VALUES
$($prsOut -join ",`n");

DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_po_line;
CREATE TEMPORARY TABLE tmp_s3_uat_po_line (
    pur_ord VARCHAR(48) NOT NULL,
    line_no INT NOT NULL,
    sales_ord VARCHAR(64) NOT NULL,
    work_ord VARCHAR(128) NULL,
    item_num VARCHAR(60) NOT NULL,
    qty DECIMAL(15,5) NOT NULL,
    PRIMARY KEY (pur_ord, line_no)
);
INSERT INTO tmp_s3_uat_po_line VALUES
$($poLinesOut -join ",`n");

DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_po_hdr;
CREATE TEMPORARY TABLE tmp_s3_uat_po_hdr (
    pur_ord VARCHAR(48) NOT NULL PRIMARY KEY,
    sales_ord VARCHAR(64) NOT NULL,
    work_ord VARCHAR(128) NULL
);
INSERT INTO tmp_s3_uat_po_hdr
SELECT pur_ord, MIN(sales_ord), MIN(work_ord) FROM tmp_s3_uat_po_line GROUP BY pur_ord;

DROP TEMPORARY TABLE IF EXISTS tmp_s4_uat_ship;
CREATE TEMPORARY TABLE tmp_s4_uat_ship (
    ship_id BIGINT NOT NULL PRIMARY KEY,
    po_billno VARCHAR(48) NOT NULL,
    shddh VARCHAR(64) NOT NULL
);
INSERT INTO tmp_s4_uat_ship VALUES
$shipIns;

"@

$body = Get-Content "$PSScriptRoot\S1S4_UAT_FULL_write_body.sql" -Raw -ErrorAction SilentlyContinue
if (-not $body) {
    $body = Get-Content "$PSScriptRoot\..\S1S4_UAT_P0_execute_write.sql" -Raw
    $start = $body.IndexOf('START TRANSACTION;')
    $body = $body.Substring($start)
    $body = $body -replace 'PR-UAT-20260604-', 'PR-UAT-FULL-20260605-'
    $body = $body -replace 'PO-UAT-20260604-', 'PO-UAT-FULL-20260605-'
    $body = $body -replace 'SH-UAT-20260604-', 'SH-UAT-FULL-20260605-'
    $body = $body -replace "CONCAT\('UAT导入:', @batch_no\)", "CONCAT('UAT导入:', @batch_no)"
    $body = $body -replace "Remark, CONCAT\('UAT:', @batch_no\)", "Remark, CONCAT('UAT:', @batch_no)"
}

Set-Content -Path $OutSql -Value ($header + $body) -Encoding UTF8
Write-Host "Wrote $OutSql"