-- ============================================================================
-- Karishye AI Database Schema
-- Three tables for managing puja bookings, intent tracking, and phone numbers
-- ============================================================================

-- Drop tables if they exist (use with caution in production!)
-- DROP TABLE IF EXISTS chat_history;
-- DROP TABLE IF EXISTS ai_user_phone_numbers;
-- DROP TABLE IF EXISTS ai_user_intent_scores;
-- DROP TABLE IF EXISTS ai_user_puja_sessions;

-- ============================================================================
-- Table 0: chat_history
-- Purpose: Store conversation history between user and assistant
-- ============================================================================

CREATE TABLE IF NOT EXISTS chat_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- User & Session identification
    user_id VARCHAR(50) NOT NULL,
    session_id VARCHAR(50) NOT NULL,
    
    -- Message details
    timestamp TIMESTAMP NOT NULL,
    type VARCHAR(20) NOT NULL, -- 'user' or 'assistant'
    message TEXT NOT NULL,
    context VARCHAR(50) DEFAULT 'general',
    
    -- Indexes for performance
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    INDEX idx_timestamp (timestamp),
    INDEX idx_user_session (user_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table 1: ai_user_puja_sessions
-- Purpose: Track puja booking progress
-- ============================================================================

CREATE TABLE IF NOT EXISTS 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()),
    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,
    
    -- Indexes for performance
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    INDEX idx_user_session (user_id, session_id),
    INDEX idx_user_session_puja (user_id, session_id, puja_type),
    INDEX idx_puja_type (puja_type),
    INDEX idx_completed (completed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table 2: ai_user_intent_scores
-- Purpose: Track user engagement and purchase intent per session
-- ============================================================================

CREATE TABLE IF NOT EXISTS 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 (optional - to track which puja this relates to)
    context_type VARCHAR(50) DEFAULT 'general',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    INDEX idx_user_session (user_id, session_id),
    INDEX idx_intent_score (intent_score),
    
    -- One intent score per user per session
    UNIQUE KEY unique_user_session (user_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table 3: ai_user_phone_numbers
-- Purpose: Securely store user phone numbers for puja bookings
-- ============================================================================

CREATE TABLE IF NOT EXISTS ai_user_phone_numbers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    -- User identification
    user_id VARCHAR(50) NOT NULL,
    session_id VARCHAR(100) NOT NULL,
    
    -- Phone number
    phone_number VARCHAR(20) NOT NULL,
    phone_country_code VARCHAR(5) DEFAULT '+91',
    
    -- Context - which puja this phone is for
    puja_type VARCHAR(50) NOT NULL,
    puja_id BIGINT,  -- Reference to ai_user_puja_sessions.id
    
    -- Verification status (optional future feature)
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP NULL,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_user_id (user_id),
    INDEX idx_session_id (session_id),
    INDEX idx_puja_id (puja_id),
    INDEX idx_phone_number (phone_number),
    
    -- One phone per user per session per puja
    UNIQUE KEY unique_user_session_puja (user_id, session_id, puja_type),
    
    -- Foreign key (optional - enable if needed)
    -- FOREIGN KEY (puja_id) REFERENCES ai_user_puja_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Verification queries
-- ============================================================================

-- Check if tables were created
SHOW TABLES LIKE 'chat_history';
SHOW TABLES LIKE 'ai_user_%';

-- Describe table structures
DESCRIBE chat_history;
DESCRIBE ai_user_puja_sessions;
DESCRIBE ai_user_intent_scores;
DESCRIBE ai_user_phone_numbers;

-- ============================================================================
-- Sample data (optional - for testing)
-- ============================================================================

-- Insert sample puja session
-- INSERT INTO ai_user_puja_sessions 
-- (user_id, session_id, puja_type, puja_name, current_question, collected_info)
-- VALUES 
-- ('test_user', 'test_session_001', 'ganapati_homam', 'Ganapati Homam', 2, 
--  JSON_OBJECT('name', 'Test User', 'gotra', 'Bharadwaja'));

-- Insert sample intent score
-- INSERT INTO ai_user_intent_scores
-- (user_id, session_id, intent_score, intent_reason, puja_type, context_type)
-- VALUES
-- ('test_user', 'test_session_001', 75, 'User showing high interest', 'ganapati_homam', 'puja');

-- Insert sample phone number
-- INSERT INTO ai_user_phone_numbers
-- (user_id, session_id, phone_number, puja_type)
-- VALUES
-- ('test_user', 'test_session_001', '9876543210', 'ganapati_homam');

-- ============================================================================
-- Useful queries for management
-- ============================================================================

-- Count records in each table
-- SELECT 'ai_user_puja_sessions' as table_name, COUNT(*) as count FROM ai_user_puja_sessions
-- UNION ALL
-- SELECT 'ai_user_intent_scores', COUNT(*) FROM ai_user_intent_scores
-- UNION ALL
-- SELECT 'ai_user_phone_numbers', COUNT(*) FROM ai_user_phone_numbers;

-- Get recent bookings
-- SELECT user_id, session_id, puja_name, completed, created_at
-- FROM ai_user_puja_sessions
-- ORDER BY created_at DESC
-- LIMIT 10;

-- Get high-intent users
-- SELECT user_id, session_id, intent_score, puja_type
-- FROM ai_user_intent_scores
-- WHERE intent_score > 70
-- ORDER BY intent_score DESC;

-- ============================================================================
-- Cleanup queries (use with EXTREME caution!)
-- ============================================================================

-- Delete test data
-- DELETE FROM ai_user_phone_numbers WHERE user_id LIKE 'test_%';
-- DELETE FROM ai_user_intent_scores WHERE user_id LIKE 'test_%';
-- DELETE FROM ai_user_puja_sessions WHERE user_id LIKE 'test_%';

-- ============================================================================
-- END OF SCRIPT
-- ============================================================================
