import sqlite3
import os
from typing import List, Dict, Optional, Any

DB_PATH = os.getenv("DB_PATH", os.path.join(os.path.dirname(__file__), "data", "facturas_index.db"))

def get_connection() -> sqlite3.Connection:
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=30.0, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    try:
        conn.execute("PRAGMA busy_timeout = 30000;")
    except Exception:
        pass
    return conn

def init_db():
    try:
        with get_connection() as conn:
            try:
                conn.execute("PRAGMA journal_mode = WAL;")
            except Exception:
                pass
            try:
                conn.execute("PRAGMA synchronous = NORMAL;")
            except Exception:
                pass

            conn.execute("""
            CREATE TABLE IF NOT EXISTS invoices_index (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                customer_id TEXT NOT NULL,
                document_date TEXT NOT NULL,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                document_type TEXT DEFAULT 'Facturas',
                cableoperador TEXT,
                agencia TEXT,
                file_name TEXT NOT NULL,
                file_relative_path TEXT UNIQUE NOT NULL,
                file_full_path TEXT NOT NULL,
                file_size INTEGER,
                file_mtime REAL,
                indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
            """)
            conn.execute("CREATE INDEX IF NOT EXISTS idx_customer_date ON invoices_index(customer_id, document_date DESC);")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_customer_ym ON invoices_index(customer_id, year, month);")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_customer_type_date ON invoices_index(customer_id, document_type, document_date DESC);")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_doc_type ON invoices_index(document_type);")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_rel_path ON invoices_index(file_relative_path);")

            conn.execute("""
            CREATE TABLE IF NOT EXISTS indexer_meta (
                key TEXT PRIMARY KEY,
                value TEXT,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            );
            """)
            conn.commit()
    except Exception as e:
        print(f"[DB] init_db warning/error: {e}")

def clear_db():
    """Limpia todos los registros de la base de datos sin borrar las tablas"""
    init_db()
    with get_connection() as conn:
        conn.execute("DELETE FROM invoices_index;")
        conn.execute("DELETE FROM indexer_meta;")
        try:
            conn.execute("DELETE FROM sqlite_sequence WHERE name IN ('invoices_index');")
        except sqlite3.OperationalError:
            pass
        conn.commit()

    # VACUUM debe ejecutarse fuera de transacciones activas
    try:
        raw_conn = sqlite3.connect(DB_PATH, isolation_level=None)
        raw_conn.execute("VACUUM;")
        raw_conn.close()
    except Exception:
        pass

def reset_db_schema():
    """Elimina las tablas y las recrea desde cero"""
    with get_connection() as conn:
        conn.execute("DROP TABLE IF EXISTS invoices_index;")
        conn.execute("DROP TABLE IF EXISTS indexer_meta;")
        conn.commit()
    init_db()

def get_indexed_mtimes() -> Dict[str, float]:
    """Retorna un diccionario de {file_relative_path: file_mtime} para verificar cambios rápidamente"""
    init_db()
    with get_connection() as conn:
        cursor = conn.execute("SELECT file_relative_path, file_mtime FROM invoices_index")
        return {row["file_relative_path"]: float(row["file_mtime"]) for row in cursor.fetchall()}

def upsert_invoices_batch(records: List[Dict[str, Any]]) -> int:
    """Inserta o actualiza un lote de documentos indexados"""
    if not records:
        return 0
    init_db()
    sql = """
    INSERT INTO invoices_index (
        customer_id, document_date, year, month, document_type,
        cableoperador, agencia, file_name, file_relative_path,
        file_full_path, file_size, file_mtime, indexed_at
    ) VALUES (
        :customer_id, :document_date, :year, :month, :document_type,
        :cableoperador, :agencia, :file_name, :file_relative_path,
        :file_full_path, :file_size, :file_mtime, CURRENT_TIMESTAMP
    )
    ON CONFLICT(file_relative_path) DO UPDATE SET
        customer_id = excluded.customer_id,
        document_date = excluded.document_date,
        year = excluded.year,
        month = excluded.month,
        document_type = excluded.document_type,
        cableoperador = excluded.cableoperador,
        agencia = excluded.agencia,
        file_name = excluded.file_name,
        file_full_path = excluded.file_full_path,
        file_size = excluded.file_size,
        file_mtime = excluded.file_mtime,
        indexed_at = CURRENT_TIMESTAMP;
    """
    with get_connection() as conn:
        conn.executemany(sql, records)
        conn.commit()
    return len(records)

