"""
Camada de persistência SQLite para o serviço PX3Lab Follow-up.
"""
import sqlite3
import threading
from contextlib import contextmanager
from datetime import datetime
from typing import Optional, List, Dict, Any

import config

_lock = threading.Lock()


# ---------------------------------------------------------------------------
# Conexão
# ---------------------------------------------------------------------------
@contextmanager
def get_conn():
    """Contexto seguro para uso em multi-thread (SQLite requer serialização)."""
    with _lock:
        conn = sqlite3.connect(config.DB_PATH, timeout=30.0)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA foreign_keys=ON;")
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()


# ---------------------------------------------------------------------------
# Schema
# ---------------------------------------------------------------------------
SCHEMA = """
CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contact_id TEXT NOT NULL,
    contact_name TEXT,
    contact_phone TEXT,
    assigned_user_id TEXT,
    tag TEXT NOT NULL,
    tag_added_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(contact_id, tag)
);

CREATE INDEX IF NOT EXISTS idx_events_tag_added_at
    ON events(tag_added_at);

CREATE TABLE IF NOT EXISTS notifications_sent (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id INTEGER REFERENCES events(id) ON DELETE CASCADE,
    delay_hours INTEGER NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(event_id, delay_hours)
);
"""


def init_db():
    with get_conn() as conn:
        conn.executescript(SCHEMA)


# ---------------------------------------------------------------------------
# Repositório
# ---------------------------------------------------------------------------
def insert_event(
    contact_id: str,
    contact_name: Optional[str],
    contact_phone: Optional[str],
    assigned_user_id: Optional[str],
    tag: str,
    tag_added_at: datetime,
) -> Optional[int]:
    """
    Insere um novo evento (tag adicionada). Se já existe (contact_id + tag),
    retorna None e ignora - isso evita reiniciar a contagem de follow-up caso
    o Linkia emita eventos repetidos.
    """
    with get_conn() as conn:
        cur = conn.execute(
            """
            INSERT OR IGNORE INTO events
                (contact_id, contact_name, contact_phone, assigned_user_id, tag, tag_added_at)
            VALUES (?, ?, ?, ?, ?, ?)
            """,
            (
                contact_id,
                contact_name,
                contact_phone,
                assigned_user_id,
                tag,
                tag_added_at.isoformat(),
            ),
        )
        return cur.lastrowid if cur.rowcount else None


def list_events_for_tag(tag: str) -> List[Dict[str, Any]]:
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM events WHERE tag = ?",
            (tag,),
        ).fetchall()
        return [dict(r) for r in rows]


def list_all_events() -> List[Dict[str, Any]]:
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM events").fetchall()
        return [dict(r) for r in rows]


def notification_already_sent(event_id: int, delay_hours: int) -> bool:
    with get_conn() as conn:
        row = conn.execute(
            "SELECT 1 FROM notifications_sent WHERE event_id=? AND delay_hours=?",
            (event_id, delay_hours),
        ).fetchone()
        return row is not None


def mark_notification_sent(event_id: int, delay_hours: int) -> bool:
    """Retorna True se marcou (não existia). False se já existia (idempotente)."""
    with get_conn() as conn:
        cur = conn.execute(
            """
            INSERT OR IGNORE INTO notifications_sent (event_id, delay_hours)
            VALUES (?, ?)
            """,
            (event_id, delay_hours),
        )
        return cur.rowcount > 0