from flask import Flask, request, jsonify, send_from_directory, redirect
from flask_cors import CORS
import sqlite3
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
import jwt
import datetime
import uuid
import csv
import io
from openpyxl import load_workbook, Workbook
from reportlab.lib.pagesizes import A4
from reportlab.pdfgen import canvas
from functools import wraps
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
DB_PATH = BASE_DIR / "costos.db"
SECRET_KEY = "antucoya-super-secret"
UPLOADS_DIR = BASE_DIR / "uploads" / "personnel"
ALLOWED_IMAGE_EXTENSIONS = {"png", "jpg", "jpeg", "webp"}

app = Flask(__name__, static_folder="static", template_folder="templates")
app.config["SECRET_KEY"] = SECRET_KEY
app.config["MAX_CONTENT_LENGTH"] = 5 * 1024 * 1024
CORS(app)
UPLOADS_DIR.mkdir(parents=True, exist_ok=True)

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    cur = conn.cursor()

    cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        created_at TEXT NOT NULL
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS cost_centers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ceco INTEGER UNIQUE NOT NULL,
        faena TEXT NOT NULL,
        adc TEXT NOT NULL,
        cliente TEXT NOT NULL
    )
    """)

    cc_cols = [r["name"] for r in cur.execute("PRAGMA table_info(cost_centers)").fetchall()]
    if "ceco" not in cc_cols:
        # Migración desde esquema anterior (name, code, description) al nuevo esquema solicitado
        cur.execute("""
        CREATE TABLE IF NOT EXISTS cost_centers_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ceco INTEGER UNIQUE NOT NULL,
            faena TEXT NOT NULL,
            adc TEXT NOT NULL,
            cliente TEXT NOT NULL
        )
        """)
        old_rows = cur.execute("SELECT id, name, code, description FROM cost_centers").fetchall()
        for r in old_rows:
            try:
                ceco_val = int(str(r["code"]).strip())
            except Exception:
                continue
            faena_val = (r["name"] or "").strip() or f"Faena {ceco_val}"
            adc_val = (r["description"] or "").strip() or "N/A"
            cliente_val = "N/A"
            cur.execute(
                "INSERT OR IGNORE INTO cost_centers_new (id, ceco, faena, adc, cliente) VALUES (?, ?, ?, ?, ?)",
                (r["id"], ceco_val, faena_val, adc_val, cliente_val)
            )
        cur.execute("DROP TABLE cost_centers")
        cur.execute("ALTER TABLE cost_centers_new RENAME TO cost_centers")

    cur.execute("""
    CREATE TABLE IF NOT EXISTS assets (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        type TEXT NOT NULL,
        cost_center_id INTEGER NOT NULL,
        description TEXT,
        FOREIGN KEY(cost_center_id) REFERENCES cost_centers(id) ON DELETE CASCADE
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS expense_categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        description TEXT
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        amount REAL NOT NULL CHECK(amount >= 0),
        expense_date TEXT NOT NULL,
        cost_center_id INTEGER NOT NULL,
        asset_id INTEGER,
        category_id INTEGER NOT NULL,
        notes TEXT,
        FOREIGN KEY(cost_center_id) REFERENCES cost_centers(id),
        FOREIGN KEY(asset_id) REFERENCES assets(id),
        FOREIGN KEY(category_id) REFERENCES expense_categories(id)
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS suppliers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        business_name TEXT NOT NULL,
        trade_name TEXT NOT NULL,
        rut INTEGER NOT NULL UNIQUE,
        category_id INTEGER NOT NULL,
        FOREIGN KEY(category_id) REFERENCES expense_categories(id)
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS personnel_records (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nombres TEXT NOT NULL,
        apellido_paterno TEXT NOT NULL,
        apellido_materno TEXT NOT NULL,
        rut INTEGER NOT NULL UNIQUE,
        fecha_nacimiento TEXT NOT NULL,
        cargo TEXT NOT NULL,
        foto_path TEXT,
        created_at TEXT NOT NULL
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS staff_templates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ceco INTEGER NOT NULL,
        area TEXT NOT NULL,
        cargo TEXT NOT NULL,
        committed_count INTEGER NOT NULL CHECK(committed_count >= 0),
        UNIQUE(ceco, area, cargo)
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS staff_assignments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        assignment_date TEXT NOT NULL,
        ceco INTEGER NOT NULL,
        area TEXT NOT NULL,
        cargo TEXT NOT NULL,
        personnel_id INTEGER NOT NULL,
        is_active INTEGER NOT NULL DEFAULT 1,
        created_at TEXT NOT NULL,
        FOREIGN KEY(personnel_id) REFERENCES personnel_records(id),
        UNIQUE(assignment_date, personnel_id, is_active)
    )
    """)

    cols = [r["name"] for r in cur.execute("PRAGMA table_info(expenses)").fetchall()]
    if "supplier_id" not in cols:
        cur.execute("ALTER TABLE expenses ADD COLUMN supplier_id INTEGER REFERENCES suppliers(id)")

    conn.commit()

    # Seed admin
    cur.execute("SELECT id FROM users WHERE username = ?", ("admin",))
    if not cur.fetchone():
        cur.execute(
            "INSERT INTO users (username, password_hash, created_at) VALUES (?, ?, ?)",
            ("admin", generate_password_hash("jpc2026"), datetime.datetime.utcnow().isoformat())
        )
        conn.commit()

    conn.close()

def token_required(fn):
    @wraps(fn)
    def decorated(*args, **kwargs):
        token = None
        auth_header = request.headers.get("Authorization", "")
        if auth_header.startswith("Bearer "):
            token = auth_header.split(" ", 1)[1]
        if not token:
            return jsonify({"error": "Token requerido"}), 401
        try:
            payload = jwt.decode(token, app.config["SECRET_KEY"], algorithms=["HS256"])
            request.user_id = payload["user_id"]
        except Exception:
            return jsonify({"error": "Token inválido"}), 401
        return fn(*args, **kwargs)
    return decorated

def row_to_dict(row):
    return dict(row) if row else None

def is_allowed_image(filename: str) -> bool:
    if not filename or "." not in filename:
        return False
    ext = filename.rsplit(".", 1)[1].lower()
    return ext in ALLOWED_IMAGE_EXTENSIONS

@app.route("/")
def index():
    return send_from_directory(app.template_folder, "index.html")

@app.route("/login")
def login_page():
    return send_from_directory(app.template_folder, "login.html")

@app.route("/costos")
def costos_page():
    return send_from_directory(app.template_folder, "costos.html")

@app.route("/dotaciones")
def dotaciones_page():
    return send_from_directory(app.template_folder, "dotaciones.html")

@app.route("/disponibilidad")
def disponibilidad_page():
    return send_from_directory(app.template_folder, "disponibilidad.html")

@app.route("/activos")
def activos_page():
    return send_from_directory(app.template_folder, "activos.html")

@app.route("/proveedores")
def proveedores_page():
    return send_from_directory(app.template_folder, "proveedores.html")

@app.route("/dotacion-asignacion")
def dotacion_asignacion_page():
    return send_from_directory(app.template_folder, "dotacion_asignacion.html")

@app.route("/api/health")
def health():
    return jsonify({"status": "ok"})

@app.route("/api/login", methods=["POST"])
def login():
    data = request.get_json(force=True)
    username = data.get("username", "").strip()
    password = data.get("password", "").strip()

    if not username or not password:
        return jsonify({"error": "Usuario y contraseña son requeridos"}), 400

    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT * FROM users WHERE username = ?", (username,))
    user = cur.fetchone()
    conn.close()

    if not user or not check_password_hash(user["password_hash"], password):
        return jsonify({"error": "Credenciales inválidas"}), 401

    token = jwt.encode({
        "user_id": user["id"],
        "username": user["username"],
        "exp": datetime.datetime.utcnow() + datetime.timedelta(hours=8)
    }, app.config["SECRET_KEY"], algorithm="HS256")

    return jsonify({"token": token, "user": {"id": user["id"], "username": user["username"]}})

# ---------------- Cost Centers ----------------
@app.route("/api/cost-centers", methods=["GET"])
@token_required
def list_cost_centers():
    conn = get_db()
    rows = conn.execute("SELECT * FROM cost_centers ORDER BY id DESC").fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/cost-centers", methods=["POST"])
@token_required
def create_cost_center():
    data = request.get_json(force=True)
    ceco = data.get("ceco")
    faena = data.get("faena", "").strip()
    adc = data.get("adc", "").strip()
    cliente = data.get("cliente", "").strip()

    try:
        ceco = int(ceco)
    except Exception:
        return jsonify({"error": "CECO debe ser numérico"}), 400

    if not faena or not adc or not cliente:
        return jsonify({"error": "faena, adc y cliente son obligatorios"}), 400

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute(
            "INSERT INTO cost_centers (ceco, faena, adc, cliente) VALUES (?, ?, ?, ?)",
            (ceco, faena, adc, cliente)
        )
        conn.commit()
        new_id = cur.lastrowid
        row = conn.execute("SELECT * FROM cost_centers WHERE id = ?", (new_id,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row)), 201
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "CECO ya existe"}), 409

@app.route("/api/cost-centers/<int:item_id>", methods=["PUT"])
@token_required
def update_cost_center(item_id):
    data = request.get_json(force=True)
    ceco = data.get("ceco")
    faena = data.get("faena", "").strip()
    adc = data.get("adc", "").strip()
    cliente = data.get("cliente", "").strip()

    try:
        ceco = int(ceco)
    except Exception:
        return jsonify({"error": "CECO debe ser numérico"}), 400

    if not faena or not adc or not cliente:
        return jsonify({"error": "faena, adc y cliente son obligatorios"}), 400

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute(
            "UPDATE cost_centers SET ceco = ?, faena = ?, adc = ?, cliente = ? WHERE id = ?",
            (ceco, faena, adc, cliente, item_id)
        )
        if cur.rowcount == 0:
            conn.close()
            return jsonify({"error": "No encontrado"}), 404
        conn.commit()
        row = conn.execute("SELECT * FROM cost_centers WHERE id = ?", (item_id,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row))
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "CECO ya existe"}), 409

@app.route("/api/cost-centers/<int:item_id>", methods=["DELETE"])
@token_required
def delete_cost_center(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM cost_centers WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Eliminado correctamente"})

# ---------------- Categories ----------------
@app.route("/api/categories", methods=["GET"])
@token_required
def list_categories():
    conn = get_db()
    rows = conn.execute("SELECT * FROM expense_categories ORDER BY id DESC").fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/categories", methods=["POST"])
@token_required
def create_category():
    data = request.get_json(force=True)
    name = data.get("name", "").strip()
    description = data.get("description", "").strip()
    if not name:
        return jsonify({"error": "name es obligatorio"}), 400

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute(
            "INSERT INTO expense_categories (name, description) VALUES (?, ?)",
            (name, description)
        )
        conn.commit()
        row = conn.execute("SELECT * FROM expense_categories WHERE id = ?", (cur.lastrowid,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row)), 201
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "Categoría ya existe"}), 409

@app.route("/api/categories/<int:item_id>", methods=["PUT"])
@token_required
def update_category(item_id):
    data = request.get_json(force=True)
    name = data.get("name", "").strip()
    description = data.get("description", "").strip()
    if not name:
        return jsonify({"error": "name es obligatorio"}), 400

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute("UPDATE expense_categories SET name = ?, description = ? WHERE id = ?", (name, description, item_id))
        if cur.rowcount == 0:
            conn.close()
            return jsonify({"error": "No encontrado"}), 404
        conn.commit()
        row = conn.execute("SELECT * FROM expense_categories WHERE id = ?", (item_id,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row))
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "Categoría ya existe"}), 409

@app.route("/api/categories/<int:item_id>", methods=["DELETE"])
@token_required
def delete_category(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM expense_categories WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Eliminado correctamente"})

# ---------------- Assets ----------------
@app.route("/api/assets", methods=["GET"])
@token_required
def list_assets():
    conn = get_db()
    rows = conn.execute("""
        SELECT a.*, (CAST(c.ceco AS TEXT) || ' - ' || c.faena) AS cost_center_name
        FROM assets a
        JOIN cost_centers c ON c.id = a.cost_center_id
        ORDER BY a.id DESC
    """).fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/assets", methods=["POST"])
@token_required
def create_asset():
    data = request.get_json(force=True)
    name = data.get("name", "").strip()
    type_ = data.get("type", "").strip()
    cost_center_id = data.get("cost_center_id")
    description = data.get("description", "").strip()

    if not name or not type_ or not cost_center_id:
        return jsonify({"error": "name, type y cost_center_id son obligatorios"}), 400

    conn = get_db()
    cc = conn.execute("SELECT id FROM cost_centers WHERE id = ?", (cost_center_id,)).fetchone()
    if not cc:
        conn.close()
        return jsonify({"error": "Centro de costo no existe"}), 404

    cur = conn.cursor()
    cur.execute(
        "INSERT INTO assets (name, type, cost_center_id, description) VALUES (?, ?, ?, ?)",
        (name, type_, cost_center_id, description)
    )
    conn.commit()
    row = conn.execute("SELECT * FROM assets WHERE id = ?", (cur.lastrowid,)).fetchone()
    conn.close()
    return jsonify(row_to_dict(row)), 201

@app.route("/api/assets/<int:item_id>", methods=["PUT"])
@token_required
def update_asset(item_id):
    data = request.get_json(force=True)
    name = data.get("name", "").strip()
    type_ = data.get("type", "").strip()
    cost_center_id = data.get("cost_center_id")
    description = data.get("description", "").strip()
    if not name or not type_ or not cost_center_id:
        return jsonify({"error": "name, type y cost_center_id son obligatorios"}), 400

    conn = get_db()
    cc = conn.execute("SELECT id FROM cost_centers WHERE id = ?", (cost_center_id,)).fetchone()
    if not cc:
        conn.close()
        return jsonify({"error": "Centro de costo no existe"}), 404

    cur = conn.cursor()
    cur.execute(
        "UPDATE assets SET name = ?, type = ?, cost_center_id = ?, description = ? WHERE id = ?",
        (name, type_, cost_center_id, description, item_id)
    )
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    row = conn.execute("SELECT * FROM assets WHERE id = ?", (item_id,)).fetchone()
    conn.close()
    return jsonify(row_to_dict(row))

@app.route("/api/assets/<int:item_id>", methods=["DELETE"])
@token_required
def delete_asset(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM assets WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Eliminado correctamente"})

# ---------------- Expenses ----------------
@app.route("/api/expenses", methods=["GET"])
@token_required
def list_expenses():
    conn = get_db()
    rows = conn.execute("""
        SELECT e.*, (CAST(cc.ceco AS TEXT) || ' - ' || cc.faena) AS cost_center_name, a.name AS asset_name, ec.name AS category_name,
               s.business_name AS supplier_business_name, s.trade_name AS supplier_trade_name
        FROM expenses e
        JOIN cost_centers cc ON cc.id = e.cost_center_id
        LEFT JOIN assets a ON a.id = e.asset_id
        JOIN expense_categories ec ON ec.id = e.category_id
        JOIN suppliers s ON s.id = e.supplier_id
        ORDER BY e.expense_date DESC, e.id DESC
    """).fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/expenses", methods=["POST"])
@token_required
def create_expense():
    data = request.get_json(force=True)
    amount = data.get("amount")
    expense_date = data.get("expense_date", "").strip()
    cost_center_id = data.get("cost_center_id")
    asset_id = data.get("asset_id")
    category_id = data.get("category_id")
    supplier_id = data.get("supplier_id")
    notes = data.get("notes", "").strip()

    if amount is None or not expense_date or not cost_center_id or not category_id or not supplier_id:
        return jsonify({"error": "amount, expense_date, cost_center_id, category_id y supplier_id son obligatorios"}), 400

    try:
        amount = float(amount)
        if amount < 0:
            raise ValueError()
    except Exception:
        return jsonify({"error": "amount debe ser número >= 0"}), 400

    conn = get_db()
    cc = conn.execute("SELECT id FROM cost_centers WHERE id = ?", (cost_center_id,)).fetchone()
    cat = conn.execute("SELECT id FROM expense_categories WHERE id = ?", (category_id,)).fetchone()
    if not cc:
        conn.close()
        return jsonify({"error": "Centro de costo no existe"}), 404
    if not cat:
        conn.close()
        return jsonify({"error": "Categoría no existe"}), 404
    if asset_id:
        asset = conn.execute("SELECT id FROM assets WHERE id = ?", (asset_id,)).fetchone()
        if not asset:
            conn.close()
            return jsonify({"error": "Activo no existe"}), 404

    supplier = conn.execute("SELECT id, category_id FROM suppliers WHERE id = ?", (supplier_id,)).fetchone()
    if not supplier:
        conn.close()
        return jsonify({"error": "Proveedor no existe"}), 404
    if int(supplier["category_id"]) != int(category_id):
        conn.close()
        return jsonify({"error": "Proveedor no corresponde a la clasificación seleccionada"}), 400

    cur = conn.cursor()
    cur.execute("""
        INSERT INTO expenses (amount, expense_date, cost_center_id, asset_id, category_id, notes, supplier_id)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (amount, expense_date, cost_center_id, asset_id, category_id, notes, supplier_id))
    conn.commit()
    row = conn.execute("SELECT * FROM expenses WHERE id = ?", (cur.lastrowid,)).fetchone()
    conn.close()
    return jsonify(row_to_dict(row)), 201

