-- Database schema V2 for MVP Career Orientation Test
-- Full schema with B2C features: user data, corporate access, payments, development, analytics

CREATE DATABASE IF NOT EXISTS mvp_career CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mvp_career;

-- ============================================
-- CORE TABLES
-- ============================================

-- Sessions table (EXTENDED)
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(32) PRIMARY KEY,
    user_type ENUM('anonymous', 'guest', 'user', 'employee', 'hr') DEFAULT 'anonymous',
    is_pro TINYINT(1) DEFAULT 0,
    email VARCHAR(255) NULL,
    phone VARCHAR(20) NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_created_at (created_at),
    INDEX idx_user_type (user_type),
    INDEX idx_is_pro (is_pro)
);

-- 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
);

-- ============================================
-- CORPORATE TABLES
-- ============================================

-- 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)
);

-- 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)
);

-- ============================================
-- TEST DATA TABLES
-- ============================================

-- Answers table (RIASEC questions)
CREATE TABLE IF NOT EXISTS answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    question_id INT NOT NULL,
    answer_value INT NOT NULL,
    time_ms INT DEFAULT 0,
    created_at DATETIME NOT NULL,
    UNIQUE KEY unique_answer (session_id, question_id),
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Visual answers (IAT pairs)
CREATE TABLE IF NOT EXISTS visual_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    pair_id INT NOT NULL,
    choice ENUM('left', 'right') NOT NULL,
    time_ms INT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Reaction game answers
CREATE TABLE IF NOT EXISTS reaction_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL,
    round INT NOT NULL,
    selected_index INT NOT NULL,
    time_ms INT NOT NULL,
    correct TINYINT(1) DEFAULT 0,
    created_at DATETIME NOT NULL,
    INDEX idx_session (session_id),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Voice analysis
CREATE TABLE IF NOT EXISTS voice_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    analysis_data JSON,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Palm photo analysis
CREATE TABLE IF NOT EXISTS palm_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    analysis_data JSON,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Results table
CREATE TABLE IF NOT EXISTS results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(32) NOT NULL UNIQUE,
    computed_result_json JSON NOT NULL,
    created_at DATETIME NOT NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- ============================================
-- PAYMENT & MONETIZATION TABLES
-- ============================================

-- Payments
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)
);

-- 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)
);

-- ============================================
-- PRO FEATURES TABLES
-- ============================================

-- 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)
);

-- 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)
);

-- 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)
);

-- ============================================
-- SOCIAL SHARING TABLES
-- ============================================

-- 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)
);

-- ============================================
-- SCHEMA V2 COMPLETE
-- ============================================

