-- ===================================
-- Skin Analysis Interactive System
-- Database Tables Creation
-- ===================================

-- 1. جدول جلسات التحليل
CREATE TABLE IF NOT EXISTS skin_analysis_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_token VARCHAR(64) UNIQUE NOT NULL,
    user_id INT NULL,
    user_name VARCHAR(100) NULL,
    user_email VARCHAR(100) NULL,
    user_phone VARCHAR(20) NULL,
    status ENUM('started', 'detecting', 'symptoms', 'completed', 'abandoned') DEFAULT 'started',
    current_step INT DEFAULT 1,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_session_token (session_token),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_started_at (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. جدول المشاكل المكتشفة
CREATE TABLE IF NOT EXISTS skin_concerns_detected (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    concern_type ENUM('moles', 'acne', 'dark_spots', 'redness', 'fine_lines', 'wrinkles', 'pores', 'dryness', 'oiliness') NOT NULL,
    concern_count INT DEFAULT 1,
    severity_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    position_x DECIMAL(5,2) NULL COMMENT 'X coordinate on face (0-100%)',
    position_y DECIMAL(5,2) NULL COMMENT 'Y coordinate on face (0-100%)',
    area_size DECIMAL(5,2) NULL COMMENT 'Size of affected area',
    confidence_score DECIMAL(3,2) DEFAULT 0.85 COMMENT 'Detection confidence (0-1)',
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES skin_analysis_sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_concern_type (concern_type),
    INDEX idx_severity (severity_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. جدول الأعراض الإضافية المتاحة
CREATE TABLE IF NOT EXISTS available_symptoms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    concern_type ENUM('moles', 'acne', 'dark_spots', 'redness', 'fine_lines', 'wrinkles', 'pores', 'dryness', 'oiliness') NOT NULL,
    symptom_name_en VARCHAR(100) NOT NULL,
    symptom_name_ar VARCHAR(100) NOT NULL,
    symptom_icon VARCHAR(50) NULL,
    severity_weight DECIMAL(3,2) DEFAULT 1.0 COMMENT 'Weight in severity calculation',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_concern_type (concern_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. جدول الأعراض المختارة من المستخدم
CREATE TABLE IF NOT EXISTS selected_symptoms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    concern_id INT NOT NULL,
    symptom_id INT NOT NULL,
    selected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES skin_analysis_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (concern_id) REFERENCES skin_concerns_detected(id) ON DELETE CASCADE,
    FOREIGN KEY (symptom_id) REFERENCES available_symptoms(id) ON DELETE CASCADE,
    UNIQUE KEY unique_selection (session_id, concern_id, symptom_id),
    INDEX idx_session_id (session_id),
    INDEX idx_concern_id (concern_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. جدول النتائج النهائية
CREATE TABLE IF NOT EXISTS analysis_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL UNIQUE,
    overall_score DECIMAL(5,2) NOT NULL COMMENT 'Overall health score (0-100)',
    severity_percentage DECIMAL(5,2) NOT NULL COMMENT 'Severity percentage shown to user',
    risk_level ENUM('low', 'moderate', 'high', 'critical') DEFAULT 'moderate',
    primary_concern VARCHAR(50) NULL,
    recommended_action TEXT NULL,
    notes TEXT NULL,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES skin_analysis_sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_risk_level (risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. جدول التوصيات (منتجات/خدمات/عيادات)
CREATE TABLE IF NOT EXISTS analysis_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    result_id INT NOT NULL,
    recommendation_type ENUM('product', 'service', 'clinic', 'general') NOT NULL,
    item_id INT NULL COMMENT 'ID of product/service/clinic',
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    priority INT DEFAULT 0 COMMENT 'Higher = more important',
    relevance_score DECIMAL(3,2) DEFAULT 0.8,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (result_id) REFERENCES analysis_results(id) ON DELETE CASCADE,
    INDEX idx_result_id (result_id),
    INDEX idx_type (recommendation_type),
    INDEX idx_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. جدول ربط المشاكل بالمنتجات (للتوصيات الذكية)
CREATE TABLE IF NOT EXISTS concern_product_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    concern_type ENUM('moles', 'acne', 'dark_spots', 'redness', 'fine_lines', 'wrinkles', 'pores', 'dryness', 'oiliness') NOT NULL,
    product_id INT NOT NULL,
    relevance_score DECIMAL(3,2) DEFAULT 0.8,
    priority INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_concern_type (concern_type),
    INDEX idx_product_id (product_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. جدول ربط المشاكل بالخدمات
CREATE TABLE IF NOT EXISTS concern_service_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    concern_type ENUM('moles', 'acne', 'dark_spots', 'redness', 'fine_lines', 'wrinkles', 'pores', 'dryness', 'oiliness') NOT NULL,
    service_id INT NOT NULL,
    relevance_score DECIMAL(3,2) DEFAULT 0.8,
    priority INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
    INDEX idx_concern_type (concern_type),
    INDEX idx_service_id (service_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. جدول الإحصائيات والتحليلات
CREATE TABLE IF NOT EXISTS analysis_statistics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    total_sessions INT DEFAULT 0,
    completed_sessions INT DEFAULT 0,
    abandoned_sessions INT DEFAULT 0,
    avg_completion_time INT NULL COMMENT 'Average time in seconds',
    most_common_concern VARCHAR(50) NULL,
    conversion_to_booking INT DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date (date),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- إدراج البيانات الأولية
-- ===================================

-- إدراج الأعراض المتاحة لكل نوع مشكلة
INSERT INTO available_symptoms (concern_type, symptom_name_en, symptom_name_ar, symptom_icon, severity_weight, display_order) VALUES
-- أعراض Moles
('moles', 'Itching', 'حكة', 'fa-hand-sparkles', 1.2, 1),
('moles', 'Fever', 'حمى', 'fa-temperature-high', 1.8, 2),
('moles', 'Pain', 'ألم', 'fa-hand-holding-medical', 1.5, 3),
('moles', 'Bleeding', 'نزيف', 'fa-droplet', 2.0, 4),
('moles', 'Growing', 'نمو', 'fa-arrow-up', 1.7, 5),
('moles', 'Color Change', 'تغير اللون', 'fa-palette', 1.6, 6),

-- أعراض Acne
('acne', 'Itching', 'حكة', 'fa-hand-sparkles', 1.1, 1),
('acne', 'Pain', 'ألم', 'fa-hand-holding-medical', 1.3, 2),
('acne', 'Swelling', 'تورم', 'fa-circle-dot', 1.4, 3),
('acne', 'Pus', 'صديد', 'fa-droplet', 1.6, 4),
('acne', 'Redness', 'احمرار', 'fa-circle', 1.2, 5),
('acne', 'Spreading', 'انتشار', 'fa-arrows-alt', 1.5, 6),

-- أعراض Dark Spots
('dark_spots', 'Itching', 'حكة', 'fa-hand-sparkles', 1.1, 1),
('dark_spots', 'Growing', 'نمو', 'fa-arrow-up', 1.4, 2),
('dark_spots', 'Texture Change', 'تغير الملمس', 'fa-hand', 1.3, 3),
('dark_spots', 'Darkening', 'اسمرار', 'fa-adjust', 1.5, 4),
('dark_spots', 'Spreading', 'انتشار', 'fa-arrows-alt', 1.4, 5),

-- أعراض Redness
('redness', 'Burning', 'حرقان', 'fa-fire', 1.4, 1),
('redness', 'Itching', 'حكة', 'fa-hand-sparkles', 1.2, 2),
('redness', 'Swelling', 'تورم', 'fa-circle-dot', 1.5, 3),
('redness', 'Dryness', 'جفاف', 'fa-wind', 1.1, 4),
('redness', 'Peeling', 'تقشر', 'fa-layer-group', 1.3, 5),

-- أعراض Fine Lines
('fine_lines', 'Dryness', 'جفاف', 'fa-wind', 1.2, 1),
('fine_lines', 'Deepening', 'تعمق', 'fa-arrows-v', 1.4, 2),
('fine_lines', 'Spreading', 'انتشار', 'fa-arrows-alt', 1.3, 3),
('fine_lines', 'Loss of Elasticity', 'فقدان المرونة', 'fa-hand', 1.5, 4);

-- ===================================
-- تحديث جدول database.sql الرئيسي
-- ===================================