"""
Contextra Analytics — Zone Store (시나리오 ROI 영속화)  [MLOps 발판 Phase 2]

역할:
  - zones 테이블 CRUD + geometry 검증 (정규화 polygon/rect, 0~1 좌표)
  - 시나리오 엔진이 소비하는 활성 zone 조회 (name → points+version)

설계 원칙:
  - 좌표는 항상 정규화(0~1) 저장 — 해상도 독립. 픽셀 변환은 평가 시점(frame_size)에 수행.
  - version은 편집 시 +1 — 이벤트 detection_result에 기록되어 "어떤 zone 버전으로
    발화했는지" 감사 추적 가능 (UI 드로잉 ↔ 이벤트 연결).
  - DB 실패는 호출부에서 non-fatal 처리 결정 (zone 부재 = 해당 스트림의
    zone-required 시나리오 비활성, 파이프라인은 계속 동작).

geometry 스키마:
  {"type": "polygon", "points": [[x,y], [x,y], ...]}   # >=3점, 각 좌표 0~1
  {"type": "rect",    "points": [[x1,y1],[x2,y2],[x3,y3],[x4,y4]]}  # 4점 (정사각/직사각)
"""

from __future__ import annotations

import json
import logging
from typing import Any, Dict, List, Optional


logger = logging.getLogger(__name__)


class ZoneError(ValueError):
    """zone geometry 스키마/범위 검증 실패."""


# ---------------------------------------------------------------------------
# geometry 검증
# ---------------------------------------------------------------------------

def validate_geometry(geometry: Any) -> dict:
    """geometry dict 검증 → 정규화 dict 반환. 실패 시 ZoneError.

    허용: {"type": "polygon"|"rect", "points": [[x,y],...]} (좌표 0~1).
    """
    if not isinstance(geometry, dict):
        raise ZoneError("geometry must be a mapping")
    gtype = str(geometry.get("type") or "").lower()
    points_raw = geometry.get("points")
    if gtype == "rect":
        min_pts = 4
    elif gtype == "polygon":
        min_pts = 3
    else:
        raise ZoneError(f"geometry.type must be 'polygon' or 'rect', got {gtype!r}")

    if not isinstance(points_raw, (list, tuple)) or len(points_raw) < min_pts:
        raise ZoneError(
            f"geometry.points must have >= {min_pts} [x,y] pairs for type={gtype!r}, "
            f"got {len(points_raw) if isinstance(points_raw, (list, tuple)) else type(points_raw).__name__}"
        )

    points: List[List[float]] = []
    for i, p in enumerate(points_raw):
        if not isinstance(p, (list, tuple)) or len(p) != 2:
            raise ZoneError(f"geometry.points[{i}] must be [x, y], got {p!r}")
        try:
            x, y = float(p[0]), float(p[1])
        except (TypeError, ValueError):
            raise ZoneError(f"geometry.points[{i}] coordinates must be numeric, got {p!r}")
        if not (0.0 <= x <= 1.0 and 0.0 <= y <= 1.0):
            raise ZoneError(
                f"geometry.points[{i}]=({x},{y}) out of normalized range [0,1] — "
                f"coordinates must be frame-relative (0~1), not pixels"
            )
        points.append([round(x, 6), round(y, 6)])

    return {"type": gtype, "points": points}


def validate_zone_name(name: str) -> str:
    """zone 논리 이름 검증 — 시나리오가 문자열로 참조하므로 형식 제한."""
    n = (name or "").strip()
    if not n or len(n) > 64:
        raise ZoneError(f"zone name must be 1-64 chars, got {name!r}")
    # 파일/쿼리 안전성: 알파벳·숫자·_·- 만 허용 (restricted_zone 등).
    if not all(c.isalnum() or c in "_-" for c in n):
        raise ZoneError(f"zone name may contain only [A-Za-z0-9_-], got {name!r}")
    return n


# ---------------------------------------------------------------------------
# ZoneStore — zones 테이블 접근 (MetadataStorage 커넥션 재사용)
# ---------------------------------------------------------------------------

