#!/usr/bin/env python3
"""
One-time backfill: set feed_price = statement amount for PAMS orders where
the parts catalog/recycler stock no longer has a price (parts get removed
after sale). Then re-reconciles affected statement lines as 'matched'.

Run after import_pams_ledger.py:
    python3 backfill_missing_feed_prices.py
"""
import pymysql

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

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

    # Find statement lines where reconciliation flagged price_mismatch
    # because the feed_buy_prices row(s) for that PO had feed_price = 0 or NULL.
    cur.execute("""
        SELECT
            l.id as line_id,
            l.po_number,
            l.amount as stmt_amount,
            l.feed_buy_price_id,
            l.type
        FROM supplier_statement_lines l
        WHERE l.supplier_group = 'PAMS'
          AND l.match_status = 'price_mismatch'
          AND (l.feed_price IS NULL OR l.feed_price <= 0.01)
          AND l.type = 'purchase'
    """)
    targets = cur.fetchall()
    print(f'Lines to backfill: {len(targets)}')

    backfilled = 0
    skipped = 0

    for line in targets:
        # Get all feed_buy_prices for this PO (PAMS only)
        cur.execute("""
            SELECT id, order_item_id, feed_price
            FROM feed_buy_prices
            WHERE external_order_id = %s AND supplier_id = 74
        """, (line['po_number'],))
        feed_rows = cur.fetchall()

        if not feed_rows:
            skipped += 1
            continue

        # Distribute statement amount across feed_buy_prices that have null/0 price
        # If only some are missing, only fill the missing ones with remainder
        existing_total = sum(float(r['feed_price']) for r in feed_rows if r['feed_price'] is not None and float(r['feed_price']) > 0)
        missing_rows = [r for r in feed_rows if r['feed_price'] is None or float(r['feed_price']) <= 0.01]

        if not missing_rows:
            skipped += 1
            continue

        stmt_amt = abs(float(line['stmt_amount']))
        remainder = stmt_amt - existing_total
        if remainder <= 0:
            # Statement amount is fully covered already by existing feed prices; skip
            skipped += 1
            continue

        per_item = round(remainder / len(missing_rows), 2)
        for r in missing_rows:
            cur.execute("""
                UPDATE feed_buy_prices
                SET feed_price = %s, updated_at = NOW()
                WHERE id = %s
            """, (per_item, r['id']))

        # Update the statement line: now matches
        new_feed_total = existing_total + (per_item * len(missing_rows))
        diff = round(stmt_amt - new_feed_total, 2)
        item_count = len(feed_rows)
        suffix = f' (sum of {item_count} items)' if item_count > 1 else ''
        if abs(diff) < 0.02:
            match_status = 'matched'
            match_notes = f'Price matches{suffix} (feed price backfilled from statement)'
        else:
            match_status = 'price_mismatch'
            match_notes = f'Statement: ${stmt_amt:.2f}, Feed: ${new_feed_total:.2f}{suffix}, Diff: ${diff:.2f}'

        cur.execute("""
            UPDATE supplier_statement_lines
            SET feed_price = %s,
                price_difference = %s,
                match_status = %s,
                match_notes = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (new_feed_total, diff, match_status, match_notes, line['line_id']))
        backfilled += 1

    # Update 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'Backfilled: {backfilled}')
    print(f'Skipped: {skipped}')
    conn.close()

if __name__ == '__main__':
    main()