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

-- Table: Asset_Parts
CREATE TABLE "spider__assets_maintenance"."Asset_Parts" (
    "asset_id" BIGINT NOT NULL,
    "part_id" BIGINT NOT NULL
);

-- Table: Assets
CREATE TABLE "spider__assets_maintenance"."Assets" (
    "asset_id" BIGINT NOT NULL,
    "maintenance_contract_id" BIGINT NOT NULL,
    "supplier_company_id" BIGINT NOT NULL,
    "asset_details" TEXT,
    "asset_make" TEXT,
    "asset_model" TEXT,
    "asset_acquired_date" TIMESTAMP,
    "asset_disposed_date" TIMESTAMP,
    "other_asset_details" TEXT,
    CONSTRAINT "Assets_pkey" PRIMARY KEY ("asset_id")
);

-- Table: Engineer_Skills
CREATE TABLE "spider__assets_maintenance"."Engineer_Skills" (
    "engineer_id" BIGINT NOT NULL,
    "skill_id" BIGINT NOT NULL
);

-- Table: Engineer_Visits
CREATE TABLE "spider__assets_maintenance"."Engineer_Visits" (
    "engineer_visit_id" BIGINT NOT NULL,
    "contact_staff_id" BIGINT,
    "engineer_id" BIGINT NOT NULL,
    "fault_log_entry_id" BIGINT NOT NULL,
    "fault_status" TEXT NOT NULL,
    "visit_start_datetime" TIMESTAMP,
    "visit_end_datetime" TIMESTAMP,
    "other_visit_details" TEXT,
    CONSTRAINT "Engineer_Visits_pkey" PRIMARY KEY ("engineer_visit_id")
);

-- Table: Fault_Log
CREATE TABLE "spider__assets_maintenance"."Fault_Log" (
    "fault_log_entry_id" BIGINT NOT NULL,
    "asset_id" BIGINT NOT NULL,
    "recorded_by_staff_id" BIGINT NOT NULL,
    "fault_log_entry_datetime" TIMESTAMP,
    "fault_description" TEXT,
    "other_fault_details" TEXT,
    CONSTRAINT "Fault_Log_pkey" PRIMARY KEY ("fault_log_entry_id")
);

-- Table: Fault_Log_Parts
CREATE TABLE "spider__assets_maintenance"."Fault_Log_Parts" (
    "fault_log_entry_id" BIGINT NOT NULL,
    "part_fault_id" BIGINT NOT NULL,
    "fault_status" TEXT NOT NULL
);

-- Table: Maintenance_Contracts
CREATE TABLE "spider__assets_maintenance"."Maintenance_Contracts" (
    "maintenance_contract_id" BIGINT NOT NULL,
    "maintenance_contract_company_id" BIGINT NOT NULL,
    "contract_start_date" TIMESTAMP,
    "contract_end_date" TIMESTAMP,
    "other_contract_details" TEXT,
    CONSTRAINT "Maintenance_Contracts_pkey" PRIMARY KEY ("maintenance_contract_id")
);

-- Table: Maintenance_Engineers
CREATE TABLE "spider__assets_maintenance"."Maintenance_Engineers" (
    "engineer_id" BIGINT NOT NULL,
    "company_id" BIGINT NOT NULL,
    "first_name" TEXT,
    "last_name" TEXT,
    "other_details" TEXT,
    CONSTRAINT "Maintenance_Engineers_pkey" PRIMARY KEY ("engineer_id")
);

-- Table: Part_Faults
CREATE TABLE "spider__assets_maintenance"."Part_Faults" (
    "part_fault_id" BIGINT NOT NULL,
    "part_id" BIGINT NOT NULL,
    "fault_short_name" TEXT,
    "fault_description" TEXT,
    "other_fault_details" TEXT,
    CONSTRAINT "Part_Faults_pkey" PRIMARY KEY ("part_fault_id")
);

-- Table: Parts
CREATE TABLE "spider__assets_maintenance"."Parts" (
    "part_id" BIGINT NOT NULL,
    "part_name" TEXT,
    "chargeable_yn" TEXT,
    "chargeable_amount" TEXT,
    "other_part_details" TEXT,
    CONSTRAINT "Parts_pkey" PRIMARY KEY ("part_id")
);

-- Table: Skills
CREATE TABLE "spider__assets_maintenance"."Skills" (
    "skill_id" BIGINT NOT NULL,
    "skill_code" TEXT,
    "skill_description" TEXT,
    CONSTRAINT "Skills_pkey" PRIMARY KEY ("skill_id")
);

-- Table: Skills_Required_To_Fix
CREATE TABLE "spider__assets_maintenance"."Skills_Required_To_Fix" (
    "part_fault_id" BIGINT NOT NULL,
    "skill_id" BIGINT NOT NULL
);