class ZoneStore:
    """zones 테이블 CRUD. MetadataStorage의 get_cursor() 컨텍스트를 재사용한다."""

    def __init__(self, storage):
        self._storage = storage

    # -- read ---------------------------------------------------------------

    def list_zones(self, stream_id: Optional[str] = None,
                   include_inactive: bool = False) -> List[dict]:
        """zone 목록 (geometry 포함). stream_id 생략 시 전체."""
        sql = ("SELECT stream_id, site_id, name, geometry, is_active, version, "
               "created_at, updated_at FROM zones")
        conds, params = [], []
        if stream_id:
            conds.append("stream_id = %s")
            params.append(stream_id)
        if not include_inactive:
            conds.append("is_active = TRUE")
        if conds:
            sql += " WHERE " + " AND ".join(conds)
        sql += " ORDER BY stream_id, name"

        with self._storage.get_cursor() as cur:
            cur.execute(sql, params or None)
            rows = cur.fetchall()

        out = []
        for r in rows:
            geom = r[3]
            if isinstance(geom, str):  # JSONB 역직렬화 실패 대비 (레거시)
                try:
                    geom = json.loads(geom)
                except Exception:
                    geom = {}
            out.append({
                "stream_id": r[0], "site_id": r[1], "name": r[2],
                "geometry": geom, "is_active": bool(r[4]), "version": int(r[5]),
                "created_at": r[6].isoformat() if r[6] else None,
                "updated_at": r[7].isoformat() if r[7] else None,
            })
        return out

    def get_active_zones(self, stream_id: str) -> Dict[str, dict]:
        """활성 zone을 {name: {"points": [[x,y],...], "version": n}}으로 반환.

        시나리오 엔진이 바로 소비하는 형태 (정규화 좌표).
        """
        out: Dict[str, dict] = {}
        for z in self.list_zones(stream_id=stream_id):
            pts = (z["geometry"] or {}).get("points")
            if not pts:
                continue
            out[z["name"]] = {"points": pts, "version": z["version"]}
        return out

    # -- write --------------------------------------------------------------

    def upsert_zone(self, stream_id: str, site_id: str, name: str,
                    geometry: Any, is_active: Optional[bool] = None) -> dict:
        """zone 생성 또는 갱신 (갱신 시 version+1). 반환: 저장된 zone dict."""
        name = validate_zone_name(name)
        geom = validate_geometry(geometry)

        with self._storage.get_cursor() as cur:
            # 기존 버전 조회 (version bump용)
            cur.execute(
                "SELECT version FROM zones WHERE stream_id = %s AND name = %s",
                (stream_id, name),
            )
            row = cur.fetchone()
            new_version = (row[0] + 1) if row else 1

            cur.execute(
                """INSERT INTO zones (stream_id, site_id, name, geometry, is_active, version)
                   VALUES (%s, %s, %s, %s, %s, %s)
                   ON CONFLICT (stream_id, name) DO UPDATE SET
                     site_id = EXCLUDED.site_id,
                     geometry = EXCLUDED.geometry,
                     is_active = COALESCE(EXCLUDED.is_active, zones.is_active),
                     version = EXCLUDED.version,
                     updated_at = NOW()""",
                (stream_id, site_id, name, json.dumps(geom),
                 True if is_active is None else bool(is_active), new_version),
            )

        logger.info("[zones] upsert %s/%s v%d (%d points)", stream_id, name,
                    new_version, len(geom["points"]))
        for z in self.list_zones(stream_id=stream_id, include_inactive=True):
            if z["name"] == name:
                return z
        raise ZoneError(f"zone {stream_id}/{name} not found after upsert")

    def delete_zone(self, stream_id: str, name: str) -> bool:
        """zone 삭제. 반환: 삭제 여부."""
        with self._storage.get_cursor() as cur:
            cur.execute(
                "DELETE FROM zones WHERE stream_id = %s AND name = %s",
                (stream_id, name),
            )
            return cur.rowcount > 0

    def set_active(self, stream_id: str, name: str, is_active: bool) -> bool:
        """활성 토글. 반환: 변경 여부."""
        with self._storage.get_cursor() as cur:
            cur.execute(
                "UPDATE zones SET is_active = %s, updated_at = NOW() "
                "WHERE stream_id = %s AND name = %s",
                (bool(is_active), stream_id, name),
            )
            return cur.rowcount > 0


# 전역 싱글턴 — main.py가 import 시점에 생성 (storage는 지연 바인딩).
_zone_store: Optional[ZoneStore] = None


def get_zone_store() -> ZoneStore:
    """ZoneStore 싱글턴 (metadata_storage.storage 지연 바인딩)."""
    global _zone_store
    if _zone_store is None:
        from app.metadata_storage import storage  # lazy — DB 없어도 import 가능
        _zone_store = ZoneStore(storage)
    return _zone_store