import os
from io import BytesIO
from app.extensions import db
from app.models import (
    SavingsPlanMember,
    Credit,
    Debit,
    Loan,
    LoanRequest,
    ReleaseRequest,
    CharityRequest,
)


def _get_pandas():
    try:
        import pandas as pd
    except ModuleNotFoundError as exc:
        raise RuntimeError(
            "pandas is required for import/export. Install it with a supported Python version (3.10-3.12)."
        ) from exc
    return pd


def create_template_excel():
    pd = _get_pandas()
    output = BytesIO()
    writer = pd.ExcelWriter(output, engine="xlsxwriter")

    pd.DataFrame(columns=[
        "member_id",
        "member_short_name",
        "user_id",
        "plan_type_id",
        "cashier_id",
        "effective_date",
        "end_date",
        "status",
    ]).to_excel(writer, sheet_name="SavingsPlanMember", index=False)

    pd.DataFrame(columns=[
        "member_id",
        "transaction_type",
        "total_amount",
        "savings_amount",
        "charity_amount",
        "loan_id",
        "cashier_id",
        "transaction_date",
    ]).to_excel(writer, sheet_name="Credit", index=False)

    pd.DataFrame(columns=[
        "member_id",
        "transaction_type",
        "amount",
        "cashier_id",
        "loan_id",
        "charity_id",
        "release_request_id",
        "transaction_date",
        "notes",
    ]).to_excel(writer, sheet_name="Debit", index=False)

    pd.DataFrame(columns=[
        "loan_id",
        "member_id",
        "request_id",
        "amount",
        "term_months",
        "status",
        "disbursement_date",
    ]).to_excel(writer, sheet_name="Loan", index=False)

    pd.DataFrame(columns=[
        "request_id",
        "member_id",
        "amount",
        "reason",
        "term_months",
        "status",
        "document_id",
        "cashier_id",
    ]).to_excel(writer, sheet_name="LoanRequest", index=False)

    pd.DataFrame(columns=[
        "release_request_id",
        "member_id",
        "current_savings",
        "outstanding_loan",
        "release_amount",
        "cashier_id",
        "status",
        "notes",
        "created_at",
    ]).to_excel(writer, sheet_name="ReleaseRequest", index=False)

    pd.DataFrame(columns=[
        "charity_id",
        "member_id",
        "charity_type_id",
        "beneficiary_name",
        "reason",
        "amount",
        "allotted_amount",
        "cashier_id",
        "document_id",
        "status",
    ]).to_excel(writer, sheet_name="CharityRequest", index=False)

    writer.close()
    output.seek(0)
    return output


def import_excel(file_path, selections):
    pd = _get_pandas()
    xl = pd.ExcelFile(file_path)
    results = {}

    if selections.get("SavingsPlanMember") and "SavingsPlanMember" in xl.sheet_names:
        results["SavingsPlanMember"] = _import_members(pd.read_excel(xl, "SavingsPlanMember"))

    if selections.get("Credit") and "Credit" in xl.sheet_names:
        results["Credit"] = _import_credits(pd.read_excel(xl, "Credit"))

    if selections.get("Debit") and "Debit" in xl.sheet_names:
        results["Debit"] = _import_debits(pd.read_excel(xl, "Debit"))

    if selections.get("Loan") and "Loan" in xl.sheet_names:
        results["Loan"] = _import_loans(pd.read_excel(xl, "Loan"))

    if selections.get("LoanRequest") and "LoanRequest" in xl.sheet_names:
        results["LoanRequest"] = _import_loan_requests(pd.read_excel(xl, "LoanRequest"))

    if selections.get("ReleaseRequest") and "ReleaseRequest" in xl.sheet_names:
        results["ReleaseRequest"] = _import_release_requests(pd.read_excel(xl, "ReleaseRequest"))

    if selections.get("CharityRequest") and "CharityRequest" in xl.sheet_names:
        results["CharityRequest"] = _import_charity_requests(pd.read_excel(xl, "CharityRequest"))

    return results


def export_tables(table_names):
    pd = _get_pandas()
    output = BytesIO()
    writer = pd.ExcelWriter(output, engine="openpyxl")

    table_map = {
        "SavingsPlanMember": SavingsPlanMember,
        "Credit": Credit,
        "Debit": Debit,
        "Loan": Loan,
        "LoanRequest": LoanRequest,
        "ReleaseRequest": ReleaseRequest,
        "CharityRequest": CharityRequest,
    }

    for name in table_names:
        model = table_map.get(name)
        if not model:
            continue
        rows = model.query.all()
        data_list = []
        for row in rows:
            record = {}
            for column in model.__table__.columns:
                value = getattr(row, column.name)
                if hasattr(value, "isoformat"):
                    record[column.name] = value.isoformat()
                else:
                    record[column.name] = value
            data_list.append(record)
        pd.DataFrame(data_list).to_excel(writer, sheet_name=name, index=False)

    writer.close()
    output.seek(0)
    return output


def _import_members(df):
    count = 0
    for _, row in df.iterrows():
        member = SavingsPlanMember.query.filter_by(member_id=row["member_id"]).first()
        if not member:
            member = SavingsPlanMember(member_id=row["member_id"])
            db.session.add(member)
        member.member_short_name = row["member_short_name"]
        member.user_id = row["user_id"]
        member.plan_type_id = row["plan_type_id"]
        member.cashier_id = row["cashier_id"]
        member.effective_date = pd.to_datetime(row["effective_date"]).date()
        if "end_date" in row and pd.notna(row["end_date"]):
            member.end_date = pd.to_datetime(row["end_date"]).date()
        member.status = row["status"]
        count += 1
    db.session.commit()
    return count


