"""Database connection and query handling for the shadow database."""

from __future__ import annotations

import json
import os
import sqlite3
import sys
import time
from datetime import datetime
from typing import Any, Callable

from .exceptions import SandboxError
from .sql_validator import SqlValidator


# Apple Core Foundation epoch: January 1, 2001 (in Unix seconds)
MAC_EPOCH_OFFSET = 978307200


def cf_to_datetime(timestamp: int) -> datetime:
    """Convert Apple Core Foundation timestamp to datetime.

    Apple timestamps are in nanoseconds since January 1, 2001.
    """
    # Convert nanoseconds to seconds and adjust for Mac epoch
    unix_seconds = (timestamp / 1e9) + MAC_EPOCH_OFFSET
    return datetime.fromtimestamp(unix_seconds)


def unix_seconds_to_cf_nanoseconds(unix_seconds: float) -> int:
    """Convert Unix seconds to Core Foundation nanoseconds since January 1, 2001."""
    return int((unix_seconds - MAC_EPOCH_OFFSET) * 1e9)


def _log_to_stderr(message: str, **metadata: Any) -> None:
    """Log structured JSON to stderr."""
    payload = {"message": message, **metadata}
    print(json.dumps(payload, ensure_ascii=True), file=sys.stderr, flush=True)


class MessageDatabase:
    """Database connection and query handling.

    Responsibilities:
    - Read-only database connection management
    - SQL query execution with validation
    - GUID tracking for benchmark mode
    - High-level message access helpers

    Dependencies are injectable for testing:
    - time_provider: Callable returning current Unix time (default: time.time)
    - quiet: If True, suppress logging to stderr (for testing)
    """

    def __init__(
        self,
        db_path: str,
        contacts_db_path: str | None = None,
        *,
        time_provider: Callable[[], float] | None = None,
        quiet: bool = False,
    ) -> None:
        self.db_path = db_path
        self.contacts_db_path = contacts_db_path
        self._conn: sqlite3.Connection | None = None
        self._retrieved_guids: list[str] = []
        self._contacts_attached = False
        self._quiet = quiet
        self._time_provider = time_provider or time.time

    def _log(self, message: str, **metadata: Any) -> None:
        """Log to stderr unless quiet mode is enabled."""
        if not self._quiet:
            _log_to_stderr(message, **metadata)

    def _get_connection(self) -> sqlite3.Connection:
        """Get read-only database connection."""
        if self._conn is None:
            self._conn = sqlite3.connect(
                f"file:{self.db_path}?mode=ro",
                uri=True,
                check_same_thread=False,
            )
            self._conn.row_factory = sqlite3.Row
            self._attach_contacts_db(self._conn)
        return self._conn

    def _attach_contacts_db(self, conn: sqlite3.Connection) -> None:
        if self._contacts_attached:
            return
        if not self.contacts_db_path or not os.path.exists(self.contacts_db_path):
            self._log(
                "contacts_attach_skipped",
                reason="path_missing",
                contacts_db_path=self.contacts_db_path,
            )
            self._attach_contacts_placeholder(conn)
            return

        uri = f"file:{self.contacts_db_path}?mode=ro"
        try:
            conn.execute("ATTACH DATABASE ? AS contacts", (uri,))
            if not self._contacts_schema_ready(conn):
                self._log(
                    "contacts_attach_failed",
                    reason="schema_not_ready",
                    contacts_db_path=self.contacts_db_path,
                )
                self._detach_contacts(conn)
                self._attach_contacts_placeholder(conn)
                return
            self._create_contacts_view(conn)
            self._contacts_attached = True
        except sqlite3.Error as exc:
            self._log(
                "contacts_attach_failed",
                reason="sqlite_error",
                error=str(exc),
                contacts_db_path=self.contacts_db_path,
            )
            self._contacts_attached = False
            self._attach_contacts_placeholder(conn)

    def _contacts_schema_ready(self, conn: sqlite3.Connection) -> bool:
        try:
            cursor = conn.execute(
                """
                SELECT name FROM contacts.sqlite_master
                WHERE type IN ('table', 'view')
                  AND name IN ('contacts', 'contact_identifiers')
                """
            )
            names = {row[0] for row in cursor.fetchall()}
            return {'contacts', 'contact_identifiers'}.issubset(names)
        except sqlite3.Error as exc:
            self._log(
                "contacts_schema_check_failed",
                error=str(exc),
                contacts_db_path=self.contacts_db_path,
            )
            return False

    def _detach_contacts(self, conn: sqlite3.Connection) -> None:
        try:
            conn.execute("DETACH DATABASE contacts")
        except sqlite3.Error as exc:
            self._log(
                "contacts_detach_failed",
                error=str(exc),
                contacts_db_path=self.contacts_db_path,
            )

    def _attach_contacts_placeholder(self, conn: sqlite3.Connection) -> None:
        try:
            conn.execute("ATTACH DATABASE ':memory:' AS contacts")
            conn.executescript("""
                CREATE TABLE IF NOT EXISTS contacts.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 IF NOT EXISTS contacts.contact_identifiers (
                    contact_id INTEGER,
                    type TEXT NOT NULL,
                    value TEXT NOT NULL,
                    PRIMARY KEY (type, value)
                );
                CREATE VIRTUAL TABLE IF NOT EXISTS contacts.contacts_fts USING fts5(
                    display_name, organization, notes
                );
            """)
            self._create_contacts_view(conn)
            self._contacts_attached = True
        except sqlite3.Error as exc:
            self._log(
                "contacts_placeholder_failed",
                error=str(exc),
            )
            self._contacts_attached = False

    def _create_contacts_view(self, conn: sqlite3.Connection) -> None:
        conn.execute("""
            CREATE TEMP VIEW IF NOT EXISTS messages_with_contacts AS
            SELECT
                m.rowid AS rowid,
                m.*,
                COALESCE(c.display_name, m.sender) AS sender_name
            FROM messages m
            LEFT JOIN contacts.contact_identifiers ci ON m.sender = ci.value
            LEFT JOIN contacts.contacts c ON ci.contact_id = c.id
        """)

    def _track_guids(self, results: list[dict[str, Any]], columns: list[str]) -> None:
        """Track GUIDs from results for benchmark mode."""
        guid_key = next((k for k in columns if k.lower() == 'guid'), None)
        if guid_key:
            for row in results:
                guid = row.get(guid_key)
                if guid and guid not in self._retrieved_guids:
                    self._retrieved_guids.append(guid)

    def get_retrieved_guids(self) -> list[str]:
        """Return the list of GUIDs retrieved during query execution."""
        return self._retrieved_guids.copy()

    def query(self, sql: str) -> list[dict[str, Any]]:
        """Execute a read-only SQL query and return results as list of dicts."""
        sql = SqlValidator.strip_trailing_semicolons(sql)
        SqlValidator.validate_select_only(sql)

        modified_sql = SqlValidator.inject_guid_if_needed(sql)
        conn = self._get_connection()
        cursor = conn.execute(modified_sql)
        columns = [desc[0] for desc in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        self._track_guids(results, columns)
        return results

    def search(
        self,
        terms: str,
        limit: int = 1000,
        sender: str | None = None,
        chat: str | None = None,
        days: int | None = None,
        table: str = "messages",
    ) -> list[dict[str, Any]]:
        """Search messages or events using FTS with optional filters."""
        conn = self._get_connection()
        normalized_table = table.strip().lower()
        if normalized_table not in {"messages", "events"}:
            raise SandboxError("search() table must be 'messages' or 'events'")

        if normalized_table == "messages":
            conditions = ["messages_fts MATCH ?"]
            params: list[Any] = [terms]

            if sender:
                conditions.append("m.sender LIKE ?")
                params.append(f"%{sender}%")

            if chat:
                conditions.append("m.chat_name LIKE ?")
                params.append(f"%{chat}%")

            if days is not None:
                cutoff = unix_seconds_to_cf_nanoseconds(self._time_provider() - (days * 86400))
                conditions.append("m.timestamp >= ?")
                params.append(cutoff)

            params.append(limit)

            # Use messages_with_contacts view if contacts are attached to include sender_name
            # Keep sender as raw handle for lookups, add sender_name for display
            if self._contacts_attached:
                sql = f"""
                    SELECT m.guid, m.text, m.sender, m.sender_name, m.chat_name, m.chat_id,
                           m.timestamp, m.is_from_me, m.is_group
                    FROM messages_fts
                    JOIN messages_with_contacts m ON messages_fts.rowid = m.rowid
                    WHERE {' AND '.join(conditions)}
                    ORDER BY m.timestamp DESC
                    LIMIT ?
                """
            else:
                sql = f"""
                    SELECT m.guid, m.text, m.sender, m.chat_name, m.chat_id,
                           m.timestamp, m.is_from_me, m.is_group
                    FROM messages_fts
                    JOIN messages m ON messages_fts.rowid = m.rowid
                    WHERE {' AND '.join(conditions)}
                    ORDER BY m.timestamp DESC
                    LIMIT ?
                """
        else:
            conditions = ["events_fts MATCH ?"]
            params = [terms]

            if days is not None:
                cutoff = int(self._time_provider() - (days * 86400))
                conditions.append("e.start_date >= ?")
                params.append(cutoff)

            params.append(limit)
            sql = f"""
                SELECT e.id, e.title, e.start_date, e.end_date, e.location, e.notes,
                       e.calendar_name, e.is_all_day, e.participants, e.recurrence_rule
                FROM events_fts
                JOIN events e ON events_fts.rowid = e.rowid
                WHERE {' AND '.join(conditions)}
                ORDER BY e.start_date DESC
                LIMIT ?
            """

        cursor = conn.execute(sql, params)
        columns = [desc[0] for desc in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        self._track_guids(results, columns)
        return results

    def get_messages(self, limit: int | None = 10000) -> list[dict[str, Any]]:
        """Get messages in bulk for exploration and processing."""
        conn = self._get_connection()

        # Use messages_with_contacts view if contacts are attached to include sender_name
        # Keep sender as raw handle for lookups, add sender_name for display
        if self._contacts_attached:
            base_sql = """
                SELECT guid, text, sender, sender_name, chat_name, chat_id,
                       timestamp, is_from_me, is_group
                FROM messages_with_contacts
            """
        else:
            base_sql = """
                SELECT guid, text, sender, chat_name, chat_id,
                       timestamp, is_from_me, is_group
                FROM messages
            """

        if limit is None:
            cursor = conn.execute(f"""
                {base_sql}
                ORDER BY timestamp DESC
            """)
        else:
            cursor = conn.execute(f"""
                {base_sql}
                ORDER BY timestamp DESC
                LIMIT ?
            """, (limit,))

        columns = [desc[0] for desc in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        self._track_guids(results, columns)
        return results

    def get_stats(self) -> dict[str, Any]:
        """Get high-level statistics about the message database."""
        conn = self._get_connection()

        cursor = conn.execute("SELECT COUNT(*) FROM messages")
        total_messages = cursor.fetchone()[0]

        cursor = conn.execute("SELECT COUNT(DISTINCT sender) FROM messages")
        unique_senders = cursor.fetchone()[0]

        cursor = conn.execute("SELECT COUNT(DISTINCT chat_id) FROM messages")
        unique_chats = cursor.fetchone()[0]

        cursor = conn.execute("SELECT MIN(timestamp), MAX(timestamp) FROM messages")
        row = cursor.fetchone()
        oldest_timestamp = row[0]
        newest_timestamp = row[1]

        cursor = conn.execute("""
            SELECT sender, COUNT(*) as count
            FROM messages
            GROUP BY sender
            ORDER BY count DESC
            LIMIT 10
        """)
        top_senders = [{'sender': r[0], 'count': r[1]} for r in cursor.fetchall()]

        cursor = conn.execute("""
            SELECT chat_name, chat_id, COUNT(*) as count
            FROM messages
            GROUP BY chat_id
            ORDER BY count DESC
            LIMIT 10
        """)
        top_chats = [
            {'chat_name': r[0] or r[1], 'chat_id': r[1], 'count': r[2]}
            for r in cursor.fetchall()
        ]

        stats: dict[str, Any] = {
            'total_messages': total_messages,
            'unique_senders': unique_senders,
            'unique_chats': unique_chats,
            'oldest_timestamp': oldest_timestamp,
            'newest_timestamp': newest_timestamp,
            'top_senders': top_senders,
            'top_chats': top_chats,
        }
        try:
            cursor = conn.execute("SELECT COUNT(*) FROM contacts.contacts")
            stats['contacts_count'] = cursor.fetchone()[0]
        except sqlite3.Error as exc:
            self._log(
                "contacts_count_query_failed",
                error=str(exc),
            )

        return stats

    def get_chats(self) -> list[dict[str, Any]]:
        """Get list of all chats with metadata."""
        conn = self._get_connection()

        cursor = conn.execute("""
            SELECT
                chat_id,
                chat_name,
                COUNT(*) as message_count,
                MAX(timestamp) as last_message,
                MAX(is_group) as is_group
            FROM messages
            GROUP BY chat_id
            ORDER BY last_message DESC
        """)

        return [
            {
                'chat_id': r[0],
                'chat_name': r[1] or r[0],
                'message_count': r[2],
                'last_message_timestamp': r[3],
                'is_group': bool(r[4]),
            }
            for r in cursor.fetchall()
        ]

    def get_recent(self, days: int = 30, limit: int = 10000) -> list[dict[str, Any]]:
        """Get recent messages within a time window."""
        cutoff = unix_seconds_to_cf_nanoseconds(self._time_provider() - (days * 86400))

        conn = self._get_connection()

        # Use messages_with_contacts view if contacts are attached to include sender_name
        # Keep sender as raw handle for lookups, add sender_name for display
        if self._contacts_attached:
            cursor = conn.execute("""
                SELECT guid, text, sender, sender_name, chat_name, chat_id,
                       timestamp, is_from_me, is_group
                FROM messages_with_contacts
                WHERE timestamp >= ?
                ORDER BY timestamp DESC
                LIMIT ?
            """, (cutoff, limit))
        else:
            cursor = conn.execute("""
                SELECT guid, text, sender, chat_name, chat_id,
                       timestamp, is_from_me, is_group
                FROM messages
                WHERE timestamp >= ?
                ORDER BY timestamp DESC
                LIMIT ?
            """, (cutoff, limit))

        columns = [desc[0] for desc in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        self._track_guids(results, columns)
        return results

    def sample(self, n: int = 1000) -> list[dict[str, Any]]:
        """Get a random sample of messages."""
        conn = self._get_connection()

        cursor = conn.execute("""
            SELECT guid, text, sender, chat_name, chat_id,
                   timestamp, is_from_me, is_group
            FROM messages
            ORDER BY RANDOM()
            LIMIT ?
        """, (n,))

        columns = [desc[0] for desc in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        self._track_guids(results, columns)
        return results

    def get_chat_messages(self, chat_id: str, limit: int = 1000) -> list[dict[str, Any]]:
        """Get messages from a specific chat."""
        conn = self._get_connection()

        cursor = conn.execute("""
            SELECT guid, text, sender, chat_name, chat_id,
                   timestamp, is_from_me, is_group
            FROM messages
            WHERE chat_id = ?
            ORDER BY timestamp DESC
            LIMIT ?
        """, (chat_id, limit))

        columns = [desc[0] for desc in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        self._track_guids(results, columns)
        return results

    def close(self) -> None:
        """Close the database connection."""
        if self._conn:
            self._conn.close()
            self._conn = None
