"""Уценка: чтение ch-stage и локальный архив полных снимков.

Транспорт использует только read-only MCP ``ch-stage`` из настроек Codex.
Секрет берётся из переменной окружения и никогда не сохраняется в базе.
"""

from __future__ import annotations

import json
import os
from datetime import date, datetime
from decimal import Decimal
from io import BytesIO
from pathlib import Path
import re
import tomllib
from typing import Any
import urllib.error
import urllib.request
from zoneinfo import ZoneInfo

import psycopg
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side


SOURCE_NAME = "triovist.products_broken"
_TZ = ZoneInfo("Europe/Minsk")
PER_PAGE = 100

CURRENT_PRODUCTS_SQL = """
WITH
snapshot AS (
    SELECT max(created_at) AS snapshot_at
    FROM triovist.products_broken
),
audit AS (
    SELECT
        product_id,
        argMax(base_price, tuple(date, created_at)) AS current_new_price,
        argMax(purchase_cost, tuple(date, created_at)) AS accounting_cost,
        argMax(discount_set_date, tuple(date, created_at)) AS discount_set_date,
        argMax(condition_type, tuple(date, created_at)) AS condition_type
    FROM broken_products.broken_discounts_products_audit FINAL
    WHERE date >= toDate((SELECT snapshot_at FROM snapshot)) - 31
    GROUP BY product_id
),
daily AS (
    SELECT
        date,
        product_id,
        argMax(price, created_at) AS price,
        argMax(broken_percent, created_at) AS broken_percent,
        sum(balance) AS total_balance,
        arrayStringConcat(arraySort(groupUniqArray(warehouse)), '|') AS warehouses
    FROM triovist.products_broken_mv FINAL
    WHERE product_id LIKE '%расп%'
      AND product_id NOT ILIKE '%некомплект%'
    GROUP BY date, product_id
),
daily_changes AS (
    SELECT *,
        row_number() OVER (PARTITION BY product_id ORDER BY date) AS rn,
        lagInFrame(tuple(price, broken_percent), 1, tuple(price, broken_percent))
          OVER (PARTITION BY product_id ORDER BY date
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS previous_price,
        lagInFrame(tuple(total_balance, warehouses), 1,
                   tuple(total_balance, warehouses))
          OVER (PARTITION BY product_id ORDER BY date
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS previous_stock
    FROM daily
),
history AS (
    SELECT
        product_id,
        min(date) AS history_available_from,
        countIf(rn > 1 AND tuple(price, broken_percent) != previous_price) AS repricing_count,
        maxIf(toNullable(date),
              rn > 1 AND tuple(total_balance, warehouses) != previous_stock)
            AS last_stock_change_date
    FROM daily_changes
    GROUP BY product_id
)
SELECT
    p.product_id,
    toString(p.origin_product_id) AS origin_product_id_text,
    p.product_code,
    p.origin_product_code,
    p.product_category_id,
    p.product_type_id,
    dictGet('triovist.dict_products', 'Name',
            tuple(toString(p.origin_product_id))) AS product_name,
    dictGet('triovist.dict_product_manufacturers', 'Name',
            tuple(toString(p.manufacturer_id))) AS brand,
    dictGet('triovist.dict_site_products', 'model', p.origin_product_id) AS model,
    dictGet('triovist.dict_site_products', 'link', p.origin_product_id) AS product_link,
    p.grade,
    toString(p.date_of_creation) AS date_of_creation_text,
    toString(p.last_price_change) AS last_price_change_text,
    p.description,
    p.price / 100 AS site_price,
    p.broken_percent,
    p.has_price,
    p.has_balances,
    p.warehouse,
    p.balance,
    dictGet('triovist.dict_product_categories_path', 'category_name_1',
            p.origin_product_id) AS category_1,
    dictGet('triovist.dict_product_categories_path', 'category_name_2',
            p.origin_product_id) AS category_2,
    dictGet('triovist.dict_product_categories_path', 'category_name_3',
            p.origin_product_id) AS category_3,
    dictGet('triovist.dict_product_categories_path', 'category_name_4',
            p.origin_product_id) AS category_4,
    nullIf(dictGet('triovist.dict_site_products', 'depth', p.origin_product_id), 0) * 100
        AS depth_cm,
    nullIf(dictGet('triovist.dict_site_products', 'width', p.origin_product_id), 0) * 100
        AS width_cm,
    nullIf(dictGet('triovist.dict_site_products', 'height', p.origin_product_id), 0) * 100
        AS height_cm,
    nullIf(dictGet('triovist.dict_site_products', 'weight', p.origin_product_id), 0)
        AS weight_kg,
    a.current_new_price,
    a.accounting_cost,
    toString(a.discount_set_date) AS discount_set_date_text,
    a.condition_type,
    h.repricing_count,
    toString(h.history_available_from) AS history_available_from_text,
    toString(h.last_stock_change_date) AS last_stock_change_date_text,
    toString(p.created_at) AS snapshot_at_text
FROM triovist.products_broken p FINAL
LEFT JOIN audit a ON a.product_id = p.product_id
LEFT JOIN history h ON h.product_id = p.product_id
WHERE p.created_at = (SELECT snapshot_at FROM snapshot)
  AND p.product_id LIKE '%расп%'
  AND p.product_id NOT ILIKE '%некомплект%'
ORDER BY p.product_id, p.warehouse
"""


