"""routes/reports.py — reports, analytics, settings, orders, storekeeper, waiter."""
import sys, json, time, hashlib, hmac
from datetime import date, timedelta
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('/reports')
@login_required
@role_required('general_manager','manager')
def reports():
    user     = current_user()
    d        = today()
    products = q("SELECT * FROM products WHERE active=1 ORDER BY category,name")
    outlets  = q("SELECT * FROM outlets WHERE active=1")
    sales_data = []
    for p in products:
        total_sold = 0; total_rev = 0; total_wastage = 0
        for outlet in outlets:
            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')""",(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']
            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 0
            sold = max(0, ti - wt - remaining)
            total_sold += sold; total_rev += sold * p['price']; total_wastage += wt
        cpu = cost_per_unit(p['id'])
        margin_pct = round(((p['price'] - cpu) / p['price'] * 100) if p['price'] > 0 else 0, 1)
        wastage_pct = round((total_wastage / (total_sold + total_wastage) * 100) if (total_sold + total_wastage) > 0 else 0, 1)
        wastage_threshold = p['wastage_threshold_pct'] if 'wastage_threshold_pct' in p.keys() else 10
        wastage_alert = wastage_pct > (wastage_threshold or 10)
        sales_data.append({'product':dict(p),'sold':total_sold,'revenue':total_rev,
                           'cpu':cpu,'margin':p['price']-cpu,'margin_pct':margin_pct,
                           'wastage':total_wastage,'wastage_pct':wastage_pct,'wastage_alert':wastage_alert})
    sales_data.sort(key=lambda x: x['revenue'], reverse=True)
    return render_template('reports.html', user=user, sales_data=sales_data, today=d, outlets=outlets)

@app.route('/storekeeper')
@login_required
@role_required('general_manager','manager','storekeeper')
def storekeeper():
    user = current_user()
    ingredients = q("""SELECT i.*, COALESCE(s.quantity,0) as quantity FROM ingredients i
                       LEFT JOIN stock s ON i.id=s.ingredient_id ORDER BY i.category,i.name""")
    requisitions = q("""SELECT r.*, u.name as chef_name,
                               u2.name as reviewer_name, u2.role as reviewer_role
                        FROM requisitions r JOIN users u ON r.requested_by=u.id
                        LEFT JOIN users u2 ON u2.id=r.reviewed_by
                        WHERE r.status='pending'
                        ORDER BY r.created_at DESC LIMIT 100""")
    d = today()
    return render_template('storekeeper.html', user=user, ingredients=ingredients,
                           requisitions=requisitions, today=d)

