-- Migration: Add monitoring fields to chat_messages and create new monitoring tables
-- Date: 2026-02-27
-- Issue: [AC-AISVC-91~AC-AISVC-95, AC-AISVC-108~AC-AISVC-110] Dashboard and monitoring enhancement

-- Add monitoring fields to chat_messages
ALTER TABLE chat_messages 
ADD COLUMN IF NOT EXISTS prompt_template_id UUID REFERENCES prompt_templates(id);

ALTER TABLE chat_messages 
ADD COLUMN IF NOT EXISTS intent_rule_id UUID REFERENCES intent_rules(id);

ALTER TABLE chat_messages 
ADD COLUMN IF NOT EXISTS flow_instance_id UUID REFERENCES flow_instances(id);

ALTER TABLE chat_messages 
ADD COLUMN IF NOT EXISTS guardrail_triggered BOOLEAN DEFAULT FALSE;

ALTER TABLE chat_messages 
ADD COLUMN IF NOT EXISTS guardrail_words JSONB;

-- Add indexes for monitoring queries
CREATE INDEX IF NOT EXISTS ix_chat_messages_tenant_template 
ON chat_messages (tenant_id, prompt_template_id);

CREATE INDEX IF NOT EXISTS ix_chat_messages_tenant_intent 
ON chat_messages (tenant_id, intent_rule_id);

CREATE INDEX IF NOT EXISTS ix_chat_messages_tenant_flow 
ON chat_messages (tenant_id, flow_instance_id);

CREATE INDEX IF NOT EXISTS ix_chat_messages_guardrail 
ON chat_messages (tenant_id, guardrail_triggered) 
WHERE guardrail_triggered = TRUE;

-- Create flow_test_records table
CREATE TABLE IF NOT EXISTS flow_test_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR NOT NULL,
    session_id VARCHAR NOT NULL,
    status VARCHAR DEFAULT 'success',
    steps JSONB NOT NULL DEFAULT '[]',
    final_response JSONB,
    total_duration_ms INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS ix_flow_test_records_tenant_created 
ON flow_test_records (tenant_id, created_at);

CREATE INDEX IF NOT EXISTS ix_flow_test_records_session 
ON flow_test_records (session_id);

-- Create export_tasks table
CREATE TABLE IF NOT EXISTS export_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR NOT NULL,
    status VARCHAR DEFAULT 'processing',
    file_path VARCHAR,
    file_name VARCHAR,
    file_size INTEGER,
    total_rows INTEGER,
    format VARCHAR DEFAULT 'json',
    filters JSONB,
    error_message TEXT,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS ix_export_tasks_tenant_status 
ON export_tasks (tenant_id, status);

CREATE INDEX IF NOT EXISTS ix_export_tasks_tenant_created 
ON export_tasks (tenant_id, created_at);
