DROP VIEW IF EXISTS report_editors_view;
CREATE VIEW report_editors_view (
    id,
    email,
    first_last_name,
    status,
    registration_date,
    language,
    city,
    country,
    lists,
    user_language_code,
    revisers_ranking,
    revisers_number_jobs,
    editors_ranking,
    editors_number_jobs,
    pms_ranking,
    pms_number_jobs,
    address,
    new_t_c,
    date_t_c
)
as 
WITH
cte_lists as (
    SELECT user_lists.user_id, GROUP_CONCAT(lists.name) list FROM `user_lists` 
    INNER JOIN lists on lists.id = user_lists.list_id
    GROUP BY user_lists.user_id
), 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_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_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_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_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_reviser_total_article_view as (
	SELECT
        count(*) as reviserTotalArticleScoring,
        scoring.user_id as user_id
    FROM
        scoring
    WHERE
        scoring.participant_type = 6 AND scoring.scoring_type = 1 AND scoring.scoring > 0
    GROUP BY
        scoring.user_id
), cte_editor_total_article_view as (
	SELECT
        count(*) as editorTotalArticleScoring,
        scoring.user_id as user_id
    FROM
        scoring
    WHERE
        scoring.participant_type = 2 AND scoring.scoring_type = 1 AND scoring.scoring > 0
    GROUP BY
        scoring.user_id
), cte_pm_total_article_view as (
	SELECT
        count(*) as pmTotalArticleScoring,
        scoring.user_id as user_id
    FROM
        scoring
    WHERE
        scoring.participant_type = 5 AND scoring.scoring_type = 1 AND scoring.scoring > 0
    GROUP BY
        scoring.user_id
)
SELECT
    editors_view.user_id,
    editors_view.email,
    editors_view.first_last_name,
	CASE WHEN editors_view.user_status = 0 THEN 'Inactive'
    	 WHEN editors_view.user_status = 10 THEN 'Accepted'
    ELSE editors_view.user_status 
    END as 'status',
    from_unixtime(user.created_at,'%Y-%m-%d') as 'registration_date',
    COALESCE(languages.language, '') AS language,
      if(editors.city is null or editors.city = '', '- -', editors.city) as city,
      if(c.country is null or c.country = '', '- -', c.country) as country,
      if(cte_lists.list is null or cte_lists.list = '', '- -', cte_lists.list) as 'lists',
      if(languages.language_code is null or languages.language_code = '', '- -', languages.language_code) AS user_language_code,
    TRUNCATE(COALESCE((
        COALESCE(
            cte_reviser_puntuality_rate.rate,
            0
        ) + COALESCE(cte_reviser_attitude_rate.rate, 0) + COALESCE(cte_reviser_quality_rate.rate, 0)
    ),0),2) AS revisers_ranking,
    COALESCE(cte_reviser_total_article_view.reviserTotalArticleScoring, 0) as revisers_number_jobs,
    TRUNCATE(COALESCE((
        COALESCE(cte_editor_puntuality_rate.rate, 0) + COALESCE(cte_editor_attitude_rate.rate, 0) + COALESCE(cte_editor_quality_rate.rate, 0)
    ),0),2) AS editors_ranking,
    COALESCE(cte_editor_total_article_view.editorTotalArticleScoring,0) as editors_number_jobs,
    TRUNCATE(COALESCE((
        COALESCE(cte_pm_puntuality_rate.rate, 0) + COALESCE(cte_pm_attitude_rate.rate, 0) + COALESCE(cte_pm_quality_rate.rate, 0)
    ),0),2) AS pms_ranking,
    TRUNCATE(COALESCE(cte_pm_total_article_view.pmTotalArticleScoring,0),0) as pms_number_jobs,
    user.ip_address as address,
    user.contract as 'new_t_c',
    user.date_terms as 'date_t_c'
FROM
    editors_view
LEFT JOIN user ON editors_view.user_id = user.id
LEFT JOIN editors on editors_view.id = editors.id
LEFT JOIN user_lang ON user.id = user_lang.user_id
LEFT JOIN languages ON user_lang.lang_id = languages.id
LEFT JOIN countries as c on c.id = editors.country_id
LEFT JOIN user_lists as lu on lu.user_id = editors_view.user_id
LEFT JOIN cte_lists on cte_lists.user_id = editors_view.user_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_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_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_reviser_total_article_view ON cte_reviser_total_article_view.user_id = user.id
LEFT JOIN cte_editor_total_article_view ON cte_editor_total_article_view.user_id = user.id
LEFT JOIN cte_pm_total_article_view ON cte_pm_total_article_view.user_id = user.id
 GROUP BY editors_view.user_id;