from __future__ import annotations

import json
from pathlib import Path
from typing import Dict, Iterable, List, Tuple

from sqlalchemy.dialects.postgresql import insert
from sqlalchemy import text
from sqlalchemy.orm import Session

from db.models import AttributeDef, AttributeOption

_SEE_OPTIONS_MARKER = "see attribute options column"
_INSTRUCTION_PREFIX = "if you add"

_seeded = False


def seed_attribute_registry_if_available(session: Session, path: Path) -> bool:
    global _seeded
    if not path.exists():
        return False

    registry = _load_registry(path)
    if not registry:
        return False

    if _seeded and not _needs_options_backfill(session):
        return False

    _upsert_attribute_defs(session, registry)
    _upsert_attribute_options(session, registry)
    _seeded = True
    return True


def _needs_options_backfill(session: Session) -> bool:
    row = session.execute(
        text("SELECT 1 FROM attribute_def WHERE options_json IS NULL LIMIT 1")
    ).first()
    return row is not None


def _load_registry(path: Path) -> Dict[str, dict]:
    try:
        data = json.loads(path.read_text(encoding="utf-8"))
    except Exception:
        return {}
    if not isinstance(data, dict):
        return {}
    return {
        str(code).strip(): meta
        for code, meta in data.items()
        if not _is_instruction_key(code)
    }


def _is_instruction_key(code: object) -> bool:
    if code is None:
        return False
    text = str(code).strip().lower()
    return text.startswith(_INSTRUCTION_PREFIX)


def _normalize_type(value: object) -> str:
    if value is None:
        return ""
    return str(value).strip().lower()


def _normalize_options(options: object) -> List[str]:
    if not isinstance(options, list):
        return []
    out: List[str] = []
    for item in options:
        if item is None:
            continue
        text = str(item).strip()
        if not text:
            continue
        if text.lower() == _SEE_OPTIONS_MARKER:
            continue
        out.append(text)
    return out


def _allows_free_text(options: Iterable[str]) -> bool:
    return not list(options)


def _upsert_attribute_defs(session: Session, registry: Dict[str, dict]) -> None:
    rows: List[dict] = []
    for code, meta in registry.items():
        if not code:
            continue
        meta = meta or {}
        options = _normalize_options(meta.get("options"))
        rows.append(
            {
                "attribute_code": str(code).strip(),
                "frontend_label": str(code).strip(),
                "type": _normalize_type(meta.get("type")),
                "is_required": bool(meta.get("required", False)),
                "allows_free_text": _allows_free_text(options),
                "options_json": options,
            }
        )

    if not rows:
        return

    stmt = insert(AttributeDef).values(rows)
    stmt = stmt.on_conflict_do_update(
        index_elements=["attribute_code"],
        set_={
            "frontend_label": stmt.excluded.frontend_label,
            "type": stmt.excluded.type,
            "is_required": stmt.excluded.is_required,
            "allows_free_text": stmt.excluded.allows_free_text,
            "options_json": stmt.excluded.options_json,
            "updated_at": stmt.excluded.updated_at,
        },
    )
    session.execute(stmt)


def _upsert_attribute_options(session: Session, registry: Dict[str, dict]) -> None:
    rows: List[dict] = []
    for code, meta in registry.items():
        if not code:
            continue
        options = _normalize_options((meta or {}).get("options"))
        for opt in options:
            rows.append({"attribute_code": str(code).strip(), "option_value": opt})

    if not rows:
        return

    stmt = insert(AttributeOption).values(rows)
    stmt = stmt.on_conflict_do_nothing(index_elements=["attribute_code", "option_value"])
    session.execute(stmt)