#!/usr/bin/env python3
"""
Build SCF TradeRecord Excel for 11月份 2nd batch (No.11279-11344)
"""

import openpyxl
from openpyxl import load_workbook
from datetime import datetime, timedelta
import copy

TEMPLATE_PATH = '/tmp/thor_scf_nov2/TradeRecord_blank.xlsx'
OUTPUT_PATH = '/tmp/thor_scf_nov2/SCF_TradeRecord_20261109.xlsx'

# Data definitions
# Each entry: (No, A_fund, B_be_linked, D_seller, E_buyer, F_inv_no, G_inv_date, H_tenor, I_inv_amount, J_due_date, K_credit_rating, N_country, P_industry, R_ntr_no, S_date_purchased, T_amount_purchased)

def make_date(y, m, d):
    return datetime(y, m, d)

AR9_BELINKED = "(10379-10383 , 10400-10407)"
AR10_BELINKED = "(10443-10450 , 10476-10481 , 10529-10538)"
AR11_BELINKED = "(10369-10378 , 10601-10617)"

DATE_11_25 = make_date(2026, 11, 25)
DATE_11_30 = make_date(2026, 11, 30)

rows_data = []

# === AR-9 (再投資日 2026/11/25) ===
# 4-17 L'Oreal (NTR: CN046260930006)
no = 11279
seller = "Universal Best Enterprise Ltd."
buyer = "L'Oreal S.A."
ntr_no = "CN046260930006"
cr = "AA"
country = "France"
industry = "Consumer Staples"
inv_date = make_date(2026, 9, 16)
due_date = make_date(2027, 3, 15)
tenor = 180

invoices_417 = [
    ("U121518V260916001", 1217432.60, 975000),
    ("U121518V260916002", 1274104.32, 1000000),
    ("U121518V260916003", 1314079.16, 1050000),
    ("U121518V260916004", 1156279.26, 950000),
    ("U121518V260916005", 1317312.60, 1050000),
    ("U121518V260916006", 1288383.76, 1025000),
    ("U121518V260916007", 1216565.20, 975000),
    ("U121518V260916008", 1067654.88, 850000),
    ("U121518V260916009", 1328575.80, 1050000),
    ("U121518V260916010", 1360794.24, 1075000),
]
for inv_no, inv_amt, purch in invoices_417:
    rows_data.append((no, "SCF", AR9_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_25, purch))
    no += 1

# 4-9 Compass Group (NTR: CN046261016004)
seller = "Universal Best Enterprise Ltd."
buyer = "Compass Group PLC"
ntr_no = "CN046261016004"
cr = "A"
country = "UK"
industry = "Consumer Discretionary"
inv_date = make_date(2026, 9, 23)
due_date = make_date(2027, 3, 22)

invoices_49 = [
    ("U353619V26092301", 900477.60, 700000),
    ("U353619V26092302", 903092.22, 700000),
    ("U353619V26092303", 1241194.80, 985000),
    ("U353619V26092304", 1296483.20, 1038966.02),
]
for inv_no, inv_amt, purch in invoices_49:
    rows_data.append((no, "SCF", AR9_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_25, purch))
    no += 1

# === AR-10 (再投資日 2026/11/30) ===
# 2-7 PepsiCo (NTR: CN046261016005)
seller = "Polaris Global Co., Ltd."
buyer = "PepsiCo, Inc."
ntr_no = "CN046261016005"
cr = "A+"
country = "USA"
industry = "Consumer Staples"
inv_date = make_date(2026, 9, 18)
due_date = make_date(2027, 3, 17)

