"""Create SCF TradeRecord Excel from template."""

import openpyxl
from openpyxl import load_workbook
from openpyxl.worksheet.formula import ArrayFormula
from openpyxl.utils import get_column_letter
from datetime import date, datetime
import json
import copy
import re

# Load trade data
with open('/tmp/thor_scf_nov2026/trade_data.json') as f:
    trade_data = json.load(f)

print(f"Trade data loaded: {len(trade_data)} rows")
print(f"No. range: {trade_data[0]['No']} to {trade_data[-1]['No']}")

# Load the template
print("\nLoading template...")
wb = load_workbook('/tmp/thor_scf_nov2026/TradeRecord_blank.xlsx', data_only=False)
ws = wb['TradeRecord']

# Save the formula templates from row 4 (before deleting)
# Column positions (1-indexed)
FORMULA_COLS = {
    'J': 10, 'K': 11, 'L': 12, 'M': 13, 'N': 14, 'O': 15, 'P': 16, 
    'Q': 17, 'U': 21, 'V': 22, 'W': 23, 'X': 24, 'Y': 25, 'Z': 26,
    'AA': 27, 'AB': 28
}

formula_templates = {}
for col_letter, col_idx in FORMULA_COLS.items():
    cell = ws.cell(4, col_idx)
    if cell.value:
        # Store the raw formula string
        if hasattr(cell.value, 'text'):
            # ArrayFormula
            formula_templates[col_letter] = ('array', cell.value.text)
        else:
            formula_templates[col_letter] = ('normal', cell.value)

print(f"Formula templates captured: {list(formula_templates.keys())}")

# Delete all data rows (row 4 to max_row)
print(f"\nClearing {ws.max_row - 3} data rows...")
# Instead of deleting (which changes table structure), overwrite with None
max_row_to_clear = ws.max_row
for row_num in range(4, max_row_to_clear + 1):
    for col_num in range(1, ws.max_column + 1):
        ws.cell(row_num, col_num).value = None

# Update the table reference to cover only 95 data rows
table = ws.tables['TradeRecord']
old_ref = table.ref
print(f"Old table ref: {old_ref}")
new_ref = f"A3:AC{3 + len(trade_data)}"  # Header at row 3, data rows 4 to 4+94=98
table.ref = new_ref
print(f"New table ref: {new_ref}")

# Write data rows
def adapt_formula(template_formula, old_row, new_row):
    """Replace row number references in formula from old_row to new_row."""
    if template_formula is None:
        return None
    # Replace absolute row numbers in cell references (e.g., J4 -> J98)
    result = re.sub(r'([A-Z]+)' + str(old_row) + r'\b', 
                    lambda m: m.group(1) + str(new_row), template_formula)
    return result

TEMPLATE_ROW = 4

print(f"\nWriting {len(trade_data)} data rows...")
for i, row_data in enumerate(trade_data):
    row_num = 4 + i
    
    # Manual data columns
    ws.cell(row_num, 1).value = row_data['Fund']           # A: Fund
    ws.cell(row_num, 2).value = row_data['Be_Linked']      # B: Be Linked
    ws.cell(row_num, 3).value = row_data['No']             # C: No.
    ws.cell(row_num, 4).value = row_data['Seller']         # D: Seller's name
    ws.cell(row_num, 5).value = row_data['Buyer']          # E: Debtor's name
    ws.cell(row_num, 6).value = row_data['Invoice_No']     # F: Invoice No.
    ws.cell(row_num, 7).value = datetime.fromisoformat(row_data['Invoice_Date'])  # G: Invoice Date
    ws.cell(row_num, 8).value = row_data['Tenor']          # H: Tenor
    ws.cell(row_num, 9).value = row_data['Invoice_Amount'] # I: Invoice Amount
    ws.cell(row_num, 18).value = row_data['NTR_No']        # R: NTR No.
    ws.cell(row_num, 19).value = datetime.fromisoformat(row_data['Date_Purchased_AR'])  # S: Date purchased AR
    ws.cell(row_num, 20).value = row_data['Amount_Purchased']  # T: Amount purchased
    
    # Copy formula columns from template (adapting row number)
    for col_letter, (formula_type, formula_str) in formula_templates.items():
        col_idx = FORMULA_COLS[col_letter]
        adapted = adapt_formula(formula_str, TEMPLATE_ROW, row_num)
        if adapted:
            if formula_type == 'array':
                ws.cell(row_num, col_idx).value = ArrayFormula('', adapted)
            else:
                ws.cell(row_num, col_idx).value = adapted

# Format date cells
from openpyxl.styles import numbers
DATE_FORMAT = 'YYYY/MM/DD'
for row_num in range(4, 4 + len(trade_data)):
    ws.cell(row_num, 7).number_format = DATE_FORMAT   # Invoice Date
    ws.cell(row_num, 10).number_format = DATE_FORMAT  # Due Date
    ws.cell(row_num, 19).number_format = DATE_FORMAT  # Date purchased AR

print("Data written successfully")
print(f"Last row: {3 + len(trade_data)}")

# Save the file
output_path = '/tmp/thor_scf_nov2026/SCF_TradeRecord_20261109.xlsx'
wb.save(output_path)
print(f"\nSaved: {output_path}")
import os
print(f"File size: {os.path.getsize(output_path):,} bytes")