class _NoRedirect(urllib.request.HTTPRedirectHandler):
    def redirect_request(self, req, fp, code, msg, headers, newurl):
        raise RuntimeError("ch-stage изменил адрес; проверьте настройки Codex")


def _mcp_server() -> tuple[str, dict[str, str]]:
    root = Path(os.environ.get("CODEX_HOME") or Path.home() / ".codex")
    path = root / "config.toml"
    if not path.is_file():
        raise RuntimeError("Настройки Codex не найдены")
    config = tomllib.loads(path.read_text(encoding="utf-8-sig"))
    server = config.get("mcp_servers", {}).get("ch-stage") or {}
    if not server or not server.get("enabled", True) or not server.get("url"):
        raise RuntimeError("Подключение ch-stage не настроено")
    headers = dict(server.get("http_headers") or {})
    for header, variable in (server.get("env_http_headers") or {}).items():
        value = os.environ.get(variable)
        if not value:
            raise RuntimeError(f"Не задана переменная окружения {variable}")
        headers[header] = value
    variable = server.get("bearer_token_env_var")
    if variable:
        token = os.environ.get(variable)
        if not token:
            raise RuntimeError(f"Не задана переменная окружения {variable}")
        headers["Authorization"] = "Bearer " + token
    if not any(name.lower() == "authorization" for name in headers):
        raise RuntimeError("Не настроена авторизация ch-stage")
    return str(server["url"]), headers


def _parse_rpc(body: str, expected_id: int) -> dict[str, Any]:
    body = body.strip()
    try:
        value = json.loads(body)
        if isinstance(value, dict) and value.get("id") == expected_id:
            return value
    except json.JSONDecodeError:
        pass
    for block in re.split(r"\r?\n\r?\n", body):
        data = "\n".join(
            line[5:].lstrip() for line in block.splitlines()
            if line.startswith("data:"))
        if not data:
            continue
        try:
            value = json.loads(data)
        except json.JSONDecodeError:
            continue
        if isinstance(value, dict) and value.get("id") == expected_id:
            return value
    raise RuntimeError("ch-stage не вернул ожидаемый ответ")


class _McpClient:
    def __init__(self):
        self.url, self.auth_headers = _mcp_server()
        self.session_id: str | None = None
        self.protocol = "2025-03-26"
        self.request_id = 0
        self.opener = urllib.request.build_opener(_NoRedirect())

    def _post(self, payload: dict[str, Any], timeout: int = 330) -> dict[str, Any] | None:
        headers = {
            "Content-Type": "application/json",
            "Accept": "application/json, text/event-stream",
            **self.auth_headers,
        }
        if self.session_id:
            headers["mcp-session-id"] = self.session_id
            headers["MCP-Protocol-Version"] = self.protocol
        request = urllib.request.Request(
            self.url, data=json.dumps(payload).encode("utf-8"),
            headers=headers, method="POST")
        try:
            with self.opener.open(request, timeout=timeout) as response:
                self.session_id = response.headers.get("mcp-session-id") or self.session_id
                if "id" not in payload:
                    return None
                body = response.read().decode("utf-8", "replace")
        except urllib.error.HTTPError as exc:
            raise RuntimeError(f"ch-stage вернул HTTP {exc.code}") from None
        except (urllib.error.URLError, TimeoutError, OSError):
            raise RuntimeError("Нет соединения с ch-stage; проверьте сеть/VPN") from None
        result = _parse_rpc(body, int(payload["id"]))
        if "error" in result:
            raise RuntimeError("ch-stage отклонил запрос")
        return result

    def query(self, sql: str) -> tuple[list[str], list[list[Any]]]:
        if not re.match(r"^\s*(SELECT|WITH)\b", sql, re.I):
            raise RuntimeError("Для ch-stage разрешены только запросы чтения")
        self.request_id += 1
        initialized = self._post({
            "jsonrpc": "2.0", "id": self.request_id, "method": "initialize",
            "params": {"protocolVersion": self.protocol, "capabilities": {},
                       "clientInfo": {"name": "ooucr-discounts", "version": "1.0"}},
        }, timeout=20) or {}
        self.protocol = initialized.get("result", {}).get("protocolVersion", self.protocol)
        self._post({"jsonrpc": "2.0", "method": "notifications/initialized"}, timeout=20)
        self.request_id += 1
        response = self._post({
            "jsonrpc": "2.0", "id": self.request_id, "method": "tools/call",
            "params": {"name": "run_query", "arguments": {"query": sql}},
        }) or {}
        result = response.get("result") or {}
        if result.get("isError"):
            raise RuntimeError("Запрос уценки отклонён ch-stage")
        for block in result.get("content", []):
            if block.get("type") != "text":
                continue
            try:
                data = json.loads(block.get("text", ""))
                if isinstance(data, dict) and isinstance(data.get("result"), str):
                    data = json.loads(data["result"])
            except (json.JSONDecodeError, TypeError):
                continue
            if isinstance(data, dict) and "columns" in data and "rows" in data:
                return list(data["columns"]), list(data["rows"])
        raise RuntimeError("Формат ответа ch-stage изменился")


def _snapshot_datetime(value: str) -> datetime:
    parsed = datetime.fromisoformat(value.strip())
    return parsed.replace(tzinfo=_TZ) if parsed.tzinfo is None else parsed


