Nutrition Management Data Specifications

CREATE TABLE diet_orders (
    order_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    encounter_id          BIGINT NOT NULL,
    ordering_provider_id  BIGINT NOT NULL,
    diet_type_id          BIGINT NOT NULL,
    texture_modification  VARCHAR(50),
    fluid_restriction_ml  INTEGER,
    supplements           JSONB NOT NULL DEFAULT '[]'::jsonb,
    cultural_preference   VARCHAR(50),
    food_allergens        JSONB NOT NULL DEFAULT '[]'::jsonb,
    start_datetime        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_datetime          TIMESTAMPTZ,
    status                VARCHAR(20) NOT NULL DEFAULT 'active',
    order_datetime        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    entered_by            BIGINT NOT NULL,
    last_modified_by      BIGINT,
    cancel_reason         TEXT,
    uae_facility_id       BIGINT NOT NULL,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_diet_orders_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_diet_orders_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_diet_orders_provider
        FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_diet_orders_diet_type
        FOREIGN KEY (diet_type_id) REFERENCES diet_type_definitions(diet_type_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_diet_orders_entered_by
        FOREIGN KEY (entered_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_diet_orders_last_modified_by
        FOREIGN KEY (last_modified_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_diet_orders_facility
        FOREIGN KEY (uae_facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_diet_orders_status
        CHECK (status IN ('draft','active','on-hold','completed','cancelled')),
    CONSTRAINT chk_diet_orders_fluid_restriction
        CHECK (fluid_restriction_ml IS NULL OR fluid_restriction_ml >= 0),
    CONSTRAINT chk_diet_orders_end_after_start
        CHECK (end_datetime IS NULL OR end_datetime > start_datetime),
    CONSTRAINT chk_diet_orders_cancel_reason
        CHECK (status <> 'cancelled' OR cancel_reason IS NOT NULL)
);

CREATE INDEX idx_diet_orders_patient_active
    ON diet_orders(patient_id, status)
    WHERE status = 'active';

CREATE INDEX idx_diet_orders_encounter
    ON diet_orders(encounter_id);

CREATE INDEX idx_diet_orders_facility_meal
    ON diet_orders(uae_facility_id, start_datetime);

CREATE INDEX idx_diet_orders_diet_type
    ON diet_orders(diet_type_id);

CREATE TABLE meal_plans (
    plan_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id        BIGINT NOT NULL,
    encounter_id      BIGINT NOT NULL,
    diet_order_id     BIGINT NOT NULL,
    meal_date         DATE NOT NULL,
    meal_type         VARCHAR(20) NOT NULL,
    menu_items_json   JSONB NOT NULL DEFAULT '[]'::jsonb,
    calories_planned  NUMERIC(6,1),
    protein_planned   NUMERIC(5,1),
    status            VARCHAR(20) NOT NULL DEFAULT 'planned',
    generated_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    generated_by      BIGINT,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_meal_plans_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_meal_plans_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_meal_plans_diet_order
        FOREIGN KEY (diet_order_id) REFERENCES diet_orders(order_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_meal_plans_generated_by
        FOREIGN KEY (generated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_meal_plans_meal_type
        CHECK (meal_type IN ('breakfast','lunch','dinner','snack')),
    CONSTRAINT chk_meal_plans_status
        CHECK (status IN ('planned','sent-to-kitchen','served','cancelled')),
    CONSTRAINT chk_meal_plans_calories
        CHECK (calories_planned IS NULL OR calories_planned >= 0),
    CONSTRAINT chk_meal_plans_protein
        CHECK (protein_planned IS NULL OR protein_planned >= 0)
);

CREATE INDEX idx_meal_plans_patient_date
    ON meal_plans(patient_id, meal_date);

CREATE INDEX idx_meal_plans_encounter_date
    ON meal_plans(encounter_id, meal_date, meal_type);

CREATE INDEX idx_meal_plans_status
    ON meal_plans(status);

CREATE TABLE meal_service_records (
    service_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    plan_id                   BIGINT NOT NULL,
    patient_id                BIGINT NOT NULL,
    meal_type                 VARCHAR(20) NOT NULL,
    delivered_datetime        TIMESTAMPTZ,
    delivered_by              BIGINT,
    consumption_percentage    INTEGER,
    calories_delivered        NUMERIC(6,1),
    protein_delivered         NUMERIC(5,1),
    patient_satisfaction_score NUMERIC(2,1),
    notes                     TEXT,
    created_at                TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_meal_service_plan
        FOREIGN KEY (plan_id) REFERENCES meal_plans(plan_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_meal_service_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_meal_service_delivered_by
        FOREIGN KEY (delivered_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,

    CONSTRAINT chk_meal_service_meal_type
        CHECK (meal_type IN ('breakfast','lunch','dinner','snack')),
    CONSTRAINT chk_meal_service_consumption
        CHECK (consumption_percentage IS NULL OR (consumption_percentage >= 0 AND consumption_percentage <= 100)),
    CONSTRAINT chk_meal_service_calories
        CHECK (calories_delivered IS NULL OR calories_delivered >= 0),
    CONSTRAINT chk_meal_service_protein
        CHECK (protein_delivered IS NULL OR protein_delivered >= 0),
    CONSTRAINT chk_meal_service_satisfaction
        CHECK (patient_satisfaction_score IS NULL OR (patient_satisfaction_score >= 1.0 AND patient_satisfaction_score <= 5.0))
);

CREATE INDEX idx_meal_service_patient_date
    ON meal_service_records(patient_id, delivered_datetime);

CREATE INDEX idx_meal_service_plan
    ON meal_service_records(plan_id);

CREATE TABLE nutrition_assessments (
    assessment_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id         BIGINT NOT NULL,
    encounter_id       BIGINT NOT NULL,
    assessed_by        BIGINT NOT NULL,
    assessment_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    bmi                NUMERIC(5,2),
    weight_kg          NUMERIC(5,2),
    height_cm          NUMERIC(5,1),
    ideal_body_weight  NUMERIC(5,2),
    calorie_target     NUMERIC(6,1),
    protein_target     NUMERIC(5,1),
    malnutrition_risk  VARCHAR(20),
    findings           TEXT,
    care_plan_text     TEXT,
    lab_values_json    JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_nut_assess_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_nut_assess_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_nut_assess_provider
        FOREIGN KEY (assessed_by) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_nut_assess_bmi
        CHECK (bmi IS NULL OR bmi >= 0),
    CONSTRAINT chk_nut_assess_weight
        CHECK (weight_kg IS NULL OR weight_kg >= 0),
    CONSTRAINT chk_nut_assess_height
        CHECK (height_cm IS NULL OR height_cm >= 0),
    CONSTRAINT chk_nut_assess_ibw
        CHECK (ideal_body_weight IS NULL OR ideal_body_weight >= 0),
    CONSTRAINT chk_nut_assess_calorie_target
        CHECK (calorie_target IS NULL OR calorie_target >= 0),
    CONSTRAINT chk_nut_assess_protein_target
        CHECK (protein_target IS NULL OR protein_target >= 0),
    CONSTRAINT chk_nut_assess_malnutrition_risk
        CHECK (malnutrition_risk IS NULL OR malnutrition_risk IN ('low','moderate','high'))
);

CREATE INDEX idx_nut_assess_patient_date
    ON nutrition_assessments(patient_id, assessment_date);

CREATE INDEX idx_nut_assess_encounter
    ON nutrition_assessments(encounter_id);

CREATE TABLE nutrition_screening (
    screening_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    encounter_id          BIGINT NOT NULL,
    screened_by           BIGINT NOT NULL,
    screening_date        DATE NOT NULL DEFAULT CURRENT_DATE,
    tool_used             VARCHAR(30) NOT NULL,
    score                 INTEGER NOT NULL,
    risk_level            VARCHAR(20) NOT NULL,
    referred_to_dietitian BOOLEAN NOT NULL DEFAULT FALSE,
    referral_datetime     TIMESTAMPTZ,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_nut_screen_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_nut_screen_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_nut_screen_user
        FOREIGN KEY (screened_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_nut_screen_tool
        CHECK (tool_used IN ('NRS-2002','MUST','SGA')),
    CONSTRAINT chk_nut_screen_risk
        CHECK (risk_level IN ('low','moderate','high')),
    CONSTRAINT chk_nut_screen_score
        CHECK (score >= 0),
    CONSTRAINT chk_nut_screen_referral
        CHECK (referred_to_dietitian = FALSE OR referral_datetime IS NOT NULL)
);

CREATE INDEX idx_nut_screen_patient_date
    ON nutrition_screening(patient_id, screening_date);

CREATE INDEX idx_nut_screen_encounter
    ON nutrition_screening(encounter_id);

CREATE INDEX idx_nut_screen_risk
    ON nutrition_screening(risk_level);

CREATE TABLE menu_items (
    item_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    item_name_en         VARCHAR(150) NOT NULL,
    item_name_ar         VARCHAR(150) NOT NULL,
    category             VARCHAR(50) NOT NULL,
    calories             NUMERIC(6,1),
    protein_g            NUMERIC(5,1),
    carbs_g              NUMERIC(5,1),
    fat_g                NUMERIC(5,1),
    fiber_g              NUMERIC(5,1),
    sodium_mg            INTEGER,
    allergens            JSONB NOT NULL DEFAULT '[]'::jsonb,
    is_halal             BOOLEAN NOT NULL DEFAULT TRUE,
    is_vegetarian        BOOLEAN NOT NULL DEFAULT FALSE,
    is_vegan             BOOLEAN NOT NULL DEFAULT FALSE,
    texture_suitable_for VARCHAR(50),
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    uae_food_code        VARCHAR(50),
    created_at           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_menu_items_nutrients_nonnegative
        CHECK (
            (calories IS NULL OR calories >= 0) AND
            (protein_g IS NULL OR protein_g >= 0) AND
            (carbs_g IS NULL OR carbs_g >= 0) AND
            (fat_g IS NULL OR fat_g >= 0) AND
            (fiber_g IS NULL OR fiber_g >= 0) AND
            (sodium_mg IS NULL OR sodium_mg >= 0)
        )
);

CREATE INDEX idx_menu_items_active_category
    ON menu_items(is_active, category);

CREATE INDEX idx_menu_items_halal
    ON menu_items(is_halal);

CREATE INDEX idx_menu_items_name_en
    ON menu_items(item_name_en);

CREATE TABLE menu_cycles (
    cycle_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cycle_name        VARCHAR(100) NOT NULL,
    cycle_length_days INTEGER NOT NULL,
    facility_id       BIGINT NOT NULL,
    effective_date    DATE NOT NULL,
    diet_type_id      BIGINT NOT NULL,
    day_number        INTEGER NOT NULL,
    meal_type         VARCHAR(20) NOT NULL,
    menu_item_ids     TEXT NOT NULL,
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_menu_cycles_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_menu_cycles_diet_type
        FOREIGN KEY (diet_type_id) REFERENCES diet_type_definitions(diet_type_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_menu_cycles_cycle_length
        CHECK (cycle_length_days > 0),
    CONSTRAINT chk_menu_cycles_day_number
        CHECK (day_number >= 1 AND day_number <= cycle_length_days),
    CONSTRAINT chk_menu_cycles_meal_type
        CHECK (meal_type IN ('breakfast','lunch','dinner','snack'))
);

CREATE INDEX idx_menu_cycles_facility_diet_day_meal
    ON menu_cycles(facility_id, diet_type_id, day_number, meal_type)
    WHERE is_active = TRUE;

CREATE TABLE kitchen_production_orders (
    production_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    meal_date             DATE NOT NULL,
    meal_type             VARCHAR(20) NOT NULL,
    facility_id           BIGINT NOT NULL,
    total_trays           INTEGER NOT NULL DEFAULT 0,
    production_items_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    status                VARCHAR(20) NOT NULL DEFAULT 'planned',
    completed_datetime    TIMESTAMPTZ,
    generated_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

    CONSTRAINT chk_kitchen_prod_meal_type
        CHECK (meal_type IN ('breakfast','lunch','dinner','snack')),
    CONSTRAINT chk_kitchen_prod_total_trays
        CHECK (total_trays >= 0),
    CONSTRAINT chk_kitchen_prod_status
        CHECK (status IN ('planned','in-progress','completed','cancelled'))
);

CREATE INDEX idx_kitchen_prod_facility_date_meal
    ON kitchen_production_orders(facility_id, meal_date, meal_type);

CREATE INDEX idx_kitchen_prod_status
    ON kitchen_production_orders(status);

CREATE TABLE tray_tickets (
    ticket_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    production_id       BIGINT NOT NULL,
    patient_id          BIGINT NOT NULL,
    bed_location        VARCHAR(50) NOT NULL,
    diet_type           VARCHAR(100) NOT NULL,
    allergen_alerts     JSONB NOT NULL DEFAULT '[]'::jsonb,
    menu_items          JSONB NOT NULL DEFAULT '[]'::jsonb,
    special_instructions TEXT,
    barcode             VARCHAR(64) NOT NULL UNIQUE,
    status              VARCHAR(20) NOT NULL DEFAULT 'pending',
    assembled_datetime  TIMESTAMPTZ,
    delivered_datetime  TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_tray_tickets_production
        FOREIGN KEY (production_id) REFERENCES kitchen_production_orders(production_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_tray_tickets_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_tray_tickets_status
        CHECK (status IN ('pending','assembled','delivered','returned','cancelled'))
);

CREATE INDEX idx_tray_tickets_production
    ON tray_tickets(production_id);

CREATE INDEX idx_tray_tickets_patient
    ON tray_tickets(patient_id);

CREATE INDEX idx_tray_tickets_status
    ON tray_tickets(status);

CREATE TABLE food_allergen_alerts (
    alert_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id        BIGINT NOT NULL,
    allergen_type     VARCHAR(50) NOT NULL,
    allergen_detail   VARCHAR(255),
    source_allergy_id BIGINT,
    is_active         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_food_allergen_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_food_allergen_source
        FOREIGN KEY (source_allergy_id) REFERENCES patient_allergies(allergy_id)
        ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX idx_food_allergen_patient_active
    ON food_allergen_alerts(patient_id)
    WHERE is_active = TRUE;

CREATE INDEX idx_food_allergen_type
    ON food_allergen_alerts(allergen_type);

CREATE TABLE enteral_parenteral_orders (
    order_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id      BIGINT NOT NULL,
    encounter_id    BIGINT NOT NULL,
    nutrition_type  VARCHAR(20) NOT NULL,
    formula_name    VARCHAR(100),
    rate_ml_hr      NUMERIC(6,1),
    total_volume    INTEGER,
    calorie_target  NUMERIC(6,1),
    protein_target  NUMERIC(5,1),
    additives       JSONB NOT NULL DEFAULT '[]'::jsonb,
    start_datetime  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_datetime    TIMESTAMPTZ,
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    ordered_by      BIGINT NOT NULL,
    order_datetime  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_enpn_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_enpn_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_enpn_ordered_by
        FOREIGN KEY (ordered_by) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,

    CONSTRAINT chk_enpn_type
        CHECK (nutrition_type IN ('EN','TPN')),
    CONSTRAINT chk_enpn_rate
        CHECK (rate_ml_hr IS NULL OR rate_ml_hr >= 0),
    CONSTRAINT chk_enpn_volume
        CHECK (total_volume IS NULL OR total_volume >= 0),
    CONSTRAINT chk_enpn_calorie_target
        CHECK (calorie_target IS NULL OR calorie_target >= 0),
    CONSTRAINT chk_enpn_protein_target
        CHECK (protein_target IS NULL OR protein_target >= 0),
    CONSTRAINT chk_enpn_status
        CHECK (status IN ('draft','active','on-hold','completed','cancelled')),
    CONSTRAINT chk_enpn_end_after_start
        CHECK (end_datetime IS NULL OR end_datetime > start_datetime)
);

CREATE INDEX idx_enpn_patient_active
    ON enteral_parenteral_orders(patient_id, status)
    WHERE status = 'active';

CREATE INDEX idx_enpn_encounter
    ON enteral_parenteral_orders(encounter_id);

CREATE INDEX idx_enpn_type_status
    ON enteral_parenteral_orders(nutrition_type, status);

CREATE TABLE diet_type_definitions (
    diet_type_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    diet_name_en        VARCHAR(100) NOT NULL,
    diet_name_ar        VARCHAR(100) NOT NULL,
    description         TEXT,
    allowed_categories  TEXT,
    excluded_categories TEXT,
    texture_level       VARCHAR(50),
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX ux_diet_type_name_en
    ON diet_type_definitions(diet_name_en)
    WHERE is_active = TRUE;