ciaovolo/flight-comparator/database/schema.sql

-- Flight Radar Web App - Database Schema
-- Version: 2.0
-- Date: 2026-02-23
-- Database: SQLite 3
--
-- This schema extends the existing cache.db with new tables for the web app.
-- Existing tables (flight_searches, flight_results) are preserved.

-- ============================================================================
-- CRITICAL: Enable Foreign Keys (SQLite default is OFF!)
-- ============================================================================
PRAGMA foreign_keys = ON;

-- ============================================================================
-- Table: scans
-- Purpose: Track flight scan requests and their status
-- ============================================================================
CREATE TABLE IF NOT EXISTS scans (
    -- Primary key with auto-increment
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Search parameters (validated by CHECK constraints)
    -- origin stores IATA code (forward scans) or ISO country code (reverse scans)
    origin TEXT NOT NULL CHECK(length(origin) >= 2),
    country TEXT NOT NULL CHECK(length(country) >= 2),
    scan_mode TEXT NOT NULL DEFAULT 'forward'
        CHECK(scan_mode IN ('forward', 'reverse')),
    start_date TEXT NOT NULL,  -- ISO 8601: YYYY-MM-DD
    end_date TEXT NOT NULL,

    -- Timestamps (auto-managed)
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,    -- Set when status transitions to 'running'
    completed_at TIMESTAMP,  -- Set when status transitions to 'completed' or 'failed'

    -- Scan status (enforced enum via CHECK)
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK(status IN ('pending', 'running', 'completed', 'failed', 'cancelled', 'paused')),

    -- Progress tracking
    total_routes INTEGER NOT NULL DEFAULT 0 CHECK(total_routes >= 0),
    routes_scanned INTEGER NOT NULL DEFAULT 0 CHECK(routes_scanned >= 0),
    total_flights INTEGER NOT NULL DEFAULT 0 CHECK(total_flights >= 0),

    -- Error information (NULL if no error)
    error_message TEXT,

    -- Additional search parameters
    seat_class TEXT DEFAULT 'economy',
    adults INTEGER DEFAULT 1 CHECK(adults > 0 AND adults <= 9),

    -- FK to scheduled_scans (NULL for manual scans)
    scheduled_scan_id INTEGER,

    -- Constraints across columns
    CHECK(end_date >= start_date),
    CHECK(routes_scanned <= total_routes OR total_routes = 0)
);

-- Performance indexes for scans table
CREATE INDEX IF NOT EXISTS idx_scans_origin_country
    ON scans(origin, country);

CREATE INDEX IF NOT EXISTS idx_scans_status
    ON scans(status)
    WHERE status IN ('pending', 'running');  -- Partial index for active scans

CREATE INDEX IF NOT EXISTS idx_scans_created_at
    ON scans(created_at DESC);  -- For recent scans query

CREATE INDEX IF NOT EXISTS idx_scans_scheduled_scan_id
    ON scans(scheduled_scan_id)
    WHERE scheduled_scan_id IS NOT NULL;

-- ============================================================================
-- Table: routes
-- Purpose: Store discovered routes with flight statistics
-- ============================================================================
CREATE TABLE IF NOT EXISTS routes (
    -- Primary key
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Foreign key to scans (cascade delete)
    scan_id INTEGER NOT NULL,

    -- Route airports
    -- For forward scans: origin_airport is NULL (implicit from scan.origin)
    -- For reverse scans: origin_airport is the variable origin IATA
    origin_airport TEXT,
    destination TEXT NOT NULL CHECK(length(destination) = 3),
    destination_name TEXT NOT NULL,
    destination_city TEXT,

    -- Flight statistics
    flight_count INTEGER NOT NULL DEFAULT 0 CHECK(flight_count >= 0),
    airlines TEXT NOT NULL,  -- JSON array: ["Ryanair", "Lufthansa"]

    -- Price statistics (NULL if no flights)
    min_price REAL CHECK(min_price >= 0),
    max_price REAL CHECK(max_price >= 0),
    avg_price REAL CHECK(avg_price >= 0),

    -- Timestamp
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Foreign key constraint with cascade delete
    FOREIGN KEY (scan_id)
        REFERENCES scans(id)
        ON DELETE CASCADE,

    -- Price consistency constraints
    CHECK(max_price >= min_price OR max_price IS NULL),
    CHECK(avg_price >= min_price OR avg_price IS NULL),
    CHECK(avg_price <= max_price OR avg_price IS NULL)
);