def _date_or_none(value: Any) -> date | None:
    text = str(value or "").strip()
    if not text or text == "1970-01-01":
        return None
    return date.fromisoformat(text[:10])


def _decimal_or_none(value: Any) -> Decimal | None:
    return Decimal(str(value)) if value is not None else None


def _product_row(columns: list[str], values: list[Any]) -> dict[str, Any]:
    row = dict(zip(columns, values))
    product_id = str(row.get("product_id") or "").strip()
    if not product_id:
        raise RuntimeError("ch-stage вернул карточку без product_id")
    return {
        "product_id": product_id,
        "origin_product_id": int(row["origin_product_id_text"]) if row.get("origin_product_id_text") else None,
        "product_code": str(row.get("product_code") or "").strip() or None,
        "origin_product_code": str(row.get("origin_product_code") or "").strip() or None,
        "product_category_id": int(row["product_category_id"]) if row.get("product_category_id") is not None else None,
        "product_type_id": int(row["product_type_id"]) if row.get("product_type_id") is not None else None,
        "product_name": str(row.get("product_name") or "").strip() or product_id,
        "brand": str(row.get("brand") or "").strip() or None,
        "model": str(row.get("model") or "").strip() or None,
        "product_url": ("https://www.21vek.by" + str(row["product_link"]).strip()
                        if str(row.get("product_link") or "").strip().startswith("/")
                        else str(row.get("product_link") or "").strip() or None),
        "grade": str(row.get("grade") or "").strip() or None,
        "date_of_creation": _date_or_none(row.get("date_of_creation_text")),
        "last_price_change": _date_or_none(row.get("last_price_change_text")),
        "description": str(row.get("description") or "").strip() or None,
        "site_price": _decimal_or_none(row.get("site_price")),
        "discount_percent": _decimal_or_none(row.get("broken_percent")),
        "has_price": bool(row.get("has_price")),
        "has_balances": bool(row.get("has_balances", True)),
        "warehouse": str(row.get("warehouse") or "").strip() or "Склад не указан",
        "balance": int(row.get("balance", 1)),
        "category_1": str(row.get("category_1") or "").strip() or None,
        "category_2": str(row.get("category_2") or "").strip() or None,
        "category_3": str(row.get("category_3") or "").strip() or None,
        "category_4": str(row.get("category_4") or "").strip() or None,
        "depth_cm": _decimal_or_none(row.get("depth_cm")),
        "width_cm": _decimal_or_none(row.get("width_cm")),
        "height_cm": _decimal_or_none(row.get("height_cm")),
        "weight_kg": _decimal_or_none(row.get("weight_kg")),
        "current_new_price": _decimal_or_none(row.get("current_new_price")),
        "accounting_cost": _decimal_or_none(row.get("accounting_cost")),
        "discount_set_date": _date_or_none(row.get("discount_set_date_text")),
        "condition_type": str(row.get("condition_type") or "").strip() or None,
        "repricing_count": (int(row["repricing_count"])
                            if row.get("repricing_count") is not None else None),
        "history_available_from": _date_or_none(row.get("history_available_from_text")),
        "last_stock_change_date": _date_or_none(row.get("last_stock_change_date_text")),
        "source_snapshot_at": _snapshot_datetime(str(row["snapshot_at_text"])),
    }


def fetch_current_products() -> dict[str, Any]:
    """Прочитать один полный текущий срез, не изменяя ch-stage."""
    columns, values = _McpClient().query(CURRENT_PRODUCTS_SQL)
    rows = [_product_row(columns, value) for value in values]
    if not rows:
        raise RuntimeError("Текущий срез уценки пуст; архивирование отменено")
    # Ключ источника — (product_id, warehouse). Карточку храним один раз, а
    # складские строки сохраняем отдельно. Это не даёт перемещениям размножить
    # экземпляр в реестре и не теряет остаток на каждом месте хранения.
    unique_rows: dict[str, dict[str, Any]] = {}
    stocks: dict[str, dict[str, int]] = {}
    for row in rows:
        product_id = row["product_id"]
        warehouse = row.pop("warehouse")
        balance = row.pop("balance")
        product_stocks = stocks.setdefault(product_id, {})
        previous_balance = product_stocks.get(warehouse)
        if previous_balance is not None and previous_balance != balance:
            raise RuntimeError(
                "В текущем срезе уценки есть противоречащие остатки "
                f"для {product_id}, склад {warehouse}")
        product_stocks[warehouse] = balance
        previous = unique_rows.get(product_id)
        if previous is None:
            unique_rows[product_id] = row
        elif previous != row:
            raise RuntimeError(
                "В текущем срезе уценки есть противоречащие строки "
                f"для product_id {product_id}")
    rows = []
    stock_rows = []
    for product_id, row in unique_rows.items():
        product_stocks = stocks[product_id]
        row["available_quantity"] = sum(max(0, value) for value in product_stocks.values())
        rows.append(row)
        for warehouse, balance in sorted(product_stocks.items()):
            stock_rows.append({"product_id": product_id, "warehouse": warehouse,
                               "balance": balance,
                               "source_snapshot_at": row["source_snapshot_at"]})
    snapshots = {row["source_snapshot_at"] for row in rows}
    if len(snapshots) != 1:
        raise RuntimeError("ch-stage вернул строки из разных снимков")
    return {"snapshot_at": snapshots.pop(), "rows": rows,
            "stock_rows": stock_rows}


