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

-- Table: city
CREATE TABLE "spider__farm"."city" (
    "City_ID" BIGINT NOT NULL,
    "Official_Name" TEXT,
    "Status" TEXT,
    "Area_km_2" DOUBLE PRECISION,
    "Population" DOUBLE PRECISION,
    "Census_Ranking" TEXT,
    CONSTRAINT "city_pkey" PRIMARY KEY ("City_ID")
);

-- Table: competition_record
CREATE TABLE "spider__farm"."competition_record" (
    "Competition_ID" BIGINT NOT NULL,
    "Farm_ID" BIGINT NOT NULL,
    "Rank" BIGINT,
    CONSTRAINT "competition_record_pkey" PRIMARY KEY ("Competition_ID", "Farm_ID")
);

-- Table: farm
CREATE TABLE "spider__farm"."farm" (
    "Farm_ID" BIGINT NOT NULL,
    "Year" BIGINT,
    "Total_Horses" DOUBLE PRECISION,
    "Working_Horses" DOUBLE PRECISION,
    "Total_Cattle" DOUBLE PRECISION,
    "Oxen" DOUBLE PRECISION,
    "Bulls" DOUBLE PRECISION,
    "Cows" DOUBLE PRECISION,
    "Pigs" DOUBLE PRECISION,
    "Sheep_and_Goats" DOUBLE PRECISION,
    CONSTRAINT "farm_pkey" PRIMARY KEY ("Farm_ID")
);

-- Table: farm_competition
CREATE TABLE "spider__farm"."farm_competition" (
    "Competition_ID" BIGINT NOT NULL,
    "Year" BIGINT,
    "Theme" TEXT,
    "Host_city_ID" BIGINT,
    "Hosts" TEXT,
    CONSTRAINT "farm_competition_pkey" PRIMARY KEY ("Competition_ID")
);

-- Foreign Keys
ALTER TABLE "spider__farm"."competition_record" ADD CONSTRAINT "fk_competition_record_0" FOREIGN KEY ("Farm_ID") REFERENCES "spider__farm"."farm" ("Farm_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__farm"."competition_record" ADD CONSTRAINT "fk_competition_record_1" FOREIGN KEY ("Competition_ID") REFERENCES "spider__farm"."farm_competition" ("Competition_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__farm"."farm_competition" ADD CONSTRAINT "fk_farm_competition_0" FOREIGN KEY ("Host_city_ID") REFERENCES "spider__farm"."city" ("City_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
