-- ============================================================================
-- Table 1: ai_chat_history
-- Purpose: Store conversation history between user and assistant
-- ============================================================================

CREATE TABLE IF NOT EXISTS ai_chat_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    session_id VARCHAR(50) NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    type VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    context VARCHAR(50) DEFAULT 'general',
    INDEX idx_user_id (user_id),
    INDEX idx_timestamp (timestamp)
);

-- ============================================================================
-- Table 2: ai_user_puja_sessions
-- Purpose: Track puja booking progress (ONE row per user+session+puja)
-- Key Change: UNIQUE constraint on (user_id, session_id, puja_type)
-- ============================================================================

CREATE TABLE ai_user_puja_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- User & Session identification
    user_id VARCHAR(50) NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    
    -- Puja details
    puja_type VARCHAR(50) NOT NULL,
    puja_name VARCHAR(100),
    
    -- Puja collection state
    current_question INT DEFAULT 0,
    collected_info JSON NOT NULL DEFAULT (JSON_OBJECT()),
    -- collected_info stores: name, location, date, phone_number, muhurtam, etc.
    
    completed BOOLEAN DEFAULT FALSE,
    kb_checked BOOLEAN DEFAULT FALSE,
    invalid_attempts INT DEFAULT 0,
    
    -- Phone collection tracking
    awaiting_phone BOOLEAN DEFAULT FALSE,
    phone_collected BOOLEAN DEFAULT FALSE,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- UNIQUE constraint: ONE row per user+session+puja combination
    UNIQUE KEY unique_user_session_puja (user_id, session_id, puja_type)
    
);

-- ============================================================================
-- Table 3: ai_user_intent_scores
-- Purpose: Track user engagement and purchase intent per session
-- Key Changes: 
--   - Removed score_history (not needed)
--   - Added puja_type field
--   - UNIQUE constraint on (user_id, session_id)
-- ============================================================================

CREATE TABLE ai_user_intent_scores (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- User & Session identification
    user_id VARCHAR(50) NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    
    -- Intent tracking
    intent_score INT DEFAULT 0 CHECK (intent_score >= 0 AND intent_score <= 100),
    intent_reason TEXT,
    
    -- Context
    puja_type VARCHAR(50),
    context_type VARCHAR(50) DEFAULT 'general',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- UNIQUE constraint: ONE score per user+session
    UNIQUE KEY unique_user_session (user_id, session_id)
    
);