-- WhatsApp CRM v22 - IA multimedia controlada
-- Crea log de audios/transcripciones y análisis de comprobantes por IA.

CREATE TABLE IF NOT EXISTS whatsapp_ai_media_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    phone VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    media_type VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL,
    media_id VARCHAR(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    file_path TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    mime_type VARCHAR(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    transcript MEDIUMTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    result_json LONGTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    status VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_phone_created (phone, created_at),
    KEY idx_media_type_status (media_type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Columnas opcionales para controlar modelos multimedia sin romper instalaciones viejas.
SET @db := DATABASE();
SET @table := 'whatsapp_ai_settings';

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=@db AND table_name=@table)
    AND NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@db AND table_name=@table AND column_name='media_ai_enabled'),
    'ALTER TABLE whatsapp_ai_settings ADD COLUMN media_ai_enabled TINYINT(1) NOT NULL DEFAULT 1',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=@db AND table_name=@table)
    AND NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@db AND table_name=@table AND column_name='audio_transcription_model'),
    "ALTER TABLE whatsapp_ai_settings ADD COLUMN audio_transcription_model VARCHAR(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'gpt-4o-mini-transcribe'",
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema=@db AND table_name=@table)
    AND NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@db AND table_name=@table AND column_name='vision_model'),
    "ALTER TABLE whatsapp_ai_settings ADD COLUMN vision_model VARCHAR(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'gpt-4o-mini'",
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
