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

-- Table: circuits
CREATE TABLE "bird__formula_1"."circuits" (
    "circuitId" BIGINT NOT NULL,
    "circuitRef" TEXT NOT NULL DEFAULT ''::text,
    "name" TEXT NOT NULL DEFAULT ''::text,
    "location" TEXT,
    "country" TEXT,
    "lat" DOUBLE PRECISION,
    "lng" DOUBLE PRECISION,
    "alt" BIGINT,
    "url" TEXT NOT NULL DEFAULT ''::text,
    CONSTRAINT "circuits_pkey" PRIMARY KEY ("circuitId")
);

-- Table: constructorResults
CREATE TABLE "bird__formula_1"."constructorResults" (
    "constructorResultsId" BIGINT NOT NULL,
    "raceId" BIGINT NOT NULL DEFAULT 0,
    "constructorId" BIGINT NOT NULL DEFAULT 0,
    "points" DOUBLE PRECISION,
    "status" TEXT,
    CONSTRAINT "constructorResults_pkey" PRIMARY KEY ("constructorResultsId")
);

-- Table: constructorStandings
CREATE TABLE "bird__formula_1"."constructorStandings" (
    "constructorStandingsId" BIGINT NOT NULL,
    "raceId" BIGINT NOT NULL DEFAULT 0,
    "constructorId" BIGINT NOT NULL DEFAULT 0,
    "points" DOUBLE PRECISION NOT NULL DEFAULT 0,
    "position" BIGINT,
    "positionText" TEXT,
    "wins" BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT "constructorStandings_pkey" PRIMARY KEY ("constructorStandingsId")
);

-- Table: constructors
CREATE TABLE "bird__formula_1"."constructors" (
    "constructorId" BIGINT NOT NULL,
    "constructorRef" TEXT NOT NULL DEFAULT ''::text,
    "name" TEXT NOT NULL DEFAULT ''::text,
    "nationality" TEXT,
    "url" TEXT NOT NULL DEFAULT ''::text,
    CONSTRAINT "constructors_pkey" PRIMARY KEY ("constructorId")
);

-- Table: driverStandings
CREATE TABLE "bird__formula_1"."driverStandings" (
    "driverStandingsId" BIGINT NOT NULL,
    "raceId" BIGINT NOT NULL DEFAULT 0,
    "driverId" BIGINT NOT NULL DEFAULT 0,
    "points" DOUBLE PRECISION NOT NULL DEFAULT 0,
    "position" BIGINT,
    "positionText" TEXT,
    "wins" BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT "driverStandings_pkey" PRIMARY KEY ("driverStandingsId")
);

-- Table: drivers
CREATE TABLE "bird__formula_1"."drivers" (
    "driverId" BIGINT NOT NULL,
    "driverRef" TEXT NOT NULL DEFAULT ''::text,
    "number" BIGINT,
    "code" TEXT,
    "forename" TEXT NOT NULL DEFAULT ''::text,
    "surname" TEXT NOT NULL DEFAULT ''::text,
    "dob" DATE,
    "nationality" TEXT,
    "url" TEXT NOT NULL DEFAULT ''::text,
    CONSTRAINT "drivers_pkey" PRIMARY KEY ("driverId")
);

-- Table: lapTimes
CREATE TABLE "bird__formula_1"."lapTimes" (
    "raceId" BIGINT NOT NULL,
    "driverId" BIGINT NOT NULL,
    "lap" BIGINT NOT NULL,
    "position" BIGINT,
    "time" TEXT,
    "milliseconds" BIGINT,
    CONSTRAINT "lapTimes_pkey" PRIMARY KEY ("raceId", "driverId", "lap")
);

-- Table: pitStops
CREATE TABLE "bird__formula_1"."pitStops" (
    "raceId" BIGINT NOT NULL,
    "driverId" BIGINT NOT NULL,
    "stop" BIGINT NOT NULL,
    "lap" BIGINT NOT NULL,
    "time" TEXT NOT NULL,
    "duration" TEXT,
    "milliseconds" BIGINT,
    CONSTRAINT "pitStops_pkey" PRIMARY KEY ("raceId", "driverId", "stop")
);

-- Table: qualifying
CREATE TABLE "bird__formula_1"."qualifying" (
    "qualifyId" BIGINT NOT NULL,
    "raceId" BIGINT NOT NULL DEFAULT 0,
    "driverId" BIGINT NOT NULL DEFAULT 0,
    "constructorId" BIGINT NOT NULL DEFAULT 0,
    "number" BIGINT NOT NULL DEFAULT 0,
    "position" BIGINT,
    "q1" TEXT,
    "q2" TEXT,
    "q3" TEXT,
    CONSTRAINT "qualifying_pkey" PRIMARY KEY ("qualifyId")
);

