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

-- Table: debate
CREATE TABLE "spider__debate"."debate" (
    "Debate_ID" BIGINT NOT NULL,
    "Date" TEXT,
    "Venue" TEXT,
    "Num_of_Audience" BIGINT,
    CONSTRAINT "debate_pkey" PRIMARY KEY ("Debate_ID")
);

-- Table: debate_people
CREATE TABLE "spider__debate"."debate_people" (
    "Debate_ID" BIGINT NOT NULL,
    "Affirmative" BIGINT NOT NULL,
    "Negative" BIGINT NOT NULL,
    "If_Affirmative_Win" BOOLEAN,
    CONSTRAINT "debate_people_pkey" PRIMARY KEY ("Debate_ID", "Affirmative", "Negative")
);

-- Table: people
CREATE TABLE "spider__debate"."people" (
    "People_ID" BIGINT NOT NULL,
    "District" TEXT,
    "Name" TEXT,
    "Party" TEXT,
    "Age" BIGINT,
    CONSTRAINT "people_pkey" PRIMARY KEY ("People_ID")
);

-- Foreign Keys
ALTER TABLE "spider__debate"."debate_people" ADD CONSTRAINT "fk_debate_people_0" FOREIGN KEY ("Negative") REFERENCES "spider__debate"."people" ("People_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__debate"."debate_people" ADD CONSTRAINT "fk_debate_people_1" FOREIGN KEY ("Affirmative") REFERENCES "spider__debate"."people" ("People_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__debate"."debate_people" ADD CONSTRAINT "fk_debate_people_2" FOREIGN KEY ("Debate_ID") REFERENCES "spider__debate"."debate" ("Debate_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
