CREATE TABLE IF NOT EXISTS hr_payroll.payroll_export_jobs (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    export_no VARCHAR(40) NOT NULL,
    payroll_run_id BIGINT NOT NULL REFERENCES hr_payroll.payroll_runs(id),
    export_type VARCHAR(40) NOT NULL,
    destination VARCHAR(160) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending_approval',
    requested_by UUID NULL,
    requested_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_by UUID NULL,
    approved_at TIMESTAMPTZ NULL,
    rejected_by UUID NULL,
    rejected_at TIMESTAMPTZ NULL,
    rejection_reason TEXT NULL,
    generated_by UUID NULL,
    generated_at TIMESTAMPTZ NULL,
    file_key VARCHAR(500) NULL,
    file_name VARCHAR(255) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT NULL,
    sha256_checksum CHAR(64) NULL,
    row_count INTEGER NOT NULL DEFAULT 0,
    total_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    currency_code VARCHAR(3) NOT NULL DEFAULT 'GHS',
    download_count INTEGER NOT NULL DEFAULT 0,
    last_downloaded_at TIMESTAMPTZ NULL,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID NULL,
    updated_by UUID NULL,
    CONSTRAINT uq_payroll_export_jobs_tenant_no UNIQUE (tenant_id, export_no),
    CONSTRAINT ck_payroll_export_jobs_type CHECK (export_type IN ('bank_ach_csv','gra_paye_csv','ssnit_tier1_txt','gl_journal_csv','payroll_json')),
    CONSTRAINT ck_payroll_export_jobs_status CHECK (status IN ('pending_approval','approved','rejected','generated','failed','revoked')),
    CONSTRAINT ck_payroll_export_jobs_checksum CHECK (sha256_checksum IS NULL OR sha256_checksum ~ '^[0-9a-f]{64}$')
);

CREATE INDEX IF NOT EXISTS idx_payroll_export_jobs_tenant_time
    ON hr_payroll.payroll_export_jobs (tenant_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_payroll_export_jobs_run
    ON hr_payroll.payroll_export_jobs (tenant_id, payroll_run_id, export_type);

CREATE TABLE IF NOT EXISTS hr_payroll.payroll_export_downloads (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    payroll_export_job_id BIGINT NOT NULL REFERENCES hr_payroll.payroll_export_jobs(id),
    downloaded_by UUID NULL,
    downloaded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    source_ip INET NULL,
    correlation_id UUID NULL,
    user_agent VARCHAR(500) NULL,
    sha256_checksum CHAR(64) NOT NULL,
    file_size BIGINT NOT NULL,
    created_by UUID NULL,
    CONSTRAINT ck_payroll_export_downloads_checksum CHECK (sha256_checksum ~ '^[0-9a-f]{64}$')
);

CREATE INDEX IF NOT EXISTS idx_payroll_export_downloads_job
    ON hr_payroll.payroll_export_downloads (tenant_id, payroll_export_job_id, downloaded_at DESC);

DROP TRIGGER IF EXISTS trg_payroll_export_jobs_payroll_audit ON hr_payroll.payroll_export_jobs;
CREATE TRIGGER trg_payroll_export_jobs_payroll_audit
AFTER INSERT OR UPDATE OR DELETE ON hr_payroll.payroll_export_jobs
FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event();

DROP TRIGGER IF EXISTS trg_payroll_export_downloads_payroll_audit ON hr_payroll.payroll_export_downloads;
CREATE TRIGGER trg_payroll_export_downloads_payroll_audit
AFTER INSERT ON hr_payroll.payroll_export_downloads
FOR EACH ROW EXECUTE FUNCTION hr_payroll.capture_payroll_audit_event();

INSERT INTO iam.permissions (module_code, permission_code, permission_name, description)
VALUES
('hr-payroll', 'hr.payroll.exports.view', 'View Payroll Exports', 'View tenant payroll export jobs and download evidence.'),
('hr-payroll', 'hr.payroll.exports.request', 'Request Payroll Exports', 'Request a governed payroll export.'),
('hr-payroll', 'hr.payroll.exports.approve', 'Approve Payroll Exports', 'Approve or reject payroll export requests.'),
('hr-payroll', 'hr.payroll.exports.generate', 'Generate Payroll Exports', 'Generate approved payroll export files.'),
('hr-payroll', 'hr.payroll.exports.download', 'Download Payroll Exports', 'Download generated payroll export files.')
ON CONFLICT (permission_code) DO UPDATE SET permission_name = EXCLUDED.permission_name, description = EXCLUDED.description;

INSERT INTO iam.role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM iam.roles r
JOIN iam.permissions p ON p.permission_code IN ('hr.payroll.exports.view','hr.payroll.exports.request','hr.payroll.exports.approve','hr.payroll.exports.generate','hr.payroll.exports.download')
WHERE LOWER(COALESCE(r.code, r.name, '')) IN ('tenant_owner','tenant-owner','owner','administrator','admin','payroll_manager','finance_manager','auditor','internal_auditor')
  AND NOT EXISTS (SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id = r.id AND rp.permission_id = p.id);

COMMENT ON TABLE hr_payroll.payroll_export_jobs IS 'Tenant-scoped governed payroll export requests and immutable generated-file evidence.';
COMMENT ON TABLE hr_payroll.payroll_export_downloads IS 'Append-only evidence of payroll export downloads.';