DROP VIEW IF EXISTS list_tests_view;
CREATE VIEW list_tests_view (
 id,
 theme_id,
 experience_id,
 checking_user_id,
 date_end,
 date_evaluation,
 test_status,
 avg_test,
 author_id,
 user_id,
 first_name,
 gender,
 birth_date,
 birth_country_id,
 city,
 edu_level_id,
 specialization_id,
 price_per_hour_id,
 cv,
 payment_method,
 user_status,
 author_status,
 role_id,
 native_language,
 target_languages,
 author_experiences,
 editor_first_name,
 editor_last_name,
 lang_id,
 target_language_id,
 author_experience_id
) as
WITH
    cte_tests AS (
	    SELECT
            t1.id,
            t1.theme_id,
            t1.checking_user_id,
            t1.date_end,
            t1.date_evaluation,
            t1.status as 'test_status',
            case when t1.status is not null THEN (
                COALESCE(t1.sum_style, 0) +
                COALESCE(t1.sum_grammar, 0) +
                COALESCE(t1.sum_briefing, 0) +
                COALESCE(t1.sum_information, 0) +
                COALESCE(t1.sum_interest, 0) +
                COALESCE(t1.sum_tone, 0)
            ) / (
            (t1.sum_style IS NOT NULL) +
                (t1.sum_grammar IS NOT NULL) +
                (t1.sum_briefing IS NOT NULL) +
                (t1.sum_information IS NOT NULL) +
                (t1.sum_interest IS NOT NULL) +
                (t1.sum_tone IS NOT NULL)
            ) ELSE null END as 'avg_test',
            t1.author_id,
            t1.user_id
        FROM tests AS t1
)
SELECT
    cte_tests.id,
    cte_tests.theme_id,
    t2.experience_id,
    cte_tests.checking_user_id,
    cte_tests.date_end,
    cte_tests.date_evaluation,
    cte_tests.test_status,
    cte_tests.avg_test,
    t4.id as 'author_id',
    CASE WHEN cte_tests.author_id IS NOT NULL THEN t4.user_id ELSE cte_tests.user_id END as 'user_id',
        CASE WHEN cte_tests.author_id IS NOT NULL THEN CONCAT(t4.first_name, ' ', t4.last_name)
        ELSE CONCAT(t3.first_name_user, ' ', t3.last_name_user)
    END as 'first_last_name',
    COALESCE(t4.gender, t3.gender)  as 'gender',
    COALESCE(t4.birth_date, t3.birth_date) as 'birth_date',
    COALESCE(t4.birth_country_id, t3.country_id_user) as 'birth_country_id',
    COALESCE(t4.city, t3.city_user) as 'city',
    COALESCE(t4.edu_level_id, t3.edu_level_id) as 'edu_level_id',
    COALESCE(t4.specialization_id, t3.specialization_id) as 'specialization_id',
    COALESCE(t4.price_per_hour_id, t3.price_per_hour_id) as 'price_per_hour_id',
    COALESCE(t4.cv, t3.cv) as 'cv',
    COALESCE(t4.payment_method, t3.payment_method) as 'payment_method',
    COALESCE(t5.status, t3.status) as 'user_status',
    COALESCE(t4.author_status_id, 0) as 'author_status',
    COALESCE(t3.role_id, t5.role_id) as 'role_id',
    languages.language AS native_Language,
    target_language.language AS targetLanguages,
    experience.experience AS authorExperiences,
    editors.first_name AS editorFirstName,
    editors.last_name AS editorLastName,
    user_lang.lang_id,
    target_language.id as target_language_id,
    experience.id as author_experience_id
FROM cte_tests
LEFT JOIN themes as t2 on t2.id = cte_tests.theme_id
LEFT JOIN user as t3 on cte_tests.user_id = t3.id
LEFT JOIN authors as t4 on cte_tests.author_id = t4.id
LEFT JOIN user as t5 on t4.user_id = t5.id

LEFT JOIN countries ON t4.birth_country_id = countries.id
LEFT JOIN user as userInfo ON cte_tests.user_id = userInfo.id
LEFT JOIN user_lang ON userInfo.id = user_lang.user_id
LEFT JOIN languages ON user_lang.lang_id = languages.id

LEFT JOIN user_translator_languages ON userInfo.id = user_translator_languages.user_id
LEFT JOIN languages target_language ON user_translator_languages.target_lang_id = target_language.id
LEFT JOIN author_experience ON t4.id = author_experience.author_id
LEFT JOIN experience ON author_experience.experience_id = experience.id

LEFT JOIN user ON cte_tests.checking_user_id = user.id
LEFT JOIN editors ON user.id = editors.user_id
LEFT JOIN roles ON t3.role_id = roles.id
WHERE
    cte_tests.date_end IS NOT NULL
GROUP BY cte_tests.id;