_PRODUCT_FIELDS = (
    "product_id", "origin_product_id", "product_code", "origin_product_code",
    "product_category_id", "product_type_id", "product_name", "brand", "model",
    "product_url", "grade", "date_of_creation", "last_price_change", "description",
    "site_price", "discount_percent", "has_price", "category_1", "category_2",
    "category_3", "category_4", "depth_cm", "width_cm", "height_cm", "weight_kg",
    "current_new_price", "accounting_cost", "discount_set_date", "condition_type",
    "repricing_count", "history_available_from", "last_stock_change_date",
    "available_quantity", "source_snapshot_at",
)


def sync_products(conn: psycopg.Connection, snapshot: dict[str, Any],
                  user_id: int | None) -> dict[str, Any]:
    """Атомарно заменить активный срез; отсутствующие карточки архивировать."""
    snapshot_at = snapshot["snapshot_at"]
    rows = snapshot["rows"]
    stock_rows = snapshot.get("stock_rows") or []
    with conn.cursor() as cur:
        cur.execute(
            "SELECT assortment_enriched FROM app.discount_sync WHERE source_snapshot_at = %s",
            (snapshot_at,))
        existing_sync = cur.fetchone()
        if existing_sync and existing_sync["assortment_enriched"]:
            return {"unchanged": True, "snapshot_at": snapshot_at,
                    "rows_total": len(rows)}
        cur.execute("SELECT max(source_snapshot_at) AS latest FROM app.discount_sync")
        latest = cur.fetchone()["latest"]
        if latest and snapshot_at < latest:
            raise ValueError("Источник вернул снимок старше уже загруженного")

        cur.execute("""
            CREATE TEMP TABLE discount_product_stage (
                product_id text PRIMARY KEY,
                origin_product_id bigint,
                product_code text,
                origin_product_code text,
                product_category_id integer,
                product_type_id integer,
                product_name text NOT NULL,
                brand text,
                model text,
                product_url text,
                grade text,
                date_of_creation date,
                last_price_change date,
                description text,
                site_price numeric(20,2),
                discount_percent numeric(8,2),
                has_price boolean NOT NULL,
                category_1 text,
                category_2 text,
                category_3 text,
                category_4 text,
                depth_cm numeric(12,2),
                width_cm numeric(12,2),
                height_cm numeric(12,2),
                weight_kg numeric(12,3),
                current_new_price numeric(20,2),
                accounting_cost numeric(20,2),
                discount_set_date date,
                condition_type text,
                repricing_count integer,
                history_available_from date,
                last_stock_change_date date,
                available_quantity integer NOT NULL,
                source_snapshot_at timestamptz NOT NULL
            ) ON COMMIT DROP
        """)
        placeholders = ", ".join(["%s"] * len(_PRODUCT_FIELDS))
        cur.executemany(
            f"INSERT INTO discount_product_stage ({', '.join(_PRODUCT_FIELDS)}) VALUES ({placeholders})",
            [tuple(row[field] for field in _PRODUCT_FIELDS) for row in rows])

        cur.execute("""
            CREATE TEMP TABLE discount_product_stock_stage (
                product_id text NOT NULL,
                warehouse text NOT NULL,
                balance integer NOT NULL,
                source_snapshot_at timestamptz NOT NULL,
                PRIMARY KEY (product_id, warehouse)
            ) ON COMMIT DROP
        """)
        if stock_rows:
            cur.executemany("""
                INSERT INTO discount_product_stock_stage
                    (product_id, warehouse, balance, source_snapshot_at)
                VALUES (%s, %s, %s, %s)
            """, [(row["product_id"], row["warehouse"], row["balance"],
                     row["source_snapshot_at"]) for row in stock_rows])

        cur.execute("""
            SELECT count(*) FILTER (WHERE p.product_id IS NULL) AS added,
                   count(*) FILTER (WHERE p.product_id IS NOT NULL AND (
                     NOT p.is_active OR
                     (p.origin_product_id, p.product_code, p.origin_product_code,
                      p.product_category_id, p.product_type_id, p.product_name,
                      p.brand, p.model, p.product_url, p.grade, p.date_of_creation,
                      p.last_price_change, p.description, p.site_price,
                      p.discount_percent, p.has_price, p.category_1, p.category_2,
                      p.category_3, p.category_4, p.depth_cm, p.width_cm,
                      p.height_cm, p.weight_kg, p.current_new_price,
                      p.accounting_cost, p.discount_set_date, p.condition_type,
                      p.repricing_count, p.history_available_from,
                      p.last_stock_change_date, p.available_quantity)
                     IS DISTINCT FROM
                     (s.origin_product_id, s.product_code, s.origin_product_code,
                      s.product_category_id, s.product_type_id, s.product_name,
                      s.brand, s.model, s.product_url, s.grade, s.date_of_creation,
                      s.last_price_change, s.description, s.site_price,
                      s.discount_percent, s.has_price, s.category_1, s.category_2,
                      s.category_3, s.category_4, s.depth_cm, s.width_cm,
                      s.height_cm, s.weight_kg, s.current_new_price,
                      s.accounting_cost, s.discount_set_date, s.condition_type,
                      s.repricing_count, s.history_available_from,
                      s.last_stock_change_date, s.available_quantity)
                   )) AS updated,
                   count(*) FILTER (WHERE p.product_id IS NOT NULL AND NOT p.is_active) AS restored
            FROM discount_product_stage s
            LEFT JOIN app.discount_product p USING (product_id)
        """)
        counts = cur.fetchone()
        cur.execute("""
            SELECT count(*) AS archived
            FROM app.discount_product p
            WHERE p.is_active
              AND NOT EXISTS (SELECT 1 FROM discount_product_stage s
                              WHERE s.product_id = p.product_id)
        """)
        archived = int(cur.fetchone()["archived"])
        added = int(counts["added"])
        updated = int(counts["updated"])
        restored = int(counts["restored"])
        unchanged = len(rows) - added - updated

        cur.execute("""
            INSERT INTO app.discount_product (
                product_id, origin_product_id, product_code, origin_product_code,
                product_category_id, product_type_id, product_name, brand, model,
                product_url, grade, date_of_creation, last_price_change, description,
                site_price, discount_percent, has_price, category_1, category_2,
                category_3, category_4, depth_cm, width_cm, height_cm, weight_kg,
                current_new_price, accounting_cost, discount_set_date, condition_type,
                repricing_count, history_available_from, last_stock_change_date,
                available_quantity, source_snapshot_at, last_seen_at, is_active,
                archived_at, updated_at)
            SELECT product_id, origin_product_id, product_code, origin_product_code,
                   product_category_id, product_type_id, product_name, brand, model,
                   product_url, grade, date_of_creation, last_price_change, description,
                   site_price, discount_percent, has_price, category_1, category_2,
                   category_3, category_4, depth_cm, width_cm, height_cm, weight_kg,
                   current_new_price, accounting_cost, discount_set_date, condition_type,
                   repricing_count, history_available_from, last_stock_change_date,
                   available_quantity, source_snapshot_at, now(), true, NULL, now()
            FROM discount_product_stage
            ON CONFLICT (product_id) DO UPDATE SET
                origin_product_id = EXCLUDED.origin_product_id,
                product_code = EXCLUDED.product_code,
                origin_product_code = EXCLUDED.origin_product_code,
                product_category_id = EXCLUDED.product_category_id,
                product_type_id = EXCLUDED.product_type_id,
                product_name = EXCLUDED.product_name,
                brand = EXCLUDED.brand,
                model = EXCLUDED.model,
                product_url = EXCLUDED.product_url,
                grade = EXCLUDED.grade,
                date_of_creation = EXCLUDED.date_of_creation,
                last_price_change = EXCLUDED.last_price_change,
                description = EXCLUDED.description,
                site_price = EXCLUDED.site_price,
                discount_percent = EXCLUDED.discount_percent,
                has_price = EXCLUDED.has_price,
                category_1 = EXCLUDED.category_1,
                category_2 = EXCLUDED.category_2,
                category_3 = EXCLUDED.category_3,
                category_4 = EXCLUDED.category_4,
                depth_cm = EXCLUDED.depth_cm,
                width_cm = EXCLUDED.width_cm,
                height_cm = EXCLUDED.height_cm,
                weight_kg = EXCLUDED.weight_kg,
                current_new_price = EXCLUDED.current_new_price,
                accounting_cost = EXCLUDED.accounting_cost,
                discount_set_date = EXCLUDED.discount_set_date,
                condition_type = EXCLUDED.condition_type,
                repricing_count = EXCLUDED.repricing_count,
                history_available_from = EXCLUDED.history_available_from,
                last_stock_change_date = EXCLUDED.last_stock_change_date,
                available_quantity = EXCLUDED.available_quantity,
                source_snapshot_at = EXCLUDED.source_snapshot_at,
                last_seen_at = now(), is_active = true, archived_at = NULL,
                updated_at = CASE WHEN
                    (discount_product.origin_product_id, discount_product.product_code,
                     discount_product.origin_product_code,
                     discount_product.product_category_id, discount_product.product_type_id,
                     discount_product.product_name, discount_product.brand,
                     discount_product.model, discount_product.product_url,
                     discount_product.grade, discount_product.date_of_creation,
                     discount_product.last_price_change, discount_product.description,
                     discount_product.site_price, discount_product.discount_percent,
                     discount_product.has_price, discount_product.category_1,
                     discount_product.category_2, discount_product.category_3,
                     discount_product.category_4, discount_product.depth_cm,
                     discount_product.width_cm, discount_product.height_cm,
                     discount_product.weight_kg, discount_product.current_new_price,
                     discount_product.accounting_cost, discount_product.discount_set_date,
                     discount_product.condition_type, discount_product.repricing_count,
                     discount_product.history_available_from,
                     discount_product.last_stock_change_date,
                     discount_product.available_quantity, discount_product.is_active)
                    IS DISTINCT FROM
                    (EXCLUDED.origin_product_id, EXCLUDED.product_code,
                     EXCLUDED.origin_product_code, EXCLUDED.product_category_id,
                     EXCLUDED.product_type_id, EXCLUDED.product_name, EXCLUDED.brand,
                     EXCLUDED.model, EXCLUDED.product_url, EXCLUDED.grade,
                     EXCLUDED.date_of_creation, EXCLUDED.last_price_change,
                     EXCLUDED.description, EXCLUDED.site_price,
                     EXCLUDED.discount_percent, EXCLUDED.has_price,
                     EXCLUDED.category_1, EXCLUDED.category_2, EXCLUDED.category_3,
                     EXCLUDED.category_4, EXCLUDED.depth_cm, EXCLUDED.width_cm,
                     EXCLUDED.height_cm, EXCLUDED.weight_kg,
                     EXCLUDED.current_new_price, EXCLUDED.accounting_cost,
                     EXCLUDED.discount_set_date, EXCLUDED.condition_type,
                     EXCLUDED.repricing_count, EXCLUDED.history_available_from,
                     EXCLUDED.last_stock_change_date, EXCLUDED.available_quantity, true)
                    THEN now() ELSE discount_product.updated_at END
        """)
        cur.execute("""
            UPDATE app.discount_product p
               SET is_active = false,
                   archived_at = coalesce(p.archived_at, now()),
                   updated_at = now()
             WHERE p.is_active
               AND NOT EXISTS (SELECT 1 FROM discount_product_stage s
                               WHERE s.product_id = p.product_id)
        """)
        cur.execute("""
            INSERT INTO app.discount_product_stock
                (product_id, warehouse, balance, source_snapshot_at,
                 is_active, archived_at, updated_at)
            SELECT product_id, warehouse, balance, source_snapshot_at,
                   true, NULL, now()
            FROM discount_product_stock_stage
            ON CONFLICT (product_id, warehouse) DO UPDATE SET
                balance = EXCLUDED.balance,
                source_snapshot_at = EXCLUDED.source_snapshot_at,
                is_active = true,
                archived_at = NULL,
                updated_at = CASE WHEN
                    (discount_product_stock.balance,
                     discount_product_stock.is_active)
                    IS DISTINCT FROM (EXCLUDED.balance, true)
                    THEN now() ELSE discount_product_stock.updated_at END
        """)
        cur.execute("""
            UPDATE app.discount_product_stock s
               SET is_active = false,
                   archived_at = coalesce(s.archived_at, now()),
                   updated_at = now()
             WHERE s.is_active
               AND NOT EXISTS (
                   SELECT 1 FROM discount_product_stock_stage x
                   WHERE x.product_id = s.product_id
                     AND x.warehouse = s.warehouse)
        """)
        cur.execute("""
            INSERT INTO app.discount_sync (
                source_snapshot_at, synced_by, rows_total, added_count,
                updated_count, unchanged_count, restored_count, archived_count,
                assortment_enriched)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, true)
            ON CONFLICT (source_snapshot_at) DO UPDATE SET
                synced_at = now(),
                synced_by = EXCLUDED.synced_by,
                rows_total = EXCLUDED.rows_total,
                added_count = EXCLUDED.added_count,
                updated_count = EXCLUDED.updated_count,
                unchanged_count = EXCLUDED.unchanged_count,
                restored_count = EXCLUDED.restored_count,
                archived_count = EXCLUDED.archived_count,
                assortment_enriched = true
        """, (snapshot_at, user_id, len(rows), added, updated, unchanged,
              restored, archived))
    conn.commit()
    return {
        "unchanged": False, "snapshot_at": snapshot_at,
        "rows_total": len(rows), "added": added, "updated": updated,
        "unchanged_count": unchanged, "restored": restored,
        "archived": archived,
    }