-- Table: Staff
CREATE TABLE "spider__assets_maintenance"."Staff" (
    "staff_id" BIGINT NOT NULL,
    "staff_name" TEXT,
    "gender" TEXT,
    "other_staff_details" TEXT,
    CONSTRAINT "Staff_pkey" PRIMARY KEY ("staff_id")
);

-- Table: Third_Party_Companies
CREATE TABLE "spider__assets_maintenance"."Third_Party_Companies" (
    "company_id" BIGINT NOT NULL,
    "company_type" TEXT NOT NULL,
    "company_name" TEXT,
    "company_address" TEXT,
    "other_company_details" TEXT,
    CONSTRAINT "Third_Party_Companies_pkey" PRIMARY KEY ("company_id")
);

-- Foreign Keys
ALTER TABLE "spider__assets_maintenance"."Asset_Parts" ADD CONSTRAINT "fk_Asset_Parts_0" FOREIGN KEY ("asset_id") REFERENCES "spider__assets_maintenance"."Assets" ("asset_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Asset_Parts" ADD CONSTRAINT "fk_Asset_Parts_1" FOREIGN KEY ("part_id") REFERENCES "spider__assets_maintenance"."Parts" ("part_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Assets" ADD CONSTRAINT "fk_Assets_0" FOREIGN KEY ("supplier_company_id") REFERENCES "spider__assets_maintenance"."Third_Party_Companies" ("company_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Assets" ADD CONSTRAINT "fk_Assets_1" FOREIGN KEY ("maintenance_contract_id") REFERENCES "spider__assets_maintenance"."Maintenance_Contracts" ("maintenance_contract_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Engineer_Skills" ADD CONSTRAINT "fk_Engineer_Skills_0" FOREIGN KEY ("skill_id") REFERENCES "spider__assets_maintenance"."Skills" ("skill_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Engineer_Skills" ADD CONSTRAINT "fk_Engineer_Skills_1" FOREIGN KEY ("engineer_id") REFERENCES "spider__assets_maintenance"."Maintenance_Engineers" ("engineer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Engineer_Visits" ADD CONSTRAINT "fk_Engineer_Visits_0" FOREIGN KEY ("contact_staff_id") REFERENCES "spider__assets_maintenance"."Staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Engineer_Visits" ADD CONSTRAINT "fk_Engineer_Visits_1" FOREIGN KEY ("engineer_id") REFERENCES "spider__assets_maintenance"."Maintenance_Engineers" ("engineer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Engineer_Visits" ADD CONSTRAINT "fk_Engineer_Visits_2" FOREIGN KEY ("fault_log_entry_id") REFERENCES "spider__assets_maintenance"."Fault_Log" ("fault_log_entry_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Fault_Log" ADD CONSTRAINT "fk_Fault_Log_0" FOREIGN KEY ("recorded_by_staff_id") REFERENCES "spider__assets_maintenance"."Staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Fault_Log" ADD CONSTRAINT "fk_Fault_Log_1" FOREIGN KEY ("asset_id") REFERENCES "spider__assets_maintenance"."Assets" ("asset_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Fault_Log_Parts" ADD CONSTRAINT "fk_Fault_Log_Parts_0" FOREIGN KEY ("fault_log_entry_id") REFERENCES "spider__assets_maintenance"."Fault_Log" ("fault_log_entry_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Fault_Log_Parts" ADD CONSTRAINT "fk_Fault_Log_Parts_1" FOREIGN KEY ("part_fault_id") REFERENCES "spider__assets_maintenance"."Part_Faults" ("part_fault_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Maintenance_Contracts" ADD CONSTRAINT "fk_Maintenance_Contracts_0" FOREIGN KEY ("maintenance_contract_company_id") REFERENCES "spider__assets_maintenance"."Third_Party_Companies" ("company_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Maintenance_Engineers" ADD CONSTRAINT "fk_Maintenance_Engineers_0" FOREIGN KEY ("company_id") REFERENCES "spider__assets_maintenance"."Third_Party_Companies" ("company_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Part_Faults" ADD CONSTRAINT "fk_Part_Faults_0" FOREIGN KEY ("part_id") REFERENCES "spider__assets_maintenance"."Parts" ("part_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Skills_Required_To_Fix" ADD CONSTRAINT "fk_Skills_Required_To_Fix_0" FOREIGN KEY ("skill_id") REFERENCES "spider__assets_maintenance"."Skills" ("skill_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__assets_maintenance"."Skills_Required_To_Fix" ADD CONSTRAINT "fk_Skills_Required_To_Fix_1" FOREIGN KEY ("part_fault_id") REFERENCES "spider__assets_maintenance"."Part_Faults" ("part_fault_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
