"""SQLite schéma pro hotely, termíny letů a historii cen."""

from __future__ import annotations

import sqlite3
from pathlib import Path

ROOT = Path(__file__).resolve().parent
DATA_DIR = ROOT / "data"
DB_PATH = DATA_DIR / "vacation.db"

SCHEMA = """
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS scrapes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at TEXT NOT NULL,
    finished_at TEXT,
    hotels_ok INTEGER DEFAULT 0,
    flights_ok INTEGER DEFAULT 0,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS hotels (
    id INTEGER PRIMARY KEY,
    slug TEXT NOT NULL,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    official_stars REAL,
    destination TEXT,
    destination_id INTEGER,
    area TEXT,
    country TEXT,
    lat REAL,
    lng REAL,
    airport_name TEXT,
    airport_iata TEXT,
    airport_distance TEXT,
    airport_distance_km REAL,
    beach_distance TEXT,
    center_distance TEXT,
    distances_json TEXT,
    benefits_json TEXT,
    summary TEXT,
    meals TEXT,
    meals_type TEXT,
    conditions TEXT,
    image_url TEXT,
    images_json TEXT,
    website TEXT,
    giata INTEGER,
    data_source INTEGER,
    provider_id TEXT,
    extra_json TEXT,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS flights (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    destination_id INTEGER,
    destination TEXT,
    country TEXT,
    departure_airport TEXT,
    departure_airport_id INTEGER,
    departure_date TEXT,
    return_date TEXT,
    nights INTEGER,
    price_from REAL,
    currency TEXT DEFAULT 'CZK',
    search_filter TEXT,
    source_url TEXT,
    scrape_id INTEGER,
    scraped_at TEXT NOT NULL,
    UNIQUE (destination_id, departure_airport_id, departure_date, return_date, nights)
);

CREATE TABLE IF NOT EXISTS offers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hotel_id INTEGER NOT NULL,
    offer_key TEXT NOT NULL,
    departure_date TEXT,
    return_date TEXT,
    nights INTEGER,
    days INTEGER,
    departure_airport TEXT,
    arrival_airport TEXT,
    meal TEXT,
    room TEXT,
    price_total REAL,
    price_per_person REAL,
    currency TEXT DEFAULT 'CZK',
    scope TEXT DEFAULT 'hotel',
    booking_url TEXT,
    raw_json TEXT,
    scrape_id INTEGER,
    scraped_at TEXT NOT NULL,
    UNIQUE (hotel_id, offer_key)
);

CREATE TABLE IF NOT EXISTS price_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hotel_id INTEGER,
    offer_key TEXT NOT NULL,
    departure_date TEXT,
    return_date TEXT,
    nights INTEGER,
    departure_airport TEXT,
    meal TEXT,
    room TEXT,
    price REAL NOT NULL,
    currency TEXT DEFAULT 'CZK',
    scope TEXT,
    scrape_id INTEGER,
    scraped_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_history_key ON price_history(offer_key, scraped_at);
CREATE INDEX IF NOT EXISTS idx_history_hotel ON price_history(hotel_id, scraped_at);
CREATE INDEX IF NOT EXISTS idx_flights_dest ON flights(destination_id, departure_date);
"""


def connect(path: Path | None = None) -> sqlite3.Connection:
    DATA_DIR.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path or DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db(conn: sqlite3.Connection | None = None) -> sqlite3.Connection:
    own = conn is None
    conn = conn or connect()
    conn.executescript(SCHEMA)
    cols = {row[1] for row in conn.execute("PRAGMA table_info(offers)")}
    if "days" not in cols:
        conn.execute("ALTER TABLE offers ADD COLUMN days INTEGER")
    conn.commit()
    if own:
        return conn
    return conn