"""
Interacțiuni cu baza de date DuckDB.
"""
import duckdb
from rich.console import Console

console = Console()


def init_db(db_path: str = "../data/datahub.duckdb"):
    """
    Inițializează baza de date DuckDB cu extensiile necesare.
    """
    db = duckdb.connect(db_path)

    # Install and load spatial extension
    db.execute("INSTALL spatial;")
    db.execute("LOAD spatial;")

    console.print("[green]✓[/green] Extensia spatială instalată și încărcată")

    # Create catalog table if it doesn't exist
    db.execute("""
        CREATE TABLE IF NOT EXISTS catalog (
            id              VARCHAR PRIMARY KEY,
            name            VARCHAR NOT NULL,
            description     TEXT,
            source          VARCHAR,
            source_url      VARCHAR,
            license         VARCHAR,
            license_url     VARCHAR,
            acquisition     TEXT,
            geometry_levels VARCHAR[],
            temporal_type   VARCHAR,
            temporal_values VARCHAR[],
            join_key        VARCHAR NOT NULL,
            columns_json    TEXT,
            created_at      TIMESTAMP DEFAULT current_timestamp,
            updated_at      TIMESTAMP DEFAULT current_timestamp
        );
    """)

    console.print("[green]✓[/green] Tabel catalog creat/verificat")
    db.close()


def get_connection(db_path: str = "../data/datahub.duckdb") -> duckdb.DuckDBPyConnection:
    """Creează o conexiune la DuckDB."""
    conn = duckdb.connect(db_path)
    conn.execute("LOAD spatial;")
    return conn