@app.route("/api/expenses/<int:item_id>", methods=["PUT"])
@token_required
def update_expense(item_id):
    data = request.get_json(force=True)
    amount = data.get("amount")
    expense_date = data.get("expense_date", "").strip()
    cost_center_id = data.get("cost_center_id")
    asset_id = data.get("asset_id")
    category_id = data.get("category_id")
    supplier_id = data.get("supplier_id")
    notes = data.get("notes", "").strip()

    if amount is None or not expense_date or not cost_center_id or not category_id or not supplier_id:
        return jsonify({"error": "amount, expense_date, cost_center_id, category_id y supplier_id son obligatorios"}), 400

    try:
        amount = float(amount)
        if amount < 0:
            raise ValueError()
    except Exception:
        return jsonify({"error": "amount debe ser número >= 0"}), 400

    conn = get_db()
    cc = conn.execute("SELECT id FROM cost_centers WHERE id = ?", (cost_center_id,)).fetchone()
    cat = conn.execute("SELECT id FROM expense_categories WHERE id = ?", (category_id,)).fetchone()
    if not cc:
        conn.close()
        return jsonify({"error": "Centro de costo no existe"}), 404
    if not cat:
        conn.close()
        return jsonify({"error": "Categoría no existe"}), 404
    if asset_id:
        asset = conn.execute("SELECT id FROM assets WHERE id = ?", (asset_id,)).fetchone()
        if not asset:
            conn.close()
            return jsonify({"error": "Activo no existe"}), 404

    supplier = conn.execute("SELECT id, category_id FROM suppliers WHERE id = ?", (supplier_id,)).fetchone()
    if not supplier:
        conn.close()
        return jsonify({"error": "Proveedor no existe"}), 404
    if int(supplier["category_id"]) != int(category_id):
        conn.close()
        return jsonify({"error": "Proveedor no corresponde a la clasificación seleccionada"}), 400

    cur = conn.cursor()
    cur.execute("""
        UPDATE expenses
        SET amount = ?, expense_date = ?, cost_center_id = ?, asset_id = ?, category_id = ?, notes = ?, supplier_id = ?
        WHERE id = ?
    """, (amount, expense_date, cost_center_id, asset_id, category_id, notes, supplier_id, item_id))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    row = conn.execute("SELECT * FROM expenses WHERE id = ?", (item_id,)).fetchone()
    conn.close()
    return jsonify(row_to_dict(row))

