"""CRUD-сервис для own_planned_project (#1169 PR1, ТЗ §25.3).

API:
- create_own_project(db, created_by, payload) → OwnPlannedProjectOut
- list_own_projects(db, filters..., limit, offset) → OwnPlannedProjectList
- get_own_project(db, project_id) → OwnPlannedProjectOut | None
- update_own_project(db, project_id, payload) → OwnPlannedProjectOut | None
- delete_own_project(db, project_id) → bool

Паттерн: raw SQL через SQLAlchemy text() + CAST(:x AS type), psycopg v3 (зеркало
app/services/insights.py #948). db.commit() после каждой мутации (regression #261).

«Будущие/планируемые» проекты приватного пайплайна — второй источник §25.3-движка
каннибализации (парный к «текущим» из domrf, см. site_finder/own_portfolio.py).
created_by берётся из X-Authenticated-User (НЕ из тела). planned_release_month
нормализуется к ПЕРВОМУ числу месяца (ось тайминга §25.3) через date_trunc в SQL.

Access control: backend rbac_guard (app/main.py) хард-блокирует ТОЛЬКО /api/v1/admin/*.
На /api/v1/own-projects pilot отсекается FRONTEND-овым RouteGuard + Caddy, НЕ бэкендом.
"""

from __future__ import annotations

import json
import logging
from typing import Any

from sqlalchemy import text

from app.schemas.own_project import (
    OwnPlannedProjectCreate,
    OwnPlannedProjectList,
    OwnPlannedProjectOut,
    OwnPlannedProjectUpdate,
)

logger = logging.getLogger(__name__)

# ── SQL ────────────────────────────────────────────────────────────────────────

# geom не отдаём (бинарь); lon/lat достаточно для фронта/карты. unit_mix — jsonb,
# psycopg3 отдаёт его как dict/None напрямую.
_SELECT_COLS = """
    id, created_by, name, district, obj_class, planned_release_month,
    price_min_per_m2, price_max_per_m2, unit_mix, lon, lat, created_at, updated_at
"""

# planned_release_month нормализуем к 1-му числу месяца (date_trunc → ось тайминга).
# unit_mix передаём как jsonb через CAST(:unit_mix AS jsonb) — bind принимает
# JSON-строку (сериализуем в сервисе), psycopg v3 CAST (НИКОГДА :x::type).
_INSERT_SQL = f"""
    INSERT INTO own_planned_project
        (created_by, name, district, obj_class, planned_release_month,
         price_min_per_m2, price_max_per_m2, unit_mix, lon, lat)
    VALUES
        (:created_by, :name, :district, :obj_class,
         CAST(date_trunc('month', CAST(:planned_release_month AS date)) AS date),
         CAST(:price_min_per_m2 AS numeric), CAST(:price_max_per_m2 AS numeric),
         CAST(:unit_mix AS jsonb),
         CAST(:lon AS double precision), CAST(:lat AS double precision))
    RETURNING {_SELECT_COLS}
"""

_SELECT_BY_ID = f"""
    SELECT {_SELECT_COLS}
      FROM own_planned_project
     WHERE id = :project_id
"""

_DELETE_SQL = """
    DELETE FROM own_planned_project
     WHERE id = :project_id
    RETURNING id
"""


# ── Row mapper ─────────────────────────────────────────────────────────────────


def _row_to_out(r: Any) -> OwnPlannedProjectOut:
    return OwnPlannedProjectOut(
        id=int(r["id"]),
        created_by=r["created_by"],
        name=r["name"],
        district=r["district"],
        obj_class=r["obj_class"],
        planned_release_month=r["planned_release_month"],
        price_min_per_m2=(
            float(r["price_min_per_m2"]) if r["price_min_per_m2"] is not None else None
        ),
        price_max_per_m2=(
            float(r["price_max_per_m2"]) if r["price_max_per_m2"] is not None else None
        ),
        unit_mix=r["unit_mix"],
        lon=float(r["lon"]) if r["lon"] is not None else None,
        lat=float(r["lat"]) if r["lat"] is not None else None,
        created_at=r["created_at"],
        updated_at=r["updated_at"],
    )


def _unit_mix_param(unit_mix: dict[str, float] | None) -> str | None:
    """Сериализовать unit_mix в JSON-строку для CAST(:unit_mix AS jsonb). None → None."""
    if unit_mix is None:
        return None
    return json.dumps(unit_mix)


# ── CRUD ───────────────────────────────────────────────────────────────────────


