r"""
Генерирует PG-схему точно по SQLite-структуре, переносит данные.
Запускай ОДИН раз: .venv\Scripts\python _scripts\_gen_pg_schema.py
"""
from __future__ import annotations
import sys, re, sqlite3, json
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))

SQLITE_DB = ROOT / "data" / "spamimpakter.db"
ENV_FILE  = ROOT / ".runtime.env"

# ── читаем DSN ────────────────────────────────────────────────────────────────
def _read_dsn() -> str:
    for line in ENV_FILE.read_text(encoding="utf-8").splitlines():
        if line.startswith("PG_DSN="):
            return line[7:].strip()
    raise RuntimeError(".runtime.env не содержит PG_DSN")

# ── SQLite type → PG type ─────────────────────────────────────────────────────
_TYPE_MAP = {
    "INTEGER": "BIGINT",
    "INT":     "BIGINT",
    "REAL":    "DOUBLE PRECISION",
    "NUMERIC": "NUMERIC",
    "TEXT":    "TEXT",
    "BLOB":    "BYTEA",
    "BOOLEAN": "BOOLEAN",
}

def _sl_type_to_pg(sltype: str) -> str:
    s = (sltype or "TEXT").strip().upper()
    for k, v in _TYPE_MAP.items():
        if s.startswith(k):
            return v
    return "TEXT"


def _sl_default_to_pg(dflt: str | None, pgtype: str) -> str | None:
    if dflt is None:
        return None
    d = dflt.strip().strip("'")
    # числа
    if re.match(r"^-?\d+(\.\d+)?$", d):
        return d
    # строки
    return f"'{d}'"


# ── получаем список таблиц из SQLite ─────────────────────────────────────────
def _get_tables(sl: sqlite3.Connection) -> list[str]:
    rows = sl.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
    ).fetchall()
    return [r[0] for r in rows]


# ── строим CREATE TABLE для PG ────────────────────────────────────────────────
def _create_table_sql(sl: sqlite3.Connection, table: str) -> str:
    cols = sl.execute(f"PRAGMA table_info({table})").fetchall()
    # cid, name, type, notnull, dflt_value, pk
    pk_cols = [c[1] for c in cols if c[5] > 0]
    parts: list[str] = []
    for cid, name, sltype, notnull, dflt, pk in cols:
        pgtype = _sl_type_to_pg(sltype)
        # AUTOINCREMENT → BIGINT GENERATED ALWAYS AS IDENTITY
        if pk and pgtype == "BIGINT" and len(pk_cols) == 1:
            parts.append(f'    "{name}" BIGINT GENERATED ALWAYS AS IDENTITY')
            continue
        col = f'    "{name}" {pgtype}'
        if notnull:
            col += " NOT NULL"
        pg_dflt = _sl_default_to_pg(dflt, pgtype)
        if pg_dflt is not None:
            col += f" DEFAULT {pg_dflt}"
        parts.append(col)

    if pk_cols:
        pks = ", ".join(f'"{c}"' for c in pk_cols)
        parts.append(f"    PRIMARY KEY ({pks})")

    # UNIQUE constraints из sqlite_master
    idx_rows = sl.execute(
        "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name=? AND sql IS NOT NULL",
        (table,)
    ).fetchall()
    for (idx_sql,) in idx_rows:
        if "UNIQUE" in (idx_sql or "").upper():
            # извлекаем колонки из CREATE UNIQUE INDEX ... ON tbl (col1, col2)
            m = re.search(r'\(\s*([^)]+)\s*\)', idx_sql)
            if m:
                ucols = ", ".join(f'"{c.strip().strip(chr(34))}"' for c in m.group(1).split(","))
                parts.append(f"    UNIQUE ({ucols})")

    return f'CREATE TABLE IF NOT EXISTS "{table}" (\n' + ",\n".join(parts) + "\n);"


