"""Tests for db.py — Schema, CRUD, FTS5."""

from db import (
    add_prompt,
    delete_prompt,
    get_prompt,
    get_stats,
    init_db,
    list_prompts,
    list_top_used,
    list_untagged,
    set_rating,
    toggle_star,
    update_prompt,
    increment_use,
    search_fts,
)


class TestSchema:
    def test_init_db_creates_tables(self, temp_db):
        import sqlite3
        conn = sqlite3.connect(temp_db)
        tables = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table'"
        ).fetchall()
        table_names = [t[0] for t in tables]
        assert "prompts" in table_names
        conn.close()

    def test_init_db_idempotent(self, temp_db):
        """Calling init_db twice should not error."""
        init_db()
        init_db()


class TestCRUD:
    def test_add_and_get(self):
        pid = add_prompt(title="Test", content="Hello world", tags="test")
        row = get_prompt(pid)
        assert row is not None
        assert row["title"] == "Test"
        assert row["content"] == "Hello world"
        assert row["tags"] == "test"

    def test_add_returns_id(self):
        pid1 = add_prompt(title="A", content="aaa")
        pid2 = add_prompt(title="B", content="bbb")
        assert pid2 > pid1

    def test_delete(self):
        pid = add_prompt(title="Del", content="to delete")
        delete_prompt(pid)
        assert get_prompt(pid) is None

    def test_update(self):
        pid = add_prompt(title="Old", content="old content")
        update_prompt(pid, title="New", content="new content")
        row = get_prompt(pid)
        assert row["title"] == "New"
        assert row["content"] == "new content"

    def test_toggle_star(self):
        pid = add_prompt(title="Star", content="star test")
        assert get_prompt(pid)["is_starred"] == 0
        result = toggle_star(pid)
        assert result == 1
        result = toggle_star(pid)
        assert result == 0

    def test_set_rating(self):
        pid = add_prompt(title="Rate", content="rate test")
        set_rating(pid, 4)
        assert get_prompt(pid)["rating"] == 4

    def test_increment_use(self):
        pid = add_prompt(title="Use", content="use test")
        assert get_prompt(pid)["use_count"] == 0
        increment_use(pid)
        increment_use(pid)
        assert get_prompt(pid)["use_count"] == 2

    def test_list_prompts_default(self):
        add_prompt(title="A", content="aaa")
        add_prompt(title="B", content="bbb")
        rows = list_prompts()
        assert len(rows) == 2

    def test_list_prompts_starred_filter(self):
        add_prompt(title="A", content="aaa", is_starred=1)
        add_prompt(title="B", content="bbb")
        rows = list_prompts(starred=True)
        assert len(rows) == 1
        assert rows[0]["title"] == "A"

    def test_list_prompts_tag_filter(self):
        add_prompt(title="A", content="aaa", tags="python,coding")
        add_prompt(title="B", content="bbb", tags="writing")
        rows = list_prompts(tag="python")
        assert len(rows) == 1

    def test_list_prompts_limit(self):
        for i in range(10):
            add_prompt(title=f"P{i}", content=f"content {i}")
        rows = list_prompts(limit=3)
        assert len(rows) == 3


class TestFTS:
    def test_fts_search_by_content(self):
        add_prompt(title="Python Tips", content="Use list comprehensions for cleaner code")
        add_prompt(title="Cooking", content="Add salt to boiling water")
        results = search_fts('"list comprehensions"')
        assert len(results) == 1
        assert results[0]["title"] == "Python Tips"

    def test_fts_search_by_title(self):
        add_prompt(title="Refactoring Guide", content="Some content here")
        results = search_fts("refactoring")
        assert len(results) == 1

    def test_fts_no_results(self):
        add_prompt(title="Test", content="test content")
        results = search_fts("nonexistent_xyz_term")
        assert len(results) == 0


class TestTopUsed:
    def test_list_top_used_empty(self):
        rows = list_top_used()
        assert rows == []

    def test_list_top_used_returns_only_used(self):
        pid1 = add_prompt(title="Used", content="used content")
        add_prompt(title="Unused", content="unused content")
        increment_use(pid1)
        increment_use(pid1)
        rows = list_top_used()
        assert len(rows) == 1
        assert rows[0]["id"] == pid1
        assert rows[0]["use_count"] == 2

    def test_list_top_used_sorted_desc(self):
        pid1 = add_prompt(title="Low", content="low")
        pid2 = add_prompt(title="High", content="high")
        increment_use(pid1)
        increment_use(pid2)
        increment_use(pid2)
        increment_use(pid2)
        rows = list_top_used()
        assert rows[0]["id"] == pid2
        assert rows[1]["id"] == pid1

    def test_list_top_used_limit(self):
        for i in range(5):
            pid = add_prompt(title=f"P{i}", content=f"c{i}")
            for _ in range(i + 1):
                increment_use(pid)
        rows = list_top_used(limit=3)
        assert len(rows) == 3


class TestListUntagged:
    def test_list_untagged_returns_null_tags(self):
        add_prompt(title="No tags", content="no tags")
        rows = list_untagged()
        assert len(rows) == 1

    def test_list_untagged_excludes_tagged(self):
        add_prompt(title="Tagged", content="content", tags="python")
        add_prompt(title="No tag", content="other")
        rows = list_untagged()
        assert len(rows) == 1
        assert rows[0]["title"] == "No tag"

    def test_list_untagged_empty_string_counts(self):
        pid = add_prompt(title="Empty tags", content="empty")
        update_prompt(pid, tags="")
        rows = list_untagged()
        assert len(rows) == 1

    def test_list_untagged_limit(self):
        for i in range(10):
            add_prompt(title=f"U{i}", content=f"c{i}")
        rows = list_untagged(limit=5)
        assert len(rows) == 5


class TestStats:
    def test_stats_empty(self):
        s = get_stats()
        assert s["total"] == 0
        assert s["starred"] == 0

    def test_stats_counts(self):
        add_prompt(title="A", content="aaa", tags="python", is_starred=1)
        add_prompt(title="B", content="bbb", tags="python,coding")
        add_prompt(title="C", content="ccc", source="chatgpt")
        s = get_stats()
        assert s["total"] == 3
        assert s["starred"] == 1
        assert s["by_tag"]["python"] == 2
        assert s["by_tag"]["coding"] == 1