SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS lead_import_batches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_key VARCHAR(64) NOT NULL,
    import_name VARCHAR(180) NOT NULL,
    import_status VARCHAR(32) NOT NULL DEFAULT 'imported',
    total_leads INT UNSIGNED NOT NULL DEFAULT 0,
    created_leads INT UNSIGNED NOT NULL DEFAULT 0,
    updated_leads INT UNSIGNED NOT NULL DEFAULT 0,
    queued_leads INT UNSIGNED NOT NULL DEFAULT 0,
    error_count INT UNSIGNED NOT NULL DEFAULT 0,
    imported_by INT UNSIGNED NULL,
    default_broker_id INT UNSIGNED NULL,
    default_employee_id INT UNSIGNED NULL,
    imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_lead_import_batches_batch_key (batch_key),
    INDEX idx_lead_import_batches_status (import_status),
    INDEX idx_lead_import_batches_imported_at (imported_at),
    INDEX idx_lead_import_batches_imported_by (imported_by),
    CONSTRAINT fk_lead_import_batches_imported_by
        FOREIGN KEY (imported_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_import_batches_default_broker
        FOREIGN KEY (default_broker_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_import_batches_default_employee
        FOREIGN KEY (default_employee_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_import_batch_leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    batch_id INT UNSIGNED NOT NULL,
    lead_id INT UNSIGNED NOT NULL,
    import_action ENUM('created', 'updated') NOT NULL DEFAULT 'created',
    broker_allocated_at DATETIME NULL,
    broker_allocated_by INT UNSIGNED NULL,
    employee_allocated_at DATETIME NULL,
    employee_allocated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_lead_import_batch_leads_batch_lead (batch_id, lead_id),
    INDEX idx_lead_import_batch_leads_batch (batch_id),
    INDEX idx_lead_import_batch_leads_lead (lead_id),
    INDEX idx_lead_import_batch_leads_broker_alloc (batch_id, broker_allocated_at),
    INDEX idx_lead_import_batch_leads_employee_alloc (batch_id, employee_allocated_at),
    CONSTRAINT fk_lead_import_batch_leads_batch
        FOREIGN KEY (batch_id) REFERENCES lead_import_batches (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_import_batch_leads_lead
        FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_import_batch_leads_broker_user
        FOREIGN KEY (broker_allocated_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_lead_import_batch_leads_employee_user
        FOREIGN KEY (employee_allocated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;