from datetime import datetime
from flask import request
from flask_jwt_extended import jwt_required, get_jwt_identity
from sqlalchemy import desc, func
from app.api import api_bp
from app.extensions import db
from app.models.transactions import Debit
from app.models.master import SavingsPlanMember, Cashier
from app.models.user import User
from app.utils.auth import role_required, permission_required
from app.utils.pagination import paginate
from app.utils.response import ok, fail


def _build_debit_query(search, transaction_type, member_ids=None):
    query = db.session.query(
        Debit,
        SavingsPlanMember.member_short_name,
        Cashier.name.label("cashier_name"),
    ).outerjoin(SavingsPlanMember, Debit.member_id == SavingsPlanMember.member_id)
    query = query.outerjoin(Cashier, Debit.cashier_id == Cashier.id)

    if member_ids:
        query = query.filter(Debit.member_id.in_(member_ids))

    if search:
        query = query.filter(
            (Debit.transaction_id.ilike(f"%{search}%"))
            | (Debit.member_id.ilike(f"%{search}%"))
            | (SavingsPlanMember.member_short_name.ilike(f"%{search}%"))
            | (Cashier.name.ilike(f"%{search}%"))
            | (Debit.transaction_type.ilike(f"%{search}%"))
        )

    if transaction_type:
        query = query.filter(Debit.transaction_type == transaction_type)

    return query


def _debits_summary(query):
    total = query.with_entities(func.coalesce(func.sum(Debit.amount), 0)).scalar()
    by_type = dict(
        query.with_entities(Debit.transaction_type, func.coalesce(func.sum(Debit.amount), 0))
        .group_by(Debit.transaction_type)
        .all()
    )
    return {
        "totalAmount": float(total or 0),
        "loanDisbursementAmount": float(by_type.get("loan_disbursement", 0) or 0),
        "charityAmount": float(by_type.get("charity", 0) or 0),
        "savingsReleaseAmount": float(by_type.get("savings_release", 0) or 0),
    }


def _ensure_editor_cashier_access(user: User, cashier_id: int | None):
    if not user or user.access_level != "editor":
        return None
    cashier_ids = [c.id for c in Cashier.query.filter_by(user_id=user.id).all()]
    if not cashier_ids:
        return fail("No cashier assigned to your account", 403)
    if cashier_id is not None and cashier_id not in cashier_ids:
        return fail("You do not have access to this cashier", 403)
    return None


@api_bp.post("/debits")
@permission_required("debits.create")
def create_debit():
    data = request.json or {}
    user_id = get_jwt_identity()
    user = User.query.get(user_id)
    cashier_id = data.get("cashier_id")
    denied = _ensure_editor_cashier_access(user, cashier_id)
    if denied:
        return denied

    debit = Debit(
        transaction_id=data["transaction_id"],
        member_id=data.get("member_id"),
        transaction_type=data["transaction_type"],
        amount=float(data["amount"]),
        cashier_id=data["cashier_id"],
        loan_id=data.get("loan_id"),
        charity_id=data.get("charity_id"),
        release_request_id=data.get("release_request_id"),
        transaction_date=datetime.strptime(data["transaction_date"], "%Y-%m-%d").date(),
        notes=data.get("notes"),
    )
    db.session.add(debit)
    db.session.commit()
    return ok({"id": debit.id}, "Debit created", 201)


@api_bp.get("/debits")
@permission_required("debits.view")
def list_debits():
    page = request.args.get("page", 1, type=int)
    page_size = request.args.get("page_size", 20, type=int)
    search = request.args.get("search", "")
    transaction_type = request.args.get("transaction_type")
    sort_by = request.args.get("sort", "transaction_date")
    order = request.args.get("order", "desc")

    base_query = Debit.query
    user_id = get_jwt_identity()
    user = User.query.get(user_id)
    denied = _ensure_editor_cashier_access(user, None)
    if denied:
        return denied
    if user and user.access_level == "editor":
        cashier_ids = [c.id for c in Cashier.query.filter_by(user_id=user.id).all()]
        base_query = base_query.filter(Debit.cashier_id.in_(cashier_ids))

    summary = _debits_summary(
        base_query.filter(Debit.transaction_type == transaction_type) if transaction_type else base_query
    )

    query = _build_debit_query(search, transaction_type)
    if user and user.access_level == "editor":
        cashier_ids = [c.id for c in Cashier.query.filter_by(user_id=user.id).all()]
        query = query.filter(Debit.cashier_id.in_(cashier_ids))

    if sort_by in ["transaction_id", "member_id", "transaction_type", "amount", "transaction_date", "created_at"]:
        column = getattr(Debit, sort_by)
    elif sort_by == "cashier_name":
        column = Cashier.name
    elif sort_by == "member_short_name":
        column = SavingsPlanMember.member_short_name
    else:
        column = Debit.transaction_date

    query = query.order_by(desc(column)) if order == "desc" else query.order_by(column)

    paged = paginate(query, page, page_size)
    data = [
        {
            "id": debit.id,
            "transaction_id": debit.transaction_id,
            "member_id": debit.member_id,
            "member_short_name": member_short_name,
            "transaction_type": debit.transaction_type,
            "amount": debit.amount,
            "cashier_id": debit.cashier_id,
            "cashier_name": cashier_name,
            "loan_id": debit.loan_id,
            "charity_id": debit.charity_id,
            "release_request_id": debit.release_request_id,
            "transaction_date": debit.transaction_date.strftime("%Y-%m-%d"),
            "notes": debit.notes,
            "created_at": debit.created_at.strftime("%Y-%m-%d %H:%M:%S"),
        }
        for debit, member_short_name, cashier_name in paged["items"]
    ]

    return ok(
        {
            "debits": data,
            "page": paged["page"],
            "pages": paged["pages"],
            "total": paged["total"],
            "page_size": paged["page_size"],
            "summary": summary,
        }
    )


