CREATE TABLE IF NOT EXISTS external_import_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    data_source_id INT DEFAULT NULL,
    batch_code VARCHAR(60) NOT NULL,
    import_name VARCHAR(180) NOT NULL,
    import_type VARCHAR(80) DEFAULT 'beneficiary_registry',
    file_path VARCHAR(255) DEFAULT '',
    source_file_name VARCHAR(255) DEFAULT '',
    total_rows INT DEFAULT 0,
    success_rows INT DEFAULT 0,
    failed_rows INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'uploaded',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    processed_at DATETIME NULL,
    UNIQUE KEY uniq_external_import_batch_code (batch_code),
    INDEX idx_external_import_batches_tenant_status (tenant_id, status),
    INDEX idx_external_import_batches_source (data_source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS external_import_rows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_id INT NOT NULL,
    row_number INT NOT NULL,
    household_code VARCHAR(60) DEFAULT '',
    beneficiary_code VARCHAR(60) DEFAULT '',
    status VARCHAR(40) DEFAULT 'processed',
    message TEXT NULL,
    created_at DATETIME NULL,
    INDEX idx_external_import_rows_batch (batch_id),
    INDEX idx_external_import_rows_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(67, 'external_imports', 'View bulk external import workspace', 'external_imports.view'),
(68, 'external_imports', 'Manage bulk external import workspace', 'external_imports.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1,67),(1,68),(2,67),(2,68),(3,67),(3,68),(5,67),(5,68),(6,67),(7,67),(7,68),(8,67),(9,67),(11,67);