CREATE TABLE IF NOT EXISTS hr_payroll.payroll_audit_events (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    event_id uuid NOT NULL DEFAULT gen_random_uuid(),
    event_type varchar(80) NOT NULL,
    category varchar(40) NOT NULL,
    risk_level varchar(20) NOT NULL DEFAULT 'low',
    payroll_run_id bigint NULL,
    employee_id bigint NULL,
    entity_type varchar(80) NOT NULL,
    entity_id varchar(120) NULL,
    entity_reference varchar(160) NULL,
    actor_user_id uuid NULL,
    actor_name varchar(180) NULL,
    actor_role varchar(120) NULL,
    action varchar(180) NOT NULL,
    reason text NULL,
    approval_reference varchar(160) NULL,
    source_ip inet NULL,
    correlation_id uuid NOT NULL DEFAULT gen_random_uuid(),
    before_state jsonb NOT NULL DEFAULT '{}'::jsonb,
    after_state jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    previous_hash varchar(64) NULL,
    event_hash varchar(64) NOT NULL,
    verification_status varchar(30) NOT NULL DEFAULT 'verified',
    occurred_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_payroll_audit_events_tenant_event UNIQUE (tenant_id, event_id),
    CONSTRAINT ck_payroll_audit_events_risk CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
    CONSTRAINT ck_payroll_audit_events_verification CHECK (verification_status IN ('verified', 'legacy_snapshot'))
);

CREATE INDEX IF NOT EXISTS idx_payroll_audit_events_tenant_time
    ON hr_payroll.payroll_audit_events (tenant_id, occurred_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_payroll_audit_events_run
    ON hr_payroll.payroll_audit_events (tenant_id, payroll_run_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_payroll_audit_events_employee
    ON hr_payroll.payroll_audit_events (tenant_id, employee_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_payroll_audit_events_category
    ON hr_payroll.payroll_audit_events (tenant_id, category, risk_level, occurred_at DESC);

CREATE OR REPLACE FUNCTION hr_payroll.prevent_payroll_audit_event_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION 'Payroll audit events are append-only';
END;
$$;

DROP TRIGGER IF EXISTS trg_payroll_audit_events_append_only ON hr_payroll.payroll_audit_events;
CREATE TRIGGER trg_payroll_audit_events_append_only
BEFORE UPDATE OR DELETE ON hr_payroll.payroll_audit_events
FOR EACH ROW EXECUTE FUNCTION hr_payroll.prevent_payroll_audit_event_mutation();

COMMENT ON TABLE hr_payroll.payroll_audit_events IS
    'Append-only, tenant-scoped evidence ledger for payroll governance events.';
COMMENT ON COLUMN hr_payroll.payroll_audit_events.verification_status IS
    'verified for server-originated evidence; legacy_snapshot for explicitly reconstructed historical records.';