#!/usr/bin/env python3
"""
One-time: insert the 15 December 2025 PAMS invoices that the central scraper
missed entirely. We use the xlsx data as truth (this was early-days PAMS
activity; scraper coverage was inconsistent).

Each inserted row goes into invoice_buy_prices with:
  - source_table = 'manual_catchup'
  - payment_status = 'paid' (or 'credit_applied' for credits) per the xlsx Date Paid
  - paid_via_payment_id linked to the historical payment batch

Run AFTER catchup_pams_payments.py (which creates the payment records).
"""
import openpyxl
import pymysql
from datetime import datetime

XLSX = '/home/argoparts/public_html/arapp/invoice-reconciliation/PAMS-Statement/DECEMBER TO APRIL STATEMENTS.xlsx'

DB = dict(
    host='127.0.0.1', port=3306, user='root',
    password=')V9IR<W:~4=HrqbL',
    database='goparts_ar', autocommit=False,
)

# 15 invoices that are in xlsx but completely missing from supplier_charges-pamsauto
MISSING_INVOICES = {
    '1925449','1925840','1926129','1926718','1928112','1928117','1928624',
    '1930057','1930443','1930820','1930822','1930824','1930825','1930826','1931098',
}

def main():
    # Read xlsx and collect data for the missing invoices
    wb = openpyxl.load_workbook(XLSX)
    ws = wb['PAMS AUTO']
    rows_to_insert = []
    for r in range(2, ws.max_row + 1):
        d, inv, amt, paid, dpaid, po, link = (ws.cell(row=r, column=c).value for c in range(1, 8))
        if not inv or str(inv) not in MISSING_INVOICES: continue
        rows_to_insert.append({
            'invoice_no': str(inv),
            'invoice_date': d.date().isoformat() if isinstance(d, datetime) else d,
            'amount': float(amt or 0),
            'date_paid': dpaid.date().isoformat() if isinstance(dpaid, datetime) else None,
            'po_number': str(po) if po else None,
        })

    print(f'Found {len(rows_to_insert)} of 15 expected missing invoices')

    conn = pymysql.connect(**DB)
    cur = conn.cursor(pymysql.cursors.DictCursor)

    # Find the matching payment for each Date Paid (Jan 28, 2026 in all cases for Dec invoices)
    cur.execute("""
        SELECT id, payment_date FROM payments
        WHERE supplier_group='PAMS' AND source='historical_catchup'
    """)
    payment_by_date = {r['payment_date'].isoformat(): r['id'] for r in cur.fetchall()}

    # Delete any prior insertions for these invoices (idempotent)
    cur.execute("""
        DELETE FROM invoice_buy_prices
        WHERE supplier_group='PAMS' AND source_table='manual_catchup'
    """)

    inserted = 0
    for idx, row in enumerate(rows_to_insert, start=1):
        is_credit = row['amount'] < 0
        payment_status = 'credit_applied' if is_credit else 'paid'
        pid = payment_by_date.get(row['date_paid'])

        cur.execute("""
            INSERT INTO invoice_buy_prices
            (source_table, source_id, supplier_group, invoice_no, invoice_date,
             po_number, qty, unit_price, line_total, invoice_total, transaction_type,
             payment_status, paid_date, paid_via_payment_id, created_at, updated_at)
            VALUES ('manual_catchup', %s, 'PAMS', %s, %s, %s, 1, %s, %s, %s,
                    'purchase', %s, %s, %s, NOW(), NOW())
        """, (
            idx,
            row['invoice_no'], row['invoice_date'], row['po_number'],
            abs(row['amount']) if not is_credit else row['amount'],
            row['amount'], row['amount'],
            payment_status, row['date_paid'], pid,
        ))
        inserted += 1
        print(f'  Inserted {"CREDIT" if is_credit else "INVOICE"} {row["invoice_no"]} PO {row["po_number"]} ${row["amount"]:.2f}')

    conn.commit()
    print(f'\nInserted {inserted} missing PAMS invoices, marked as paid/credit_applied')
    conn.close()


if __name__ == '__main__':
    main()