import sqlite3
import tempfile
import unittest
from pathlib import Path

from rcl_rlm.message_database import MessageDatabase


class ContactsAttachTests(unittest.TestCase):
    def test_messages_with_contacts_view_resolves_sender_name(self) -> None:
        with tempfile.TemporaryDirectory() as tmpdir:
            tmp_path = Path(tmpdir)
            messages_path = tmp_path / "shadow.db"
            contacts_path = tmp_path / "contacts.db"

            self._create_messages_db(messages_path, sender="+15550001111")
            self._create_contacts_db(contacts_path, sender="+15550001111", display_name="Mom")

            db = MessageDatabase(
                str(messages_path),
                contacts_db_path=str(contacts_path),
                quiet=True,
            )

            rows = db.query(
                "SELECT guid, sender_name, sender, text "
                "FROM messages_with_contacts "
                "WHERE sender_name = 'Mom'"
            )

            self.assertEqual(len(rows), 1)
            self.assertEqual(rows[0]["sender_name"], "Mom")
            self.assertEqual(rows[0]["sender"], "+15550001111")

    def test_empty_contacts_db_falls_back_to_placeholder(self) -> None:
        with tempfile.TemporaryDirectory() as tmpdir:
            tmp_path = Path(tmpdir)
            messages_path = tmp_path / "shadow.db"
            contacts_path = tmp_path / "contacts.db"

            self._create_messages_db(messages_path, sender="+15550001111")

            # Create empty contacts.db without schema
            conn = sqlite3.connect(contacts_path)
            conn.close()

            db = MessageDatabase(
                str(messages_path),
                contacts_db_path=str(contacts_path),
                quiet=True,
            )

            rows = db.query(
                "SELECT guid, sender_name, sender "
                "FROM messages_with_contacts "
                "LIMIT 1"
            )

            self.assertEqual(len(rows), 1)
            self.assertEqual(rows[0]["sender_name"], "+15550001111")
            self.assertEqual(rows[0]["sender"], "+15550001111")

    def _create_messages_db(self, path: Path, sender: str) -> None:
        conn = sqlite3.connect(path)
        try:
            conn.executescript(
                """
                CREATE TABLE messages (
                    rowid INTEGER PRIMARY KEY,
                    guid TEXT NOT NULL,
                    sender TEXT NOT NULL,
                    text TEXT NOT NULL,
                    timestamp INTEGER NOT NULL
                );
                """
            )
            conn.execute(
                "INSERT INTO messages (guid, sender, text, timestamp) VALUES (?, ?, ?, ?)",
                ("guid-1", sender, "Hello from Mom", 12345),
            )
            conn.commit()
        finally:
            conn.close()

    def _create_contacts_db(self, path: Path, sender: str, display_name: str) -> None:
        conn = sqlite3.connect(path)
        try:
            conn.executescript(
                """
                CREATE TABLE contacts (
                    id INTEGER PRIMARY KEY,
                    apple_id TEXT UNIQUE,
                    display_name TEXT NOT NULL,
                    given_name TEXT,
                    family_name TEXT,
                    organization TEXT,
                    job_title TEXT,
                    notes TEXT,
                    updated_at INTEGER NOT NULL
                );

                CREATE TABLE contact_identifiers (
                    contact_id INTEGER REFERENCES contacts(id) ON DELETE CASCADE,
                    type TEXT NOT NULL,
                    value TEXT NOT NULL,
                    PRIMARY KEY (type, value)
                );

                CREATE VIRTUAL TABLE contacts_fts USING fts5(
                    display_name, organization, notes,
                    content='contacts',
                    content_rowid='id'
                );

                CREATE TRIGGER contacts_fts_insert AFTER INSERT ON contacts BEGIN
                    INSERT INTO contacts_fts(rowid, display_name, organization, notes)
                    VALUES (new.id, new.display_name, new.organization, new.notes);
                END;

                CREATE TRIGGER contacts_fts_delete AFTER DELETE ON contacts BEGIN
                    INSERT INTO contacts_fts(contacts_fts, rowid, display_name, organization, notes)
                    VALUES ('delete', old.id, old.display_name, old.organization, old.notes);
                END;

                CREATE TRIGGER contacts_fts_update AFTER UPDATE ON contacts BEGIN
                    INSERT INTO contacts_fts(contacts_fts, rowid, display_name, organization, notes)
                    VALUES ('delete', old.id, old.display_name, old.organization, old.notes);
                    INSERT INTO contacts_fts(rowid, display_name, organization, notes)
                    VALUES (new.id, new.display_name, new.organization, new.notes);
                END;
                """
            )

            cursor = conn.execute(
                "INSERT INTO contacts (apple_id, display_name, updated_at) VALUES (?, ?, ?)",
                ("test-contact", display_name, 12345),
            )
            contact_id = cursor.lastrowid
            conn.execute(
                "INSERT INTO contact_identifiers (contact_id, type, value) VALUES (?, ?, ?)",
                (contact_id, "phone", sender),
            )
            conn.commit()
        finally:
            conn.close()


    def test_view_exposes_rowid_for_fts_joins(self) -> None:
        """Verify the view includes rowid so FTS joins work (fixes P1 issue)."""
        with tempfile.TemporaryDirectory() as tmpdir:
            tmp_path = Path(tmpdir)
            messages_path = tmp_path / "shadow.db"
            contacts_path = tmp_path / "contacts.db"

            self._create_messages_db(messages_path, sender="+15550001111")
            self._create_contacts_db(contacts_path, sender="+15550001111", display_name="Mom")

            db = MessageDatabase(
                str(messages_path),
                contacts_db_path=str(contacts_path),
                quiet=True,
            )

            # Query that uses rowid - this would fail without the fix
            rows = db.query(
                "SELECT rowid, guid, sender_name FROM messages_with_contacts LIMIT 1"
            )

            self.assertEqual(len(rows), 1)
            self.assertIn("rowid", rows[0])
            self.assertIsNotNone(rows[0]["rowid"])

    def test_search_returns_both_sender_and_sender_name(self) -> None:
        """Verify search() returns both sender (for lookups) and sender_name (for display)."""
        with tempfile.TemporaryDirectory() as tmpdir:
            tmp_path = Path(tmpdir)
            messages_path = tmp_path / "shadow.db"
            contacts_path = tmp_path / "contacts.db"

            self._create_messages_db_with_fts(messages_path, sender="+15550001111")
            self._create_contacts_db(contacts_path, sender="+15550001111", display_name="Mom")

            db = MessageDatabase(
                str(messages_path),
                contacts_db_path=str(contacts_path),
                quiet=True,
            )

            results = db.search("Hello", limit=1)

            self.assertEqual(len(results), 1)
            # sender should be raw handle for lookups
            self.assertEqual(results[0]["sender"], "+15550001111")
            # sender_name should be resolved for display
            self.assertEqual(results[0]["sender_name"], "Mom")

    def test_search_works_with_contacts_attached(self) -> None:
        """Verify FTS search works when contacts are attached (uses rowid join)."""
        with tempfile.TemporaryDirectory() as tmpdir:
            tmp_path = Path(tmpdir)
            messages_path = tmp_path / "shadow.db"
            contacts_path = tmp_path / "contacts.db"

            self._create_messages_db_with_fts(messages_path, sender="+15550001111")
            self._create_contacts_db(contacts_path, sender="+15550001111", display_name="Mom")

            db = MessageDatabase(
                str(messages_path),
                contacts_db_path=str(contacts_path),
                quiet=True,
            )

            # This would fail without rowid in the view
            results = db.search("Hello", limit=10)

            self.assertGreater(len(results), 0)
            self.assertIn("guid", results[0])
            self.assertIn("text", results[0])

    def _create_messages_db_with_fts(self, path: Path, sender: str) -> None:
        """Create messages DB with FTS table for search() tests."""
        conn = sqlite3.connect(path)
        try:
            conn.executescript(
                """
                CREATE TABLE messages (
                    rowid INTEGER PRIMARY KEY,
                    guid TEXT NOT NULL,
                    sender TEXT NOT NULL,
                    text TEXT NOT NULL,
                    timestamp INTEGER NOT NULL,
                    chat_name TEXT,
                    chat_id TEXT,
                    is_from_me INTEGER DEFAULT 0,
                    is_group INTEGER DEFAULT 0
                );

                CREATE VIRTUAL TABLE messages_fts USING fts5(
                    text, chat_name, sender,
                    content='messages',
                    content_rowid='rowid'
                );

                CREATE TRIGGER messages_fts_insert AFTER INSERT ON messages BEGIN
                    INSERT INTO messages_fts(rowid, text, chat_name, sender)
                    VALUES (new.rowid, new.text, new.chat_name, new.sender);
                END;
                """
            )
            conn.execute(
                """INSERT INTO messages (guid, sender, text, timestamp, chat_name, chat_id)
                   VALUES (?, ?, ?, ?, ?, ?)""",
                ("guid-1", sender, "Hello from Mom", 12345, "Mom Chat", "chat-1"),
            )
            conn.commit()
        finally:
            conn.close()


if __name__ == "__main__":
    unittest.main()
