import json
from datetime import UTC, datetime
from pathlib import Path

import pytest
import pytest_asyncio
from alembic import command
from alembic.config import Config
from sqlalchemy import inspect
from sqlalchemy.ext.asyncio import AsyncSession

from cartoonos.db.base import Base
from cartoonos.db.models import (
    ApprovalRecordModel,
    ChannelModel,
    EpisodeAssetModel,
    OperationalTaskModel,
    OrganizationModel,
)
from cartoonos.db.repositories import (
    ApprovalRepository,
    ChannelRepository,
    EpisodeRepository,
    OrganizationRepository,
    TaskRepository,
)
from cartoonos.db.session import create_engine_and_session_factory

ROOT_DIR = Path(__file__).resolve().parents[2]


def test_alembic_upgrade_and_downgrade_rehearsal(tmp_path: Path) -> None:
    """Rehearses migration upgrade to head and downgrade to base."""
    db_file = tmp_path / "test_migration.db"
    db_url = f"sqlite:///{db_file}"

    alembic_cfg = Config(str(ROOT_DIR / "backend" / "alembic.ini"))
    alembic_cfg.set_main_option("script_location", str(ROOT_DIR / "backend" / "alembic"))
    alembic_cfg.set_main_option("sqlalchemy.url", db_url)

    # 1. Upgrade to head
    command.upgrade(alembic_cfg, "head")

    from sqlalchemy import create_engine

    engine = create_engine(db_url)
    inspector = inspect(engine)
    tables = set(inspector.get_table_names())
    expected = {
        "organizations",
        "channels",
        "episode_assets",
        "operational_tasks",
        "approval_records",
        "metric_snapshots",
    }
    assert expected.issubset(tables), f"Missing tables: {expected - tables}"
    engine.dispose()

    # 2. Downgrade to base (rollback)
    command.downgrade(alembic_cfg, "base")
    engine = create_engine(db_url)
    inspector = inspect(engine)
    remaining_tables = set(inspector.get_table_names())
    assert not expected.intersection(remaining_tables), (
        f"Tables not dropped: {expected.intersection(remaining_tables)}"
    )
    engine.dispose()

    # 3. Re-apply upgrade to ensure cleanly repeatable
    command.upgrade(alembic_cfg, "head")
    engine = create_engine(db_url)
    inspector = inspect(engine)
    reapplied_tables = set(inspector.get_table_names())
    assert expected.issubset(reapplied_tables)
    engine.dispose()


@pytest_asyncio.fixture
async def async_session(tmp_path: Path):
    """Provides an isolated async SQLite database session with Base metadata created."""
    db_file = tmp_path / "test_async_repo.db"
    db_url = f"sqlite+aiosqlite:///{db_file}"

    engine, session_factory = create_engine_and_session_factory(db_url)

    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

    async with session_factory() as session:
        yield session

    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)

    await engine.dispose()


@pytest.mark.asyncio
async def test_organization_and_channel_repositories(async_session: AsyncSession) -> None:
    org_repo = OrganizationRepository(async_session)
    chan_repo = ChannelRepository(async_session)

    # 1. Create Organization
    org = OrganizationModel(id="ORG-CARTOONOS", name="CartoonOS Studio")
    saved_org = await org_repo.save(org)
    assert saved_org.id == "ORG-CARTOONOS"

    fetched_org = await org_repo.get_by_id("ORG-CARTOONOS")
    assert fetched_org is not None
    assert fetched_org.name == "CartoonOS Studio"

    # 2. Create Channels
    ch1 = ChannelModel(
        id="CH-MAIN",
        organization_id="ORG-CARTOONOS",
        name="Luminautas Principal",
        platform="youtube",
    )
    ch2 = ChannelModel(
        id="CH-SHORTS", organization_id="ORG-CARTOONOS", name="Luminautas Shorts", platform="tiktok"
    )
    await chan_repo.save(ch1)
    await chan_repo.save(ch2)

    channels = await chan_repo.list_for_organization("ORG-CARTOONOS")
    assert len(channels) == 2
    assert [c.id for c in channels] == ["CH-MAIN", "CH-SHORTS"]


