"""SQLite repository, transaction, recovery and uniqueness tests."""

from __future__ import annotations

from pathlib import Path

import pytest

from app.database.database import Database
from app.database.models import (
    Job,
    JobStatus,
    PageStatus,
    Video,
    VideoStatus,
)
from app.database.repository import Repository


async def _job(repo: Repository, url: str = "https://example.com/list", owner: int = 1) -> Job:
    job = Job.create(url, owner_id=owner, owner_chat_id=owner)
    await repo.create_job(job)
    return job


async def _video(repo: Repository, job: Job, suffix: str = "1") -> Video:
    video = Video.create(
        job_id=job.id,
        source_url=f"https://example.com/video-{suffix}.mp4",
        page_url=f"https://example.com/page-{suffix}",
        title=f"Video {suffix}",
        caption="Caption",
        media_type="video/mp4",
        media_url=f"https://example.com/video-{suffix}.mp4",
    )
    stored, _ = await repo.add_video(video)
    return stored


@pytest.mark.asyncio
async def test_create_and_read_job(repository: Repository) -> None:
    job = await _job(repository)
    loaded = await repository.get_job(job.id)
    assert loaded is not None
    assert loaded.source_url == job.source_url
    assert loaded.status == JobStatus.PENDING
    assert loaded.owner_id == 1
    assert loaded.correlation_id == job.id[:12]
    assert await repository.get_job("missing") is None


@pytest.mark.asyncio
async def test_active_job_detection_is_transactional(repository: Repository) -> None:
    job = await _job(repository)
    assert (await repository.find_active_job_for_source(job.source_url)).id == job.id
    await repository.set_job_status(job.id, JobStatus.COMPLETED, finished=True)
    assert await repository.find_active_job_for_source(job.source_url) is None
    again = await _job(repository)
    assert again.id != job.id
    assert (await repository.find_active_job_for_source(job.source_url)).id == again.id


@pytest.mark.asyncio
async def test_start_job_is_atomic_and_only_from_pending(repository: Repository) -> None:
    job = await _job(repository)
    assert await repository.start_job(job.id) is True
    assert await repository.start_job(job.id) is False
    loaded = await repository.get_job(job.id)
    assert loaded is not None
    assert loaded.status == JobStatus.RUNNING
    assert loaded.started_at is not None
    assert loaded.current_stage == "crawl"


@pytest.mark.asyncio
async def test_update_job_fields_ignores_unknown_and_handles_enums(
    repository: Repository,
) -> None:
    job = await _job(repository)
    assert await repository.update_job_fields(
        job.id,
        status=JobStatus.PAUSED,
        processed_pages=4,
        unknown_field="ignored",
    )
    loaded = await repository.get_job(job.id)
    assert loaded is not None
    assert loaded.status == JobStatus.PAUSED
    assert loaded.processed_pages == 4
    assert await repository.update_job_fields(job.id, unknown_field="ignored") is False
    assert await repository.update_job_fields("missing", processed_pages=1) is False


@pytest.mark.asyncio
async def test_set_job_status_finished_sets_timestamp(repository: Repository) -> None:
    job = await _job(repository)
    await repository.set_job_status(job.id, JobStatus.FAILED, error_message="boom", finished=True)
    loaded = await repository.get_job(job.id)
    assert loaded is not None
    assert loaded.status == JobStatus.FAILED
    assert loaded.error_message == "boom"
    assert loaded.finished_at is not None


@pytest.mark.asyncio
async def test_pages_are_unique_per_job_only(repository: Repository) -> None:
    first_job = await _job(repository)
    second_job = await _job(repository, "https://example.com/other")
    page, created = await repository.add_page(first_job.id, "https://example.com/list")
    assert created is True
    same, created_again = await repository.add_page(first_job.id, "https://example.com/list")
    assert created_again is False
    assert same.id == page.id

    other, created_other = await repository.add_page(second_job.id, "https://example.com/list")
    assert created_other is True
    assert other.id != page.id

    await repository.update_page(page.id, PageStatus.PROCESSED, http_status=200)
    pages = await repository.list_pages(first_job.id)
    assert len(pages) == 1
    assert pages[0].status == PageStatus.PROCESSED
    assert pages[0].processed_at is not None
    assert pages[0].http_status == 200


