-- AI module performance indexes
-- MySQL repeatable script: create each index only when the same index name does not already exist.
-- If an existing index uses the same name but different columns, compare it manually before changing it.

set @db_name = database();

set @sql = if (
    exists (
        select 1
        from information_schema.statistics
        where table_schema = @db_name
          and table_name = 'sys_ai_chat_session'
          and index_name = 'idx_sys_ai_chat_session_user_prompt_active'
    ),
    'select ''skip idx_sys_ai_chat_session_user_prompt_active''',
    'create index idx_sys_ai_chat_session_user_prompt_active on sys_ai_chat_session (tenant_id, user_id, prompt_code, deleted, status, pinned, last_message_time, id)'
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

set @sql = if (
    exists (
        select 1
        from information_schema.statistics
        where table_schema = @db_name
          and table_name = 'sys_ai_chat_message'
          and index_name = 'idx_sys_ai_chat_message_session_active_seq'
    ),
    'select ''skip idx_sys_ai_chat_message_session_active_seq''',
    'create index idx_sys_ai_chat_message_session_active_seq on sys_ai_chat_message (session_id, deleted, seq_no, id)'
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

set @sql = if (
    exists (
        select 1
        from information_schema.statistics
        where table_schema = @db_name
          and table_name = 'sys_ai_call_log'
          and index_name = 'idx_sys_ai_call_log_tenant_status'
    ),
    'select ''skip idx_sys_ai_call_log_tenant_status''',
    'create index idx_sys_ai_call_log_tenant_status on sys_ai_call_log (tenant_id, deleted, status, id)'
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

set @sql = if (
    exists (
        select 1
        from information_schema.statistics
        where table_schema = @db_name
          and table_name = 'sys_ai_mcp_call_log'
          and index_name = 'idx_sys_ai_mcp_call_log_call'
    ),
    'select ''skip idx_sys_ai_mcp_call_log_call''',
    'create index idx_sys_ai_mcp_call_log_call on sys_ai_mcp_call_log (call_log_id, tenant_id, id)'
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

set @sql = if (
    exists (
        select 1
        from information_schema.statistics
        where table_schema = @db_name
          and table_name = 'sys_ai_mcp_call_log'
          and index_name = 'idx_sys_ai_mcp_call_log_tenant_status'
    ),
    'select ''skip idx_sys_ai_mcp_call_log_tenant_status''',
    'create index idx_sys_ai_mcp_call_log_tenant_status on sys_ai_mcp_call_log (tenant_id, status, id)'
);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

-- Deployment note:
-- Clear legacy AI cache keys before the new version takes traffic to avoid stale payload compatibility issues.