-- Table: races
CREATE TABLE "bird__formula_1"."races" (
    "raceId" BIGINT NOT NULL,
    "year" BIGINT NOT NULL DEFAULT 0,
    "round" BIGINT NOT NULL DEFAULT 0,
    "circuitId" BIGINT NOT NULL DEFAULT 0,
    "name" TEXT NOT NULL DEFAULT ''::text,
    "date" DATE NOT NULL,
    "time" TEXT,
    "url" TEXT,
    CONSTRAINT "races_pkey" PRIMARY KEY ("raceId"),
    CONSTRAINT "races_url_key" UNIQUE ("url")
);

-- Table: results
CREATE TABLE "bird__formula_1"."results" (
    "resultId" BIGINT NOT NULL,
    "raceId" BIGINT NOT NULL DEFAULT 0,
    "driverId" BIGINT NOT NULL DEFAULT 0,
    "constructorId" BIGINT NOT NULL DEFAULT 0,
    "number" BIGINT,
    "grid" BIGINT NOT NULL DEFAULT 0,
    "position" BIGINT,
    "positionText" TEXT NOT NULL DEFAULT ''::text,
    "positionOrder" BIGINT NOT NULL DEFAULT 0,
    "points" DOUBLE PRECISION NOT NULL DEFAULT 0,
    "laps" BIGINT NOT NULL DEFAULT 0,
    "time" TEXT,
    "milliseconds" BIGINT,
    "fastestLap" BIGINT,
    "rank" BIGINT DEFAULT 0,
    "fastestLapTime" TEXT,
    "fastestLapSpeed" TEXT,
    "statusId" BIGINT NOT NULL DEFAULT 0,
    CONSTRAINT "results_pkey" PRIMARY KEY ("resultId")
);

-- Table: seasons
CREATE TABLE "bird__formula_1"."seasons" (
    "year" BIGINT NOT NULL DEFAULT 0,
    "url" TEXT NOT NULL DEFAULT ''::text,
    CONSTRAINT "seasons_pkey" PRIMARY KEY ("year")
);

-- Table: status
CREATE TABLE "bird__formula_1"."status" (
    "statusId" BIGINT NOT NULL,
    "status" TEXT NOT NULL DEFAULT ''::text,
    CONSTRAINT "status_pkey" PRIMARY KEY ("statusId")
);

-- Foreign Keys
ALTER TABLE "bird__formula_1"."constructorResults" ADD CONSTRAINT "fk_constructorResults_0" FOREIGN KEY ("constructorId") REFERENCES "bird__formula_1"."constructors" ("constructorId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."constructorResults" ADD CONSTRAINT "fk_constructorResults_1" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."constructorStandings" ADD CONSTRAINT "fk_constructorStandings_0" FOREIGN KEY ("constructorId") REFERENCES "bird__formula_1"."constructors" ("constructorId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."constructorStandings" ADD CONSTRAINT "fk_constructorStandings_1" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."driverStandings" ADD CONSTRAINT "fk_driverStandings_0" FOREIGN KEY ("driverId") REFERENCES "bird__formula_1"."drivers" ("driverId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."driverStandings" ADD CONSTRAINT "fk_driverStandings_1" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."lapTimes" ADD CONSTRAINT "fk_lapTimes_0" FOREIGN KEY ("driverId") REFERENCES "bird__formula_1"."drivers" ("driverId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."lapTimes" ADD CONSTRAINT "fk_lapTimes_1" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."pitStops" ADD CONSTRAINT "fk_pitStops_0" FOREIGN KEY ("driverId") REFERENCES "bird__formula_1"."drivers" ("driverId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."pitStops" ADD CONSTRAINT "fk_pitStops_1" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."qualifying" ADD CONSTRAINT "fk_qualifying_0" FOREIGN KEY ("constructorId") REFERENCES "bird__formula_1"."constructors" ("constructorId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."qualifying" ADD CONSTRAINT "fk_qualifying_1" FOREIGN KEY ("driverId") REFERENCES "bird__formula_1"."drivers" ("driverId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."qualifying" ADD CONSTRAINT "fk_qualifying_2" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."races" ADD CONSTRAINT "fk_races_1" FOREIGN KEY ("year") REFERENCES "bird__formula_1"."seasons" ("year") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."results" ADD CONSTRAINT "fk_results_0" FOREIGN KEY ("statusId") REFERENCES "bird__formula_1"."status" ("statusId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."results" ADD CONSTRAINT "fk_results_1" FOREIGN KEY ("constructorId") REFERENCES "bird__formula_1"."constructors" ("constructorId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."results" ADD CONSTRAINT "fk_results_2" FOREIGN KEY ("driverId") REFERENCES "bird__formula_1"."drivers" ("driverId") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__formula_1"."results" ADD CONSTRAINT "fk_results_3" FOREIGN KEY ("raceId") REFERENCES "bird__formula_1"."races" ("raceId") ON DELETE NO ACTION ON UPDATE NO ACTION;