invoices_27 = [
    ("PG26SEP18001", 1201918.50, 975000),
    ("PG26SEP18002", 1159073.40, 950000),
    ("PG26SEP18003", 1035559.20, 825000),
    ("PG26SEP18004", 1091403.80, 865000),
    ("PG26SEP18005", 1328296.20, 1050000),
    ("PG26SEP18006", 1235009.40, 985000),
    ("PG26SEP18007", 1073451.60, 850000),
    ("PG26SEP18008", 1199078.80, 900000),
]
for inv_no, inv_amt, purch in invoices_27:
    rows_data.append((no, "SCF", AR10_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

# 7-1 Magna (NTR: CN046261013003)
seller = "WONDERFUL MILESTONE CO., LTD."
buyer = "Magna International Inc."
ntr_no = "CN046261013003"
cr = "A-"
country = "Canada"
industry = "Consumer Discretionary"
inv_date = make_date(2026, 9, 16)
due_date = make_date(2027, 3, 15)

invoices_71 = [
    ("WD26-INV0916-001", 1254079.60, 1000000),
    ("WD26-INV0916-002", 1234255.80, 985000),
    ("WD26-INV0916-003", 1089032.70, 865000),
    ("WD26-INV0916-004", 1255382.58, 1000000),
    ("WD26-INV0916-005", 1117552.80, 887547.47),
]
for inv_no, inv_amt, purch in invoices_71:
    rows_data.append((no, "SCF", AR10_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

# 10-4 Illinois Tool Works (NTR: CN046261015005)
seller = "Century Leader Int'l Ltd."
buyer = "Illinois Tool Works Inc."
ntr_no = "CN046261015005"
cr = "A+"
country = "USA"
industry = "Industrials"
inv_date = make_date(2026, 9, 17)
due_date = make_date(2027, 3, 16)

invoices_104 = [
    ("CL2026091701-I294", 1557079.20, 1250000),
    ("CL2026091702-I294", 1509840.60, 1230000),
    ("CL2026091703-I294", 1534572.39, 1240000),
    ("CL2026091704-I294", 1306317.74, 1050000),
    ("CL2026091705-I294", 1458226.21, 1200000),
    ("CL2026091706-I294", 1469098.63, 1200000),
    ("CL2026091707-I294", 1457224.39, 1200000),
    ("CL2026091708-I294", 1566846.00, 1250000),
    ("CL2026091709-I294", 1688297.37, 1375000),
    ("CL2026091710-I294", 1512444.48, 1005000),
]
for inv_no, inv_amt, purch in invoices_104:
    rows_data.append((no, "SCF", AR10_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

# === AR-11 (再投資日 2026/11/30) ===
# 3-11 Analog Devices (NTR: CN046261014004)
seller = "LONGWIN INDUSTRY (HK) CO., LTD."
buyer = "Analog Devices, Inc."
ntr_no = "CN046261014004"
cr = "A"
country = "USA"
industry = "Information Technology"
inv_date = make_date(2026, 9, 17)
due_date = make_date(2027, 3, 16)

invoices_311 = [
    ("06281-260917001", 1362730.20, 1100000),
    ("06281-260917002", 1130526.60, 900000),
    ("06281-260917003", 1254061.30, 1000000),
    ("06281-260917004", 1348112.50, 1050000),
    ("06281-260917005", 1231159.92, 985000),
    ("06281-260917006", 1226131.40, 985000),
    ("06281-260917007", 1125936.00, 900000),
    ("06281-260917008", 1227077.90, 985000),
    ("06281-260917009", 1236737.60, 985000),
    ("06281-260917010", 1216277.49, 967742.99),
]
for inv_no, inv_amt, purch in invoices_311:
    rows_data.append((no, "SCF", AR11_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

# 8-1 Costco (NTR: CN046260930007)
seller = "VICTOR SAIL GLOBAL LIMITED"
buyer = "Costco Wholesale Corp."
ntr_no = "CN046260930007"
cr = "AA"
country = "USA"
industry = "Consumer Staples"
inv_date = make_date(2026, 9, 11)
due_date = make_date(2027, 3, 10)

invoices_81 = [
    ("VS260911001", 1288505.40, 1025000),
    ("VS260911002", 1193791.60, 965000),
    ("VS260911003", 1192998.46, 965000),
    ("VS260911004", 1343203.16, 1050000),
    ("VS260911005", 1330563.96, 1050000),
    ("VS260911006", 1189324.80, 965000),
    ("VS260911007", 1262160.16, 1000000),
    ("VS260911008", 1206990.51, 975000),
    ("VS260911009", 1273755.14, 1005000),
]
for inv_no, inv_amt, purch in invoices_81:
    rows_data.append((no, "SCF", AR11_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

# 13-2 Caterpillar (NTR: CN046261019003)
seller = "Jiangsu Jiutian Supply Chain Management Co., Ltd."
buyer = "Caterpillar Inc."
ntr_no = "CN046261019003"
cr = "A"
country = "USA"
industry = "Industrials"
inv_date = make_date(2026, 9, 14)
due_date = make_date(2027, 3, 13)

invoices_132 = [
    ("26-0316-0914-01", 1350052.40, 1100000),
    ("26-0316-0914-02", 1176753.88, 965000),
    ("26-0316-0914-03", 1277616.00, 1025000),
    ("26-0316-0914-04", 1353538.16, 1100000),
    ("26-0316-0914-05", 1129756.56, 810000),
]
for inv_no, inv_amt, purch in invoices_132:
    rows_data.append((no, "SCF", AR11_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

# 13-7 Hitachi (NTR: CN046261016003)
seller = "Jiangsu Jiutian Supply Chain Management Co., Ltd."
buyer = "Hitachi Ltd."
ntr_no = "CN046261016003"
cr = "A"
country = "Japan"
industry = "Industrials"
inv_date = make_date(2026, 9, 16)
due_date = make_date(2027, 3, 15)

invoices_137 = [
    ("26-0892-0916-01", 1217124.60, 975000),
    ("26-0892-0916-02", 1380304.80, 1125000),
    ("26-0892-0916-03", 1372071.86, 1100000),
    ("26-0892-0916-04", 1163726.40, 950000),
    ("26-0892-0916-05", 1165649.40, 850000),
]
for inv_no, inv_amt, purch in invoices_137:
    rows_data.append((no, "SCF", AR11_BELINKED, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, DATE_11_30, purch))
    no += 1

print(f"Total rows prepared: {len(rows_data)}")
print(f"No. range: {rows_data[0][0]} - {rows_data[-1][0]}")

# Verify totals
def verify_totals():
    ntr_groups = {}
    for row in rows_data:
        ntr_no = row[13]
        if ntr_no not in ntr_groups:
            ntr_groups[ntr_no] = {'inv': 0, 'purch': 0}
        ntr_groups[ntr_no]['inv'] += row[8]
        ntr_groups[ntr_no]['purch'] += row[15]
    
    expected = {
        'CN046260930006': (12541181.82, 10000000.00),   # L'Oreal
        'CN046261016004': (4341247.82, 3423966.02),      # Compass
        'CN046261016005': (9323790.90, 7400000.00),      # PepsiCo
        'CN046261013003': (5950303.48, 4737547.47),      # Magna
        'CN046261015005': (15059947.01, 12000000.00),    # Illinois Tool
        'CN046261014004': (12358750.91, 9857742.99),     # Analog Devices
        'CN046260930007': (11281293.19, 9000000.00),     # Costco
        'CN046261019003': (6287717.00, 5000000.00),      # Caterpillar
        'CN046261016003': (6298877.06, 5000000.00),      # Hitachi
    }
    
    all_ok = True
    for ntr_no, (exp_inv, exp_purch) in expected.items():
        actual = ntr_groups.get(ntr_no, {})
        inv_ok = abs(actual.get('inv', 0) - exp_inv) < 0.01
        purch_ok = abs(actual.get('purch', 0) - exp_purch) < 0.01
        status = "✓" if (inv_ok and purch_ok) else "✗"
        print(f"  {status} {ntr_no}: inv={actual.get('inv',0):.2f}/{exp_inv:.2f}, purch={actual.get('purch',0):.2f}/{exp_purch:.2f}")
        if not (inv_ok and purch_ok):
            all_ok = False
    return all_ok

print("\n=== Verifying totals ===")
ok = verify_totals()
print(f"\nAll totals OK: {ok}")

# Now build the Excel
print("\n=== Building Excel ===")
wb = load_workbook(TEMPLATE_PATH)
ws = wb['TradeRecord']

# Set Z2 to end of month (2026-11-30)
ws['Z2'] = datetime(2026, 11, 30)

# Column mapping (1-indexed):
# A=1, B=2, C=3, D=4, E=5, F=6, G=7, H=8, I=9, J=10, K=11, L=12, M=13, N=14, O=15, P=16, Q=17, R=18, S=19, T=20

for i, row in enumerate(rows_data):
    excel_row = 4 + i  # Start from row 4
    (no, fund, be_linked, seller, buyer, inv_no, inv_date, tenor, inv_amt, due_date, cr, country, industry, ntr_no, date_purch, amount_purch) = row
    
    ws.cell(row=excel_row, column=1).value = fund         # A: Fund
    ws.cell(row=excel_row, column=2).value = be_linked    # B: Be Linked
    ws.cell(row=excel_row, column=3).value = no           # C: No.
    ws.cell(row=excel_row, column=4).value = seller       # D: Seller's name
    ws.cell(row=excel_row, column=5).value = buyer        # E: Debtor's name
    ws.cell(row=excel_row, column=6).value = inv_no       # F: Invoice No.
    ws.cell(row=excel_row, column=7).value = inv_date     # G: Invoice Date
    ws.cell(row=excel_row, column=8).value = tenor        # H: Tenor (static)
    ws.cell(row=excel_row, column=9).value = inv_amt      # I: Invoice Amount
    ws.cell(row=excel_row, column=10).value = due_date    # J: Due Date
    ws.cell(row=excel_row, column=11).value = cr          # K: Credit Rating (static)
    # L=12 (Score): leave formula from template
    # M=13 (Grade): leave formula from template
    ws.cell(row=excel_row, column=14).value = country     # N: Country (static)
    # O=15 (Currency): leave formula from template
    ws.cell(row=excel_row, column=16).value = industry    # P: Industry (static)
    # Q=17 (Proportion): leave formula from template
    ws.cell(row=excel_row, column=18).value = ntr_no      # R: NTR No.
    ws.cell(row=excel_row, column=19).value = date_purch  # S: Date purchased AR
    ws.cell(row=excel_row, column=20).value = amount_purch # T: Amount Purchased

print(f"Writing {len(rows_data)} rows to Excel (rows 4 to {3+len(rows_data)})...")
wb.save(OUTPUT_PATH)
print(f"Saved to: {OUTPUT_PATH}")
print("Done!")