CREATE TABLE public.flights (
   id integer NOT NULL,
   user_id integer NOT NULL,
   origin_airfield_id integer NOT NULL,
   destination_airfield_id integer NOT NULL,
   date date NOT NULL,
   aircraft_id integer,
   role_id integer,
   flight_number character varying(255) NOT NULL,
   actual_out timestamp without time zone,
   actual_in timestamp without time zone,
   actual_block integer,
   night integer,
   instrument integer,
   hood integer,
   cross_country boolean,
   remarks text,
   scheduled_out timestamp without time zone,
   scheduled_in timestamp without time zone,
   scheduled_block integer,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   number_of_passengers integer,
   day_landings integer,
   night_landings integer,
   day_takeoffs integer,
   night_takeoffs integer,
   CONSTRAINT flights_actual_block_matches_in_out CHECK (((actual_block)::double precision = date_part('epoch'::text, (actual_in - actual_out)))),
   CONSTRAINT flights_actual_out_before_in CHECK ((actual_out <= actual_in)),
   CONSTRAINT flights_positive_values CHECK (((scheduled_block >= 0) AND (actual_block >= 0) AND (night >= 0) AND (instrument >= 0) AND (hood >= 0) AND (day_landings >= 0) A
ND (night_landings >= 0) AND (day_takeoffs >= 0) AND (night_takeoffs >= 0) AND (number_of_passengers >= 0))),
   CONSTRAINT flights_scheduled_block_matches_in_out CHECK (((scheduled_block)::double precision = date_part('epoch'::text, (scheduled_in - scheduled_out)))),
   CONSTRAINT flights_scheduled_out_before_in CHECK ((scheduled_out <= scheduled_in)),
   CONSTRAINT flights_times_within_actual_block CHECK (((night <= actual_block) AND (instrument <= actual_block) AND (hood <= actual_block)))
);

CREATE TABLE public.enroute_stops ( 
   id integer NOT NULL,
   flight_id integer NOT NULL,
   airfield_id integer NOT NULL,
   sequence integer NOT NULL,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   user_id integer NOT NULL
);

CREATE TABLE public.airfields ( 
   id integer NOT NULL,
   user_id integer NOT NULL,
   name character varying(255),
   city character varying(255),
   state character varying(255),
   country character varying(255),
   latitude double precision,
   longitude double precision,
   elevation integer,
   timezone character varying(255),
   remarks text,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   icao_code character varying(255),
   iata_code character varying(255),
   identifier character varying(255) NOT NULL,
   seconds_std_offset integer,
   seconds_dst_offset integer
);


CREATE TABLE public.approaches (
   id integer NOT NULL,
   flight_id integer NOT NULL,
   airfield_id integer NOT NULL,
   approach_type_id integer NOT NULL,
   runway character varying(255),
   ceiling character varying(255),
   visibility character varying(255),
   coupled boolean,
   missed boolean,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   user_id integer NOT NULL
);

CREATE TABLE public.roles ( 
   id integer NOT NULL,
   user_id integer NOT NULL,
   identifier character varying(255) NOT NULL,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   description character varying(255)
);

CREATE TABLE public.aircraft ( 
   id integer NOT NULL,
   user_id integer NOT NULL,
   remarks text,
   created_at timestamp without time zone,
   updated_at timestamp without time zone,
   aircraft_type_id integer,
   serial_number character varying(255),
   registration_number character varying(255),
   fleet_number character varying(255),
   identifier character varying(255) NOT NULL
); 

CREATE TABLE public.aircraft_types ( 
   id integer NOT NULL,
   user_id integer NOT NULL,
   identifier character varying(255) NOT NULL,
   description character varying(255),
   created_at timestamp without time zone,
   updated_at timestamp without time zone
); 

CREATE TABLE public.aircraft_properties ( 
   id integer NOT NULL,
   identifier character varying(255),
   description character varying(255)
); 


CREATE TABLE public.glides ( 
   id integer NOT NULL,
   flight_id integer NOT NULL,
   launch_type_id integer NOT NULL,
   duration integer,
   distance double precision,
   release_altitude double precision,
   max_altitude double precision,
   engine_start_altitude double precision,
   created_at timestamp without time zone,
   updated_at timestamp without time zone
);

CREATE TABLE public.holds ( 
   id integer NOT NULL,
   flight_id integer NOT NULL,
   location character varying(255),
   turns integer,
   duration integer,
   created_at timestamp without time zone,
   updated_at timestamp without time zone
);