"""routes/finance.py — finance dashboard, expenses, M-Pesa, reconciliation."""
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('/finance')
@login_required
@role_required('general_manager','manager')
def finance():
    user    = current_user()
    d       = today()
    role    = session.get('role')
    outlets = [dict(r) for r in q("SELECT * FROM outlets WHERE active=1")]
    recon   = []
    for outlet in outlets:
        exp_rev = calc_expected_revenue(outlet['id'], d)
        manual_mpesa = 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']
        auto_mpesa   = 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']
        mpesa = manual_mpesa + auto_mpesa
        cp    = get_cash_position(outlet['id'], d)
        tc    = mpesa + cp['cash_revenue']
        variance = tc - exp_rev
        recon.append({'outlet':dict(outlet),'exp_rev':exp_rev,'mpesa':mpesa,
                      'manual_mpesa':manual_mpesa,'cp':cp,'tc':tc,'variance':variance})
    ing_cost    = calc_ingredient_cost(d)
    other_exp   = q("SELECT * FROM expenses WHERE date=? ORDER BY created_at DESC",(d,))
    other_total = sum(e['amount']+e['tx_cost'] for e in other_exp)
    receipts    = q("""SELECT ir.*, i.name as ing_name FROM ingredient_receipts ir
                       LEFT JOIN ingredients i ON ir.ingredient_id=i.id WHERE ir.date=?""",(d,))
    payroll     = calc_payroll() if role == 'general_manager' else None
    total_rev   = sum(r['exp_rev'] for r in recon)
    profit      = total_rev - ing_cost - other_total
    exp_cats    = [r['name'] for r in q("SELECT name FROM expense_categories ORDER BY is_default DESC, name")]
    return render_template('finance.html', user=user, recon=recon,
        ing_cost=ing_cost, other_exp=other_exp, other_total=other_total,
        receipts=receipts, payroll=payroll, total_rev=total_rev,
        profit=profit, today=d, role=role, outlets=outlets, exp_cats=exp_cats)

@app.route('/api/finance/expense', methods=['POST'])
@login_required
@role_required('general_manager','manager')
@csrf_protect
def add_expense():
    data = request.get_json()
    if not data.get('category') or data.get('amount') is None:
        return jsonify({'error': 'Missing fields'}), 400
    run("INSERT INTO expenses (date,category,amount,tx_cost,note,recorded_by) VALUES (?,?,?,?,?,?)",
        (today(),_str(data['category']),_float(data['amount']),_float(data.get('tx_cost',0)),_str(data.get('note',''),MAX_NOTE),session['user_id']))
    return jsonify({'ok':True})

@app.route('/api/finance/mpesa', methods=['POST'])
@login_required
@role_required('general_manager','manager')
@csrf_protect
def add_mpesa():
    data      = request.get_json()
    outlet_id = data.get('outlet_id')
    amount    = _float(data.get('amount', 0))
    note      = _str(data.get('note',''), MAX_NOTE)
    mpesa_ref = _str(data.get('mpesa_ref',''), 30)
    if not outlet_id or amount <= 0: return jsonify({'error':'Invalid'}), 400
    # Dedup: if mpesa_ref provided and already exists in auto-table today, warn
    if mpesa_ref:
        auto_exists = q("SELECT id FROM mpesa_transactions WHERE mpesa_ref=?", (mpesa_ref,), one=True)
        if auto_exists:
            return jsonify({'error': f'M-Pesa ref {mpesa_ref} already captured automatically. Do not add manually to avoid double-counting.'}), 409
        manual_exists = q("SELECT id FROM outlet_mpesa WHERE mpesa_ref=?", (mpesa_ref,), one=True)
        if manual_exists:
            return jsonify({'error': f'M-Pesa ref {mpesa_ref} already recorded manually.'}), 409
    run("INSERT INTO outlet_mpesa (outlet_id,amount,note,mpesa_ref,recorded_by) VALUES (?,?,?,?,?)",
        (outlet_id, amount, note, mpesa_ref or None, session['user_id']))
    return jsonify({'ok':True})

# ── Admin ─────────────────────────────────────────────────────────────────────