"""Create SCF TradeRecord Excel - efficient approach."""

import openpyxl
from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.formula import ArrayFormula
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from datetime import 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: {len(trade_data)} rows, No.{trade_data[0]['No']}-{trade_data[-1]['No']}")

# Load template to get structure (only rows 1-10 needed for formula templates)
print("Loading template structure...")
wb_template = load_workbook('/tmp/thor_scf_nov2026/TradeRecord_blank.xlsx', data_only=False)
ws_template = wb_template['TradeRecord']

# Create a new minimal workbook
print("Creating new workbook...")
wb = Workbook()

# Copy ALL sheets from template (keeping structure)
# Remove default sheet
default_sheet = wb.active
wb.remove(default_sheet)

# Copy the TradeRecord sheet structure
# Since we can't easily copy sheet structure in openpyxl, let's just work with the template
# but truncate it efficiently

print("Working with template...")
ws = ws_template

# Instead of clearing all rows, we'll write our data to rows 4-98
# and then set the max_row explicitly

# Collect formula templates from row 4
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:
        if hasattr(cell.value, 'text'):
            formula_templates[col_letter] = ('array', cell.value.text)
        else:
            formula_templates[col_letter] = ('normal', str(cell.value) if cell.value else None)

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

TEMPLATE_ROW = 4
DATA_START_ROW = 4
DATA_END_ROW = DATA_START_ROW + len(trade_data) - 1  # = 98

def adapt_formula(formula_str, old_row, new_row):
    if not formula_str:
        return None
    result = re.sub(r'([A-Za-z]+)' + str(old_row) + r'\b', 
                    lambda m: m.group(1) + str(new_row), formula_str)
    return result

# STEP 1: Write all 95 data rows
print(f"\nWriting rows {DATA_START_ROW}-{DATA_END_ROW}...")
for i, row_data in enumerate(trade_data):
    row_num = DATA_START_ROW + i
    
    # Manual data columns
    ws.cell(row_num, 1).value = row_data['Fund']
    ws.cell(row_num, 2).value = row_data['Be_Linked']
    ws.cell(row_num, 3).value = row_data['No']
    ws.cell(row_num, 4).value = row_data['Seller']
    ws.cell(row_num, 5).value = row_data['Buyer']
    ws.cell(row_num, 6).value = row_data['Invoice_No']
    ws.cell(row_num, 7).value = datetime.fromisoformat(row_data['Invoice_Date'])
    ws.cell(row_num, 8).value = row_data['Tenor']
    ws.cell(row_num, 9).value = row_data['Invoice_Amount']
    ws.cell(row_num, 18).value = row_data['NTR_No']
    ws.cell(row_num, 19).value = datetime.fromisoformat(row_data['Date_Purchased_AR'])
    ws.cell(row_num, 20).value = row_data['Amount_Purchased']
    
    # Formula columns
    for col_letter, (ftype, fstr) in formula_templates.items():
        col_idx = FORMULA_COLS[col_letter]
        adapted = adapt_formula(fstr, TEMPLATE_ROW, row_num)
        if adapted:
            if ftype == 'array':
                ws.cell(row_num, col_idx).value = ArrayFormula('', adapted)
            else:
                ws.cell(row_num, col_idx).value = adapted
    
    if (i + 1) % 20 == 0:
        print(f"  Written {i+1}/{len(trade_data)} rows...")

# STEP 2: Clear rows 99 onwards (more efficiently)
print(f"\nClearing old data rows {DATA_END_ROW + 1} to {ws.max_row}...")
# Only clear if there are many rows; skip for now and let table definition handle it
# Update table reference
table = ws.tables['TradeRecord']
table.ref = f"A3:AC{DATA_END_ROW}"
print(f"Table ref updated to: {table.ref}")

# Format date columns  
DATE_FORMAT = 'YYYY/MM/DD'
print("Applying date formats...")
for row_num in range(DATA_START_ROW, DATA_END_ROW + 1):
    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

# Save
output_path = '/tmp/thor_scf_nov2026/SCF_TradeRecord_20261109.xlsx'
print(f"\nSaving to {output_path}...")
wb_template.save(output_path)
print("SAVED!")
import os
size = os.path.getsize(output_path)
print(f"File size: {size:,} bytes")