def _import_credits(df):
    count = 0
    for _, row in df.iterrows():
        credit = Credit(
            transaction_id=f"TR{pd.Timestamp.now().strftime('%y%m%d%H%M%S')}",
            member_id=row["member_id"],
            transaction_type=row["transaction_type"],
            total_amount=row["total_amount"],
            transaction_date=pd.to_datetime(row["transaction_date"]).date(),
        )
        if "savings_amount" in row and pd.notna(row["savings_amount"]):
            credit.savings_amount = row["savings_amount"]
        if "charity_amount" in row and pd.notna(row["charity_amount"]):
            credit.charity_amount = row["charity_amount"]
        if "loan_id" in row and pd.notna(row["loan_id"]):
            credit.loan_id = str(row["loan_id"])
        if "cashier_id" in row and pd.notna(row["cashier_id"]):
            credit.cashier_id = int(row["cashier_id"])
        db.session.add(credit)
        count += 1
    db.session.commit()
    return count


def _import_debits(df):
    count = 0
    for _, row in df.iterrows():
        debit = Debit(
            transaction_id=f"TD{pd.Timestamp.now().strftime('%y%m%d%H%M%S')}",
            member_id=row["member_id"],
            transaction_type=row["transaction_type"],
            amount=row["amount"],
            transaction_date=pd.to_datetime(row["transaction_date"]).date(),
        )
        if "cashier_id" in row and pd.notna(row["cashier_id"]):
            debit.cashier_id = int(row["cashier_id"])
        if "loan_id" in row and pd.notna(row["loan_id"]):
            debit.loan_id = str(row["loan_id"])
        if "charity_id" in row and pd.notna(row["charity_id"]):
            debit.charity_id = str(row["charity_id"])
        if "release_request_id" in row and pd.notna(row["release_request_id"]):
            debit.release_request_id = str(row["release_request_id"])
        if "notes" in row and pd.notna(row["notes"]):
            debit.notes = str(row["notes"])
        db.session.add(debit)
        count += 1
    db.session.commit()
    return count


def _import_loans(df):
    count = 0
    for _, row in df.iterrows():
        loan = Loan.query.filter_by(loan_id=row["loan_id"]).first()
        if not loan:
            loan = Loan(loan_id=row["loan_id"])
            db.session.add(loan)
        loan.member_id = row["member_id"]
        loan.request_id = row["request_id"]
        loan.amount = row["amount"]
        loan.term_months = row["term_months"]
        loan.status = row["status"]
        if "disbursement_date" in row and pd.notna(row["disbursement_date"]):
            loan.disbursement_date = pd.to_datetime(row["disbursement_date"])
        count += 1
    db.session.commit()
    return count


def _import_loan_requests(df):
    count = 0
    for _, row in df.iterrows():
        req = LoanRequest.query.filter_by(request_id=row["request_id"]).first()
        if not req:
            req = LoanRequest(request_id=row["request_id"])
            db.session.add(req)
        req.member_id = row["member_id"]
        req.amount = row["amount"]
        req.reason = row["reason"]
        req.term_months = row["term_months"]
        req.status = row["status"]
        if "document_id" in row and pd.notna(row["document_id"]):
            req.document_id = int(row["document_id"])
        if "cashier_id" in row and pd.notna(row["cashier_id"]):
            req.cashier_id = int(row["cashier_id"])
        count += 1
    db.session.commit()
    return count


def _import_release_requests(df):
    count = 0
    for _, row in df.iterrows():
        req = ReleaseRequest.query.filter_by(release_request_id=row["release_request_id"]).first()
        if not req:
            req = ReleaseRequest(release_request_id=row["release_request_id"])
            db.session.add(req)
        req.member_id = row["member_id"]
        req.current_savings = row["current_savings"]
        req.outstanding_loan = row["outstanding_loan"]
        req.release_amount = row["release_amount"]
        req.status = row["status"]
        if "cashier_id" in row and pd.notna(row["cashier_id"]):
            req.cashier_id = int(row["cashier_id"])
        if "notes" in row and pd.notna(row["notes"]):
            req.notes = str(row["notes"])
        if "created_at" in row and pd.notna(row["created_at"]):
            req.created_at = pd.to_datetime(row["created_at"])
        count += 1
    db.session.commit()
    return count


def _import_charity_requests(df):
    count = 0
    for _, row in df.iterrows():
        req = CharityRequest.query.filter_by(charity_id=row["charity_id"]).first()
        if not req:
            req = CharityRequest(charity_id=row["charity_id"])
            db.session.add(req)
        req.member_id = row["member_id"]
        req.charity_type_id = row["charity_type_id"]
        req.beneficiary_name = row["beneficiary_name"]
        req.reason = row["reason"]
        req.amount = row["amount"]
        req.status = row["status"]
        if "allotted_amount" in row and pd.notna(row["allotted_amount"]):
            req.allotted_amount = row["allotted_amount"]
        if "cashier_id" in row and pd.notna(row["cashier_id"]):
            req.cashier_id = int(row["cashier_id"])
        if "document_id" in row and pd.notna(row["document_id"]):
            req.document_id = int(row["document_id"])
        count += 1
    db.session.commit()
    return count
