#!/usr/bin/env python3
"""Daily check: GA warehouse orders that never got a WHIMS deduction.

Read-only. Written 2026-09-11 to watch the central numeric-part deduction bug for a week
(see memory note central-deduction-numeric-part-bug).

    python3 dev-documents/inventory-watch/check_missed_deductions.py [--since 2026-09-01]

Reports:
  1. whether the fix is live in the central ShipmentProcessorService
  2. every GA warehouse order line since --since with no WHIMS deduction; lines already
     handled on 11 Sept are labelled KNOWN so new misses stand out
  3. how many numeric-part central deductions exist (0 before the fix; >0 proves it works)
  4. deduction failures still visible in the central logs (only 2 days are kept)
"""
import argparse, collections, csv, datetime, glob, os, re
import pymysql

ROOT = '/home/whgoparts/public_html/whims-dev'
CENTRAL = '/home/centralgoparts/public_html'
OUT_DIR = os.path.join(ROOT, 'dev-documents/inventory-watch/missed-deductions')

# Missed on or before 11 Sept, already dealt with (stock zeroed; the open two need sourcing).
KNOWN = {
    '112-7324468-5747400': 'shipped; WHIMS zeroed by hand',
    '229755297':           'shipped; zeroed MISSED-DEDUCTION-2026-09-11',
    '111-8047277-2000225': 'shipped; zeroed MISSED-DEDUCTION-2026-09-11',
    '114-1469963-7519464': 'shipped; zeroed MISSED-DEDUCTION-2026-09-11',
    '113-9223336-4038637': 'open; unit already gone, needs sourcing',
    '113-5524594-3421831': 'open; unit already gone, needs sourcing',
}