def synchronize(conn: psycopg.Connection, user_id: int | None) -> dict[str, Any]:
    return sync_products(conn, fetch_current_products(), user_id)


def summary(conn: psycopg.Connection) -> dict[str, Any]:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT coalesce(sum(available_quantity) FILTER (WHERE is_active), 0) AS active,
                   count(*) FILTER (WHERE is_active) AS active_records,
                   count(*) FILTER (WHERE NOT is_active) AS archived,
                   count(*) FILTER (WHERE is_active AND has_price) AS with_price,
                   count(*) FILTER (WHERE is_active AND NOT has_price) AS without_price,
                   coalesce(sum(site_price * available_quantity)
                            FILTER (WHERE is_active), 0) AS site_value,
                   max(source_snapshot_at) FILTER (WHERE is_active) AS source_snapshot_at
            FROM app.discount_product
        """)
        result = dict(cur.fetchone())
        cur.execute("""
            SELECT s.*, u.full_name AS synced_by_name
            FROM app.discount_sync s
            LEFT JOIN app.app_user u ON u.id = s.synced_by
            ORDER BY s.synced_at DESC LIMIT 1
        """)
        result["last_sync"] = cur.fetchone()
    return result


def filters(conn: psycopg.Connection) -> dict[str, list[str]]:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT DISTINCT grade FROM app.discount_product
            WHERE grade IS NOT NULL AND grade <> '' ORDER BY grade
        """)
        grades = [row["grade"] for row in cur.fetchall()]
        cur.execute("""
            SELECT DISTINCT category_2 FROM app.discount_product
            WHERE category_2 IS NOT NULL AND category_2 <> '' ORDER BY category_2
        """)
        categories = [row["category_2"] for row in cur.fetchall()]
        cur.execute("""
            SELECT DISTINCT brand FROM app.discount_product
            WHERE brand IS NOT NULL AND brand <> '' ORDER BY brand
        """)
        brands = [row["brand"] for row in cur.fetchall()]
    return {"grades": grades, "discount_categories": categories,
            "discount_brands": brands}


