#!/usr/bin/env python3
"""Refresh the autonomous 1C warehouse-stock snapshot on S3.

The tokenized feed URL is accepted only through ``ONEC_STOCK_URL`` from the
root-owned runtime secret store.  The job reads local kernel SKUs, requests
the selected distributor warehouses in bounded batches, validates the full
response, and replaces only the local ``gwptd_intake`` snapshot.
"""
from __future__ import annotations

import json
import logging
import os
import sys
import urllib.request
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
sys.path.insert(0, str(ROOT))

from db.connection import get_cursor  # noqa: E402


logger = logging.getLogger("v3-reference.onec_warehouse_stock")
STOCK_URL = os.environ.get("ONEC_STOCK_URL", "").strip()
WAREHOUSES = tuple(
    value.strip()
    for value in os.environ.get(
        "ONEC_STOCK_WAREHOUSES",
        "b34a81d5-be87-11e0-abf5-90e6ba1e09a4,"
        "c26263a2-52ef-11e1-af94-90e6ba1e09a4,"
        "d3e60543-e78b-11ea-8df0-002590d5fc53",
    ).split(",")
    if value.strip()
)
BATCH_SKUS = int(os.environ.get("ONEC_STOCK_BATCH", "2000"))
MIN_ROWS = int(os.environ.get("ONEC_STOCK_MIN_ROWS", "50"))
TIMEOUT = int(os.environ.get("ONEC_STOCK_TIMEOUT", "120"))

DDL = """
    CREATE TABLE IF NOT EXISTS gwptd_intake.onec_warehouse_stock (
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      article_upper VARCHAR(255) NOT NULL,
      warehouse_guid CHAR(36) NOT NULL,
      warehouse_name VARCHAR(255) NULL,
      quantity INT NOT NULL DEFAULT 0,
      collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY uq_art_wh (article_upper, warehouse_guid),
      KEY idx_article (article_upper),
      KEY idx_warehouse (warehouse_guid)
    ) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

INSERT_SQL = """
    INSERT INTO gwptd_intake.onec_warehouse_stock
      (article_upper, warehouse_guid, warehouse_name, quantity)
    VALUES (%s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
      warehouse_name = VALUES(warehouse_name),
      quantity = VALUES(quantity),
      collected_at = CURRENT_TIMESTAMP
"""


def _skus(cur) -> list[str]:
    cur.execute(
        "SELECT DISTINCT artikul FROM gwptd_kernel.dim_product "
        "WHERE artikul IS NOT NULL AND TRIM(artikul) <> ''"
    )
    return [str(row["artikul"]).strip() for row in cur.fetchall()]


def _warehouse_names(cur) -> dict[str, str]:
    placeholders = ",".join(["%s"] * len(WAREHOUSES))
    cur.execute(
        "SELECT uuid_1c, name FROM gwptd_intake.onec_warehouse_ref "
        f"WHERE uuid_1c IN ({placeholders})",
        WAREHOUSES,
    )
    return {str(row["uuid_1c"]): str(row["name"]) for row in cur.fetchall()}


def _post_stock(skus: list[str]) -> list[tuple[str, str, int]]:
    body = json.dumps(
        {"skus": skus, "warehouse_GUIDs": list(WAREHOUSES)},
        ensure_ascii=False,
    ).encode("utf-8")
    request = urllib.request.Request(STOCK_URL, data=body, method="POST")
    request.add_header("Content-Type", "application/json")
    with urllib.request.urlopen(request, timeout=TIMEOUT) as response:
        text = response.read().decode("utf-8", "replace")

    rows: list[tuple[str, str, int]] = []
    for line in text.splitlines():
        line = line.strip()
        if not line or line.startswith("Артикул;"):
            continue
        parts = line.split(";")
        if len(parts) != 3:
            continue
        article, warehouse, quantity = parts
        try:
            parsed_quantity = int(quantity)
        except ValueError:
            continue
        warehouse = warehouse.strip()
        if warehouse not in WAREHOUSES:
            continue
        rows.append((article.strip().upper(), warehouse, parsed_quantity))
    return rows


def collect_snapshot(skus: list[str]) -> list[tuple[str, str, int]]:
    if BATCH_SKUS < 1 or BATCH_SKUS > 5000:
        raise ValueError("ONEC_STOCK_BATCH must be between 1 and 5000")
    rows: list[tuple[str, str, int]] = []
    for offset in range(0, len(skus), BATCH_SKUS):
        batch = skus[offset:offset + BATCH_SKUS]
        fetched = _post_stock(batch)
        rows.extend(fetched)
        logger.info(
            "batch %d: requested=%d received=%d",
            offset // BATCH_SKUS + 1,
            len(batch),
            len(fetched),
        )
    return rows


def main() -> int:
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s %(levelname)s %(name)s: %(message)s",
    )
    if not STOCK_URL:
        logger.error("ONEC_STOCK_URL is required")
        return 2
    if not WAREHOUSES:
        logger.error("ONEC_STOCK_WAREHOUSES is empty")
        return 2

    with get_cursor() as cur:
        cur.execute(DDL)
        skus = _skus(cur)
        names = _warehouse_names(cur)
    if not skus:
        logger.error("local dim_product has no SKUs; keeping previous snapshot")
        return 1

    try:
        rows = collect_snapshot(skus)
    except Exception as exc:  # noqa: BLE001 - CLI boundary, old data is preserved
        # urllib exceptions can embed the tokenized feed URL. Keep only the
        # exception class in durable cron logs; the last-good snapshot remains.
        logger.error(
            "1C stock fetch failed (%s); keeping previous snapshot",
            type(exc).__name__,
        )
        return 1
    if len(rows) < MIN_ROWS:
        logger.error(
            "received only %d rows (<%d); keeping previous snapshot",
            len(rows),
            MIN_ROWS,
        )
        return 1

    # De-duplicate defensively before the transactional replacement.
    snapshot = {
        (article, warehouse): (article, warehouse, names.get(warehouse), quantity)
        for article, warehouse, quantity in rows
    }
    with get_cursor() as cur:
        cur.execute("DELETE FROM gwptd_intake.onec_warehouse_stock")
        values = list(snapshot.values())
        for offset in range(0, len(values), 1000):
            cur.executemany(INSERT_SQL, values[offset:offset + 1000])

    logger.info(
        "onec_warehouse_stock refreshed: rows=%d articles=%d warehouses=%d",
        len(snapshot),
        len({key[0] for key in snapshot}),
        len({key[1] for key in snapshot}),
    )
    return 0


if __name__ == "__main__":
    raise SystemExit(main())