@app.route('/api/sale', methods=['POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def record_sale():
    """Record an actual point-of-sale transaction."""
    data = request.get_json()
    outlet_id  = data.get('outlet_id')
    product_id = data.get('product_id')
    qty        = max(1, _int(data.get('qty', 1)))
    if not outlet_id or not product_id: return jsonify({'error':'Missing fields'}), 400
    run("INSERT INTO sale_events (outlet_id,product_id,qty,cashier_id,recorded_at) VALUES (?,?,?,?,?)",
        (outlet_id, product_id, qty, session['user_id'], now()))
    return jsonify({'ok':True})

@app.route('/api/finance/weekly_pnl')
@login_required
@role_required('general_manager','manager')
def api_weekly_pnl():
    """7-day P&L trend for the GM dashboard."""
    outlets = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    result = []
    for i in range(7):
        d = (date.today() - timedelta(days=6-i)).isoformat()
        rev  = sum(calc_expected_revenue(o['id'], d) for o in outlets)
        cost = calc_ingredient_cost(d)
        other = q("SELECT COALESCE(SUM(amount+tx_cost),0) as t FROM expenses WHERE date=?", (d,), one=True)['t']
        result.append({'date': d, 'revenue': round(rev), 'cost': round(cost + other), 'profit': round(rev - cost - other)})
    return jsonify(result)

@app.route('/api/wastage/summary')
@login_required
@role_required('general_manager','manager')
def api_wastage_summary():
    """Wastage by product for the last N days."""
    days = min(30, max(1, _int(request.args.get('days', 7))))
    since = (date.today() - timedelta(days=days)).isoformat()
    rows = q("""SELECT se.product_id, p.name, p.price,
                       COALESCE(p.wastage_threshold_pct, 10) as threshold,
                       SUM(se.qty_out) as total_wastage,
                       SUM(si2.qty_in) as total_dispatched
                FROM stock_events se
                JOIN products p ON se.product_id=p.id
                LEFT JOIN (
                    SELECT product_id, SUM(qty_in) as qty_in
                    FROM stock_events
                    WHERE event_type IN ('OPENING_DISPATCH','TOPUP') AND timestamp>=?
                    GROUP BY product_id
                ) si2 ON si2.product_id=se.product_id
                WHERE se.event_type='WASTAGE' AND se.timestamp>=?
                GROUP BY se.product_id ORDER BY total_wastage DESC""", (since, since))
    result = []
    for r in rows:
        wasted = r['total_wastage'] or 0
        dispatched = r['total_dispatched'] or 1
        pct = round(wasted / dispatched * 100, 1)
        alert = pct > (r['threshold'] or 10)
        cost = cost_per_unit(r['product_id']) * wasted
        result.append({'product': r['name'], 'product_id': r['product_id'],
                       'wastage': wasted, 'dispatched': dispatched, 'pct': pct,
                       'threshold': r['threshold'], 'alert': alert,
                       'cost': round(cost), 'revenue_lost': round(wasted * r['price'])})
    return jsonify(result)

@app.route('/api/shifts', methods=['GET','POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def api_shifts():
    if request.method == 'POST':
        data = request.get_json()
        action = data.get('action')
        outlet_id = data.get('outlet_id')
        if action == 'start':
            shift_type = _str(data.get('shift_type','morning'), 20)
            sid = run("INSERT INTO shifts (outlet_id,cashier_id,shift_type,started_at) VALUES (?,?,?,?)",
                      (outlet_id, session['user_id'], shift_type, now()))
            return jsonify({'ok':True,'shift_id':sid})
        elif action == 'end':
            shift_id = data.get('shift_id')
            note = _str(data.get('handover_note',''), MAX_NOTE)
            run("UPDATE shifts SET ended_at=?,handover_note=? WHERE id=? AND cashier_id=?",
                (now(), note, shift_id, session['user_id']))
            return jsonify({'ok':True})
    outlet_id = request.args.get('outlet_id', type=int)
    d = today()
    shifts = q("""SELECT s.*, u.name as cashier_name FROM shifts s
                  JOIN users u ON s.cashier_id=u.id
                  WHERE s.outlet_id=? AND s.started_at >= ? AND s.started_at < date(?, '+1 day')
                  ORDER BY s.started_at DESC""",(outlet_id, d, d))
    result = []
    for s in shifts:
        row = dict(s)
        # Attach clock-in and clock-out times for this cashier today
        clock_in_ev  = q("""SELECT recorded_at FROM clock_events
                             WHERE user_id=? AND event_type='CLOCK_IN' AND recorded_at >= ? AND recorded_at < date(?, '+1 day')
                             ORDER BY recorded_at ASC LIMIT 1""", (s['cashier_id'], d, d), one=True)
        clock_out_ev = q("""SELECT recorded_at FROM clock_events
                             WHERE user_id=? AND event_type='CLOCK_OUT' AND recorded_at >= ? AND recorded_at < date(?, '+1 day')
                             ORDER BY recorded_at DESC LIMIT 1""", (s['cashier_id'], d, d), one=True)
        row['clock_in']  = clock_in_ev['recorded_at']  if clock_in_ev  else None
        row['clock_out'] = clock_out_ev['recorded_at'] if clock_out_ev else None
        result.append(row)
    return jsonify(result)

@app.route('/analytics')
@login_required
@role_required('general_manager','manager')
def analytics():
    user = current_user()
    return render_template('analytics.html', user=user, role=session.get('role'))

@app.route('/api/orders_view')
@login_required
@role_required('general_manager','manager')
def orders_view():
    """Manager read-only order monitor page."""
    user = current_user()
    outlets = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    chefs   = [dict(r) for r in q("SELECT id,name,line FROM users WHERE role='chef' AND active=1 ORDER BY name")]
    return render_template('orders_monitor.html', user=user, role=session.get('role'),
                           outlets=outlets, chefs=chefs, today=today())

@app.route('/api/settings', methods=['GET','POST'])
@login_required
@csrf_protect
def api_settings():
    if request.method == 'POST':
        if session.get('role') not in MANAGER_ROLES: return jsonify({'error':'Unauthorized'}),403
        d = request.get_json()
        for k,v in d.items():
            k = _str(k, 50); v = _str(v, 500)
            exists = q("SELECT key FROM settings WHERE key=?", (k,), one=True)
            if exists: run("UPDATE settings SET value=? WHERE key=?",(v,k))
            else:      run("INSERT INTO settings (key,value) VALUES (?,?)",(k,v))
        return jsonify({'ok':True})
    settings = q("SELECT * FROM settings")
    return jsonify({r['key']:r['value'] for r in settings})

# M-Pesa IPN — no auth required (called by Safaricom servers)
@app.route('/api/mpesa/ipn', methods=['POST'])
def mpesa_ipn():
    """M-Pesa IPN endpoint.  Requires HMAC-SHA256 signature from Safaricom.
    Set MPESA_SECRET env var to the shared secret configured in Daraja portal.
    If MPESA_SECRET is unset the endpoint is disabled — return 503 so Safaricom
    retries once the secret is configured.
    """
    import sys
    if not MPESA_SECRET:
        print("MPESA IPN called but MPESA_SECRET is not configured — rejecting.", file=sys.stderr)
        return jsonify({'ResultCode': 1, 'ResultDesc': 'IPN not configured'}), 503

    # Validate Safaricom SecurityCredential / HMAC header
    # Safaricom sends the signature in X-Safaricom-Signature as HMAC-SHA256 of the raw body
    provided_sig = request.headers.get('X-Safaricom-Signature', '')
    raw_body     = request.get_data()
    expected_sig = hmac.new(MPESA_SECRET.encode(), raw_body, hashlib.sha256).hexdigest()
    if not provided_sig or not hmac.compare_digest(provided_sig, expected_sig):
        print(f"MPESA IPN signature mismatch from {_client_ip()}", file=sys.stderr)
        return jsonify({'ResultCode': 1, 'ResultDesc': 'Signature mismatch'}), 401

    data   = request.get_json(force=True) or {}
    phone  = _str(data.get('MSISDN') or data.get('phone',''), 20)
    amount = _float(data.get('TransAmount') or data.get('amount',0))
    ref    = _str(data.get('TransID') or data.get('ref',''), 30)
    till   = _str(data.get('BusinessShortCode') or data.get('till',''), 20)
    outlet = q("SELECT id FROM outlets WHERE till_number=?",(str(till),),one=True)
    outlet_id = outlet['id'] if outlet else None
    # Deduplication: if mpesa_ref already exists, accept silently without double-recording
    if ref:
        existing = q("SELECT id FROM mpesa_transactions WHERE mpesa_ref=?", (ref,), one=True)
        if existing:
            return jsonify({'ResultCode':0,'ResultDesc':'Accepted (duplicate ignored)'})
    try:
        run("INSERT INTO mpesa_transactions (outlet_id,phone,amount,mpesa_ref,till_number) VALUES (?,?,?,?,?)",
            (outlet_id,phone,amount,ref,till))
    except Exception as e:
        print(f"MPESA IPN DB write failed: {e}", file=sys.stderr)
        return jsonify({'ResultCode':1,'ResultDesc':'Internal error'}), 500
    return jsonify({'ResultCode':0,'ResultDesc':'Accepted'})

@app.route('/api/requisitions/<int:req_id>/items')
@login_required
def api_req_items(req_id):
    items = q("""SELECT ri.qty_requested, i.name as ing_name, i.unit, i.cost_per_unit
                 FROM requisition_items ri JOIN ingredients i ON ri.ingredient_id=i.id
                 WHERE ri.requisition_id=?""",(req_id,))
    return jsonify([dict(i) for i in items])

@app.route('/api/outlet/low_stock')
@login_required
def api_low_stock():
    outlet_id = request.args.get('outlet_id', type=int)
    d = today()
    if not outlet_id: return jsonify({'error':'outlet_id required'}),400
    return jsonify(get_outlet_low_stock(outlet_id, d))

@app.route('/api/activity_logs')
@login_required
@role_required('general_manager', 'manager')
def api_activity_logs():
    """Return recent activity/audit log entries for managers. Paginated."""
    page   = max(1, _int(request.args.get('page', 1)))
    limit  = min(100, max(10, _int(request.args.get('limit', 50))))
    offset = (page - 1) * limit
    rows   = q("""SELECT al.*, u.name as user_name
                  FROM activity_logs al
                  LEFT JOIN users u ON al.user_id=u.id
                  ORDER BY al.recorded_at DESC
                  LIMIT ? OFFSET ?""", (limit, offset))
    total  = q("SELECT COUNT(*) as c FROM activity_logs", one=True)['c']
    return jsonify({'logs': [dict(r) for r in rows], 'total': total, 'page': page, 'limit': limit})

@app.route('/api/reconciliation', methods=['GET','POST'])
@login_required
@role_required('general_manager','manager','cashier')
@csrf_protect
def api_reconciliation():
    """
    Daily outlet reconciliation:
    Opening Stock + Received - Remaining = Expected Sold
    Compare against Cash + MPesa collected.
    """
    d         = request.args.get('date', today())
    outlet_id = request.args.get('outlet_id', type=int)
    if not outlet_id:
        return jsonify({'error': 'outlet_id required'}), 400

    if request.method == 'POST':
        # Lock/submit reconciliation for the day
        data  = request.get_json() or {}
        notes = _str(data.get('notes', ''), MAX_NOTE)
        existing = q("SELECT id FROM daily_reconciliation WHERE date=? AND outlet_id=?", (d, outlet_id), one=True)
        if existing and existing['is_locked']:
            return jsonify({'error': 'Reconciliation already locked for this day'}), 400
        # Compute figures
        rec = _build_reconciliation(outlet_id, d)
        if existing:
            run("""UPDATE daily_reconciliation
                   SET opening_stock=?,received_stock=?,remaining_stock=?,expected_sold=?,
                       cash_declared=?,mpesa_total=?,total_collected=?,variance=?,
                       is_locked=1,locked_by=?,locked_at=?,notes=?
                   WHERE id=?""",
                (rec['opening_stock'], rec['received_stock'], rec['remaining_stock'],
                 rec['expected_sold'], rec['cash_declared'], rec['mpesa_total'],
                 rec['total_collected'], rec['variance'],
                 session['user_id'], now(), notes, existing['id']))
        else:
            run("""INSERT INTO daily_reconciliation
                   (date,outlet_id,opening_stock,received_stock,remaining_stock,expected_sold,
                    cash_declared,mpesa_total,total_collected,variance,is_locked,locked_by,locked_at,notes)
                   VALUES (?,?,?,?,?,?,?,?,?,?,1,?,?,?)""",
                (d, outlet_id, rec['opening_stock'], rec['received_stock'], rec['remaining_stock'],
                 rec['expected_sold'], rec['cash_declared'], rec['mpesa_total'],
                 rec['total_collected'], rec['variance'], session['user_id'], now(), notes))
        _log_action('reconciliation_locked', 'daily_reconciliation', outlet_id,
                    new_value={'date': d, 'variance': rec['variance']})
        return jsonify({'ok': True, **rec})

    # GET — return live reconciliation figures
    rec = _build_reconciliation(outlet_id, d)
    locked = q("SELECT * FROM daily_reconciliation WHERE date=? AND outlet_id=?", (d, outlet_id), one=True)
    if locked:
        rec['is_locked']  = bool(locked['is_locked'])
        rec['locked_at']  = locked['locked_at']
        rec['notes']      = locked['notes']
    return jsonify(rec)

@app.route('/api/profit_engine')
@login_required
@role_required('general_manager','manager')
def api_profit_engine():
    """Simple daily profit engine: Revenue - Direct Costs - Operating Expenses."""
    d = request.args.get('date', today())
    outlets = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]

    # Revenue
    cash_rev = mpesa_rev = 0.0
    for outlet in outlets:
        last_sub = q("SELECT cash_declared FROM cashier_submissions WHERE outlet_id=? AND submitted_at >= ? AND submitted_at < date(?, '+1 day') ORDER BY submitted_at DESC LIMIT 1",(outlet['id'], d, d), one=True)
        cash_rev += last_sub['cash_declared'] if last_sub else 0
        mpesa_rev += q("SELECT COALESCE(SUM(amount),0) as t FROM outlet_mpesa WHERE outlet_id=? AND recorded_at >= ? AND recorded_at < date(?, '+1 day')",(outlet['id'], d, d), one=True)['t']
        mpesa_rev += q("SELECT COALESCE(SUM(amount),0) as t FROM mpesa_transactions WHERE outlet_id=? AND created_at >= ? AND created_at < date(?, '+1 day')",(outlet['id'], d, d), one=True)['t']
    total_revenue = cash_rev + mpesa_rev

    # Direct costs: ingredient usage (based on production + approved requisitions)
    ing_cost = calc_ingredient_cost(d)
    # Also add ingredient purchases today
    purchase_cost = q("SELECT COALESCE(SUM(amount+tx_cost),0) as t FROM ingredient_receipts WHERE date=?", (d,), one=True)['t']

    # Operating expenses by category
    expenses = q("""SELECT category, SUM(amount+tx_cost) as total
                    FROM expenses WHERE date=? GROUP BY category ORDER BY total DESC""", (d,))
    op_expenses = {e['category']: round(e['total'], 2) for e in expenses}
    total_op_expenses = sum(op_expenses.values())

    # Waste cost
    waste_cost = q("SELECT COALESCE(SUM(cost_estimate),0) as t FROM waste_logs WHERE date=?", (d,), one=True)['t']

    gross_profit = total_revenue - ing_cost - purchase_cost - waste_cost
    net_profit   = gross_profit - total_op_expenses

    return jsonify({
        'date': d,
        'revenue': {'cash': round(cash_rev,2), 'mpesa': round(mpesa_rev,2), 'total': round(total_revenue,2)},
        'direct_costs': {'ingredient_usage': round(ing_cost,2), 'purchases': round(purchase_cost,2), 'waste': round(waste_cost,2)},
        'gross_profit': round(gross_profit, 2),
        'operating_expenses': op_expenses,
        'total_operating_expenses': round(total_op_expenses, 2),
        'net_profit': round(net_profit, 2),
    })

@app.route('/api/expense_categories', methods=['GET','POST','DELETE'])
@login_required
@role_required('general_manager','manager')
@csrf_protect
def api_expense_categories():
    if request.method == 'POST':
        data = request.get_json() or {}
        name = _str(data.get('name',''), 60).strip()
        if not name: return jsonify({'error': 'name required'}), 400
        run("INSERT OR IGNORE INTO expense_categories (name) VALUES (?)", (name,))
        return jsonify({'ok': True})
    if request.method == 'DELETE':
        name = _str(request.args.get('name',''), 60)
        row  = q("SELECT is_default FROM expense_categories WHERE name=?", (name,), one=True)
        if row and row['is_default'] and session.get('role') != 'general_manager':
            return jsonify({'error': 'Only GM can delete default categories'}), 403
        run("DELETE FROM expense_categories WHERE name=?", (name,))
        return jsonify({'ok': True})
    cats = q("SELECT name, is_default FROM expense_categories ORDER BY is_default DESC, name")
    return jsonify([dict(c) for c in cats])

@app.route('/api/waste_summary')
@login_required
@role_required('general_manager','manager')
def api_waste_summary_v2():
    """Structured waste summary from waste_logs table."""
    days  = min(30, max(1, _int(request.args.get('days', 7))))
    since = (date.today() - timedelta(days=days)).isoformat()
    rows  = q("""SELECT wl.date, wl.qty, wl.reason, wl.cost_estimate,
                        p.name as product_name, p.price,
                        o.name as outlet_name
                 FROM waste_logs wl
                 JOIN products p ON wl.item_id=p.id
                 LEFT JOIN outlets o ON wl.outlet_id=o.id
                 WHERE wl.date>=? AND wl.item_type='product'
                 ORDER BY wl.recorded_at DESC LIMIT 200""", (since,))
    total_cost = sum(r['cost_estimate'] or 0 for r in rows)
    return jsonify({'items': [dict(r) for r in rows], 'total_cost': round(total_cost, 2), 'days': days})

@app.route('/api/cashier/history')
@login_required
@role_required('general_manager','manager','cashier')
def api_cashier_history():
    outlet_id = request.args.get('outlet_id', type=int)
    days      = min(365, max(1, _int(request.args.get('days', 30))))
    if not outlet_id: return jsonify({'error':'outlet_id required'}), 400
    since = (date.today() - timedelta(days=days)).isoformat()
    subs = q("""SELECT cs.id, cs.submitted_at, cs.cash_declared,
                       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 >= ?
                ORDER BY cs.submitted_at DESC LIMIT 200""", (outlet_id, since))
    result = []
    for s in subs:
        items = q("""SELECT si.qty_remaining, p.name as product_name
                     FROM submission_items si JOIN products p ON si.product_id=p.id
                     WHERE si.submission_id=?""", (s['id'],))
        result.append({**dict(s), 'items': [dict(i) for i in items]})
    return jsonify(result)

@app.route('/api/dispatch/history')
@login_required
@role_required('general_manager','manager')
def api_dispatch_history():
    outlet_id = request.args.get('outlet_id', type=int)
    days      = min(365, max(1, _int(request.args.get('days', 7))))
    since = (date.today() - timedelta(days=days)).isoformat()
    sql = """SELECT se.timestamp, se.event_type, se.qty_in, se.reference,
                    p.name as product_name, o.name as outlet_name, u.name as by_name
             FROM stock_events se
             JOIN products p ON se.product_id=p.id
             JOIN outlets o ON se.outlet_id=o.id
             LEFT JOIN users u ON se.recorded_by=u.id
             WHERE se.event_type IN ('OPENING_DISPATCH','TOPUP')
             AND se.timestamp >= ?"""
    args = [since]
    if outlet_id:
        sql += " AND se.outlet_id=?"
        args.append(outlet_id)
    page  = max(1, _int(request.args.get('page', 1)))
    limit = min(200, max(10, _int(request.args.get('limit', 100))))
    offset = (page - 1) * limit
    sql += " ORDER BY se.timestamp DESC LIMIT ? OFFSET ?"
    args.extend([limit, offset])
    rows = q(sql, args)
    total = q("SELECT COUNT(*) as c FROM stock_events WHERE event_type IN ('OPENING_DISPATCH','TOPUP')", one=True)['c']
    return jsonify({'rows': [dict(r) for r in rows], 'total': total, 'page': page, 'limit': limit})

@app.route('/api/analytics')
@login_required
@role_required('general_manager','manager')
def api_analytics():
    days = min(90, max(1, _int(request.args.get('days', 14))))
    since = (date.today() - timedelta(days=days)).isoformat()
    outlets = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    products = q("SELECT * FROM products WHERE active=1 ORDER BY category,name")
    daily_rev = []
    for i in range(days):
        d = (date.today() - timedelta(days=days-1-i)).isoformat()
        total = sum(calc_expected_revenue(o['id'], d) for o in outlets)
        daily_rev.append({'date': d, 'revenue': round(total)})
    product_sales = []
    end_date = date.today().isoformat()
    for p in products:
        total_sold = 0; total_rev = 0
        for o in outlets:
            ti = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                      WHERE outlet_id=? AND product_id=? AND timestamp>=?
                      AND event_type IN ('OPENING_DISPATCH','TOPUP')""",
                   (o['id'], p['id'], since), 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 event_type='WASTAGE'""",
                   (o['id'], p['id'], since), one=True)['t']
            # FIX: scope remaining to end_date only (not pulling today's count into all historical days)
            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""",
                     (o['id'], p['id'], end_date, end_date), one=True)
            remaining = last['qty_remaining'] if last else 0
            sold = max(0, ti - wt - remaining)
            total_sold += sold
            total_rev  += sold * p['price']
        if total_sold > 0:
            cpu = cost_per_unit(p['id'])
            product_sales.append({'name': p['name'], 'category': p['category'],
                                   'sold': total_sold, 'revenue': round(total_rev),
                                   'margin_pct': round((p['price'] - cpu) / p['price'] * 100, 1) if p['price'] > 0 else 0})
    product_sales.sort(key=lambda x: x['revenue'], reverse=True)
    return jsonify({'daily_revenue': daily_rev, 'product_sales': product_sales[:20]})

@app.route('/api/clock', methods=['POST'])
@login_required
@csrf_protect
def api_clock():
    data       = request.get_json() or {}
    event_type = _str(data.get('event_type','CLOCK_IN'), 20)
    if event_type not in ('CLOCK_IN','CLOCK_OUT'): return jsonify({'error':'Invalid type'}), 400
    run("INSERT INTO clock_events (user_id, event_type, recorded_at) VALUES (?,?,?)",
        (session['user_id'], event_type, now()))
    return jsonify({'ok': True, 'recorded_at': now()})

@app.route('/api/my_requisitions')
@login_required
def api_my_requisitions():
    """Chef/cashier: see status of their own recent requisitions."""
    days = min(30, max(1, _int(request.args.get('days', 7))))
    since = (date.today() - timedelta(days=days)).isoformat()
    rows = q("""SELECT r.id, r.req_type, r.for_date, r.status,
                       r.created_at, r.reviewed_at, r.review_note,
                       u2.name as reviewer_name,
                       GROUP_CONCAT(i.name || ' × ' || CAST(CAST(ri.qty_requested AS INTEGER) AS TEXT), ', ') as items
                FROM requisitions r
                LEFT JOIN requisition_items ri ON ri.requisition_id=r.id
                LEFT JOIN ingredients i ON i.id=ri.ingredient_id
                LEFT JOIN users u2 ON u2.id=r.reviewed_by
                WHERE r.requested_by=? AND r.created_at>=?
                GROUP BY r.id ORDER BY r.created_at DESC LIMIT 30""",
              (session['user_id'], since))
    return jsonify([dict(r) for r in rows])

@app.route('/api/production/chef_log')
@login_required
@role_required('general_manager','manager')
def api_chef_log():
    """Per-chef breakdown for the manager's production view. No portions_made — uses finished only."""
    d = request.args.get('date', today())
    rows = q("""SELECT pr.finished, pr.wastage, pr.expected_output, pr.recorded_at,
                       COALESCE(pr.slot_3am,0)  as slot_3am,
                       COALESCE(pr.slot_7am,0)  as slot_7am,
                       COALESCE(pr.slot_11am,0) as slot_11am,
                       COALESCE(pr.slot_3pm,0)  as slot_3pm,
                       COALESCE(pr.slot_7pm,0)  as slot_7pm,
                       u.name as chef_name, p.name as product_name
                FROM production_records pr
                JOIN users u ON pr.chef_id=u.id
                JOIN products p ON pr.product_id=p.id
                WHERE pr.date=?
                ORDER BY u.name, p.name""", (d,))
    return jsonify([dict(r) for r in rows])

@app.route('/api/requisitions/pending')
@login_required
@role_required('general_manager','manager','chef')
def api_pending_requisitions():
    req_type = request.args.get('type', '')
    if req_type:
        rows = q("""SELECT r.id, r.req_type, r.created_at, u.name as chef_name,
                           GROUP_CONCAT(i.name||' x'||CAST(ri.qty_requested as INTEGER), ', ') as items
                    FROM requisitions r
                    JOIN users u ON r.requested_by=u.id
                    LEFT JOIN requisition_items ri ON ri.requisition_id=r.id
                    LEFT JOIN ingredients i ON i.id=ri.ingredient_id
                    WHERE r.status='pending' AND r.req_type=?
                    GROUP BY r.id ORDER BY r.created_at DESC LIMIT 50""", (req_type,))
    else:
        rows = q("""SELECT r.id, r.req_type, r.for_date, r.created_at, u.name as chef_name,
                           GROUP_CONCAT(i.name||' x'||CAST(ri.qty_requested as INTEGER), ', ') as items
                    FROM requisitions r
                    JOIN users u ON r.requested_by=u.id
                    LEFT JOIN requisition_items ri ON ri.requisition_id=r.id
                    LEFT JOIN ingredients i ON i.id=ri.ingredient_id
                    WHERE r.status='pending'
                    GROUP BY r.id ORDER BY r.created_at DESC LIMIT 50""")
    return jsonify([dict(r) for r in rows])

@app.route('/api/store/receipts')
@login_required
@role_required('general_manager','manager','storekeeper')
def api_store_receipts():
    """Return recent stock receipts for the receipt log."""
    rows = q("""SELECT ir.*, i.name as ing_name, i.unit as ing_unit
                FROM ingredient_receipts ir
                LEFT JOIN ingredients i ON i.id=ir.ingredient_id
                ORDER BY ir.created_at DESC LIMIT 100""")
    result = []
    for r in rows:
        row = dict(r)
        # Compute unit price for display if quantity is stored
        qty = row.get('quantity') or 0
        amt = row.get('amount') or 0
        row['unit_price'] = round(amt / qty, 2) if qty > 0 else 0
        result.append(row)
    return jsonify(result)

# ── Category Management ───────────────────────────────────────────────────────
@app.route('/api/product_categories', methods=['GET'])
@login_required
@role_required(*MANAGER_ROLES)
def api_product_categories_get():
    """Return all distinct product categories (static + from DB)."""
    static_cats = ['Burgers & Hotdogs','Chicken','Pasta','Staples','Potatoes','Vegetables','Bread & Snacks','Sides','Beverages']
    db_cats = [row[0] for row in q("SELECT DISTINCT category FROM products WHERE active=1 ORDER BY category")]
    custom = [row[0] for row in q("SELECT DISTINCT value FROM settings WHERE key LIKE 'custom_product_cat:%' ORDER BY value")]
    all_cats = sorted(set(static_cats + db_cats + custom))
    return jsonify({'categories': all_cats, 'static': static_cats})

@app.route('/api/product_categories', methods=['POST'])
@login_required
@role_required(*MANAGER_ROLES)
@csrf_protect
def api_product_categories_add():
    """Add a new product category (both managers can do this)."""
    data = request.get_json() or {}
    name = _str(data.get('name',''), 60).strip()
    if not name:
        return jsonify({'error': 'Category name required'}), 400
    key = 'custom_product_cat:' + name.lower().replace(' ', '_')
    run("INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)", (key, name))
    return jsonify({'ok': True, 'name': name})

@app.route('/api/product_categories/<path:name>', methods=['DELETE'])
@login_required
@csrf_protect
def api_product_categories_delete(name):
    """Delete a product category — only GM can delete."""
    if session.get('role') != 'general_manager':
        return jsonify({'error': 'Only the General Manager can delete categories'}), 403
    # Prevent deleting if products are still using it
    count = q("SELECT COUNT(*) as c FROM products WHERE category=? AND active=1", (name,))
    if count and count[0]['c'] > 0:
        return jsonify({'error': f'Cannot delete: {count[0]["c"]} product(s) still use this category'}), 400
    key = 'custom_product_cat:' + name.lower().replace(' ', '_')
    run("DELETE FROM settings WHERE key=?", (key,))
    return jsonify({'ok': True})

@app.route('/api/ingredient_categories', methods=['GET'])
@login_required
@role_required(*MANAGER_ROLES)
@csrf_protect
def api_ingredient_categories_get():
    """Return all distinct ingredient categories (static + from DB + custom)."""
    static_cats = ['ingredient', 'consumable']
    db_cats = [row[0] for row in q("SELECT DISTINCT category FROM ingredients ORDER BY category")]
    custom = [row[0] for row in q("SELECT DISTINCT value FROM settings WHERE key LIKE 'custom_ing_cat:%' ORDER BY value")]
    all_cats = sorted(set(static_cats + db_cats + custom))
    return jsonify({'categories': all_cats, 'static': static_cats})

@app.route('/api/ingredient_categories', methods=['POST'])
@login_required
@role_required(*MANAGER_ROLES)
@csrf_protect
def api_ingredient_categories_add():
    """Add a new ingredient category (both managers can do this)."""
    data = request.get_json() or {}
    name = _str(data.get('name',''), 60).strip()
    if not name:
        return jsonify({'error': 'Category name required'}), 400
    key = 'custom_ing_cat:' + name.lower().replace(' ', '_')
    run("INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)", (key, name))
    return jsonify({'ok': True, 'name': name})

@app.route('/api/ingredient_categories/<path:name>', methods=['DELETE'])
@login_required
@csrf_protect
def api_ingredient_categories_delete(name):
    """Delete an ingredient category — only GM can delete."""
    if session.get('role') != 'general_manager':
        return jsonify({'error': 'Only the General Manager can delete categories'}), 403
    # Prevent deleting built-in categories
    if name in ('ingredient', 'consumable'):
        return jsonify({'error': 'Cannot delete built-in categories'}), 400
    # Prevent deleting if ingredients are still using it
    count = q("SELECT COUNT(*) as c FROM ingredients WHERE category=?", (name,))
    if count and count[0]['c'] > 0:
        return jsonify({'error': f'Cannot delete: {count[0]["c"]} ingredient(s) still use this category'}), 400
    key = 'custom_ing_cat:' + name.lower().replace(' ', '_')
    run("DELETE FROM settings WHERE key=?", (key,))
    return jsonify({'ok': True})

# ── Waiter Orders ────────────────────────────────────────────────────────────
@app.route('/waiter')
@login_required
@role_required('waiter')
def waiter():
    user = current_user()
    outlets = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    products = [dict(r) for r in q("SELECT * FROM products WHERE active=1 ORDER BY category,name")]
    chefs = [dict(r) for r in q("SELECT id,name,line FROM users WHERE role='chef' AND active=1 ORDER BY name")]
    role = session.get('role')
    # Get today's open orders
    orders = q("""SELECT co.*, u.name as waiter_name, u2.name as chef_name, o.name as outlet_name
                   FROM customer_orders co
                   JOIN users u ON co.waiter_id=u.id
                   LEFT JOIN users u2 ON co.chef_id=u2.id
                   JOIN outlets o ON co.outlet_id=o.id
                   WHERE co.created_at >= ? AND co.created_at < date(?, '+1 day') ORDER BY co.created_at DESC""", (today(), today()))
    return render_template('waiter.html', user=user, outlets=outlets,
                           products=products, chefs=chefs, orders=orders,
                           today=today(), role=role)

@app.route('/api/orders', methods=['GET','POST'])
@login_required
@csrf_protect
def api_orders():
    role = session.get('role')
    if request.method == 'POST':
        # Only waiters can CREATE orders; managers/GMs view but not create on behalf of waiters
        if role not in ('waiter',):
            return jsonify({'error': 'Only waiters can place orders'}), 403
        data = request.get_json()
        outlet_id = data.get('outlet_id')
        chef_id   = data.get('chef_id')
        table_ref = _str(data.get('table_ref',''), 30)
        items     = data.get('items', [])
        notes     = _str(data.get('notes',''), MAX_NOTE)
        if not outlet_id or not items:
            return jsonify({'error': 'outlet_id and items required'}), 400
        oid = run("INSERT INTO customer_orders (outlet_id,waiter_id,chef_id,table_ref,status,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?)",
                  (outlet_id, session['user_id'], chef_id or None, table_ref, 'pending', notes, now(), now()))
        for item in items:
            pid = item.get('product_id')
            qty = max(1, _int(item.get('qty', 1)))
            item_notes = _str(item.get('notes',''), 200)
            if pid:
                run("INSERT INTO customer_order_items (order_id,product_id,qty,notes) VALUES (?,?,?,?)",
                    (oid, pid, qty, item_notes))
        return jsonify({'ok': True, 'order_id': oid})
    # GET — accessible to all relevant roles
    if role not in ('general_manager','manager','waiter','chef','cashier'):
        return jsonify({'error': 'Unauthorized'}), 403
    d = request.args.get('date', today())
    # Chefs only see orders assigned to them or unassigned
    if role == 'chef':
        chef_id = session['user_id']
        orders = q("""SELECT co.*, u.name as waiter_name, u2.name as chef_name, o.name as outlet_name
                       FROM customer_orders co
                       JOIN users u ON co.waiter_id=u.id
                       LEFT JOIN users u2 ON co.chef_id=u2.id
                       JOIN outlets o ON co.outlet_id=o.id
                       WHERE co.created_at >= ? AND co.created_at < date(?, '+1 day') AND (co.chef_id=? OR co.chef_id IS NULL)
                       AND co.status NOT IN ('served','cancelled')
                       ORDER BY co.created_at DESC""", (d, d, chef_id))
    else:
        orders = q("""SELECT co.*, u.name as waiter_name, u2.name as chef_name, o.name as outlet_name
                       FROM customer_orders co
                       JOIN users u ON co.waiter_id=u.id
                       LEFT JOIN users u2 ON co.chef_id=u2.id
                       JOIN outlets o ON co.outlet_id=o.id
                       WHERE co.created_at >= ? AND co.created_at < date(?, '+1 day') ORDER BY co.created_at DESC""", (d, d))
    result = []
    for o in orders:
        row = dict(o)
        row['items'] = [dict(i) for i in q("""SELECT coi.*, p.name as product_name, p.price
                                               FROM customer_order_items coi
                                               JOIN products p ON coi.product_id=p.id
                                               WHERE coi.order_id=?""", (row['id'],))]
        result.append(row)
    return jsonify(result)

@app.route('/api/orders/<int:oid>', methods=['PUT'])
@login_required
@csrf_protect
def api_order_update(oid):
    role = session.get('role')
    if role not in ('general_manager','manager','waiter','chef','cashier'):
        return jsonify({'error': 'Unauthorized'}), 403
    data   = request.get_json()
    status = _str(data.get('status',''), 20)
    if status not in ('pending','preparing','ready','served','cancelled'):
        return jsonify({'error': 'Invalid status'}), 400
    # Role-based status transition rules:
    # Chef can only set: preparing, ready
    # Waiter can only set: cancelled (cancel own order) or served (after ready)
    # Cashier/Manager/GM can set any status
    order = q("SELECT * FROM customer_orders WHERE id=?", (oid,), one=True)
    if not order:
        return jsonify({'error': 'Order not found'}), 404
    if role == 'chef':
        if status not in ('preparing','ready'):
            return jsonify({'error': 'Chefs can only set orders to preparing or ready'}), 403
    elif role == 'waiter':
        if status not in ('served','cancelled'):
            return jsonify({'error': 'Waiters can only mark orders as served or cancelled'}), 403
        # Waiters can only serve ready orders
        if status == 'served' and order['status'] != 'ready':
            return jsonify({'error': 'Can only serve orders that are ready'}), 400
    run("UPDATE customer_orders SET status=?,updated_at=? WHERE id=?", (status, now(), oid))
    _log_action('order_status_change', 'customer_orders', oid,
                old_value={'status': order['status']}, new_value={'status': status})
    return jsonify({'ok': True})

# ── Role Management API ───────────────────────────────────────────────────────
@app.route('/api/roles', methods=['GET','POST'])
@login_required
@role_required(*MANAGER_ROLES)
@csrf_protect
def api_roles():
    if request.method == 'POST':
        data = request.get_json()
        key  = _str(data.get('role_key',''), 40).lower().replace(' ','_')
        name = _str(data.get('display_name',''), 60)
        perms = _str(data.get('permissions',''), 200)
        notes = _str(data.get('notes',''), 200)
        if not key or not name:
            return jsonify({'error': 'role_key and display_name required'}), 400
        # Prevent overwriting built-in roles
        if key in ('general_manager','manager','chef','cashier','waiter','storekeeper','logistics'):
            return jsonify({'error': 'Cannot overwrite a built-in role'}), 400
        run("INSERT OR REPLACE INTO custom_roles (role_key,display_name,permissions,notes,created_by,created_at) VALUES (?,?,?,?,?,?)",
            (key, name, perms, notes, session['user_id'], now()))
        return jsonify({'ok': True, 'role_key': key})
    roles = q("SELECT * FROM custom_roles ORDER BY display_name")
    return jsonify([dict(r) for r in roles])

@app.route('/api/roles/<path:key>', methods=['PUT','DELETE'])
@login_required
@role_required(*MANAGER_ROLES)
@csrf_protect
def api_role(key):
    if request.method == 'DELETE':
        if session.get('role') != 'general_manager':
            return jsonify({'error': 'Only GM can delete roles'}), 403
        run("DELETE FROM custom_roles WHERE role_key=?", (key,))
        return jsonify({'ok': True})
    data  = request.get_json()
    name  = _str(data.get('display_name',''), 60)
    perms = _str(data.get('permissions',''), 200)
    notes = _str(data.get('notes',''), 200)
    run("UPDATE custom_roles SET display_name=?,permissions=?,notes=? WHERE role_key=?",
        (name, perms, notes, key))
    return jsonify({'ok': True})