from datetime import datetime, timedelta
import calendar
from flask import request
from sqlalchemy import func, extract
from flask_jwt_extended import get_jwt_identity
from app.api import api_bp
from app.extensions import db
from app.models.master import Cashier, SavingsPlanMember, CharityType
from app.models.user import User
from app.models.transactions import CharityRequest, Credit, Loan, LoanRequest, Debit, ReleaseRequest
from app.utils.auth import permission_required
from app.utils.pagination import paginate
from app.utils.response import ok, fail


@api_bp.get("/reports/overall-summary")
@permission_required("reports.view")
def overall_summary():
    total_amount = db.session.query(func.sum(Credit.total_amount)).scalar() or 0
    total_savings = db.session.query(func.sum(Credit.savings_amount)).filter(Credit.transaction_type == "savings").scalar() or 0
    total_charity = db.session.query(func.sum(Credit.charity_amount)).filter(Credit.transaction_type.in_(["charity", "savings"])).scalar() or 0
    total_borrowed = db.session.query(func.sum(Loan.amount)).scalar() or 0
    total_loan_paid = db.session.query(func.sum(Credit.total_amount)).filter(Credit.transaction_type == "emi").scalar() or 0
    return ok({
        "total_amount": total_amount,
        "total_savings": total_savings,
        "total_charity": total_charity,
        "total_borrowed": total_borrowed,
        "total_loan_paid": total_loan_paid,
    })


def _active_member_ids():
    return [m[0] for m in db.session.query(SavingsPlanMember.member_id).filter(SavingsPlanMember.status == "active").all()]


@api_bp.get("/reports/summary-counts")
@permission_required("reports.view")
def summary_counts():
    active_members = db.session.query(func.count(SavingsPlanMember.id)).filter(SavingsPlanMember.status == "active").scalar() or 0
    inactive_members = db.session.query(func.count(SavingsPlanMember.id)).filter(SavingsPlanMember.status == "inactive").scalar() or 0
    active_loans = db.session.query(func.count(Loan.id)).filter(Loan.status == "active").scalar() or 0
    total_requested = db.session.query(func.sum(CharityRequest.amount)).filter(CharityRequest.status == "released").scalar() or 0
    return ok({
        "active_members": int(active_members),
        "inactive_members": int(inactive_members),
        "active_loans": int(active_loans),
        "total_requested": float(total_requested),
    })


@api_bp.get("/reports/monthly-trends")
@permission_required("reports.view")
def monthly_trends():
    year = datetime.now().year
    months = list(range(1, 13))
    result = {
        "months": [datetime(2000, month, 1).strftime("%B") for month in months],
        "savings": [0] * 12,
        "charity": [0] * 12,
        "loan": [0] * 12,
        "emi": [0] * 12,
    }

    savings_data = db.session.query(
        extract("month", Credit.transaction_date).label("month"),
        func.sum(Credit.savings_amount).label("amount"),
    ).filter(
        Credit.transaction_type == "savings",
        extract("year", Credit.transaction_date) == year,
    ).group_by(extract("month", Credit.transaction_date)).all()
    for month, amount in savings_data:
        result["savings"][int(month) - 1] = float(amount)

    charity_data = db.session.query(
        extract("month", Credit.transaction_date).label("month"),
        func.sum(Credit.charity_amount).label("amount"),
    ).filter(
        Credit.transaction_type == "charity",
        extract("year", Credit.transaction_date) == year,
    ).group_by(extract("month", Credit.transaction_date)).all()
    for month, amount in charity_data:
        result["charity"][int(month) - 1] = float(amount)

    loan_data = db.session.query(
        extract("month", Loan.disbursement_date).label("month"),
        func.sum(Loan.amount).label("amount"),
    ).filter(
        extract("year", Loan.disbursement_date) == year,
    ).group_by(extract("month", Loan.disbursement_date)).all()
    for month, amount in loan_data:
        result["loan"][int(month) - 1] = float(amount)

    emi_data = db.session.query(
        extract("month", Credit.transaction_date).label("month"),
        func.sum(Credit.total_amount).label("amount"),
    ).filter(
        Credit.transaction_type == "emi",
        extract("year", Credit.transaction_date) == year,
    ).group_by(extract("month", Credit.transaction_date)).all()
    for month, amount in emi_data:
        result["emi"][int(month) - 1] = float(amount) if amount is not None else 0.0

    return ok(result)


@api_bp.get("/reports/yearly-trends")
@permission_required("reports.view")
def yearly_trends():
    current_year = datetime.now().year
    years = list(range(current_year - 6, current_year + 1))
    result = {
        "years": years,
        "savings": [0] * 7,
        "charity": [0] * 7,
        "loan": [0] * 7,
        "emi": [0] * 7,
    }
    for i, year in enumerate(years):
        savings = db.session.query(func.sum(Credit.savings_amount)).filter(
            Credit.transaction_type == "savings",
            extract("year", Credit.transaction_date) == year,
        ).scalar() or 0
        result["savings"][i] = float(savings)

        charity = db.session.query(func.sum(Credit.charity_amount)).filter(
            Credit.transaction_type.in_(["charity", "savings"]),
            extract("year", Credit.transaction_date) == year,
        ).scalar() or 0
        result["charity"][i] = float(charity)

        loan = db.session.query(func.sum(Loan.amount)).filter(
            extract("year", Loan.disbursement_date) == year,
        ).scalar() or 0
        result["loan"][i] = float(loan)

        emi = db.session.query(func.sum(Credit.total_amount)).filter(
            Credit.transaction_type == "emi",
            extract("year", Credit.transaction_date) == year,
        ).scalar() or 0
        result["emi"][i] = float(emi)

    return ok(result)