def history(conn: psycopg.Connection, limit: int = 10) -> list[dict[str, Any]]:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT s.*, u.full_name AS synced_by_name
            FROM app.discount_sync s
            LEFT JOIN app.app_user u ON u.id = s.synced_by
            ORDER BY s.synced_at DESC LIMIT %s
        """, (limit,))
        return list(cur.fetchall())


def _search_parts(query: str, status: str, grade: str, category: str,
                  manufacturer: str, has_price: str,
                  kufar_status: str = "", kufar_product_ids: list[str] | None = None
                  ) -> tuple[str, list[Any]]:
    clauses: list[str] = []
    params: list[Any] = []
    if status == "active":
        clauses.append("p.is_active")
    elif status == "archive":
        clauses.append("NOT p.is_active")
    if query.strip():
        needle = "%" + query.strip() + "%"
        clauses.append("""(
            p.product_id ILIKE %s OR p.product_code ILIKE %s OR
            p.origin_product_code ILIKE %s OR p.product_name ILIKE %s OR
            p.brand ILIKE %s OR p.description ILIKE %s OR
            p.category_2 ILIKE %s OR p.category_4 ILIKE %s)""")
        params.extend([needle] * 8)
    if grade:
        clauses.append("p.grade = %s")
        params.append(grade)
    if category:
        clauses.append("p.category_2 = %s")
        params.append(category)
    if manufacturer:
        clauses.append("p.brand = %s")
        params.append(manufacturer)
    if has_price == "yes":
        clauses.append("p.has_price")
    elif has_price == "no":
        clauses.append("NOT p.has_price")
    kufar_product_ids = kufar_product_ids or []
    if kufar_status == "yes":
        if kufar_product_ids:
            clauses.append("p.product_id = ANY(%s)")
            params.append(kufar_product_ids)
        else:
            clauses.append("false")
    elif kufar_status == "no" and kufar_product_ids:
        clauses.append("NOT (p.product_id = ANY(%s))")
        params.append(kufar_product_ids)
    where = "WHERE " + " AND ".join(clauses) if clauses else ""
    return where, params


def _sort_parts(sort: str, direction: str) -> tuple[str, str]:
    sort_columns = {
        "name": "p.product_name", "created": "p.date_of_creation",
        "age": "p.date_of_creation", "price": "p.site_price",
        "discount": "p.discount_percent", "updated": "p.updated_at",
    }
    order = sort_columns.get(sort, sort_columns["name"])
    sql_direction = "DESC" if direction == "desc" else "ASC"
    return order, sql_direction


def search(conn: psycopg.Connection, query: str = "", status: str = "active",
           grade: str = "", category: str = "", manufacturer: str = "",
           has_price: str = "", sort: str = "name", direction: str = "asc",
           page: int = 1, per_page: int = PER_PAGE,
           kufar_status: str = "", kufar_product_ids: list[str] | None = None
           ) -> dict[str, Any]:
    where, params = _search_parts(
        query, status, grade, category, manufacturer, has_price,
        kufar_status, kufar_product_ids)
    order, sql_direction = _sort_parts(sort, direction)
    with conn.cursor() as cur:
        cur.execute(f"SELECT count(*) AS total FROM app.discount_product p {where}", params)
        total = int(cur.fetchone()["total"])
        pages = max(1, (total + per_page - 1) // per_page)
        page = min(max(1, page), pages)
        offset = (page - 1) * per_page
        cur.execute(f"""
            SELECT p.*,
                   CASE WHEN p.date_of_creation IS NULL THEN NULL
                        ELSE p.source_snapshot_at::date - p.date_of_creation END AS age_days
            FROM app.discount_product p
            {where}
            ORDER BY {order} {sql_direction} NULLS LAST, p.product_id ASC
            LIMIT %s OFFSET %s
        """, [*params, per_page, offset])
        rows = list(cur.fetchall())
    return {"rows": rows, "total": total, "page": page,
            "pages": pages, "per_page": per_page}


def export_rows(conn: psycopg.Connection, query: str = "",
                status: str = "active", grade: str = "", category: str = "",
                manufacturer: str = "", has_price: str = "",
                sort: str = "name", direction: str = "asc",
                kufar_status: str = "", kufar_product_ids: list[str] | None = None
                ) -> list[dict[str, Any]]:
    """Все строки текущей выборки без ограничения экранной пагинацией."""
    where, params = _search_parts(
        query, status, grade, category, manufacturer, has_price,
        kufar_status, kufar_product_ids)
    order, sql_direction = _sort_parts(sort, direction)
    with conn.cursor() as cur:
        cur.execute(f"""
            SELECT p.*,
                   CASE WHEN p.date_of_creation IS NULL THEN NULL
                        ELSE p.source_snapshot_at::date - p.date_of_creation END AS age_days
            FROM app.discount_product p
            {where}
            ORDER BY {order} {sql_direction} NULLS LAST, p.product_id ASC
        """, params)
        return list(cur.fetchall())


def assortment_rows(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Активные карточки, по одной строке на product_id, для полного отчёта."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT p.*,
                   coalesce(string_agg(s.warehouse, '; ' ORDER BY s.warehouse)
                            FILTER (WHERE s.is_active), '') AS warehouses,
                   count(s.warehouse) FILTER (WHERE s.is_active) AS warehouse_count
            FROM app.discount_product p
            LEFT JOIN app.discount_product_stock s
              ON s.product_id = p.product_id AND s.is_active
            WHERE p.is_active
            GROUP BY p.product_id
            ORDER BY p.product_id
        """)
        return list(cur.fetchall())


