from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from fastapi import HTTPException, status
from typing import Optional, List
from decimal import Decimal
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from . import repository
from .schema import (
    CalculationsettingsCreate,
    CalculationsettingsUpdate,
    LoancalculationCreate,
    LoancalculationUpdate,
    AmortizationentryCreate,
    AmortizationentryUpdate,
)
from utils.utils import utc_now


# CalculationSettings handlers
def create_calculationsettings(db: Session, data: CalculationsettingsCreate):
    existing = repository.get_by_settings_id(db, data.settings_id)
    if existing:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Calculation settings with this settings_id already exists.",
        )
    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="Resource 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_calculationsettings_by_id(db: Session, entity_id: str):
    obj = repository.get_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Calculation settings not found.",
        )
    return obj


def get_default_calculationsettings(db: Session):
    obj = repository.list_all(db, limit=1, offset=0)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="No default calculation settings found.",
        )
    return obj[0]


def list_calculationsettings(db: Session, limit: int, offset: int, **filters):
    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_calculationsettings(db: Session, entity_id: str, data: CalculationsettingsUpdate):
    obj = repository.get_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Calculation settings not found.",
        )
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        return obj
    if "settings_id" in update_data and update_data["settings_id"] != obj.settings_id:
        existing = repository.get_by_settings_id(db, update_data["settings_id"])
        if existing:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Another calculation settings with this settings_id already exists.",
            )
    try:
        obj = repository.update(db, entity_id, update_data)
        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="Resource 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_calculationsettings(db: Session, entity_id: str):
    obj = repository.get_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Calculation settings not found.",
        )
    try:
        repository.delete(db, entity_id)
        db.commit()
        return {"message": "Calculation settings deleted successfully"}
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Cannot delete calculation settings that is referenced by loan calculations.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Data integrity error.",
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


# LoanCalculation handlers
def create_loancalculation(db: Session, data: LoancalculationCreate):
    existing = repository.get_calculation_by_calculation_id(db, data.calculation_id)
    if existing:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Loan calculation with this calculation_id already exists.",
        )
    if data.settings_id:
        settings = repository.get_by_id(db, data.settings_id)
        if not settings:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced calculation settings does not exist.",
            )
    try:
        obj = repository.create_calculation(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="Resource 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_loancalculation_by_id(db: Session, entity_id: str):
    obj = repository.get_calculation_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Loan calculation not found.",
        )
    return obj


def get_loancalculation_details(db: Session, entity_id: str):
    obj = repository.get_calculation_with_details(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Loan calculation not found.",
        )
    return obj