@api_bp.get("/reports/active-members-summary")
@permission_required("reports.view")
def active_members_summary():
    active_member_ids = _active_member_ids()

    total_credit = db.session.query(func.sum(Credit.total_amount)).filter(
        Credit.member_id.in_(active_member_ids)
    ).scalar() or 0

    current_savings = db.session.query(func.sum(Credit.savings_amount)).filter(
        Credit.transaction_type == "savings",
        Credit.member_id.in_(active_member_ids)
    ).scalar() or 0

    total_charity = db.session.query(func.sum(Debit.amount)).filter(
        Debit.transaction_type == "charity"
    ).scalar() or 0

    overall_total_charity = db.session.query(func.sum(Credit.charity_amount)).filter(
        Credit.transaction_type.in_(["charity", "savings"])
    ).scalar() or 0
    charity_balance = (overall_total_charity or 0) - (total_charity or 0)

    charity_debit = db.session.query(func.sum(Debit.amount)).filter(
        Debit.transaction_type == "charity",
        Debit.member_id.in_(active_member_ids)
    ).scalar() or 0

    current_borrowed = db.session.query(func.sum(Loan.amount)).filter(
        Loan.member_id.in_(active_member_ids)
    ).scalar() or 0

    current_loan_paid = db.session.query(func.sum(Credit.total_amount)).filter(
        Credit.transaction_type == "emi",
        Credit.member_id.in_(active_member_ids)
    ).scalar() or 0

    current_outstanding = (current_borrowed or 0) - (current_loan_paid or 0)
    credit_balance = (current_savings or 0) + charity_balance - current_outstanding

    total_debit = db.session.query(func.sum(Debit.amount)).filter(
        Debit.member_id.in_(active_member_ids)
    ).scalar() or 0

    return ok({
        "total_credit": float(total_credit or 0),
        "current_savings": float(current_savings or 0),
        "total_charity": float(total_charity or 0),
        "charity_balance": float(charity_balance or 0),
        "charity_debit": float(charity_debit or 0),
        "current_outstanding": float(current_outstanding or 0),
        "total_debit": float(total_debit or 0),
        "credit_balance": float(credit_balance or 0),
    })


@api_bp.get("/reports/loan-summary")
@permission_required("reports.view")
def loan_summary():
    active_member_ids = _active_member_ids()
    current_borrowed = db.session.query(func.sum(Loan.amount)).filter(
        Loan.member_id.in_(active_member_ids)
    ).scalar() or 0
    current_loan_paid = db.session.query(func.sum(Credit.total_amount)).filter(
        Credit.transaction_type == "emi",
        Credit.member_id.in_(active_member_ids)
    ).scalar() or 0
    current_outstanding = (current_borrowed or 0) - (current_loan_paid or 0)
    return ok({
        "current_borrowed": float(current_borrowed or 0),
        "current_loan_paid": float(current_loan_paid or 0),
        "current_outstanding": float(current_outstanding or 0),
    })


@api_bp.get("/reports/cashier-summary")
@permission_required("cashier_summary.view")
def cashier_summary():
    active_member_ids = _active_member_ids()

    credit_summary = db.session.query(
        Cashier.id,
        Cashier.name,
        func.sum(Credit.total_amount).label("total_credit"),
    ).join(Credit, Cashier.id == Credit.cashier_id).filter(Credit.member_id.in_(active_member_ids)).group_by(Cashier.id, Cashier.name).all()

    debit_summary = db.session.query(
        Cashier.id,
        Cashier.name,
        func.sum(Debit.amount).label("total_debit"),
    ).join(Debit, Cashier.id == Debit.cashier_id).filter(Debit.member_id.in_(active_member_ids)).group_by(Cashier.id, Cashier.name).all()

    cashier_data = {}
    for cashier_id, name, total_credit in credit_summary:
        cashier_data[cashier_id] = {"name": name, "total_credit": total_credit or 0, "total_debit": 0, "balance": 0}

    for cashier_id, name, total_debit in debit_summary:
        if cashier_id not in cashier_data:
            cashier_data[cashier_id] = {"name": name, "total_credit": 0, "total_debit": total_debit or 0, "balance": 0}
        else:
            cashier_data[cashier_id]["total_debit"] = total_debit or 0

    summary = []
    for cashier_id, data in cashier_data.items():
        balance = (data["total_credit"] or 0) - (data["total_debit"] or 0)
        summary.append({
            "id": cashier_id,
            "name": data["name"],
            "total_credit": data["total_credit"],
            "total_debit": data["total_debit"],
            "balance": balance,
        })

    grand_total_credit = sum([float(item["total_credit"] or 0) for item in summary])
    grand_total_debit = sum([float(item["total_debit"] or 0) for item in summary])
    summary.append({
        "id": "grand_total",
        "name": "GRAND TOTAL",
        "total_credit": grand_total_credit,
        "total_debit": grand_total_debit,
        "balance": grand_total_credit - grand_total_debit,
    })

    return ok(summary)


