Cleaning Management Data Specifications

CREATE TABLE cleaning_zones (
    zone_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    facility_id          BIGINT NOT NULL,
    zone_name            VARCHAR(100) NOT NULL,
    floor                VARCHAR(20),
    wing                 VARCHAR(50),
    location_ids         JSONB,
    default_protocol_id  BIGINT,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_cleaning_zones_facility_name
        UNIQUE (facility_id, zone_name),

    CONSTRAINT fk_cleaning_zones_facility
        FOREIGN KEY (facility_id)
        REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_cleaning_zones_default_protocol
        FOREIGN KEY (default_protocol_id)
        REFERENCES cleaning_protocols(protocol_id)
        ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX idx_cleaning_zones_facility
    ON cleaning_zones(facility_id);

CREATE INDEX idx_cleaning_zones_active
    ON cleaning_zones(facility_id, is_active);

CREATE TABLE cleaning_staff (
    staff_id                       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id                        BIGINT NOT NULL,
    full_name                      VARCHAR(150) NOT NULL,
    full_name_ar                   VARCHAR(150),
    zone_id                        BIGINT,
    shift                          VARCHAR(50),
    certification_infection_control BOOLEAN NOT NULL DEFAULT FALSE,
    certification_biohazard        BOOLEAN NOT NULL DEFAULT FALSE,
    is_active                      BOOLEAN NOT NULL DEFAULT TRUE,
    phone                          VARCHAR(20),
    emirates_id                    VARCHAR(25),
    created_at                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cleaning_staff_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_cleaning_staff_zone
        FOREIGN KEY (zone_id)
        REFERENCES cleaning_zones(zone_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT uq_cleaning_staff_emirates_id
        UNIQUE (emirates_id),

    CONSTRAINT chk_cleaning_staff_shift
        CHECK (shift IS NULL OR shift IN (
            'morning', 'evening', 'night', 'split', 'on_call'
        )),

    CONSTRAINT chk_cleaning_staff_phone
        CHECK (phone IS NULL OR phone ~ '^\+971[0-9 ]+$')
);

CREATE INDEX idx_cleaning_staff_zone_active
    ON cleaning_staff(zone_id, is_active);

CREATE INDEX idx_cleaning_staff_user
    ON cleaning_staff(user_id);

CREATE TABLE cleaning_protocols (
    protocol_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    protocol_name             VARCHAR(150) NOT NULL,
    protocol_type             VARCHAR(50) NOT NULL,
    description               TEXT,
    estimated_duration_minutes INT,
    required_agents           JSONB,
    ppe_requirements          JSONB,
    checklist_id              BIGINT,
    is_active                 BOOLEAN NOT NULL DEFAULT TRUE,
    pathogen_codes            JSONB,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uq_cleaning_protocols_name
        UNIQUE (protocol_name),

    CONSTRAINT chk_cleaning_protocols_type
        CHECK (protocol_type IN (
            'standard', 'enhanced', 'terminal', 'or_turnover', 'biohazard'
        )),

    CONSTRAINT chk_cleaning_protocols_duration
        CHECK (estimated_duration_minutes IS NULL OR estimated_duration_minutes > 0),

    CONSTRAINT fk_cleaning_protocols_checklist
        FOREIGN KEY (checklist_id)
        REFERENCES cleaning_checklists(checklist_id)
        ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX idx_cleaning_protocols_type_active
    ON cleaning_protocols(protocol_type, is_active);

CREATE INDEX idx_cleaning_protocols_pathogen
    ON cleaning_protocols USING GIN (pathogen_codes);

CREATE TABLE cleaning_checklists (
    checklist_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    protocol_id    BIGINT NOT NULL,
    checklist_name VARCHAR(150) NOT NULL,
    items_count    INT NOT NULL DEFAULT 0,
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    notes          TEXT,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cleaning_checklists_protocol
        FOREIGN KEY (protocol_id)
        REFERENCES cleaning_protocols(protocol_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_cleaning_checklists_items_count
        CHECK (items_count >= 0)
);

CREATE INDEX idx_cleaning_checklists_protocol
    ON cleaning_checklists(protocol_id);

CREATE TABLE cleaning_checklist_items (
    item_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    checklist_id    BIGINT NOT NULL,
    item_description VARCHAR(255) NOT NULL,
    display_order   INT NOT NULL DEFAULT 0,
    is_mandatory    BOOLEAN NOT NULL DEFAULT TRUE,
    category        VARCHAR(50),
    criteria        JSONB,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT fk_cleaning_checklist_items_checklist
        FOREIGN KEY (checklist_id)
        REFERENCES cleaning_checklists(checklist_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT chk_cleaning_checklist_items_order
        CHECK (display_order >= 0)
);

CREATE INDEX idx_cleaning_checklist_items_checklist
    ON cleaning_checklist_items(checklist_id, display_order);

CREATE TABLE cleaning_schedules (
    schedule_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    zone_id       BIGINT NOT NULL,
    facility_id   BIGINT NOT NULL,
    frequency     VARCHAR(20) NOT NULL,
    day_of_week   VARCHAR(10),
    time_slot     VARCHAR(20) NOT NULL,
    protocol_id   BIGINT NOT NULL,
    assigned_team VARCHAR(100),
    is_active     BOOLEAN NOT NULL DEFAULT TRUE,
    start_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    end_date      DATE,
    exceptions    JSONB,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cleaning_schedules_zone
        FOREIGN KEY (zone_id)
        REFERENCES cleaning_zones(zone_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cleaning_schedules_facility
        FOREIGN KEY (facility_id)
        REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_cleaning_schedules_protocol
        FOREIGN KEY (protocol_id)
        REFERENCES cleaning_protocols(protocol_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_cleaning_schedules_frequency
        CHECK (frequency IN ('daily','weekly','monthly','quarterly','ad_hoc')),

    CONSTRAINT chk_cleaning_schedules_day_of_week
        CHECK (
            day_of_week IS NULL OR
            day_of_week IN ('sun','mon','tue','wed','thu','fri','sat')
        ),

    CONSTRAINT chk_cleaning_schedules_dates
        CHECK (end_date IS NULL OR end_date >= start_date)
);

CREATE INDEX idx_cleaning_schedules_zone_active
    ON cleaning_schedules(zone_id, is_active);

CREATE INDEX idx_cleaning_schedules_facility_freq
    ON cleaning_schedules(facility_id, frequency);

CREATE TABLE cleaning_tasks (
    task_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    task_type              VARCHAR(30) NOT NULL,
    facility_id            BIGINT NOT NULL,
    zone_id                BIGINT,
    location_id            BIGINT,
    bed_id                 BIGINT,
    encounter_id           BIGINT,
    priority               VARCHAR(20) NOT NULL DEFAULT 'routine',
    protocol_id            BIGINT NOT NULL,
    assigned_to            BIGINT,
    task_created_datetime  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    assigned_datetime      TIMESTAMP,
    started_datetime       TIMESTAMP,
    completed_datetime     TIMESTAMP,
    status                 VARCHAR(20) NOT NULL DEFAULT 'pending',
    trigger_source         VARCHAR(30) NOT NULL,
    inspection_required    BOOLEAN NOT NULL DEFAULT FALSE,
    inspection_result      VARCHAR(20),
    is_terminal_cleaning   BOOLEAN NOT NULL DEFAULT FALSE,
    infection_flag_source  VARCHAR(50),
    created_by             BIGINT,
    updated_by             BIGINT,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cleaning_tasks_facility
        FOREIGN KEY (facility_id)
        REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_cleaning_tasks_zone
        FOREIGN KEY (zone_id)
        REFERENCES cleaning_zones(zone_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_tasks_location
        FOREIGN KEY (location_id)
        REFERENCES locations(location_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_tasks_bed
        FOREIGN KEY (bed_id)
        REFERENCES beds(bed_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_tasks_encounter
        FOREIGN KEY (encounter_id)
        REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_tasks_protocol
        FOREIGN KEY (protocol_id)
        REFERENCES cleaning_protocols(protocol_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_cleaning_tasks_assigned_staff
        FOREIGN KEY (assigned_to)
        REFERENCES cleaning_staff(staff_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_tasks_created_by
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_tasks_updated_by
        FOREIGN KEY (updated_by)
        REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_cleaning_tasks_type
        CHECK (task_type IN (
            'bed_discharge','bed_transfer','or_turnover',
            'routine','terminal','ad_hoc'
        )),

    CONSTRAINT chk_cleaning_tasks_priority
        CHECK (priority IN ('stat','high','routine','low')),

    CONSTRAINT chk_cleaning_tasks_status
        CHECK (status IN ('pending','assigned','in_progress','completed','cancelled')),

    CONSTRAINT chk_cleaning_tasks_trigger_source
        CHECK (trigger_source IN (
            'adt_discharge','adt_transfer','or_case',
            'schedule','manual','infection_flag'
        )),

    CONSTRAINT chk_cleaning_tasks_inspection_result
        CHECK (inspection_result IS NULL OR inspection_result IN ('pass','fail')),

    CONSTRAINT chk_cleaning_tasks_timestamps
        CHECK (
            (assigned_datetime IS NULL OR assigned_datetime >= task_created_datetime) AND
            (started_datetime IS NULL OR started_datetime >= COALESCE(assigned_datetime, task_created_datetime)) AND
            (completed_datetime IS NULL OR completed_datetime >= COALESCE(started_datetime, task_created_datetime))
        )
);

CREATE INDEX idx_cleaning_tasks_facility_status
    ON cleaning_tasks(facility_id, status);

CREATE INDEX idx_cleaning_tasks_zone_status
    ON cleaning_tasks(zone_id, status);

CREATE INDEX idx_cleaning_tasks_bed_status
    ON cleaning_tasks(bed_id, status);

CREATE INDEX idx_cleaning_tasks_assigned_status
    ON cleaning_tasks(assigned_to, status);

CREATE INDEX idx_cleaning_tasks_created_datetime
    ON cleaning_tasks(task_created_datetime);

CREATE INDEX idx_cleaning_tasks_terminal
    ON cleaning_tasks(is_terminal_cleaning)
    WHERE is_terminal_cleaning = TRUE;

CREATE TABLE cleaning_inspections (
    inspection_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    task_id             BIGINT NOT NULL,
    location_id         BIGINT,
    inspector_id        BIGINT NOT NULL,
    inspection_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    score               NUMERIC(5,2),
    pass_fail           VARCHAR(10) NOT NULL,
    deficiencies        TEXT,
    photo_paths         JSONB,
    re_clean_required   BOOLEAN NOT NULL DEFAULT FALSE,
    re_clean_task_id    BIGINT,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cleaning_inspections_task
        FOREIGN KEY (task_id)
        REFERENCES cleaning_tasks(task_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cleaning_inspections_location
        FOREIGN KEY (location_id)
        REFERENCES locations(location_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT fk_cleaning_inspections_inspector
        FOREIGN KEY (inspector_id)
        REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT fk_cleaning_inspections_reclean_task
        FOREIGN KEY (re_clean_task_id)
        REFERENCES cleaning_tasks(task_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_cleaning_inspections_pass_fail
        CHECK (pass_fail IN ('pass','fail')),

    CONSTRAINT chk_cleaning_inspections_score
        CHECK (score IS NULL OR (score >= 0 AND score <= 100)),

    CONSTRAINT chk_cleaning_inspections_reclean
        CHECK (re_clean_required = FALSE OR re_clean_task_id IS NOT NULL)
);

CREATE INDEX idx_cleaning_inspections_task
    ON cleaning_inspections(task_id);

CREATE INDEX idx_cleaning_inspections_inspector
    ON cleaning_inspections(inspector_id);

CREATE INDEX idx_cleaning_inspections_pass_fail
    ON cleaning_inspections(pass_fail);

CREATE TABLE cleaning_supplies_usage (
    usage_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    task_id        BIGINT NOT NULL,
    supply_name    VARCHAR(150) NOT NULL,
    supply_code    VARCHAR(50),
    quantity_used  NUMERIC(10,3) NOT NULL,
    unit           VARCHAR(20) NOT NULL,
    lot_number     VARCHAR(50),
    expiry_date    DATE,
    supplier_name  VARCHAR(150),
    recorded_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    recorded_by    BIGINT,

    CONSTRAINT fk_cleaning_supplies_usage_task
        FOREIGN KEY (task_id)
        REFERENCES cleaning_tasks(task_id)
        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT fk_cleaning_supplies_usage_user
        FOREIGN KEY (recorded_by)
        REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_cleaning_supplies_usage_qty
        CHECK (quantity_used > 0)
);

CREATE INDEX idx_cleaning_supplies_usage_task
    ON cleaning_supplies_usage(task_id);

CREATE INDEX idx_cleaning_supplies_usage_supply
    ON cleaning_supplies_usage(supply_code);