DROP VIEW IF EXISTS offer_authors_view_es;
CREATE VIEW offer_authors_view_es(
    id,
    user_id,
    offer_status,
    participant_type,
    partitipant_name,
    offer_id,
    totalRate,
    totalArticleScoringAuthor
) as
SELECT
    offer_partitipants.id,
    offer_partitipants.user_id,
    offer_partitipants.offer_status,
    offer_partitipants.participant_type,
    offer_partitipants.partitipant_name,
    offer_partitipants.offer_id, 
    authors_rate_view.totalRate,
    authors_rate_view.totalArticleScoringAuthor
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 editors editor ON
    user.id = editor.user_id
LEFT JOIN offer_deliveries offerDeliveries ON
    offer.id = offerDeliveries.offer_id
LEFT JOIN (
    SELECT
        (
            COALESCE(puntuality_rate.rate, 0) + COALESCE(attitude_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_attitude_rate_view attitude_rate ON (authors_view.user_id = attitude_rate.user_id) AND (attitude_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
) as authors_rate_view ON authors_rate_view.author_id = author.id
WHERE
    (
        participant_type = 1
    )
GROUP BY
    offer_id,
    user_id;