"""routes/production.py — production targets, records, requisitions, recipes."""
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)
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('/api/production/targets', methods=['GET','POST'])
@login_required
@role_required('general_manager','manager','chef')
@csrf_protect
def api_targets():
    d = request.args.get('date', today())
    if request.method == 'POST':
        data = request.get_json()
        for pid, qty in data.get('targets',{}).items():
            qty = max(0, _int(qty))
            # Atomic UPSERT — safe against concurrent submissions (UNIQUE constraint on date+product_id)
            run("""INSERT INTO production_targets (date,product_id,target_qty,submitted_by)
                    VALUES (?,?,?,?)
                    ON CONFLICT(date,product_id) DO UPDATE SET
                        target_qty=excluded.target_qty,
                        submitted_by=excluded.submitted_by""",
                (d, pid, qty, session['user_id']))
        return jsonify({'ok':True})
    # Auto-copy from previous day's tomorrow-targets if today has none yet
    targets = q("SELECT product_id, target_qty, locked FROM production_targets WHERE date=?",(d,))
    if not targets and d == today():
        yesterday = (date.today() - timedelta(days=1)).isoformat()
        tomorrow_of_yesterday = today()  # that's today from yesterday's perspective
        prev = q("SELECT product_id, target_qty FROM production_targets WHERE date=?",(tomorrow_of_yesterday,))
        # Nothing to copy on first ever run; just return empty
    return jsonify({str(r['product_id']): {'qty':r['target_qty'],'locked':r['locked']} for r in targets})

@app.route('/api/production/carry_forward', methods=['POST'])
@login_required
@role_required('general_manager','manager','chef')
@csrf_protect
def api_carry_forward():
    """Copy tomorrow's saved targets into today's slots (called at day start or manually)."""
    d = request.get_json().get('date', today())
    yesterday = (date.fromisoformat(d) - timedelta(days=1)).isoformat()
    # Get targets that were set as 'tomorrow' from yesterday (stored under today's date)
    already = q("SELECT COUNT(*) as c FROM production_targets WHERE date=? AND target_qty>0", (d,), one=True)['c']
    if already > 0:
        return jsonify({'ok':True,'copied':0,'note':'Today already has targets'})
    # Copy any targets stored for this date (set yesterday as tomorrow)
    rows = q("SELECT product_id, target_qty FROM production_targets WHERE date=? AND target_qty>0",(d,))
    copied = 0
    for r in rows:
        # INSERT OR IGNORE: safe — if a target already exists for this date+product, skip it
        rows_changed = get_db().execute(
            """INSERT OR IGNORE INTO production_targets (date,product_id,target_qty,submitted_by)
                VALUES (?,?,?,?)""",
            (d, r['product_id'], r['target_qty'], session['user_id'])
        ).rowcount
        get_db().commit()
        copied += rows_changed
    return jsonify({'ok':True,'copied':copied})

@app.route('/api/production/requisition', methods=['GET','POST'])
@login_required
@role_required('general_manager','manager','chef')
@csrf_protect
def api_requisition():
    d = request.args.get('date', today())
    if request.method == 'POST':
        data     = request.get_json()
        for_date = _str(data.get('for_date', d), 10)
        items    = data.get('items', {})
        req_type = _str(data.get('req_type', 'kitchen'), 20)
        auto_from_recipe = data.get('auto_from_recipe', False)

        # If auto_from_recipe: calculate ingredient needs from recipe × targets
        if auto_from_recipe:
            targets = q("SELECT product_id, target_qty FROM production_targets WHERE date=? AND target_qty>0",(for_date,))
            ingredient_totals = {}
            for t in targets:
                recipe = q("""SELECT ingredient_id, qty_used, yields FROM recipes WHERE product_id=?""",(t['product_id'],))
                for row in recipe:
                    usage = (row['qty_used'] / row['yields']) * t['target_qty']
                    iid = str(row['ingredient_id'])
                    ingredient_totals[iid] = ingredient_totals.get(iid, 0) + usage
            items = ingredient_totals

        req_id   = run("INSERT INTO requisitions (date,for_date,requested_by,req_type,status) VALUES (?,?,?,?,?)",
                       (d, for_date, session['user_id'], req_type, 'pending'))
        for ing_id, qty in items.items():
            qty = max(0, _float(qty))
            if qty > 0:
                run("INSERT INTO requisition_items (requisition_id,ingredient_id,qty_requested) VALUES (?,?,?)",
                    (req_id, ing_id, qty))
        if for_date == d:
            run("UPDATE production_targets SET locked=1 WHERE date=?",(d,))
        return jsonify({'ok':True,'req_id':req_id})
    return jsonify({'ok':True})

