-- Tracking tool database schema. Run this ONCE in cPanel ->
-- phpMyAdmin (or via the `mysql` CLI) to create the three tables.
-- Re-runnable: every CREATE is IF NOT EXISTS.

-- NOTE: pilots stores only id + self-registered display NAME. There is NO
-- colour column — colour is chosen per-receiver in the client, not dictated by
-- the pilot being tracked. If you created an earlier schema that had a `color`
-- column, drop it once:  ALTER TABLE pilots DROP COLUMN color;
CREATE TABLE IF NOT EXISTS pilots (
    id          VARCHAR(32)  PRIMARY KEY,
    display     VARCHAR(64)  NOT NULL,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS positions (
    pilot_id     VARCHAR(32)  PRIMARY KEY,
    lat          DOUBLE       NOT NULL,
    lon          DOUBLE       NOT NULL,
    alt_msl_ft   DOUBLE       NOT NULL,
    climb_fpm    DOUBLE       NOT NULL DEFAULT 0,
    speed_mph    DOUBLE       NOT NULL DEFAULT 0,
    heading      DOUBLE       NOT NULL DEFAULT 0,
    fix_time     DATETIME     NOT NULL,
    receive_time DATETIME     NOT NULL,
    FOREIGN KEY (pilot_id) REFERENCES pilots(id) ON DELETE CASCADE,
    INDEX (fix_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS winds (
    pilot_id    VARCHAR(32)  PRIMARY KEY,
    winds_time  DATETIME     NOT NULL,
    min_alt_ft  INT          NOT NULL,
    max_alt_ft  INT          NOT NULL,
    bucket_ft   INT          NOT NULL,
    units_json  VARCHAR(64)  NOT NULL,
    rows_json   JSON         NOT NULL,
    FOREIGN KEY (pilot_id) REFERENCES pilots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;