@app.route("/api/expenses/<int:item_id>", methods=["DELETE"])
@token_required
def delete_expense(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM expenses WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Eliminado correctamente"})

# ---------------- Suppliers ----------------
@app.route("/api/suppliers", methods=["GET"])
@token_required
def list_suppliers():
    conn = get_db()
    rows = conn.execute("""
        SELECT s.*, ec.name AS category_name
        FROM suppliers s
        JOIN expense_categories ec ON ec.id = s.category_id
        ORDER BY s.id DESC
    """).fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/suppliers", methods=["POST"])
@token_required
def create_supplier():
    data = request.get_json(force=True)
    business_name = data.get("business_name", "").strip()
    trade_name = data.get("trade_name", "").strip()
    rut_raw = str(data.get("rut", "")).strip()
    category_id = data.get("category_id")

    if not business_name or not trade_name or not rut_raw or not category_id:
        return jsonify({"error": "business_name, trade_name, rut y category_id son obligatorios"}), 400
    if not rut_raw.isdigit():
        return jsonify({"error": "RUT debe ser numérico"}), 400

    rut = int(rut_raw)

    conn = get_db()
    cat = conn.execute("SELECT id FROM expense_categories WHERE id = ?", (category_id,)).fetchone()
    if not cat:
        conn.close()
        return jsonify({"error": "Clasificación de gasto no existe"}), 404

    cur = conn.cursor()
    try:
        cur.execute(
            "INSERT INTO suppliers (business_name, trade_name, rut, category_id) VALUES (?, ?, ?, ?)",
            (business_name, trade_name, rut, category_id)
        )
        conn.commit()
        row = conn.execute("SELECT * FROM suppliers WHERE id = ?", (cur.lastrowid,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row)), 201
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "RUT ya existe"}), 409

