PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    json TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
    UNIQUE(did, rkey)
);
CREATE TABLE IF NOT EXISTS weather_checks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id INTEGER NOT NULL,
    json TEXT NOT NULL,
    checked_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS weather_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hash TEXT NOT NULL UNIQUE,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    data BLOB NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE TABLE IF NOT EXISTS geo_cache (
    query TEXT PRIMARY KEY,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE TABLE IF NOT EXISTS locations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    search_name TEXT NOT NULL CHECK(length(search_name) > 0),
    region TEXT NOT NULL,
    country TEXT NOT NULL,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    population INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS follows (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    subject_uri TEXT NOT NULL,
    subject_did TEXT NOT NULL,
    subject_rkey TEXT NOT NULL,
    subject_collection TEXT NOT NULL,
    subject_when TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
    UNIQUE(did, rkey)
);

CREATE INDEX IF NOT EXISTS idx_weather_cache_hash ON weather_cache(hash);
CREATE INDEX IF NOT EXISTS idx_weather_checks_event ON weather_checks(event_id, checked_at DESC);
CREATE INDEX IF NOT EXISTS idx_locations_search_name ON locations(search_name);
CREATE INDEX IF NOT EXISTS idx_follows_did ON follows(did);
CREATE INDEX IF NOT EXISTS idx_follows_subject ON follows(subject_did, subject_rkey);