from decimal import Decimal
import re

from sqlalchemy import func
from sqlalchemy.orm import Session

from app.models import EventProduct, Expense, Invoice, Payment, Product, StaffAllocation


def next_public_id(db: Session, model, prefix: str) -> str:
    last_id = db.query(func.max(model.id)).scalar() or 0
    return f"{prefix}-{last_id + 1:04d}"


def slugify(value: str) -> str:
    text = (value or "").strip().lower()
    text = re.sub(r"[^a-z0-9]+", "-", text)
    return text.strip("-") or "product"


def unique_product_slug(db: Session, base: str, exclude_id: int | None = None) -> str:
    root = slugify(base)[:160] or "product"
    candidate = root
    suffix = 2
    while True:
        query = db.query(Product).filter(Product.slug == candidate)
        if exclude_id is not None:
            query = query.filter(Product.id != exclude_id)
        if query.first() is None:
            return candidate
        candidate = f"{root}-{suffix}"
        suffix += 1


def money(value) -> Decimal:
    if value is None:
        return Decimal("0.00")
    return Decimal(str(value)).quantize(Decimal("0.01"))


def line_amount(quantity, unit_price, discount=0) -> Decimal:
    return money(money(quantity) * money(unit_price) - money(discount))


def invoice_totals(invoice: Invoice) -> None:
    invoice.amount_paid = money(invoice.amount_paid)
    invoice.final_amount = money(invoice.final_amount)
    invoice.balance_amount = money(invoice.final_amount - invoice.amount_paid)
    if invoice.balance_amount <= 0 and invoice.final_amount > 0:
        invoice.payment_status = "Paid"
        invoice.status = "Paid"
    elif invoice.amount_paid > 0:
        invoice.payment_status = "Partially Paid"
        if invoice.status not in {"Cancelled", "Overdue"}:
            invoice.status = "Partially Paid"
    else:
        invoice.payment_status = "Unpaid"


def event_profitability(db: Session, event_id: int) -> dict:
    package = db.query(EventProduct).filter(EventProduct.event_id == event_id).all()
    package_amt = money(0)
    for item in package:
        line = money(item.amount)
        if line <= 0:
            line = line_amount(item.quantity, item.unit_price, item.discount)
        package_amt += line

    billed = db.query(func.coalesce(func.sum(Invoice.final_amount), 0)).filter(
        Invoice.event_id == event_id, Invoice.status != "Cancelled"
    ).scalar()
    billed_amt = money(billed)
    # Until an invoice exists, estimate revenue from selected products.
    revenue_amt = billed_amt if billed_amt > 0 else package_amt
    revenue_source = "invoice" if billed_amt > 0 else ("products" if package_amt > 0 else "none")

    staff_cost = db.query(func.coalesce(func.sum(StaffAllocation.total_staff_cost), 0)).filter(
        StaffAllocation.event_id == event_id, StaffAllocation.status != "Cancelled"
    ).scalar()
    expenses = (
        db.query(Expense.category, func.coalesce(func.sum(Expense.amount), 0))
        .filter(Expense.event_id == event_id, Expense.approval_status != "Rejected")
        .group_by(Expense.category)
        .all()
    )
    expense_map = {name: money(total) for name, total in expenses}
    other_expenses = sum((amt for amt in expense_map.values()), money(0))
    travel = expense_map.get("Travel", money(0)) + expense_map.get("Fuel", money(0))
    salary_exp = expense_map.get("Staff Salary", money(0))
    total_cost = money(staff_cost) + other_expenses
    net = money(revenue_amt - total_cost)
    margin = money((net / revenue_amt) * 100) if revenue_amt > 0 else money(0)
    paid = db.query(func.coalesce(func.sum(Payment.amount), 0)).filter(Payment.event_id == event_id).scalar()
    return {
        "package_amount": package_amt,
        "final_billed_amount": billed_amt,
        "revenue_amount": revenue_amt,
        "revenue_source": revenue_source,
        "travel_expense": travel,
        "staff_cost": money(staff_cost) + salary_exp,
        "other_expenses": money(other_expenses - travel - salary_exp),
        "total_event_cost": total_cost,
        "amount_paid": money(paid),
        "balance_amount": money(revenue_amt - money(paid)),
        "net_profit": net,
        "profit_margin": margin,
    }