@app.route("/api/suppliers/<int:item_id>", methods=["PUT"])
@token_required
def update_supplier(item_id):
    data = request.get_json(force=True)
    business_name = data.get("business_name", "").strip()
    trade_name = data.get("trade_name", "").strip()
    rut_raw = str(data.get("rut", "")).strip()
    category_id = data.get("category_id")

    if not business_name or not trade_name or not rut_raw or not category_id:
        return jsonify({"error": "business_name, trade_name, rut y category_id son obligatorios"}), 400
    if not rut_raw.isdigit():
        return jsonify({"error": "RUT debe ser numérico"}), 400

    rut = int(rut_raw)

    conn = get_db()
    cat = conn.execute("SELECT id FROM expense_categories WHERE id = ?", (category_id,)).fetchone()
    if not cat:
        conn.close()
        return jsonify({"error": "Clasificación de gasto no existe"}), 404

    cur = conn.cursor()
    try:
        cur.execute("""
            UPDATE suppliers
            SET business_name = ?, trade_name = ?, rut = ?, category_id = ?
            WHERE id = ?
        """, (business_name, trade_name, rut, category_id, item_id))
        if cur.rowcount == 0:
            conn.close()
            return jsonify({"error": "No encontrado"}), 404
        conn.commit()
        row = conn.execute("SELECT * FROM suppliers WHERE id = ?", (item_id,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row))
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "RUT ya existe"}), 409

