-- ======================================
-- 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)
-- Исправлено: session_id → id для FK к sessions
CREATE TABLE IF NOT EXISTS diagnostic_results (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_session_id VARCHAR(32) NOT NULL COMMENT 'id из таблицы sessions',
    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(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 ДНЕЙ
-- Исправлено: session_id → id для FK к sessions
CREATE TABLE IF NOT EXISTS employee_development_plans (
    plan_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(32) 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(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. ТАБЛИЦА ИСТОРИИ ВЗАИМОДЕЙСТВИЙ
-- Исправлено: session_id → id для FK к sessions
CREATE TABLE IF NOT EXISTS employee_interactions (
    interaction_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(32) 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(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(255) NOT NULL COMMENT 'Название шаблона',
    description TEXT COMMENT 'Описание шаблона',
    target_role VARCHAR(100) COMMENT 'Для кого: менеджеры, продажи, IT и т.д.',
    includes_riasec BOOLEAN DEFAULT TRUE,
    includes_voice BOOLEAN DEFAULT TRUE,
    includes_games BOOLEAN DEFAULT TRUE,
    includes_stress BOOLEAN DEFAULT FALSE,
    duration_minutes INT DEFAULT 15 COMMENT 'Примерная длительность',
    competencies_weights JSON COMMENT 'Веса компетенций {"communication": 30, "analytics": 20, ...}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (target_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Шаблоны диагностик';

-- Вставка шаблонов по умолчанию
INSERT INTO diagnostic_templates (template_name, description, target_role, includes_riasec, includes_voice, includes_games, includes_stress, duration_minutes, competencies_weights) VALUES
('Базовая диагностика', 'Стандартный тест RIASEC + голосовой анализ', 'Все', TRUE, TRUE, FALSE, FALSE, 10, '{"communication": 25, "analytics": 25, "creativity": 25, "stress_resistance": 25}'),
('Диагностика менеджеров', 'Расширенная диагностика для управленческих позиций', 'Менеджеры', TRUE, TRUE, TRUE, TRUE, 20, '{"leadership": 30, "communication": 25, "decision_making": 25, "stress_resistance": 20}'),
('Диагностика продаж', 'Фокус на коммуникации и стрессоустойчивости', 'Продажи', TRUE, TRUE, TRUE, TRUE, 15, '{"communication": 40, "persuasion": 30, "stress_resistance": 20, "empathy": 10}'),
('IT-специалисты', 'Аналитика и структурное мышление', 'IT', TRUE, TRUE, TRUE, FALSE, 15, '{"analytics": 40, "problem_solving": 30, "attention_to_detail": 20, "creativity": 10}')
ON DUPLICATE KEY UPDATE template_name=template_name;

-- 7. ТАБЛИЦА ЗАПУЩЕННЫХ ДИАГНОСТИК
CREATE TABLE IF NOT EXISTS diagnostics (
    diagnostic_id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    template_id INT COMMENT 'Ссылка на шаблон',
    diagnostic_name VARCHAR(255) NOT NULL COMMENT 'Название диагностики',
    target_employees JSON COMMENT 'Список session_id сотрудников или "all"',
    target_departments JSON COMMENT 'Список отделов или NULL для всех',
    deadline_date DATE COMMENT 'Дедлайн прохождения',
    status ENUM('draft', 'active', 'completed', 'cancelled') DEFAULT 'draft',
    total_invited INT DEFAULT 0 COMMENT 'Всего приглашено',
    total_completed INT DEFAULT 0 COMMENT 'Завершили',
    created_by INT COMMENT 'hr_user_id создателя',
    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 SET NULL,
    INDEX idx_company (company_id),
    INDEX idx_status (status),
    INDEX idx_deadline (deadline_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Запущенные диагностики для компаний';

-- 8. ТАБЛИЦА РЕКОМЕНДОВАННЫХ МИКРОКУРСОВ
-- Исправлено: session_id → id для FK к sessions
CREATE TABLE IF NOT EXISTS recommended_microcourses (
    recommendation_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(32) NOT NULL,
    course_name VARCHAR(255) NOT NULL,
    course_url TEXT COMMENT 'Ссылка на курс',
    skill_to_improve VARCHAR(100) COMMENT 'Навык для прокачки',
    priority ENUM('high', 'medium', 'low') DEFAULT 'medium',
    status ENUM('recommended', 'started', 'completed', 'skipped') DEFAULT 'recommended',
    recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_status (status),
    INDEX idx_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Рекомендованные микрокурсы для сотрудников';

-- ======================================
-- ОБНОВЛЕНИЕ СУЩЕСТВУЮЩИХ ТАБЛИЦ
-- ======================================

-- 9. ОБНОВЛЕНИЕ ТАБЛИЦЫ ТАРИФОВ (добавить годовую оплату)
-- Проверка и добавление колонки price_yearly
SET @dbname = DATABASE();
SET @tablename = 'tariffs';
SET @columnname = 'price_yearly';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DECIMAL(10,2) COMMENT "Цена при годовой оплате (со скидкой 15%)" AFTER price_monthly')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Проверка и добавление колонки features
SET @columnname = 'features';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' JSON COMMENT "Список возможностей в JSON формате" AFTER description')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Обновление цен для годовых тарифов (со скидкой 15%)
UPDATE tariffs SET price_yearly = price_monthly * 12 * 0.85 WHERE price_yearly IS NULL;

-- 10. ОБНОВЛЕНИЕ ТАБЛИЦЫ КОМПАНИЙ
SET @tablename = 'companies';

-- Добавление name_verified
SET @columnname = 'name_verified';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BOOLEAN DEFAULT FALSE COMMENT "Проверено ли название компании через API МИР КЗ" AFTER company_name')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Добавление api_data
SET @columnname = 'api_data';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' JSON COMMENT "Данные из API МИР КЗ" AFTER bin')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ======================================
-- СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ (VIEWS)
-- ======================================

-- VIEW 1: Статистика компаний
CREATE OR REPLACE VIEW v_company_stats AS
SELECT 
    c.company_id,
    c.company_name,
    t.tariff_name,
    COUNT(DISTINCT ce.employee_id) AS total_employees,
    c.license_count,
    ROUND((COUNT(DISTINCT ce.employee_id) / c.license_count) * 100, 2) AS license_usage_percentage,
    COUNT(DISTINCT dr.result_id) AS total_diagnostics,
    s.payment_status AS subscription_status,
    s.end_date AS subscription_end_date
FROM companies c
LEFT JOIN tariffs t ON c.tariff_id = t.tariff_id
LEFT JOIN corporate_employees ce ON c.company_id = ce.company_id AND ce.is_active = TRUE
LEFT JOIN diagnostic_results dr ON ce.session_id = dr.employee_session_id
LEFT JOIN subscriptions s ON c.company_id = s.company_id AND s.payment_status = 'active'
GROUP BY c.company_id;

-- VIEW 2: Сотрудники с высоким риском
CREATE OR REPLACE VIEW v_high_risk_employees AS
SELECT 
    ce.employee_id,
    ce.first_name,
    ce.last_name,
    ce.department,
    ce.position,
    ep.potential_score,
    ep.risk_score,
    ep.last_updated,
    c.company_id,
    c.company_name
FROM corporate_employees ce
JOIN employee_progress ep ON ce.session_id = ep.session_id
JOIN companies c ON ce.company_id = c.company_id
WHERE ep.risk_score >= 60
ORDER BY ep.risk_score DESC;

-- VIEW 3: Кандидаты на повышение
CREATE OR REPLACE VIEW v_promotion_candidates AS
SELECT 
    ce.employee_id,
    ce.first_name,
    ce.last_name,
    ce.department,
    ce.position,
    ep.potential_score,
    ep.risk_score,
    ep.growth_trend,
    c.company_id,
    c.company_name
FROM corporate_employees ce
JOIN employee_progress ep ON ce.session_id = ep.session_id
JOIN companies c ON ce.company_id = c.company_id
WHERE ep.potential_score >= 80 
  AND ep.risk_score < 30
  AND ep.growth_trend > 0
ORDER BY ep.potential_score DESC, ep.growth_trend DESC;

-- VIEW 4: Статистика по диагностикам
CREATE OR REPLACE VIEW v_diagnostics_stats AS
SELECT 
    d.diagnostic_id,
    d.diagnostic_name,
    d.company_id,
    c.company_name,
    d.status,
    d.total_invited,
    d.total_completed,
    ROUND((d.total_completed / NULLIF(d.total_invited, 0)) * 100, 2) AS completion_percentage,
    d.deadline_date,
    DATEDIFF(d.deadline_date, CURDATE()) AS days_until_deadline,
    CASE 
        WHEN DATEDIFF(d.deadline_date, CURDATE()) < 0 THEN 'overdue'
        WHEN DATEDIFF(d.deadline_date, CURDATE()) <= 2 THEN 'critical'
        WHEN DATEDIFF(d.deadline_date, CURDATE()) <= 7 THEN 'high'
        ELSE 'normal'
    END AS urgency_level
FROM diagnostics d
JOIN companies c ON d.company_id = c.company_id
WHERE d.status = 'active';

-- VIEW 5: Сотрудники с планами развития
CREATE OR REPLACE VIEW v_employees_with_development AS
SELECT 
    ce.employee_id,
    ce.first_name,
    ce.last_name,
    ce.department,
    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,
    MAX(edp.updated_at) AS last_update
FROM corporate_employees ce
LEFT JOIN employee_development_plans edp ON ce.session_id = edp.session_id
GROUP BY ce.employee_id;

-- VIEW 6: Недавние взаимодействия
CREATE OR REPLACE VIEW v_recent_interactions AS
SELECT 
    ei.interaction_id,
    ce.first_name,
    ce.last_name,
    ce.department,
    ei.interaction_type,
    ei.interaction_date,
    ei.title,
    ei.outcome,
    hr.email AS hr_email,
    c.company_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
JOIN companies c ON ce.company_id = c.company_id
ORDER BY ei.interaction_date DESC;

-- ======================================
-- ТРИГГЕРЫ
-- ======================================

-- ТРИГГЕР 1: Автоматическое обновление счетчика завершенных диагностик
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS update_diagnostic_completion
AFTER INSERT ON diagnostic_results
FOR EACH ROW
BEGIN
    UPDATE diagnostics 
    SET total_completed = (
        SELECT COUNT(DISTINCT dr.employee_session_id)
        FROM diagnostic_results dr
        WHERE dr.diagnostic_id = NEW.diagnostic_id
    )
    WHERE diagnostic_id = NEW.diagnostic_id;
END$$
DELIMITER ;

-- ТРИГГЕР 2: Автоматическое обновление процента выполнения плана
DELIMITER $$
CREATE TRIGGER IF NOT EXISTS update_plan_completion
BEFORE UPDATE ON employee_development_plans
FOR EACH ROW
BEGIN
    DECLARE completed_count INT;
    DECLARE total_count INT;
    
    -- Подсчет выполненных задач в JSON checklist
    IF NEW.checklist IS NOT NULL THEN
        SET completed_count = JSON_LENGTH(JSON_EXTRACT(NEW.checklist, '$[*].completed[?(@ == true)]'));
        SET total_count = JSON_LENGTH(NEW.checklist);
        
        IF total_count > 0 THEN
            SET NEW.completion_percentage = ROUND((completed_count / total_count) * 100);
        END IF;
    END IF;
END$$
DELIMITER ;

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

-- ПРОЦЕДУРА: Расчет 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_risk_percentage DECIMAL(5,2);
    DECLARE v_retention_rate DECIMAL(5,2);
    DECLARE v_total_employees INT;
    DECLARE v_high_potential_count INT;
    DECLARE v_high_risk_count INT;
    
    -- Расчет показателей
    SELECT 
        AVG(ep.skills_score),
        AVG(ep.potential_score),
        (SUM(CASE WHEN ep.risk_score >= 60 THEN 1 ELSE 0 END) / COUNT(*)) * 100,
        (SUM(CASE WHEN ep.risk_score < 60 THEN 1 ELSE 0 END) / COUNT(*)) * 100,
        COUNT(*),
        SUM(CASE WHEN ep.potential_score >= 80 THEN 1 ELSE 0 END),
        SUM(CASE WHEN ep.risk_score >= 60 THEN 1 ELSE 0 END)
    INTO 
        v_avg_skills,
        v_avg_potential,
        v_risk_percentage,
        v_retention_rate,
        v_total_employees,
        v_high_potential_count,
        v_high_risk_count
    FROM corporate_employees ce
    JOIN employee_progress ep ON ce.session_id = ep.session_id
    WHERE ce.company_id = p_company_id
      AND ce.is_active = TRUE
      AND (p_department IS NULL OR ce.department = p_department);
    
    -- Вставка или обновление записи в kpi_analytics
    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 ;

-- ======================================
-- ЗАВЕРШЕНИЕ МИГРАЦИИ
-- ======================================

