"""Seed a synthetic 100x-scale dataset for the M8 performance pass
(09-build-plan §M8; 04-architecture "Performance baseline").

Production carries ~4,593 time_entries across 8 months. This builds a
SEPARATE scratch database of ~460k entries (100x) by MULTIPLYING the
real migrated shapes: it lands the legacy fixture, runs the real
transforms to get a valid R1 graph (parties, engagements, terms,
projects, import batches, invoices, entries with verbatim stamps), then
fans the time_entries out with new ids and dates spread over a two-year
window while preserving every FK (project/worker/engagement/import), so
scope() joins and the date/tenant indexes are exercised exactly as in
production.

SAFETY (dev-DB only): this script REFUSES to touch the production books
database (`with`). It only ever DROP/CREATEs a dedicated scratch DB
(default `with_perf`). It never writes to the source it reads shapes
from — the shapes come from a freshly-migrated copy of the fixture.

Usage:
    python -m scripts.seed_100x                 # -> with_perf, ~460k rows
    python -m scripts.seed_100x --total 120000  # smaller (perf test size)
    python -m scripts.seed_100x --target-url postgresql+psycopg://with:pw@127.0.0.1:54317/with_perf
"""

from __future__ import annotations

import argparse
import logging
import math
import time
from pathlib import Path

from sqlalchemy import create_engine, text

from app.config import settings

log = logging.getLogger("with.seed100x")

ROOT = Path(__file__).resolve().parents[1]
FIXTURE_SQL = ROOT / "tests" / "migrate" / "fixtures" / "legacy_fixture.sql"

#: Databases this script will NEVER drop/write — the production books.
PROTECTED_DBS = frozenset({"with"})
DEFAULT_TARGET_DB = "with_perf"
DEFAULT_TOTAL = 459_300  # 100 × the ~4,593 production entries
#: Spread synthetic dates over ~2 years so a month filter is selective.
SPREAD_DAYS = 730
#: Insert copies in reps-batches to keep any single statement bounded.
CHUNK_REPS = 400


def _base_url() -> str:
    url = settings.with_db_url_host or settings.with_db_url
    if not url:
        raise SystemExit(
            "no sidecar URL — set WITH_DB_URL_HOST in .env (127.0.0.1:54317)"
        )
    return url


def _db_name(url: str) -> str:
    return url.rsplit("/", 1)[-1].split("?", 1)[0]


def _with_db(url: str, db: str) -> str:
    return url.rsplit("/", 1)[0] + f"/{db}"


def _guard(target_url: str) -> None:
    name = _db_name(target_url)
    if name in PROTECTED_DBS:
        raise SystemExit(
            f"REFUSING to seed {name!r}: that is the production books DB. "
            "Point --target-url at a scratch DB (e.g. with_perf)."
        )


def _create_target(base_url: str, target_url: str) -> None:
    """DROP + CREATE the scratch target on the same server, then migrate
    it to head with the real alembic chain."""
    target_db = _db_name(target_url)
    # Connect to the server via the (protected, never written) base DB
    # purely to run CREATE/DROP DATABASE.
    admin = create_engine(
        base_url, isolation_level="AUTOCOMMIT", connect_args={"connect_timeout": 3}
    )
    try:
        with admin.connect() as conn:
            conn.execute(text(f'DROP DATABASE IF EXISTS "{target_db}" WITH (FORCE)'))
            conn.execute(text(f'CREATE DATABASE "{target_db}"'))
    finally:
        admin.dispose()

    import os

    from alembic.config import Config

    from alembic import command

    cfg = Config(str(ROOT / "alembic.ini"))
    cfg.set_main_option("script_location", str(ROOT / "alembic"))
    previous = os.environ.get("WITH_DB_URL")
    os.environ["WITH_DB_URL"] = target_url
    try:
        command.upgrade(cfg, "head")
    finally:
        os.environ["WITH_DB_URL"] = previous if previous is not None else ""


def _load_base_graph(engine) -> int:
    """Land the fixture + run the transforms; return the base entry count."""
    with engine.begin() as conn:
        conn.exec_driver_sql(FIXTURE_SQL.read_text(encoding="utf-8"))
    from app.services.migrate import run_all

    report = run_all(engine=engine)
    if not report.ok:
        raise SystemExit("transforms failed on the perf base graph:\n" + report.render())
    with engine.connect() as conn:
        return int(conn.execute(text("select count(*) from time_entries")).scalar() or 0)


