#!/usr/bin/env python3
"""
One-time: clear the 8 mismatches caused by goparts scraper gaps (NO_SCRAPE +
PARTIAL_SCRAPE). For these, the central supplier_charges-pamsauto scraper
missed rows or invoices, so the statement is the truth. Set feed_price equal
to the statement amount and mark as matched. The 12 CONFIRMED real
mismatches (statement = PAMS invoice_amount, but ≠ feed) remain flagged.
"""
import pymysql

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

# (invoice_no, po_number) pairs from the 8 scraper-gap cases
SCRAPER_GAPS = [
    # NO_SCRAPE: 0 rows in supplier_charges-pamsauto
    ('1947525', '229649373'),
    ('1947602', '229649743'),
    ('1949711', '229651019'),
    ('1949723', '229651069'),
    ('1957633', '229656369'),
    # PARTIAL_SCRAPE: some rows missing in supplier_charges-pamsauto
    ('1944766', '229647587'),
    ('1948245', '229650029'),
    ('1948882', '229649962'),
]

def main():
    conn = pymysql.connect(**DB)
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cleared = 0

    for inv, po in SCRAPER_GAPS:
        cur.execute("""
            SELECT id, amount, feed_buy_price_id
            FROM supplier_statement_lines
            WHERE supplier_group = 'PAMS'
              AND invoice_no = %s
              AND po_number = %s
        """, (inv, po))
        rows = cur.fetchall()
        for r in rows:
            stmt_amt = abs(float(r['amount']))

            # Distribute statement amount across all feed_buy_prices rows for this PO
            cur.execute("""
                SELECT id FROM feed_buy_prices
                WHERE external_order_id = %s AND supplier_id = 74
            """, (po,))
            feed_rows = cur.fetchall()
            if feed_rows:
                per_item = round(stmt_amt / len(feed_rows), 2)
                for f in feed_rows:
                    cur.execute("""
                        UPDATE feed_buy_prices SET feed_price = %s, updated_at = NOW()
                        WHERE id = %s
                    """, (per_item, f['id']))
                feed_total = per_item * len(feed_rows)
                # Adjust last row to absorb rounding to exactly match stmt_amt
                drift = round(stmt_amt - feed_total, 2)
                if abs(drift) >= 0.01 and feed_rows:
                    cur.execute("""
                        UPDATE feed_buy_prices SET feed_price = feed_price + %s, updated_at = NOW()
                        WHERE id = %s
                    """, (drift, feed_rows[-1]['id']))
                feed_total = stmt_amt
            else:
                feed_total = stmt_amt  # no feed row to update — just heal the stmt line

            cur.execute("""
                UPDATE supplier_statement_lines
                SET feed_price = %s,
                    price_difference = 0,
                    match_status = 'matched',
                    match_notes = 'Cleared: goparts scraper gap, statement is authoritative',
                    updated_at = NOW()
                WHERE id = %s
            """, (feed_total, r['id']))
            cleared += 1
            print(f'  Cleared Inv {inv} / PO {po} → feed=${feed_total:.2f}')

    # Refresh statement counts
    cur.execute("""
        UPDATE supplier_statements s
        SET
            matched_count = (SELECT COUNT(*) FROM supplier_statement_lines WHERE statement_id = s.id AND match_status = 'matched'),
            price_mismatch_count = (SELECT COUNT(*) FROM supplier_statement_lines WHERE statement_id = s.id AND match_status = 'price_mismatch'),
            unmatched_count = (SELECT COUNT(*) FROM supplier_statement_lines WHERE statement_id = s.id AND match_status = 'not_found')
        WHERE supplier_group = 'PAMS' AND filename = 'HISTORICAL_LEDGER_DEC25_MAR26'
    """)

    conn.commit()
    print(f'Done. Cleared {cleared} scraper-gap lines.')
    conn.close()

if __name__ == '__main__':
    main()