BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.onboarding_it_bundles (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    bundle_no varchar(80) NOT NULL,
    bundle_name varchar(160) NOT NULL,
    owner_role varchar(120) NOT NULL DEFAULT 'IT Helpdesk',
    items jsonb NOT NULL DEFAULT '[]'::jsonb,
    status varchar(40) NOT NULL DEFAULT 'active',
    description text NULL,
    sort_order integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_onboarding_it_bundles_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_onboarding_it_bundles_no
    ON hr_payroll.onboarding_it_bundles (tenant_id, bundle_no)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_onboarding_it_bundles_status
    ON hr_payroll.onboarding_it_bundles (tenant_id, status, sort_order)
    WHERE is_deleted = FALSE;

CREATE TABLE IF NOT EXISTS hr_payroll.onboarding_it_sla_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    rule_no varchar(80) NOT NULL,
    rule_name varchar(160) NOT NULL,
    item_type varchar(40) NOT NULL DEFAULT 'all',
    target_status varchar(40) NOT NULL DEFAULT 'Provisioned',
    due_offset_days integer NULL,
    threshold_percent numeric(5, 2) NOT NULL DEFAULT 100,
    severity varchar(40) NOT NULL DEFAULT 'normal',
    status varchar(40) NOT NULL DEFAULT 'active',
    description text NULL,
    sort_order integer NOT NULL DEFAULT 0,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_onboarding_it_sla_rules_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_onboarding_it_sla_rules_no
    ON hr_payroll.onboarding_it_sla_rules (tenant_id, rule_no)
    WHERE is_deleted = FALSE;

CREATE INDEX IF NOT EXISTS idx_onboarding_it_sla_rules_status
    ON hr_payroll.onboarding_it_sla_rules (tenant_id, status, sort_order)
    WHERE is_deleted = FALSE;

COMMIT;