@api_bp.get("/reports/charity-analysis")
@permission_required("reports.view")
def charity_analysis():
    charity_data = db.session.query(
        CharityType.id,
        CharityType.charity_type,
        func.sum(CharityRequest.amount).label("requested_amount"),
        func.sum(CharityRequest.allotted_amount).label("allotted_amount"),
    ).join(CharityRequest, CharityType.id == CharityRequest.charity_type_id).filter(
        CharityRequest.status == "released"
    ).group_by(CharityType.id, CharityType.charity_type).all()

    result = []
    for charity_id, charity_type, requested_amount, allotted_amount in charity_data:
        result.append({
            "id": charity_id,
            "type": charity_type,
            "requested_amount": float(requested_amount) if requested_amount else 0,
            "allotted_amount": float(allotted_amount) if allotted_amount else 0,
        })

    return ok(result)


@api_bp.get("/reports/requests-statistics")
@permission_required("reports.view")
def requests_statistics():
    end_date = datetime.now()
    start_date = end_date - timedelta(days=365)

    result = {
        "loan_requests": {"pending": 0, "approved": 0, "rejected": 0, "released": 0, "total": 0},
        "charity_requests": {"pending": 0, "approved": 0, "rejected": 0, "released": 0, "total": 0},
        "release_requests": {"pending": 0, "approved": 0, "rejected": 0, "released": 0, "total": 0},
        "monthly_data": [],
    }

    loan_stats = db.session.query(LoanRequest.status, func.count(LoanRequest.id)).filter(
        LoanRequest.created_at.between(start_date, end_date)
    ).group_by(LoanRequest.status).all()
    for status, count in loan_stats:
        if status in result["loan_requests"]:
            result["loan_requests"][status] = int(count)
        result["loan_requests"]["total"] += int(count)

    charity_stats = db.session.query(CharityRequest.status, func.count(CharityRequest.id)).filter(
        CharityRequest.created_at.between(start_date, end_date)
    ).group_by(CharityRequest.status).all()
    for status, count in charity_stats:
        if status in result["charity_requests"]:
            result["charity_requests"][status] = int(count)
        result["charity_requests"]["total"] += int(count)

    release_stats = db.session.query(ReleaseRequest.status, func.count(ReleaseRequest.id)).filter(
        ReleaseRequest.created_at.between(start_date, end_date)
    ).group_by(ReleaseRequest.status).all()
    for status, count in release_stats:
        if status in result["release_requests"]:
            result["release_requests"][status] = int(count)
        result["release_requests"]["total"] += int(count)

    monthly_data = []
    for i in range(12):
        month_end = end_date - timedelta(days=30 * i)
        month_start = end_date - timedelta(days=30 * (i + 1))
        month_name = month_end.strftime("%b")

        loan_count = db.session.query(func.count(LoanRequest.id)).filter(
            LoanRequest.created_at.between(month_start, month_end)
        ).scalar() or 0
        charity_count = db.session.query(func.count(CharityRequest.id)).filter(
            CharityRequest.created_at.between(month_start, month_end)
        ).scalar() or 0
        release_count = db.session.query(func.count(ReleaseRequest.id)).filter(
            ReleaseRequest.created_at.between(month_start, month_end)
        ).scalar() or 0

        monthly_data.append({
            "month": month_name,
            "loan_requests": int(loan_count),
            "charity_requests": int(charity_count),
            "release_requests": int(release_count),
        })

    monthly_data.reverse()
    result["monthly_data"] = monthly_data

    return ok(result)


@api_bp.get("/reports/overall-savings-vs-loan")
@permission_required("reports.view")
def overall_savings_vs_loan():
    total_savings = db.session.query(func.sum(Credit.savings_amount)).filter(
        Credit.transaction_type == "savings"
    ).scalar() or 0
    total_loans = db.session.query(func.sum(Loan.amount)).scalar() or 0
    return ok({"total_savings": float(total_savings), "total_loans": float(total_loans)})


@api_bp.get("/reports/active-savings-vs-loan")
@permission_required("reports.view")
def active_savings_vs_loan():
    active_member_ids = _active_member_ids()
    current_savings = db.session.query(func.sum(Credit.savings_amount)).filter(
        Credit.transaction_type == "savings",
        Credit.member_id.in_(active_member_ids),
    ).scalar() or 0
    current_borrowed = db.session.query(func.sum(Loan.amount)).filter(
        Loan.member_id.in_(active_member_ids)
    ).scalar() or 0
    return ok({
        "total_savings": float(current_savings),
        "total_loans": float(current_borrowed),
    })


