-- Users table
CREATE TABLE `users` (
    `id` VARCHAR(36) PRIMARY KEY,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `username` VARCHAR(100) UNIQUE NOT NULL,
    `first_name` VARCHAR(100),
    `last_name` VARCHAR(100),
    `phone` VARCHAR(20),
    `date_of_birth` DATE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_email` (`email`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Visits table (tracks every user visit and analysis)
CREATE TABLE `visits` (
    `id` VARCHAR(36) PRIMARY KEY,
    `user_id` VARCHAR(36),
    `session_id` VARCHAR(255),
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `referrer` TEXT,
    `landing_page` VARCHAR(500),
    `device_type` ENUM('desktop', 'mobile', 'tablet') DEFAULT 'desktop',
    `browser` VARCHAR(100),
    `os` VARCHAR(100),
    `visit_type` ENUM('free_trial', 'paid', 'subscription') DEFAULT 'paid',
    `amount_charged` DECIMAL(10,2) DEFAULT 0.00,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `payment_status` ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    `payment_method` VARCHAR(50),
    `transaction_id` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_user_id` (`user_id`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_payment_status` (`payment_status`),
    CONSTRAINT `visits_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Visit images table (one image per visit for all analyses)
CREATE TABLE `visit_images` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `user_id` VARCHAR(36) NOT NULL,
    `visit_id` VARCHAR(36) NOT NULL UNIQUE,
    `file_path` VARCHAR(500) NOT NULL,
    `file_size` INTEGER,
    `mime_type` VARCHAR(50),
    `width` INTEGER,
    `height` INTEGER,
    `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_user_id` (`user_id`),
    KEY `idx_visit_id` (`visit_id`),
    CONSTRAINT `visit_images_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `visit_images_visit_fk` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Skin analysis table (comprehensive skin analysis)
CREATE TABLE `skin_analysis` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `visit_id` VARCHAR(36) NOT NULL UNIQUE,
    `user_id` VARCHAR(36) NOT NULL,
    `analysis_status` ENUM('success','no_face_detected','error') DEFAULT 'success',
    `analysis_message` TEXT DEFAULT NULL,
    
    -- Basic demographics
    `age_range` VARCHAR(200) DEFAULT NULL,
    `gender` VARCHAR(20) DEFAULT NULL,
    `face_shape` VARCHAR(50) DEFAULT NULL,
    
    -- Skin scores (0-100 scale)
    `appearance_score` DECIMAL(5,2) DEFAULT NULL,
    `wrinkles_score` DECIMAL(5,2) DEFAULT NULL,
    `skin_color_score` DECIMAL(5,2) DEFAULT NULL,
    `sebum_level_score` DECIMAL(5,2) DEFAULT NULL,
    `pores_level_score` DECIMAL(5,2) DEFAULT NULL,
    `acne_level_score` DECIMAL(5,2) DEFAULT NULL,
    `black_spots_score` DECIMAL(5,2) DEFAULT NULL,
    `dark_circles_score` DECIMAL(5,2) DEFAULT NULL,
    `pl_sensitivity_score` DECIMAL(5,2) DEFAULT NULL,
    `uv_acnes_score` DECIMAL(5,2) DEFAULT NULL,
    `uv_spots_score` DECIMAL(5,2) DEFAULT NULL,
    `pigment_score` DECIMAL(5,2) DEFAULT NULL,
    `collagen_level_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- Detailed text analysis
    `spots_on_skin` TEXT DEFAULT NULL,
    `pi_sensitivity` TEXT DEFAULT NULL,
    `sebum_balance` TEXT DEFAULT NULL,
    `consistency` TEXT DEFAULT NULL,
    `skin_tone_unevenness` TEXT DEFAULT NULL,
    `eye_position` TEXT DEFAULT NULL,
    `forehead_complexion` TEXT DEFAULT NULL,
    `mouth_shape` TEXT DEFAULT NULL,
    `oil_secretion` TEXT DEFAULT NULL,
    
    -- Summary scores
    `overall_score` DECIMAL(5,2) DEFAULT NULL,
    `confidence_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- AI metadata
    `ai_provider` VARCHAR(100) DEFAULT NULL,
    `ai_model_version` VARCHAR(50) DEFAULT NULL,
    `processing_time_ms` INTEGER DEFAULT NULL,
    `raw_analysis_data` JSON DEFAULT NULL,
    
    -- Timestamps
    `analyzed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_visit` (`visit_id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_analyzed_at` (`analyzed_at`),
    KEY `idx_analysis_status` (`analysis_status`),
    CONSTRAINT `skin_analysis_visit_fk` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE,
    CONSTRAINT `skin_analysis_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Hair analysis table (comprehensive hair analysis)
CREATE TABLE `hair_analysis` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `visit_id` VARCHAR(36) NOT NULL UNIQUE,
    `user_id` VARCHAR(36) NOT NULL,
    `analysis_status` ENUM('success','no_hair_detected','error') DEFAULT 'success',
    `analysis_message` TEXT DEFAULT NULL,
    
    -- Hair characteristics
    `hair_type` ENUM('straight','wavy','curly','coily') DEFAULT NULL,
    `hair_thickness` ENUM('fine','medium','thick') DEFAULT NULL,
    `hair_density` ENUM('low','medium','high') DEFAULT NULL,
    `hair_porosity` ENUM('low','medium','high') DEFAULT NULL,
    `hair_elasticity` ENUM('low','medium','high') DEFAULT NULL,
    `hair_texture` VARCHAR(100) DEFAULT NULL,
    `hair_color` VARCHAR(100) DEFAULT NULL,
    `hair_length` VARCHAR(50) DEFAULT NULL,
    
    -- Hair condition scores (0-100 scale)
    `overall_health_score` DECIMAL(5,2) DEFAULT NULL,
    `shine_score` DECIMAL(5,2) DEFAULT NULL,
    `moisture_score` DECIMAL(5,2) DEFAULT NULL,
    `damage_score` DECIMAL(5,2) DEFAULT NULL,
    `frizz_score` DECIMAL(5,2) DEFAULT NULL,
    `volume_score` DECIMAL(5,2) DEFAULT NULL,
    `scalp_health_score` DECIMAL(5,2) DEFAULT NULL,
    `breakage_score` DECIMAL(5,2) DEFAULT NULL,
    `split_ends_score` DECIMAL(5,2) DEFAULT NULL,
    `color_vibrancy_score` DECIMAL(5,2) DEFAULT NULL,
    `oil_balance_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- Hair problems detected
    `dandruff_detected` BOOLEAN DEFAULT FALSE,
    `hair_loss_detected` BOOLEAN DEFAULT FALSE,
    `chemical_damage_detected` BOOLEAN DEFAULT FALSE,
    `heat_damage_detected` BOOLEAN DEFAULT FALSE,
    `environmental_damage_detected` BOOLEAN DEFAULT FALSE,
    
    -- Detailed analysis
    `scalp_condition` TEXT DEFAULT NULL,
    `hair_concerns` TEXT DEFAULT NULL,
    `styling_issues` TEXT DEFAULT NULL,
    `recommended_treatments` TEXT DEFAULT NULL,
    `product_recommendations` TEXT DEFAULT NULL,
    
    -- Summary
    `overall_score` DECIMAL(5,2) DEFAULT NULL,
    `confidence_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- AI metadata
    `ai_provider` VARCHAR(100) DEFAULT NULL,
    `ai_model_version` VARCHAR(50) DEFAULT NULL,
    `processing_time_ms` INTEGER DEFAULT NULL,
    `raw_analysis_data` JSON DEFAULT NULL,
    
    -- Timestamps
    `analyzed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_visit` (`visit_id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_analyzed_at` (`analyzed_at`),
    CONSTRAINT `hair_analysis_visit_fk` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE,
    CONSTRAINT `hair_analysis_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Makeup analysis table (comprehensive makeup analysis)
CREATE TABLE `makeup_analysis` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `visit_id` VARCHAR(36) NOT NULL UNIQUE,
    `user_id` VARCHAR(36) NOT NULL,
    `analysis_status` ENUM('success','no_face_detected','error') DEFAULT 'success',
    `analysis_message` TEXT DEFAULT NULL,
    
    -- Makeup presence and style
    `makeup_detected` BOOLEAN DEFAULT FALSE,
    `makeup_style` ENUM('natural','everyday','professional','dramatic','artistic','none') DEFAULT NULL,
    `occasion_suitability` VARCHAR(100) DEFAULT NULL,
    
    -- Face makeup scores (0-100 scale)
    `foundation_score` DECIMAL(5,2) DEFAULT NULL,
    `concealer_score` DECIMAL(5,2) DEFAULT NULL,
    `powder_score` DECIMAL(5,2) DEFAULT NULL,
    `blush_score` DECIMAL(5,2) DEFAULT NULL,
    `contour_score` DECIMAL(5,2) DEFAULT NULL,
    `highlight_score` DECIMAL(5,2) DEFAULT NULL,
    `overall_base_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- Eye makeup scores
    `eyeshadow_score` DECIMAL(5,2) DEFAULT NULL,
    `eyeliner_score` DECIMAL(5,2) DEFAULT NULL,
    `mascara_score` DECIMAL(5,2) DEFAULT NULL,
    `eyebrow_score` DECIMAL(5,2) DEFAULT NULL,
    `overall_eye_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- Lip makeup scores
    `lipstick_score` DECIMAL(5,2) DEFAULT NULL,
    `lipliner_score` DECIMAL(5,2) DEFAULT NULL,
    `lip_gloss_score` DECIMAL(5,2) DEFAULT NULL,
    `overall_lip_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- Technical aspects
    `blending_quality` DECIMAL(5,2) DEFAULT NULL,
    `symmetry_score` DECIMAL(5,2) DEFAULT NULL,
    `color_harmony_score` DECIMAL(5,2) DEFAULT NULL,
    `skin_tone_match_score` DECIMAL(5,2) DEFAULT NULL,
    `longevity_prediction` VARCHAR(50) DEFAULT NULL,
    
    -- Detailed analysis
    `foundation_shade_match` TEXT DEFAULT NULL,
    `color_palette_used` TEXT DEFAULT NULL,
    `techniques_detected` TEXT DEFAULT NULL,
    `improvement_areas` TEXT DEFAULT NULL,
    `product_recommendations` TEXT DEFAULT NULL,
    `technique_recommendations` TEXT DEFAULT NULL,
    
    -- Face shape and features
    `face_shape_for_makeup` VARCHAR(50) DEFAULT NULL,
    `eye_shape` VARCHAR(50) DEFAULT NULL,
    `lip_shape` VARCHAR(50) DEFAULT NULL,
    `skin_undertone` ENUM('warm','cool','neutral') DEFAULT NULL,
    
    -- Summary
    `overall_score` DECIMAL(5,2) DEFAULT NULL,
    `confidence_score` DECIMAL(5,2) DEFAULT NULL,
    
    -- AI metadata
    `ai_provider` VARCHAR(100) DEFAULT NULL,
    `ai_model_version` VARCHAR(50) DEFAULT NULL,
    `processing_time_ms` INTEGER DEFAULT NULL,
    `raw_analysis_data` JSON DEFAULT NULL,
    
    -- Timestamps
    `analyzed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_visit` (`visit_id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_analyzed_at` (`analyzed_at`),
    CONSTRAINT `makeup_analysis_visit_fk` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE,
    CONSTRAINT `makeup_analysis_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Recommendations table (unified for all types)
CREATE TABLE `recommendations` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `user_id` VARCHAR(36) NOT NULL,
    `visit_id` VARCHAR(36) NOT NULL,
    `category` ENUM('skin','hair','makeup') NOT NULL,
    `analysis_id` INTEGER NOT NULL,
    `priority` ENUM('high','medium','low') DEFAULT 'medium',
    `recommendation_type` VARCHAR(50),
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `expected_improvement` TEXT,
    `time_frame` VARCHAR(100),
    `products_suggested` JSON,
    `techniques_suggested` JSON,
    `is_completed` BOOLEAN DEFAULT FALSE,
    `completed_at` TIMESTAMP NULL,
    `user_feedback` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_user_id` (`user_id`),
    KEY `idx_visit_id` (`visit_id`),
    KEY `idx_category` (`category`),
    KEY `idx_priority` (`priority`),
    CONSTRAINT `recommendations_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `recommendations_visit_fk` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Progress tracking table
CREATE TABLE `progress_tracking` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `user_id` VARCHAR(36) NOT NULL,
    `category` ENUM('skin','hair','makeup') NOT NULL,
    `metric_name` VARCHAR(100) NOT NULL,
    `metric_value` DECIMAL(5,2) NOT NULL,
    `visit_id` VARCHAR(36) NOT NULL,
    `analysis_id` INTEGER NOT NULL,
    `recorded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_user_category` (`user_id`, `category`),
    KEY `idx_metric` (`metric_name`),
    KEY `idx_recorded_at` (`recorded_at`),
    CONSTRAINT `progress_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `progress_visit_fk` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- User sessions
CREATE TABLE `user_sessions` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `user_id` VARCHAR(36) NOT NULL,
    `token` VARCHAR(255) UNIQUE NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `sessions_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Visit comparisons table (for tracking comparisons)
CREATE TABLE `visit_comparisons` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `user_id` VARCHAR(36) NOT NULL,
    `visit_id_1` VARCHAR(36) NOT NULL,
    `visit_id_2` VARCHAR(36) NOT NULL,
    `category` ENUM('skin','hair','makeup','all') NOT NULL,
    `comparison_data` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_user_id` (`user_id`),
    CONSTRAINT `comparisons_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `comparisons_visit1_fk` FOREIGN KEY (`visit_id_1`) REFERENCES `visits` (`id`) ON DELETE CASCADE,
    CONSTRAINT `comparisons_visit2_fk` FOREIGN KEY (`visit_id_2`) REFERENCES `visits` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Translations table (for dynamic content)
CREATE TABLE `translations` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `locale` VARCHAR(5) NOT NULL,
    `group` VARCHAR(100) NOT NULL,
    `key` VARCHAR(255) NOT NULL,
    `value` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `translations_unique` (`locale`, `group`, `key`),
    KEY `idx_locale` (`locale`),
    KEY `idx_group` (`group`)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- User language preferences
CREATE TABLE `user_language_preferences` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `user_id` VARCHAR(36) NOT NULL,
    `preferred_language` VARCHAR(5) NOT NULL DEFAULT 'en',
    `date_format` VARCHAR(20) DEFAULT 'MM/DD/YYYY',
    `currency_display` VARCHAR(10) DEFAULT 'USD',
    `measurement_system` ENUM('metric', 'imperial') DEFAULT 'metric',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `idx_user_id` (`user_id`),
    CONSTRAINT `user_lang_pref_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;