BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.privacy_audit_logs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    event_no varchar(80) NOT NULL,
    occurred_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actor_user_id uuid NULL,
    action varchar(80) NOT NULL,
    module_name varchar(80) NOT NULL DEFAULT 'Privacy',
    resource_type varchar(100) NOT NULL,
    resource_id bigint NULL,
    target_reference varchar(180) NULL,
    description text NOT NULL,
    severity varchar(20) NOT NULL DEFAULT 'info',
    compliance_status varchar(30) NOT NULL DEFAULT 'compliant',
    ip_address varchar(80) NULL,
    user_agent text NULL,
    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,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_privacy_audit_logs_event_no
    ON hr_payroll.privacy_audit_logs (tenant_id, event_no);

CREATE INDEX IF NOT EXISTS idx_privacy_audit_logs_timeline
    ON hr_payroll.privacy_audit_logs (tenant_id, occurred_at DESC, id DESC);

CREATE INDEX IF NOT EXISTS idx_privacy_audit_logs_review
    ON hr_payroll.privacy_audit_logs (tenant_id, compliance_status, severity, action);

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cutehorse_app') THEN
        GRANT SELECT, INSERT
            ON TABLE hr_payroll.privacy_audit_logs
            TO cutehorse_app;
        GRANT USAGE, SELECT
            ON SEQUENCE hr_payroll.privacy_audit_logs_id_seq
            TO cutehorse_app;
    END IF;
END $$;

COMMIT;