DROP VIEW IF EXISTS list_task_project_view;
CREATE VIEW list_task_project_view (
id,
id_project,
user_id,
user_id_admin,
offer_id,
author_id,
offer_participant_id,
deadline,
project_id,
topic_id,
offer_participant_editor_id,
editor_id,
offer_participant_project_manager_id,
project_manager_id,
offer_participant_reviser_id,
reviser_id,
offer_participant_translator_id,
translator_id,
article_status_id,
article_name,
avg_score,
project_name,
author_name,
author_deadline,
translator_deadline,
reviser_deadline,
editor_name,
editor_deadline,
pm_name,
pm_deadline,
pm_email,
status_project,
offer_code,
offer_name,
article_number,
article_code,
main_keywords,
secondary_keywords,
extra_info,
url,
comment,
notes,
article_received,
article_id,
language,
invoiced,
paid_author,
paid_editor,
paid_pm,
invoice_type,
language_id
) as
WITH
cte_last_article_tasks_view as (
    SELECT
        `offer_participant_delivery_id`,
        MAX(created_at) AS `last_date`
    FROM
        `articles`
    GROUP BY
        `offer_participant_delivery_id`
), cte_article_score_tasks_view as (
    SELECT
        `offer_participant_delivery_id`,
        AVG(score) AS `avg_score`
    FROM
        `articles`
    INNER JOIN `article_comment_score` ON articles.id = article_comment_score.article_id
    GROUP BY
        `offer_participant_delivery_id`
)
SELECT
        offer_participant_deliveries.id, COALESCE(projects.id,0) as id_project, COALESCE(projects.user_id,0) as user_id,
        project_admin_offers.user_id as user_id_admin,
        COALESCE(offer_partitipants.offer_id,0) as offer_id, COALESCE(offer_participant_deliveries.author_id,0) as author_id, 
        COALESCE(offer_participant_deliveries.offer_participant_id,0) as offer_participant_id,
        offer_participant_deliveries.end_date as deadline, COALESCE(offer_participant_deliveries.project_id,0) as project_id,
        COALESCE(offer_participant_deliveries.topic_id,0) as topic_id, COALESCE(offer_participant_deliveries.offer_participant_editor_id,0) as offer_participant_editor_id,
        COALESCE(offer_participant_deliveries.editor_id,0) as editor_id, COALESCE(offer_participant_deliveries.offer_participant_project_manager_id,0) as offer_participant_project_manager_id,
        COALESCE(offer_participant_deliveries.project_manager_id,0) as project_manager_id,
        COALESCE(offer_participant_deliveries.offer_participant_reviser_id,0) as offer_participant_reviser_id, 
        COALESCE(offer_participant_deliveries.reviser_id,0) as reviser_id, COALESCE(offer_participant_deliveries.offer_participant_translator_id,0) as offer_participant_translator_id, 
        COALESCE(offer_participant_deliveries.translator_id,0) as translator_id, COALESCE(articles.article_status_id,0) AS article_status_id, COALESCE(articles.title, tp.topics) AS article_name,
        COALESCE(cte_article_score_tasks_view.avg_score,0) AS avg_score, projects.name as project_name, concat(authors.first_name,' ',authors.last_name) as author_name, tp.author_deadline,
        -- translator_name
        tp.translator_deadline, tp.reviser_deadline,
        --  reviser_name
        concat(editor.first_name,' ',editor.last_name) as editor_name, tp.editor_deadline, concat(pm.first_name,' ',pm.last_name) as pm_name,
        tp.pm_deadline, rm.email as pm_email, projects.status as status_project,
        concat('PO#',projects.id,'/', project_offers.position, '/', project_offers.offer_code) as offer_code,
        project_offers.offer_name as offer_name, tp.article_number, tp.article_code, tp.main_keywords, tp.secondary_keywords, tp.extra_info,
        tp.url,tp.comment, tp.notes,tp.created_at as article_received, articles.id as article_id,
        languages.language, bill.created_at as invoiced, IF(project_offers.price > 0, payment.created_at, "-") as paid_author,
        IF(project_offers.editor_price > 0, payment.created_at, "-") as paid_editor,
        IF(project_offers.project_manager_price > 0, payment.created_at, "-") as paid_pm,bill.invoice_type, languages.id as language_id
    FROM
        offer_participant_deliveries
    LEFT JOIN offer_partitipants ON offer_participant_deliveries.offer_participant_id = offer_partitipants.id
    LEFT JOIN project_offers ON offer_participant_deliveries.project_id = project_offers.id
    LEFT JOIN languages ON project_offers.language_id = languages.id
    LEFT JOIN projects ON project_offers.project_id = projects.id
    INNER JOIN project_admin_offers on project_admin_offers.project_id = projects.id
    LEFT JOIN user rm ON projects.user_id = rm.id
    LEFT JOIN authors ON offer_participant_deliveries.author_id = authors.id
    LEFT JOIN user ON offer_participant_deliveries.reviser_id = user.id
    LEFT JOIN editors editor ON offer_participant_deliveries.editor_id = editor.id
    LEFT JOIN editors pm ON offer_participant_deliveries.project_manager_id = pm.id
    LEFT JOIN offer_deliveries_offer_bills ON offer_participant_deliveries.id = offer_deliveries_offer_bills.offer_participant_deliveries_id
    LEFT JOIN offer_participant_bills bill ON offer_deliveries_offer_bills.offer_participant_bills_id = bill.id
    LEFT JOIN offer_participant_payments payment ON bill.id = payment.offer_id
    LEFT JOIN cte_last_article_tasks_view ON offer_participant_deliveries.id = cte_last_article_tasks_view.offer_participant_delivery_id
    LEFT JOIN articles ON articles.created_at = cte_last_article_tasks_view.last_date AND articles.offer_participant_delivery_id = 			cte_last_article_tasks_view.offer_participant_delivery_id
    LEFT JOIN cte_article_score_tasks_view ON offer_participant_deliveries.id = cte_article_score_tasks_view.offer_participant_delivery_id
    INNER JOIN topics tp ON offer_participant_deliveries.topic_id = tp.id