SET @needs_rvx_sync_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND COLUMN_NAME = 'needs_rvx_sync'
);

SET @ddl := IF(
    @needs_rvx_sync_exists = 0,
    'ALTER TABLE leads ADD COLUMN needs_rvx_sync TINYINT(1) NOT NULL DEFAULT 0 AFTER last_exported_at',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @rvx_sync_status_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND COLUMN_NAME = 'rvx_sync_status'
);

SET @ddl := IF(
    @rvx_sync_status_exists = 0,
    'ALTER TABLE leads ADD COLUMN rvx_sync_status VARCHAR(32) NULL AFTER needs_rvx_sync',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @rvx_sync_locked_at_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND COLUMN_NAME = 'rvx_sync_locked_at'
);

SET @ddl := IF(
    @rvx_sync_locked_at_exists = 0,
    'ALTER TABLE leads ADD COLUMN rvx_sync_locked_at DATETIME NULL AFTER rvx_sync_status',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @rvx_sync_error_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND COLUMN_NAME = 'rvx_sync_error'
);

SET @ddl := IF(
    @rvx_sync_error_exists = 0,
    'ALTER TABLE leads ADD COLUMN rvx_sync_error TEXT NULL AFTER rvx_sync_locked_at',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @rvx_sync_attempts_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND COLUMN_NAME = 'rvx_sync_attempts'
);

SET @ddl := IF(
    @rvx_sync_attempts_exists = 0,
    'ALTER TABLE leads ADD COLUMN rvx_sync_attempts INT NOT NULL DEFAULT 0 AFTER rvx_sync_error',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @last_rvx_synced_at_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND COLUMN_NAME = 'last_rvx_synced_at'
);

SET @ddl := IF(
    @last_rvx_synced_at_exists = 0,
    'ALTER TABLE leads ADD COLUMN last_rvx_synced_at DATETIME NULL AFTER rvx_sync_attempts',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @needs_rvx_sync_index_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND INDEX_NAME = 'idx_leads_needs_rvx_sync'
);

SET @ddl := IF(
    @needs_rvx_sync_index_exists = 0,
    'ALTER TABLE leads ADD INDEX idx_leads_needs_rvx_sync (needs_rvx_sync, rvx_sync_status)',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @last_rvx_synced_index_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'leads'
      AND INDEX_NAME = 'idx_leads_last_rvx_synced'
);

SET @ddl := IF(
    @last_rvx_synced_index_exists = 0,
    'ALTER TABLE leads ADD INDEX idx_leads_last_rvx_synced (last_rvx_synced_at)',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
