DROP VIEW IF EXISTS editors_view_es;
CREATE VIEW editors_view_es(
    id,
    user_id,
    email,
    first_last_name,
    user_status,
    cnt_tests,
    avg_results,
    userLanguage,
    userLanguageCode,
    alias,
    revisers_puntuality_rate,
    revisers_attitude_rate,
    revisers_quality_rate,
    revisers_total_rate,
    revisers_total_article_scoring,
    editors_puntuality_rate,
    editors_attitude_rate,
    editors_quality_rate,
    editors_total_rate,
    editors_total_article_scoring,
    pms_puntuality_rate,
    pms_attitude_rate,
    pms_quality_rate,
    pms_total_rate,
    pms_total_article_scoring
) as
WITH
    cte_reviser_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_reviser_attitude_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_attitude_rate_view	as sc
            )
            )
            scoring_amount join (
                                select sc.user_id, sc.participant_type, sc.scoring_type, sc.count_scoring 
                                from count_scoring_attitude_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 = 2
), cte_reviser_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_editor_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_reviser_total_article_view as (SELECT
        scoring.user_id as user_id, count(scoring.id) as reviserTotalArticleScoring
    FROM
        scoring
    WHERE
        scoring.participant_type = 6 AND scoring.scoring_type = 1 AND scoring.scoring > 0
    GROUP BY
        scoring.user_id
), cte_editor_attitude_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_attitude_rate_view	as sc
            )
            )
            scoring_amount join (
                                select sc.user_id, sc.participant_type, sc.scoring_type, sc.count_scoring 
                                from count_scoring_attitude_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 = 2
), cte_editor_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_editor_total_article_view as (
   	SELECT
        scoring.user_id as user_id, count(scoring.id) as editorTotalArticleScoring
    FROM
        scoring
    WHERE
        scoring.participant_type = 2 AND scoring.scoring_type = 1 AND scoring.scoring > 0
    GROUP BY
        scoring.user_id
   ),cte_pm_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_pm_attitude_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_pm_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_pm_total_article_view as (SELECT
        scoring.user_id as user_id, count(scoring.id) as pmTotalArticleScoring
    FROM
        scoring
    WHERE
        scoring.participant_type = 5 AND scoring.scoring_type = 1 AND scoring.scoring > 0
    GROUP BY
        scoring.user_id)
SELECT
    editors_view.id,editors_view.user_id, editors_view.email, editors_view.first_last_name, editors_view.user_status, editors_view.cnt_tests,
    COALESCE(editors_view.avg_results, 0) as avg_results, languages.language AS userLanguage, languages.language_code AS userLanguageCode, user.alias,
    COALESCE(cte_reviser_puntuality_rate.rate,0) AS revisers_puntuality_rate,
    COALESCE(cte_reviser_attitude_rate.rate, 0) AS revisers_attitude_rate,
    COALESCE(cte_reviser_quality_rate.rate, 0) AS revisers_quality_rate,
    (COALESCE(cte_reviser_puntuality_rate.rate, 0) + COALESCE(cte_reviser_attitude_rate.rate, 0) + COALESCE(cte_reviser_quality_rate.rate, 0)) AS revisers_total_rate,
    COALESCE(cte_reviser_total_article_view.reviserTotalArticleScoring,0) as revisers_total_article_scoring,
    COALESCE(cte_editor_puntuality_rate.rate, 0) AS editors_puntuality_rate,
   	COALESCE(cte_editor_attitude_rate.rate, 0) AS editors_attitude_rate,
    COALESCE(cte_editor_quality_rate.rate, 0) AS editors_quality_rate,
    (COALESCE(cte_editor_puntuality_rate.rate, 0) + COALESCE(cte_editor_attitude_rate.rate, 0) + COALESCE(cte_editor_quality_rate.rate, 0)) AS editors_total_rate,
    COALESCE(cte_editor_total_article_view.editorTotalArticleScoring,0) as editors_total_article_scoring,
    COALESCE(cte_pm_puntuality_rate.rate, 0) AS pms_puntuality_rate,
    COALESCE(cte_pm_attitude_rate.rate, 0) AS pms_attitude_rate,
    COALESCE(cte_pm_quality_rate.rate, 0) AS pms_quality_rate,
    (COALESCE(cte_pm_puntuality_rate.rate, 0) + COALESCE(cte_pm_attitude_rate.rate, 0) + COALESCE(cte_pm_quality_rate.rate, 0)) AS pms_total_rate,
    COALESCE(cte_pm_total_article_view.pmTotalArticleScoring,0) as pms_total_article_scoring
FROM
    editors_view
LEFT JOIN user ON editors_view.user_id = user.id
LEFT JOIN user_lang ON user.id = user_lang.user_id
LEFT JOIN languages ON user_lang.lang_id = languages.id
LEFT JOIN cte_reviser_puntuality_rate ON (editors_view.user_id = cte_reviser_puntuality_rate.user_id) AND (cte_reviser_puntuality_rate.participant_type = 6)
LEFT JOIN cte_reviser_attitude_rate ON (editors_view.user_id = cte_reviser_attitude_rate.user_id) AND (cte_reviser_attitude_rate.participant_type = 6)
LEFT JOIN cte_reviser_quality_rate ON (editors_view.user_id = cte_reviser_quality_rate.user_id) AND (cte_reviser_quality_rate.participant_type = 6)
LEFT JOIN cte_editor_puntuality_rate ON (editors_view.user_id = cte_editor_puntuality_rate.user_id) AND (cte_editor_puntuality_rate.participant_type = 2)
LEFT JOIN cte_reviser_total_article_view ON cte_reviser_total_article_view.user_id = user.id
LEFT JOIN cte_editor_attitude_rate ON (editors_view.user_id = cte_editor_attitude_rate.user_id) AND (cte_editor_attitude_rate.participant_type = 2)
LEFT JOIN cte_editor_quality_rate ON (editors_view.user_id = cte_editor_quality_rate.user_id) AND (cte_editor_quality_rate.participant_type = 2)
LEFT JOIN cte_editor_total_article_view ON cte_editor_total_article_view.user_id = user.id
LEFT JOIN cte_pm_puntuality_rate ON (editors_view.user_id = cte_pm_puntuality_rate.user_id) AND (cte_pm_puntuality_rate.participant_type = 5)
LEFT JOIN cte_pm_attitude_rate ON (editors_view.user_id = cte_pm_attitude_rate.user_id) AND (cte_pm_attitude_rate.participant_type = 5)
LEFT JOIN cte_pm_quality_rate ON (editors_view.user_id = cte_pm_quality_rate.user_id) AND (cte_pm_quality_rate.participant_type = 5)
LEFT JOIN cte_pm_total_article_view ON cte_pm_total_article_view.user_id = user.id
ORDER BY
    editors_total_rate
DESC
    ,
    editors_total_article_scoring
DESC;