-- Repair the operating_* tables: 0008 referenced a non-existent `profiles`
-- table (the real table is `station_profiles`), so the FK validation
-- failed on every insert with "no such table: main.profiles". Dropping
-- and recreating is safe here because no operating data could have been
-- inserted (every attempt errored out).

DROP TABLE IF EXISTS operating_antenna_bands;
DROP TABLE IF EXISTS operating_antennas;
DROP TABLE IF EXISTS operating_stations;

CREATE TABLE operating_stations (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    profile_id   INTEGER NOT NULL,
    name         TEXT NOT NULL,
    adif_rig     TEXT NOT NULL DEFAULT '',
    sort_order   INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    updated_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    FOREIGN KEY (profile_id) REFERENCES station_profiles(id) ON DELETE CASCADE
);
CREATE INDEX idx_operating_stations_profile ON operating_stations(profile_id, sort_order);

CREATE TABLE operating_antennas (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    station_id   INTEGER NOT NULL,
    name         TEXT NOT NULL,
    adif_ant     TEXT NOT NULL DEFAULT '',
    sort_order   INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    updated_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    FOREIGN KEY (station_id) REFERENCES operating_stations(id) ON DELETE CASCADE
);
CREATE INDEX idx_operating_antennas_station ON operating_antennas(station_id, sort_order);

CREATE TABLE operating_antenna_bands (
    antenna_id   INTEGER NOT NULL,
    band         TEXT NOT NULL,
    is_default   INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (antenna_id, band),
    FOREIGN KEY (antenna_id) REFERENCES operating_antennas(id) ON DELETE CASCADE
);
CREATE INDEX idx_operating_bands_band ON operating_antenna_bands(band, is_default);