@api_bp.get("/reports/member-savings-summary")
@permission_required("admin.member_savings.view")
def member_savings_summary():
    status = request.args.get("status", "active")
    cashier_id = request.args.get("cashier_id", type=int)

    query = SavingsPlanMember.query
    if status in ["active", "inactive"]:
        query = query.filter(SavingsPlanMember.status == status)
    if cashier_id:
        query = query.filter(SavingsPlanMember.cashier_id == cashier_id)

    members = query.order_by(SavingsPlanMember.created_at.desc()).all()
    member_ids = [m.member_id for m in members]
    if not member_ids:
        return ok({"items": [], "grand_totals": {"savings": 0, "charity": 0, "loan": 0, "loan_paid": 0, "outstanding": 0}})

    savings_rows = db.session.query(
        Credit.member_id,
        func.sum(Credit.savings_amount).label("total"),
    ).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type == "savings",
    ).group_by(Credit.member_id).all()
    savings_map = {row.member_id: float(row.total or 0) for row in savings_rows}

    charity_rows = db.session.query(
        Credit.member_id,
        func.sum(Credit.charity_amount).label("total"),
    ).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type.in_(["charity", "savings"]),
    ).group_by(Credit.member_id).all()
    charity_map = {row.member_id: float(row.total or 0) for row in charity_rows}

    active_loans = Loan.query.filter(Loan.member_id.in_(member_ids), Loan.status == "active").all()
    active_loan_ids = [l.loan_id for l in active_loans]
    loan_rows = db.session.query(Loan.member_id, func.sum(Loan.amount).label("total")).filter(
        Loan.member_id.in_(member_ids), Loan.status == "active"
    ).group_by(Loan.member_id).all()
    loan_map = {row.member_id: float(row.total or 0) for row in loan_rows}

    loan_paid_map = {}
    if active_loan_ids:
        paid_rows = db.session.query(
            Loan.member_id,
            func.sum(Credit.total_amount).label("total"),
        ).join(Loan, Loan.loan_id == Credit.loan_id).filter(
            Credit.loan_id.in_(active_loan_ids),
            Credit.transaction_type == "emi",
        ).group_by(Loan.member_id).all()
        loan_paid_map = {row.member_id: float(row.total or 0) for row in paid_rows}

    items = []
    grand = {"savings": 0.0, "charity": 0.0, "loan": 0.0, "loan_paid": 0.0, "outstanding": 0.0}
    for m in members:
        savings_total = savings_map.get(m.member_id, 0.0)
        charity_total = charity_map.get(m.member_id, 0.0)
        loan_total = loan_map.get(m.member_id, 0.0)
        loan_paid_total = loan_paid_map.get(m.member_id, 0.0)
        outstanding = max(0.0, loan_total - loan_paid_total)

        items.append(
            {
                "member_id": m.member_id,
                "member_short_name": m.member_short_name,
                "cashier_id": m.cashier_id,
                "cashier_name": m.cashier.name if m.cashier else None,
                "status": m.status,
                "savings": savings_total,
                "charity": charity_total,
                "loan": loan_total,
                "loan_paid": loan_paid_total,
                "outstanding": outstanding,
            }
        )

        grand["savings"] += savings_total
        grand["charity"] += charity_total
        grand["loan"] += loan_total
        grand["loan_paid"] += loan_paid_total
        grand["outstanding"] += outstanding

    return ok({"items": items, "grand_totals": grand})


@api_bp.get("/reports/cashier-monthly-summary")
@permission_required("cashier_summary.view")
def cashier_monthly_summary():
    year = request.args.get("year", type=int) or datetime.now().year

    cashiers = Cashier.query.filter_by(status="active").order_by(Cashier.name.asc()).all()
    months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
    month_map = {i + 1: months[i] for i in range(12)}

    credit_rows = db.session.query(
        Credit.cashier_id,
        extract("month", Credit.transaction_date).label("month"),
        func.count(Credit.id).label("count"),
        func.sum(Credit.total_amount).label("amount"),
    ).filter(
        extract("year", Credit.transaction_date) == year,
    ).group_by(Credit.cashier_id, extract("month", Credit.transaction_date)).all()

    debit_rows = db.session.query(
        Debit.cashier_id,
        extract("month", Debit.transaction_date).label("month"),
        func.count(Debit.id).label("count"),
        func.sum(Debit.amount).label("amount"),
    ).filter(
        extract("year", Debit.transaction_date) == year,
    ).group_by(Debit.cashier_id, extract("month", Debit.transaction_date)).all()

    credit_map = {}
    for cashier_id, month, count, amount in credit_rows:
        credit_map[(cashier_id, int(month))] = {"count": int(count), "amount": float(amount or 0)}

    debit_map = {}
    for cashier_id, month, count, amount in debit_rows:
        debit_map[(cashier_id, int(month))] = {"count": int(count), "amount": float(amount or 0)}

    items = []
    for cashier in cashiers:
        monthly = {}
        for month_num in range(1, 13):
            c = credit_map.get((cashier.id, month_num), {"count": 0, "amount": 0.0})
            d = debit_map.get((cashier.id, month_num), {"count": 0, "amount": 0.0})
            monthly[month_map[month_num]] = {
                "month_num": month_num,
                "credits": c,
                "debits": d,
            }
        items.append({"cashier_id": cashier.id, "cashier_name": cashier.name, "monthly_data": monthly})

    return ok({"year": year, "months": months, "items": items})


