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

-- Table: aircraft
CREATE TABLE "spider__pilot_record"."aircraft" (
    "Aircraft_ID" BIGINT NOT NULL,
    "Order_Year" BIGINT,
    "Manufacturer" TEXT,
    "Model" TEXT,
    "Fleet_Series" TEXT,
    "Powertrain" TEXT,
    "Fuel_Propulsion" TEXT,
    CONSTRAINT "aircraft_pkey" PRIMARY KEY ("Aircraft_ID")
);

-- Table: pilot
CREATE TABLE "spider__pilot_record"."pilot" (
    "Pilot_ID" BIGINT NOT NULL,
    "Pilot_name" TEXT,
    "Rank" BIGINT,
    "Age" BIGINT,
    "Nationality" TEXT,
    "Position" TEXT,
    "Join_Year" BIGINT,
    "Team" TEXT,
    CONSTRAINT "pilot_pkey" PRIMARY KEY ("Pilot_ID")
);

-- Table: pilot_record
CREATE TABLE "spider__pilot_record"."pilot_record" (
    "Record_ID" BIGINT,
    "Pilot_ID" BIGINT NOT NULL,
    "Aircraft_ID" BIGINT NOT NULL,
    "Date" TEXT NOT NULL,
    CONSTRAINT "pilot_record_pkey" PRIMARY KEY ("Pilot_ID", "Aircraft_ID", "Date")
);

-- Foreign Keys
ALTER TABLE "spider__pilot_record"."pilot_record" ADD CONSTRAINT "fk_pilot_record_0" FOREIGN KEY ("Aircraft_ID") REFERENCES "spider__pilot_record"."aircraft" ("Aircraft_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__pilot_record"."pilot_record" ADD CONSTRAINT "fk_pilot_record_1" FOREIGN KEY ("Pilot_ID") REFERENCES "spider__pilot_record"."pilot" ("Pilot_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
