-- Schema: spider__aircraft
CREATE SCHEMA IF NOT EXISTS "spider__aircraft";

-- Table: aircraft
CREATE TABLE "spider__aircraft"."aircraft" (
    "Aircraft_ID" BIGINT NOT NULL,
    "Aircraft" TEXT NOT NULL,
    "Description" TEXT NOT NULL,
    "Max_Gross_Weight" TEXT NOT NULL,
    "Total_disk_area" TEXT NOT NULL,
    "Max_disk_Loading" TEXT NOT NULL,
    CONSTRAINT "aircraft_pkey" PRIMARY KEY ("Aircraft_ID")
);

-- Table: airport
CREATE TABLE "spider__aircraft"."airport" (
    "Airport_ID" BIGINT NOT NULL,
    "Airport_Name" TEXT,
    "Total_Passengers" DOUBLE PRECISION,
    "%_Change_2007" TEXT,
    "International_Passengers" DOUBLE PRECISION,
    "Domestic_Passengers" DOUBLE PRECISION,
    "Transit_Passengers" DOUBLE PRECISION,
    "Aircraft_Movements" DOUBLE PRECISION,
    "Freight_Metric_Tonnes" DOUBLE PRECISION,
    CONSTRAINT "airport_pkey" PRIMARY KEY ("Airport_ID")
);

-- Table: airport_aircraft
CREATE TABLE "spider__aircraft"."airport_aircraft" (
    "ID" BIGINT,
    "Airport_ID" BIGINT NOT NULL,
    "Aircraft_ID" BIGINT NOT NULL,
    CONSTRAINT "airport_aircraft_pkey" PRIMARY KEY ("Airport_ID", "Aircraft_ID")
);

-- Table: match
CREATE TABLE "spider__aircraft"."match" (
    "Round" DOUBLE PRECISION NOT NULL,
    "Location" TEXT,
    "Country" TEXT,
    "Date" TEXT,
    "Fastest_Qualifying" TEXT,
    "Winning_Pilot" TEXT,
    "Winning_Aircraft" TEXT,
    CONSTRAINT "match_pkey" PRIMARY KEY ("Round")
);

-- Table: pilot
CREATE TABLE "spider__aircraft"."pilot" (
    "Pilot_Id" BIGINT NOT NULL,
    "Name" TEXT NOT NULL,
    "Age" BIGINT NOT NULL,
    CONSTRAINT "pilot_pkey" PRIMARY KEY ("Pilot_Id")
);

-- Foreign Keys
ALTER TABLE "spider__aircraft"."airport_aircraft" ADD CONSTRAINT "fk_airport_aircraft_0" FOREIGN KEY ("Aircraft_ID") REFERENCES "spider__aircraft"."aircraft" ("Aircraft_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__aircraft"."airport_aircraft" ADD CONSTRAINT "fk_airport_aircraft_1" FOREIGN KEY ("Airport_ID") REFERENCES "spider__aircraft"."airport" ("Airport_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