def _member_current_balance(member_ids):
    if not member_ids:
        return {}
    credit_rows = db.session.query(
        Credit.member_id,
        func.sum(Credit.total_amount).label("total"),
    ).filter(Credit.member_id.in_(member_ids)).group_by(Credit.member_id).all()
    debit_rows = db.session.query(
        Debit.member_id,
        func.sum(Debit.amount).label("total"),
    ).filter(Debit.member_id.in_(member_ids)).group_by(Debit.member_id).all()
    credit_map = {row.member_id: float(row.total or 0) for row in credit_rows}
    debit_map = {row.member_id: float(row.total or 0) for row in debit_rows}
    balances = {}
    for member_id in member_ids:
        balances[member_id] = credit_map.get(member_id, 0.0) - debit_map.get(member_id, 0.0)
    return balances


@api_bp.get("/reports/cashier-overall-summary")
@permission_required("cashier_summary.view")
def cashier_overall_summary():
    cashiers = Cashier.query.filter_by(status="active").order_by(Cashier.name.asc()).all()
    overall_summary = []

    for cashier in cashiers:
        active_members = SavingsPlanMember.query.filter_by(cashier_id=cashier.id, status="active").all()
        member_ids = [m.member_id for m in active_members]

        balances = _member_current_balance(member_ids)
        total_current_balance = sum(balances.values())

        total_credits_processed = db.session.query(func.sum(Credit.total_amount)).filter(
            Credit.cashier_id == cashier.id,
            Credit.member_id.in_(member_ids) if member_ids else Credit.member_id == "__none__",
        ).scalar() or 0

        total_debits_processed = db.session.query(func.sum(Debit.amount)).filter(
            Debit.cashier_id == cashier.id,
            Debit.member_id.in_(member_ids) if member_ids else Debit.member_id == "__none__",
        ).scalar() or 0

        credit_count = db.session.query(func.count(Credit.id)).filter(
            Credit.cashier_id == cashier.id,
        ).scalar() or 0
        debit_count = db.session.query(func.count(Debit.id)).filter(
            Debit.cashier_id == cashier.id,
        ).scalar() or 0

        first_credit = db.session.query(func.min(Credit.transaction_date)).filter(Credit.cashier_id == cashier.id).scalar()
        first_debit = db.session.query(func.min(Debit.transaction_date)).filter(Debit.cashier_id == cashier.id).scalar()
        last_credit = db.session.query(func.max(Credit.transaction_date)).filter(Credit.cashier_id == cashier.id).scalar()
        last_debit = db.session.query(func.max(Debit.transaction_date)).filter(Debit.cashier_id == cashier.id).scalar()

        first_transaction = None
        last_transaction = None
        if first_credit and first_debit:
            first_transaction = min(first_credit, first_debit)
        else:
            first_transaction = first_credit or first_debit
        if last_credit and last_debit:
            last_transaction = max(last_credit, last_debit)
        else:
            last_transaction = last_credit or last_debit

        overall_summary.append(
            {
                "cashier_id": cashier.id,
                "cashier_name": cashier.name,
                "active_members_count": len(active_members),
                "total_current_balance": float(total_current_balance),
                "total_credits_processed": float(total_credits_processed or 0),
                "total_debits_processed": float(total_debits_processed or 0),
                "net_transactions": float((total_credits_processed or 0) - (total_debits_processed or 0)),
                "credit_count": int(credit_count),
                "debit_count": int(debit_count),
                "total_transactions": int(credit_count) + int(debit_count),
                "first_transaction": first_transaction.isoformat() if first_transaction else None,
                "last_transaction": last_transaction.isoformat() if last_transaction else None,
            }
        )

    return ok({"items": overall_summary})


@api_bp.get("/reports/cashier-overall-details")
@permission_required("cashier_summary.view")
def cashier_overall_details():
    cashier_id = request.args.get("cashier_id", type=int)
    detail_type = request.args.get("type")  # current_balance | credits | debits
    limit = request.args.get("limit", 15, type=int)

    if not cashier_id or not detail_type:
        return fail("Missing parameters", 400)

    details = []
    total_amount = 0.0

    if detail_type == "current_balance":
        active_members = SavingsPlanMember.query.filter_by(cashier_id=cashier_id, status="active").all()
        member_ids = [m.member_id for m in active_members]
        balances = _member_current_balance(member_ids)
        for member in active_members:
            balance = balances.get(member.member_id, 0.0)
            if balance != 0:
                details.append(
                    {
                        "member_id": member.member_id,
                        "member_name": member.member_short_name,
                        "current_balance": float(balance),
                        "effective_date": member.effective_date.isoformat() if member.effective_date else None,
                        "plan_type": member.plan_type.name if member.plan_type else None,
                    }
                )
                total_amount += float(balance)
        details.sort(key=lambda x: x["current_balance"], reverse=True)
        details = details[:limit]

    elif detail_type == "credits":
        credits = Credit.query.filter(Credit.cashier_id == cashier_id).order_by(Credit.transaction_date.desc()).limit(limit).all()
        for credit in credits:
            details.append(
                {
                    "transaction_id": credit.transaction_id,
                    "member_id": credit.member_id,
                    "member_name": credit.member.member_short_name if credit.member else None,
                    "amount": float(credit.total_amount),
                    "transaction_type": credit.transaction_type,
                    "date": credit.transaction_date.isoformat() if credit.transaction_date else None,
                }
            )
            total_amount += float(credit.total_amount or 0)

    elif detail_type == "debits":
        debits = Debit.query.filter(Debit.cashier_id == cashier_id).order_by(Debit.transaction_date.desc()).limit(limit).all()
        for debit in debits:
            details.append(
                {
                    "transaction_id": debit.transaction_id,
                    "member_id": debit.member_id,
                    "member_name": debit.member.member_short_name if debit.member else None,
                    "amount": float(debit.amount),
                    "transaction_type": debit.transaction_type,
                    "date": debit.transaction_date.isoformat() if debit.transaction_date else None,
                }
            )
            total_amount += float(debit.amount or 0)

    else:
        return fail("Invalid type", 400)

    return ok({"details": details, "total_amount": float(total_amount), "total_count": len(details)})