def list_loancalculations(db: Session, limit: int, offset: int, **filters):
    items = repository.list_calculations(db, limit=limit, offset=offset, **filters)
    total = repository.count_calculations(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_loancalculation(db: Session, entity_id: str, data: LoancalculationUpdate):
    obj = repository.get_calculation_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Loan calculation not found.",
        )
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        return obj
    if "calculation_id" in update_data and update_data["calculation_id"] != obj.calculation_id:
        existing = repository.get_calculation_by_calculation_id(db, update_data["calculation_id"])
        if existing:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Another loan calculation with this calculation_id already exists.",
            )
    if "settings_id" in update_data and update_data["settings_id"]:
        settings = repository.get_by_id(db, update_data["settings_id"])
        if not settings:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced calculation settings does not exist.",
            )
    try:
        obj = repository.update_calculation(db, entity_id, update_data)
        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="Resource 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_loancalculation(db: Session, entity_id: str):
    obj = repository.get_calculation_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Loan calculation not found.",
        )
    try:
        repository.delete_calculation(db, entity_id)
        db.commit()
        return {"message": "Loan calculation deleted successfully"}
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Cannot delete loan calculation that is referenced by other records.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Data integrity error.",
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def calculate_loan_service(
    db: Session,
    calculation_id: str,
    principal_amount: Decimal,
    annual_interest_rate: Decimal,
    loan_term_months: int,
    settings_id: Optional[str] = None,
    start_date: Optional[datetime] = None,
):
    if principal_amount <= 0:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Principal amount must be positive.",
        )
    if annual_interest_rate < 0:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Interest rate must be non-negative.",
        )
    if loan_term_months <= 0:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Loan term must be at least 1 month.",
        )

    existing = repository.get_calculation_by_calculation_id(db, calculation_id)
    if existing:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Loan calculation with this calculation_id already exists.",
        )

    if settings_id:
        settings = repository.get_by_id(db, settings_id)
        if not settings:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced calculation settings does not exist.",
            )

    monthly_interest_rate = annual_interest_rate / Decimal("100") / Decimal("12")
    
    if annual_interest_rate == 0:
        monthly_payment = principal_amount / Decimal(str(loan_term_months))
        total_interest = Decimal("0.00")
    else:
        numerator = monthly_interest_rate * ((Decimal("1") + monthly_interest_rate) ** loan_term_months)
        denominator = ((Decimal("1") + monthly_interest_rate) ** loan_term_months) - Decimal("1")
        monthly_payment = principal_amount * (numerator / denominator)
        monthly_payment = monthly_payment.quantize(Decimal("0.01"))
        total_interest = (monthly_payment * Decimal(str(loan_term_months))) - principal_amount

    total_amount_paid = principal_amount + total_interest
    calculation_timestamp = start_date if start_date else utc_now()

    try:
        loan_calc_data = {
            "calculation_id": calculation_id,
            "settings_id": settings_id,
            "principal_amount": principal_amount,
            "annual_interest_rate": annual_interest_rate,
            "loan_term_months": loan_term_months,
            "monthly_payment": monthly_payment,
            "total_interest": total_interest,
            "total_amount_paid": total_amount_paid,
            "calculation_timestamp": calculation_timestamp,
        }
        loan_calc = repository.create_calculation(db, loan_calc_data)
        db.flush()

        remaining_balance = principal_amount
        first_payment_date = calculation_timestamp.date() + relativedelta(months=1)

        for i in range(1, loan_term_months + 1):
            if annual_interest_rate == 0:
                interest_portion = Decimal("0.00")
            else:
                interest_portion = (remaining_balance * monthly_interest_rate).quantize(Decimal("0.01"))
            
            principal_portion = monthly_payment - interest_portion
            
            if i == loan_term_months:
                principal_portion = remaining_balance
                payment_amount = remaining_balance + interest_portion
                remaining_balance = Decimal("0.00")
            else:
                payment_amount = monthly_payment
                remaining_balance = remaining_balance - principal_portion

            payment_date = first_payment_date + relativedelta(months=i - 1)
            
            entry_data = {
                "entry_id": f"{calculation_id}_payment_{i}",
                "calculation_id": loan_calc.id,
                "payment_number": i,
                "payment_date": payment_date,
                "payment_amount": payment_amount,
                "principal_portion": principal_portion,
                "interest_portion": interest_portion,
                "remaining_balance": remaining_balance,
            }
            repository.create_entry(db, entry_data)

        db.commit()
        db.refresh(loan_calc)
        return repository.get_calculation_with_details(db, loan_calc.id)
    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="Resource 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_amortization_schedule(db: Session, calculation_id: str):
    loan_calc = repository.get_calculation_with_details(db, calculation_id)
    if not loan_calc:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Loan calculation not found.",
        )
    return loan_calc.amortization_entries


# AmortizationEntry handlers
def create_amortizationentry(db: Session, data: AmortizationentryCreate):
    existing = repository.get_entry_by_entry_id(db, data.entry_id)
    if existing:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Amortization entry with this entry_id already exists.",
        )
    loan_calc = repository.get_calculation_by_id(db, data.calculation_id)
    if not loan_calc:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Referenced loan calculation does not exist.",
        )
    try:
        obj = repository.create_entry(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="Resource 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_amortizationentry_by_id(db: Session, entity_id: str):
    obj = repository.get_entry_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Amortization entry not found.",
        )
    return obj


def list_amortizationentries(db: Session, limit: int, offset: int, **filters):
    items = repository.list_entries(db, limit=limit, offset=offset, **filters)
    total = repository.count_entries(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_amortizationentry(db: Session, entity_id: str, data: AmortizationentryUpdate):
    obj = repository.get_entry_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Amortization entry not found.",
        )
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        return obj
    if "entry_id" in update_data and update_data["entry_id"] != obj.entry_id:
        existing = repository.get_entry_by_entry_id(db, update_data["entry_id"])
        if existing:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Another amortization entry with this entry_id already exists.",
            )
    if "calculation_id" in update_data:
        loan_calc = repository.get_calculation_by_id(db, update_data["calculation_id"])
        if not loan_calc:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced loan calculation does not exist.",
            )
    try:
        obj = repository.update_entry(db, entity_id, update_data)
        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="Resource 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_amortizationentry(db: Session, entity_id: str):
    obj = repository.get_entry_by_id(db, entity_id)
    if not obj:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Amortization entry not found.",
        )
    try:
        repository.delete_entry(db, entity_id)
        db.commit()
        return {"message": "Amortization entry deleted successfully"}
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Cannot delete amortization entry that is referenced by other records.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Data integrity error.",
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise