from __future__ import annotations

import csv
from pathlib import Path

from sqlalchemy.orm import Session

from app.models.procurement import ItemDatabase
from app.models.master_data import BudgetHead, OFESEstablishment


BUDGET_HEAD_CSV = Path(__file__).resolve().parents[1] / "data" / "budget_head_master.csv"
ITEM_MASTER_CSV = Path(__file__).resolve().parents[1] / "data" / "item_master.csv"
OFES_ESTABLISHMENTS_CSV = Path(__file__).resolve().parents[1] / "data" / "ofes_establishments.csv"

ODIA_ITEM_NAMES = {
    "Fire Extinguisher": "ଅଗ୍ନିନିବାରକ",
    "Fire Hose": "ଅଗ୍ନିଶମ ହୋଜ୍",
    "Breathing Apparatus": "ଶ୍ୱାସ ଯନ୍ତ୍ର",
    "Rescue Cutter": "ଉଦ୍ଧାର କଟର",
    "Safety Gloves": "ସୁରକ୍ଷା ହାତମୋଜା",
    "Hydraulic Rescue Kit": "ହାଇଡ୍ରୋଲିକ୍ ଉଦ୍ଧାର କିଟ୍",
}

ODIA_ITEM_TYPES = {
    "Equipment": "ଉପକରଣ",
    "Vehicle": "ଯାନ",
    "PPE": "ବ୍ୟକ୍ତିଗତ ସୁରକ୍ଷା ସାମଗ୍ରୀ",
    "Consumable": "ବ୍ୟବହାର୍ଯ୍ୟ ସାମଗ୍ରୀ",
    "Service": "ସେବା",
}

ODIA_BUDGET_TERMS = {
    "Equipment": "ଉପକରଣ",
    "Vehicle": "ଯାନ",
    "Office Expenses": "କାର୍ଯ୍ୟାଳୟ ବ୍ୟୟ",
    "Machinery": "ଯନ୍ତ୍ରପାତି",
    "Fire Fighting": "ଅଗ୍ନିଶମ",
    "Protective Gear": "ସୁରକ୍ଷା ସାମଗ୍ରୀ",
}

ODIA_DISTRICTS = {
    "Khordha": "ଖୋର୍ଦ୍ଧା",
    "Cuttack": "କଟକ",
    "Puri": "ପୁରୀ",
    "Sambalpur": "ସମ୍ବଲପୁର",
    "Balasore": "ବାଲେଶ୍ୱର",
}

ODIA_RANGES = {
    "Central Range": "କେନ୍ଦ୍ରୀୟ ରେଞ୍ଜ",
    "Northern Range": "ଉତ୍ତର ରେଞ୍ଜ",
    "Southern Range": "ଦକ୍ଷିଣ ରେଞ୍ଜ",
}


def odia_lookup(value: str, mapping: dict[str, str]) -> str | None:
    normalized = value.strip()
    return mapping.get(normalized)


def budget_head_display_name(row: dict[str, str]) -> str:
    return " - ".join(
        part.strip()
        for part in (
            row.get("category_name", ""),
            row.get("expenditure_head", ""),
            row.get("item_component", ""),
        )
        if part and part.strip()
    )


def budget_head_display_name_or(row: dict[str, str]) -> str | None:
    parts = []
    for key in ("category_name", "expenditure_head", "item_component"):
        value = odia_lookup(row.get(key, ""), ODIA_BUDGET_TERMS)
        if value:
            parts.append(value)
    return " - ".join(parts) if parts else None


