DROP VIEW IF EXISTS verified_authors_view_es;
CREATE VIEW verified_authors_view_es(
    id,
    user_id,
    first_name,
    gender,
    city,
    cv,
    payment_method,
    ranking,
    author_status,
    price_per_hour_id,
    birth_country_id,
    edu_level_id,
    specialization_id,
    avg_test,
    native_language,
    date_start,
    author_experiences,
    editor_first_name,
    editor_last_name,
    puntuality_rate,
    quality_rate,
    total_rate,
    total_article_scoring,
    alias,
    author_status_id,
    lang_id,
    checking_user_id,
    payment_transferwise_email,
    email
) as
WITH 
cte_puntuality_rate as (
	select
            scoring_amount.user_id AS user_id,
            scoring_amount.participant_type AS participant_type,
            (scoring_amount.total_scoring / scoring_count.count_scoring) AS rate
        from ((
            (
                select sc.user_id, sc.participant_type, sc.scoring_type, sc.total_scoring 
                    from sum_scoring_puntuality_rate_view as sc
                    
            )
            )
            scoring_amount join ( select sc.user_id, sc.participant_type, sc.scoring_type, sc.count_scoring 
                                from count_scoring_puntuality_rate_view as sc
                            )
            scoring_count on( ( (scoring_amount.user_id = scoring_count.user_id) 
                                and (scoring_amount.participant_type = scoring_count.participant_type) 
                                and (scoring_amount.scoring_type = scoring_count.scoring_type)
                              )
                            )
            ) where scoring_amount.scoring_type = 1
), cte_quality_rate as (
  	select 
            scoring_amount.user_id AS user_id, scoring_amount.participant_type AS participant_type,
            (scoring_amount.total_scoring / scoring_count.count_scoring) AS rate
        from ((
            (
                select sc.user_id ,sc.participant_type ,sc.scoring_type, sc.total_scoring 
                    from sum_scoring_quality_rate_view sc      
            )
            )
            scoring_amount join (
                                select sc.user_id, sc.participant_type, sc.scoring_type, sc.count_scoring 
                                from count_scoring_quality_rate_view sc
                            )
            scoring_count on(
                                    (
                                        (scoring_amount.user_id = scoring_count.user_id) 
                                        and (scoring_amount.participant_type = scoring_count.participant_type) 
                                        and (scoring_amount.scoring_type = scoring_count.scoring_type)
                                    )
                                )
            )
        where scoring_amount.scoring_type = 3
), cte_total_article_scoring_author_view as (
  Select
        COUNT(sc.id) as total_scoring, sc.user_id 
        from scoring as sc 
        where sc.participant_type = 1 and sc.scoring_type = 1 and sc.scoring > 0
        GROUP by sc.user_id
  )
SELECT
        t1.id, t1.user_id, CONCAT(t1.first_name, ' ', t1.last_name) as 'first_last_name',
        t1.gender, t1.city, t1.cv, t1.payment_method,
        TRUNCATE((
            case when t3.status is not null
            THEN (
                    COALESCE(t3.sum_style, 0) + COALESCE(t3.sum_grammar, 0) + COALESCE(t3.sum_briefing, 0) +
                    COALESCE(t3.sum_information, 0) + COALESCE(t3.sum_interest, 0) + COALESCE(t3.sum_tone, 0)
                ) / (
                    (t3.sum_style IS NOT NULL) + (t3.sum_grammar IS NOT NULL) + (t3.sum_briefing IS NOT NULL) +
                    (t3.sum_information IS NOT NULL) + (t3.sum_interest IS NOT NULL) + (t3.sum_tone IS NOT NULL)
                )
            ELSE null END
        ), 2) as 'ranking', 
        t2.status, 
        t1.price_per_hour_id, 
        t1.birth_country_id, t1.edu_level_id,
        t1.specialization_id,
        case when t3.status is not null
        THEN (
                COALESCE(t3.sum_style, 0) + COALESCE(t3.sum_grammar, 0) + COALESCE(t3.sum_briefing, 0) +
                COALESCE(t3.sum_information, 0) + COALESCE(t3.sum_interest, 0) + COALESCE(t3.sum_tone, 0)
            ) / (
                (t3.sum_style IS NOT NULL) + (t3.sum_grammar IS NOT NULL) + (t3.sum_briefing IS NOT NULL) +
                (t3.sum_information IS NOT NULL) + (t3.sum_interest IS NOT NULL) + (t3.sum_tone IS NOT NULL)
            )
        ELSE null END  as 'avg_test',
        languages.language AS native_language,
        t2.created_at AS date_start,
        experience.experience AS author_experiences,
        editors.first_name AS editor_first_name,
        editors.last_name AS editor_last_name,
        COALESCE (cte_puntuality_rate.rate, 0) AS puntuality_rate,
        COALESCE (cte_quality_rate.rate,0) AS quality_rate,
        (COALESCE(cte_puntuality_rate.rate, 0) + COALESCE(cte_quality_rate.rate, 0)) AS total_rate,
        COALESCE(cte_total_article_scoring_author_view.total_scoring, 0) AS total_article_scoring,
        t2.alias,
        t1.author_status_id,
        user_lang.lang_id,
        tests.checking_user_id,
        t1.payment_transferwise_email, t2.email
    FROM authors as t1
    LEFT JOIN user as t2 on t2.id = t1.user_id
    LEFT JOIN tests as t3 on t3.author_id = t1.id and t3.status = 1
    LEFT JOIN countries ON t1.birth_country_id = countries.id
    LEFT JOIN user_lang ON t2.id = user_lang.user_id
    LEFT JOIN languages ON user_lang.lang_id = languages.id
    LEFT JOIN author_experience ON t1.id = author_experience.author_id
    LEFT JOIN experience ON author_experience.experience_id = experience.id
    LEFT JOIN tests ON t1.id = tests.author_id
    LEFT JOIN editors ON t2.id = editors.user_id
    LEFT JOIN cte_puntuality_rate ON ( t1.user_id = cte_puntuality_rate.user_id) AND (cte_puntuality_rate.participant_type = 1)
    LEFT JOIN cte_quality_rate ON (t1.user_id = cte_quality_rate.user_id) AND (cte_quality_rate.participant_type = 1)
    LEFT JOIN cte_total_article_scoring_author_view ON cte_total_article_scoring_author_view.user_id = t2.id
    WHERE t2.registration_step = 10 and t1.author_status_id = 5
    GROUP BY t1.id;