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

-- Table: Addresses
CREATE TABLE "spider__behavior_monitoring"."Addresses" (
    "address_id" BIGINT NOT NULL,
    "line_1" TEXT,
    "line_2" TEXT,
    "line_3" TEXT,
    "city" TEXT,
    "zip_postcode" TEXT,
    "state_province_county" TEXT,
    "country" TEXT,
    "other_address_details" TEXT,
    CONSTRAINT "Addresses_pkey" PRIMARY KEY ("address_id")
);

-- Table: Assessment_Notes
CREATE TABLE "spider__behavior_monitoring"."Assessment_Notes" (
    "notes_id" BIGINT NOT NULL,
    "student_id" BIGINT,
    "teacher_id" BIGINT NOT NULL,
    "date_of_notes" TIMESTAMP,
    "text_of_notes" TEXT,
    "other_details" TEXT
);

-- Table: Behavior_Incident
CREATE TABLE "spider__behavior_monitoring"."Behavior_Incident" (
    "incident_id" BIGINT NOT NULL,
    "incident_type_code" TEXT NOT NULL,
    "student_id" BIGINT NOT NULL,
    "date_incident_start" TIMESTAMP,
    "date_incident_end" TIMESTAMP,
    "incident_summary" TEXT,
    "recommendations" TEXT,
    "other_details" TEXT,
    CONSTRAINT "Behavior_Incident_pkey" PRIMARY KEY ("incident_id")
);

-- Table: Detention
CREATE TABLE "spider__behavior_monitoring"."Detention" (
    "detention_id" BIGINT NOT NULL,
    "detention_type_code" TEXT NOT NULL,
    "teacher_id" BIGINT,
    "datetime_detention_start" TIMESTAMP,
    "datetime_detention_end" TIMESTAMP,
    "detention_summary" TEXT,
    "other_details" TEXT,
    CONSTRAINT "Detention_pkey" PRIMARY KEY ("detention_id")
);

-- Table: Ref_Address_Types
CREATE TABLE "spider__behavior_monitoring"."Ref_Address_Types" (
    "address_type_code" TEXT NOT NULL,
    "address_type_description" TEXT,
    CONSTRAINT "Ref_Address_Types_pkey" PRIMARY KEY ("address_type_code")
);

-- Table: Ref_Detention_Type
CREATE TABLE "spider__behavior_monitoring"."Ref_Detention_Type" (
    "detention_type_code" TEXT NOT NULL,
    "detention_type_description" TEXT,
    CONSTRAINT "Ref_Detention_Type_pkey" PRIMARY KEY ("detention_type_code")
);

-- Table: Ref_Incident_Type
CREATE TABLE "spider__behavior_monitoring"."Ref_Incident_Type" (
    "incident_type_code" TEXT NOT NULL,
    "incident_type_description" TEXT,
    CONSTRAINT "Ref_Incident_Type_pkey" PRIMARY KEY ("incident_type_code")
);

-- Table: Student_Addresses
CREATE TABLE "spider__behavior_monitoring"."Student_Addresses" (
    "student_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "date_address_from" TIMESTAMP NOT NULL,
    "date_address_to" TIMESTAMP,
    "monthly_rental" NUMERIC(19,4),
    "other_details" TEXT
);

-- Table: Students
CREATE TABLE "spider__behavior_monitoring"."Students" (
    "student_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "first_name" TEXT,
    "middle_name" TEXT,
    "last_name" TEXT,
    "cell_mobile_number" TEXT,
    "email_address" TEXT,
    "date_first_rental" TIMESTAMP,
    "date_left_university" TIMESTAMP,
    "other_student_details" TEXT,
    CONSTRAINT "Students_pkey" PRIMARY KEY ("student_id")
);

-- Table: Students_in_Detention
CREATE TABLE "spider__behavior_monitoring"."Students_in_Detention" (
    "student_id" BIGINT NOT NULL,
    "detention_id" BIGINT NOT NULL,
    "incident_id" BIGINT NOT NULL
);

-- Table: Teachers
CREATE TABLE "spider__behavior_monitoring"."Teachers" (
    "teacher_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "first_name" TEXT,
    "middle_name" TEXT,
    "last_name" TEXT,
    "gender" TEXT,
    "cell_mobile_number" TEXT,
    "email_address" TEXT,
    "other_details" TEXT,
    CONSTRAINT "Teachers_pkey" PRIMARY KEY ("teacher_id")
);

-- Foreign Keys
ALTER TABLE "spider__behavior_monitoring"."Assessment_Notes" ADD CONSTRAINT "fk_Assessment_Notes_0" FOREIGN KEY ("teacher_id") REFERENCES "spider__behavior_monitoring"."Teachers" ("teacher_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Assessment_Notes" ADD CONSTRAINT "fk_Assessment_Notes_1" FOREIGN KEY ("student_id") REFERENCES "spider__behavior_monitoring"."Students" ("student_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Behavior_Incident" ADD CONSTRAINT "fk_Behavior_Incident_0" FOREIGN KEY ("student_id") REFERENCES "spider__behavior_monitoring"."Students" ("student_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Behavior_Incident" ADD CONSTRAINT "fk_Behavior_Incident_1" FOREIGN KEY ("incident_type_code") REFERENCES "spider__behavior_monitoring"."Ref_Incident_Type" ("incident_type_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Detention" ADD CONSTRAINT "fk_Detention_0" FOREIGN KEY ("teacher_id") REFERENCES "spider__behavior_monitoring"."Teachers" ("teacher_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Detention" ADD CONSTRAINT "fk_Detention_1" FOREIGN KEY ("detention_type_code") REFERENCES "spider__behavior_monitoring"."Ref_Detention_Type" ("detention_type_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Student_Addresses" ADD CONSTRAINT "fk_Student_Addresses_0" FOREIGN KEY ("student_id") REFERENCES "spider__behavior_monitoring"."Students" ("student_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Student_Addresses" ADD CONSTRAINT "fk_Student_Addresses_1" FOREIGN KEY ("address_id") REFERENCES "spider__behavior_monitoring"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Students" ADD CONSTRAINT "fk_Students_0" FOREIGN KEY ("address_id") REFERENCES "spider__behavior_monitoring"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Students_in_Detention" ADD CONSTRAINT "fk_Students_in_Detention_0" FOREIGN KEY ("student_id") REFERENCES "spider__behavior_monitoring"."Students" ("student_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Students_in_Detention" ADD CONSTRAINT "fk_Students_in_Detention_1" FOREIGN KEY ("detention_id") REFERENCES "spider__behavior_monitoring"."Detention" ("detention_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Students_in_Detention" ADD CONSTRAINT "fk_Students_in_Detention_2" FOREIGN KEY ("incident_id") REFERENCES "spider__behavior_monitoring"."Behavior_Incident" ("incident_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__behavior_monitoring"."Teachers" ADD CONSTRAINT "fk_Teachers_0" FOREIGN KEY ("address_id") REFERENCES "spider__behavior_monitoring"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
