-- =============================================================================
-- Migration: 2025_10_28_add_ai_fields.sql
-- Purpose: Add new AI-related fields to the cloudprime_ai_settings table.
-- =============================================================================

-- Add 'business_hours_days' column (JSON array of days, e.g., ["Mon","Tue","Wed","Thu","Fri"])
SET @sql = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = DATABASE()
        AND table_name = 'cloudprime_ai_settings'
        AND column_name = 'business_hours_days'
    ) > 0,
    'SELECT "Column business_hours_days already exists."',
    CONCAT('ALTER TABLE cloudprime_ai_settings ADD COLUMN business_hours_days JSON NULL DEFAULT \'["Mon","Tue","Wed","Thu","Fri"]\' COMMENT \'JSON array of business days, e.g., [\"Mon\",\"Tue\",\"Wed\",\"Thu\",\"Fri\"]\' AFTER timezone;')
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add 'greeting_after_hours' column
SET @sql = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = DATABASE()
        AND table_name = 'cloudprime_ai_settings'
        AND column_name = 'greeting_after_hours'
    ) > 0,
    'SELECT "Column greeting_after_hours already exists."',
    'ALTER TABLE cloudprime_ai_settings ADD COLUMN greeting_after_hours TEXT NULL DEFAULT \'Thank you for calling. Our business hours are Monday to Friday, 9 AM to 5 PM. Please call back during business hours or leave a message.\' AFTER business_hours_days;'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add 'voice_tone' column
SET @sql = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = DATABASE()
        AND table_name = 'cloudprime_ai_settings'
        AND column_name = 'voice_tone'
    ) > 0,
    'SELECT "Column voice_tone already exists."',
    'ALTER TABLE cloudprime_ai_settings ADD COLUMN voice_tone VARCHAR(50) NULL DEFAULT \'professional\' COMMENT \'e.g., professional, friendly, urgent\' AFTER greeting_after_hours;'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;