-- ======================================
-- HR V3 - УПРОЩЕННАЯ МИГРАЦИЯ (БЕЗ FK)
-- ======================================

-- 1. Сначала создаем таблицы БЕЗ foreign keys
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,
    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,
    INDEX idx_company (company_id),
    INDEX idx_status (payment_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS diagnostic_results (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_session_id VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    diagnostic_id INT,
    diagnostic_type ENUM('riasec', 'voice', 'games', 'stress', 'full') NOT NULL,
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    scores_json JSON,
    insights_json JSON,
    recommendations_json JSON,
    INDEX idx_employee (employee_session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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),
    avg_skills DECIMAL(5,2),
    avg_potential DECIMAL(5,2),
    risk_percentage DECIMAL(5,2),
    retention_rate DECIMAL(5,2),
    growth_indicators JSON,
    total_employees INT DEFAULT 0,
    high_potential_count INT DEFAULT 0,
    high_risk_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_period (company_id, period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_development_plans (
    plan_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    goal_title VARCHAR(255) NOT NULL,
    goal_description TEXT,
    week_number INT NOT NULL,
    checklist JSON,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    completion_percentage INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_interactions (
    interaction_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    interaction_type ENUM('meeting', 'feedback', 'diagnostic', 'coaching', 'review', 'training') NOT NULL,
    interaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    title VARCHAR(255),
    notes TEXT,
    outcome ENUM('positive', 'neutral', 'negative'),
    hr_user_id INT,
    attachments JSON,
    INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS diagnostic_templates (
    template_id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(255) NOT NULL,
    description TEXT,
    target_role VARCHAR(100),
    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,
    competencies_weights JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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;

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,
    target_employees JSON,
    target_departments JSON,
    deadline_date DATE,
    status ENUM('draft', 'active', 'completed', 'cancelled') DEFAULT 'draft',
    total_invited INT DEFAULT 0,
    total_completed INT DEFAULT 0,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recommended_microcourses (
    recommendation_id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(32) COLLATE utf8mb4_unicode_ci NOT NULL,
    course_name VARCHAR(255) NOT NULL,
    course_url TEXT,
    skill_to_improve VARCHAR(100),
    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,
    INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Обновляем существующие таблицы
-- Колонки price_yearly и features уже существуют, просто обновляем данные
UPDATE tariffs SET price_yearly = ROUND(price_monthly * 12 * 0.85, 2) WHERE price_yearly IS NULL OR price_yearly = 0;

-- 3. Теперь добавляем foreign keys
ALTER TABLE subscriptions ADD CONSTRAINT fk_subscriptions_company FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE;
ALTER TABLE subscriptions ADD CONSTRAINT fk_subscriptions_tariff FOREIGN KEY (tariff_id) REFERENCES tariffs(tariff_id);

ALTER TABLE diagnostic_results ADD CONSTRAINT fk_diagnostic_results_session FOREIGN KEY (employee_session_id) REFERENCES sessions(id) ON DELETE CASCADE;

ALTER TABLE kpi_analytics ADD CONSTRAINT fk_kpi_analytics_company FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE;

ALTER TABLE employee_development_plans ADD CONSTRAINT fk_dev_plans_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE;

ALTER TABLE employee_interactions ADD CONSTRAINT fk_interactions_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE;
ALTER TABLE employee_interactions ADD CONSTRAINT fk_interactions_hr FOREIGN KEY (hr_user_id) REFERENCES hr_users(hr_id) ON DELETE SET NULL;

ALTER TABLE diagnostics ADD CONSTRAINT fk_diagnostics_company FOREIGN KEY (company_id) REFERENCES companies(company_id) ON DELETE CASCADE;
ALTER TABLE diagnostics ADD CONSTRAINT fk_diagnostics_template FOREIGN KEY (template_id) REFERENCES diagnostic_templates(template_id) ON DELETE SET NULL;
ALTER TABLE diagnostics ADD CONSTRAINT fk_diagnostics_hr FOREIGN KEY (created_by) REFERENCES hr_users(hr_id) ON DELETE SET NULL;

ALTER TABLE recommended_microcourses ADD CONSTRAINT fk_microcourses_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE;

-- 4. Создаем views
CREATE OR REPLACE VIEW v_company_stats AS
SELECT 
    c.company_id,
    c.company_name,
    t.tariff_name,
    COUNT(DISTINCT ce.id) AS total_employees,
    c.license_count,
    ROUND((COUNT(DISTINCT ce.id) / NULLIF(c.license_count, 0)) * 100, 2) AS license_usage_percentage,
    COUNT(DISTINCT dr.result_id) AS total_diagnostics
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
LEFT JOIN diagnostic_results dr ON ce.session_id = dr.employee_session_id
GROUP BY c.company_id;

CREATE OR REPLACE VIEW v_high_risk_employees AS
SELECT 
    ce.id AS 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;

CREATE OR REPLACE VIEW v_promotion_candidates AS
SELECT 
    ce.id AS 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;

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
FROM diagnostics d
JOIN companies c ON d.company_id = c.company_id
WHERE d.status = 'active';

SELECT '✅ МИГРАЦИЯ V3 УСПЕШНО ЗАВЕРШЕНА!' AS status;

