-- ======================================
-- HR-КАБИНЕТ LEVELUP - МИГРАЦИЯ V3
-- Дата создания: 2025-12-15
-- Описание: Новые таблицы согласно ТЗ v2.0
-- ======================================

-- 1. ТАБЛИЦА ПОДПИСОК (SUBSCRIPTION)
CREATE TABLE IF NOT EXISTS subscriptions (
    subscription_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    tariff_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    auto_renew BOOLEAN DEFAULT TRUE COMMENT 'Автоматическое продление',
    payment_status ENUM('active', 'pending', 'suspended') DEFAULT 'pending',
    last_payment_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id),
    INDEX idx_company (company_id),
    INDEX idx_status (payment_status),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Подписки компаний на тарифы';

-- 2. ТАБЛИЦА РЕЗУЛЬТАТОВ ДИАГНОСТИК (DIAGNOSTIC_RESULT)
CREATE TABLE IF NOT EXISTS diagnostic_results (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_session_id VARCHAR(255) NOT NULL COMMENT 'session_id сотрудника',
    diagnostic_id INT COMMENT 'Ссылка на запущенную диагностику',
    diagnostic_type ENUM('riasec', 'voice', 'games', 'stress', 'full') NOT NULL,
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    scores_json JSON COMMENT 'Баллы по всем параметрам {"R": 75, "I": 80, ...}',
    insights_json JSON COMMENT 'Инсайты и выводы AI',
    recommendations_json JSON COMMENT 'Рекомендации по развитию',
    FOREIGN KEY (employee_session_id) REFERENCES sessions(session_id) ON DELETE CASCADE,
    INDEX idx_employee (employee_session_id),
    INDEX idx_type (diagnostic_type),
    INDEX idx_completed (completed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Результаты диагностик сотрудников';

-- 3. ТАБЛИЦА KPI АНАЛИТИКИ (KPI_ANALYTICS)
CREATE TABLE IF NOT EXISTS kpi_analytics (
    analytics_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    department VARCHAR(100) COMMENT 'NULL для общей статистики компании',
    avg_skills DECIMAL(5,2) COMMENT 'Средний уровень навыков 0-100',
    avg_potential DECIMAL(5,2) COMMENT 'Средний потенциал 0-100',
    risk_percentage DECIMAL(5,2) COMMENT 'Процент сотрудников в зоне риска',
    retention_rate DECIMAL(5,2) COMMENT 'Коэффициент удержания 0-100',
    growth_indicators JSON COMMENT 'Динамика изменений {"skills": +5, "potential": +3}',
    total_employees INT DEFAULT 0 COMMENT 'Всего сотрудников в выборке',
    high_potential_count INT DEFAULT 0 COMMENT 'Количество с высоким потенциалом (≥80%)',
    high_risk_count INT DEFAULT 0 COMMENT 'Количество в зоне риска (≥60%)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    INDEX idx_company_period (company_id, period_start, period_end),
    INDEX idx_department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Агрегированная KPI аналитика';

-- 4. ТАБЛИЦА ПЛАНОВ РАЗВИТИЯ НА 90 ДНЕЙ
CREATE TABLE IF NOT EXISTS employee_development_plans (
    plan_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(255) NOT NULL,
    goal_title VARCHAR(255) NOT NULL COMMENT 'Название цели',
    goal_description TEXT COMMENT 'Описание цели',
    week_number INT NOT NULL COMMENT '1-12 недель (90 дней)',
    checklist JSON COMMENT 'Чек-лист задач на неделю [{"task": "...", "completed": false}, ...]',
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    completion_percentage INT DEFAULT 0 COMMENT '0-100%',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_status (status),
    INDEX idx_week (week_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Планы развития сотрудников на 90 дней';

-- 5. ТАБЛИЦА ИСТОРИИ ВЗАИМОДЕЙСТВИЙ
CREATE TABLE IF NOT EXISTS employee_interactions (
    interaction_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(255) NOT NULL,
    interaction_type ENUM('meeting', 'feedback', 'diagnostic', 'coaching', 'review', 'training') NOT NULL,
    interaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    title VARCHAR(255) COMMENT 'Заголовок взаимодействия',
    notes TEXT COMMENT 'Заметки HR',
    outcome ENUM('positive', 'neutral', 'negative') COMMENT 'Результат взаимодействия',
    hr_user_id INT COMMENT 'Кто провел взаимодействие',
    attachments JSON COMMENT 'Прикрепленные файлы/ссылки',
    FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE,
    FOREIGN KEY (hr_user_id) REFERENCES hr_users(hr_id) ON DELETE SET NULL,
    INDEX idx_session (session_id),
    INDEX idx_type (interaction_type),
    INDEX idx_date (interaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='История взаимодействий HR с сотрудниками';

-- 6. ТАБЛИЦА ШАБЛОНОВ ДИАГНОСТИК
CREATE TABLE IF NOT EXISTS diagnostic_templates (
    template_id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(100) NOT NULL,
    description TEXT,
    target_role VARCHAR(100) COMMENT 'Целевая роль: менеджеры, продажи, общая',
    included_tests JSON COMMENT 'Какие тесты включены: {"riasec": true, "voice": true, "games": true, "stress": true}',
    competency_weights JSON COMMENT 'Веса компетенций: {"leadership": 0.3, "communication": 0.2, ...}',
    duration_minutes INT DEFAULT 30 COMMENT 'Примерная длительность в минутах',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (target_role),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Шаблоны диагностик';

-- Заполнение шаблонов диагностик
INSERT INTO diagnostic_templates (template_name, description, target_role, included_tests, competency_weights, duration_minutes) VALUES
('Базовая диагностика', 'Базовая оценка профессиональных склонностей и навыков', 'Общая', 
 '{"riasec": true, "voice": false, "games": true, "stress": false}',
 '{"analytical": 0.25, "creative": 0.25, "social": 0.25, "practical": 0.25}', 20),
 
('Диагностика для менеджеров', 'Расширенная оценка лидерских и управленческих компетенций', 'Менеджеры',
 '{"riasec": true, "voice": true, "games": true, "stress": true}',
 '{"leadership": 0.3, "communication": 0.25, "decision_making": 0.25, "stress_management": 0.2}', 35),
 
('Диагностика для продаж', 'Оценка коммуникативных навыков и стрессоустойчивости', 'Продажи',
 '{"riasec": true, "voice": true, "games": true, "stress": true}',
 '{"communication": 0.35, "persuasion": 0.25, "stress_management": 0.2, "energy": 0.2}', 30),
 
('Полная диагностика', 'Максимально детальная оценка всех компетенций', 'Общая',
 '{"riasec": true, "voice": true, "games": true, "stress": true}',
 '{"analytical": 0.15, "creative": 0.15, "social": 0.15, "leadership": 0.15, "stress_management": 0.15, "communication": 0.15, "practical": 0.1}', 45);

-- 7. ТАБЛИЦА ЗАПУЩЕННЫХ ДИАГНОСТИК
CREATE TABLE IF NOT EXISTS diagnostics (
    diagnostic_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    template_id INT,
    diagnostic_name VARCHAR(255) NOT NULL COMMENT 'Название диагностики (например "Оценка отдела продаж - Январь 2025")',
    target_employees JSON COMMENT 'Массив session_id сотрудников или "all" для всех',
    target_departments JSON COMMENT 'Массив названий отделов',
    deadline DATE COMMENT 'Крайний срок прохождения',
    reminder_sent BOOLEAN DEFAULT FALSE COMMENT 'Отправлено ли напоминание за 24 часа',
    status ENUM('draft', 'active', 'completed', 'cancelled') DEFAULT 'draft',
    participants_total INT DEFAULT 0 COMMENT 'Всего участников',
    participants_completed INT DEFAULT 0 COMMENT 'Завершили диагностику',
    created_by INT NOT NULL COMMENT 'ID HR-пользователя',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES diagnostic_templates(template_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES hr_users(hr_id) ON DELETE CASCADE,
    INDEX idx_company (company_id),
    INDEX idx_status (status),
    INDEX idx_deadline (deadline)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Запущенные диагностики компаний';

-- 8. ТАБЛИЦА РЕКОМЕНДОВАННЫХ МИКРОКУРСОВ
CREATE TABLE IF NOT EXISTS recommended_microcourses (
    microcourse_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(255) NOT NULL,
    course_title VARCHAR(255) NOT NULL,
    course_description TEXT,
    course_url VARCHAR(500) COMMENT 'Ссылка на курс',
    target_skill VARCHAR(100) COMMENT 'Навык для прокачки',
    duration_hours INT COMMENT 'Длительность в часах',
    provider VARCHAR(100) COMMENT 'Провайдер курса',
    price_usd DECIMAL(10,2) DEFAULT 0.00,
    priority ENUM('high', 'medium', 'low') DEFAULT 'medium',
    recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('recommended', 'started', 'completed', 'dismissed') DEFAULT 'recommended',
    FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_skill (target_skill),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Рекомендованные микрокурсы для сотрудников';

-- 9. ДОПОЛНИТЕЛЬНЫЕ ПОЛЯ В СУЩЕСТВУЮЩИХ ТАБЛИЦАХ
-- Добавляем price_yearly в tariffs
ALTER TABLE tariffs 
ADD COLUMN IF NOT EXISTS price_yearly DECIMAL(10,2) COMMENT 'Цена при годовой оплате' AFTER price_monthly;

-- Обновляем цены с учетом скидки 15%
UPDATE tariffs SET price_yearly = price_monthly * 12 * 0.85 WHERE price_yearly IS NULL;

-- Добавляем поле для автоподстановки названия компании
ALTER TABLE companies 
ADD COLUMN IF NOT EXISTS name_verified BOOLEAN DEFAULT FALSE COMMENT 'Проверено ли название по БИН' AFTER company_name;

-- Добавляем поле для хранения данных с API МИР КЗ
ALTER TABLE companies 
ADD COLUMN IF NOT EXISTS api_data JSON COMMENT 'Данные из API МИР КЗ' AFTER bin;

-- ======================================
-- ИНДЕКСЫ ДЛЯ ОПТИМИЗАЦИИ
-- ======================================

-- Составной индекс для быстрого поиска активных подписок
CREATE INDEX idx_active_subscriptions 
ON subscriptions(company_id, payment_status, end_date);

-- Индекс для поиска результатов диагностик по типу и дате
CREATE INDEX idx_diagnostic_results_search 
ON diagnostic_results(employee_session_id, diagnostic_type, completed_at);

-- Индекс для аналитики по периодам
CREATE INDEX idx_kpi_period_analysis 
ON kpi_analytics(company_id, department, period_start, period_end);

-- ======================================
-- TRIGGERS ДЛЯ АВТОМАТИЗАЦИИ
-- ======================================

-- Trigger: автоматическое обновление счетчика завершивших диагностику
DELIMITER //
CREATE TRIGGER IF NOT EXISTS update_diagnostic_completion
AFTER INSERT ON diagnostic_results
FOR EACH ROW
BEGIN
    UPDATE diagnostics 
    SET participants_completed = participants_completed + 1
    WHERE diagnostic_id = NEW.diagnostic_id;
    
    -- Автоматически завершаем диагностику, если все прошли
    UPDATE diagnostics 
    SET status = 'completed'
    WHERE diagnostic_id = NEW.diagnostic_id 
    AND participants_completed >= participants_total
    AND status = 'active';
END//
DELIMITER ;

-- Trigger: автоматическое обновление процента выполнения плана развития
DELIMITER //
CREATE TRIGGER IF NOT EXISTS update_plan_completion
BEFORE UPDATE ON employee_development_plans
FOR EACH ROW
BEGIN
    IF NEW.checklist IS NOT NULL THEN
        -- Вычисляем процент выполнения на основе checklist
        SET NEW.completion_percentage = (
            SELECT CAST(
                SUM(CASE WHEN JSON_EXTRACT(value, '$.completed') = true THEN 1 ELSE 0 END) * 100.0 / 
                JSON_LENGTH(NEW.checklist) AS UNSIGNED
            )
            FROM JSON_TABLE(NEW.checklist, '$[*]' COLUMNS(value JSON PATH '$')) AS jt
        );
        
        -- Если все задачи выполнены, меняем статус на completed
        IF NEW.completion_percentage = 100 THEN
            SET NEW.status = 'completed';
        ELSEIF NEW.completion_percentage > 0 THEN
            SET NEW.status = 'in_progress';
        END IF;
    END IF;
END//
DELIMITER ;

-- ======================================
-- VIEWS ДЛЯ УДОБНОЙ РАБОТЫ
-- ======================================

-- View: Активные подписки с информацией о тарифе
CREATE OR REPLACE VIEW v_active_subscriptions AS
SELECT 
    s.subscription_id,
    s.company_id,
    c.company_name,
    c.bin,
    t.tariff_name,
    t.price_monthly,
    t.price_yearly,
    s.start_date,
    s.end_date,
    s.payment_status,
    s.auto_renew,
    DATEDIFF(s.end_date, CURDATE()) as days_until_renewal
FROM subscriptions s
JOIN companies c ON s.company_id = c.company_id
JOIN tariffs t ON s.tariff_id = t.tariff_id
WHERE s.payment_status = 'active';

-- View: Статистика диагностик по компаниям
CREATE OR REPLACE VIEW v_diagnostics_stats AS
SELECT 
    d.company_id,
    c.company_name,
    COUNT(d.diagnostic_id) as total_diagnostics,
    SUM(CASE WHEN d.status = 'active' THEN 1 ELSE 0 END) as active_diagnostics,
    SUM(CASE WHEN d.status = 'completed' THEN 1 ELSE 0 END) as completed_diagnostics,
    SUM(d.participants_total) as total_participants,
    SUM(d.participants_completed) as total_completed,
    ROUND(SUM(d.participants_completed) * 100.0 / NULLIF(SUM(d.participants_total), 0), 2) as completion_rate
FROM diagnostics d
JOIN companies c ON d.company_id = c.company_id
GROUP BY d.company_id, c.company_name;

-- View: Сотрудники с планами развития
CREATE OR REPLACE VIEW v_employees_with_development AS
SELECT 
    ce.session_id,
    CONCAT(ce.first_name, ' ', ce.last_name) as full_name,
    ce.position,
    ce.department,
    ce.company_id,
    COUNT(DISTINCT edp.plan_id) as total_plans,
    SUM(CASE WHEN edp.status = 'completed' THEN 1 ELSE 0 END) as completed_plans,
    AVG(edp.completion_percentage) as avg_completion_percentage
FROM corporate_employees ce
LEFT JOIN employee_development_plans edp ON ce.session_id = edp.session_id
GROUP BY ce.session_id, ce.first_name, ce.last_name, ce.position, ce.department, ce.company_id;

-- View: Последние взаимодействия HR с сотрудниками
CREATE OR REPLACE VIEW v_recent_interactions AS
SELECT 
    ei.interaction_id,
    ei.session_id,
    CONCAT(ce.first_name, ' ', ce.last_name) as employee_name,
    ce.department,
    ei.interaction_type,
    ei.title,
    ei.interaction_date,
    ei.outcome,
    hr.full_name as hr_manager_name
FROM employee_interactions ei
JOIN corporate_employees ce ON ei.session_id = ce.session_id
LEFT JOIN hr_users hr ON ei.hr_user_id = hr.hr_id
ORDER BY ei.interaction_date DESC;

-- ======================================
-- ХРАНИМЫЕ ПРОЦЕДУРЫ
-- ======================================

-- Процедура: Расчет KPI аналитики за период
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS calculate_kpi_analytics(
    IN p_company_id INT,
    IN p_period_start DATE,
    IN p_period_end DATE,
    IN p_department VARCHAR(100)
)
BEGIN
    DECLARE v_avg_skills DECIMAL(5,2);
    DECLARE v_avg_potential DECIMAL(5,2);
    DECLARE v_total_employees INT;
    DECLARE v_high_potential_count INT;
    DECLARE v_high_risk_count INT;
    DECLARE v_risk_percentage DECIMAL(5,2);
    DECLARE v_retention_rate DECIMAL(5,2);
    
    -- Вычисляем метрики
    SELECT 
        AVG(COALESCE(potential_score, 0)),
        AVG(COALESCE(risk_score, 0)),
        COUNT(*),
        SUM(CASE WHEN potential_score >= 80 THEN 1 ELSE 0 END),
        SUM(CASE WHEN risk_score >= 60 THEN 1 ELSE 0 END)
    INTO 
        v_avg_potential,
        v_avg_skills,
        v_total_employees,
        v_high_potential_count,
        v_high_risk_count
    FROM corporate_employees
    WHERE company_id = p_company_id
    AND (p_department IS NULL OR department = p_department)
    AND last_diagnostic_date BETWEEN p_period_start AND p_period_end;
    
    SET v_risk_percentage = CASE 
        WHEN v_total_employees > 0 THEN (v_high_risk_count * 100.0 / v_total_employees)
        ELSE 0 
    END;
    
    SET v_retention_rate = 100 - v_risk_percentage;
    
    -- Вставляем или обновляем запись аналитики
    INSERT INTO kpi_analytics (
        company_id, period_start, period_end, department,
        avg_skills, avg_potential, risk_percentage, retention_rate,
        total_employees, high_potential_count, high_risk_count
    ) VALUES (
        p_company_id, p_period_start, p_period_end, p_department,
        v_avg_skills, v_avg_potential, v_risk_percentage, v_retention_rate,
        v_total_employees, v_high_potential_count, v_high_risk_count
    ) ON DUPLICATE KEY UPDATE
        avg_skills = v_avg_skills,
        avg_potential = v_avg_potential,
        risk_percentage = v_risk_percentage,
        retention_rate = v_retention_rate,
        total_employees = v_total_employees,
        high_potential_count = v_high_potential_count,
        high_risk_count = v_high_risk_count;
END//
DELIMITER ;

-- ======================================
-- КОММЕНТАРИИ К НОВЫМ ТАБЛИЦАМ
-- ======================================

-- subscriptions: Управление подписками компаний (период, статус, автопродление)
-- diagnostic_results: Результаты всех диагностик с JSON-данными
-- kpi_analytics: Агрегированная аналитика KPI за периоды
-- employee_development_plans: Индивидуальные планы развития на 90 дней
-- employee_interactions: История всех взаимодействий HR с сотрудниками
-- diagnostic_templates: Шаблоны диагностик для разных ролей
-- diagnostics: Запущенные диагностики с участниками и статусом
-- recommended_microcourses: Персональные рекомендации курсов для сотрудников

-- ======================================
-- ГОТОВО!
-- ======================================
-- Применить миграцию:
-- mysql -u root -p mvp_career < hr_tables_v3_migration.sql

