-- Expand the QSO table to cover the rest of the common ADIF fields.
-- SQLite ALTER TABLE ADD COLUMN is metadata-only, so this is fast even on
-- large logbooks. Anything not promoted to a column lives in extras_json.

-- --- Times / frequencies / mode ---
ALTER TABLE qso ADD COLUMN qso_date_off TEXT;        -- ISO UTC end datetime
ALTER TABLE qso ADD COLUMN freq_rx_hz   INTEGER;     -- RX frequency for split operation
ALTER TABLE qso ADD COLUMN band_rx      TEXT;
ALTER TABLE qso ADD COLUMN submode      TEXT;        -- USB, LSB, USB-DATA, ...

-- --- Contacted station extras ---
ALTER TABLE qso ADD COLUMN state              TEXT; -- US state, JA prefecture, etc.
ALTER TABLE qso ADD COLUMN cnty               TEXT;
ALTER TABLE qso ADD COLUMN address            TEXT;
ALTER TABLE qso ADD COLUMN email              TEXT;
ALTER TABLE qso ADD COLUMN web                TEXT;
ALTER TABLE qso ADD COLUMN age                INTEGER;
ALTER TABLE qso ADD COLUMN lat                REAL;
ALTER TABLE qso ADD COLUMN lon                REAL;
ALTER TABLE qso ADD COLUMN gridsquare_ext     TEXT; -- 8/10-char extension
ALTER TABLE qso ADD COLUMN vucc_grids         TEXT;
ALTER TABLE qso ADD COLUMN rig                TEXT; -- contacted station's rig
ALTER TABLE qso ADD COLUMN ant                TEXT; -- contacted station's antenna

-- --- QSL bureau / direct / LoTW / eQSL / Clublog / HRDLog ---
ALTER TABLE qso ADD COLUMN qsl_via                       TEXT;
ALTER TABLE qso ADD COLUMN qsl_msg                       TEXT;
ALTER TABLE qso ADD COLUMN qslmsg_rcvd                   TEXT;
ALTER TABLE qso ADD COLUMN qsl_sent_date                 TEXT;
ALTER TABLE qso ADD COLUMN qsl_rcvd_date                 TEXT;
ALTER TABLE qso ADD COLUMN lotw_sent_date                TEXT;
ALTER TABLE qso ADD COLUMN lotw_rcvd_date                TEXT;
ALTER TABLE qso ADD COLUMN eqsl_sent_date                TEXT;
ALTER TABLE qso ADD COLUMN eqsl_rcvd_date                TEXT;
ALTER TABLE qso ADD COLUMN clublog_qso_upload_date       TEXT;
ALTER TABLE qso ADD COLUMN clublog_qso_upload_status     TEXT;
ALTER TABLE qso ADD COLUMN hrdlog_qso_upload_date        TEXT;
ALTER TABLE qso ADD COLUMN hrdlog_qso_upload_status      TEXT;

-- --- Contest ---
ALTER TABLE qso ADD COLUMN contest_id TEXT;
ALTER TABLE qso ADD COLUMN srx        INTEGER;
ALTER TABLE qso ADD COLUMN stx        INTEGER;
ALTER TABLE qso ADD COLUMN srx_string TEXT;
ALTER TABLE qso ADD COLUMN stx_string TEXT;
ALTER TABLE qso ADD COLUMN check_field TEXT;  -- ADIF CHECK (reserved word in SQL)
ALTER TABLE qso ADD COLUMN precedence TEXT;
ALTER TABLE qso ADD COLUMN arrl_sect  TEXT;

-- --- Satellite / propagation ---
ALTER TABLE qso ADD COLUMN prop_mode TEXT;
ALTER TABLE qso ADD COLUMN sat_name  TEXT;
ALTER TABLE qso ADD COLUMN sat_mode  TEXT;
ALTER TABLE qso ADD COLUMN ant_az    REAL;
ALTER TABLE qso ADD COLUMN ant_el    REAL;
ALTER TABLE qso ADD COLUMN ant_path  TEXT;

-- --- My station extras (per-QSO overrides of the active profile) ---
ALTER TABLE qso ADD COLUMN my_state          TEXT;
ALTER TABLE qso ADD COLUMN my_cnty           TEXT;
ALTER TABLE qso ADD COLUMN my_iota           TEXT;
ALTER TABLE qso ADD COLUMN my_dxcc           INTEGER;
ALTER TABLE qso ADD COLUMN my_cq_zone        INTEGER;
ALTER TABLE qso ADD COLUMN my_itu_zone       INTEGER;
ALTER TABLE qso ADD COLUMN my_lat            REAL;
ALTER TABLE qso ADD COLUMN my_lon            REAL;
ALTER TABLE qso ADD COLUMN my_street         TEXT;
ALTER TABLE qso ADD COLUMN my_city           TEXT;
ALTER TABLE qso ADD COLUMN my_postal_code    TEXT;
ALTER TABLE qso ADD COLUMN my_rig            TEXT;
ALTER TABLE qso ADD COLUMN my_antenna        TEXT;
ALTER TABLE qso ADD COLUMN my_gridsquare_ext TEXT;

-- --- Catch-all for ADIF fields we don't promote to columns ---
-- JSON object: { "FIELD_NAME": "value", ... } (keys uppercase as in ADIF).
ALTER TABLE qso ADD COLUMN extras_json TEXT;

CREATE INDEX IF NOT EXISTS idx_qso_state      ON qso(state);
CREATE INDEX IF NOT EXISTS idx_qso_contest_id ON qso(contest_id);
CREATE INDEX IF NOT EXISTS idx_qso_sat_name   ON qso(sat_name);
CREATE INDEX IF NOT EXISTS idx_qso_prop_mode  ON qso(prop_mode);