from decimal import Decimal, InvalidOperation
from io import BytesIO

from openpyxl import load_workbook
from sqlalchemy.orm import Session

from app.core.exceptions import ValidationError
from app.models.enums import PolicyCoverageType, PolicyStatus
from app.repositories.customer_repository import CustomerRepository
from app.repositories.insurance_company_repository import InsuranceCompanyRepository
from app.repositories.policy_repository import PolicyRepository
from app.repositories.vehicle_repository import VehicleRepository
from app.services.commission_service import CommissionService
from app.utils.date_parser import parse_flexible_date
from app.utils.helpers import update_payment_summary
from app.utils.policy_status import compute_policy_status
from app.utils.vehicle_types import normalize_vehicle_type


HEADER_ALIASES = {
    "customer_name": {"customer_name", "name", "customer", "insured_name"},
    "mobile": {"mobile", "mobile_number", "phone", "contact"},
    "policy_number": {"policy_number", "policy_no", "policy no"},
    "vehicle_registration": {"vehicle_registration", "registration", "reg_no", "vehicle_no"},
    "policy_end_date": {"policy_end_date", "expiry_date", "expiry", "end_date"},
    "premium_amount": {"premium_amount", "premium", "premium_amt"},
    "total_commission": {"total_commission", "commission"},
    "insurance_company": {"insurance_company", "insurer", "company"},
    "vehicle_type": {"vehicle_type", "type"},
    "coverage_type": {"coverage_type", "coverage"},
}


class ImportService:
    def __init__(self, db: Session):
        self.db = db
        self.customers = CustomerRepository(db)
        self.vehicles = VehicleRepository(db)
        self.policies = PolicyRepository(db)
        self.companies = InsuranceCompanyRepository(db)
        self.commission = CommissionService(db)

    def import_excel(self, agency_id: int, file_bytes: bytes, user_id: int | None = None) -> dict:
        try:
            workbook = load_workbook(BytesIO(file_bytes), read_only=True, data_only=True)
        except Exception as exc:
            raise ValidationError("Invalid Excel file") from exc

        sheet = workbook.active
        rows = list(sheet.iter_rows(values_only=True))
        if len(rows) < 2:
            raise ValidationError("Excel file must have a header row and at least one data row")

        header_map = self._map_headers(rows[0])
        required = {"customer_name", "mobile", "policy_end_date"}
        missing = required - set(header_map.keys())
        if missing:
            raise ValidationError(f"Missing required columns: {', '.join(sorted(missing))}")

        created = 0
        skipped = 0
        errors: list[dict] = []

        for idx, row in enumerate(rows[1:], start=2):
            if not row or all(cell is None or str(cell).strip() == "" for cell in row):
                continue
            try:
                data = self._parse_row(row, header_map)
                if self._policy_exists(agency_id, data):
                    skipped += 1
                    continue
                self._create_from_row(agency_id, data, user_id=user_id)
                created += 1
            except Exception as exc:
                errors.append({"row": idx, "error": str(exc)})

        self.db.commit()
        return {"created": created, "skipped": skipped, "errors": errors}

    def _map_headers(self, header_row: tuple) -> dict[str, int]:
        mapping: dict[str, int] = {}
        for idx, cell in enumerate(header_row):
            if cell is None:
                continue
            normalized = str(cell).strip().lower().replace(" ", "_")
            for canonical, aliases in HEADER_ALIASES.items():
                if normalized in aliases and canonical not in mapping:
                    mapping[canonical] = idx
        return mapping

    def _parse_row(self, row: tuple, header_map: dict[str, int]) -> dict:
        def cell(key: str):
            col = header_map.get(key)
            if col is None or col >= len(row):
                return None
            val = row[col]
            return None if val is None else str(val).strip()

        premium = self._to_decimal(cell("premium_amount"))
        commission = self._to_decimal(cell("total_commission"))
        end_date = parse_flexible_date(cell("policy_end_date"))
        if not end_date:
            raise ValidationError("Invalid policy end date")

        return {
            "customer_name": cell("customer_name") or "",
            "mobile": cell("mobile") or "",
            "policy_number": cell("policy_number"),
            "vehicle_registration": cell("vehicle_registration"),
            "policy_end_date": end_date,
            "premium_amount": premium,
            "total_commission": commission,
            "insurance_company": cell("insurance_company"),
            "vehicle_type": normalize_vehicle_type(cell("vehicle_type")) or cell("vehicle_type"),
            "coverage_type": cell("coverage_type"),
        }

    def _create_from_row(self, agency_id: int, data: dict, user_id: int | None = None) -> None:
        if not data["customer_name"]:
            raise ValidationError("Customer name is required")
        if not data["mobile"]:
            raise ValidationError("Mobile is required")
        if not data.get("policy_number") or not str(data["policy_number"]).strip():
            raise ValidationError("Policy number is required")

        customer = self.customers.get_by_mobile(agency_id, data["mobile"])
        if not customer:
            customer = self.customers.create(
                agency_id,
                {"name": data["customer_name"], "mobile": data["mobile"]},
            )
        elif customer.name != data["customer_name"]:
            customer = self.customers.update(customer, {"name": data["customer_name"]})

        vehicle = None
        if data["vehicle_registration"]:
            vehicle = self.vehicles.create(
                customer.id,
                {"registration_number": data["vehicle_registration"], "vehicle_type": data.get("vehicle_type")},
            )

        company_id = None
        if data.get("insurance_company"):
            company = self.companies.get_by_code(data["insurance_company"].lower().replace(" ", "_"))
            if not company:
                company = self.companies.find_by_name(data["insurance_company"])
            company_id = company.id if company else None

        coverage_type = None
        if data.get("coverage_type"):
            normalized = data["coverage_type"].lower()
            if "third" in normalized:
                coverage_type = PolicyCoverageType.THIRD_PARTY
            elif "comprehensive" in normalized or "comp" in normalized:
                coverage_type = PolicyCoverageType.COMPREHENSIVE

        premium = data.get("premium_amount") or Decimal("0.00")
        commission = self.commission.auto_commission(
            agency_id,
            premium,
            company_id,
            data.get("vehicle_type"),
            coverage_type.value if coverage_type else None,
            data.get("total_commission"),
        )
        pending, _, payment_status = update_payment_summary(commission, Decimal("0.00"))
        status = compute_policy_status(data["policy_end_date"], PolicyStatus.DRAFT)

        self.policies.create(
            agency_id,
            {
                "customer_id": customer.id,
                "vehicle_id": vehicle.id if vehicle else None,
                "insurance_company_id": company_id,
                "policy_number": data.get("policy_number"),
                "policy_end_date": data["policy_end_date"],
                "premium_amount": premium,
                "total_commission": commission,
                "total_paid": Decimal("0.00"),
                "pending_amount": pending,
                "payment_status": payment_status,
                "coverage_type": coverage_type,
                "status": status,
                "created_by": user_id,
            },
        )

    def _policy_exists(self, agency_id: int, data: dict) -> bool:
        policy_number = data.get("policy_number")
        if not policy_number:
            return False
        return self.policies.find_by_policy_number(agency_id, policy_number) is not None

    @staticmethod
    def _to_decimal(value: str | None) -> Decimal | None:
        if not value:
            return None
        cleaned = value.replace(",", "").replace("₹", "").strip()
        try:
            return Decimal(cleaned)
        except InvalidOperation as exc:
            raise ValidationError(f"Invalid number: {value}") from exc