#!/usr/bin/env python3
"""
Process all input CSV files and generate transfer reports.

Finds CSV files in the input directory, extracts transfer information,
calculates amounts using Contract/Transfer models, and outputs results
as CSV and detailed text reports.
"""
import csv
import os
import glob
from decimal import Decimal
from typing import Dict, List, Tuple
from contract import Contract
from transfer import Transfer


def parse_calculation_from_description(input_desc: str) -> Tuple[str, Dict]:
    """
    Parse an Input description into a calculation type and parameters.

    Examples:
        "50% Closing Balance Transfer" -> ("percentage_balance", {"percentage": 50})
        "100% Net Revenue Override" -> ("percentage_revenue", {"percentage": 100, "base": "net_revenue"})
        "6,300 Override" -> ("fixed_amount", {"amount": 6300})
    """
    input_desc = input_desc.strip()

    if '%' in input_desc:
        parts = input_desc.split('%')
        percentage_str = parts[0].strip().split()[-1]
        percentage = float(percentage_str)

        if 'Net Revenue' in input_desc:
            return "percentage_revenue", {"percentage": percentage, "base": "net_revenue"}
        elif 'Gross Revenue' in input_desc:
            return "percentage_revenue", {"percentage": percentage, "base": "gross_revenue"}
        else:
            return "percentage_balance", {"percentage": percentage}
    else:
        parts = input_desc.split()
        if parts and parts[0].replace(',', '').replace('.', '').replace('-', '').isdigit():
            amount = float(parts[0].replace(',', ''))
            return "fixed_amount", {"amount": amount}

    return "percentage_balance", {"percentage": 100}


def parse_float(value: str) -> float:
    """Parse a string to float, handling commas, empty strings, and sentinel values."""
    if not value or value.strip() in ['', '#N/A', 'FALSE']:
        return 0.0
    try:
        return float(value.replace(',', '').strip())
    except (ValueError, AttributeError):
        return 0.0


def read_csv_with_duplicate_headers(csv_path: str) -> List[Dict]:
    """Read CSV file, disambiguating duplicate column headers by appending _N suffixes."""
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.reader(f)
        headers = next(reader)

        seen_headers = {}
        unique_headers = []
        for header in headers:
            if header in seen_headers:
                seen_headers[header] += 1
                unique_headers.append(f"{header}_{seen_headers[header]}")
            else:
                seen_headers[header] = 0
                unique_headers.append(header)

        rows = []
        for row in reader:
            row_dict = {unique_headers[i]: row[i] if i < len(row) else '' for i in range(len(unique_headers))}
            rows.append(row_dict)

        return rows


def read_original_csv(csv_path: str) -> Dict[str, Dict]:
    """Read original CSV (skipping 2 metadata rows) and create a (group, type) -> {amount, comments} lookup."""
    original_data = {}

    with open(csv_path, 'r', encoding='utf-8') as f:
        next(f)
        next(f)

        reader = csv.DictReader(f)

        for row in reader:
            group = row.get('Group', '').strip()
            row_type = row.get('Type', '').strip()

            if not group or not row_type:
                continue

            amount = None
            comments = None

            cols = list(row.keys())
            amount_indices = [i for i, col in enumerate(cols) if col == 'Amount']

            # Use the last Amount column (the output amount) when duplicates exist
            if len(amount_indices) >= 2:
                amount_col_name = cols[amount_indices[-1]]
                amount = row.get(amount_col_name, '').strip()
            elif amount_indices:
                amount = row.get('Amount', '').strip()

            comments = row.get('Client Facing Comments', '').strip()

            key = (group, row_type)
            original_data[key] = {
                'amount': parse_float(amount) if amount else 0.0,
                'comments': comments if comments else ''
            }

    return original_data


