-- 20260406 ChatGPT
-- $Header$
--
-- Example:
--   createdb satellite_data
--   psql -d satellite_data -f satellite_data_schema.sql
--
-- Purpose:
--   Schema for importing GNSS field QA CSV logs generated by T-Beam units.
--   A log file is recorded in table logs, and each CSV row is stored in
--   table log_data with a foreign-key reference back to logs.
--
-- Notes:
--   This revision adds support for the enhanced logger fields:
--     * sample_seq
--     * ms_since_run_start
--   The importer can still load older files that do not contain those fields.

BEGIN;

CREATE TABLE IF NOT EXISTS logs (
    log_id                      bigserial PRIMARY KEY,
    source_filename             text NOT NULL,
    source_path                 text,
    file_sha256                 text,
    file_size_bytes             bigint,
    raw_header_text             text,
    csv_header_line             text NOT NULL,
    imported_at                 timestamptz NOT NULL DEFAULT now(),
    import_notes                text,

    board_id                    text,
    gnss_chip                   text,
    firmware_exercise_name      text,
    firmware_version            text,
    boot_timestamp_utc          timestamptz,
    run_id                      text,
    first_timestamp_utc         timestamptz,
    last_timestamp_utc          timestamptz,

    row_count                   integer NOT NULL DEFAULT 0,
    sample_count                integer NOT NULL DEFAULT 0,
    satellite_count             integer NOT NULL DEFAULT 0,

    CONSTRAINT logs_source_filename_key UNIQUE (source_filename, file_sha256)
);

COMMENT ON TABLE logs IS
'One row per imported CSV log file. Stores file provenance, hash, raw hash-prefixed header text, CSV header line, and summary counts.';

COMMENT ON COLUMN logs.log_id IS
'Primary key for this imported file.';
COMMENT ON COLUMN logs.source_filename IS
'Filename of the imported CSV file, e.g. 20260406_175441_GUY.csv.';
COMMENT ON COLUMN logs.source_path IS
'Path used at import time. Useful for provenance when files are staged from different directories.';
COMMENT ON COLUMN logs.file_sha256 IS
'SHA-256 digest of the source file. Helps prevent duplicate imports and supports provenance audits.';
COMMENT ON COLUMN logs.file_size_bytes IS
'Size of the source file in bytes.';
COMMENT ON COLUMN logs.raw_header_text IS
'All leading hash-prefixed lines from the file, preserved verbatim as a text block.';
COMMENT ON COLUMN logs.csv_header_line IS
'Effective CSV column header line used by the importer, either from the file or from importer fallback logic.';
COMMENT ON COLUMN logs.imported_at IS
'Timestamp when the file was imported into PostgreSQL.';
COMMENT ON COLUMN logs.import_notes IS
'Optional free-form notes supplied at import time.';
COMMENT ON COLUMN logs.board_id IS
'Board identifier observed in the file, e.g. GUY, AMY, or CY.';
COMMENT ON COLUMN logs.gnss_chip IS
'GNSS receiver chip or module name, e.g. MAX-M10S or L76K.';
COMMENT ON COLUMN logs.firmware_exercise_name IS
'Firmware exercise or logger program name that produced the file.';
COMMENT ON COLUMN logs.firmware_version IS
'Firmware build or version string written by the device.';
COMMENT ON COLUMN logs.boot_timestamp_utc IS
'UTC timestamp representing when the device booted, as reported by the firmware.';
COMMENT ON COLUMN logs.run_id IS
'Run identifier shared by all rows from one logger session.';
COMMENT ON COLUMN logs.first_timestamp_utc IS
'First UTC sample timestamp found in this file.';
COMMENT ON COLUMN logs.last_timestamp_utc IS
'Last UTC sample timestamp found in this file.';
COMMENT ON COLUMN logs.row_count IS
'Total number of imported CSV data rows in the file.';
COMMENT ON COLUMN logs.sample_count IS
'Number of imported rows where record_type = sample.';
COMMENT ON COLUMN logs.satellite_count IS
'Number of imported rows where record_type = satellite.';

