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

-- Table: city
CREATE TABLE "spider__city_record"."city" (
    "City_ID" BIGINT NOT NULL,
    "City" TEXT,
    "Hanzi" TEXT,
    "Hanyu_Pinyin" TEXT,
    "Regional_Population" BIGINT,
    "GDP" DOUBLE PRECISION,
    CONSTRAINT "city_pkey" PRIMARY KEY ("City_ID")
);

-- Table: hosting_city
CREATE TABLE "spider__city_record"."hosting_city" (
    "Year" BIGINT NOT NULL,
    "Match_ID" BIGINT,
    "Host_City" TEXT,
    CONSTRAINT "hosting_city_pkey" PRIMARY KEY ("Year")
);

-- Table: match
CREATE TABLE "spider__city_record"."match" (
    "Match_ID" BIGINT NOT NULL,
    "Date" TEXT,
    "Venue" TEXT,
    "Score" TEXT,
    "Result" TEXT,
    "Competition" TEXT,
    CONSTRAINT "match_pkey" PRIMARY KEY ("Match_ID")
);

-- Table: temperature
CREATE TABLE "spider__city_record"."temperature" (
    "City_ID" BIGINT NOT NULL,
    "Jan" DOUBLE PRECISION,
    "Feb" DOUBLE PRECISION,
    "Mar" DOUBLE PRECISION,
    "Apr" DOUBLE PRECISION,
    "Jun" DOUBLE PRECISION,
    "Jul" DOUBLE PRECISION,
    "Aug" DOUBLE PRECISION,
    "Sep" DOUBLE PRECISION,
    "Oct" DOUBLE PRECISION,
    "Nov" DOUBLE PRECISION,
    "Dec" DOUBLE PRECISION,
    CONSTRAINT "temperature_pkey" PRIMARY KEY ("City_ID")
);

-- Foreign Keys
ALTER TABLE "spider__city_record"."hosting_city" ADD CONSTRAINT "fk_hosting_city_0" FOREIGN KEY ("Match_ID") REFERENCES "spider__city_record"."match" ("Match_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__city_record"."temperature" ADD CONSTRAINT "fk_temperature_0" FOREIGN KEY ("City_ID") REFERENCES "spider__city_record"."city" ("City_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