def process_csv_file(csv_path: str, original_data: Dict[str, Dict] = None) -> List[Dict]:
    """Process a single CSV file and extract transfer information."""
    transfers = []
    rows = read_csv_with_duplicate_headers(csv_path)

    transfer_groups: Dict[str, List[dict]] = {}

    for row in rows:
        group = row.get('Group', '').strip()
        if not group:
            continue

        if group not in transfer_groups:
            transfer_groups[group] = []
        transfer_groups[group].append(row)

    for group, group_rows in transfer_groups.items():
        # "to" = source contract (money going out), "from" = destination (money coming in)
        to_row = None
        from_row = None

        for row in group_rows:
            type_1 = row.get('Type_1', '').strip()
            type_0 = row.get('Type', '').strip()

            # Handle both explicit to/from and Main/Payee naming conventions
            if type_1 in ['to', 'from']:
                transfer_type = type_1
            elif type_0 == 'Main':
                transfer_type = 'to'
            elif type_0 == 'Payee':
                transfer_type = 'from'
            else:
                transfer_type = type_1 or type_0

            if transfer_type == 'to':
                to_row = row
            elif transfer_type == 'from':
                from_row = row

        if not to_row and not from_row:
            continue

        if not to_row:
            to_row = group_rows[0]
        if not from_row:
            from_row = group_rows[-1]

        from_contract_id = to_row.get('Contract ID *', '').strip()
        to_contract_id = from_row.get('Contract ID *', '').strip()

        if not from_contract_id or not to_contract_id:
            continue

        from_contract_name = to_row.get('Agreement Name', '').strip()
        to_contract_name = from_row.get('Agreement Name', '').strip()

        # Balance comes from Balance or Closing Balance column;
        # Source column holds the value actually used in calculations
        from_balance_str = to_row.get('Balance', '') or to_row.get('Closing Balance', '')
        from_balance = parse_float(from_balance_str)
        from_source = parse_float(to_row.get('Source', ''))

        from_net_revenue = from_source if 'Net Revenue' in from_row.get('Input', '') else None
        from_gross_revenue = from_source if 'Gross Revenue' in from_row.get('Input', '') else None

        # For Closing Balance calculations, prefer the Source value
        if 'Closing Balance' in from_row.get('Input', '') and from_source != 0:
            from_balance = from_source

        to_balance_str = from_row.get('Balance', '') or from_row.get('Closing Balance', '')
        to_balance = parse_float(to_balance_str)

        # Check both Input and Input_1 due to duplicate columns
        input_desc = from_row.get('Input_1', '').strip() or from_row.get('Input', '').strip()
        if not input_desc or input_desc == '#N/A':
            input_desc = 'No Calculation Defined'

        csv_amount = parse_float(from_row.get('Amount', ''))

        currency = from_row.get('Currency_1', '').strip() or from_row.get('Currency', '').strip() or 'USD'
        is_recouped = to_row.get('Current Status', '').strip().lower() == 'recouped'

        original_amount = None
        original_comments = ''
        if original_data:
            key = (group, 'from')
            if key in original_data:
                original_amount = original_data[key]['amount']
                original_comments = original_data[key]['comments']

        transfers.append({
            'group': group,
            'from_contract_id': from_contract_id,
            'from_contract_name': from_contract_name,
            'from_balance': from_balance,
            'from_net_revenue': from_net_revenue,
            'from_gross_revenue': from_gross_revenue,
            'is_recouped': is_recouped,
            'to_contract_id': to_contract_id,
            'to_contract_name': to_contract_name,
            'to_balance': to_balance,
            'calculation_description': input_desc,
            'csv_amount': csv_amount,
            'currency': currency,
            'source_file': os.path.basename(csv_path),
            'original_amount': original_amount,
            'original_comments': original_comments
        })

    return transfers


def process_transfer(transfer_data: Dict) -> Dict:
    """Process a single transfer using Contract and Transfer models, returning calculated results."""
    from_contract = Contract(
        contract_id=transfer_data['from_contract_id'],
        name=transfer_data['from_contract_name'],
        projected_balance=transfer_data['from_balance'],
        net_revenue=transfer_data['from_net_revenue'],
        gross_revenue=transfer_data['from_gross_revenue'],
        is_recouped=transfer_data['is_recouped']
    )

    to_contract = Contract(
        contract_id=transfer_data['to_contract_id'],
        name=transfer_data['to_contract_name'],
        projected_balance=transfer_data['to_balance']
    )

    calc_type, calc_params = parse_calculation_from_description(
        transfer_data['calculation_description']
    )

    transfer = Transfer(
        transfer_id=f"T_{transfer_data['from_contract_id']}_{transfer_data['to_contract_id']}",
        from_contract_id=from_contract.contract_id,
        to_contract_id=to_contract.contract_id,
        calculation_type=calc_type,
        calculation_params=calc_params
    )

    calculated_amount = transfer.calculate_amount(from_contract)

    from_final_balance = Decimal(str(from_contract.projected_balance)) - calculated_amount
    to_final_balance = Decimal(str(to_contract.projected_balance)) + calculated_amount

    original_amount = transfer_data.get('original_amount')
    matches_original = False
    if original_amount is not None:
        matches_original = abs(float(calculated_amount) - original_amount) < 0.01

    return {
        'group': transfer_data['group'],
        'from_contract_id': from_contract.contract_id,
        'from_contract_name': from_contract.name,
        'from_initial_balance': from_contract.projected_balance,
        'from_final_balance': float(from_final_balance),
        'to_contract_id': to_contract.contract_id,
        'to_contract_name': to_contract.name,
        'to_initial_balance': to_contract.projected_balance,
        'to_final_balance': float(to_final_balance),
        'transfer_amount': float(calculated_amount),
        'calculation_type': calc_type,
        'calculation_params': calc_params,
        'calculation_description': transfer_data['calculation_description'],
        'csv_amount': transfer_data['csv_amount'],
        'currency': transfer_data['currency'],
        'matches_csv': abs(float(calculated_amount) - transfer_data['csv_amount']) < 0.01,
        'source_file': transfer_data['source_file'],
        'original_amount': original_amount if original_amount is not None else '',
        'original_comments': transfer_data.get('original_comments', ''),
        'matches_original': 'Y' if matches_original else ('N' if original_amount is not None else '')
    }


def write_detailed_report(all_results: List[Dict], output_path: str):
    """Write detailed transfer report to file."""
    with open(output_path, 'w', encoding='utf-8') as f:
        f.write("=" * 100 + "\n")
        f.write("TRANSFER PROCESSING RESULTS - DETAILED REPORT\n")
        f.write("=" * 100 + "\n\n")

        for i, result in enumerate(all_results, 1):
            f.write(f"Transfer #{i}: {result['group']}\n")
            f.write("-" * 100 + "\n")
            f.write(f"FROM Contract: {result['from_contract_id']} - {result['from_contract_name']}\n")
            f.write(f"  Initial Balance: {result['currency']} {result['from_initial_balance']:,.2f}\n")
            f.write(f"  Final Balance:   {result['currency']} {result['from_final_balance']:,.2f}\n")
            f.write("\n")
            f.write(f"TO Contract: {result['to_contract_id']} - {result['to_contract_name']}\n")
            f.write(f"  Initial Balance: {result['currency']} {result['to_initial_balance']:,.2f}\n")
            f.write(f"  Final Balance:   {result['currency']} {result['to_final_balance']:,.2f}\n")
            f.write("\n")
            f.write(f"Transfer Amount: {result['currency']} {result['transfer_amount']:,.2f}\n")
            f.write(f"Calculation: {result['calculation_description']}\n")
            f.write(f"  Type: {result['calculation_type']}\n")
            f.write(f"  Params: {result['calculation_params']}\n")
            f.write("\n")

            if result['matches_csv']:
                f.write(" Calculated amount matches CSV amount\n")
            else:
                f.write(f"✗ MISMATCH: CSV amount = {result['currency']} {result['csv_amount']:,.2f}, "
                       f"Calculated = {result['currency']} {result['transfer_amount']:,.2f}\n")

            original_amt = result.get('original_amount', '')
            if original_amt != '' and original_amt is not None:
                f.write("\n")
                f.write(f"Original Amount: {result['currency']} {original_amt:,.2f}\n")
                f.write(f"Original Calculation: {result.get('original_comments', '')}\n")
                if result.get('matches_original') == 'Y':
                    f.write(" Calculated amount matches ORIGINAL amount\n")
                else:
                    f.write(f"✗ MISMATCH: Original amount = {result['currency']} {original_amt:,.2f}, "
                           f"Calculated = {result['currency']} {result['transfer_amount']:,.2f}\n")

            f.write("=" * 100 + "\n\n")

        matches = sum(1 for r in all_results if r['matches_csv'])
        f.write("\n" + "=" * 100 + "\n")
        f.write("SUMMARY\n")
        f.write("=" * 100 + "\n")
        f.write(f"Total Transfers Processed: {len(all_results)}\n")
        f.write(f"Calculations Matching CSV: {matches}/{len(all_results)} ({100*matches/len(all_results):.1f}%)\n")
        f.write("=" * 100 + "\n")