CREATE TABLE IF NOT EXISTS log_data (
    log_data_id                  bigserial PRIMARY KEY,
    log_id                       bigint NOT NULL REFERENCES logs(log_id) ON DELETE CASCADE,
    source_row_number            integer NOT NULL,

    record_type                  text NOT NULL,
    timestamp_utc                timestamptz,
    board_id                     text,
    gnss_chip                    text,
    firmware_exercise_name       text,
    firmware_version             text,
    boot_timestamp_utc           timestamptz,
    run_id                       text,
    sample_seq                   bigint,
    ms_since_run_start           bigint,

    fix_type                     text,
    fix_dimension                integer,
    sats_in_view                 integer,
    sat_seen                     integer,
    sats_used                    integer,
    hdop                         numeric(8,3),
    vdop                         numeric(8,3),
    pdop                         numeric(8,3),
    latitude                     double precision,
    longitude                    double precision,
    altitude_m                   numeric(12,3),
    speed_mps                    numeric(12,3),
    course_deg                   numeric(12,3),
    pps_seen                     boolean,
    quality_class                text,

    gps_count                    integer,
    galileo_count                integer,
    glonass_count                integer,
    beidou_count                 integer,
    navic_count                  integer,
    qzss_count                   integer,
    sbas_count                   integer,

    mean_cn0                     numeric(8,3),
    max_cn0                      numeric(8,3),
    age_of_fix_ms                bigint,
    ttff_ms                      bigint,
    longest_no_fix_ms            bigint,

    sat_talker                   text,
    sat_constellation            text,
    sat_prn                      integer,
    sat_elevation_deg            numeric(8,3),
    sat_azimuth_deg              numeric(8,3),
    sat_snr                      numeric(8,3),
    sat_used_in_solution         boolean,

    raw_csv_line                 text,

    CONSTRAINT log_data_record_type_chk CHECK (record_type IN ('sample', 'satellite'))
);

COMMENT ON TABLE log_data IS
'One row per CSV data row from a GNSS logger file. Stores both sample rows and per-satellite rows.';

