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

-- Создание/обновление 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.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
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;

SELECT '✅ VIEWS СОЗДАНЫ УСПЕШНО!' AS status;