def env():
    e = {}
    for l in open(os.path.join(ROOT, '.env')):
        l = l.strip()
        if l and not l.startswith('#') and '=' in l:
            k, v = l.split('=', 1)
            v = v.strip()
            if len(v) >= 2 and v[0] == v[-1] and v[0] in "\"'":
                v = v[1:-1]
            e[k.strip()] = v
    return e


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--since', default='2026-09-01', help='first order date to check (YYYY-MM-DD)')
    a = ap.parse_args()

    E = env()
    cn = lambda db: pymysql.connect(host='127.0.0.1', user=E['DB_USERNAME'], password=E['DB_PASSWORD'],
                                    database=db, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    nk = lambda s: re.sub(r'\s+', '', str(s)).upper()

    # 1. Is the fix live?
    src = open(os.path.join(CENTRAL, 'app/Services/ShipmentProcessorService.php')).read()
    if r"whims_(\d+)_(\d+)_(\d+|null)" in src:
        fix = 'NOT APPLIED (numeric part numbers are still skipped)'
    elif r"whims_(.+)_(\d+)_(\d+|null)" in src:
        fix = 'APPLIED'
    else:
        fix = 'UNKNOWN (extractWhimsLocationInfo changed; read it)'
    print(f"Fix in central ShipmentProcessorService: {fix}\n")

    # 2. Order lines with no deduction.
    S, W = cn('shipdev'), cn('whims_dev')
    with S.cursor() as c:
        c.execute("""select o.order_number onum, o.order_date od, oi.product_name nm, ifnull(o.shipment_status,'') ss,
                            exists(select 1 from shipments s where s.order_id=o.id) shipped
                     from orders o join order_items oi on oi.order_id=o.id
                     where o.user_id=16 and oi.product_name like 'STOCK [%%' and o.order_date>=%s
                       and ifnull(o.shipment_status,'') not in ('Refunded','Cancelled')""", (a.since,))
        lines = c.fetchall()
    with W.cursor() as c:
        c.execute("""select ifnull(reference_id,'') ref, ifnull(notes,'') notes from stock_history
                     where action_type='deduction' and created_at>=%s - interval 1 day""", (a.since,))
        seen = set()
        for r in c.fetchall():
            seen.add(r['ref'])
            m = re.search(r'Order\s+([A-Za-z0-9\-]+)\s*$', r['notes'].strip())  # reference_id is often truncated
            if m:
                seen.add(m.group(1))

    base = lambda o: re.sub(r'-\d$', '', o)
    out, cnt = [], collections.Counter()
    for r in lines:
        m = re.match(r'STOCK \[([^\]]+?)\s*-\s*([^\]]+)\]', r['nm'])
        if not m:
            continue
        loc, part = m.group(1).strip(), m.group(2).strip()
        state = 'shipped' if r['shipped'] else 'open'
        ok = r['onum'] in seen or base(r['onum']) in seen
        cnt[(state, 'deducted' if ok else 'MISSED')] += 1
        if ok:
            continue
        with W.cursor() as c:
            c.execute("""select s.quantity from stocks s join products p on p.id=s.product_id
                         join locations l on l.id=s.location_id where p.product_id=%s and l.location=%s""", (part, loc))
            q = c.fetchone()
        out.append({'order': r['onum'], 'order_date': str(r['od'])[:10], 'state': state, 'part': part, 'shelf': loc,
                    'numeric': 'yes' if re.fullmatch(r'\d+', part) else 'no',
                    'whims_qty_now': q['quantity'] if q else 'no row',
                    'known': KNOWN.get(r['onum'], 'NEW')})

    print(f"GA warehouse order lines since {a.since} (refunded/cancelled excluded):")
    for k in sorted(cnt):
        print(f"   {k[0]:<8} {k[1]:<9} {cnt[k]}")
    new = [o for o in out if o['known'] == 'NEW']
    print(f"\nLines with NO WHIMS deduction: {len(out)}  (NEW since 11 Sept: {len(new)})")
    if out:
        print(f"  {'order':<24}{'date':<12}{'state':<9}{'part':<16}{'shelf':<20}{'num':<5}{'qty now':<9}status")
        for o in sorted(out, key=lambda x: (x['known'] != 'NEW', x['order_date'])):
            print(f"  {o['order']:<24}{o['order_date']:<12}{o['state']:<9}{o['part']:<16}{o['shelf']:<20}"
                  f"{o['numeric']:<5}{str(o['whims_qty_now']):<9}{o['known']}")
    if new:
        print("\n  *** NEW misses: the unit is still on sale if 'qty now' > 0. Zero it, and source it if the order is open.")

    # 3. Proof the fix works: numeric-part central deductions.
    with W.cursor() as c:
        c.execute("""select h.created_at, p.product_id part, l.location loc, h.notes from stock_history h
                     join products p on p.id=h.product_id join locations l on l.id=h.location_id
                     where h.notes like 'Central Order Deduction%%' and p.product_id regexp '^[0-9]+$'
                     order by h.created_at""")
        nd = c.fetchall()
    print(f"\nCentral deductions for numeric part numbers, all time: {len(nd)}"
          + ("  (0 means the fix has not been exercised yet)" if not nd else ""))
    for r in nd[-10:]:
        print(f"   {r['created_at']}  {r['part']:<12}{r['loc']:<10}{r['notes'][-40:]}")

    # 4. Failures still in the central logs.
    pat = re.compile(r"WHIMS stock record not found|WHIMS insufficient stock|Could not extract WHIMS|Stock deduction failed")
    fails = []
    for f in sorted(glob.glob(os.path.join(CENTRAL, 'storage/logs/laravel-*.log'))):
        try:
            for l in open(f, errors='replace'):
                if pat.search(l):
                    fails.append(l.rstrip()[:200])
        except OSError:
            pass
    print(f"\nDeduction failures in the central logs (last ~2 days only): {len(fails)}")
    for l in fails:
        print('   ' + l)

    os.makedirs(OUT_DIR, exist_ok=True)
    p = os.path.join(OUT_DIR, f"missed_{datetime.date.today()}.csv")
    with open(p, 'w', newline='') as f:
        w = csv.DictWriter(f, fieldnames=['order', 'order_date', 'state', 'part', 'shelf', 'numeric', 'whims_qty_now', 'known'])
        w.writeheader()
        w.writerows(out)
    print(f"\nWritten: {p}")
    S.close(); W.close()


if __name__ == '__main__':
    main()