-- Add up migration script here
CREATE TABLE "aircrafts" (
    "id" uuid NOT NULL,
    "registration" text NOT NULL,
    "category" text NOT NULL,

    "manufacturer" text NOT NULL,
    "model" text NOT NULL,
    
    "on_ground" boolean NOT NULL DEFAULT 1,

    "created_at" timestamp DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "aircrafts_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "aircrafts_registration_unique" UNIQUE ("registration")
) WITH (oids = false);

CREATE TABLE "gps" (
    "aircraft_id" uuid NOT NULL,

    "latitude_deg" numeric NOT NULL,
    "longitude_deg" numeric NOT NULL,
    "elevation_ft" numeric NOT NULL,

    "created_at" timestamp DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "gps_pkey" PRIMARY KEY ("aircraft_id")
)

CREATE TABLE "fuel" (
    "aircraft_id" uuid NOT NULL,
    "center_quantity" numeric,
    "center_capacity" numeric,
    "center2_quantity" numeric,
    "center2_capacity" numeric,
    "center3_quantity" numeric,
    "center3_capacity" numeric,
    "left_main_quantity" numeric,
    "left_main_capacity" numeric,
    "left_aux_quantity" numeric,
    "left_aux_capacity" numeric,
    "left_tip_quantity" numeric,
    "left_tip_capacity" numeric,
    "right_main_quantity" numeric,
    "right_main_capacity" numeric,
    "right_aux_quantity" numeric,
    "right_aux_capacity" numeric,
    "right_tip_quantity" numeric,
    "right_tip_capacity" numeric,
    "external1_quantity" numeric,
    "external1_capacity" numeric,
    "external2_quantity" numeric,
    "external2_capacity" numeric,
    "fuel_weight" numeric,
    
    "created_at" timestamp DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "fuel_pkey" PRIMARY KEY ("aircraft_id")
)

-- If a player is in an aircraft that doesnt exist on our end, ask them to "purchace" it from the factory
-- What's the pricing going to look like though... hmm

-- If a pilot buys a new aircraft they need to get in it at least once to get all the fuel-cell data, we don't know beforehand how many tanks it has
-- after updating, the capacity should be > 0 to indicate which ones are used