"""routes/store.py — store (storekeeper) and logistics routes."""
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('/store')
@login_required
@role_required('general_manager','manager','storekeeper')
def store():
    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
                        ORDER BY r.created_at DESC LIMIT 200""")
    # Gather distinct product categories (static list + any custom ones in products table)
    static_prod_cats = ['Burgers & Hotdogs','Chicken','Pasta','Staples','Potatoes','Vegetables','Bread & Snacks','Sides','Beverages']
    db_prod_cats = [row[0] for row in q("SELECT DISTINCT category FROM products WHERE active=1 ORDER BY category")]
    product_categories = sorted(set(static_prod_cats + db_prod_cats))
    d = today()
    return render_template('store.html', user=user, ingredients=ingredients,
                           requisitions=requisitions, today=d,
                           product_categories=product_categories)

@app.route('/api/store/approve/<int:req_id>', methods=['POST'])
@login_required
@role_required('general_manager','manager','storekeeper')
@csrf_protect
def approve_req(req_id):
    data = request.get_json() or {}
    note = _str(data.get('note',''), MAX_NOTE)
    reviewer = session['user_id']
    ts = now()
    if data.get('reject'):
        run("UPDATE requisitions SET status='rejected', reviewed_by=?, reviewed_at=?, review_note=? WHERE id=?",
            (reviewer, ts, note, req_id))
    else:
        run("UPDATE requisitions SET status='approved', reviewed_by=?, reviewed_at=?, review_note=? WHERE id=?",
            (reviewer, ts, note, req_id))
    return jsonify({'ok': True})

@app.route('/api/store/restock', methods=['POST'])
@login_required
@role_required('general_manager','manager','storekeeper')
@csrf_protect
def restock():
    data       = request.get_json() or {}
    ing_id     = data.get('ingredient_id')
    qty        = _float(data.get('quantity', 0))
    amount     = _float(data.get('amount', 0))
    tx         = _float(data.get('tx_cost', 0))
    supplier   = _str(data.get('supplier', ''), 200)
    invoice_no = _str(data.get('invoice_no', ''), 100)
    recv_date  = _str(data.get('date', today()), 10) or today()
    note       = _str(data.get('note', ''), MAX_NOTE)
    if not ing_id or qty <= 0: return jsonify({'error': 'Invalid data'}), 400
    run("UPDATE stock SET quantity=quantity+?, last_updated=CURRENT_TIMESTAMP WHERE ingredient_id=?",
        (qty, ing_id))
    run("""INSERT INTO ingredient_receipts
              (date, ingredient_id, quantity, amount, tx_cost, supplier, invoice_no, note)
            VALUES (?,?,?,?,?,?,?,?)""",
        (recv_date, ing_id, qty, amount, tx, supplier or None, invoice_no or None, note or None))
    _log_action('stock_restock', 'ingredients', ing_id,
                new_value={'qty_added': qty, 'amount': amount, 'supplier': supplier, 'invoice_no': invoice_no})
    return jsonify({'ok': True})

# ── Logistics ─────────────────────────────────────────────────────────────────
@app.route('/logistics')
@login_required
@role_required('general_manager','manager')
def logistics():
    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")]
    available = {}
    d = today()
    for p in products:
        # SUM across all chefs for this product on this date
        finished   = q("SELECT COALESCE(SUM(finished),0) as t FROM production_records WHERE date=? AND product_id=?",(d,p['id']),one=True)['t']
        dispatched = q("""SELECT COALESCE(SUM(qty_in),0) as t FROM stock_events
                          WHERE product_id=? AND timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type IN ('OPENING_DISPATCH','TOPUP')""",(p['id'],d,d),one=True)['t']
        available[p['id']] = max(0, finished - dispatched)
    dispatch_log = q("""SELECT se.*, 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 date(se.timestamp)=? AND se.event_type IN ('OPENING_DISPATCH','TOPUP')
                        ORDER BY se.timestamp DESC""",(d,))
    # Compute margin per product for dispatch screen
    margins = {}
    for p in products:
        cpu = cost_per_unit(p['id'])
        margins[p['id']] = {'cpu': round(cpu, 2), 'margin': round(p['price'] - cpu, 2),
                             'margin_pct': round((p['price']-cpu)/p['price']*100, 1) if p['price'] > 0 else 0}
    return render_template('logistics.html', user=user, outlets=outlets,
                           products=products, available=available,
                           dispatch_log=[dict(r) for r in dispatch_log], today=d, margins=margins)

@app.route('/api/logistics/dispatch', methods=['POST'])
@login_required
@role_required('general_manager','manager')
@csrf_protect
def dispatch():
    data       = request.get_json()
    outlet_id  = data.get('outlet_id')
    event_type = _str(data.get('event_type','OPENING_DISPATCH'), 30)
    items      = data.get('items',{})
    ref        = _str(data.get('ref','DISP-'+today().replace('-','')), 50)
    fuel       = _float(data.get('fuel_cost', 0))
    if not outlet_id or not items: return jsonify({'error': 'Missing outlet or items'}), 400
    ts  = now()
    d   = today()
    # Auto-generate receipt reference: OUTLET-YYYYMMDD-SEQN
    seq = q("SELECT COUNT(*)+1 as n FROM stock_events WHERE timestamp >= ? AND timestamp < date(?, '+1 day') AND event_type IN ('OPENING_DISPATCH','TOPUP')", (d, d), one=True)['n']
    auto_ref = f"DISP-{d.replace('-','')}-{seq:04d}"
    ref = _str(data.get('ref', auto_ref), 50) or auto_ref
    for pid, qty in items.items():
        qty_int = max(0, _int(qty))
        run("""INSERT INTO stock_events (outlet_id,product_id,event_type,qty_in,reference,recorded_by,timestamp)
               VALUES (?,?,?,?,?,?,?)""",(outlet_id,pid,event_type,qty_int,ref,session['user_id'],ts))
        # Write immutable ledger entry
        ledger_entry(pid, 'DISPATCH', qty_int, 'product', 'stock_events', None,
                     f"Dispatched to outlet {outlet_id} · ref {ref}")
    if fuel > 0:
        run("INSERT INTO expenses (date,category,amount,note,recorded_by) VALUES (?,?,?,?,?)",
            (d,'Transport',fuel,f"Delivery to outlet {outlet_id}",session['user_id']))
    return jsonify({'ok':True,'ref':ref,'timestamp':ts})

# ── Cashier ───────────────────────────────────────────────────────────────────