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

-- Table: Addresses
CREATE TABLE "spider__cre_Doc_Control_Systems"."Addresses" (
    "address_id" BIGINT NOT NULL,
    "address_details" TEXT,
    CONSTRAINT "Addresses_pkey" PRIMARY KEY ("address_id")
);

-- Table: Circulation_History
CREATE TABLE "spider__cre_Doc_Control_Systems"."Circulation_History" (
    "document_id" BIGINT NOT NULL,
    "draft_number" BIGINT NOT NULL,
    "copy_number" BIGINT NOT NULL,
    "employee_id" BIGINT NOT NULL,
    CONSTRAINT "Circulation_History_pkey" PRIMARY KEY ("document_id", "draft_number", "copy_number", "employee_id")
);

-- Table: Document_Drafts
CREATE TABLE "spider__cre_Doc_Control_Systems"."Document_Drafts" (
    "document_id" BIGINT NOT NULL,
    "draft_number" BIGINT NOT NULL,
    "draft_details" TEXT,
    CONSTRAINT "Document_Drafts_pkey" PRIMARY KEY ("document_id", "draft_number")
);

-- Table: Documents
CREATE TABLE "spider__cre_Doc_Control_Systems"."Documents" (
    "document_id" BIGINT NOT NULL,
    "document_status_code" TEXT NOT NULL,
    "document_type_code" TEXT NOT NULL,
    "shipping_agent_code" TEXT,
    "receipt_date" TIMESTAMP,
    "receipt_number" TEXT,
    "other_details" TEXT,
    CONSTRAINT "Documents_pkey" PRIMARY KEY ("document_id")
);

-- Table: Documents_Mailed
CREATE TABLE "spider__cre_Doc_Control_Systems"."Documents_Mailed" (
    "document_id" BIGINT NOT NULL,
    "mailed_to_address_id" BIGINT NOT NULL,
    "mailing_date" TIMESTAMP,
    CONSTRAINT "Documents_Mailed_pkey" PRIMARY KEY ("document_id", "mailed_to_address_id")
);

-- Table: Draft_Copies
CREATE TABLE "spider__cre_Doc_Control_Systems"."Draft_Copies" (
    "document_id" BIGINT NOT NULL,
    "draft_number" BIGINT NOT NULL,
    "copy_number" BIGINT NOT NULL,
    CONSTRAINT "Draft_Copies_pkey" PRIMARY KEY ("document_id", "draft_number", "copy_number")
);

-- Table: Employees
CREATE TABLE "spider__cre_Doc_Control_Systems"."Employees" (
    "employee_id" BIGINT NOT NULL,
    "role_code" TEXT NOT NULL,
    "employee_name" TEXT,
    "other_details" TEXT,
    CONSTRAINT "Employees_pkey" PRIMARY KEY ("employee_id")
);

-- Table: Ref_Document_Status
CREATE TABLE "spider__cre_Doc_Control_Systems"."Ref_Document_Status" (
    "document_status_code" TEXT NOT NULL,
    "document_status_description" TEXT NOT NULL,
    CONSTRAINT "Ref_Document_Status_pkey" PRIMARY KEY ("document_status_code")
);

-- Table: Ref_Document_Types
CREATE TABLE "spider__cre_Doc_Control_Systems"."Ref_Document_Types" (
    "document_type_code" TEXT NOT NULL,
    "document_type_description" TEXT NOT NULL,
    CONSTRAINT "Ref_Document_Types_pkey" PRIMARY KEY ("document_type_code")
);

-- Table: Ref_Shipping_Agents
CREATE TABLE "spider__cre_Doc_Control_Systems"."Ref_Shipping_Agents" (
    "shipping_agent_code" TEXT NOT NULL,
    "shipping_agent_name" TEXT NOT NULL,
    "shipping_agent_description" TEXT NOT NULL,
    CONSTRAINT "Ref_Shipping_Agents_pkey" PRIMARY KEY ("shipping_agent_code")
);

-- Table: Roles
CREATE TABLE "spider__cre_Doc_Control_Systems"."Roles" (
    "role_code" TEXT NOT NULL,
    "role_description" TEXT,
    CONSTRAINT "Roles_pkey" PRIMARY KEY ("role_code")
);

-- Foreign Keys
ALTER TABLE "spider__cre_Doc_Control_Systems"."Circulation_History" ADD CONSTRAINT "fk_Circulation_History_0" FOREIGN KEY ("employee_id") REFERENCES "spider__cre_Doc_Control_Systems"."Employees" ("employee_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Circulation_History" ADD CONSTRAINT "fk_Circulation_History_1" FOREIGN KEY ("document_id", "document_id", "document_id", "draft_number", "draft_number", "draft_number", "copy_number", "copy_number", "copy_number") REFERENCES "spider__cre_Doc_Control_Systems"."Draft_Copies" ("document_id", "document_id", "document_id", "draft_number", "draft_number", "draft_number", "copy_number", "copy_number", "copy_number") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Document_Drafts" ADD CONSTRAINT "fk_Document_Drafts_0" FOREIGN KEY ("document_id") REFERENCES "spider__cre_Doc_Control_Systems"."Documents" ("document_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Documents" ADD CONSTRAINT "fk_Documents_0" FOREIGN KEY ("shipping_agent_code") REFERENCES "spider__cre_Doc_Control_Systems"."Ref_Shipping_Agents" ("shipping_agent_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Documents" ADD CONSTRAINT "fk_Documents_1" FOREIGN KEY ("document_status_code") REFERENCES "spider__cre_Doc_Control_Systems"."Ref_Document_Status" ("document_status_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Documents" ADD CONSTRAINT "fk_Documents_2" FOREIGN KEY ("document_type_code") REFERENCES "spider__cre_Doc_Control_Systems"."Ref_Document_Types" ("document_type_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Documents_Mailed" ADD CONSTRAINT "fk_Documents_Mailed_0" FOREIGN KEY ("mailed_to_address_id") REFERENCES "spider__cre_Doc_Control_Systems"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Documents_Mailed" ADD CONSTRAINT "fk_Documents_Mailed_1" FOREIGN KEY ("document_id") REFERENCES "spider__cre_Doc_Control_Systems"."Documents" ("document_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Draft_Copies" ADD CONSTRAINT "fk_Draft_Copies_0" FOREIGN KEY ("document_id", "document_id", "draft_number", "draft_number") REFERENCES "spider__cre_Doc_Control_Systems"."Document_Drafts" ("document_id", "document_id", "draft_number", "draft_number") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Doc_Control_Systems"."Employees" ADD CONSTRAINT "fk_Employees_0" FOREIGN KEY ("role_code") REFERENCES "spider__cre_Doc_Control_Systems"."Roles" ("role_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
