CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION hr_payroll.capture_payroll_audit_event()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = hr_payroll, public
AS $$
DECLARE
    v_row jsonb := CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END;
    v_before jsonb := CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) ELSE '{}'::jsonb END;
    v_after jsonb := CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE '{}'::jsonb END;
    v_tenant uuid := (v_row->>'tenant_id')::uuid;
    v_actor_text text := COALESCE(v_row->>'updated_by', v_row->>'created_by', v_row->>'approved_by', v_row->>'paid_by', v_row->>'published_by');
    v_actor uuid := CASE WHEN v_actor_text ~* '^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$' THEN v_actor_text::uuid END;
    v_run_id bigint := CASE WHEN COALESCE(v_row->>'payroll_run_id', '') ~ '^\d+$' THEN (v_row->>'payroll_run_id')::bigint END;
    v_employee_id bigint := CASE WHEN COALESCE(v_row->>'employee_id', '') ~ '^\d+$' THEN (v_row->>'employee_id')::bigint END;
    v_entity_id text := v_row->>'id';
    v_reference text := COALESCE(v_row->>'payroll_run_no', v_row->>'payslip_no', v_row->>'adjustment_no', v_row->>'exception_no', v_row->>'component_code', v_row->>'request_no', v_entity_id);
    v_event_type text := replace(TG_TABLE_NAME, '_', '.') || '.' || lower(TG_OP);
    v_category text := CASE
        WHEN TG_TABLE_NAME = 'payroll_runs' THEN CASE WHEN TG_OP = 'UPDATE' AND COALESCE(OLD.status, '') IS DISTINCT FROM COALESCE(NEW.status, '') THEN 'approval' ELSE 'payroll_run' END
        WHEN TG_TABLE_NAME = 'payroll_adjustments' THEN 'override'
        WHEN TG_TABLE_NAME IN ('payroll_exceptions', 'payroll_validation_issues') THEN 'validation'
        WHEN TG_TABLE_NAME = 'employee_self_service_change_requests' THEN 'master_data'
        WHEN TG_TABLE_NAME = 'payslips' THEN 'payslip'
        ELSE 'component'
    END;
    v_risk text := CASE
        WHEN TG_OP = 'DELETE' THEN 'high'
        WHEN TG_TABLE_NAME = 'payroll_adjustments' THEN 'high'
        WHEN TG_TABLE_NAME = 'payroll_runs' AND lower(COALESCE(v_row->>'status', '')) IN ('rejected', 'reopened', 'posted', 'paid') THEN 'high'
        WHEN TG_TABLE_NAME IN ('payroll_exceptions', 'payroll_validation_issues') THEN 'medium'
        ELSE 'low'
    END;
    v_previous_hash text;
    v_event_id uuid := gen_random_uuid();
    v_occurred_at timestamptz := CURRENT_TIMESTAMP;
    v_hash text;
BEGIN
    PERFORM pg_advisory_xact_lock(hashtext(v_tenant::text), hashtext('payroll_audit_events'));
    SELECT event_hash INTO v_previous_hash
      FROM hr_payroll.payroll_audit_events
     WHERE tenant_id = v_tenant
     ORDER BY occurred_at DESC, id DESC
     LIMIT 1;

    v_hash := encode(digest(concat_ws('|', v_previous_hash, v_event_id::text, v_tenant::text, v_event_type,
        TG_TABLE_NAME, v_entity_id, COALESCE(v_actor::text, ''), v_occurred_at::text,
        v_before::text, v_after::text), 'sha256'), 'hex');

    INSERT INTO hr_payroll.payroll_audit_events (
        tenant_id, event_id, event_type, category, risk_level, payroll_run_id, employee_id,
        entity_type, entity_id, entity_reference, actor_user_id, actor_name, actor_role,
        action, reason, approval_reference, source_ip, correlation_id, before_state,
        after_state, metadata, previous_hash, event_hash, verification_status, occurred_at
    ) VALUES (
        v_tenant, v_event_id, v_event_type, v_category, v_risk,
        CASE WHEN TG_TABLE_NAME = 'payroll_runs' THEN v_entity_id::bigint ELSE v_run_id END,
        v_employee_id, TG_TABLE_NAME, v_entity_id, v_reference, v_actor,
        COALESCE(v_row->>'updated_by_name', v_row->>'created_by_name', v_row->>'approved_by_name', v_row->>'paid_by_name'),
        NULL, initcap(replace(TG_TABLE_NAME, '_', ' ')) || ' ' || initcap(lower(TG_OP)),
        COALESCE(v_row->>'decision_reason', v_row->>'reason', v_row->>'notes'),
        COALESCE(v_row->>'approval_reference', v_row->>'accounting_journal_no', v_row->>'payment_journal_no'),
        NULL, gen_random_uuid(), v_before, v_after,
        jsonb_build_object('schema', TG_TABLE_SCHEMA, 'table', TG_TABLE_NAME, 'operation', TG_OP),
        v_previous_hash, v_hash, 'verified', v_occurred_at
    );
    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
END;
$$;

DO $$
DECLARE
    v_table text;
    v_tables text[] := ARRAY[
        'payroll_runs', 'payroll_run_items', 'payslips', 'payroll_adjustments',
        'payroll_exceptions', 'payroll_validation_issues', 'payroll_components',
        'employee_self_service_change_requests'
    ];
BEGIN
    FOREACH v_table IN ARRAY v_tables LOOP
        IF to_regclass('hr_payroll.' || v_table) IS NOT NULL THEN
            EXECUTE format('DROP TRIGGER IF EXISTS %I ON hr_payroll.%I', 'trg_' || v_table || '_payroll_audit', v_table);
            EXECUTE format(
                'CREATE TRIGGER %I AFTER INSERT OR UPDATE OR DELETE ON hr_payroll.%I FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event()',
                'trg_' || v_table || '_payroll_audit', v_table
            );
        END IF;
    END LOOP;
END;
$$;

REVOKE ALL ON FUNCTION hr_payroll.capture_payroll_audit_event() FROM PUBLIC;