"""Shared product-photo catalogue for defect stock, discounts and RMB."""
from __future__ import annotations

import json
import re
import shutil
import sqlite3
import time
from contextlib import contextmanager
from pathlib import Path

from . import rmbimages


def code(value) -> str:
    """Only a website code or the leading code of a BK/discount key, never a 1C code."""
    value = str(value or "").strip()
    if re.fullmatch(r"[1-9]\d{0,11}", value):
        return value
    match = re.match(r"^([1-9]\d{0,11})\s*[-–]\s*[^\d\s]", value)
    return match.group(1) if match else ""


def defect_code(row: dict) -> str:
    return code(row.get("article"))


def discount_code(row: dict) -> str:
    return code(row.get("origin_product_id")) or code(row.get("product_id"))


@contextmanager
def connect(root: Path):
    root.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(root / "catalogue.sqlite3", timeout=30)
    conn.row_factory = sqlite3.Row
    conn.create_function("casefold", 1, lambda value: str(value or "").casefold(), deterministic=True)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("""CREATE TABLE IF NOT EXISTS image (
        code TEXT PRIMARY KEY, title TEXT NOT NULL DEFAULT '', sources TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'pending', source_url TEXT, mime TEXT, checked_at REAL)""")
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


def migrate_legacy(root: Path, legacy: Path):
    photos = root / "photos"
    photos.mkdir(parents=True, exist_ok=True)
    if legacy.exists():
        for path in legacy.iterdir():
            if path.suffix in {".json", ".image"} and path.stem.isdigit():
                destination = photos / path.name
                if not destination.exists():
                    shutil.copy2(path, destination)


def resolve(root: Path, product_code: str, *, client=None):
    if not code(product_code) or code(product_code) != product_code:
        raise ValueError("Неверный код товара")
    photo = rmbimages.cached_photo(root / "photos", product_code, client=client)
    metadata = root / "photos" / (product_code + ".json")
    record = json.loads(metadata.read_text(encoding="utf-8")) if metadata.exists() else {}
    status = "found" if photo else record.get("status", "missing")
    with connect(root) as conn:
        conn.execute("""INSERT INTO image(code,status,source_url,mime,checked_at) VALUES(?,?,?,?,?)
            ON CONFLICT(code) DO UPDATE SET status=excluded.status,source_url=excluded.source_url,
                mime=excluded.mime,checked_at=excluded.checked_at""",
                     (product_code, status, record.get("source"), record.get("mime"), record.get("checked_at", time.time())))
    return photo


def inventory(conn) -> tuple[list[dict], int]:
    """Merge exact website codes. Unknown mappings remain placeholders in the product lists."""
    items = {}
    unknown = 0
    queries = [
        ("Брак", "SELECT article AS unit_key, name AS title FROM app.defect_product"),
        ("Уценка", "SELECT coalesce(origin_product_id::text,product_id) AS unit_key, product_name AS title FROM app.discount_product"),
        ("Обработчик", "SELECT external_unit_key AS unit_key, external_name AS title FROM app.defect_handler_event WHERE deleted_at IS NULL"),
        ("РМБ", "SELECT external_unit_key AS unit_key, '' AS title FROM app.defect_rmb_case"),
        ("Файлы менеджеров", "SELECT external_unit_key AS unit_key, external_name AS title FROM app.defect_rmb_manager_item WHERE is_source_present"),
    ]
    with conn.cursor() as cur:
        for source, query in queries:
            cur.execute(query)
            for row in cur.fetchall():
                product_code = code(row["unit_key"])
                if not product_code:
                    unknown += 1
                    continue
                item = items.setdefault(product_code, {"code": product_code, "title": row["title"] or "", "sources": set()})
                item["sources"].add(source)
    return [dict(item, sources=", ".join(sorted(item["sources"]))) for item in items.values()], unknown


def register(root: Path, items: list[dict]):
    with connect(root) as conn:
        conn.executemany("""INSERT INTO image(code,title,sources) VALUES(:code,:title,:sources)
            ON CONFLICT(code) DO UPDATE SET title=excluded.title,sources=excluded.sources""", items)


def catalogue(root: Path, query="", status="", page=1):
    where, params = " WHERE 1=1", []
    if query:
        where += " AND (code LIKE ? OR casefold(title) LIKE ?)"
        params += ["%" + query[:200].casefold() + "%"] * 2
    if status in {"pending", "found", "missing", "error"}:
        where += " AND status=?"
        params.append(status)
    with connect(root) as conn:
        conn.execute("BEGIN")
        counts = {row["status"]: row["n"] for row in conn.execute("SELECT status,count(*) n FROM image GROUP BY status")}
        total = conn.execute("SELECT count(*) FROM image" + where, params).fetchone()[0]
        pages = max(1, (total+59)//60)
        page = max(1, min(page, pages))
        rows = [dict(row) for row in conn.execute("SELECT * FROM image" + where + " ORDER BY code LIMIT 60 OFFSET ?", params + [(page-1)*60])]
    state_path = root / "sync.json"
    try:
        sync = json.loads(state_path.read_text(encoding="utf-8"))
    except (OSError, ValueError):
        sync = {}
    return dict(rows=rows, counts=counts, total=total, page=page, pages=pages, sync=sync,
                total_codes=sum(counts.values()), checked_codes=sum(counts.values())-counts.get("pending",0))
