BEGIN;

ALTER TABLE IF EXISTS hr_payroll.report_exports
    ADD COLUMN IF NOT EXISTS filters jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS preview_rows jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS generated_columns jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS executed_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS error_message text;

CREATE TABLE IF NOT EXISTS hr_payroll.report_execution_audit (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    report_definition_id bigint REFERENCES hr_payroll.report_definitions(id),
    report_export_id bigint REFERENCES hr_payroll.report_exports(id),
    action varchar(40) NOT NULL,
    filters jsonb NOT NULL DEFAULT '{}'::jsonb,
    row_count integer NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'completed',
    message text,
    executed_by uuid,
    executed_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_hr_report_execution_audit_tenant_report
    ON hr_payroll.report_execution_audit (tenant_id, report_definition_id, executed_at DESC);

CREATE INDEX IF NOT EXISTS idx_hr_report_execution_audit_tenant_export
    ON hr_payroll.report_execution_audit (tenant_id, report_export_id, executed_at DESC);

WITH permissions_to_seed(module_code, permission_code, permission_name, description) AS (
    VALUES
        ('hr-payroll', 'hr.reports.execute', 'Execute HR Reports', 'Allows previewing saved HR report definitions against live tenant data.'),
        ('hr-payroll', 'hr.reports.audit.view', 'View HR Report Audit', 'Allows viewing HR report execution and export audit history.')
),
inserted_permissions AS (
    INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
    SELECT module_code, permission_code, permission_name, description
    FROM permissions_to_seed seed
    WHERE NOT EXISTS (
        SELECT 1
        FROM iam.permissions existing
        WHERE existing.permission_code = seed.permission_code
    )
    RETURNING id, permission_code
),
all_permissions AS (
    SELECT id, permission_code FROM inserted_permissions
    UNION
    SELECT p.id, p.permission_code
    FROM iam.permissions p
    INNER JOIN permissions_to_seed seed ON seed.permission_code = p.permission_code
),
privileged_roles AS (
    SELECT id
    FROM iam.roles
    WHERE COALESCE(is_active, TRUE) = TRUE
      AND LOWER(REPLACE(COALESCE(code, name, ''), ' ', '_')) IN (
          'tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator',
          'hr_admin', 'hr-admin', 'hr_manager', 'human_resources_manager',
          'payroll_manager'
      )
)
INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT pr.id, ap.id
FROM privileged_roles pr
CROSS JOIN all_permissions ap
WHERE NOT EXISTS (
    SELECT 1
    FROM iam.role_permissions existing
    WHERE existing.role_id = pr.id
      AND existing.permission_id = ap.id
);

COMMIT;