import sqlite3
import os
from typing import Optional
from loguru import logger

DB_PATH = os.path.join(os.path.dirname(os.path.dirname(__file__)), "monitor.db")
_MONITOR_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
_server_dir = os.environ.get("MONITOR_SERVER_DIR")
ACCESS_PATH = os.path.join(_server_dir if _server_dir else _MONITOR_DIR, "db.accdb")


def _get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    with _get_conn() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS scheda_prodotto (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                titolo TEXT,
                tipologia TEXT,
                anno TEXT,
                genere TEXT,
                paese TEXT,
                rete TEXT,
                cod_oss TEXT
            )
        """)


def sync_from_access() -> bool:
    try:
        import pyodbc
        conn_str = (
            r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
            rf"DBQ={ACCESS_PATH};"
        )
        acc = pyodbc.connect(conn_str)
        rows = acc.execute("SELECT TITOLO, TIPOLOGIA, ANNO, GENERE, PAESE, RETE, CodOss FROM SCHEDA_PRODOTTO").fetchall()
        acc.close()

        with _get_conn() as conn:
            conn.execute("DELETE FROM scheda_prodotto")
            conn.executemany(
                "INSERT INTO scheda_prodotto (titolo, tipologia, anno, genere, paese, rete, cod_oss) VALUES (?,?,?,?,?,?,?)",
                [(r[0], r[1], r[2], r[3], r[4], r[5], r[6]) for r in rows]
            )
        logger.info(f"Sync da Access completato: {len(rows)} record")
        return True
    except Exception as e:
        logger.warning(f"Sync da Access fallito: {e} — uso monitor.db esistente")
        return False


def search_scheda(query: str, contains: bool = False) -> list[dict]:
    pattern = f"%{query}%" if contains else f"{query}%"
    with _get_conn() as conn:
        rows = conn.execute(
            "SELECT titolo, tipologia, anno, genere, paese, rete, cod_oss FROM scheda_prodotto WHERE titolo LIKE ? ORDER BY titolo",
            (pattern,)
        ).fetchall()
    return [dict(r) for r in rows]


def find_by_title_exact(title: str) -> Optional[dict]:
    with _get_conn() as conn:
        row = conn.execute(
            "SELECT titolo, tipologia, anno, genere, paese, rete, cod_oss FROM scheda_prodotto WHERE UPPER(titolo) = UPPER(?)",
            (title,)
        ).fetchone()
    return dict(row) if row else None