def upsert_budget_heads(db: Session, csv_path: Path = BUDGET_HEAD_CSV) -> int:
    if not csv_path.exists():
        return 0

    changed = 0
    with csv_path.open(newline="", encoding="utf-8") as handle:
        for index, row in enumerate(csv.DictReader(handle), start=1):
            category_code = row["category_code"].strip()
            head_code = f"{category_code}-{index:03d}"
            budget_head = db.query(BudgetHead).filter(BudgetHead.head_code == head_code).first()
            values = {
                "head_code": head_code,
                "display_name": budget_head_display_name(row),
                "display_name_en": budget_head_display_name(row),
                "display_name_or": budget_head_display_name_or(row),
                "category_code": category_code,
                "category_name": row["category_name"].strip(),
                "category_name_en": row["category_name"].strip(),
                "category_name_or": odia_lookup(row["category_name"], ODIA_BUDGET_TERMS),
                "fund_source": row["fund_source"].strip(),
                "expenditure_head": row["expenditure_head"].strip(),
                "expenditure_head_en": row["expenditure_head"].strip(),
                "expenditure_head_or": odia_lookup(row["expenditure_head"], ODIA_BUDGET_TERMS),
                "item_component": row["item_component"].strip(),
                "item_component_en": row["item_component"].strip(),
                "item_component_or": odia_lookup(row["item_component"], ODIA_BUDGET_TERMS),
                "quantity": row.get("quantity", "").strip() or None,
                "unit": row.get("unit", "").strip() or None,
                "purpose_description": row.get("purpose_description", "").strip() or None,
                "remarks": row.get("remarks", "").strip() or None,
                "is_active": row.get("is_active", "1").strip() in {"1", "true", "True", "yes", "Y"},
            }
            if budget_head:
                for key, value in values.items():
                    setattr(budget_head, key, value)
            else:
                db.add(BudgetHead(**values))
            changed += 1
    return changed


def upsert_item_master(db: Session, csv_path: Path = ITEM_MASTER_CSV) -> int:
    if not csv_path.exists():
        return 0

    changed = 0
    with csv_path.open(newline="", encoding="utf-8-sig") as handle:
        for row in csv.DictReader(handle):
            sr_no = int(row["sr_no"])
            item = db.query(ItemDatabase).filter(ItemDatabase.sr_no == sr_no).first()
            values = {
                "sr_no": sr_no,
                "name": row["name"].strip(),
                "name_en": row["name"].strip(),
                "name_or": odia_lookup(row["name"], ODIA_ITEM_NAMES),
                "item_type": row["item_type"].strip(),
                "item_type_en": row["item_type"].strip(),
                "item_type_or": odia_lookup(row["item_type"], ODIA_ITEM_TYPES),
                "description": None,
                "description_en": None,
                "description_or": None,
                "quantity": 0,
                "remarks": None,
                "is_active": row.get("is_active", "1").strip() in {"1", "true", "True", "yes", "Y"},
            }
            if item:
                for key, value in values.items():
                    setattr(item, key, value)
            else:
                db.add(ItemDatabase(**values))
            changed += 1
    return changed


def upsert_ofes_establishments(db: Session, csv_path: Path = OFES_ESTABLISHMENTS_CSV) -> int:
    if not csv_path.exists():
        return 0

    changed = 0
    with csv_path.open(newline="", encoding="utf-8-sig") as handle:
        for row in csv.DictReader(handle):
            sr_no = int(row["sr_no"])
            establishment = db.query(OFESEstablishment).filter(OFESEstablishment.sr_no == sr_no).first()
            values = {
                "sr_no": sr_no,
                "name": row["name"].strip(),
                "name_en": row["name"].strip(),
                "name_or": odia_lookup(row["name"], ODIA_DISTRICTS) or None,
                "district": row["district"].strip(),
                "district_en": row["district"].strip(),
                "district_or": odia_lookup(row["district"], ODIA_DISTRICTS),
                "range_name": row["range_name"].strip(),
                "range_name_en": row["range_name"].strip(),
                "range_name_or": odia_lookup(row["range_name"], ODIA_RANGES),
                "display_name": row["display_name"].strip(),
                "display_name_en": row["display_name"].strip(),
                "display_name_or": " - ".join(
                    part for part in (
                        odia_lookup(row["name"], ODIA_DISTRICTS),
                        odia_lookup(row["range_name"], ODIA_RANGES),
                    ) if part
                ) or None,
                "is_active": row.get("is_active", "1").strip() in {"1", "true", "True", "yes", "Y"},
            }
            if establishment:
                for key, value in values.items():
                    setattr(establishment, key, value)
            else:
                db.add(OFESEstablishment(**values))
            changed += 1
    return changed