# The columns copied verbatim on each synthetic clone (everything except
# id [new], start_at/end_at [shifted], invoice_line_id [NULL — clones are
# unattached; never fake an invoice lock] and created_at [now()]).
_CLONE_COLUMNS = (
    "tenant_id, project_id, worker_party_id, via_engagement_id, source, "
    "source_timezone, import_id, source_user_name, source_user_email, "
    "raw_operator, raw_client, raw_project, description, billable, "
    "duration_seconds, source_rate, source_amount, cost_amount, "
    "cost_currency, billout_amount, billout_currency"
)

# Clone dates depend ONLY on the rep number (evenly across `spread` days,
# anchored at 2026-06-30), so the synthetic set is uniformly distributed
# regardless of how the small base graph clusters — a selective date
# range is then genuinely index-served, not an artifact of clustering.
# The base row's original time-of-day and duration are preserved.
_CLONE_SQL = text(
    f"""
    INSERT INTO time_entries (
        id, start_at, end_at, invoice_line_id, created_at, {_CLONE_COLUMNS}
    )
    SELECT
        gen_random_uuid(),
        new_start,
        CASE WHEN base.end_at IS NULL THEN NULL
             ELSE new_start + (base.end_at - base.start_at) END,
        NULL,
        now(),
        {_CLONE_COLUMNS}
    FROM base
    CROSS JOIN generate_series(1, :reps) AS g(n)
    CROSS JOIN LATERAL (
        SELECT (timestamp '2026-06-30 09:00'
                - ((g.n % :spread) * interval '1 day')
                + (base.start_at::time - time '00:00')) AS new_start
    ) d
    """
)


def _multiply_entries(engine, base_count: int, total: int) -> int:
    """Fan the base entries out to ~`total` rows. Clones preserve all FKs
    and stamps; only id/dates change and invoice_line_id is nulled."""
    reps_total = max(0, math.ceil(total / base_count) - 1)
    inserted = 0
    with engine.begin() as conn:
        # Snapshot the ORIGINAL base rows so re-running the chunked insert
        # never re-copies clones (the "real shapes" stay fixed).
        conn.execute(
            text(
                "CREATE TEMP TABLE base ON COMMIT DROP AS "
                "SELECT * FROM time_entries"
            )
        )
        remaining = reps_total
        while remaining > 0:
            reps = min(CHUNK_REPS, remaining)
            conn.execute(_CLONE_SQL, {"reps": reps, "spread": SPREAD_DAYS})
            inserted += reps * base_count
            remaining -= reps
        conn.execute(text("ANALYZE time_entries"))
    return inserted


def build_perf_database(
    target_url: str | None = None,
    total_entries: int = DEFAULT_TOTAL,
    *,
    base_url: str | None = None,
) -> dict:
    """Build (or rebuild) the scratch perf DB. Returns a small report."""
    base_url = base_url or _base_url()
    target_url = target_url or _with_db(base_url, DEFAULT_TARGET_DB)
    _guard(target_url)

    t0 = time.perf_counter()
    _create_target(base_url, target_url)
    engine = create_engine(target_url)
    try:
        base_count = _load_base_graph(engine)
        if base_count == 0:
            raise SystemExit("base graph produced 0 entries — cannot multiply")
        cloned = _multiply_entries(engine, base_count, total_entries)
        with engine.connect() as conn:
            final = int(conn.execute(text("select count(*) from time_entries")).scalar() or 0)
    finally:
        engine.dispose()

    return {
        "target": _db_name(target_url),
        "base_count": base_count,
        "cloned": cloned,
        "total": final,
        "seconds": round(time.perf_counter() - t0, 1),
    }


def main(argv: list[str] | None = None) -> int:
    logging.basicConfig(level=logging.INFO, format="%(message)s")
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--target-url", default=None, help="scratch DB URL (never 'with')")
    ap.add_argument("--total", type=int, default=DEFAULT_TOTAL, help="target entry count")
    args = ap.parse_args(argv)

    report = build_perf_database(args.target_url, args.total)
    log.info(
        "seeded %s: base=%d cloned=%d total=%d in %ss",
        report["target"],
        report["base_count"],
        report["cloned"],
        report["total"],
        report["seconds"],
    )
    return 0


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