SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND CONSTRAINT_NAME = 'fk_contract_data_lead'
      AND TABLE_NAME = 'lead_contract_data'
);

SET @drop_fk_sql := IF(
    @fk_exists > 0,
    'ALTER TABLE lead_contract_data DROP FOREIGN KEY fk_contract_data_lead;',
    'SELECT 1;'
);

PREPARE stmt FROM @drop_fk_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'lead_contract_data'
      AND INDEX_NAME = 'lead_id'
);

SET @drop_idx_sql := IF(
    @idx_exists > 0,
    'ALTER TABLE lead_contract_data DROP INDEX lead_id;',
    'SELECT 1;'
);

PREPARE stmt FROM @drop_idx_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @new_idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'lead_contract_data'
      AND INDEX_NAME = 'idx_contract_data_lead'
);

SET @add_idx_sql := IF(
    @new_idx_exists = 0,
    'ALTER TABLE lead_contract_data ADD INDEX idx_contract_data_lead (lead_id);',
    'SELECT 1;'
);

PREPARE stmt FROM @add_idx_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE lead_contract_data
    ADD CONSTRAINT fk_contract_data_lead
        FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE;