COMMENT ON COLUMN log_data.log_data_id IS
'Primary key for one imported CSV data row.';
COMMENT ON COLUMN log_data.log_id IS
'Foreign key to logs.log_id, linking this row back to the source file.';
COMMENT ON COLUMN log_data.source_row_number IS
'1-based row number within the CSV data section, excluding preserved hash-prefixed header lines.';
COMMENT ON COLUMN log_data.record_type IS
'Logical row type. sample = one epoch summary row. satellite = one satellite snapshot tied to a sample epoch.';
COMMENT ON COLUMN log_data.timestamp_utc IS
'UTC time for the sample epoch, as reported by the GNSS receiver.';
COMMENT ON COLUMN log_data.board_id IS
'Board identifier such as GUY, AMY, or CY.';
COMMENT ON COLUMN log_data.gnss_chip IS
'GNSS module name, for example MAX-M10S or L76K.';
COMMENT ON COLUMN log_data.firmware_exercise_name IS
'Name of the firmware exercise or logger mode that generated the row.';
COMMENT ON COLUMN log_data.firmware_version IS
'Firmware build or version string embedded in the row.';
COMMENT ON COLUMN log_data.boot_timestamp_utc IS
'UTC timestamp representing when the device booted, according to firmware.';
COMMENT ON COLUMN log_data.run_id IS
'Run identifier shared across one logger session.';
COMMENT ON COLUMN log_data.sample_seq IS
'Sequential sample number within a run. Starts at 1 when a new log begins. Satellite rows inherit the parent sample sequence value.';
COMMENT ON COLUMN log_data.ms_since_run_start IS
'Monotonic milliseconds elapsed since the log file was opened. Useful for jitter, gap, and SD-write-impact analysis.';
COMMENT ON COLUMN log_data.fix_type IS
'Fix quality label such as NO_FIX, 2D, 3D, or DGPS depending on what the firmware emits.';
COMMENT ON COLUMN log_data.fix_dimension IS
'Numeric dimension of the position fix, typically 0, 2, or 3.';
COMMENT ON COLUMN log_data.sats_in_view IS
'Count of satellites reportedly in view at this epoch according to the receiver summary.';
COMMENT ON COLUMN log_data.sat_seen IS
'Count of satellites actually observed or emitted by the logger for this epoch. This may differ from sats_in_view depending on firmware logic.';
COMMENT ON COLUMN log_data.sats_used IS
'Count of satellites used in the navigation solution at this epoch.';
COMMENT ON COLUMN log_data.hdop IS
'Horizontal Dilution of Precision. Lower values generally indicate better horizontal geometry.';
COMMENT ON COLUMN log_data.vdop IS
'Vertical Dilution of Precision. Lower values generally indicate better vertical geometry.';
COMMENT ON COLUMN log_data.pdop IS
'Position Dilution of Precision. Combined geometry indicator for the position solution.';
COMMENT ON COLUMN log_data.latitude IS
'Latitude in decimal degrees.';
COMMENT ON COLUMN log_data.longitude IS
'Longitude in decimal degrees.';
COMMENT ON COLUMN log_data.altitude_m IS
'Altitude in meters, generally above mean sea level according to receiver output.';
COMMENT ON COLUMN log_data.speed_mps IS
'Receiver-reported speed over ground in meters per second.';
COMMENT ON COLUMN log_data.course_deg IS
'Receiver-reported course over ground in degrees.';
COMMENT ON COLUMN log_data.pps_seen IS
'Boolean indicating whether a PPS pulse was seen by the firmware during this epoch.';
COMMENT ON COLUMN log_data.quality_class IS
'Human-friendly firmware quality label such as POOR, FAIR, GOOD, or similar.';
COMMENT ON COLUMN log_data.gps_count IS
'Count of GPS satellites observed at this epoch.';
COMMENT ON COLUMN log_data.galileo_count IS
'Count of Galileo satellites observed at this epoch.';
COMMENT ON COLUMN log_data.glonass_count IS
'Count of GLONASS satellites observed at this epoch.';
COMMENT ON COLUMN log_data.beidou_count IS
'Count of BeiDou satellites observed at this epoch.';
COMMENT ON COLUMN log_data.navic_count IS
'Count of NavIC satellites observed at this epoch.';
COMMENT ON COLUMN log_data.qzss_count IS
'Count of QZSS satellites observed at this epoch.';
COMMENT ON COLUMN log_data.sbas_count IS
'Count of SBAS satellites observed at this epoch.';
COMMENT ON COLUMN log_data.mean_cn0 IS
'Mean carrier-to-noise density estimate across observed satellites. Higher values generally indicate stronger signals.';
COMMENT ON COLUMN log_data.max_cn0 IS
'Maximum carrier-to-noise density estimate among observed satellites for this epoch.';
COMMENT ON COLUMN log_data.age_of_fix_ms IS
'Age of the current fix in milliseconds, as reported by the firmware or receiver API.';
COMMENT ON COLUMN log_data.ttff_ms IS
'Time to first fix in milliseconds for the run or current acquisition state.';
COMMENT ON COLUMN log_data.longest_no_fix_ms IS
'Longest contiguous no-fix interval observed so far in the run, in milliseconds.';
COMMENT ON COLUMN log_data.sat_talker IS
'Talker or source prefix associated with the satellite row, such as GP, GA, GL, GB, or GN.';
COMMENT ON COLUMN log_data.sat_constellation IS
'Constellation name for the satellite row, such as GPS, Galileo, GLONASS, BeiDou, NavIC, QZSS, or SBAS.';
COMMENT ON COLUMN log_data.sat_prn IS
'PRN or SVID number identifying the satellite within its constellation.';
COMMENT ON COLUMN log_data.sat_elevation_deg IS
'Satellite elevation angle above the horizon in degrees.';
COMMENT ON COLUMN log_data.sat_azimuth_deg IS
'Satellite azimuth in degrees clockwise from true north, according to receiver output.';
COMMENT ON COLUMN log_data.sat_snr IS
'Signal-to-noise style quality measure for this satellite row. Depending on firmware, this may be SNR or CN0-like output.';
COMMENT ON COLUMN log_data.sat_used_in_solution IS
'Boolean indicating whether this specific satellite was used in the navigation solution.';
COMMENT ON COLUMN log_data.raw_csv_line IS
'Original CSV line preserved verbatim for audit and recovery purposes.';

CREATE INDEX IF NOT EXISTS log_data_log_id_idx
    ON log_data(log_id);

CREATE INDEX IF NOT EXISTS log_data_run_id_idx
    ON log_data(run_id);

CREATE INDEX IF NOT EXISTS log_data_timestamp_idx
    ON log_data(timestamp_utc);

CREATE INDEX IF NOT EXISTS log_data_record_type_idx
    ON log_data(record_type);

CREATE INDEX IF NOT EXISTS log_data_board_run_seq_idx
    ON log_data(board_id, run_id, sample_seq, record_type);

CREATE INDEX IF NOT EXISTS log_data_satellite_lookup_idx
    ON log_data(sat_constellation, sat_prn, timestamp_utc)
    WHERE record_type = 'satellite';

COMMIT;