@pytest.mark.asyncio
async def test_episode_asset_repository(async_session: AsyncSession) -> None:
    org_repo = OrganizationRepository(async_session)
    chan_repo = ChannelRepository(async_session)
    ep_repo = EpisodeRepository(async_session)

    await org_repo.save(OrganizationModel(id="ORG-TEST", name="Test Org"))
    await chan_repo.save(
        ChannelModel(id="CH-TEST", organization_id="ORG-TEST", name="Test Channel")
    )

    episode = EpisodeAssetModel(
        id="S01E01",
        organization_id="ORG-TEST",
        channel_id="CH-TEST",
        project_id="PROJ-LUMINAUTAS-v1",
        title="Tiko no es lo que crees: el increíble secreto del ajolote",
        state="RESEARCHING",
        priority="P0",
        duration_sec=510,
        language="es-419",
        lead_character_id="CHAR-TIKO-v1",
    )
    await ep_repo.save(episode)

    fetched = await ep_repo.get_by_id("S01E01")
    assert fetched is not None
    assert fetched.project_id == "PROJ-LUMINAUTAS-v1"
    assert fetched.lead_character_id == "CHAR-TIKO-v1"

    episodes = await ep_repo.list_for_channel("CH-TEST")
    assert len(episodes) == 1
    assert episodes[0].id == "S01E01"


@pytest.mark.asyncio
async def test_task_repository_batch_save_and_reload(async_session: AsyncSession) -> None:
    org_repo = OrganizationRepository(async_session)
    task_repo = TaskRepository(async_session)

    await org_repo.save(OrganizationModel(id="ORG-CARTOONOS", name="CartoonOS Studio"))

    plan_path = ROOT_DIR / "data/operations/priority-tasks.json"
    plan_data = json.loads(plan_path.read_text(encoding="utf-8"))

    task_models = [
        OperationalTaskModel(
            id=t["id"],
            organization_id="ORG-CARTOONOS",
            rank=t["rank"],
            priority=t["priority"],
            title=t["title"],
            status=t["status"],
            owner=t["owner"],
            effort=t["effort"],
            acceptance=t["acceptance"],
            depends_on=t.get("depends_on", []),
            evidence=t.get("evidence", []),
            blocked_reason=t.get("blocked_reason"),
        )
        for t in plan_data["tasks"]
    ]

    await task_repo.save_many(task_models)

    # Verify reload of all 50 tasks in order of rank
    loaded_tasks = await task_repo.list_for_organization("ORG-CARTOONOS")
    assert len(loaded_tasks) == 50
    assert loaded_tasks[0].id == "CO-001"
    assert loaded_tasks[-1].id == "CO-050"

    t1 = await task_repo.get_by_id("CO-001")
    assert t1 is not None
    assert t1.title == "Audit readiness and establish one launch plan"


@pytest.mark.asyncio
async def test_approval_repository(async_session: AsyncSession) -> None:
    org_repo = OrganizationRepository(async_session)
    chan_repo = ChannelRepository(async_session)
    ep_repo = EpisodeRepository(async_session)
    approval_repo = ApprovalRepository(async_session)

    await org_repo.save(OrganizationModel(id="ORG-A", name="Org A"))
    await chan_repo.save(ChannelModel(id="CH-A", organization_id="ORG-A", name="Channel A"))
    await ep_repo.save(
        EpisodeAssetModel(
            id="S01E01",
            organization_id="ORG-A",
            channel_id="CH-A",
            project_id="PROJ-LUMINAUTAS-v1",
            title="Episode 1",
            state="ROUGH_CUT_QA",
            priority="P0",
            duration_sec=510,
            language="es-419",
        )
    )

    approval = ApprovalRecordModel(
        id="APP-S01E01-RELEASE",
        organization_id="ORG-A",
        channel_id="CH-A",
        asset_id="S01E01",
        gate="final_release",
        decision="approved",
        reviewer="Head of Content",
        content_brief_version="v0.4",
        approved_at=datetime.now(UTC),
        notes="All safety gates, evidence, and packaging contracts verified.",
    )
    await approval_repo.record_approval(approval)

    latest = await approval_repo.get_latest_for_asset("S01E01", "final_release")
    assert latest is not None
    assert latest.decision == "approved"
    assert latest.reviewer == "Head of Content"
    assert latest.content_brief_version == "v0.4"