from decimal import Decimal, ROUND_HALF_UP
from datetime import date
from dateutil.relativedelta import relativedelta
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from fastapi import HTTPException, status
from typing import Optional
from . import repository
from .models import AmortizationScheduleEntry
from .schema import LoancalculationCreate, LoancalculationUpdate


def create_loan_calculation(db: Session, data: LoancalculationCreate) -> dict:
    try:
        obj = repository.create(db, data.model_dump())
        db.commit()
        db.refresh(obj)
        return obj
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "unique" in msg or "duplicate" in msg:
            raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Loan calculation already exists.")
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def get_loan_calculation(db: Session, calculation_id: str) -> dict:
    obj = repository.get_by_id(db, calculation_id)
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Loan calculation not found.")
    return obj


def list_loan_calculations(db: Session, limit: int, offset: int, session_id: Optional[str] = None) -> dict:
    filters = {}
    if session_id is not None:
        filters["session_id"] = session_id
    items = repository.list_all(db, limit=limit, offset=offset, **filters)
    total = repository.count_all(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_loan_calculation(db: Session, calculation_id: str, data: LoancalculationUpdate) -> dict:
    try:
        obj = repository.update(db, calculation_id, data.model_dump(exclude_unset=True))
        if not obj:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Loan calculation not found.")
        db.commit()
        db.refresh(obj)
        return obj
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "unique" in msg or "duplicate" in msg:
            raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Loan calculation already exists.")
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def delete_loan_calculation(db: Session, calculation_id: str) -> bool:
    try:
        deleted = repository.delete(db, calculation_id)
        if not deleted:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Loan calculation not found.")
        db.commit()
        return True
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def get_loan_calculation_with_details(db: Session, calculation_id: str) -> dict:
    obj = repository.get_with_details(db, calculation_id)
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Loan calculation not found.")
    return obj


def generate_amortization_schedule(db: Session, calculation_id: str, start_date: Optional[date] = None) -> dict:
    loan = repository.get_by_id(db, calculation_id)
    if not loan:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Loan calculation not found.")

    # Delete existing schedule entries using direct query
    db.query(AmortizationScheduleEntry).filter(
        AmortizationScheduleEntry.calculation_id == calculation_id
    ).delete(synchronize_session=False)

    if start_date is None:
        start_date = date.today()

    principal = loan.principal
    annual_rate = loan.annual_interest_rate
    term_months = loan.loan_term_months
    monthly_payment = loan.monthly_payment

    monthly_rate = (annual_rate / Decimal("100")) / Decimal("12")
    balance = principal
    current_date = start_date

    entries = []
    for payment_num in range(1, term_months + 1):
        beginning_balance = balance

        if annual_rate == Decimal("0"):
            interest_portion = Decimal("0")
        else:
            interest_portion = (balance * monthly_rate).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

        if payment_num == term_months:
            payment_amount = balance + interest_portion
            principal_portion = balance
            ending_balance = Decimal("0")
        else:
            payment_amount = monthly_payment
            principal_portion = payment_amount - interest_portion
            ending_balance = balance - principal_portion

        entry_data = {
            "calculation_id": calculation_id,
            "payment_number": payment_num,
            "payment_date": current_date,
            "beginning_balance": beginning_balance,
            "payment_amount": payment_amount,
            "principal_portion": principal_portion,
            "interest_portion": interest_portion,
            "ending_balance": ending_balance,
        }

        entries.append(entry_data)
        balance = ending_balance
        current_date = current_date + relativedelta(months=1)

    try:
        for entry_data in entries:
            entry = AmortizationScheduleEntry(**entry_data)
            db.add(entry)
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "unique" in msg or "duplicate" in msg:
            raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Schedule entry already exists.")
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced calculation does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise

    refreshed_loan = repository.get_with_details(db, calculation_id)
    return refreshed_loan


def list_amortization_schedule_entries(db: Session, limit: int, offset: int, calculation_id: Optional[str] = None) -> dict:
    q = db.query(AmortizationScheduleEntry)
    if calculation_id is not None:
        q = q.filter(AmortizationScheduleEntry.calculation_id == calculation_id)
    
    items = q.order_by(AmortizationScheduleEntry.payment_number).limit(limit).offset(offset).all()
    
    count_q = db.query(AmortizationScheduleEntry.id)
    if calculation_id is not None:
        count_q = count_q.filter(AmortizationScheduleEntry.calculation_id == calculation_id)
    total = count_q.count()
    
    return {"items": items, "total": total, "limit": limit, "offset": offset}