DROP VIEW IF EXISTS report_authors_view;
CREATE VIEW report_authors_view (
id,
email,
first_last_name,
status,
registration_date,
language,
city,
country,
specialization,
jobs_current,
jobs_completed,
lists,
ranking,
address,
new_t_c,
date_t_c
)
as
WITH
cte_jobs_current as (
    SELECT COUNT(*) as current, c.author_id FROM
        (
            SELECT projects.id, topics.author_id
            FROM
                projects
            INNER JOIN project_offers ON projects.id = project_offers.project_id
            INNER JOIN topics ON projects.id = topics.project_id
            WHERE (projects.status IN(0, 3)) AND(topics.status = 1)
            GROUP BY projects.id, topics.author_id
        ) c GROUP BY c.author_id
    ORDER BY c.author_id ASC
), 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_jobs_completed as (
    SELECT COUNT(*) as completed, c.author_id FROM
    (
    SELECT historical_projects.id, historical_topics.author_id
    FROM historical_projects
    LEFT JOIN historical_project_offers ON historical_projects.id = historical_project_offers.historical_project_id
    INNER JOIN historical_topics ON historical_projects.id = historical_topics.historical_project_id
    WHERE (historical_topics.status = 1)
    GROUP BY historical_projects.id, historical_topics.author_id
    ) c GROUP BY c.author_id ORDER BY c.author_id ASC
)
SELECT
    t2.id as id,
    t2.email,
    CONCAT(t1.first_name, ' ', t1.last_name) as 'first_last_name',
    CASE WHEN t1.author_status_id = 1 and t2.status = 10 THEN 'Registered'
         WHEN t1.author_status_id = 2 and t2.status = 10 THEN 'Test Delivered'
         WHEN t1.author_status_id = 3 and t2.status = 10 THEN 'Test Delivery Failed'
         WHEN t1.author_status_id = 4 and t2.status = 10 THEN 'Rejected'
         WHEN t1.author_status_id = 5 and t2.status = 10 THEN 'Accepted'
         WHEN t1.author_status_id = 1 and t2.status = 11 THEN 'In waiting list'
         WHEN t1.author_status_id = 5 and t2.status = 0 THEN 'Inactive'
         ELSE t1.author_status_id 
    END as 'status',
    from_unixtime(t2.created_at,'%Y-%m-%d') as 'registration_date',
    COALESCE(l.language, '') as language,
     COALESCE (if(t1.city = '', '- -', t1.city), '') as city,
    COALESCE (c.country, '') as country,
    sp.specialization,
    cte_jobs_current.current as 'jobs_current',
    cte_jobs_completed.completed as 'jobs_completed',
    cte_lists.list as 'Lists',
    COALESCE( 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), 0)  as 'ranking'
    , '' as 'address',
    t2.contract as 'new_t_c',
    t2.date_terms as 'date_t_c'
    FROM authors as t1
        JOIN user as t2 on t2.id = t1.user_id
        LEFT JOIN user_lang as ul on t2.id = ul.user_id
        LEFT JOIN languages as l on l.id = ul.lang_id
        LEFT JOIN tests as t3 on t3.author_id = t1.id and t3.status = 1
        LEFT JOIN countries as c on c.id = t1.country_id
        LEFT JOIN specialization_list as sp on sp.id = t1.specialization_id
        LEFT JOIN user_lists as lu on lu.user_id = t2.id
        LEFT JOIN cte_lists on cte_lists.user_id = t2.id
        LEFT JOIN cte_jobs_current on cte_jobs_current.author_id = t1.id
        LEFT JOIN cte_jobs_completed on cte_jobs_completed.author_id = t1.id
   GROUP by t2.id  
ORDER BY `t2`.`id`  DESC;