from datetime import datetime, time, timezone
from decimal import Decimal
from uuid import UUID

from sqlalchemy import func, select
from sqlalchemy.orm import Session

from app.models.expense_income import Expense, OtherIncome
from app.models.inventory import BranchInventory, StockAdjustment
from app.models.membership import Membership, MembershipBranch
from app.models.payroll import PayrollRun
from app.models.product import Product
from app.models.purchase import Purchase, PurchaseItem
from app.models.receivable import Receivable
from app.models.sale import Sale, SaleItem
from app.schemas.report import ReportFilters


ZERO = Decimal("0")


def allowed_branch_ids(db: Session, membership: Membership, business_id: UUID, requested: UUID | None) -> list[UUID] | None:
    if requested:
        if membership.all_branches:
            return [requested]
        assigned = db.scalar(select(MembershipBranch.id).where(MembershipBranch.membership_id == membership.id, MembershipBranch.branch_id == requested))
        if not assigned:
            raise PermissionError("You do not have access to this branch.")
        return [requested]
    if membership.all_branches:
        return None
    return list(db.scalars(select(MembershipBranch.branch_id).where(MembershipBranch.membership_id == membership.id)))


def _dates(column, filters: ReportFilters):
    clauses = []
    if filters.start_date:
        clauses.append(column >= filters.start_date)
    if filters.end_date:
        clauses.append(column <= filters.end_date)
    return clauses


def _dt_dates(column, filters: ReportFilters):
    clauses = []
    if filters.start_date:
        clauses.append(column >= datetime.combine(filters.start_date, time.min, tzinfo=timezone.utc))
    if filters.end_date:
        clauses.append(column <= datetime.combine(filters.end_date, time.max, tzinfo=timezone.utc))
    return clauses


def scalar(db: Session, statement) -> Decimal:
    return Decimal(db.scalar(statement) or ZERO)


def sales_summary(db: Session, business_id: UUID, branch_ids: list[UUID] | None, filters: ReportFilters):
    query = select(func.count(Sale.id), func.coalesce(func.sum(Sale.total), ZERO), func.coalesce(func.sum(Sale.amount_paid), ZERO), func.coalesce(func.sum(Sale.balance), ZERO)).where(Sale.business_id == business_id, Sale.lifecycle_status == "COMPLETED", *_dt_dates(Sale.sale_datetime, filters))
    if branch_ids is not None: query = query.where(Sale.branch_id.in_(branch_ids))
    if filters.staff_id: query = query.where(Sale.created_by == filters.staff_id)
    if filters.customer_id: query = query.where(Sale.customer_id == filters.customer_id)
    count, total, paid, balance = db.execute(query).one()
    return {"sale_count": count, "revenue": total, "amount_paid": paid, "outstanding": balance}


def profit_loss(db: Session, business_id: UUID, branch_ids: list[UUID] | None, filters: ReportFilters):
    sales = sales_summary(db, business_id, branch_ids, filters)
    cogs_query = select(func.coalesce(func.sum(SaleItem.quantity_in_base * Product.purchase_price), ZERO)).join(Sale, Sale.id == SaleItem.sale_id).join(Product, Product.id == SaleItem.product_id).where(Sale.business_id == business_id, Sale.lifecycle_status == "COMPLETED", *_dt_dates(Sale.sale_datetime, filters))
    if branch_ids is not None: cogs_query = cogs_query.where(Sale.branch_id.in_(branch_ids))
    expenses_query = select(func.coalesce(func.sum(Expense.amount), ZERO)).where(Expense.business_id == business_id, Expense.status == "ACTIVE", *_dates(Expense.expense_date, filters))
    income_query = select(func.coalesce(func.sum(OtherIncome.amount), ZERO)).where(OtherIncome.business_id == business_id, OtherIncome.status == "ACTIVE", *_dates(OtherIncome.income_date, filters))
    if branch_ids is not None:
        expenses_query = expenses_query.where(Expense.branch_id.in_(branch_ids))
        income_query = income_query.where(OtherIncome.branch_id.in_(branch_ids))
    cogs = scalar(db, cogs_query)
    expenses = scalar(db, expenses_query)
    other_income = scalar(db, income_query)
    gross_profit = Decimal(sales["revenue"]) - cogs
    return {"revenue": sales["revenue"], "cost_of_goods_sold": cogs, "gross_profit": gross_profit, "expenses": expenses, "other_income": other_income, "net_profit": gross_profit - expenses + other_income, "cogs_basis": "Current product purchase_price; historical cost snapshots are not stored."}


