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

-- Table: All_Documents
CREATE TABLE "spider__cre_Doc_Tracking_DB"."All_Documents" (
    "Document_ID" BIGINT NOT NULL,
    "Date_Stored" TIMESTAMP,
    "Document_Type_Code" TEXT NOT NULL,
    "Document_Name" TEXT,
    "Document_Description" TEXT,
    "Other_Details" TEXT,
    CONSTRAINT "All_Documents_pkey" PRIMARY KEY ("Document_ID")
);

-- Table: Document_Locations
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Document_Locations" (
    "Document_ID" BIGINT NOT NULL,
    "Location_Code" TEXT NOT NULL,
    "Date_in_Location_From" TIMESTAMP NOT NULL,
    "Date_in_Locaton_To" TIMESTAMP,
    CONSTRAINT "Document_Locations_pkey" PRIMARY KEY ("Document_ID", "Location_Code", "Date_in_Location_From")
);

-- Table: Documents_to_be_Destroyed
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Documents_to_be_Destroyed" (
    "Document_ID" BIGINT NOT NULL,
    "Destruction_Authorised_by_Employee_ID" BIGINT,
    "Destroyed_by_Employee_ID" BIGINT,
    "Planned_Destruction_Date" TIMESTAMP,
    "Actual_Destruction_Date" TIMESTAMP,
    "Other_Details" TEXT,
    CONSTRAINT "Documents_to_be_Destroyed_pkey" PRIMARY KEY ("Document_ID")
);

-- Table: Employees
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Employees" (
    "Employee_ID" BIGINT NOT NULL,
    "Role_Code" TEXT NOT NULL,
    "Employee_Name" TEXT,
    "Gender_MFU" TEXT NOT NULL,
    "Date_of_Birth" TIMESTAMP NOT NULL,
    "Other_Details" TEXT,
    CONSTRAINT "Employees_pkey" PRIMARY KEY ("Employee_ID")
);

-- Table: Ref_Calendar
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Ref_Calendar" (
    "Calendar_Date" TIMESTAMP NOT NULL,
    "Day_Number" BIGINT,
    CONSTRAINT "Ref_Calendar_pkey" PRIMARY KEY ("Calendar_Date")
);

-- Table: Ref_Document_Types
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Ref_Document_Types" (
    "Document_Type_Code" TEXT NOT NULL,
    "Document_Type_Name" TEXT NOT NULL,
    "Document_Type_Description" TEXT NOT NULL,
    CONSTRAINT "Ref_Document_Types_pkey" PRIMARY KEY ("Document_Type_Code")
);

-- Table: Ref_Locations
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Ref_Locations" (
    "Location_Code" TEXT NOT NULL,
    "Location_Name" TEXT NOT NULL,
    "Location_Description" TEXT NOT NULL,
    CONSTRAINT "Ref_Locations_pkey" PRIMARY KEY ("Location_Code")
);

-- Table: Roles
CREATE TABLE "spider__cre_Doc_Tracking_DB"."Roles" (
    "Role_Code" TEXT NOT NULL,
    "Role_Name" TEXT,
    "Role_Description" TEXT,
    CONSTRAINT "Roles_pkey" PRIMARY KEY ("Role_Code")
);

-- Foreign Keys
ALTER TABLE "spider__cre_Doc_Tracking_DB"."All_Documents" ADD CONSTRAINT "fk_All_Documents_0" FOREIGN KEY ("Date_Stored") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Calendar" ("Calendar_Date") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."All_Documents" ADD CONSTRAINT "fk_All_Documents_1" FOREIGN KEY ("Document_Type_Code") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Document_Types" ("Document_Type_Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Document_Locations" ADD CONSTRAINT "fk_Document_Locations_0" FOREIGN KEY ("Document_ID") REFERENCES "spider__cre_Doc_Tracking_DB"."All_Documents" ("Document_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Document_Locations" ADD CONSTRAINT "fk_Document_Locations_1" FOREIGN KEY ("Date_in_Locaton_To") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Calendar" ("Calendar_Date") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Document_Locations" ADD CONSTRAINT "fk_Document_Locations_2" FOREIGN KEY ("Date_in_Location_From") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Calendar" ("Calendar_Date") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Document_Locations" ADD CONSTRAINT "fk_Document_Locations_3" FOREIGN KEY ("Location_Code") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Locations" ("Location_Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Documents_to_be_Destroyed" ADD CONSTRAINT "fk_Documents_to_be_Destroyed_0" FOREIGN KEY ("Document_ID") REFERENCES "spider__cre_Doc_Tracking_DB"."All_Documents" ("Document_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Documents_to_be_Destroyed" ADD CONSTRAINT "fk_Documents_to_be_Destroyed_1" FOREIGN KEY ("Actual_Destruction_Date") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Calendar" ("Calendar_Date") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Documents_to_be_Destroyed" ADD CONSTRAINT "fk_Documents_to_be_Destroyed_2" FOREIGN KEY ("Planned_Destruction_Date") REFERENCES "spider__cre_Doc_Tracking_DB"."Ref_Calendar" ("Calendar_Date") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Documents_to_be_Destroyed" ADD CONSTRAINT "fk_Documents_to_be_Destroyed_3" FOREIGN KEY ("Destruction_Authorised_by_Employee_ID") REFERENCES "spider__cre_Doc_Tracking_DB"."Employees" ("Employee_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Documents_to_be_Destroyed" ADD CONSTRAINT "fk_Documents_to_be_Destroyed_4" FOREIGN KEY ("Destroyed_by_Employee_ID") REFERENCES "spider__cre_Doc_Tracking_DB"."Employees" ("Employee_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Tracking_DB"."Employees" ADD CONSTRAINT "fk_Employees_0" FOREIGN KEY ("Role_Code") REFERENCES "spider__cre_Doc_Tracking_DB"."Roles" ("Role_Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
