"""The migration runner (app/db_migrate.py), against migration files of its
own in a throwaway schema — the database's real schema_migrations is never
touched."""

import uuid

import pytest

from app import db_migrate


def test_split_statements_ignores_semicolons_in_quotes_and_comments():
    sql = """
    -- a comment; with a semicolon
    CREATE TABLE t (v text DEFAULT 'a;b''c');
    /* block; comment */
    DO $$ BEGIN PERFORM 1; END $$;
    CREATE FUNCTION f() RETURNS int LANGUAGE sql AS $fn$ SELECT 1; $fn$;
    SELECT 2
    """
    assert db_migrate.split_statements(sql) == [
        "CREATE TABLE t (v text DEFAULT 'a;b''c')",
        "DO $$ BEGIN PERFORM 1; END $$",
        "CREATE FUNCTION f() RETURNS int LANGUAGE sql AS $fn$ SELECT 1; $fn$",
        "SELECT 2",
    ]


@pytest.fixture
async def scratch(tmp_path, monkeypatch):
    """A connection whose search_path is a fresh schema, and an empty migrations folder."""
    monkeypatch.setattr(db_migrate, "MIGRATIONS_DIR", tmp_path)
    schema = f"mig_test_{uuid.uuid4().hex[:8]}"
    conn = await db_migrate._connect()
    await conn.execute(f"CREATE SCHEMA {schema}")
    await conn.execute(f"SET search_path TO {schema}")
    try:
        yield conn, tmp_path
    finally:
        await conn.execute(f"DROP SCHEMA {schema} CASCADE")
        await conn.close()


async def test_up_applies_pending_files_in_order_once(scratch):
    conn, folder = scratch
    (folder / "2026-01-02-second.sql").write_text("BEGIN;\nINSERT INTO things VALUES (2);\nCOMMIT;\n", encoding="utf-8")
    (folder / "2026-01-01-first.sql").write_text("BEGIN;\nCREATE TABLE IF NOT EXISTS things (n int);\nCOMMIT;\n", encoding="utf-8")
    # No BEGIN and an index built CONCURRENTLY: run statement by statement.
    (folder / "2026-01-03-index.sql").write_text("CREATE INDEX CONCURRENTLY IF NOT EXISTS things_n ON things (n);\nANALYZE things;\n", encoding="utf-8")

    assert "aren't tracked" in await db_migrate.startup_warning(conn)
    todo = await db_migrate.pending(conn)
    assert [m.name for m in todo] == ["2026-01-01-first.sql", "2026-01-02-second.sql", "2026-01-03-index.sql"]
    assert todo[2].statement_by_statement and not todo[0].statement_by_statement
    assert "pending: 2026-01-01-first.sql" in await db_migrate.startup_warning(conn)

    for m in todo:
        await db_migrate.apply(conn, m)
    assert await db_migrate.pending(conn) == []
    assert await db_migrate.startup_warning(conn) is None
    assert await conn.fetchval("SELECT count(*) FROM things") == 1
    assert await conn.fetchval("SELECT to_regclass('things_n')") is not None
    assert {r["how"] for r in await conn.fetch("SELECT how FROM schema_migrations")} == {"applied"}

    # A new file is the only one pending; an edited one shows as changed.
    (folder / "2026-01-04-more.sql").write_text("BEGIN;\nINSERT INTO things VALUES (4);\nCOMMIT;\n", encoding="utf-8")
    (folder / "2026-01-02-second.sql").write_text("BEGIN;\nINSERT INTO things VALUES (22);\nCOMMIT;\n", encoding="utf-8")
    assert [m.name for m in await db_migrate.pending(conn)] == ["2026-01-04-more.sql"]
    done = await db_migrate.applied(conn)
    changed = [m.name for m in db_migrate.migrations() if done.get(m.name) not in (None, m.checksum)]
    assert changed == ["2026-01-02-second.sql"]


async def test_a_failing_file_is_not_recorded(scratch):
    conn, folder = scratch
    (folder / "2026-01-01-broken.sql").write_text("BEGIN;\nCREATE TABLE ok (n int);\nSELECT * FROM missing_table;\nCOMMIT;\n", encoding="utf-8")
    [m] = await db_migrate.pending(conn)
    with pytest.raises(Exception):
        await db_migrate.apply(conn, m)
    await conn.execute("ROLLBACK")
    assert await conn.fetchval("SELECT to_regclass('ok')") is None
    assert [p.name for p in await db_migrate.pending(conn)] == ["2026-01-01-broken.sql"]


async def test_baseline_records_files_without_running_them(scratch):
    conn, folder = scratch
    (folder / "2026-01-01-would-fail.sql").write_text("SELECT * FROM missing_table;\n", encoding="utf-8")
    assert await db_migrate.baseline(conn) == ["2026-01-01-would-fail.sql"]
    assert await db_migrate.baseline(conn) == []  # already recorded
    assert await db_migrate.pending(conn) == []
    assert await conn.fetchval("SELECT how FROM schema_migrations") == "baseline"


async def test_deploy_builds_an_empty_database_then_brings_it_forward(scratch, monkeypatch):
    conn, folder = scratch
    schema = folder.parent / f"{folder.name}-schema.sql"
    schema.write_text("CREATE TABLE tenants (id int);\nCREATE TABLE things (n int);\n", encoding="utf-8")
    monkeypatch.setattr(db_migrate, "SCHEMA_SQL", schema)
    (folder / "2026-01-01-old.sql").write_text("SELECT * FROM missing_table;\n", encoding="utf-8")  # already in schema.sql

    assert (await db_migrate.deploy(conn))[0].startswith("Built the database from schema.sql (1 migration")
    assert await conn.fetchval("SELECT to_regclass('things')") is not None

    (folder / "2026-01-02-new.sql").write_text("BEGIN;\nINSERT INTO things VALUES (1);\nCOMMIT;\n", encoding="utf-8")
    assert await db_migrate.deploy(conn) == ["Applied 2026-01-02-new.sql"]
    assert await db_migrate.deploy(conn) == ["Database is up to date."]
    assert await conn.fetchval("SELECT count(*) FROM things") == 1


async def test_index_files_from_before_partitioning_are_passed_over():
    """The record tables are partitioned, so these couldn't run: CI re-runs every file."""
    conn = await db_migrate._connect()
    try:
        names = {m.name: m for m in db_migrate.migrations()}
        for name in db_migrate._BUILT_BY_PARTITIONING:
            assert await db_migrate._superseded(conn, names[name]), name
        assert not await db_migrate._superseded(conn, names["2026-11-08-pg-trgm.sql"])
    finally:
        await conn.close()