import os
import sqlite3
import subprocess
import sys

import pytest


def run_alembic(database_url: str, *arguments: str) -> None:
    environment = os.environ.copy()
    environment.update({
        "APP_SECRET": "migration-test-secret-that-is-longer-than-32-characters",
        "OWNER_BOOTSTRAP_TOKEN": "migration-bootstrap-token-that-is-longer-than-32",
        "DATABASE_URL": database_url,
        "REDIS_URL": "redis://localhost:6379/15",
    })
    subprocess.run(
        [sys.executable, "-m", "alembic", *arguments],
        check=True,
        capture_output=True,
        text=True,
        env=environment,
    )


def test_migration_round_trip_and_model_drift(tmp_path):
    database_file = tmp_path / "migration.sqlite3"
    database_url = f"sqlite+aiosqlite:///{database_file.as_posix()}"
    run_alembic(database_url, "upgrade", "head")
    run_alembic(database_url, "check")

    with sqlite3.connect(database_file) as connection:
        tables = {row[0] for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type = 'table'")}
        triggers = {row[0] for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type = 'trigger'")}
    assert {"users", "workspaces", "device_sessions", "audit_events", "login_events",
            "approval_requests", "installation_state", "executions", "execution_steps",
            "ai_model_profiles",
            "computer_profiles", "computer_sessions", "computer_control_leases",
            "computer_actions", "computer_artifacts", "computer_checkpoints",
            "workspace_control_states",
            "computers", "computer_templates", "computer_network_policies",
            "computer_operations", "computer_snapshots", "computer_metrics",
            "workspace_computer_limits",
            "alembic_version"} <= tables
    assert {"audit_events_no_update", "audit_events_no_delete",
            "login_events_no_update", "login_events_no_delete"} <= triggers

    with sqlite3.connect(database_file) as connection:
        installation = connection.execute(
            "SELECT owner_setup_completed FROM installation_state WHERE id = 1"
        ).fetchone()
        assert installation == (0,)
        connection.execute(
            "INSERT INTO login_events "
            "(id, email_hash, successful, reason) VALUES (?, ?, ?, ?)",
            ("migration-probe", "0" * 64, False, "probe"),
        )
        with pytest.raises(sqlite3.IntegrityError, match="append-only"):
            connection.execute(
                "UPDATE login_events SET reason = ? WHERE id = ?",
                ("mutated", "migration-probe"),
            )
        with pytest.raises(sqlite3.IntegrityError, match="append-only"):
            connection.execute("DELETE FROM login_events WHERE id = ?", ("migration-probe",))

    run_alembic(database_url, "downgrade", "base")
    with sqlite3.connect(database_file) as connection:
        remaining = {row[0] for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type = 'table'")}
    assert not ({"users", "workspaces", "device_sessions", "audit_events", "executions"}
                & remaining)

    run_alembic(database_url, "upgrade", "head")


def test_runtime_permission_migration_updates_existing_system_roles(tmp_path):
    database_file = tmp_path / "runtime-permissions.sqlite3"
    database_url = f"sqlite+aiosqlite:///{database_file.as_posix()}"
    run_alembic(database_url, "upgrade", "7e76a1a0dd21")
    workspace_id = "00000000000000000000000000000001"
    role_id = "00000000000000000000000000000002"
    user_id = "00000000000000000000000000000003"
    membership_id = "00000000000000000000000000000004"
    with sqlite3.connect(database_file) as connection:
        connection.executemany(
            "INSERT INTO permissions (key, description) VALUES (?, ?)",
            [(key, key) for key in (
                "calendar.read", "email.read", "whatsapp.read", "tasks.read", "memory.read",
            )],
        )
        connection.execute(
            "INSERT INTO workspaces (id, name, timezone, audit_sequence) VALUES (?, ?, ?, ?)",
            (workspace_id, "Existing", "UTC", 0),
        )
        connection.execute(
            "INSERT INTO users (id, email, password_hash, display_name, is_active) "
            "VALUES (?, ?, ?, ?, ?)",
            (user_id, "existing@example.com", "not-used", "Existing", True),
        )
        connection.execute(
            "INSERT INTO workspace_memberships (id, workspace_id, user_id, status) "
            "VALUES (?, ?, ?, ?)",
            (membership_id, workspace_id, user_id, "active"),
        )
        connection.execute(
            "INSERT INTO roles (id, workspace_id, name, is_system) VALUES (?, ?, ?, ?)",
            (role_id, workspace_id, "Owner", True),
        )
        connection.execute(
            "INSERT INTO membership_roles (workspace_id, membership_id, role_id) "
            "VALUES (?, ?, ?)",
            (workspace_id, membership_id, role_id),
        )
        connection.commit()

    run_alembic(database_url, "upgrade", "head")
    with sqlite3.connect(database_file) as connection:
        permission_keys = {
            row[0] for row in connection.execute("SELECT key FROM permissions")
        }
        owner_runtime_permissions = {
            row[0]
            for row in connection.execute(
                "SELECT permission_key FROM role_permissions WHERE role_id = ?",
                (role_id,),
            )
        }
        default_agent = connection.execute(
            "SELECT id, status, autonomy_level FROM agents WHERE workspace_id = ?",
            (workspace_id,),
        ).fetchone()
        default_agent_permissions = {
            row[0] for row in connection.execute(
                "SELECT permission_key FROM agent_permissions WHERE agent_id = ?",
                (default_agent[0],),
            )
        }
        personal_computer = connection.execute(
            "SELECT name, status FROM computers WHERE workspace_id = ?",
            (workspace_id,),
        ).fetchone()
        control_state = connection.execute(
            "SELECT emergency_stopped, version FROM workspace_control_states "
            "WHERE workspace_id = ?",
            (workspace_id,),
        ).fetchone()
    expected = {
        "system.status.read",
        "workspace.profile.read",
        "executions.create",
        "executions.read",
        "executions.run",
        "executions.cancel",
    }
    assert expected <= permission_keys
    assert expected <= owner_runtime_permissions
    assert "agents.read" in permission_keys
    assert "agents.read" in owner_runtime_permissions
    assert "settings.read" in permission_keys
    assert "settings.read" in owner_runtime_permissions
    assert "computer.emergency_stop" in permission_keys
    assert "computer.emergency_stop" in owner_runtime_permissions
    assert default_agent[1:] == ("disabled", 3)
    assert {"calendar.read", "email.read", "whatsapp.read", "tasks.read", "memory.read"} <= (
        default_agent_permissions
    )
    assert personal_computer == (
        "Personal Executive Assistant - Private Computer", "unconfigured"
    )
    assert control_state == (0, 1)