-- Обновление цен для годовых тарифов
UPDATE tariffs SET price_yearly = ROUND(price_monthly * 12 * 0.85, 2) WHERE price_yearly IS NULL OR price_yearly = 0;

-- VIEW 1: Статистика компаний
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.licenses_total,
    c.licenses_used,
    ROUND((c.licenses_used / NULLIF(c.licenses_total, 0)) * 100, 2) AS license_usage_percentage,
    COUNT(DISTINCT dr.result_id) AS total_diagnostics,
    c.status AS company_status,
    c.next_payment_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
LEFT JOIN diagnostic_results dr ON ce.session_id = dr.employee_session_id
GROUP BY c.company_id;

-- VIEW 2: Сотрудники с высоким риском (используем данные из corporate_employees)
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,
    ce.potential_score,
    ce.risk_score,
    ce.last_diagnostic_date,
    c.company_id,
    c.company_name
FROM corporate_employees ce
JOIN companies c ON ce.company_id = c.company_id
WHERE ce.risk_score >= 60
ORDER BY ce.risk_score DESC;

-- VIEW 3: Кандидаты на повышение (используем данные из corporate_employees)
CREATE OR REPLACE VIEW v_promotion_candidates AS
SELECT 
    ce.id AS employee_id,
    ce.first_name,
    ce.last_name,
    ce.department,
    ce.position,
    ce.potential_score,
    ce.risk_score,
    ce.last_diagnostic_date,
    c.company_id,
    c.company_name
FROM corporate_employees ce
JOIN companies c ON ce.company_id = c.company_id
WHERE ce.potential_score >= 80 
  AND ce.risk_score < 30
ORDER BY ce.potential_score DESC;

-- VIEW 4: Сотрудники с активными планами развития
CREATE OR REPLACE VIEW v_employees_with_development AS
SELECT 
    ce.id AS employee_id,
    ce.first_name,
    ce.last_name,
    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,
    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.id;

-- VIEW 5: Недавние взаимодействия
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,
    c.company_id
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
LIMIT 100;

SELECT '✅ МИГРАЦИЯ V3 УСПЕШНО ЗАВЕРШЕНА!' AS status;
SELECT CONCAT('📊 Создано таблиц: ', COUNT(*)) AS tables_count 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mvp_career' 
  AND TABLE_NAME IN ('subscriptions', 'diagnostic_results', 'kpi_analytics', 'employee_development_plans', 'employee_interactions', 'diagnostic_templates', 'diagnostics', 'recommended_microcourses');

SELECT CONCAT('👁️ Создано views: ', COUNT(*)) AS views_count 
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'mvp_career' 
  AND TABLE_NAME LIKE 'v_%';