@app.route('/api/production/record', methods=['POST'])
@login_required
@role_required('general_manager','manager','chef')
@csrf_protect
def api_save_production():
    data = request.get_json()
    d    = _str(data.get('date', today()), 10)
    records = data.get('records', {})
    chef_id = session['user_id']
    for pid, vals in records.items():
        # Slot-based finished values (5 production windows)
        slot_3am  = max(0, _int(vals.get('slot_3am',  0)))
        slot_7am  = max(0, _int(vals.get('slot_7am',  0)))
        slot_11am = max(0, _int(vals.get('slot_11am', 0)))
        slot_3pm  = max(0, _int(vals.get('slot_3pm',  0)))
        slot_7pm  = max(0, _int(vals.get('slot_7pm',  0)))
        # Finished = sum of all slot inputs
        finished  = slot_3am + slot_7am + slot_11am + slot_3pm + slot_7pm
        # Portions Made removed — wastage = target - finished
        expected  = max(0, _int(vals.get('expected', 0)))
        wastage   = max(0, expected - finished)
        if finished == 0 and all(s == 0 for s in [slot_3am,slot_7am,slot_11am,slot_3pm,slot_7pm]):
            continue
        existing = q("SELECT id, finished as prev_finished FROM production_records WHERE date=? AND product_id=? AND chef_id=?",
                     (d, pid, chef_id), one=True)
        if existing:
            prev_finished = existing['prev_finished'] or 0
            run("""UPDATE production_records
                   SET finished=?,wastage=?,expected_output=?,recorded_at=?,
                       slot_3am=?,slot_7am=?,slot_11am=?,slot_3pm=?,slot_7pm=?
                   WHERE id=?""",
                (finished, wastage, expected, now(),
                 slot_3am, slot_7am, slot_11am, slot_3pm, slot_7pm, existing['id']))
            delta = finished - prev_finished
        else:
            run("""INSERT INTO production_records
                   (date,product_id,finished,wastage,expected_output,recorded_by,chef_id,recorded_at,
                    slot_3am,slot_7am,slot_11am,slot_3pm,slot_7pm)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                (d, pid, finished, wastage, expected, chef_id, chef_id, now(),
                 slot_3am, slot_7am, slot_11am, slot_3pm, slot_7pm))
            delta = finished
        # Deduct actual ingredient usage from stock based on recipe × net new finished units
        # FIX 4.3: wrapped in explicit transaction to prevent concurrency desync
        if delta > 0:
            db_conn = get_db()
            try:
                recipe = q("SELECT ingredient_id, qty_used, yields FROM recipes WHERE product_id=?",(pid,))
                for row in recipe:
                    usage = (row['qty_used'] / row['yields']) * delta
                    db_conn.execute(
                        "UPDATE stock SET quantity=MAX(0,quantity-?), last_updated=CURRENT_TIMESTAMP WHERE ingredient_id=?",
                        (usage, row['ingredient_id'])
                    )
                db_conn.commit()
            except Exception as e:
                db_conn.rollback()
                return jsonify({'ok': False, 'error': 'Stock deduction failed: ' + str(e)}), 500
        # Write ledger entry for production output
        if finished > 0:
            ledger_entry(int(pid), 'PRODUCTION_OUTPUT', finished, 'product',
                         'production_records', None,
                         f"Chef {chef_id} · date {d} · wastage {wastage}")
    return jsonify({'ok': True})

@app.route('/api/production/my_records')
@login_required
@role_required('general_manager','manager','chef')
def api_my_records():
    """Return production records for the current chef only (managers see all summed)."""
    d = request.args.get('date', today())
    role = session.get('role')
    if role in MANAGER_ROLES:
        rows = q("""SELECT product_id,
                           SUM(finished) as finished,
                           SUM(wastage) as wastage,
                           SUM(expected_output) as expected,
                           SUM(COALESCE(slot_3am,0))  as slot_3am,
                           SUM(COALESCE(slot_7am,0))  as slot_7am,
                           SUM(COALESCE(slot_11am,0)) as slot_11am,
                           SUM(COALESCE(slot_3pm,0))  as slot_3pm,
                           SUM(COALESCE(slot_7pm,0))  as slot_7pm
                    FROM production_records WHERE date=? GROUP BY product_id""", (d,))
    else:
        rows = q("""SELECT product_id, finished, wastage, expected_output as expected,
                           COALESCE(slot_3am,0)  as slot_3am,
                           COALESCE(slot_7am,0)  as slot_7am,
                           COALESCE(slot_11am,0) as slot_11am,
                           COALESCE(slot_3pm,0)  as slot_3pm,
                           COALESCE(slot_7pm,0)  as slot_7pm
                    FROM production_records WHERE date=? AND chef_id=?""", (d, session['user_id']))
    return jsonify({str(r['product_id']): dict(r) for r in rows})

@app.route('/api/production/real_expected_revenue')
@login_required
@role_required('general_manager','manager','chef')
def api_real_expected_revenue():
    """Real expected revenue = SUM(finished portions × product price) across all chefs for today."""
    d = request.args.get('date', today())
    rows = q("""SELECT pr.product_id, SUM(pr.finished) as total_finished, p.price, p.name
                FROM production_records pr
                JOIN products p ON pr.product_id = p.id
                WHERE pr.date=?
                GROUP BY pr.product_id""", (d,))
    total = 0.0
    items = []
    for r in rows:
        rev = (r['total_finished'] or 0) * (r['price'] or 0)
        total += rev
        items.append({'product_id': r['product_id'], 'name': r['name'],
                      'finished': r['total_finished'] or 0, 'price': r['price'],
                      'revenue': round(rev, 2)})
    return jsonify({'total': round(total, 2), 'items': items, 'date': d})

@app.route('/api/recipe/<int:product_id>')
@login_required
def api_recipe(product_id):
    recipe = q("""SELECT r.id, r.ingredient_id, r.qty_used, r.yields, r.note,
                         i.name as ing_name, i.unit, i.cost_per_unit
                  FROM recipes r JOIN ingredients i ON r.ingredient_id=i.id
                  WHERE r.product_id=?""",(product_id,))
    return jsonify([dict(r) for r in recipe])

# ── Store ─────────────────────────────────────────────────────────────────────