import os
import uuid

from flask import (
    Blueprint,
    abort,
    current_app,
    flash,
    redirect,
    render_template,
    request,
    send_from_directory,
    url_for,
)
from werkzeug.utils import secure_filename

from .auth import login_required
from .db import get_db

bp = Blueprint("ledger", __name__)


def ledger_balance(db, ledger_id):
    contrib = db.execute(
        "SELECT COALESCE(SUM(amount), 0) AS total FROM contributions WHERE ledger_id = ?",
        (ledger_id,),
    ).fetchone()["total"]
    spent = db.execute(
        "SELECT COALESCE(SUM(amount), 0) AS total FROM expenses WHERE ledger_id = ?",
        (ledger_id,),
    ).fetchone()["total"]
    return contrib - spent


@bp.route("/")
@login_required
def dashboard():
    db = get_db()
    ledgers = db.execute("SELECT * FROM ledgers ORDER BY name").fetchall()
    rows = [(l, ledger_balance(db, l["id"])) for l in ledgers]
    return render_template("dashboard.html", rows=rows)


@bp.route("/ledger/<int:ledger_id>")
@login_required
def ledger_detail(ledger_id):
    db = get_db()
    ledger = db.execute("SELECT * FROM ledgers WHERE id = ?", (ledger_id,)).fetchone()
    if ledger is None:
        abort(404)
    contributions = db.execute(
        """SELECT c.*, p.name AS person_name FROM contributions c
           LEFT JOIN people p ON p.id = c.person_id
           WHERE c.ledger_id = ? ORDER BY c.contributed_on DESC, c.id DESC""",
        (ledger_id,),
    ).fetchall()
    expenses = db.execute(
        """SELECT e.*, p.name AS paid_by_name,
                  (SELECT COUNT(*) FROM receipts r WHERE r.expense_id = e.id) AS receipt_count
           FROM expenses e LEFT JOIN people p ON p.id = e.paid_by_id
           WHERE e.ledger_id = ? ORDER BY e.spent_on DESC, e.id DESC""",
        (ledger_id,),
    ).fetchall()
    people = db.execute("SELECT * FROM people ORDER BY name").fetchall()
    balance = ledger_balance(db, ledger_id)
    return render_template(
        "ledger.html",
        ledger=ledger,
        contributions=contributions,
        expenses=expenses,
        people=people,
        balance=balance,
    )


def _get_or_create_person(db, name):
    name = (name or "").strip()
    if not name:
        return None
    row = db.execute("SELECT id FROM people WHERE name = ?", (name,)).fetchone()
    if row:
        return row["id"]
    cur = db.execute("INSERT INTO people (name) VALUES (?)", (name,))
    db.commit()
    return cur.lastrowid


@bp.route("/ledger/<int:ledger_id>/contribution/new", methods=["POST"])
@login_required
def add_contribution(ledger_id):
    db = get_db()
    person_id = _get_or_create_person(db, request.form.get("person_name"))
    db.execute(
        "INSERT INTO contributions (ledger_id, person_id, amount, contributed_on, note) VALUES (?, ?, ?, ?, ?)",
        (
            ledger_id,
            person_id,
            request.form["amount"],
            request.form["contributed_on"],
            request.form.get("note", "").strip() or None,
        ),
    )
    db.commit()
    flash("Contribution added.")
    return redirect(url_for("ledger.ledger_detail", ledger_id=ledger_id))


def _allowed_receipt(filename):
    ext = filename.rsplit(".", 1)[-1].lower() if "." in filename else ""
    return ext in current_app.config["ALLOWED_RECEIPT_EXTENSIONS"]


@bp.route("/ledger/<int:ledger_id>/expense/new", methods=["POST"])
@login_required
def add_expense(ledger_id):
    db = get_db()
    paid_by_id = _get_or_create_person(db, request.form.get("paid_by_name"))
    cur = db.execute(
        """INSERT INTO expenses (ledger_id, category, amount, paid_by_id, status, spent_on, note, lhdn_claimable)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
        (
            ledger_id,
            request.form["category"],
            request.form["amount"],
            paid_by_id,
            request.form.get("status", "").strip() or None,
            request.form["spent_on"],
            request.form.get("note", "").strip() or None,
            1 if request.form.get("lhdn_claimable") else 0,
        ),
    )
    expense_id = cur.lastrowid

    files = request.files.getlist("receipts")
    for file in files:
        if not file or not file.filename:
            continue
        if not _allowed_receipt(file.filename):
            flash(f"Skipped {file.filename}: unsupported file type.")
            continue
        safe_name = secure_filename(file.filename)
        stored_name = f"{expense_id}-{uuid.uuid4().hex[:8]}-{safe_name}"
        file.save(os.path.join(current_app.config["RECEIPTS_DIR"], stored_name))
        db.execute(
            "INSERT INTO receipts (expense_id, file_path, original_name) VALUES (?, ?, ?)",
            (expense_id, stored_name, safe_name),
        )

    db.commit()
    flash("Expense added.")
    return redirect(url_for("ledger.ledger_detail", ledger_id=ledger_id))


@bp.route("/expense/<int:expense_id>/receipts")
@login_required
def expense_receipts(expense_id):
    db = get_db()
    expense = db.execute("SELECT * FROM expenses WHERE id = ?", (expense_id,)).fetchone()
    if expense is None:
        abort(404)
    receipts = db.execute("SELECT * FROM receipts WHERE expense_id = ? ORDER BY id", (expense_id,)).fetchall()
    return render_template("receipts.html", expense=expense, receipts=receipts)


@bp.route("/receipt/<int:receipt_id>")
@login_required
def view_receipt(receipt_id):
    db = get_db()
    receipt = db.execute("SELECT * FROM receipts WHERE id = ?", (receipt_id,)).fetchone()
    if receipt is None:
        abort(404)
    return send_from_directory(
        current_app.config["RECEIPTS_DIR"],
        receipt["file_path"],
        download_name=receipt["original_name"] or receipt["file_path"],
    )


@bp.route("/lhdn")
@login_required
def lhdn_report():
    db = get_db()
    year = request.args.get("year", "").strip()
    query = """SELECT e.*, p.name AS paid_by_name, l.name AS ledger_name,
                      (SELECT COUNT(*) FROM receipts r WHERE r.expense_id = e.id) AS receipt_count
               FROM expenses e
               LEFT JOIN people p ON p.id = e.paid_by_id
               JOIN ledgers l ON l.id = e.ledger_id
               WHERE e.lhdn_claimable = 1"""
    params = []
    if year:
        query += " AND strftime('%Y', e.spent_on) = ?"
        params.append(year)
    query += " ORDER BY e.spent_on DESC"
    expenses = db.execute(query, params).fetchall()
    total = sum(row["amount"] for row in expenses)
    return render_template("lhdn_report.html", expenses=expenses, total=total, year=year)