@pytest.mark.asyncio
async def test_video_source_url_is_globally_unique(repository: Repository) -> None:
    first_job = await _job(repository)
    second_job = await _job(repository, "https://example.com/other")
    original = Video.create(
        job_id=first_job.id,
        source_url="https://example.com/shared.mp4",
        page_url="https://example.com/list",
        title="Original",
        caption="Original caption",
        media_type="video/mp4",
        media_url="https://example.com/shared.mp4",
    )
    stored, created = await repository.add_video(original)
    assert created is True
    assert stored.id == original.id

    duplicate = Video.create(
        job_id=second_job.id,
        source_url="https://example.com/shared.mp4",
        page_url="https://example.com/other",
        title="Repost",
        caption="Repost",
        media_type="video/mp4",
    )
    existing, created_again = await repository.add_video(duplicate)
    assert created_again is False
    assert existing.id == original.id
    assert existing.job_id == first_job.id
    assert existing.title == "Original"
    assert len(await repository.list_videos(second_job.id)) == 0


@pytest.mark.asyncio
async def test_claim_video_transitions_and_attempts(repository: Repository) -> None:
    job = await _job(repository)
    video = await _video(repository, job)
    assert await repository.claim_video(video.id, (VideoStatus.DOWNLOADED,), VideoStatus.UPLOADING) is False
    assert await repository.claim_video(video.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADING) is True
    loaded = await repository.get_video(video.id)
    assert loaded is not None
    assert loaded.status == VideoStatus.DOWNLOADING
    assert loaded.attempts == 1
    assert await repository.claim_video(video.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADING) is False
    assert await repository.claim_video(
        video.id, (VideoStatus.DOWNLOADING,), VideoStatus.DOWNLOADED
    ) is True
    loaded = await repository.get_video(video.id)
    assert loaded is not None
    assert loaded.status == VideoStatus.DOWNLOADED
    assert loaded.attempts == 1


@pytest.mark.asyncio
async def test_video_queries_and_updates(repository: Repository) -> None:
    job = await _job(repository)
    first = await _video(repository, job, "1")
    second = await _video(repository, job, "2")
    assert await repository.get_video_by_source(first.source_url) is not None
    assert {video.id for video in await repository.list_videos(job.id)} == {
        first.id,
        second.id,
    }
    assert {video.id for video in await repository.list_videos(
        job.id, [VideoStatus.PENDING]
    )} == {first.id, second.id}
    assert await repository.list_videos(job.id, []) == []
    assert await repository.update_video_fields(first.id, status=VideoStatus.FAILED) is True
    assert await repository.update_video_fields(first.id, nonsense=1) is False
    loaded = await repository.get_video(first.id)
    assert loaded is not None
    assert loaded.status == VideoStatus.FAILED


