"""Tests for SQLite storage layer."""
from datetime import UTC, datetime
from pathlib import Path

import pytest

from ai_usecases_explorer.models.usecase import Novelty, ScenarioType, UseCase
from ai_usecases_explorer.storage.database import Database


def _make_usecase(id: str = "abc123def456", url: str = "https://example.com/1") -> UseCase:
    now = datetime.now(tz=UTC)
    return UseCase(
        id=id,
        title="Test Use Case",
        summary="A test summary.",
        source_url=url,
        source_platform="hackernews",
        author="testuser",
        published_at=now,
        collected_at=now,
        scenario_type=ScenarioType.CODE_GEN_REVIEW,
        tools_used=["Claude", "Python"],
        has_local_data=False,
        has_workflow_opt=True,
        value_score=0.75,
        is_real_value=True,
        novelty=Novelty.NEW,
        similar_to_ids=[],
        raw_content="raw text",
    )


@pytest.fixture
def db(tmp_path: Path) -> Database:
    """Create a fresh in-memory Database for each test."""
    return Database(db_path=tmp_path / "test.db")


class TestDatabaseInit:
    def test_creates_db_file(self, tmp_path: Path) -> None:
        db_file = tmp_path / "mydb.db"
        Database(db_path=db_file)
        assert db_file.exists()

    def test_accepts_path_object(self, tmp_path: Path) -> None:
        db = Database(db_path=tmp_path / "test.db")
        assert db is not None


class TestDatabaseSave:
    def test_save_returns_usecase_id(self, db: Database) -> None:
        uc = _make_usecase()
        result = db.save(uc)
        assert result == uc.id

    def test_save_persists_record(self, db: Database) -> None:
        uc = _make_usecase()
        db.save(uc)
        assert db.exists(uc.id)

    def test_save_duplicate_does_not_raise(self, db: Database) -> None:
        uc = _make_usecase()
        db.save(uc)
        db.save(uc)  # should not raise

    def test_save_multiple_records(self, db: Database) -> None:
        uc1 = _make_usecase("aaa111bbb222", "https://example.com/1")
        uc2 = _make_usecase("ccc333ddd444", "https://example.com/2")
        db.save(uc1)
        db.save(uc2)
        assert db.exists(uc1.id)
        assert db.exists(uc2.id)


class TestDatabaseExists:
    def test_exists_false_for_unknown_id(self, db: Database) -> None:
        assert db.exists("nonexistent123") is False

    def test_exists_true_after_save(self, db: Database) -> None:
        uc = _make_usecase()
        db.save(uc)
        assert db.exists(uc.id) is True


class TestDatabaseGetByScenario:
    def test_returns_empty_list_when_no_records(self, db: Database) -> None:
        results = db.get_by_scenario(ScenarioType.CODE_GEN_REVIEW)
        assert results == []

    def test_returns_matching_records(self, db: Database) -> None:
        uc = _make_usecase()
        db.save(uc)
        results = db.get_by_scenario(ScenarioType.CODE_GEN_REVIEW)
        assert len(results) == 1
        assert results[0].id == uc.id

    def test_does_not_return_other_scenario(self, db: Database) -> None:
        uc = _make_usecase()
        db.save(uc)
        results = db.get_by_scenario(ScenarioType.WORKFLOW_AUTOMATION)
        assert results == []

    def test_returns_multiple_matching_records(self, db: Database) -> None:
        uc1 = _make_usecase("aaa111bbb222", "https://example.com/1")
        uc2 = _make_usecase("ccc333ddd444", "https://example.com/2")
        db.save(uc1)
        db.save(uc2)
        results = db.get_by_scenario(ScenarioType.CODE_GEN_REVIEW)
        assert len(results) == 2

    def test_limits_results(self, db: Database) -> None:
        for i in range(5):
            uc = _make_usecase(f"id{i:010d}", f"https://example.com/{i}")
            db.save(uc)
        results = db.get_by_scenario(ScenarioType.CODE_GEN_REVIEW, limit=2)
        assert len(results) == 2


class TestDatabaseGetRecent:
    def test_get_recent_returns_last_n_real_value(self, db: Database) -> None:
        uc1 = _make_usecase("aaa111bbb222", "https://example.com/1")
        uc2 = _make_usecase("ccc333ddd444", "https://example.com/2")
        db.save(uc1)
        db.save(uc2)
        results = db.get_recent(limit=10)
        assert len(results) == 2

    def test_roundtrip_preserves_fields(self, db: Database) -> None:
        uc = _make_usecase()
        db.save(uc)
        results = db.get_by_scenario(ScenarioType.CODE_GEN_REVIEW)
        loaded = results[0]
        assert loaded.title == uc.title
        assert loaded.tools_used == uc.tools_used
        assert loaded.value_score == uc.value_score
        assert loaded.novelty == uc.novelty
        assert loaded.similar_to_ids == uc.similar_to_ids