-- Performance indexes for routes table
CREATE INDEX IF NOT EXISTS idx_routes_scan_id
    ON routes(scan_id);

CREATE INDEX IF NOT EXISTS idx_routes_destination
    ON routes(destination);

CREATE INDEX IF NOT EXISTS idx_routes_min_price
    ON routes(min_price)
    WHERE min_price IS NOT NULL;  -- Partial index for routes with prices

-- One route row per (scan, origin_airport, destination) — supports both forward and reverse scans
CREATE UNIQUE INDEX IF NOT EXISTS uq_routes_scan_origin_dest
    ON routes(scan_id, COALESCE(origin_airport, ''), destination);

-- ============================================================================
-- Triggers: Auto-update timestamps and aggregates
-- ============================================================================

-- Trigger: Update scans.updated_at on any update
CREATE TRIGGER IF NOT EXISTS update_scans_timestamp
AFTER UPDATE ON scans
FOR EACH ROW
BEGIN
    UPDATE scans
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END;

-- Trigger: Update total_flights count when routes are inserted
CREATE TRIGGER IF NOT EXISTS update_scan_flight_count_insert
AFTER INSERT ON routes
FOR EACH ROW
BEGIN
    UPDATE scans
    SET total_flights = (
        SELECT COALESCE(SUM(flight_count), 0)
        FROM routes
        WHERE scan_id = NEW.scan_id
    )
    WHERE id = NEW.scan_id;
END;

-- Trigger: Update total_flights count when routes are updated
CREATE TRIGGER IF NOT EXISTS update_scan_flight_count_update
AFTER UPDATE OF flight_count ON routes
FOR EACH ROW
BEGIN
    UPDATE scans
    SET total_flights = (
        SELECT COALESCE(SUM(flight_count), 0)
        FROM routes
        WHERE scan_id = NEW.scan_id
    )
    WHERE id = NEW.scan_id;
END;

-- Trigger: Update total_flights count when routes are deleted
CREATE TRIGGER IF NOT EXISTS update_scan_flight_count_delete
AFTER DELETE ON routes
FOR EACH ROW
BEGIN
    UPDATE scans
    SET total_flights = (
        SELECT COALESCE(SUM(flight_count), 0)
        FROM routes
        WHERE scan_id = OLD.scan_id
    )
    WHERE id = OLD.scan_id;
END;

