"""
The uq_groups_owner_name migration refuses to run over data that already
violates it, naming the offending pairs — it must never silently rename a
group.
"""

import sqlite3
from pathlib import Path

import pytest
from alembic import command
from alembic.config import Config

HUB = Path(__file__).resolve().parents[1]
BEFORE = "b1c2d3e4f5a6"
AFTER = "c3d4e5f6a7b8"

_USER = ("INSERT INTO users (id, username, email, pw_hash, pw_salt, hub_id) "
         "VALUES ('u1', 'alice', 'a@x', x'00', x'00', 'h')")
_GROUP = ("INSERT INTO groups (id, name, admin_id, visibility, join_policy, status) "
          "VALUES (?, ?, 'u1', 'private', 'invite', 'active')")


@pytest.fixture
def at_before(tmp_path, monkeypatch):
    """A hub DB migrated up to the revision just before uq_groups_owner_name."""
    db = tmp_path / "hub.db"
    monkeypatch.setenv("MESHBAY_DATABASE_URL", f"sqlite+aiosqlite:///{db}")
    cfg = Config(str(HUB / "alembic.ini"))
    command.upgrade(cfg, BEFORE)
    return db, cfg


def test_migration_aborts_on_a_pre_existing_duplicate(at_before):
    db, cfg = at_before
    con = sqlite3.connect(db)
    con.execute(_USER)
    con.execute(_GROUP, ("g1", "Photos"))
    con.execute(_GROUP, ("g2", "photos"))     # same owner, same name bar case
    con.commit()
    con.close()

    with pytest.raises(Exception) as exc:
        command.upgrade(cfg, AFTER)
    assert "uq_groups_owner_name" in str(exc.value)
    assert "photos" in str(exc.value).lower()


def test_migration_runs_when_data_is_clean(at_before):
    db, cfg = at_before
    con = sqlite3.connect(db)
    con.execute(_USER)
    con.execute(_GROUP, ("g1", "Photos"))
    con.execute(_GROUP, ("g2", "Videos"))
    con.commit()
    con.close()

    command.upgrade(cfg, AFTER)

    con = sqlite3.connect(db)
    idx = [r[0] for r in con.execute(
        "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='groups'")]
    con.close()
    assert "uq_groups_owner_name" in idx