import sqlite3
import time
from pathlib import Path

from app.core.config import Settings


def _connect(settings: Settings) -> sqlite3.Connection:
    db_path = Path(settings.event_store_path)
    db_path.parent.mkdir(parents=True, exist_ok=True)
    connection = sqlite3.connect(db_path)
    connection.execute(
        """
        CREATE TABLE IF NOT EXISTS processed_events (
            event_key TEXT PRIMARY KEY,
            created_at REAL NOT NULL
        )
        """
    )
    connection.execute(
        """
        CREATE TABLE IF NOT EXISTS conversation_state (
            conversation_id TEXT PRIMARY KEY,
            menu_sent INTEGER NOT NULL DEFAULT 0,
            last_option TEXT,
            updated_at REAL NOT NULL
        )
        """
    )
    connection.commit()
    return connection


def is_event_processed(settings: Settings, event_key: str) -> bool:
    with _connect(settings) as connection:
        row = connection.execute(
            "SELECT 1 FROM processed_events WHERE event_key = ?",
            (event_key,),
        ).fetchone()
        return row is not None


def mark_event_processed(settings: Settings, event_key: str) -> None:
    with _connect(settings) as connection:
        connection.execute(
            "INSERT OR IGNORE INTO processed_events (event_key, created_at) VALUES (?, ?)",
            (event_key, time.time()),
        )
        connection.commit()


def was_menu_sent(settings: Settings, conversation_id: str) -> bool:
    with _connect(settings) as connection:
        row = connection.execute(
            "SELECT menu_sent FROM conversation_state WHERE conversation_id = ?",
            (conversation_id,),
        ).fetchone()
        return bool(row and row[0])


def mark_menu_sent(settings: Settings, conversation_id: str) -> None:
    with _connect(settings) as connection:
        connection.execute(
            """
            INSERT INTO conversation_state (conversation_id, menu_sent, updated_at)
            VALUES (?, 1, ?)
            ON CONFLICT(conversation_id) DO UPDATE SET
                menu_sent = 1,
                updated_at = excluded.updated_at
            """,
            (conversation_id, time.time()),
        )
        connection.commit()


def set_last_option(settings: Settings, conversation_id: str, option_id: str) -> None:
    with _connect(settings) as connection:
        connection.execute(
            """
            INSERT INTO conversation_state (conversation_id, menu_sent, last_option, updated_at)
            VALUES (?, 1, ?, ?)
            ON CONFLICT(conversation_id) DO UPDATE SET
                last_option = excluded.last_option,
                updated_at = excluded.updated_at
            """,
            (conversation_id, option_id, time.time()),
        )
        connection.commit()