def write_csv_output(all_results: List[Dict], output_path: str):
    """Write transfer results to CSV with separate debit/credit rows per transfer."""
    with open(output_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)

        writer.writerow([
            'Source File',
            'Transfer Group',
            'Type',
            'Contract ID',
            'Contract Name',
            'Initial Balance',
            'Final Balance',
            'Amount',
            'Currency',
            'Calculation Description',
            'Calculation Type',
            'Matches CSV',
            'Original Amount',
            'Original Calculation',
            'Matches Original'
        ])

        for result in all_results:
            original_amt = result.get('original_amount', '')
            original_amt_str = f"{original_amt:.2f}" if original_amt != '' and original_amt is not None else ''
            transfer_amt = result['transfer_amount']

            writer.writerow([
                result['source_file'],
                result['group'],
                'Debit',
                result['from_contract_id'],
                result['from_contract_name'],
                f"{result['from_initial_balance']:.2f}",
                f"{result['from_final_balance']:.2f}",
                f"-{transfer_amt:.2f}",
                result['currency'],
                result['calculation_description'],
                result['calculation_type'],
                'Yes' if result['matches_csv'] else 'No',
                f"-{original_amt_str}" if original_amt_str else '',
                result.get('original_comments', ''),
                result.get('matches_original', '')
            ])

            writer.writerow([
                result['source_file'],
                result['group'],
                'Credit',
                result['to_contract_id'],
                result['to_contract_name'],
                f"{result['to_initial_balance']:.2f}",
                f"{result['to_final_balance']:.2f}",
                f"{transfer_amt:.2f}",
                result['currency'],
                result['calculation_description'],
                result['calculation_type'],
                'Yes' if result['matches_csv'] else 'No',
                original_amt_str,
                result.get('original_comments', ''),
                result.get('matches_original', '')
            ])


def main():
    base_dir = os.path.dirname(os.path.abspath(__file__))
    input_dir = os.path.join(base_dir, 'input')
    output_dir = os.path.join(base_dir, 'output')
    originals_dir = os.path.join(base_dir, 'originals')

    print("=" * 100)
    print("LOADING ORIGINAL CSV FILES FOR COMPARISON")
    print("=" * 100)

    original_overrides_path = os.path.join(originals_dir, 'overrides.csv')
    original_transfers_path = os.path.join(originals_dir, 'transfers.csv')
    original_cross_recoupment_path = os.path.join(originals_dir, 'cross_recoupment.csv')

    original_overrides = {}
    original_transfers = {}
    original_cross_recoupment = {}

    if os.path.exists(original_overrides_path):
        original_overrides = read_original_csv(original_overrides_path)
        print(f" Loaded {len(original_overrides)} records from overrides.csv")

    if os.path.exists(original_transfers_path):
        original_transfers = read_original_csv(original_transfers_path)
        print(f" Loaded {len(original_transfers)} records from transfers.csv")

    if os.path.exists(original_cross_recoupment_path):
        original_cross_recoupment = read_original_csv(original_cross_recoupment_path)
        print(f" Loaded {len(original_cross_recoupment)} records from cross_recoupment.csv")

    print()

    csv_files = glob.glob(os.path.join(input_dir, '*.csv'))

    if not csv_files:
        print(f"No CSV files found in {input_dir}")
        return

    print("=" * 100)
    print(f"PROCESSING CSV FILES FROM: {input_dir}")
    print("=" * 100)
    print(f"Found {len(csv_files)} CSV file(s):\n")
    for csv_file in csv_files:
        print(f"  - {os.path.basename(csv_file)}")
    print()

    all_results = []
    results_by_file = {}

    for csv_file in csv_files:
        print("=" * 100)
        print(f"PROCESSING: {os.path.basename(csv_file)}")
        print("=" * 100)

        original_data = None
        if 'overrides' in os.path.basename(csv_file).lower():
            original_data = original_overrides
        elif 'transfers' in os.path.basename(csv_file).lower():
            original_data = original_transfers
        elif 'cross_recoupment' in os.path.basename(csv_file).lower():
            original_data = original_cross_recoupment

        transfers = process_csv_file(csv_file, original_data)
        print(f"Found {len(transfers)} transfers")

        file_results = []
        for transfer_data in transfers:
            result = process_transfer(transfer_data)
            all_results.append(result)
            file_results.append(result)

        results_by_file[csv_file] = file_results

    print("\n" + "=" * 100)
    print("WRITING OUTPUT FILES")
    print("=" * 100)

    for csv_file, file_results in results_by_file.items():
        input_basename = os.path.basename(csv_file)
        output_name = input_basename.replace('.csv', '_results.csv')
        csv_output_path = os.path.join(output_dir, output_name)

        write_csv_output(file_results, csv_output_path)
        print(f" CSV output written to: {csv_output_path}")

    report_path = os.path.join(output_dir, 'transfer_report_all.txt')
    write_detailed_report(all_results, report_path)
    print(f" Combined detailed report written to: {report_path}")

    matches = sum(1 for r in all_results if r['matches_csv'])
    print("\n" + "=" * 100)
    print("SUMMARY")
    print("=" * 100)
    print(f"Total Transfers Processed: {len(all_results)}")
    print(f"Calculations Matching CSV: {matches}/{len(all_results)} ({100*matches/len(all_results):.1f}%)")
    print("=" * 100)


if __name__ == "__main__":
    main()