DROP VIEW IF EXISTS offer_revisers_view_es;
CREATE VIEW offer_revisers_view_es(
    id,
    user_id,
    offer_status,
    participant_type,
    partitipant_name,
    offer_id,
    total_rate_reviser,
    reviser_total_article_scoring
) 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, 
        revisers_rate_view.totalRateReviser as total_rate_reviser,
        revisers_rate_view.reviserTotalArticleScoring as reviser_total_article_scoring
    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 totalRateReviser,
            COALESCE(
                total_article_scoring_reviser_view.total_scoring,
                0
            ) AS reviserTotalArticleScoring,
            user.id AS reviser_id
        FROM
            user
        LEFT JOIN scoring_puntuality_rate_view puntuality_rate ON
            (
                user.id = puntuality_rate.user_id
            ) AND(
                puntuality_rate.participant_type = 6
            )
        LEFT JOIN scoring_attitude_rate_view attitude_rate ON
            (
                user.id = attitude_rate.user_id
            ) AND(
                attitude_rate.participant_type = 6
            )
        LEFT JOIN scoring_quality_rate_view quality_rate ON
            (user.id = quality_rate.user_id) AND(
                quality_rate.participant_type = 6
            )
        LEFT JOIN total_article_scoring_reviser_view ON total_article_scoring_reviser_view.user_id = user.id
        GROUP BY
            user.id  
        ORDER BY reviserTotalArticleScoring  DESC
    ) as revisers_rate_view ON revisers_rate_view.reviser_id = user.id
    WHERE
        participant_type = 6
    GROUP BY
        offer_id,
        user_id;