#!/usr/bin/env python3
"""Import normalized inventory CSV into the medicore database."""

from __future__ import annotations

import csv
from dataclasses import dataclass
from decimal import Decimal
from pathlib import Path
from typing import Dict, Tuple

import psycopg2

REPO_ROOT = Path(__file__).resolve().parents[1]
CSV_PATH = REPO_ROOT / "imports" / "mobile_medic_parts_normalized.csv"


@dataclass
class ImportStats:
    parts_inserted: int = 0
    parts_updated: int = 0
    variants_inserted: int = 0
    variants_updated: int = 0
    compat_inserted: int = 0


def parse_bool(value: str) -> bool:
    return str(value).strip().upper() in {"1", "TRUE", "T", "YES", "Y"}


def parse_int(value: str) -> int:
    value = (value or "").strip()
    if not value:
        return 0
    return int(float(value))


def parse_decimal(value: str) -> Decimal:
    value = (value or "").strip()
    if not value:
        return Decimal("0.00")
    return Decimal(value)


def ensure_part(cur, row) -> Tuple[int, bool]:
    cur.execute(
        """
        INSERT INTO parts (part_number, name, service_type, category, description, manufacturer_part_number, active)
        VALUES (%s, %s, %s, %s, %s, NULLIF(%s, ''), %s)
        ON CONFLICT (part_number) DO UPDATE
        SET name = EXCLUDED.name,
            service_type = EXCLUDED.service_type,
            category = EXCLUDED.category,
            description = EXCLUDED.description,
            manufacturer_part_number = EXCLUDED.manufacturer_part_number,
            active = EXCLUDED.active
        RETURNING id, xmax = 0;
        """,
        (
            row["part_number"],
            row["part_name"],
            row["service_type"],
            row["category"] or "main_component",
            row["part_description"] or f"{row['part_name']} auto-imported",
            row.get("manufacturer_part_number") or "",
            parse_bool(row.get("active", "TRUE")),
        ),
    )
    part_id, inserted = cur.fetchone()
    return part_id, inserted


def ensure_variant(cur, part_id: int, row) -> bool:
    supplier_raw = (row.get("supplier_id") or "").strip()
    supplier_id = int(supplier_raw) if supplier_raw else None
    cur.execute(
        """
        INSERT INTO part_variants (
            part_id, sku, color, grade, cost, quantity_on_hand,
            quantity_reserved, reorder_threshold, reorder_quantity,
            supplier_id, active
        )
        VALUES (%s, %s, NULLIF(%s, ''), %s, %s, %s, %s, %s, %s, %s, %s)
        ON CONFLICT (sku) DO UPDATE
        SET part_id = EXCLUDED.part_id,
            color = EXCLUDED.color,
            grade = EXCLUDED.grade,
            cost = EXCLUDED.cost,
            quantity_on_hand = EXCLUDED.quantity_on_hand,
            quantity_reserved = EXCLUDED.quantity_reserved,
            reorder_threshold = EXCLUDED.reorder_threshold,
            reorder_quantity = EXCLUDED.reorder_quantity,
            supplier_id = EXCLUDED.supplier_id,
            active = EXCLUDED.active
        RETURNING xmax = 0;
        """,
        (
            part_id,
            row["sku"],
            row.get("color", ""),
            row["grade"],
            parse_decimal(row["cost"]),
            parse_int(row.get("quantity_on_hand")),
            parse_int(row.get("quantity_reserved")),
            parse_int(row.get("reorder_threshold")),
            parse_int(row.get("reorder_quantity")),
            supplier_id,
            parse_bool(row.get("active", "TRUE")),
        ),
    )
    inserted = cur.fetchone()[0]
    return inserted


def ensure_compatibility(cur, part_id: int, device_ids: str) -> int:
    if not device_ids:
        return 0
    inserted = 0
    for raw_id in device_ids.split(","):
        device_id = raw_id.strip()
        if not device_id:
            continue
        cur.execute(
            "SELECT 1 FROM part_compatibility WHERE part_id = %s AND device_id = %s",
            (part_id, int(device_id)),
        )
        if cur.fetchone():
            continue
        cur.execute(
            """
            INSERT INTO part_compatibility (part_id, device_id)
            VALUES (%s, %s)
            """,
            (part_id, int(device_id)),
        )
        inserted += 1
    return inserted


def main() -> None:
    if not CSV_PATH.exists():
        raise SystemExit(f"{CSV_PATH} not found. Run the normalizer first.")

    stats = ImportStats()
    conn = psycopg2.connect(dbname="medicore", user="postgres")
    conn.autocommit = False
    cur = conn.cursor()

    with CSV_PATH.open() as handle:
        reader = csv.DictReader(handle)
        for row in reader:
            part_id, part_inserted = ensure_part(cur, row)
            if part_inserted:
                stats.parts_inserted += 1
            else:
                stats.parts_updated += 1

            variant_inserted = ensure_variant(cur, part_id, row)
            if variant_inserted:
                stats.variants_inserted += 1
            else:
                stats.variants_updated += 1

            stats.compat_inserted += ensure_compatibility(cur, part_id, row.get("compatible_device_ids", ""))

    conn.commit()
    cur.close()
    conn.close()

    print(f"Parts inserted: {stats.parts_inserted}")
    print(f"Parts updated: {stats.parts_updated}")
    print(f"Variants inserted: {stats.variants_inserted}")
    print(f"Variants updated: {stats.variants_updated}")
    print(f"Compatibility rows inserted: {stats.compat_inserted}")


if __name__ == "__main__":
    main()