def assortment_stock_rows(conn: psycopg.Connection) -> list[dict[str, Any]]:
    """Детальный активный остаток по складам для вкладки 08_Склады."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT s.product_id, s.warehouse, s.balance, s.source_snapshot_at,
                   p.product_name, p.category_1, p.category_2, p.category_3,
                   p.category_4, p.site_price, p.accounting_cost
            FROM app.discount_product_stock s
            JOIN app.discount_product p ON p.product_id = s.product_id
            WHERE s.is_active AND p.is_active
            UNION ALL
            SELECT p.product_id, 'Склад не указан' AS warehouse,
                   p.available_quantity AS balance, p.source_snapshot_at,
                   p.product_name, p.category_1, p.category_2, p.category_3,
                   p.category_4, p.site_price, p.accounting_cost
            FROM app.discount_product p
            WHERE p.is_active
              AND NOT EXISTS (
                  SELECT 1 FROM app.discount_product_stock s
                  WHERE s.product_id = p.product_id AND s.is_active)
            ORDER BY warehouse, product_id
        """)
        return list(cur.fetchall())


_EXPORT_HEADERS = (
    "Статус", "Номенклатура", "Артикул уценки", "Артикул основного товара",
    "Код уценки", "Код основного товара", "Производитель", "Категория товара",
    "Подкатегория", "Грейд", "Дата создания", "Возраст, дней",
    "Цена на сайте, руб.", "Скидка, %", "Есть цена на сайте",
    "Описание состояния", "Дата снимка", "Дата архивации",
)


