-- Migration V2: Extended B2C features
-- Adds: user data, corporate access, payments, development goals, micropractices, analytics

USE mvp_career;

-- ============================================
-- 1. EXTEND SESSIONS TABLE
-- ============================================
ALTER TABLE sessions 
ADD COLUMN user_type ENUM('anonymous', 'employee', 'hr') DEFAULT 'anonymous' AFTER id,
ADD COLUMN is_pro TINYINT(1) DEFAULT 0 AFTER user_type,
ADD COLUMN email VARCHAR(255) NULL AFTER is_pro,
ADD COLUMN phone VARCHAR(20) NULL AFTER email,
ADD INDEX idx_user_type (user_type),
ADD INDEX idx_is_pro (is_pro);

-- ============================================
-- 2. USER OPTIONAL DATA
-- ============================================
CREATE TABLE IF NOT EXISTS user_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(100) NULL,
    age INT NULL,
    city VARCHAR(100) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- ============================================
-- 3. CORPORATE COMPANIES
-- ============================================
CREATE TABLE IF NOT EXISTS companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bin VARCHAR(12) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    INDEX idx_bin (bin),
    INDEX idx_active (is_active)
);

-- ============================================
-- 4. CORPORATE EMPLOYEES
-- ============================================
CREATE TABLE IF NOT EXISTS corporate_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    company_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    position VARCHAR(150) NOT NULL,
    department VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company (company_id)
);

-- ============================================
-- 5. PAYMENTS (PRO ACCESS)
-- ============================================
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'KZT',
    payment_method ENUM('kaspi', 'freedom', 'card') NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    transaction_id VARCHAR(100) NULL,
    created_at DATETIME NOT NULL,
    completed_at DATETIME NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- ============================================
-- 6. USER DEVELOPMENT GOALS
-- ============================================
CREATE TABLE IF NOT EXISTS user_goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    goal_text VARCHAR(255) NOT NULL,
    is_completed TINYINT(1) DEFAULT 0,
    created_at DATETIME NOT NULL,
    completed_at DATETIME NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id)
);

-- ============================================
-- 7. MICROPRACTICE PROGRESS
-- ============================================
CREATE TABLE IF NOT EXISTS micropractice_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    practice_type ENUM('voice', 'presentation', 'challenge') NOT NULL,
    practice_date DATE NOT NULL,
    completed TINYINT(1) DEFAULT 0,
    score INT NULL COMMENT 'Progress points earned',
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_date (practice_date),
    UNIQUE KEY unique_practice (session_id, practice_type, practice_date)
);

-- ============================================
-- 8. ANALYTICS SNAPSHOTS (PRO FEATURE)
-- ============================================
CREATE TABLE IF NOT EXISTS analytics_snapshots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    snapshot_date DATE NOT NULL,
    overall_progress INT DEFAULT 0 COMMENT 'Total progress percentage',
    confidence_level INT DEFAULT 0,
    presentation_skill INT DEFAULT 0,
    creativity_level INT DEFAULT 0,
    voice_confidence INT DEFAULT 0,
    voice_clarity INT DEFAULT 0,
    voice_tempo INT DEFAULT 0,
    stress_index INT DEFAULT 0,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_date (snapshot_date),
    UNIQUE KEY unique_snapshot (session_id, snapshot_date)
);

-- ============================================
-- 9. SKILL RECOMMENDATIONS
-- ============================================
CREATE TABLE IF NOT EXISTS skill_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    profession_name VARCHAR(150) NOT NULL,
    skill_name VARCHAR(150) NOT NULL,
    skill_category ENUM('technical', 'soft', 'creative') NOT NULL,
    priority INT DEFAULT 1 COMMENT '1=high, 2=medium, 3=low',
    is_clicked TINYINT(1) DEFAULT 0,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id)
);

-- ============================================
-- 10. SHARED RESULTS (TRACKING)
-- ============================================
CREATE TABLE IF NOT EXISTS shared_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    share_platform ENUM('telegram', 'whatsapp', 'vk', 'instagram', 'link') NOT NULL,
    share_token VARCHAR(32) NOT NULL UNIQUE COMMENT 'Unique token for shared link',
    views_count INT DEFAULT 0,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_token (share_token)
);

-- ============================================
-- MIGRATION COMPLETE
-- ============================================