@pytest.mark.asyncio
async def test_cancel_and_reset_video_state(repository: Repository) -> None:
    job = await _job(repository)
    pending = await _video(repository, job, "pending")
    downloading = await _video(repository, job, "downloading")
    downloaded = await _video(repository, job, "downloaded")
    uploading = await _video(repository, job, "uploading")
    await repository.claim_video(downloading.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADING)
    await repository.claim_video(downloaded.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADED)
    await repository.claim_video(uploading.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADED)
    await repository.claim_video(uploading.id, (VideoStatus.DOWNLOADED,), VideoStatus.UPLOADING)

    await repository.cancel_pending_videos(job.id)
    statuses = {video.id: video.status for video in await repository.list_videos(job.id)}
    assert statuses[pending.id] == VideoStatus.CANCELLED
    assert statuses[downloaded.id] == VideoStatus.CANCELLED
    assert statuses[downloading.id] == VideoStatus.DOWNLOADING
    assert statuses[uploading.id] == VideoStatus.UPLOADING

    await repository.reset_interrupted_videos(job.id)
    statuses = {video.id: video.status for video in await repository.list_videos(job.id)}
    assert statuses[downloading.id] == VideoStatus.PENDING
    assert statuses[uploading.id] == VideoStatus.UPLOAD_UNKNOWN

    await repository.cancel_unfinished_videos(job.id)
    statuses = {video.id: video.status for video in await repository.list_videos(job.id)}
    assert statuses[pending.id] == VideoStatus.CANCELLED
    assert statuses[downloading.id] == VideoStatus.CANCELLED
    assert statuses[downloaded.id] == VideoStatus.CANCELLED
    assert statuses[uploading.id] == VideoStatus.UPLOAD_UNKNOWN


@pytest.mark.asyncio
async def test_refresh_job_counts(repository: Repository) -> None:
    job = await _job(repository)
    processed, _ = await repository.add_page(job.id, "https://example.com/one")
    skipped, _ = await repository.add_page(job.id, "https://example.com/two")
    await repository.update_page(processed.id, PageStatus.PROCESSED)
    await repository.update_page(skipped.id, PageStatus.SKIPPED)

    uploaded = await _video(repository, job, "uploaded")
    failed = await _video(repository, job, "failed")
    unknown = await _video(repository, job, "unknown")
    await _video(repository, job, "pending")
    await repository.update_video_fields(uploaded.id, status=VideoStatus.UPLOADED)
    await repository.update_video_fields(failed.id, status=VideoStatus.FAILED)
    await repository.update_video_fields(unknown.id, status=VideoStatus.UPLOAD_UNKNOWN)

    refreshed = await repository.refresh_job_counts(job.id)
    assert refreshed is not None
    assert refreshed.total_pages == 2
    assert refreshed.processed_pages == 2
    assert refreshed.total_videos == 4
    assert refreshed.processed_videos == 3
    assert refreshed.downloaded_videos == 2
    assert refreshed.uploaded_videos == 1
    assert refreshed.failed_videos == 2
    assert refreshed.id == job.id

    counts = await repository.video_status_counts(job.id)
    assert counts[VideoStatus.PENDING.value] == 1
    assert counts[VideoStatus.FAILED.value] == 1
    assert counts[VideoStatus.UPLOAD_UNKNOWN.value] == 1
    assert counts[VideoStatus.UPLOADED.value] == 1


@pytest.mark.asyncio
async def test_restart_recovery_is_conservative(repository: Repository) -> None:
    interrupted = await _job(repository, "https://example.com/interrupted")
    paused = await _job(repository, "https://example.com/paused")
    await repository.start_job(interrupted.id)
    await repository.update_job_fields(paused.id, status=JobStatus.PAUSED)

    downloading = await _video(repository, interrupted, "downloading")
    await repository.claim_video(
        downloading.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADING
    )
    uploading = await _video(repository, interrupted, "uploading")
    await repository.claim_video(uploading.id, (VideoStatus.PENDING,), VideoStatus.DOWNLOADED)
    await repository.claim_video(uploading.id, (VideoStatus.DOWNLOADED,), VideoStatus.UPLOADING)
    page, _ = await repository.add_page(interrupted.id, "https://example.com/interrupted")
    await repository.update_page(page.id, PageStatus.PROCESSING)

    recovered = await repository.recover_after_restart()
    assert recovered == [interrupted.id]
    job = await repository.get_job(interrupted.id)
    assert job is not None
    assert job.status == JobStatus.PENDING
    assert job.error_message is not None
    assert "بازیابی" in job.error_message
    assert (await repository.get_video(downloading.id)).status == VideoStatus.PENDING
    assert (await repository.get_video(uploading.id)).status == VideoStatus.UPLOAD_UNKNOWN
    assert (await repository.list_pages(interrupted.id))[0].status == PageStatus.PENDING
    paused_job = await repository.get_job(paused.id)
    assert paused_job is not None
    assert paused_job.status == JobStatus.PAUSED