-- ============================================================================
-- Table: flights
-- Purpose: Store individual flights discovered per scan
-- ============================================================================
CREATE TABLE IF NOT EXISTS flights (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Foreign key to scans (cascade delete)
    scan_id INTEGER NOT NULL,

    -- Route
    -- origin_airport: NULL for forward scans, specific IATA for reverse scans
    origin_airport TEXT,
    destination TEXT NOT NULL CHECK(length(destination) = 3),
    date TEXT NOT NULL,  -- ISO 8601: YYYY-MM-DD

    -- Flight details
    airline TEXT,
    departure_time TEXT,   -- HH:MM
    arrival_time TEXT,     -- HH:MM
    price REAL CHECK(price >= 0),
    stops INTEGER NOT NULL DEFAULT 0,

    -- Timestamp
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (scan_id)
        REFERENCES scans(id)
        ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_flights_scan_id
    ON flights(scan_id);

CREATE INDEX IF NOT EXISTS idx_flights_scan_dest
    ON flights(scan_id, destination);

CREATE INDEX IF NOT EXISTS idx_flights_price
    ON flights(scan_id, price ASC)
    WHERE price IS NOT NULL;

-- ============================================================================
-- Views: Useful queries
-- ============================================================================

-- View: Recent scans with route counts
CREATE VIEW IF NOT EXISTS recent_scans AS
SELECT
    s.id,
    s.origin,
    s.country,
    s.status,
    s.created_at,
    s.total_routes,
    s.total_flights,
    COUNT(r.id) as routes_found,
    MIN(r.min_price) as cheapest_flight,
    s.error_message
FROM scans s
LEFT JOIN routes r ON r.scan_id = s.id
GROUP BY s.id
ORDER BY s.created_at DESC
LIMIT 10;

-- View: Active scans (pending or running)
CREATE VIEW IF NOT EXISTS active_scans AS
SELECT *
FROM scans
WHERE status IN ('pending', 'running')
ORDER BY created_at ASC;

-- ============================================================================
-- Initial Data: None (tables start empty)
-- ============================================================================

-- ============================================================================
-- Schema version tracking (for future migrations)
-- ============================================================================
CREATE TABLE IF NOT EXISTS schema_version (
    version INTEGER PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT
);

INSERT OR IGNORE INTO schema_version (version, description)
VALUES (1, 'Initial web app schema with scans and routes tables');

-- ============================================================================
-- Table: scheduled_scans
-- Purpose: Define recurring scan schedules (daily / weekly / monthly)
-- ============================================================================
CREATE TABLE IF NOT EXISTS scheduled_scans (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Scan parameters (same as scans table)
    origin          TEXT NOT NULL CHECK(length(origin) >= 2),
    country         TEXT NOT NULL CHECK(length(country) >= 2),
    scan_mode       TEXT NOT NULL DEFAULT 'forward'
                        CHECK(scan_mode IN ('forward', 'reverse')),
    window_months   INTEGER NOT NULL DEFAULT 1
                        CHECK(window_months >= 1 AND window_months <= 12),
    seat_class      TEXT NOT NULL DEFAULT 'economy',
    adults          INTEGER NOT NULL DEFAULT 1
                        CHECK(adults > 0 AND adults <= 9),

    -- Schedule definition
    frequency       TEXT NOT NULL
                        CHECK(frequency IN ('daily', 'weekly', 'monthly')),
    hour            INTEGER NOT NULL DEFAULT 6
                        CHECK(hour >= 0 AND hour <= 23),
    minute          INTEGER NOT NULL DEFAULT 0
                        CHECK(minute >= 0 AND minute <= 59),
    day_of_week     INTEGER CHECK(day_of_week >= 0 AND day_of_week <= 6),
    day_of_month    INTEGER CHECK(day_of_month >= 1 AND day_of_month <= 28),

    -- State
    enabled         INTEGER NOT NULL DEFAULT 1,
    label           TEXT,
    last_run_at     TIMESTAMP,
    next_run_at     TIMESTAMP NOT NULL,

    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Frequency-specific field requirements
    CHECK(
        (frequency = 'weekly'  AND day_of_week  IS NOT NULL) OR
        (frequency = 'monthly' AND day_of_month IS NOT NULL) OR
        (frequency = 'daily')
    )
);

-- Fast lookup of due schedules (partial index on enabled rows only)
CREATE INDEX IF NOT EXISTS idx_scheduled_scans_next_run
    ON scheduled_scans(next_run_at)
    WHERE enabled = 1;

-- Auto-update updated_at on every PATCH
CREATE TRIGGER IF NOT EXISTS update_scheduled_scans_timestamp
AFTER UPDATE ON scheduled_scans
FOR EACH ROW BEGIN
    UPDATE scheduled_scans SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

INSERT OR IGNORE INTO schema_version (version, description)
VALUES (2, 'Add scheduled_scans table');

-- ============================================================================
-- Verification Queries (for testing)
-- ============================================================================

-- Uncomment to verify schema creation:
-- SELECT name, type FROM sqlite_master WHERE type IN ('table', 'index', 'trigger', 'view') ORDER BY type, name;
-- PRAGMA foreign_keys;
-- PRAGMA table_info(scans);
-- PRAGMA table_info(routes);