@api_bp.get("/reports/cashier-monthly-details")
@permission_required("cashier_summary.view")
def cashier_monthly_details():
    cashier_id = request.args.get("cashier_id", type=int)
    month = request.args.get("month", type=int)
    year = request.args.get("year", type=int)
    transaction_type = request.args.get("type")  # credit | debit

    if not cashier_id or not month or not year or transaction_type not in ["credit", "debit"]:
        return fail("Missing parameters", 400)

    details = []
    total_amount = 0.0

    if transaction_type == "credit":
        transactions = Credit.query.filter(
            Credit.cashier_id == cashier_id,
            extract("month", Credit.transaction_date) == month,
            extract("year", Credit.transaction_date) == year,
        ).order_by(Credit.transaction_date.desc()).all()
        for trans in transactions:
            details.append(
                {
                    "transaction_id": trans.transaction_id,
                    "member_id": trans.member_id,
                    "member_name": trans.member.member_short_name if trans.member else None,
                    "amount": float(trans.total_amount),
                    "transaction_type": trans.transaction_type,
                    "date": trans.transaction_date.isoformat() if trans.transaction_date else None,
                }
            )
            total_amount += float(trans.total_amount or 0)

    if transaction_type == "debit":
        transactions = Debit.query.filter(
            Debit.cashier_id == cashier_id,
            extract("month", Debit.transaction_date) == month,
            extract("year", Debit.transaction_date) == year,
        ).order_by(Debit.transaction_date.desc()).all()
        for trans in transactions:
            details.append(
                {
                    "transaction_id": trans.transaction_id,
                    "member_id": trans.member_id,
                    "member_name": trans.member.member_short_name if trans.member else None,
                    "amount": float(trans.amount),
                    "transaction_type": trans.transaction_type,
                    "date": trans.transaction_date.isoformat() if trans.transaction_date else None,
                }
            )
            total_amount += float(trans.amount or 0)

    return ok({"details": details, "total_amount": float(total_amount), "total_count": len(details)})


def _get_member_ids_for_user(user_id, membership_id=None):
    query = db.session.query(SavingsPlanMember.member_id).filter(
        SavingsPlanMember.user_id == user_id
    )
    if membership_id and membership_id != "all":
        query = query.filter(SavingsPlanMember.member_id == membership_id)
    return [m.member_id for m in query.all()]


def _get_member_ids_for_profile(actor: User, membership_id=None):
    if not actor:
        return []

    if membership_id and membership_id != "all":
        membership = SavingsPlanMember.query.filter_by(member_id=membership_id).first()
        if not membership:
            return []
        if actor.access_level != "administrator" and membership.user_id != actor.id:
            return []
        return [membership.member_id]

    if actor.access_level == "administrator":
        return [m.member_id for m in db.session.query(SavingsPlanMember.member_id).all()]

    return [m.member_id for m in db.session.query(SavingsPlanMember.member_id).filter(SavingsPlanMember.user_id == actor.id).all()]


@api_bp.get("/reports/profile-summary")
@permission_required("profile.view")
def profile_summary():
    user_id = get_jwt_identity()
    actor = User.query.get(user_id)
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_profile(actor, membership_id)
    if not member_ids:
        return ok({})

    total_savings = db.session.query(func.sum(Credit.savings_amount)).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type == "savings",
    ).scalar() or 0

    total_charity = db.session.query(func.sum(Credit.charity_amount)).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type.in_(["charity", "savings"]),
    ).scalar() or 0

    total_borrowed = db.session.query(func.sum(Loan.amount)).filter(
        Loan.member_id.in_(member_ids)
    ).scalar() or 0

    total_emi = db.session.query(func.sum(Credit.total_amount)).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type == "emi",
    ).scalar() or 0

    outstanding = max(0, (total_borrowed or 0) - (total_emi or 0))

    loan_requests = db.session.query(func.count(LoanRequest.id)).filter(
        LoanRequest.member_id.in_(member_ids)
    ).scalar() or 0
    charity_requests = db.session.query(func.count(CharityRequest.id)).filter(
        CharityRequest.member_id.in_(member_ids)
    ).scalar() or 0
    release_requests = db.session.query(func.count(ReleaseRequest.id)).filter(
        ReleaseRequest.member_id.in_(member_ids)
    ).scalar() or 0

    return ok({
        "total_savings": float(total_savings),
        "total_charity": float(total_charity),
        "total_borrowed": float(total_borrowed),
        "total_emi": float(total_emi),
        "outstanding": float(outstanding),
        "loan_requests": loan_requests,
        "charity_requests": charity_requests,
        "release_requests": release_requests,
    })


