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

-- Table: Accounts
CREATE TABLE "spider__cre_Docs_and_Epenses"."Accounts" (
    "Account_ID" BIGINT NOT NULL,
    "Statement_ID" BIGINT NOT NULL,
    "Account_Details" TEXT,
    CONSTRAINT "Accounts_pkey" PRIMARY KEY ("Account_ID")
);

-- Table: Documents
CREATE TABLE "spider__cre_Docs_and_Epenses"."Documents" (
    "Document_ID" BIGINT NOT NULL,
    "Document_Type_Code" TEXT NOT NULL,
    "Project_ID" BIGINT NOT NULL,
    "Document_Date" TIMESTAMP,
    "Document_Name" TEXT,
    "Document_Description" TEXT,
    "Other_Details" TEXT,
    CONSTRAINT "Documents_pkey" PRIMARY KEY ("Document_ID")
);

-- Table: Documents_with_Expenses
CREATE TABLE "spider__cre_Docs_and_Epenses"."Documents_with_Expenses" (
    "Document_ID" BIGINT NOT NULL,
    "Budget_Type_Code" TEXT NOT NULL,
    "Document_Details" TEXT,
    CONSTRAINT "Documents_with_Expenses_pkey" PRIMARY KEY ("Document_ID")
);

-- Table: Projects
CREATE TABLE "spider__cre_Docs_and_Epenses"."Projects" (
    "Project_ID" BIGINT NOT NULL,
    "Project_Details" TEXT,
    CONSTRAINT "Projects_pkey" PRIMARY KEY ("Project_ID")
);

-- Table: Ref_Budget_Codes
CREATE TABLE "spider__cre_Docs_and_Epenses"."Ref_Budget_Codes" (
    "Budget_Type_Code" TEXT NOT NULL,
    "Budget_Type_Description" TEXT NOT NULL,
    CONSTRAINT "Ref_Budget_Codes_pkey" PRIMARY KEY ("Budget_Type_Code")
);

-- Table: Ref_Document_Types
CREATE TABLE "spider__cre_Docs_and_Epenses"."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: Statements
CREATE TABLE "spider__cre_Docs_and_Epenses"."Statements" (
    "Statement_ID" BIGINT NOT NULL,
    "Statement_Details" TEXT,
    CONSTRAINT "Statements_pkey" PRIMARY KEY ("Statement_ID")
);

-- Foreign Keys
ALTER TABLE "spider__cre_Docs_and_Epenses"."Accounts" ADD CONSTRAINT "fk_Accounts_0" FOREIGN KEY ("Statement_ID") REFERENCES "spider__cre_Docs_and_Epenses"."Statements" ("Statement_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Docs_and_Epenses"."Documents" ADD CONSTRAINT "fk_Documents_0" FOREIGN KEY ("Project_ID") REFERENCES "spider__cre_Docs_and_Epenses"."Projects" ("Project_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Docs_and_Epenses"."Documents" ADD CONSTRAINT "fk_Documents_1" FOREIGN KEY ("Document_Type_Code") REFERENCES "spider__cre_Docs_and_Epenses"."Ref_Document_Types" ("Document_Type_Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Docs_and_Epenses"."Documents_with_Expenses" ADD CONSTRAINT "fk_Documents_with_Expenses_0" FOREIGN KEY ("Document_ID") REFERENCES "spider__cre_Docs_and_Epenses"."Documents" ("Document_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Docs_and_Epenses"."Documents_with_Expenses" ADD CONSTRAINT "fk_Documents_with_Expenses_1" FOREIGN KEY ("Budget_Type_Code") REFERENCES "spider__cre_Docs_and_Epenses"."Ref_Budget_Codes" ("Budget_Type_Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Docs_and_Epenses"."Statements" ADD CONSTRAINT "fk_Statements_0" FOREIGN KEY ("Statement_ID") REFERENCES "spider__cre_Docs_and_Epenses"."Documents" ("Document_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
