BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.workflow_escalations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    escalation_no varchar(80) NOT NULL,
    source_type varchar(50) NOT NULL,
    source_id bigint NOT NULL,
    source_reference varchar(100) NOT NULL,
    subject varchar(255) NOT NULL,
    current_stage varchar(100) NOT NULL,
    current_owner_id uuid NULL,
    current_owner_name varchar(180) NULL,
    original_owner_name varchar(180) NULL,
    sla_hours integer NOT NULL,
    source_created_at timestamp NOT NULL,
    severity varchar(20) NOT NULL DEFAULT 'medium',
    escalation_reason text NOT NULL,
    status varchar(30) NOT NULL DEFAULT 'active',
    resolution_note text NULL,
    escalated_at timestamp NULL,
    escalated_by uuid NULL,
    resolved_at timestamp NULL,
    resolved_by uuid NULL,
    workflow_audit jsonb NOT NULL DEFAULT '[]'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    CONSTRAINT uq_workflow_escalation_source UNIQUE (tenant_id, source_type, source_id),
    CONSTRAINT uq_workflow_escalation_no UNIQUE (tenant_id, escalation_no)
);

CREATE INDEX IF NOT EXISTS idx_workflow_escalations_queue
    ON hr_payroll.workflow_escalations (tenant_id, status, severity, source_created_at)
    WHERE is_deleted = FALSE;

COMMIT;