@api_bp.get("/reports/profile-monthly")
@permission_required("profile.view")
def profile_monthly():
    user_id = get_jwt_identity()
    actor = User.query.get(user_id)
    year = int(request.args.get("year", datetime.now().year))
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_profile(actor, membership_id)
    months = list(range(1, 13))
    result = {
        "months": [calendar.month_name[m] for m in months],
        "savings": [0] * 12,
        "charity": [0] * 12,
        "loans": [0] * 12,
        "emi": [0] * 12,
    }

    if not member_ids:
        return ok(result)

    savings_by_month = db.session.query(
        extract("month", Credit.transaction_date).label("month"),
        func.sum(Credit.savings_amount).label("savings"),
    ).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type == "savings",
        extract("year", Credit.transaction_date) == year,
        Credit.savings_amount.isnot(None),
    ).group_by(extract("month", Credit.transaction_date)).all()

    for month, amount in savings_by_month:
        if month and amount:
            result["savings"][int(month) - 1] = float(amount)

    charity_by_month = db.session.query(
        extract("month", Credit.transaction_date).label("month"),
        func.sum(Credit.charity_amount).label("charity"),
    ).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type.in_(["charity", "savings"]),
        extract("year", Credit.transaction_date) == year,
        Credit.charity_amount.isnot(None),
    ).group_by(extract("month", Credit.transaction_date)).all()

    for month, amount in charity_by_month:
        if month and amount:
            result["charity"][int(month) - 1] = float(amount)

    loans_by_month = db.session.query(
        extract("month", Loan.disbursement_date).label("month"),
        func.sum(Loan.amount).label("loan_amount"),
    ).filter(
        Loan.member_id.in_(member_ids),
        Loan.status.in_(["approved", "active", "disbursed", "closed"]),
        extract("year", Loan.disbursement_date) == year,
        Loan.disbursement_date.isnot(None),
    ).group_by(extract("month", Loan.disbursement_date)).all()

    for month, amount in loans_by_month:
        if month and amount:
            result["loans"][int(month) - 1] = float(amount)

    emi_by_month = db.session.query(
        extract("month", Credit.transaction_date).label("month"),
        func.sum(Credit.total_amount).label("emi_amount"),
    ).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type == "emi",
        extract("year", Credit.transaction_date) == year,
    ).group_by(extract("month", Credit.transaction_date)).all()

    for month, amount in emi_by_month:
        if month and amount:
            result["emi"][int(month) - 1] = float(amount)

    return ok(result)


@api_bp.get("/reports/profile-savings-vs-loan")
@permission_required("profile.view")
def profile_savings_vs_loan():
    user_id = get_jwt_identity()
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_user(user_id, membership_id)
    if not member_ids:
        return ok({"labels": ["Savings", "Loans", "Charity"], "values": [0, 0, 0]})

    total_savings = db.session.query(func.sum(Credit.savings_amount)).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type == "savings",
    ).scalar() or 0

    total_loans = db.session.query(func.sum(Loan.amount)).filter(
        Loan.member_id.in_(member_ids),
        Loan.status.in_(["approved", "active", "disbursed", "closed"]),
    ).scalar() or 0

    total_charity = db.session.query(func.sum(Credit.charity_amount)).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_type.in_(["charity", "savings"]),
    ).scalar() or 0

    return ok({
        "labels": ["Savings", "Loans", "Charity"],
        "values": [float(total_savings), float(total_loans), float(total_charity)],
    })


@api_bp.get("/reports/profile-requests-summary")
@permission_required("profile.view")
def profile_requests_summary():
    user_id = get_jwt_identity()
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_user(user_id, membership_id)
    if not member_ids:
        return ok({})

    loan_data = db.session.query(
        LoanRequest.status, func.count(LoanRequest.id)
    ).filter(
        LoanRequest.member_id.in_(member_ids)
    ).group_by(LoanRequest.status).all()

    charity_data = db.session.query(
        CharityRequest.status, func.count(CharityRequest.id)
    ).filter(
        CharityRequest.member_id.in_(member_ids)
    ).group_by(CharityRequest.status).all()

    release_data = db.session.query(
        ReleaseRequest.status, func.count(ReleaseRequest.id)
    ).filter(
        ReleaseRequest.member_id.in_(member_ids)
    ).group_by(ReleaseRequest.status).all()

    return ok({
        "loan_requests": {status: count for status, count in loan_data},
        "charity_requests": {status: count for status, count in charity_data},
        "release_requests": {status: count for status, count in release_data},
    })


