/* VIEW translators_view */
CREATE OR REPLACE VIEW translators_view (
    id,
    user_id, 
    first_name, 
    photo, 
    gender, 
    birth_date, 
    birth_country_id, 
    city, 
    edu_level_id, 
    specialization_id, 
    cv, 
    payment_method,
    user_status,
    test_id,
    avg_test, 
    ranking
) as
SELECT
    t1.id,
    t1.id as user_id,
    CONCAT(t1.first_name_user, ' ', t1.last_name_user) as 'first_last_name',
    t1.admin_photo,
    t1.gender,
    t1.birth_date,
    t1.birth_country_id,
    t1.city_user,
    t1.edu_level_id,
    t1.specialization_id,
    t1.cv,
    t1.payment_method,
    t1.status as 'user_status',
    t2.id,
    case when t2.status is not null
      THEN (
             ifnull(t2.sum_style, 0) +
             ifnull(t2.sum_grammar, 0) +
             ifnull(t2.sum_briefing, 0) +
             ifnull(t2.sum_information, 0) +
             ifnull(t2.sum_interest, 0) +
             ifnull(t2.sum_tone, 0)
           ) / (
             (t2.sum_style IS NOT NULL) +
             (t2.sum_grammar IS NOT NULL) +
             (t2.sum_briefing IS NOT NULL) +
             (t2.sum_information IS NOT NULL) +
             (t2.sum_interest IS NOT NULL) +
             (t2.sum_tone IS NOT NULL)
           )
    ELSE null END as 'avg_test',
    TRUNCATE(((SELECT avg_test) + IFNULL(score.average_article,0)) / (IFNULL(score.num_articles,0) + 1), 2) as 'ranking'
    from `user` as t1 
    JOIN `tests` as t2 on t2.user_id = t1.id and t2.status = 1
    JOIN max_test_translator_id as t3 on t3.test_id = t2.id
    LEFT JOIN translator_score as score ON score.translator_id = t1.id;