# ── миграция данных одной таблицы ─────────────────────────────────────────────
def _migrate_table(sl: sqlite3.Connection, pg_cur, table: str) -> int:
    cols_info = sl.execute(f"PRAGMA table_info({table})").fetchall()
    # cid, name, type, notnull, dflt, pk
    pk_cols = {c[1] for c in cols_info if c[5] > 0}
    auto_id = any(c[5] > 0 and "INT" in c[2].upper() and len(pk_cols) == 1 for c in cols_info)
    col_names = [c[1] for c in cols_info]

    rows = sl.execute(f"SELECT * FROM {table}").fetchall()
    if not rows:
        return 0

    insert_cols = col_names
    quoted = ", ".join(f'"{c}"' for c in insert_cols)
    placeholders = ", ".join(["%s"] * len(insert_cols))
    # OVERRIDING SYSTEM VALUE позволяет вставить явный id в GENERATED ALWAYS AS IDENTITY
    overriding = " OVERRIDING SYSTEM VALUE" if auto_id else ""
    insert_sql = (
        f'INSERT INTO "{table}" ({quoted})'
        f'{overriding} VALUES ({placeholders}) ON CONFLICT DO NOTHING'
    )
    data = [tuple(r) for r in rows]

    pg_cur.execute(f"SAVEPOINT sp_{table}")
    try:
        pg_cur.executemany(insert_sql, data)
    except Exception as e:
        pg_cur.execute(f"ROLLBACK TO SAVEPOINT sp_{table}")
        print(f"  [warn] {table}: {e}")
        return 0
    pg_cur.execute(f"RELEASE SAVEPOINT sp_{table}")

    # Обновляем sequence чтобы следующий INSERT не конфликтовал
    if auto_id:
        pk_name_auto = next(c[1] for c in cols_info if c[5] > 0)
        pk_name_q = f'"{pk_name_auto}"'
        try:
            pg_cur.execute(f'SELECT MAX({pk_name_q}) FROM "{table}"')
            max_id = (pg_cur.fetchone() or [None])[0]
            if max_id:
                pg_cur.execute(
                    f'ALTER TABLE "{table}" ALTER COLUMN {pk_name_q}'
                    f' RESTART WITH {int(max_id) + 1}'
                )
        except Exception:
            pass

    return len(data)


# ── главная функция ───────────────────────────────────────────────────────────
def main() -> int:
    import psycopg2

    dsn = _read_dsn()
    pg = psycopg2.connect(dsn + "?connect_timeout=5")
    pg.autocommit = False
    cur = pg.cursor()

    sl = sqlite3.connect(SQLITE_DB)

    tables = _get_tables(sl)
    print(f"Таблиц в SQLite: {len(tables)}")

    # 1. Удаляем старые PG-таблицы (CASCADE чтобы убрать FKs)
    print("\n[1/3] Пересоздаём схему PG...")
    cur.execute("SET session_replication_role = 'replica'")  # отключаем FK checks
    for t in reversed(tables):  # удаляем в обратном порядке
        cur.execute(f'DROP TABLE IF EXISTS "{t}" CASCADE')
    pg.commit()

    # 2. Создаём таблицы по SQLite-структуре
    created = 0
    for t in tables:
        sql = _create_table_sql(sl, t)
        try:
            cur.execute(sql)
            created += 1
        except Exception as e:
            print(f"  [err] CREATE {t}: {e}")
            pg.rollback()
            cur = pg.cursor()
            cur.execute("SET session_replication_role = 'replica'")
    pg.commit()
    print(f"  Создано таблиц: {created}/{len(tables)}")

    # 3. Переносим данные
    print("\n[2/3] Переносим данные SQLite → PG...")
    total_rows = 0
    for t in tables:
        n = _migrate_table(sl, cur, t)
        if n:
            print(f"  {t}: {n} строк")
            total_rows += n
    pg.commit()
    print(f"  Итого строк: {total_rows}")

    # 4. Восстанавливаем FK checks
    cur.execute("SET session_replication_role = 'origin'")
    pg.commit()

    # 5. Пересоздаём индексы из SQLite
    print("\n[3/3] Создаём индексы...")
    idx_rows = sl.execute(
        "SELECT sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL"
    ).fetchall()
    for (idx_sql,) in idx_rows:
        # CREATE [UNIQUE] INDEX idx_name ON tbl (cols)
        m = re.match(r'CREATE\s+(UNIQUE\s+)?INDEX\s+(\w+)\s+ON\s+(\w+)\s*\(([^)]+)\)', idx_sql, re.IGNORECASE)
        if not m:
            continue
        unique = "UNIQUE " if m.group(1) else ""
        idx_name = m.group(2)
        tbl = m.group(3)
        cols = ", ".join(f'"{c.strip()}"' for c in m.group(4).split(","))
        pg_sql = f'CREATE {unique}INDEX IF NOT EXISTS "{idx_name}" ON "{tbl}" ({cols})'
        try:
            cur.execute(pg_sql)
        except Exception:
            pass
    pg.commit()

    sl.close()
    pg.close()
    print("\n✓ Миграция завершена. SQLite данные перенесены в PG.")
    return 0


if __name__ == "__main__":
    sys.exit(main())
