"""routes/cashier.py — cashier POS, cash events, wastage, outlet assignments."""
import sys, json, time, hashlib, hmac
from db import (app, q, run, get_db, today, now, _log_action, _int, _float, _str,
                csrf_protect, MANAGER_ROLES, ALL_ROLES, MPESA_SECRET,
                _client_ip, _get_csrf_token, hash_pw, check_pw, MAX_NOTE)
from auth import login_required, role_required, is_manager, is_gm, current_user
from services import get_cash_position, get_outlet_low_stock, _build_reconciliation, calc_expected_revenue, cost_per_unit, calc_payroll, calc_ingredient_cost
from flask import render_template, request, redirect, url_for, session, jsonify, flash

@app.route('/cashier')
@login_required
@role_required('general_manager','manager','cashier')
def cashier():
    user      = current_user()
    role      = session.get('role')
    d = today()
    outlets   = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    if role in MANAGER_ROLES:
        outlet_id = int(request.args.get('outlet_id') or outlets[0]['id'])
    else:
        # Cashier: use today's assignment; fall back to any assignment; last resort outlet 1
        assignment = q("SELECT outlet_id FROM outlet_assignments WHERE date=? AND cashier_id=? ORDER BY assigned_at DESC LIMIT 1",
                       (d, user['id']), one=True)
        if assignment:
            outlet_id = assignment['outlet_id']
        else:
            # Legacy fallback: use user's stored outlet_id if set
            outlet_id = user['outlet_id'] or (outlets[0]['id'] if outlets else 1)
    outlet_id = int(outlet_id)
    outlet    = dict(q("SELECT * FROM outlets WHERE id=?", (outlet_id,), one=True) or {})
    products  = [dict(r) for r in q("SELECT * FROM products WHERE active=1 ORDER BY category,name")]
    d = today()
    stock_at_outlet = {}
    for p in products:
        ti = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                  WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                  AND event_type IN ('OPENING_DISPATCH','TOPUP','RECEIPT_CORRECTION')""",(outlet_id,p['id'],d,d),one=True)['t']
        dispatched = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                          WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                          AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(outlet_id,p['id'],d,d),one=True)['t']
        wt = q("""SELECT COALESCE(SUM(qty_out),0) as t FROM stock_events
                  WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                  AND event_type='WASTAGE'""",(outlet_id,p['id'],d,d),one=True)['t']
        # Use confirmed receipt quantity if available
        confirmed_rcv = q("""SELECT COALESCE(SUM(qty_received),0) as t FROM outlet_receipts
                             WHERE outlet_id=? AND product_id=? AND received_at >= ? AND received_at < date(?, '+1 day')""",(outlet_id, p['id'], d, d), one=True)['t']
        last = q("""SELECT si.qty_remaining FROM submission_items si
                    JOIN cashier_submissions cs ON si.submission_id=cs.id
                    WHERE cs.outlet_id=? AND si.product_id=? AND cs.submitted_at >= ? AND cs.submitted_at < date(?, '+1 day')
                    ORDER BY cs.submitted_at DESC LIMIT 1""",(outlet_id,p['id'],d,d),one=True)
        remaining = last['qty_remaining'] if last else max(0, ti - wt)
        stock_at_outlet[p['id']] = {
            'total_in':        ti,
            'dispatched':      dispatched,
            'confirmed_rcv':   confirmed_rcv if confirmed_rcv else None,
            'discrepancy':     round(confirmed_rcv - dispatched, 2) if (confirmed_rcv and dispatched > 0) else None,
            'wastage':         wt,
            'remaining':       max(0, remaining),
            'sold':            max(0, ti - wt - (last['qty_remaining'] if last else 0))
        }
    outlet_receipts = [dict(r) for r in q("""
        SELECT or2.*, p.name as product_name, u.name as received_by_name
        FROM outlet_receipts or2
        JOIN products p ON or2.product_id=p.id
        LEFT JOIN users u ON or2.received_by=u.id
        WHERE or2.outlet_id=? AND or2.received_at >= ? AND or2.received_at < date(?, '+1 day')
        ORDER BY or2.received_at DESC""",(outlet_id, d, d))]
    submissions = [dict(r) for r in q("""SELECT cs.*, u.name as cashier_name FROM cashier_submissions cs
                       JOIN users u ON cs.cashier_id=u.id
                       WHERE cs.outlet_id=? AND cs.submitted_at >= ? AND cs.submitted_at < date(?, '+1 day')
                       ORDER BY cs.submitted_at DESC""",(outlet_id, d, d))]
    cash_events = [dict(r) for r in q("SELECT * FROM cash_events WHERE outlet_id=? AND recorded_at >= ? AND recorded_at < date(?, '+1 day') ORDER BY recorded_at",(outlet_id,d,d))]
    eod_drop    = q("SELECT amount FROM cash_events WHERE outlet_id=? AND recorded_at >= ? AND recorded_at < date(?, '+1 day') AND event_type='CASH_DROP' ORDER BY recorded_at DESC LIMIT 1",(outlet_id,d,d),one=True)
    outlets     = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    consumables = [dict(r) for r in q("SELECT id,name,unit FROM ingredients WHERE category='consumable' ORDER BY name")]
    low_stock   = get_outlet_low_stock(outlet_id, d)
    return render_template('cashier.html', user=user, outlet=outlet,
        outlets=outlets, products=products, stock_at_outlet=stock_at_outlet,
        submissions=submissions, cash_events=cash_events,
        eod_drop=eod_drop, today=d, role=role,
        outlet_receipts=outlet_receipts, consumables=consumables, low_stock=low_stock)

@app.route('/api/cashier/receive', methods=['POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def cashier_receive():
    data      = request.get_json()
    outlet_id = data.get('outlet_id')
    items     = data.get('items', {})
    ref       = _str(data.get('ref',''), 50)
    if not outlet_id: return jsonify({'error': 'outlet_id required'}), 400
    ts = now()
    d  = today()
    for pid, qty_received in items.items():
        qty_received = _int(qty_received)
        if qty_received <= 0: continue
        # Look up how much was dispatched for this product at this outlet today
        dispatched = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                          WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                          AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(outlet_id, pid, d, d), one=True)['t'] or 0
        run("""INSERT INTO outlet_receipts
               (outlet_id,product_id,qty_received,qty_dispatched,received_by,received_at,dispatch_ref)
               VALUES (?,?,?,?,?,?,?)""",
            (outlet_id, pid, qty_received, dispatched, session['user_id'], ts, ref))
        # Override stock to the confirmed received quantity (cashier's physical count)
        # by adjusting the stock_events so remaining = confirmed qty
        existing_in = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                            WHERE outlet_id=? AND product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day')
                            AND event_type='OPENING_DISPATCH'""",(outlet_id, pid, d, d), one=True)['t'] or 0
        if existing_in > 0 and qty_received != existing_in:
            # Insert a correction event so the effective stock matches received
            correction = qty_received - existing_in
            run("""INSERT INTO stock_events
                   (outlet_id,product_id,event_type,qty_in,qty_out,reference,recorded_by)
                   VALUES (?,?,?,?,?,?,?)""",
                (outlet_id, pid,
                 'RECEIPT_CORRECTION',
                 max(0, correction), max(0, -correction),
                 'Cashier confirmed receipt: ' + str(qty_received) + ' (dispatched: ' + str(int(existing_in)) + ')',
                 session['user_id']))
        elif existing_in == 0:
            # No dispatch recorded yet — just add a receipt event
            run("""INSERT INTO stock_events
                   (outlet_id,product_id,event_type,qty_in,reference,recorded_by)
                   VALUES (?,?,?,?,?,?)""",
                (outlet_id, pid, 'RECEIPT_CORRECTION', qty_received,
                 'Direct cashier receipt (no dispatch found)', session['user_id']))
    _log_action('cashier_receive', 'outlet_receipts', outlet_id,
                new_value={'items': {str(k): v for k, v in items.items()}, 'ref': ref})
    return jsonify({'ok':True,'received_at':ts})