@api_bp.get("/reports/profile-history/credits")
@permission_required("profile.view")
def profile_history_credits():
    user_id = get_jwt_identity()
    actor = User.query.get(user_id)
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_profile(actor, membership_id)
    if not member_ids:
        return ok({"items": [], "page": 1, "pages": 0, "total": 0, "page_size": 20})

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

    query = Credit.query.filter(Credit.member_id.in_(member_ids))
    if search:
        query = query.filter(
            (Credit.transaction_id.ilike(f"%{search}%"))
            | (Credit.member_id.ilike(f"%{search}%"))
            | (Credit.transaction_type.ilike(f"%{search}%"))
        )
    query = query.order_by(Credit.transaction_date.desc())
    paged = paginate(query, page, page_size)
    items = [
        {
            "id": credit.id,
            "transaction_id": credit.transaction_id,
            "member_id": credit.member_id,
            "transaction_type": credit.transaction_type,
            "total_amount": float(credit.total_amount or 0),
            "savings_amount": float(credit.savings_amount or 0),
            "charity_amount": float(credit.charity_amount or 0),
            "transaction_date": credit.transaction_date.isoformat() if credit.transaction_date else None,
            "created_at": credit.created_at.isoformat() if credit.created_at else None,
        }
        for credit in paged["items"]
    ]
    return ok(
        {
            "items": items,
            "page": paged["page"],
            "pages": paged["pages"],
            "total": paged["total"],
            "page_size": paged["page_size"],
        }
    )


@api_bp.get("/reports/profile-history/debits")
@permission_required("profile.view")
def profile_history_debits():
    user_id = get_jwt_identity()
    actor = User.query.get(user_id)
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_profile(actor, membership_id)
    if not member_ids:
        return ok({"items": [], "page": 1, "pages": 0, "total": 0, "page_size": 20})

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

    query = Debit.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}%"))
            | (Debit.transaction_type.ilike(f"%{search}%"))
        )
    query = query.order_by(Debit.transaction_date.desc())
    paged = paginate(query, page, page_size)
    items = [
        {
            "id": debit.id,
            "transaction_id": debit.transaction_id,
            "member_id": debit.member_id,
            "transaction_type": debit.transaction_type,
            "amount": float(debit.amount or 0),
            "transaction_date": debit.transaction_date.isoformat() if debit.transaction_date else None,
            "created_at": debit.created_at.isoformat() if debit.created_at else None,
        }
        for debit in paged["items"]
    ]
    return ok(
        {
            "items": items,
            "page": paged["page"],
            "pages": paged["pages"],
            "total": paged["total"],
            "page_size": paged["page_size"],
        }
    )


@api_bp.get("/reports/profile-history/requests")
@permission_required("profile.view")
def profile_history_requests():
    user_id = get_jwt_identity()
    actor = User.query.get(user_id)
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_profile(actor, membership_id)
    if not member_ids:
        return ok({"loan_requests": [], "charity_requests": [], "release_requests": []})

    loan_requests = LoanRequest.query.filter(LoanRequest.member_id.in_(member_ids)).order_by(LoanRequest.created_at.desc()).all()
    charity_requests = CharityRequest.query.filter(CharityRequest.member_id.in_(member_ids)).order_by(CharityRequest.created_at.desc()).all()
    release_requests = ReleaseRequest.query.filter(ReleaseRequest.member_id.in_(member_ids)).order_by(ReleaseRequest.created_at.desc()).all()

    return ok({
        "loan_requests": [
            {
                "id": lr.id,
                "request_id": lr.request_id,
                "member_id": lr.member_id,
                "amount": lr.amount,
                "status": lr.status,
                "created_at": lr.created_at.strftime("%Y-%m-%d %H:%M"),
            }
            for lr in loan_requests
        ],
        "charity_requests": [
            {
                "id": cr.id,
                "charity_id": cr.charity_id,
                "member_id": cr.member_id,
                "beneficiary_name": cr.beneficiary_name,
                "amount": cr.amount,
                "status": cr.status,
                "created_at": cr.created_at.strftime("%Y-%m-%d %H:%M"),
            }
            for cr in charity_requests
        ],
        "release_requests": [
            {
                "id": rr.id,
                "release_request_id": rr.release_request_id,
                "member_id": rr.member_id,
                "release_amount": rr.release_amount,
                "status": rr.status,
                "created_at": rr.created_at.strftime("%Y-%m-%d %H:%M"),
            }
            for rr in release_requests
        ],
    })


@api_bp.get("/reports/profile-years")
@permission_required("profile.view")
def profile_years():
    actor = User.query.get(get_jwt_identity())
    membership_id = request.args.get("membership_id")
    member_ids = _get_member_ids_for_profile(actor, membership_id)
    if not member_ids:
        return ok({"years": []})

    years = set()

    membership_years = db.session.query(extract("year", SavingsPlanMember.effective_date)).filter(
        SavingsPlanMember.member_id.in_(member_ids),
        SavingsPlanMember.effective_date.isnot(None),
    ).distinct().all()
    for (y,) in membership_years:
        if y:
            years.add(int(y))

    credit_years = db.session.query(extract("year", Credit.transaction_date)).filter(
        Credit.member_id.in_(member_ids),
        Credit.transaction_date.isnot(None),
    ).distinct().all()
    for (y,) in credit_years:
        if y:
            years.add(int(y))

    debit_years = db.session.query(extract("year", Debit.transaction_date)).filter(
        Debit.member_id.in_(member_ids),
        Debit.transaction_date.isnot(None),
    ).distinct().all()
    for (y,) in debit_years:
        if y:
            years.add(int(y))

    loan_years = db.session.query(extract("year", Loan.disbursement_date)).filter(
        Loan.member_id.in_(member_ids),
        Loan.disbursement_date.isnot(None),
    ).distinct().all()
    for (y,) in loan_years:
        if y:
            years.add(int(y))

    years_list = sorted(list(years), reverse=True)
    return ok({"years": years_list})
