DROP VIEW IF EXISTS offer_authors_view_es;
CREATE VIEW offer_authors_view_es(
    id,
    user_id,
    project_id,
    offer_code,
    offer_name,
    offer_status,
    participant_type,
    first_name,
    last_name,
    partitipant_name,
    offer_id,
    total_rate,
    total_article_scoring_author
) as
WITH cte_authors_rate_view as (
SELECT
        (
            COALESCE(puntuality_rate.rate, 0) + COALESCE(quality_rate.rate, 0)
        ) AS totalRate,
        COALESCE(total_article_scoring_author_view.total_scoring, 0) AS totalArticleScoringAuthor,
        authorInfo.id AS author_id
    FROM
        authors_view
    LEFT JOIN user userInfo ON authors_view.user_id = userInfo.id
    LEFT JOIN authors authorInfo ON authors_view.id = authorInfo.id
    LEFT JOIN scoring_puntuality_rate_view puntuality_rate ON (authors_view.user_id = puntuality_rate.user_id ) AND ( puntuality_rate.participant_type = 1)
    LEFT JOIN scoring_quality_rate_view quality_rate ON (authors_view.user_id = quality_rate.user_id) AND( quality_rate.participant_type = 1)
    LEFT JOIN total_article_scoring_author_view ON total_article_scoring_author_view.user_id = userInfo.id
    GROUP BY authors_view.id, authorInfo.id
)
SELECT
    offer_partitipants.id,
    offer_partitipants.user_id,
    offer.project_id,
    offer.offer_code,
    offer.offer_name,
    offer_partitipants.offer_status,
    offer_partitipants.participant_type,
    author.first_name,
    author.last_name,
    offer_partitipants.partitipant_name,
    offer_partitipants.offer_id,
    cte_authors_rate_view.totalRate as total_rate,
    cte_authors_rate_view.totalArticleScoringAuthor as total_article_scoring_author
FROM
    offer_partitipants
LEFT JOIN project_offers offer ON offer_partitipants.offer_id = offer.id
LEFT JOIN user ON offer_partitipants.user_id = user.id
LEFT JOIN authors author ON user.id = author.user_id
LEFT JOIN cte_authors_rate_view ON cte_authors_rate_view.author_id = author.id
WHERE
    (
        offer_partitipants.participant_type = 1
    )
GROUP BY
    offer_partitipants.offer_id,
    offer_partitipants.user_id;