def get_invoices_by_customer(
    customer_id: str,
    year: Optional[int] = None,
    month: Optional[int] = None,
    document_type: Optional[str] = None
) -> List[Dict[str, Any]]:
    """Consulta los documentos indexados de un cliente específico con filtros opcionales de fecha y tipo documental"""
    init_db()
    query = "SELECT * FROM invoices_index WHERE customer_id = ?"
    params: List[Any] = [str(customer_id).strip()]

    if year is not None and int(year) > 0:
        query += " AND year = ?"
        params.append(int(year))

    if month is not None and int(month) > 0:
        query += " AND month = ?"
        params.append(int(month))

    if document_type and str(document_type).strip().lower() not in ("", "todos", "all"):
        query += " AND LOWER(document_type) = LOWER(?)"
        params.append(str(document_type).strip())

    query += " ORDER BY document_date DESC, id DESC"

    with get_connection() as conn:
        cursor = conn.execute(query, params)
        return [dict(row) for row in cursor.fetchall()]

def get_customer_years(customer_id: str, document_type: Optional[str] = None) -> List[int]:
    """Retorna los años en los que el suscriptor tiene documentos registrados"""
    init_db()
    query = "SELECT DISTINCT year FROM invoices_index WHERE customer_id = ?"
    params: List[Any] = [str(customer_id).strip()]

    if document_type and str(document_type).strip().lower() not in ("", "todos", "all"):
        query += " AND LOWER(document_type) = LOWER(?)"
        params.append(str(document_type).strip())

    query += " ORDER BY year DESC"

    with get_connection() as conn:
        cursor = conn.execute(query, params)
        return [row["year"] for row in cursor.fetchall()]

def get_customer_document_types(customer_id: str) -> List[str]:
    """Retorna los tipos documentales disponibles para un suscriptor específico"""
    init_db()
    with get_connection() as conn:
        cursor = conn.execute(
            "SELECT DISTINCT document_type FROM invoices_index WHERE customer_id = ? ORDER BY document_type ASC",
            [str(customer_id).strip()]
        )
        return [row["document_type"] for row in cursor.fetchall() if row["document_type"]]

def get_all_document_types() -> List[str]:
    """Retorna todos los tipos documentales registrados globalmente en el sistema"""
    init_db()
    with get_connection() as conn:
        cursor = conn.execute(
            "SELECT DISTINCT document_type FROM invoices_index WHERE document_type IS NOT NULL AND document_type != '' ORDER BY document_type ASC"
        )
        return [row["document_type"] for row in cursor.fetchall()]

def set_meta(key: str, value: str):
    init_db()
    with get_connection() as conn:
        conn.execute(
            "INSERT INTO indexer_meta (key, value, updated_at) VALUES (?, ?, CURRENT_TIMESTAMP) "
            "ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = CURRENT_TIMESTAMP",
            (key, str(value))
        )
        conn.commit()

def get_meta(key: str, default: str = "") -> str:
    init_db()
    with get_connection() as conn:
        cursor = conn.execute("SELECT value FROM indexer_meta WHERE key = ?", (key,))
        row = cursor.fetchone()
        return row["value"] if row else default

def get_stats() -> Dict[str, Any]:
    init_db()
    with get_connection() as conn:
        total = conn.execute("SELECT COUNT(*) as count FROM invoices_index").fetchone()["count"]
        unique_customers = conn.execute("SELECT COUNT(DISTINCT customer_id) as count FROM invoices_index").fetchone()["count"]
        last_scan = get_meta("last_scan_time", "Nunca")
        last_count = get_meta("last_scan_count", "0")
        total_dir_size = get_meta("total_dir_size_bytes", "0")

        # Conteo agrupado por tipo documental
        cursor_types = conn.execute("SELECT document_type, COUNT(*) as count FROM invoices_index GROUP BY document_type ORDER BY count DESC")
        by_type = {row["document_type"]: row["count"] for row in cursor_types.fetchall() if row["document_type"]}

        # Conteo agrupado por empresa / cableoperador
        cursor_companies = conn.execute("SELECT cableoperador, COUNT(*) as count FROM invoices_index WHERE cableoperador IS NOT NULL AND cableoperador != '' GROUP BY cableoperador ORDER BY count DESC")
        by_company = {row["cableoperador"]: row["count"] for row in cursor_companies.fetchall()}

        return {
            "total_invoices": total,
            "unique_customers": unique_customers,
            "by_type": by_type,
            "by_company": by_company,
            "last_scan_time": last_scan,
            "last_scan_added": last_count,
            "total_dir_size_bytes": total_dir_size
        }