import openpyxl
from openpyxl import load_workbook
from datetime import datetime, date
import json
import os

# =====================================================================
# OC Ratio Table Lookup
# =====================================================================
with open('/tmp/thor_scf_nov2026/oc_table.json') as f:
    oc_data = {int(k): v for k, v in json.load(f).items()}

def lookup_purchased_amount(invoice_amount):
    """Round invoice amount DOWN to nearest 25,000, look up Purchased Amount."""
    rounded = (int(invoice_amount) // 25000) * 25000
    # Find the matching or next lower entry
    keys = sorted(oc_data.keys())
    result = None
    for k in keys:
        if k <= rounded:
            result = oc_data[k]
        else:
            break
    return result

def compute_purchased_amounts(invoice_amounts, total_purchased):
    """Compute purchased amount for each invoice using OC Ratio table.
    Last invoice absorbs remainder to match total_purchased."""
    n = len(invoice_amounts)
    purchased = []
    for i in range(n - 1):
        pa = lookup_purchased_amount(invoice_amounts[i])
        purchased.append(pa)
    # Last invoice = total - sum of others
    last = round(total_purchased - sum(purchased), 2)
    purchased.append(last)
    return purchased

# =====================================================================
# NTR Data (from OCR and email extraction)
# =====================================================================

# Format: (ntr_id, ntr_no, inv_date, tenor, due_date, 
#          [(invoice_no, invoice_amount), ...], total_inv_amount)
# Note: Invoice amounts derived where needed

NTR_DATA = {
    # NTR ID: {ntr_no, inv_date, tenor_days, due_date, invoices[(inv_no, inv_amt)]}
    
    '12-9': {
        'ntr_no': 'CN046261008002',
        'inv_date': date(2026, 9, 18),
        'tenor': 180,
        'due_date': date(2027, 3, 17),
        'invoices': [
            ('670417-I-260918001', 1194780.64),
            ('670417-I-260918002', 1233807.92),
            ('670417-I-260918003', 1232113.20),
            ('670417-I-260918004', 1279478.88),
            ('670417-I-260918005', 1160277.36),
            ('670417-I-260918006', 1187713.20),
            ('670417-I-260918007', 1132183.40),
            ('670417-I-260918008', 1241264.00),
        ],
        'total_purchased': 7711214.60,
    },
    
    '12-7-2': {
        'ntr_no': 'CN046261015001',
        'inv_date': date(2026, 9, 21),
        'tenor': 180,
        'due_date': date(2027, 3, 20),
        'invoices': [
            ('073309-I-260921001', 1178323.20),
            ('073309-I-260921002', 1267030.20),
            ('073309-I-260921003', 1131794.00),  # derived: total 8165832.60 - others 6
            ('073309-I-260921004', 1193445.20),
            ('073309-I-260921005', 1130154.80),
            ('073309-I-260921006', 1135021.00),
            ('073309-I-260921007', 1130064.20),
        ],
        'total_purchased': 6500000.00,
    },
    
    '15-4': {
        'ntr_no': 'CN046261012005',
        'inv_date': date(2026, 9, 14),
        'tenor': 180,
        'due_date': date(2027, 3, 13),
        'invoices': [
            ('J31277N260914001', 1397665.80),
            ('J31277N260914002', 1300754.64),
            ('J31277N260914003', 1441601.54),
            ('J31277N260914004', 1393368.20),
            ('J31277N260914005', 1390217.60),
            ('J31277N260914006', 1327388.00),
            ('J31277N260914007', 1288924.44),
            ('J31277N260914008', 1314156.40),
        ],
        'total_purchased': 8657522.86,
    },
    
    '10-10': {
        'ntr_no': 'CN046261013002',
        'inv_date': date(2026, 9, 11),
        'tenor': 180,
        'due_date': date(2027, 3, 10),
        'invoices': [
            ('CL2026091101-W085', 1190704.20),
            ('CL2026091102-W085', 1157874.20),
            ('CL2026091103-W085', 1182709.40),
            ('CL2026091104-W085', 1132564.80),
            ('CL2026091105-W085', 1064955.00),
            ('CL2026091106-W085', 1305644.20),
            ('CL2026091107-W085', 1143021.40),
            ('CL2026091108-W085', 1276163.40),
            ('CL2026091109-W085', 1133120.28),
            ('CL2026091110-W085', 1138588.40),
        ],
        'total_purchased': 9367428.68,
    },
    
    '1-11': {
        'ntr_no': 'CN046260924008',
        'inv_date': date(2026, 9, 10),
        'tenor': 180,
        'due_date': date(2027, 3, 9),
        'invoices': [
            ('INV5142260910001', 1189908.00),
            ('INV5142260910002', 1220924.40),
            ('INV5142260910003', 1252950.16),
            ('INV5142260910004', 1189558.24),
            ('INV5142260910005', 1312921.64),
            ('INV5142260910006', 1266681.64),
            ('INV5142260910007', 1218955.60),
            ('INV5142260910008', 1144006.40),
        ],
        'total_purchased': 7800000.00,
    },
    
    '5-9': {
        'ntr_no': 'CN046261013004',
        'inv_date': date(2026, 9, 16),
        'tenor': 180,
        'due_date': date(2027, 3, 15),
        # Adidas AG: I correction for 1260916 -> I260916
        'invoices': [
            ('I260916-6084-01', 1063362.60),
            ('I260916-6084-02', 1070539.00),
            ('I260916-6084-03', 1150084.60),
            ('I260916-6084-04', 1032626.92),  # derived: 4316613.12 - 3284986.20
        ],
        'total_purchased': 3420867.72,
    },
    
    '1-1': {
        'ntr_no': 'CN046261015006',
        'inv_date': date(2026, 9, 21),
        'tenor': 180,
        'due_date': date(2027, 3, 20),
        'invoices': [
            ('INV8874260921001', 1242593.80),
            ('INV8874260921002', 1279569.72),
            ('INV8874260921003', 1316634.68),
            ('INV8874260921004', 1336790.70),
            ('INV8874260921005', 1273033.40),
            ('INV8874260921006', 1126917.84),
            ('INV8874260921007', 1352560.00),
            ('INV8874260921008', 1203559.32),
            ('INV8874260921009', 1262368.80),
            ('INV8874260921010', 1168797.92),
        ],
        'total_purchased': 10000000.00,
    },
    
    '37-14-4': {
        'ntr_no': 'CN046261013006',
        'inv_date': date(2026, 9, 11),
        'tenor': 180,
        'due_date': date(2027, 3, 10),
        # 3M Co.: 6740-I format (I correction)
        'invoices': [
            ('6740-I20260911001', 1049514.40),
            ('6740-I20260911002', 1130150.80),
            ('6740-I20260911003', 1458579.20),
        ],
        'total_purchased': 2895996.30,
    },
    
    '37-14-5': {
        'ntr_no': 'CN046261014002',
        'inv_date': date(2026, 9, 21),
        'tenor': 180,
        'due_date': date(2027, 3, 20),
        # HCL America: 8524-I correction
        'invoices': [
            ('8524-I20260921001', 1344337.92),
            ('8524-I20260921002', 1430000.00),
            ('8524-I20260921003', 1290911.28),
        ],
        'total_purchased': 3245981.65,
    },
    
    '16-14-2': {
        'ntr_no': 'CN046260922006',
        'inv_date': date(2026, 9, 8),
        'tenor': 180,
        'due_date': date(2027, 3, 7),
        # Novartis AG: in "混合 I/1" correction list - check if S4448 starts with S (letter not I/1)
        'invoices': [
            ('S4448-260908001', 1247228.84),
            ('S4448-260908002', 1106950.92),
            ('S4448-260908003', 1319854.42),
            ('S4448-260908004', 1269706.80),
            ('S4448-260908005', 1256621.44),  # garbled in OCR - derived from total
            ('S4448-260908006', 1320063.00),
            ('S4448-260908007', 1163623.86),
            ('S4448-260908008', 1231539.40),
            ('S4448-260908009', 1302668.64),
            ('S4448-260908010', 1254262.56),
        ],
        'total_purchased': 10000000.00,
    },
    
    '16-14-6': {
        'ntr_no': 'CN046261008003',
        'inv_date': date(2026, 9, 14),
        'tenor': 180,
        'due_date': date(2027, 3, 13),
        'invoices': [
            ('S0158-260914001', 1337133.76),
            ('S0158-260914002', 1315632.80),
            ('S0158-260914003', 1260932.48),
            ('S0158-260914004', 1392156.20),
            ('S0158-260914005', 1150308.56),
            ('S0158-260914006', 1318555.04),  # derived: 10284187.72 - rest
            ('S0158-260914007', 1195530.72),
            ('S0158-260914008', 1313938.16),
        ],
        'total_purchased': 8211022.01,
    },
    
    '13-6': {
        'ntr_no': 'CN046260930005',
        'inv_date': date(2026, 9, 9),
        'tenor': 180,
        'due_date': date(2027, 3, 8),
        'invoices': [
            ('26-0185-0909-01', 1255169.67),
            ('26-0185-0909-02', 1202677.70),
            ('26-0185-0909-03', 1262193.10),
            ('26-0185-0909-04', 1144099.20),
            ('26-0185-0909-05', 1193616.24),
        ],
        'total_purchased': 4800000.00,
    },
    
    '36-14-1': {
        'ntr_no': 'CN046261012006',
        'inv_date': date(2026, 9, 14),
        'tenor': 180,
        'due_date': date(2027, 3, 13),
        # Siemens AG: I362 correction (1362 -> I362)
        'invoices': [
            ('I3620260914001', 1126017.20),
            ('I3620260914002', 1318321.86),
            ('I3620260914003', 1216605.18),
            ('I3620260914004', 1104570.30),
            ('I3620260914005', 1140043.69),
            ('I3620260914006', 1009456.90),
        ],
        'total_purchased': 5511424.00,
    },
    
    '10-9': {
        'ntr_no': 'CN046261013005',
        'inv_date': date(2026, 9, 24),
        'tenor': 180,
        'due_date': date(2027, 3, 23),
        'invoices': [
            ('CL2026092401-A199', 1282858.14),
            ('CL2026092402-A199', 1313612.70),
            ('CL2026092403-A199', 1191494.00),
            ('CL2026092404-A199', 1380596.00),
            ('CL2026092405-A199', 1338271.40),
        ],
        'total_purchased': 5177165.45,
    },
}

# =====================================================================
# TradeRecord Entry structure (sorted by No.)
# =====================================================================

# Be Linked lookup (from Replace No. in emails)
BE_LINKED = {
    '12-9': '(10408-10415)',
    '12-7-2': '(10433-10439 , 10468-10475)',
    '15-4': '(10433-10439 , 10468-10475)',
    '10-10': '(10420-10424 , 10499-10502)',
    '1-11': '(10419 , 10489-10498)',
    '5-9': '(10419 , 10489-10498)',
    '1-1': '(10395-10399 , 10425-10432)',
    '37-14-4': '(10395-10399 , 10425-10432)',
    '37-14-5': '(10440-10442)',
    '16-14-2': '(10384-10394 , 10451-10458)',
    '16-14-6': '(10384-10394 , 10451-10458)',
    '13-6': '(10503-10506 , 10523-10528)',
    '36-14-1': '(10503-10506 , 10523-10528)',
    '10-9': '(10459 , 10462-10465)',
}

# Seller canonical names (from SellerSource)
SELLER = {
    '12-9': 'Hainan Kehuajian Industrial Co., Ltd.',
    '12-7-2': 'Hainan Kehuajian Industrial Co., Ltd.',
    '15-4': 'Jiangsu Shine Wise Trading Co., Ltd',
    '10-10': "Century Leader Int'l Ltd.",
    '1-11': 'SUPER ACHIEVER LIMITED',
    '5-9': 'Creation Factory Co., Ltd.',
    '1-1': 'SUPER ACHIEVER LIMITED',
    '37-14-4': 'GREAT MASTER PTE. LTD.',
    '37-14-5': 'GREAT MASTER PTE. LTD.',
    '16-14-2': 'Xiamen Honest Supply Chain Co., Ltd',
    '16-14-6': 'Xiamen Honest Supply Chain Co., Ltd',
    '13-6': 'Jiangsu Jiutian Supply Chain Management Co., Ltd.',
    '36-14-1': 'MAGICAL TREASURE LIMITED',
    '10-9': "Century Leader Int'l Ltd.",
}

# Buyer canonical names (from BuyerSource)
BUYER = {
    '12-9': 'LVMH Moet Hennessy Louis Vuitton S.E.',
    '12-7-2': 'Nucor Corp.',
    '15-4': 'Eaton Corporation',
    '10-10': 'Whole Foods Market, Inc.',
    '1-11': 'Denso Corporation',
    '5-9': 'Adidas AG',
    '1-1': 'Nestle S.A.',
    '37-14-4': '3M Co.',
    '37-14-5': 'HCL America, Inc.',
    '16-14-2': 'Novartis AG',
    '16-14-6': 'Merck and Co., Inc.',
    '13-6': 'Rockwell Automation Inc.',
    '36-14-1': 'Siemens AG',
    '10-9': 'Associated British Foods PLC',
}

# Date Purchased AR (re-investment date)
DATE_PURCHASED = {
    '12-9': date(2026, 11, 16),
    '12-7-2': date(2026, 11, 16),
    '15-4': date(2026, 11, 16),
    '10-10': date(2026, 11, 17),
    '1-11': date(2026, 11, 18),
    '5-9': date(2026, 11, 18),
    '1-1': date(2026, 11, 23),
    '37-14-4': date(2026, 11, 23),
    '37-14-5': date(2026, 11, 23),
    '16-14-2': date(2026, 11, 23),
    '16-14-6': date(2026, 11, 23),
    '13-6': date(2026, 11, 24),
    '36-14-1': date(2026, 11, 24),
    '10-9': date(2026, 11, 24),
}

# No. assignment order (by re-investment date, then by email order)
NTR_ORDER = [
    '12-9', '12-7-2', '15-4',          # 11/16: 11184-11186
    '10-10',                             # 11/17: 11187
    '1-11', '5-9',                       # 11/18: 11188-11189
    '1-1', '37-14-4', '37-14-5', '16-14-2', '16-14-6',  # 11/23: 11190-11194
    '13-6', '36-14-1', '10-9',          # 11/24: 11195-11197
]

START_NO = 11184

# =====================================================================
# Build the full rows for TradeRecord
# =====================================================================
all_rows = []
current_no = START_NO

for ntr_id in NTR_ORDER:
    ntr = NTR_DATA[ntr_id]
    invoices = ntr['invoices']
    
    # Compute Amount Purchased for each invoice
    inv_amounts = [inv[1] for inv in invoices]
    purchased_amounts = compute_purchased_amounts(inv_amounts, ntr['total_purchased'])
    
    for i, (inv_no, inv_amt) in enumerate(invoices):
        row = {
            'No': current_no,
            'Fund': 'SCF',
            'Be_Linked': BE_LINKED[ntr_id],
            'Seller': SELLER[ntr_id],
            'Buyer': BUYER[ntr_id],
            'Invoice_No': inv_no,
            'Invoice_Date': ntr['inv_date'],
            'Tenor': ntr['tenor'],
            'Invoice_Amount': inv_amt,
            'Due_Date': ntr['due_date'],
            'NTR_No': ntr['ntr_no'],
            'Date_Purchased_AR': DATE_PURCHASED[ntr_id],
            'Amount_Purchased': purchased_amounts[i],
        }
        all_rows.append(row)
        current_no += 1

print(f"Total rows to write: {len(all_rows)}")
print(f"No. range: {all_rows[0]['No']} to {all_rows[-1]['No']}")
print("\nSample rows:")
for r in all_rows[:3]:
    print(f"  No.{r['No']}: {r['Buyer'][:30]:30s} | Inv: {r['Invoice_No']:25s} | Amt: {r['Invoice_Amount']:15,.2f} | Purch: {r['Amount_Purchased']:12,.2f}")

# Verify totals by NTR
print("\n=== Amount Purchased Verification by NTR ===")
for ntr_id in NTR_ORDER:
    total_email = NTR_DATA[ntr_id]['total_purchased']
    ntr_rows = [r for r in all_rows if r['NTR_No'] == NTR_DATA[ntr_id]['ntr_no'] and r['Seller'] == SELLER[ntr_id] and r['Buyer'] == BUYER[ntr_id]]
    total_calc = sum(r['Amount_Purchased'] for r in ntr_rows)
    match = '✓' if abs(total_calc - total_email) < 0.02 else '✗'
    print(f"  {match} NTR {ntr_id}: Email={total_email:,.2f}, Calc={total_calc:,.2f}")

# Save for later use
with open('/tmp/thor_scf_nov2026/trade_data.json', 'w') as f:
    json.dump([{**r, 'Invoice_Date': r['Invoice_Date'].isoformat(), 
                'Due_Date': r['Due_Date'].isoformat(),
                'Date_Purchased_AR': r['Date_Purchased_AR'].isoformat()} 
               for r in all_rows], f, indent=2)
print("\nData saved to trade_data.json")