@app.route("/api/suppliers/<int:item_id>", methods=["DELETE"])
@token_required
def delete_supplier(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM suppliers WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Eliminado correctamente"})

@app.route("/uploads/personnel/<path:filename>")
def personnel_uploads(filename):
    return send_from_directory(UPLOADS_DIR, filename)

@app.route("/api/personnel", methods=["GET"])
@token_required
def list_personnel():
    conn = get_db()
    rows = conn.execute("SELECT * FROM personnel_records ORDER BY id DESC").fetchall()
    conn.close()

    result = []
    for r in rows:
        item = row_to_dict(r)
        item["foto_url"] = f"/uploads/personnel/{item['foto_path']}" if item.get("foto_path") else ""
        result.append(item)
    return jsonify(result)

@app.route("/api/personnel", methods=["POST"])
@token_required
def create_personnel():
    nombres = request.form.get("nombres", "").strip()
    apellido_paterno = request.form.get("apellido_paterno", "").strip()
    apellido_materno = request.form.get("apellido_materno", "").strip()
    rut_raw = str(request.form.get("rut", "")).strip()
    fecha_nacimiento = request.form.get("fecha_nacimiento", "").strip()
    cargo = request.form.get("cargo", "").strip()
    foto = request.files.get("foto")

    if not nombres or not apellido_paterno or not apellido_materno or not rut_raw or not fecha_nacimiento or not cargo:
        return jsonify({"error": "Todos los campos son obligatorios excepto la fotografía"}), 400
    if not rut_raw.isdigit():
        return jsonify({"error": "RUT debe ser numérico"}), 400

    rut = int(rut_raw)
    foto_path = None

    if foto and foto.filename:
        if not is_allowed_image(foto.filename):
            return jsonify({"error": "Formato de imagen no permitido. Usa PNG/JPG/JPEG/WEBP"}), 400
        ext = foto.filename.rsplit(".", 1)[1].lower()
        file_name = secure_filename(f"{rut}_{uuid.uuid4().hex}.{ext}")
        file_path = UPLOADS_DIR / file_name
        foto.save(file_path)
        foto_path = file_name

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute("""
            INSERT INTO personnel_records
            (nombres, apellido_paterno, apellido_materno, rut, fecha_nacimiento, cargo, foto_path, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (nombres, apellido_paterno, apellido_materno, rut, fecha_nacimiento, cargo, foto_path, datetime.datetime.utcnow().isoformat()))
        conn.commit()
        row = conn.execute("SELECT * FROM personnel_records WHERE id = ?", (cur.lastrowid,)).fetchone()
        conn.close()
        item = row_to_dict(row)
        item["foto_url"] = f"/uploads/personnel/{item['foto_path']}" if item.get("foto_path") else ""
        return jsonify(item), 201
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "RUT ya existe"}), 409

@app.route("/api/personnel/<int:item_id>", methods=["PUT"])
@token_required
def update_personnel(item_id):
    nombres = request.form.get("nombres", "").strip()
    apellido_paterno = request.form.get("apellido_paterno", "").strip()
    apellido_materno = request.form.get("apellido_materno", "").strip()
    rut_raw = str(request.form.get("rut", "")).strip()
    fecha_nacimiento = request.form.get("fecha_nacimiento", "").strip()
    cargo = request.form.get("cargo", "").strip()
    foto = request.files.get("foto")

    if not nombres or not apellido_paterno or not apellido_materno or not rut_raw or not fecha_nacimiento or not cargo:
        return jsonify({"error": "Todos los campos son obligatorios excepto la fotografía"}), 400
    if not rut_raw.isdigit():
        return jsonify({"error": "RUT debe ser numérico"}), 400

    rut = int(rut_raw)

    conn = get_db()
    current = conn.execute("SELECT * FROM personnel_records WHERE id = ?", (item_id,)).fetchone()
    if not current:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404

    foto_path = current["foto_path"]
    if foto and foto.filename:
        if not is_allowed_image(foto.filename):
            conn.close()
            return jsonify({"error": "Formato de imagen no permitido. Usa PNG/JPG/JPEG/WEBP"}), 400
        ext = foto.filename.rsplit(".", 1)[1].lower()
        file_name = secure_filename(f"{rut}_{uuid.uuid4().hex}.{ext}")
        file_path = UPLOADS_DIR / file_name
        foto.save(file_path)
        if foto_path:
            old_path = UPLOADS_DIR / foto_path
            if old_path.exists():
                old_path.unlink()
        foto_path = file_name

    try:
        conn.execute("""
            UPDATE personnel_records
            SET nombres = ?, apellido_paterno = ?, apellido_materno = ?, rut = ?, fecha_nacimiento = ?, cargo = ?, foto_path = ?
            WHERE id = ?
        """, (nombres, apellido_paterno, apellido_materno, rut, fecha_nacimiento, cargo, foto_path, item_id))
        conn.commit()
        row = conn.execute("SELECT * FROM personnel_records WHERE id = ?", (item_id,)).fetchone()
        conn.close()
        item = row_to_dict(row)
        item["foto_url"] = f"/uploads/personnel/{item['foto_path']}" if item.get("foto_path") else ""
        return jsonify(item)
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "RUT ya existe"}), 409

@app.route("/api/personnel/<int:item_id>", methods=["DELETE"])
@token_required
def delete_personnel(item_id):
    conn = get_db()
    row = conn.execute("SELECT * FROM personnel_records WHERE id = ?", (item_id,)).fetchone()
    if not row:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404

    foto_path = row["foto_path"]
    conn.execute("DELETE FROM personnel_records WHERE id = ?", (item_id,))
    conn.commit()
    conn.close()

    if foto_path:
        fp = UPLOADS_DIR / foto_path
        if fp.exists():
            fp.unlink()

    return jsonify({"message": "Eliminado correctamente"})

@app.route("/api/personnel/import", methods=["POST"])
@token_required
def import_personnel():
    file = request.files.get("file")
    if not file or not file.filename:
        return jsonify({"error": "Debes seleccionar un archivo .xlsx o .csv"}), 400

    filename = file.filename.lower()
    required = ["nombres", "apellido_paterno", "apellido_materno", "rut", "fecha_nacimiento", "cargo"]

    rows = []
    try:
        if filename.endswith(".csv"):
            content = file.read().decode("utf-8-sig", errors="replace")
            reader = csv.DictReader(io.StringIO(content))
            if not reader.fieldnames:
                return jsonify({"error": "La planilla no tiene encabezados"}), 400
            headers = [str(h or "").strip().lower() for h in reader.fieldnames]
            missing = [k for k in required if k not in headers]
            if missing:
                return jsonify({"error": f"Faltan columnas obligatorias: {', '.join(missing)}"}), 400
            for r in reader:
                norm = {str(k or "").strip().lower(): (str(v or "").strip()) for k, v in r.items()}
                rows.append(norm)
        elif filename.endswith(".xlsx"):
            wb = load_workbook(file, data_only=True)
            ws = wb.active
            header_cells = [str(c.value or "").strip().lower() for c in next(ws.iter_rows(min_row=1, max_row=1))]
            missing = [k for k in required if k not in header_cells]
            if missing:
                return jsonify({"error": f"Faltan columnas obligatorias: {', '.join(missing)}"}), 400
            idx = {h: i for i, h in enumerate(header_cells)}
            for row_cells in ws.iter_rows(min_row=2):
                vals = [c.value for c in row_cells]
                norm = {}
                for col in required:
                    v = vals[idx[col]] if idx[col] < len(vals) else ""
                    norm[col] = str(v or "").strip()
                rows.append(norm)
        else:
            return jsonify({"error": "Formato no soportado. Usa .xlsx o .csv"}), 400
    except Exception as e:
        return jsonify({"error": f"No se pudo leer la planilla: {str(e)}"}), 400

    conn = get_db()
    inserted = 0
    updated = 0
    errors = []

    for i, r in enumerate(rows, start=2):
        nombres = r.get("nombres", "").strip()
        ap = r.get("apellido_paterno", "").strip()
        am = r.get("apellido_materno", "").strip()
        rut_raw = str(r.get("rut", "")).strip()
        fecha_raw = r.get("fecha_nacimiento", "").strip()
        cargo = r.get("cargo", "").strip()

        if not nombres or not ap or not am or not rut_raw or not fecha_raw or not cargo:
            errors.append({"row": i, "error": "Campos obligatorios incompletos"})
            continue
        if not rut_raw.isdigit():
            errors.append({"row": i, "error": "RUT debe ser numérico"})
            continue

        try:
            fecha_norm = str(datetime.datetime.strptime(fecha_raw[:10], "%Y-%m-%d").date())
        except Exception:
            try:
                fecha_norm = str(datetime.datetime.strptime(fecha_raw[:10], "%d-%m-%Y").date())
            except Exception:
                errors.append({"row": i, "error": "Fecha inválida. Usa YYYY-MM-DD o DD-MM-YYYY"})
                continue

        rut = int(rut_raw)
        existing = conn.execute("SELECT id FROM personnel_records WHERE rut = ?", (rut,)).fetchone()

        try:
            if existing:
                conn.execute("""
                    UPDATE personnel_records
                    SET nombres = ?, apellido_paterno = ?, apellido_materno = ?, fecha_nacimiento = ?, cargo = ?
                    WHERE rut = ?
                """, (nombres, ap, am, fecha_norm, cargo, rut))
                updated += 1
            else:
                conn.execute("""
                    INSERT INTO personnel_records
                    (nombres, apellido_paterno, apellido_materno, rut, fecha_nacimiento, cargo, foto_path, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """, (nombres, ap, am, rut, fecha_norm, cargo, None, datetime.datetime.utcnow().isoformat()))
                inserted += 1
        except Exception as e:
            errors.append({"row": i, "error": str(e)})

    conn.commit()
    conn.close()

    return jsonify({
        "message": "Importación procesada",
        "inserted": inserted,
        "updated": updated,
        "errors": errors
    })

def _get_staff_compliance(conn, date_value=None, month_value=None, ceco=None, area=None):
    where_tpl = []
    params_tpl = []
    if ceco:
        where_tpl.append("t.ceco = ?")
        params_tpl.append(int(ceco))
    if area:
        where_tpl.append("LOWER(t.area) = LOWER(?)")
        params_tpl.append(area.strip())

    where_asg = ["a.is_active = 1"]
    params_asg = []
    if date_value:
        where_asg.append("a.assignment_date = ?")
        params_asg.append(date_value)
    elif month_value:
        where_asg.append("substr(a.assignment_date, 1, 7) = ?")
        params_asg.append(month_value)

    if ceco:
        where_asg.append("a.ceco = ?")
        params_asg.append(int(ceco))
    if area:
        where_asg.append("LOWER(a.area) = LOWER(?)")
        params_asg.append(area.strip())

    tpl_sql = f"""
        SELECT t.ceco, t.area, t.cargo, t.committed_count
        FROM staff_templates t
        {"WHERE " + " AND ".join(where_tpl) if where_tpl else ""}
    """
    asg_sql = f"""
        SELECT a.ceco, a.area, a.cargo, COUNT(*) AS assigned_count
        FROM staff_assignments a
        {"WHERE " + " AND ".join(where_asg) if where_asg else ""}
        GROUP BY a.ceco, a.area, a.cargo
    """

    templates = conn.execute(tpl_sql, params_tpl).fetchall()
    assigned_rows = conn.execute(asg_sql, params_asg).fetchall()

    assigned_map = {}
    for r in assigned_rows:
        key = (r["ceco"], r["area"], r["cargo"])
        assigned_map[key] = int(r["assigned_count"] or 0)

    by_role = []
    total_committed = 0
    total_assigned = 0

    for t in templates:
        key = (t["ceco"], t["area"], t["cargo"])
        committed = int(t["committed_count"] or 0)
        assigned = int(assigned_map.get(key, 0))
        vacancy = max(committed - assigned, 0)
        over = max(assigned - committed, 0)

        by_role.append({
            "ceco": t["ceco"],
            "area": t["area"],
            "cargo": t["cargo"],
            "committed": committed,
            "assigned": assigned,
            "vacancy": vacancy,
            "overassigned": over
        })

        total_committed += committed
        total_assigned += assigned

    compliance = (total_assigned / total_committed * 100) if total_committed > 0 else 0.0

    return {
        "total_committed": total_committed,
        "total_assigned": total_assigned,
        "total_vacancy": max(total_committed - total_assigned, 0),
        "total_overassigned": max(total_assigned - total_committed, 0),
        "compliance_pct": round(compliance, 2),
        "by_role": by_role
    }

@app.route("/api/staff-templates", methods=["GET"])
@token_required
def list_staff_templates():
    ceco = request.args.get("ceco")
    area = request.args.get("area")

    where = []
    params = []
    if ceco:
        where.append("ceco = ?")
        params.append(int(ceco))
    if area:
        where.append("LOWER(area) = LOWER(?)")
        params.append(area.strip())

    conn = get_db()
    rows = conn.execute(
        f"SELECT * FROM staff_templates {'WHERE ' + ' AND '.join(where) if where else ''} ORDER BY ceco, area, cargo",
        params
    ).fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/staff-templates", methods=["POST"])
@token_required
def create_staff_template():
    data = request.get_json(force=True)
    ceco = data.get("ceco")
    area = str(data.get("area", "")).strip()
    cargo = str(data.get("cargo", "")).strip()
    committed_count = data.get("committed_count")

    try:
        ceco = int(ceco)
        committed_count = int(committed_count)
    except Exception:
        return jsonify({"error": "ceco y committed_count deben ser numéricos"}), 400

    if not area or not cargo or committed_count < 0:
        return jsonify({"error": "area, cargo y committed_count válidos son obligatorios"}), 400

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute(
            "INSERT INTO staff_templates (ceco, area, cargo, committed_count) VALUES (?, ?, ?, ?)",
            (ceco, area, cargo, committed_count)
        )
        conn.commit()
        row = conn.execute("SELECT * FROM staff_templates WHERE id = ?", (cur.lastrowid,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row)), 201
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "Ya existe plantilla para ese CECO/Área/Cargo"}), 409

@app.route("/api/staff-templates/<int:item_id>", methods=["PUT"])
@token_required
def update_staff_template(item_id):
    data = request.get_json(force=True)
    ceco = data.get("ceco")
    area = str(data.get("area", "")).strip()
    cargo = str(data.get("cargo", "")).strip()
    committed_count = data.get("committed_count")

    try:
        ceco = int(ceco)
        committed_count = int(committed_count)
    except Exception:
        return jsonify({"error": "ceco y committed_count deben ser numéricos"}), 400

    if not area or not cargo or committed_count < 0:
        return jsonify({"error": "area, cargo y committed_count válidos son obligatorios"}), 400

    conn = get_db()
    cur = conn.cursor()
    try:
        cur.execute("""
            UPDATE staff_templates
            SET ceco = ?, area = ?, cargo = ?, committed_count = ?
            WHERE id = ?
        """, (ceco, area, cargo, committed_count, item_id))
        if cur.rowcount == 0:
            conn.close()
            return jsonify({"error": "No encontrado"}), 404
        conn.commit()
        row = conn.execute("SELECT * FROM staff_templates WHERE id = ?", (item_id,)).fetchone()
        conn.close()
        return jsonify(row_to_dict(row))
    except sqlite3.IntegrityError:
        conn.close()
        return jsonify({"error": "Ya existe plantilla para ese CECO/Área/Cargo"}), 409

@app.route("/api/staff-templates/<int:item_id>", methods=["DELETE"])
@token_required
def delete_staff_template(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM staff_templates WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Eliminado correctamente"})

@app.route("/api/staff-assignments", methods=["GET"])
@token_required
def list_staff_assignments():
    date_value = request.args.get("date", "").strip()
    ceco = request.args.get("ceco")
    area = request.args.get("area", "").strip()

    where = ["a.is_active = 1"]
    params = []

    if date_value:
        where.append("a.assignment_date = ?")
        params.append(date_value)
    if ceco:
        where.append("a.ceco = ?")
        params.append(int(ceco))
    if area:
        where.append("LOWER(a.area) = LOWER(?)")
        params.append(area)

    conn = get_db()
    rows = conn.execute(f"""
        SELECT a.*, p.nombres, p.apellido_paterno, p.apellido_materno, p.rut
        FROM staff_assignments a
        JOIN personnel_records p ON p.id = a.personnel_id
        WHERE {" AND ".join(where)}
        ORDER BY a.assignment_date DESC, a.ceco, a.area, a.cargo
    """, params).fetchall()
    conn.close()
    return jsonify([row_to_dict(r) for r in rows])

@app.route("/api/staff-assignments", methods=["POST"])
@token_required
def create_staff_assignment():
    data = request.get_json(force=True)
    assignment_date = str(data.get("assignment_date", "")).strip()
    ceco = data.get("ceco")
    area = str(data.get("area", "")).strip()
    cargo = str(data.get("cargo", "")).strip()
    personnel_id = data.get("personnel_id")

    try:
        datetime.datetime.strptime(assignment_date, "%Y-%m-%d")
        ceco = int(ceco)
        personnel_id = int(personnel_id)
    except Exception:
        return jsonify({"error": "Datos inválidos (fecha YYYY-MM-DD, ceco y personnel_id numéricos)"}), 400

    if not area or not cargo:
        return jsonify({"error": "area y cargo son obligatorios"}), 400

    conn = get_db()

    person = conn.execute("SELECT id FROM personnel_records WHERE id = ?", (personnel_id,)).fetchone()
    if not person:
        conn.close()
        return jsonify({"error": "Persona no existe en Dotaciones"}), 404

    exists_person = conn.execute("""
        SELECT id FROM staff_assignments
        WHERE assignment_date = ? AND personnel_id = ? AND is_active = 1
    """, (assignment_date, personnel_id)).fetchone()
    if exists_person:
        conn.close()
        return jsonify({"error": "La persona ya tiene un puesto activo en esa fecha"}), 409

    cur = conn.cursor()
    cur.execute("""
        INSERT INTO staff_assignments
        (assignment_date, ceco, area, cargo, personnel_id, is_active, created_at)
        VALUES (?, ?, ?, ?, ?, 1, ?)
    """, (assignment_date, ceco, area, cargo, personnel_id, datetime.datetime.utcnow().isoformat()))
    conn.commit()
    row = conn.execute("SELECT * FROM staff_assignments WHERE id = ?", (cur.lastrowid,)).fetchone()
    conn.close()
    return jsonify(row_to_dict(row)), 201

@app.route("/api/staff-assignments/<int:item_id>", methods=["DELETE"])
@token_required
def delete_staff_assignment(item_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE staff_assignments SET is_active = 0 WHERE id = ?", (item_id,))
    if cur.rowcount == 0:
        conn.close()
        return jsonify({"error": "No encontrado"}), 404
    conn.commit()
    conn.close()
    return jsonify({"message": "Desasignado correctamente"})

@app.route("/api/staff-compliance", methods=["GET"])
@token_required
def staff_compliance():
    date_value = request.args.get("date", "").strip()
    month_value = request.args.get("month", "").strip()
    ceco = request.args.get("ceco")
    area = request.args.get("area", "").strip()

    if not date_value and not month_value:
        date_value = datetime.date.today().isoformat()

    conn = get_db()
    data = _get_staff_compliance(conn, date_value=date_value or None, month_value=month_value or None, ceco=ceco, area=area or None)
    conn.close()

    return jsonify({
        "scope": {"date": date_value, "month": month_value, "ceco": ceco, "area": area},
        **data
    })

@app.route("/api/staff-report/excel", methods=["GET"])
@token_required
def staff_report_excel():
    date_value = request.args.get("date", "").strip()
    month_value = request.args.get("month", "").strip()
    ceco = request.args.get("ceco")
    area = request.args.get("area", "").strip()
    if not date_value and not month_value:
        date_value = datetime.date.today().isoformat()

    conn = get_db()
    data = _get_staff_compliance(conn, date_value=date_value or None, month_value=month_value or None, ceco=ceco, area=area or None)
    conn.close()

    wb = Workbook()
    ws = wb.active
    ws.title = "Cumplimiento"
    ws.append(["CECO", "Área", "Cargo", "Comprometido", "Asignado", "Vacante", "Sobreasignado"])
    for r in data["by_role"]:
        ws.append([r["ceco"], r["area"], r["cargo"], r["committed"], r["assigned"], r["vacancy"], r["overassigned"]])

    ws2 = wb.create_sheet("Resumen")
    ws2.append(["Indicador", "Valor"])
    ws2.append(["Total comprometido", data["total_committed"]])
    ws2.append(["Total asignado", data["total_assigned"]])
    ws2.append(["Vacantes", data["total_vacancy"]])
    ws2.append(["Sobreasignación", data["total_overassigned"]])
    ws2.append(["Cumplimiento %", data["compliance_pct"]])

    from io import BytesIO
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    from flask import send_file
    return send_file(
        output,
        as_attachment=True,
        download_name="reporte_dotacion.xlsx",
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )

@app.route("/api/staff-report/pdf", methods=["GET"])
@token_required
def staff_report_pdf():
    date_value = request.args.get("date", "").strip()
    month_value = request.args.get("month", "").strip()
    ceco = request.args.get("ceco")
    area = request.args.get("area", "").strip()
    if not date_value and not month_value:
        date_value = datetime.date.today().isoformat()

    conn = get_db()
    data = _get_staff_compliance(conn, date_value=date_value or None, month_value=month_value or None, ceco=ceco, area=area or None)
    conn.close()

    from io import BytesIO
    buffer = BytesIO()
    c = canvas.Canvas(buffer, pagesize=A4)
    width, height = A4

    y = height - 40
    c.setFont("Helvetica-Bold", 14)
    c.drawString(40, y, "Reporte de Cumplimiento de Dotación")
    y -= 24
    c.setFont("Helvetica", 10)
    c.drawString(40, y, f"Fecha: {date_value or '-'}    Mes: {month_value or '-'}    CECO: {ceco or 'Todos'}    Área: {area or 'Todas'}")
    y -= 20
    c.drawString(40, y, f"Total comprometido: {data['total_committed']} | Total asignado: {data['total_assigned']} | Cumplimiento: {data['compliance_pct']}%")
    y -= 24

    c.setFont("Helvetica-Bold", 9)
    c.drawString(40, y, "CECO")
    c.drawString(90, y, "Área")
    c.drawString(190, y, "Cargo")
    c.drawString(350, y, "Comp")
    c.drawString(390, y, "Asig")
    c.drawString(430, y, "Vac")
    c.drawString(470, y, "Sobre")
    y -= 14
    c.setFont("Helvetica", 8)

    for r in data["by_role"]:
        if y < 40:
            c.showPage()
            y = height - 40
            c.setFont("Helvetica", 8)
        c.drawString(40, y, str(r["ceco"]))
        c.drawString(90, y, str(r["area"])[:18])
        c.drawString(190, y, str(r["cargo"])[:30])
        c.drawString(350, y, str(r["committed"]))
        c.drawString(390, y, str(r["assigned"]))
        c.drawString(430, y, str(r["vacancy"]))
        c.drawString(470, y, str(r["overassigned"]))
        y -= 12

    c.save()
    buffer.seek(0)

    from flask import send_file
    return send_file(
        buffer,
        as_attachment=True,
        download_name="reporte_dotacion.pdf",
        mimetype="application/pdf"
    )

@app.route("/api/dashboard", methods=["GET"])
@token_required
def dashboard():
    start_date = request.args.get("start_date")
    end_date = request.args.get("end_date")
    cost_center_id = request.args.get("cost_center_id")
    category_id = request.args.get("category_id")

    where = []
    params = []

    if start_date:
        where.append("e.expense_date >= ?")
        params.append(start_date)
    if end_date:
        where.append("e.expense_date <= ?")
        params.append(end_date)
    if cost_center_id:
        where.append("e.cost_center_id = ?")
        params.append(cost_center_id)
    if category_id:
        where.append("e.category_id = ?")
        params.append(category_id)

    where_sql = f"WHERE {' AND '.join(where)}" if where else ""

    conn = get_db()

    total = conn.execute(f"SELECT COALESCE(SUM(e.amount),0) AS total FROM expenses e {where_sql}", params).fetchone()["total"]

    by_category = conn.execute(f"""
        SELECT ec.name AS label, COALESCE(SUM(e.amount),0) AS value
        FROM expenses e
        JOIN expense_categories ec ON ec.id = e.category_id
        {where_sql}
        GROUP BY ec.name
        ORDER BY value DESC
    """, params).fetchall()

    by_cost_center = conn.execute(f"""
        SELECT (CAST(cc.ceco AS TEXT) || ' - ' || cc.faena) AS label, COALESCE(SUM(e.amount),0) AS value
        FROM expenses e
        JOIN cost_centers cc ON cc.id = e.cost_center_id
        {where_sql}
        GROUP BY cc.ceco, cc.faena
        ORDER BY value DESC
    """, params).fetchall()

    trend = conn.execute(f"""
        SELECT e.expense_date AS label, COALESCE(SUM(e.amount),0) AS value
        FROM expenses e
        {where_sql}
        GROUP BY e.expense_date
        ORDER BY e.expense_date ASC
    """, params).fetchall()

    conn.close()

    return jsonify({
        "total_expense": total,
        "by_category": [row_to_dict(r) for r in by_category],
        "by_cost_center": [row_to_dict(r) for r in by_cost_center],
        "trend": [row_to_dict(r) for r in trend]
    })

if __name__ == "__main__":
    init_db()
    app.run(host="127.0.0.1", port=5000, debug=True)