CREATE TABLE flightplan (
  id                    UUID             NOT NULL DEFAULT uuid_generate_v4()PRIMARY KEY,
  callsign              UUID             NOT NULL,
  registration          UUID             NOT NULL,
  uassp_id              UUID             NOT NULL,
  uaso_id               UUID             NOT NULL,
  pilot_id              UUID,
  typeof_flight         VARCHAR,
  public_id             VARCHAR                   DEFAULT '00',
  departure_latitude    DOUBLE PRECISION NOT NULL,
  departure_longitude   DOUBLE PRECISION NOT NULL,
  departure_altitude    DOUBLE PRECISION NOT NULL,
  departure_time        TIMESTAMPTZ      NOT NULL,
  destination_latitude  DOUBLE PRECISION NOT NULL,
  destination_longitude DOUBLE PRECISION NOT NULL,
  destination_altitude  DOUBLE PRECISION NOT NULL,
  destination_time      TIMESTAMPTZ      NOT NULL,
  ground_speed          DOUBLE PRECISION,
  altitude              DOUBLE PRECISION NOT NULL,
  apply_status          VARCHAR                   DEFAULT '00',
  adjust_result         VARCHAR                   DEFAULT '00',
  flight_status         VARCHAR                   DEFAULT '00',
  com_equipment         VARCHAR,
  nav_equipment         VARCHAR(256),
  route_type            VARCHAR          NOT NULL,
  route_radius          DOUBLE PRECISION,
  route_space_err       INTEGER,
  route_time_eerr       INTEGER,
  registration_info     VARCHAR(256),
  flight_name           VARCHAR(256),
  free_text             VARCHAR(1000)
);


CREATE TABLE flightplan_coordinate (
  id            VARCHAR          NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  flightplan_id UUID             NOT NULL REFERENCES flightplan (id),
  longitude     DOUBLE PRECISION NOT NULL,
  latitude      DOUBLE PRECISION NOT NULL,
  altitude      DOUBLE PRECISION,
  velocity      DOUBLE PRECISION,
  time          TIMESTAMPTZ
);

--============================================================

CREATE TABLE flightplan_application_result (
  id            UUID    NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  flightplan_id UUID    NOT NULL REFERENCES flightplan (id),
  callsign      UUID    NOT NULL,
  registration  UUID    NOT NULL,
  uassp_id      UUID    NOT NULL,
  uaso_id       UUID,
  pilot_id      UUID,
  apply_status  VARCHAR NOT NULL,
  result_id     VARCHAR
);


CREATE TABLE incomplete_info (
  id                               VARCHAR NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  flightplan_application_result_id UUID    NOT NULL REFERENCES flightplan_application_result (id),
  content                          VARCHAR(256),
  item                             VARCHAR(256)
);


CREATE TABLE conflict_info (
  id                               UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  flightplan_application_result_id UUID NOT NULL REFERENCES flightplan_application_result (id),
  emergency_id                     VARCHAR,
  no_fly_area_id                   VARCHAR,
  no_fly_area_name                 VARCHAR,
  no_fly_area_type                 VARCHAR,
  ground_obstacle_id               VARCHAR,
  ground_obstacle_name             VARCHAR(256),
  weather_info                     VARCHAR(256)
);

CREATE TABLE conflict_coordinate (
  id                    UUID             NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  conflict_info_id      UUID             NOT NULL REFERENCES conflict_info (id),
  start_point_longitude DOUBLE PRECISION NOT NULL,
  start_point_latitude  DOUBLE PRECISION NOT NULL,
  start_point_altitude  DOUBLE PRECISION NOT NULL,
  start_time            TIMESTAMPTZ      NOT NULL,
  end_point_longitude   DOUBLE PRECISION NOT NULL,
  end_point_latitude    DOUBLE PRECISION NOT NULL,
  end_point_altitude    DOUBLE PRECISION NOT NULL,
  end_time              TIMESTAMPTZ      NOT NULL
);

--=================================================================================================

CREATE TABLE telemetry (
  id            UUID             NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  flightplan_id UUID             NOT NULL REFERENCES flightplan (id),
  longitude     DOUBLE PRECISION NOT NULL,
  latitude      DOUBLE PRECISION NOT NULL,
  altitude_gl   DOUBLE PRECISION NOT NULL,
  gpc_recv_time TIMESTAMPTZ      NOT NULL,
  speed_x       DOUBLE PRECISION NOT NULL,
  speed_y       DOUBLE PRECISION NOT NULL,
  speed_z       DOUBLE PRECISION NOT NULL,
  squence_no    INTEGER          NOT NULL,
  yaw           DOUBLE PRECISION,
  pitch         DOUBLE PRECISION,
  roll          DOUBLE PRECISION
);

--===================================================================================================

CREATE TABLE message (
  id            UUID        NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
  flightplan_id UUID        NOT NULL REFERENCES flightplan (id),
  origin        VARCHAR     NOT NULL,
  origin_id     UUID        NOT NULL,
  time_sent     TIMESTAMPTZ NOT NULL,
  severity      VARCHAR     NOT NULL,
  message_type  VARCHAR     NOT NULL,
  free_text     VARCHAR(1000),
  callbacl      VARCHAR(256)
);

-- DROP SCRIPTS


DROP TABLE IF EXISTS flightplan_coordinate;

DROP TABLE IF EXISTS flightplan;

--=========================================

DROP TABLE IF EXISTS incomplete_info;

DROP TABLE IF EXISTS conflict_coordinate;

DROP TABLE IF EXISTS conflict_info;

DROP TABLE IF EXISTS flightplan_application_result;

--=================================================

DROP TABLE IF EXISTS telemetry;

--=================================================

DROP TABLE IF EXISTS message;