def sales_by_product(db: Session, business_id: UUID, branch_ids: list[UUID] | None, filters: ReportFilters):
    query = select(SaleItem.product_id, Product.name, func.sum(SaleItem.quantity), func.sum(SaleItem.line_total)).join(Sale, Sale.id == SaleItem.sale_id).join(Product, Product.id == SaleItem.product_id).where(Sale.business_id == business_id, Sale.lifecycle_status == "COMPLETED", *_dt_dates(Sale.sale_datetime, filters)).group_by(SaleItem.product_id, Product.name).order_by(func.sum(SaleItem.line_total).desc())
    if branch_ids is not None: query = query.where(Sale.branch_id.in_(branch_ids))
    if filters.product_id: query = query.where(SaleItem.product_id == filters.product_id)
    return [{"product_id": row[0], "product_name": row[1], "quantity": row[2], "revenue": row[3]} for row in db.execute(query)]


def purchases_summary(db: Session, business_id: UUID, branch_ids: list[UUID] | None, filters: ReportFilters):
    query = select(func.count(Purchase.id), func.coalesce(func.sum(Purchase.total), ZERO), func.coalesce(func.sum(Purchase.amount_paid), ZERO), func.coalesce(func.sum(Purchase.balance), ZERO)).where(Purchase.business_id == business_id, *_dates(Purchase.purchase_date, filters))
    if branch_ids is not None: query = query.where(Purchase.branch_id.in_(branch_ids))
    if filters.supplier_id: query = query.where(Purchase.supplier_id == filters.supplier_id)
    count, total, paid, balance = db.execute(query).one()
    return {"purchase_count": count, "total": total, "amount_paid": paid, "outstanding": balance}



def payables_summary(
    db: Session,
    business_id: UUID,
    branch_ids: list[UUID] | None,
    filters: ReportFilters,
):
    query = select(
        func.count(Purchase.id),
        func.coalesce(func.sum(Purchase.total), ZERO),
        func.coalesce(func.sum(Purchase.amount_paid), ZERO),
        func.coalesce(func.sum(Purchase.balance), ZERO),
    ).where(
        Purchase.business_id == business_id,
        Purchase.lifecycle_status == "RECEIVED",
        Purchase.balance > ZERO,
        *_dates(Purchase.purchase_date, filters),
    )

    if branch_ids is not None:
        query = query.where(Purchase.branch_id.in_(branch_ids))

    if filters.supplier_id:
        query = query.where(
            Purchase.supplier_id == filters.supplier_id
        )

    count, total, paid, outstanding = db.execute(query).one()

    return {
        "purchase_count": count,
        "total": total,
        "amount_paid": paid,
        "outstanding": outstanding,
    }

def receivables_aging(db: Session, business_id: UUID, filters: ReportFilters):
    query = select(Receivable).where(Receivable.business_id == business_id)
    if filters.customer_id: query = query.where(Receivable.customer_id == filters.customer_id)
    rows = list(db.scalars(query))
    today = filters.end_date or datetime.now(timezone.utc).date()
    buckets = {"current": ZERO, "1_30": ZERO, "31_60": ZERO, "61_90": ZERO, "90_plus": ZERO}
    for row in rows:
        if row.balance <= ZERO: continue
        days = max(0, (today - (row.due_date or row.created_at.date())).days)
        key = "current" if days == 0 else "1_30" if days <= 30 else "31_60" if days <= 60 else "61_90" if days <= 90 else "90_plus"
        buckets[key] += row.balance
    return {"total_outstanding": sum(buckets.values(), ZERO), "buckets": buckets}


def inventory_value(db: Session, business_id: UUID, branch_ids: list[UUID] | None):
    query = select(BranchInventory.product_id, func.sum(BranchInventory.quantity), Product.purchase_price).join(Product, Product.id == BranchInventory.product_id).where(BranchInventory.business_id == business_id).group_by(BranchInventory.product_id, Product.purchase_price)
    if branch_ids is not None: query = query.where(BranchInventory.branch_id.in_(branch_ids))
    rows = [{"product_id": product_id, "quantity": quantity, "unit_cost": cost, "value": Decimal(quantity) * Decimal(cost or ZERO)} for product_id, quantity, cost in db.execute(query)]
    return {"total_value": sum((row["value"] for row in rows), ZERO), "items": rows}


def stock_history(db: Session, business_id: UUID, branch_ids: list[UUID] | None, filters: ReportFilters):
    query = select(StockAdjustment).where(StockAdjustment.business_id == business_id, *_dt_dates(StockAdjustment.created_at, filters)).order_by(StockAdjustment.created_at.desc()).limit(200)
    if branch_ids is not None: query = query.where(StockAdjustment.branch_id.in_(branch_ids))
    if filters.product_id: query = query.where(StockAdjustment.product_id == filters.product_id)
    return list(db.scalars(query))