def build_export_xlsx(rows: list[dict[str, Any]], snapshot_at: datetime | None,
                      filter_description: str) -> bytes:
    """Сформировать один компактный лист Excel из уже отфильтрованных строк."""
    workbook = Workbook(write_only=True)
    sheet = workbook.create_sheet("Уценка")
    sheet.sheet_view.showGridLines = False
    sheet.freeze_panes = "A7"

    title = WriteOnlyCell(sheet, "Уценка")
    title.font = Font(name="Arial", size=14, bold=True, color="1D2025")
    sheet.append([title])
    source = WriteOnlyCell(
        sheet,
        "Источник: локальная PostgreSQL-база приложения OOUCR, срез "
        + (snapshot_at.strftime("%d.%m.%Y %H:%M") if snapshot_at else "не указан"))
    source.font = Font(name="Arial", size=10, italic=True, color="666A70")
    sheet.append([source])
    scope = WriteOnlyCell(sheet, "Выборка: " + (filter_description or "все карточки"))
    scope.font = Font(name="Arial", size=10, italic=True, color="666A70")
    sheet.append([scope])
    count_cell = WriteOnlyCell(sheet, f"Строк в выгрузке: {len(rows)}")
    count_cell.font = Font(name="Arial", size=10, italic=True, color="666A70")
    sheet.append([count_cell])
    sheet.append([])

    header_fill = PatternFill("solid", fgColor="7B1F71")
    header_border = Border(bottom=Side(style="thin", color="FFFFFF"))
    header_cells = []
    for value in _EXPORT_HEADERS:
        cell = WriteOnlyCell(sheet, value)
        cell.font = Font(name="Arial", size=10, bold=True, color="FFFFFF")
        cell.fill = header_fill
        cell.border = header_border
        cell.alignment = Alignment(horizontal="center", vertical="center")
        header_cells.append(cell)
    sheet.append(header_cells)

    wrap = Alignment(vertical="top", wrap_text=True)
    for row in rows:
        values = [
            "В остатке" if row["is_active"] else "Архив",
            row["product_name"], row["product_id"], row["origin_product_id"],
            row["product_code"], row["origin_product_code"], row["brand"],
            row["category_2"], row["category_4"], row["grade"],
            row["date_of_creation"], row["age_days"], row["site_price"],
            row["discount_percent"], "Да" if row["has_price"] else "Нет",
            row["description"], row["source_snapshot_at"].date(),
            row["archived_at"].date() if row["archived_at"] else None,
        ]
        # Для скорости большой выгрузки создаём стилизованные объекты только
        # там, где Excel действительно нужен формат или перенос текста.
        for index, number_format in ((10, "dd.mm.yyyy"), (12, "#,##0.00"),
                                     (13, "0.0"), (16, "dd.mm.yyyy"),
                                     (17, "dd.mm.yyyy")):
            cell = WriteOnlyCell(sheet, values[index])
            cell.number_format = number_format
            values[index] = cell
        description = WriteOnlyCell(sheet, values[15])
        description.alignment = wrap
        values[15] = description
        sheet.append(values)

    widths = {
        "A": 12, "B": 44, "C": 25, "D": 22, "E": 16, "F": 18,
        "G": 20, "H": 28, "I": 25, "J": 10, "K": 14, "L": 13,
        "M": 18, "N": 12, "O": 17, "P": 60, "Q": 14, "R": 16,
    }
    for column, width in widths.items():
        sheet.column_dimensions[column].width = width
    last_row = max(6, len(rows) + 6)
    sheet.auto_filter.ref = f"A6:R{last_row}"
    sheet.row_dimensions[6].height = 30
    output = BytesIO()
    workbook.save(output)
    return output.getvalue()