@pytest.mark.asyncio
async def test_settings_round_trip(repository: Repository) -> None:
    assert await repository.get_setting("destination_chat_id") is None
    assert await repository.get_setting("missing", "default") == "default"
    await repository.set_setting("destination_chat_id", "-100123")
    assert await repository.get_setting("destination_chat_id") == "-100123"
    assert await repository.get_setting_updated_at("destination_chat_id") is not None
    await repository.set_setting("destination_chat_id", "-100456")
    assert await repository.get_setting("destination_chat_id") == "-100456"
    await repository.delete_setting("destination_chat_id")
    assert await repository.get_setting("destination_chat_id") is None
    assert await repository.get_setting_updated_at("destination_chat_id") is None


@pytest.mark.asyncio
async def test_job_listing_counts_and_history(repository: Repository) -> None:
    first = await _job(repository, "https://example.com/a", owner=1)
    second = await _job(repository, "https://example.com/b", owner=2)
    third = await _job(repository, "https://example.com/c", owner=1)
    await repository.set_job_status(first.id, JobStatus.COMPLETED, finished=True)
    await repository.set_job_status(second.id, JobStatus.FAILED, finished=True)

    active = await repository.get_active_jobs()
    assert [job.id for job in active] == [third.id]
    owner_active = await repository.get_active_jobs(1)
    assert [job.id for job in owner_active] == [third.id]
    assert await repository.latest_active_job(2) is None
    assert await repository.latest_active_job() is not None

    all_jobs = await repository.list_jobs(limit=10)
    assert {job.id for job in all_jobs} == {first.id, second.id, third.id}
    terminal = await repository.list_jobs(limit=10, statuses=[JobStatus.COMPLETED, JobStatus.FAILED])
    assert {job.id for job in terminal} == {first.id, second.id}
    assert await repository.list_jobs(limit=10, statuses=[]) == []

    counts = await repository.job_status_counts()
    assert counts[JobStatus.PENDING.value] == 1
    assert counts[JobStatus.COMPLETED.value] == 1
    assert counts[JobStatus.FAILED.value] == 1

    uploaded = await _video(repository, first, "uploaded")
    await repository.update_video_fields(
        uploaded.id,
        status=VideoStatus.UPLOADED,
        telegram_message_id=55,
        uploaded_at="2026-01-01T00:00:00+00:00",
    )
    assert await repository.total_uploaded_videos() == 1
    recent = await repository.recent_uploaded()
    assert [video.id for video in recent] == [uploaded.id]


@pytest.mark.asyncio
async def test_transaction_rolls_back(repository: Repository) -> None:
    job = Job.create("https://example.com/rollback", 1, 1)
    with pytest.raises(RuntimeError):
        async with repository.db.transaction() as connection:
            await connection.execute(
                "INSERT INTO jobs (id, source_url, status, owner_id, owner_chat_id, "
                "correlation_id, created_at, updated_at, current_stage) "
                "VALUES (?, ?, 'PENDING', 1, 1, 'x', 'now', 'now', 'queued')",
                (job.id, job.source_url),
            )
            raise RuntimeError("abort")
    assert await repository.get_job(job.id) is None


@pytest.mark.asyncio
async def test_database_can_be_reopened(tmp_path: Path) -> None:
    database = Database(tmp_path / "reopen.db")
    await database.connect()
    first = await database.fetch_one("SELECT 1 AS value")
    assert first is not None
    assert first["value"] == 1
    await database.close()
    with pytest.raises(RuntimeError):
        _ = database.connection
    with pytest.raises(RuntimeError):
        await database.fetch_one("SELECT 1")
    await database.connect()
    second = await database.fetch_one("SELECT 2 AS value")
    assert second is not None
    assert second["value"] == 2
    await database.close()
    await database.close()