-- v4 fixes for lead communications and occasions/deals UI
-- Run this once if v3 migration did not add lead_id correctly.

SET @db := DATABASE();
SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='communications' AND COLUMN_NAME='lead_id'
);
SET @sql := IF(@col_exists=0,
  'ALTER TABLE communications ADD COLUMN lead_id INT NULL AFTER source_type',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='communications' AND INDEX_NAME='idx_communications_lead_id'
);
SET @sql := IF(@idx_exists=0,
  'CREATE INDEX idx_communications_lead_id ON communications (lead_id)',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
