CREATE TABLE properties (
    keyword VARCHAR(16) PRIMARY KEY,
    value   VARCHAR(64)
);

CREATE TABLE airports (
    ident VARCHAR(4) PRIMARY KEY,
    faa_ident CHAR(3),
    icao_ident CHAR(4),

    name VARCHAR(70),
    state VARCHAR(20) NULL,

    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    elevation REAL
);

CREATE INDEX faa_ident ON airports(faa_ident);

CREATE TABLE runways (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    airport VARCHAR(4) REFERENCES airports(ident),
    ident VARCHAR(3),

    latitude REAL,
    longitude REAL,
    elevation REAL,
    length INTEGER,
    width INTEGER
);

CREATE INDEX runway_ident ON runways(airport, ident);

CREATE TABLE navaids (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ident VARCHAR(5) NOT NULL,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    elevation REAL,
    frequency VARCHAR(6),
    type VARCHAR(8) NOT NULL,
    magvar REAL
);

CREATE INDEX navaid_ident ON navaids(ident);

CREATE TABLE fixes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ident VARCHAR(14),
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    type VARCHAR(10)
);

CREATE INDEX fix_ident ON fixes(ident);

CREATE TABLE routes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(16) NOT NULL,
    type CHAR(3) NOT NULL,
    conditions VARCHAR(50)
);

CREATE INDEX route_name ON routes(name);

CREATE TABLE legs (
    route_id INTEGER NOT NULL REFERENCES routes(id),
    from_fix_id INTEGER NOT NULL REFERENCES fixes(id),
    to_fix_id INTEGER NOT NULL REFERENCES fixes(id)
);

CREATE INDEX leg_route ON legs(route_id);
CREATE INDEX leg_from ON legs(from_fix_id);

CREATE TABLE preferred_routes (
    route_id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_airport VARCHAR(4) NOT NULL REFERENCES airports(ident),
    to_airport VARCHAR(4) NOT NULL REFERENCES airport(ident),
    type CHAR(3) NOT NULL,
    conditions VARCHAR(60),
    route VARCHAR(160) NOT NULL
);

CREATE INDEX preferred_route_airports ON preferred_routes(from_airport, to_airport, type);

