-- Add operator-focused call details for CRM-like call qualification.
-- Compatible with older MySQL/MariaDB that do not support
-- `ADD COLUMN IF NOT EXISTS` in ALTER TABLE.

SET @db := DATABASE();

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'app_calls' AND COLUMN_NAME = 'client_name') = 0,
    'ALTER TABLE app_calls ADD COLUMN client_name VARCHAR(120) NULL AFTER called_phone',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'app_calls' AND COLUMN_NAME = 'client_address') = 0,
    'ALTER TABLE app_calls ADD COLUMN client_address VARCHAR(255) NULL AFTER client_name',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'app_calls' AND COLUMN_NAME = 'area_m2') = 0,
    'ALTER TABLE app_calls ADD COLUMN area_m2 DECIMAL(10,2) NULL AFTER client_address',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'app_calls' AND COLUMN_NAME = 'service_type') = 0,
    'ALTER TABLE app_calls ADD COLUMN service_type ENUM(''roofs'') NOT NULL DEFAULT ''roofs'' AFTER area_m2',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'app_calls' AND COLUMN_NAME = 'traffic_source') = 0,
    'ALTER TABLE app_calls ADD COLUMN traffic_source ENUM(''fb'',''google'',''other'') NOT NULL DEFAULT ''other'' AFTER service_type',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;