-- v25 External Sync & Data Exchange
CREATE TABLE IF NOT EXISTS external_sync_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    data_source_id INT DEFAULT NULL,
    job_code VARCHAR(60) NOT NULL,
    job_name VARCHAR(180) NOT NULL,
    connection_mode VARCHAR(60) DEFAULT 'manual_upload',
    sync_direction VARCHAR(30) DEFAULT 'inbound',
    sync_scope VARCHAR(120) DEFAULT 'beneficiary_registry',
    target_module VARCHAR(120) DEFAULT 'registry',
    frequency VARCHAR(40) DEFAULT 'ad_hoc',
    endpoint_reference VARCHAR(255) DEFAULT '',
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    last_run_at DATETIME NULL,
    next_run_at DATETIME NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_external_sync_tenant_status (tenant_id, status),
    INDEX idx_external_sync_source (data_source_id),
    INDEX idx_external_sync_job_code (job_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS external_sync_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    sync_job_id INT NOT NULL,
    run_started_at DATETIME NULL,
    run_finished_at DATETIME NULL,
    records_received INT DEFAULT 0,
    records_processed INT DEFAULT 0,
    records_failed INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'success',
    message TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_external_sync_logs_job (sync_job_id),
    INDEX idx_external_sync_logs_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(69, 'external_sync', 'View external sync and exchange workspace', 'external_sync.view'),
(70, 'external_sync', 'Manage external sync and exchange workspace', 'external_sync.manage');