"""Lightweight SQLite initialisers (no HTTP deps)."""
from __future__ import annotations

import sqlite3
from pathlib import Path

import config

PNP_SCHEMA = """
CREATE TABLE IF NOT EXISTS seeds (
    code              TEXT PRIMARY KEY,
    url               TEXT NOT NULL,
    slug              TEXT,
    sitemap_image_url TEXT,
    status            TEXT NOT NULL DEFAULT 'pending',
    attempts          INTEGER NOT NULL DEFAULT 0,
    last_error        TEXT,
    http_status       INTEGER,
    fetched_at        TEXT
);
CREATE INDEX IF NOT EXISTS idx_seeds_status ON seeds(status);

CREATE TABLE IF NOT EXISTS products (
    code               TEXT PRIMARY KEY,
    name               TEXT,
    brand              TEXT,
    barcode            TEXT,
    manufacturer       TEXT,
    price_value        REAL,
    price_currency     TEXT,
    price_formatted    TEXT,
    unit_of_measure    TEXT,
    quantity_type      TEXT,
    average_weight     REAL,
    in_stock_indicator TEXT,
    stock_status       TEXT,
    stock_level        INTEGER,
    available          INTEGER,
    purchasable        INTEGER,
    average_rating     REAL,
    number_of_reviews  INTEGER,
    summary            TEXT,
    description        TEXT,
    primary_image_url  TEXT,
    image_count        INTEGER,
    category_path      TEXT,
    category_codes     TEXT,
    url                TEXT,
    raw_json           TEXT NOT NULL,
    scraped_at         TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_path);
CREATE INDEX IF NOT EXISTS idx_products_barcode ON products(barcode);

CREATE TABLE IF NOT EXISTS meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);
"""


def init_pnp_db() -> Path:
    """Create pnp.db with schema if missing."""
    path = config.PNP_DB
    config.ensure_dirs()
    path.parent.mkdir(parents=True, exist_ok=True)
    created = not path.exists()
    conn = sqlite3.connect(path)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.executescript(PNP_SCHEMA)
    conn.close()
    if created:
        print(f"[pnp] created database at {path}")
    return path