@api_bp.get("/debits/my")
@jwt_required()
def list_my_debits():
    user_id = get_jwt_identity()
    member_ids = [m.member_id for m in SavingsPlanMember.query.filter_by(user_id=user_id).all()]
    if not member_ids:
        return ok({"debits": [], "page": 1, "pages": 0, "total": 0, "page_size": 20, "summary": {}})

    page = request.args.get("page", 1, type=int)
    page_size = request.args.get("page_size", 20, type=int)
    search = request.args.get("search", "")
    transaction_type = request.args.get("transaction_type")

    base_query = Debit.query.filter(Debit.member_id.in_(member_ids))
    if transaction_type:
        base_query = base_query.filter(Debit.transaction_type == transaction_type)
    summary = _debits_summary(base_query)

    query = _build_debit_query(search, transaction_type, member_ids)
    query = query.order_by(desc(Debit.transaction_date))
    paged = paginate(query, page, page_size)
    data = [
        {
            "id": debit.id,
            "transaction_id": debit.transaction_id,
            "member_id": debit.member_id,
            "member_short_name": member_short_name,
            "transaction_type": debit.transaction_type,
            "amount": debit.amount,
            "cashier_id": debit.cashier_id,
            "cashier_name": cashier_name,
            "loan_id": debit.loan_id,
            "charity_id": debit.charity_id,
            "release_request_id": debit.release_request_id,
            "transaction_date": debit.transaction_date.strftime("%Y-%m-%d"),
            "notes": debit.notes,
            "created_at": debit.created_at.strftime("%Y-%m-%d %H:%M:%S"),
        }
        for debit, member_short_name, cashier_name in paged["items"]
    ]
    return ok(
        {
            "debits": data,
            "page": paged["page"],
            "pages": paged["pages"],
            "total": paged["total"],
            "page_size": paged["page_size"],
            "summary": summary,
        }
    )


@api_bp.get("/debits/<int:debit_id>")
@permission_required("debits.view")
def get_debit(debit_id):
    debit = Debit.query.get_or_404(debit_id)
    data = {
        "id": debit.id,
        "transaction_id": debit.transaction_id,
        "member_id": debit.member_id,
        "transaction_type": debit.transaction_type,
        "amount": debit.amount,
        "cashier_id": debit.cashier_id,
        "loan_id": debit.loan_id,
        "charity_id": debit.charity_id,
        "release_request_id": debit.release_request_id,
        "transaction_date": debit.transaction_date.strftime("%Y-%m-%d"),
        "notes": debit.notes,
        "created_at": debit.created_at.strftime("%Y-%m-%d %H:%M:%S"),
    }
    return ok(data)


@api_bp.put("/debits/<int:debit_id>")
@permission_required("debits.update")
def update_debit(debit_id):
    user_id = get_jwt_identity()
    user = User.query.get(user_id)
    debit = Debit.query.get_or_404(debit_id)
    data = request.json or {}

    denied = _ensure_editor_cashier_access(user, debit.cashier_id)
    if denied:
        return denied

    if "member_id" in data:
        debit.member_id = data["member_id"]
    if "transaction_type" in data:
        debit.transaction_type = data["transaction_type"]
    if "amount" in data:
        debit.amount = data["amount"]
    if "cashier_id" in data:
        denied = _ensure_editor_cashier_access(user, data["cashier_id"])
        if denied:
            return denied
        debit.cashier_id = data["cashier_id"]
    if "transaction_date" in data:
        debit.transaction_date = datetime.strptime(data["transaction_date"], "%Y-%m-%d").date()
    if "notes" in data:
        debit.notes = data["notes"]

    db.session.commit()
    return ok({"id": debit.id}, "Debit updated")


@api_bp.delete("/debits/<int:debit_id>")
@permission_required("debits.delete")
def delete_debit(debit_id):
    user_id = get_jwt_identity()
    user = User.query.get(user_id)
    debit = Debit.query.get_or_404(debit_id)
    denied = _ensure_editor_cashier_access(user, debit.cashier_id)
    if denied:
        return denied
    db.session.delete(debit)
    db.session.commit()
    return ok(message="Debit deleted")
