select load_extension('mod_spatialite.so.8');
select InitSpatialMetadata(1);

begin transaction;

create table nexrad_wfo (
    code    TEXT PRIMARY KEY NOT NULL,
    city    TEXT NOT NULL,
    state   TEXT NOT NULL,
    address TEXT NOT NULL
);

select
    AddGeometryColumn('nexrad_wfo', 'coord', 4326, 'POINT', 'XY', 1),
    CreateSpatialIndex('nexrad_wfo', 'coord');

create table nexrad_radar (
    call           TEXT PRIMARY KEY NOT NULL,
    name           TEXT NOT NULL,
    wban           INTEGER,
    site_elevation FLOAT NOT NULL,
    tower_height   FLOAT NOT NULL
);

select
    AddGeometryColumn('nexrad_radar', 'coord', 4326, 'POINT', 'XY'),
    CreateSpatialIndex('nexrad_radar', 'coord');

create table nexrad_storm_event (
    id               INTEGER PRIMARY KEY NOT NULL,
    episode_id       INTEGER,
    timestamp_start  TIMESTAMP NOT NULL,
    timestamp_end    TIMESTAMP NOT NULL,
    state            TEXT NOT NULL,
    event_type       TEXT NOT NULL,
    wfo              TEXT NOT NULL,
    locale_start     TEXT NOT NULL,
    locale_end       TEXT NOT NULL,
    tornado_f_rating TEXT
);

create index nexrad_storm_event_episode_id_idx      on nexrad_storm_event (episode_id);
create index nexrad_storm_event_event_type_idx      on nexrad_storm_event (event_type);
create index nexrad_storm_event_wfo_idx             on nexrad_storm_event (wfo);
create index nexrad_storm_event_timestamp_start_idx on nexrad_storm_event (timestamp_start);
create index nexrad_storm_event_timestamp_end_idx   on nexrad_storm_event (timestamp_end);

select
    AddGeometryColumn('nexrad_storm_event', 'coord_start', 4326, 'POINT', 'XY', 0),
    CreateSpatialIndex('nexrad_storm_event', 'coord_start');

select
    AddGeometryColumn('nexrad_storm_event', 'coord_end', 4326, 'POINT', 'XY', 0),
    CreateSpatialIndex('nexrad_storm_event', 'coord_end');

create table nexrad_afos_message (
    id               INTEGER PRIMARY KEY NOT NULL,
    timestamp_issued TIMESTAMP NOT NULL,
    serial           INTEGER NOT NULL,
    text_raw         TEXT NOT NULL,
    product          TEXT NOT NULL,
    wfo              TEXT NOT NULL,
    vtec_start       TIMESTAMP,
    vtec_end         TIMESTAMP,
    vtec_type        TEXT,
    actions          TEXT,
    phenom           TEXT,
    sig              TEXT,
    etn              INTEGER,
    hydro_severity   TEXT,
    hydro_cause      TEXT,
    hydro_record     TEXT,
    azimuth          FLOAT,
    speed            FLOAT,
    forecaster       TEXT NOT NULL
);

create index nexrad_afos_message_timestamp_idx      on nexrad_afos_message (timestamp_issued);
create index nexrad_afos_message_vtec_timestamp_idx on nexrad_afos_message (vtec_start, vtec_end);
create index nexrad_afos_message_product_idx        on nexrad_afos_message (product);
create index nexrad_afos_message_wfo_idx            on nexrad_afos_message (wfo);
create index nexrad_afos_message_phenom_idx         on nexrad_afos_message (phenom);
create index nexrad_afos_message_sig_idx            on nexrad_afos_message (sig);

select
    AddGeometryColumn('nexrad_afos_message', 'location', 4326, 'POINT'),
    CreateSpatialIndex('nexrad_afos_message', 'location');

select
    AddGeometryColumn('nexrad_afos_message', 'poly', 4326, 'POLYGON'),
    CreateSpatialIndex('nexrad_afos_message', 'poly');

commit;