def create_own_project(
    db: Any, created_by: str, payload: OwnPlannedProjectCreate
) -> OwnPlannedProjectOut:
    """Создать own_planned_project. created_by — автор из X-Authenticated-User."""
    row = (
        db.execute(
            text(_INSERT_SQL),
            {
                "created_by": created_by,
                "name": payload.name,
                "district": payload.district,
                "obj_class": payload.obj_class,
                "planned_release_month": payload.planned_release_month,
                "price_min_per_m2": payload.price_min_per_m2,
                "price_max_per_m2": payload.price_max_per_m2,
                "unit_mix": _unit_mix_param(payload.unit_mix),
                "lon": payload.lon,
                "lat": payload.lat,
            },
        )
        .mappings()
        .first()
    )
    db.commit()
    assert row is not None, "INSERT RETURNING вернул пустой результат"
    logger.info(
        "own_planned_project created: id=%s by=%s class=%s district=%s release=%s",
        row["id"],
        created_by,
        payload.obj_class,
        payload.district,
        payload.planned_release_month,
    )
    return _row_to_out(row)


def list_own_projects(
    db: Any,
    *,
    district: str | None = None,
    obj_class: str | None = None,
    created_by: str | None = None,
    limit: int = 50,
    offset: int = 0,
) -> OwnPlannedProjectList:
    """Список планируемых проектов с фильтрами + пагинацией (зеркало list_insights)."""
    where: list[str] = []
    params: dict[str, Any] = {"lim": limit, "off": offset}
    if district is not None:
        where.append("district = :district")
        params["district"] = district
    if obj_class is not None:
        where.append("obj_class = :obj_class")
        params["obj_class"] = obj_class
    if created_by is not None:
        where.append("created_by = :created_by")
        params["created_by"] = created_by

    where_sql = "WHERE " + " AND ".join(where) if where else ""

    rows = (
        db.execute(
            text(
                f"SELECT {_SELECT_COLS} FROM own_planned_project {where_sql} "
                "ORDER BY created_at DESC LIMIT :lim OFFSET :off"
            ),
            params,
        )
        .mappings()
        .all()
    )
    total = db.execute(
        text(f"SELECT COUNT(*) FROM own_planned_project {where_sql}"),
        {k: v for k, v in params.items() if k not in ("lim", "off")},
    ).scalar_one()

    return OwnPlannedProjectList(
        total=int(total or 0),
        limit=limit,
        offset=offset,
        rows=[_row_to_out(r) for r in rows],
    )


def get_own_project(db: Any, project_id: int) -> OwnPlannedProjectOut | None:
    """Вернуть один планируемый проект по id."""
    row = db.execute(text(_SELECT_BY_ID), {"project_id": project_id}).mappings().first()
    if row is None:
        return None
    return _row_to_out(row)


def update_own_project(
    db: Any, project_id: int, payload: OwnPlannedProjectUpdate
) -> OwnPlannedProjectOut | None:
    """Partial update. Возвращает None если не найден (зеркало update_insight).

    Динамический SET по переданным полям. `is None` как «не передано» — для всех
    полей корректно (нет boolean-полей, где False ≠ None имеет значение).
    """
    existing = get_own_project(db, project_id)
    if existing is None:
        return None

    sets: list[str] = ["updated_at = NOW()"]
    params: dict[str, Any] = {"project_id": project_id}

    if payload.name is not None:
        sets.append("name = :name")
        params["name"] = payload.name
    if payload.district is not None:
        sets.append("district = :district")
        params["district"] = payload.district
    if payload.obj_class is not None:
        sets.append("obj_class = :obj_class")
        params["obj_class"] = payload.obj_class
    if payload.planned_release_month is not None:
        # Нормализуем к 1-му числу месяца (как INSERT) — ось тайминга §25.3.
        sets.append(
            "planned_release_month = "
            "CAST(date_trunc('month', CAST(:planned_release_month AS date)) AS date)"
        )
        params["planned_release_month"] = payload.planned_release_month
    if payload.price_min_per_m2 is not None:
        sets.append("price_min_per_m2 = CAST(:price_min_per_m2 AS numeric)")
        params["price_min_per_m2"] = payload.price_min_per_m2
    if payload.price_max_per_m2 is not None:
        sets.append("price_max_per_m2 = CAST(:price_max_per_m2 AS numeric)")
        params["price_max_per_m2"] = payload.price_max_per_m2
    if payload.unit_mix is not None:
        sets.append("unit_mix = CAST(:unit_mix AS jsonb)")
        params["unit_mix"] = _unit_mix_param(payload.unit_mix)
    # lon/lat меняем независимо — geom GENERATED пересчитается (NULL если одна NULL).
    if payload.lon is not None:
        sets.append("lon = CAST(:lon AS double precision)")
        params["lon"] = payload.lon
    if payload.lat is not None:
        sets.append("lat = CAST(:lat AS double precision)")
        params["lat"] = payload.lat

    if len(sets) > 1:
        db.execute(
            text(f"UPDATE own_planned_project SET {', '.join(sets)} WHERE id = :project_id"),
            params,
        )
        db.commit()

    return get_own_project(db, project_id)


def delete_own_project(db: Any, project_id: int) -> bool:
    """Удалить планируемый проект (hard delete — зеркало insight). True если удалён."""
    result = db.execute(text(_DELETE_SQL), {"project_id": project_id}).first()
    if result is None:
        return False
    db.commit()
    logger.info("own_planned_project deleted: id=%s", project_id)
    return True