@app.route('/api/cashier/submit', methods=['POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def cashier_submit():
    data      = request.get_json()
    outlet_id = data.get('outlet_id')
    cash      = max(0, _float(data.get('cash_declared', 0)))
    items     = data.get('items', {})
    if not outlet_id: return jsonify({'error': 'outlet_id required'}), 400
    ts = now()
    sub_id = run("INSERT INTO cashier_submissions (outlet_id,cashier_id,cash_declared,submitted_at) VALUES (?,?,?,?)",
                 (outlet_id, session['user_id'], cash, ts))
    for pid, remaining in items.items():
        run("INSERT INTO submission_items (submission_id,product_id,qty_remaining) VALUES (?,?,?)",
            (sub_id, pid, max(0, _int(remaining))))
    return jsonify({'ok':True,'sub_id':sub_id,'submitted_at':ts})

@app.route('/api/cashier/cash_event', methods=['POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def cash_event():
    data = request.get_json()
    if not data.get('outlet_id') or not data.get('event_type') or data.get('amount') is None:
        return jsonify({'error': 'Missing fields'}), 400
    run("INSERT INTO cash_events (outlet_id,event_type,amount,note,recorded_by) VALUES (?,?,?,?,?)",
        (data['outlet_id'],_str(data['event_type'],30),_float(data['amount']),_str(data.get('note',''),MAX_NOTE),session['user_id']))
    return jsonify({'ok':True})

@app.route('/api/cashier/wastage', methods=['POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def record_wastage():
    data       = request.get_json()
    outlet_id  = data.get('outlet_id')
    product_id = data.get('product_id')
    qty        = max(0, _float(data.get('qty', 0)))
    reason     = _str(data.get('reason', ''), MAX_NOTE)
    if not outlet_id or not product_id or qty <= 0: return jsonify({'error': 'Invalid data'}), 400
    wid = run("""INSERT INTO stock_events (outlet_id,product_id,event_type,qty_out,reason,recorded_by,timestamp)
           VALUES (?,?,'WASTAGE',?,?,?,?)""",(outlet_id, product_id, qty, reason, session['user_id'], now()))
    # Also write to waste_logs for structured reporting
    p = q("SELECT price FROM products WHERE id=?", (product_id,), one=True)
    cpu = cost_per_unit(product_id)
    run("INSERT INTO waste_logs (date,outlet_id,item_id,item_type,qty,reason,cost_estimate,recorded_by) VALUES (?,?,?,?,?,?,?,?)",
        (today(), outlet_id, product_id, 'product', qty, reason, round(cpu * qty, 2), session['user_id']))
    ledger_entry(product_id, 'WASTE', qty, 'product', 'stock_events', wid, reason)
    return jsonify({'ok': True})

@app.route('/api/cashier/requisition', methods=['POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def cashier_requisition():
    data      = request.get_json()
    outlet_id = data.get('outlet_id')
    items     = data.get('items', {})
    d = today()
    req_id = run("INSERT INTO requisitions (date,for_date,requested_by,req_type,status) VALUES (?,?,?,?,?)",
                 (d, d, session['user_id'], 'outlet', 'pending'))
    for ing_id, qty in items.items():
        run("INSERT INTO requisition_items (requisition_id,ingredient_id,qty_requested) VALUES (?,?,?)",
            (req_id, ing_id, max(0, _float(qty))))
    return jsonify({'ok':True,'req_id':req_id})

@app.route('/api/outlet_assignments', methods=['GET','POST','DELETE'])
@login_required
@role_required('general_manager','manager')
@csrf_protect
def api_outlet_assignments():
    d = request.args.get('date', today())
    if request.method == 'POST':
        data = request.get_json()
        cashier_id = data.get('cashier_id')
        outlet_id  = data.get('outlet_id')
        date_str   = _str(data.get('date', d), 10)
        if not cashier_id or not outlet_id: return jsonify({'error':'Missing fields'}), 400
        # Upsert — one cashier can only be assigned to one outlet per day
        run("DELETE FROM outlet_assignments WHERE date=? AND cashier_id=?",(date_str, cashier_id))
        run("INSERT INTO outlet_assignments (date,outlet_id,cashier_id,assigned_by) VALUES (?,?,?,?)",
            (date_str, outlet_id, cashier_id, session['user_id']))
        return jsonify({'ok':True})
    if request.method == 'DELETE':
        data = request.get_json() or {}
        run("DELETE FROM outlet_assignments WHERE date=? AND cashier_id=?",
            (_str(data.get('date', d),10), data.get('cashier_id')))
        return jsonify({'ok':True})
    # GET — return today's assignments
    rows = q("""SELECT oa.*, u.name as cashier_name, o.name as outlet_name
                FROM outlet_assignments oa
                JOIN users u ON oa.cashier_id=u.id
                JOIN outlets o ON oa.outlet_id=o.id
                WHERE oa.date=? ORDER BY o.name, u.name""", (d,))
    return jsonify([dict(r) for r in rows])

# ── Finance ───────────────────────────────────────────────────────────────────