"""
Load the unified catalogue + MVP catalogue from baskit.db (SQLite) into the
localhost MySQL `baskit` database used by the web app.

The read-only MCP can't write, so this uses pymysql directly against WAMP's MySQL.
Connection defaults to WAMP root@localhost (empty password); override via env:
  BASKIT_MYSQL_HOST / _PORT / _USER / _PASSWORD / _DB

Idempotent: drops and recreates the two tables, then bulk-inserts. Re-run after
any re-scrape + `python baskit_match.py build && python baskit_match.py mvp`.

  python load_mysql.py
"""
from __future__ import annotations

import sqlite3
from pathlib import Path

import pymysql

import config

BASKIT_DB = config.BASKIT_DB

CFG = config.MYSQL

CATALOGUE_DDL = """
CREATE TABLE catalogue (
    retailer      VARCHAR(20)  NOT NULL,
    product_key   VARCHAR(128) NOT NULL,
    barcode_raw   VARCHAR(64),
    barcode_norm  VARCHAR(32),
    is_instore_bc TINYINT      NOT NULL DEFAULT 0,
    name          VARCHAR(512),
    brand         VARCHAR(255),
    price         DECIMAL(10,2),
    was_price     DECIMAL(10,2),
    image_url     TEXT,
    url           TEXT,
    bucket        VARCHAR(255),
    scraped_at    VARCHAR(32),
    PRIMARY KEY (retailer, product_key),
    KEY idx_barcode_norm (barcode_norm),
    KEY idx_retailer (retailer)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

MVP_DDL = """
CREATE TABLE mvp_catalogue (
    barcode          VARCHAR(32) NOT NULL,
    bucket           VARCHAR(40),
    name             VARCHAR(512),
    brand            VARCHAR(255),
    n_retailers      TINYINT,
    pnp_price        DECIMAL(10,2),
    checkers_price   DECIMAL(10,2),
    woolworths_price DECIMAL(10,2),
    min_price        DECIMAL(10,2),
    max_price        DECIMAL(10,2),
    PRIMARY KEY (barcode),
    KEY idx_bucket (bucket)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""

CATALOGUE_SELECT = """
    SELECT retailer, product_key, barcode_raw, barcode_norm, is_instore_bc,
           name, brand, price, was_price, image_url, url, bucket, scraped_at
    FROM catalogue
"""
CATALOGUE_INSERT = (
    "INSERT INTO catalogue (retailer, product_key, barcode_raw, barcode_norm, "
    "is_instore_bc, name, brand, price, was_price, image_url, url, bucket, scraped_at) "
    "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
)

MVP_SELECT = """
    SELECT barcode, bucket, name, brand, n_retailers,
           pnp_price, checkers_price, woolworths_price, min_price, max_price
    FROM mvp_catalogue
"""
MVP_INSERT = (
    "INSERT INTO mvp_catalogue (barcode, bucket, name, brand, n_retailers, "
    "pnp_price, checkers_price, woolworths_price, min_price, max_price) "
    "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
)

BATCH = 1000


def load_table(scur, mcur, name, ddl, select, insert) -> int:
    mcur.execute(f"DROP TABLE IF EXISTS {name}")
    mcur.execute(ddl)
    rows = scur.execute(select).fetchall()
    total = 0
    for i in range(0, len(rows), BATCH):
        chunk = rows[i:i + BATCH]
        mcur.executemany(insert, chunk)
        total += len(chunk)
    return total


def main() -> None:
    if not BASKIT_DB.exists():
        raise SystemExit(f"{BASKIT_DB} not found — run `python baskit_match.py build` first.")
    sconn = sqlite3.connect(f"file:{BASKIT_DB}?mode=ro", uri=True)
    scur = sconn.cursor()

    print(f"[mysql] connecting to {CFG['user']}@{CFG['host']}:{CFG['port']}/{CFG['database']}")
    mconn = pymysql.connect(**CFG, autocommit=False)
    try:
        with mconn.cursor() as mcur:
            n_cat = load_table(scur, mcur, "catalogue", CATALOGUE_DDL,
                               CATALOGUE_SELECT, CATALOGUE_INSERT)
            print(f"[mysql] catalogue: {n_cat} rows loaded")
            # mvp_catalogue may not exist yet if `baskit_match.py mvp` wasn't run
            has_mvp = scur.execute(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='mvp_catalogue'"
            ).fetchone()
            if has_mvp:
                n_mvp = load_table(scur, mcur, "mvp_catalogue", MVP_DDL,
                                   MVP_SELECT, MVP_INSERT)
                print(f"[mysql] mvp_catalogue: {n_mvp} rows loaded")
            else:
                print("[mysql] mvp_catalogue skipped (run `baskit_match.py mvp` first)")
        mconn.commit()
        print("[mysql] committed.")
    except Exception:
        mconn.rollback()
        raise
    finally:
        mconn.close()
        sconn.close()


if __name__ == "__main__":
    main()