DROP VIEW IF EXISTS offer_editors_view_es;
CREATE VIEW offer_editors_view_es(
    id,
    user_id,
    project_id,
    offer_code,
    offer_name,
    offer_status,
    participant_type,
    first_name,
    last_name,
    partitipant_name,
    offer_id,
    total_scoring,
    editor_total_article_scoring
) as
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,
        editor.first_name,
        editor.last_name,
        offer_partitipants.partitipant_name,
        offer_partitipants.offer_id,
        scoring_by_setting_view.total_scoring,
        total_article_scoring_editor_view.total_scoring as editor_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 scoring_by_setting_view on scoring_by_setting_view.user_id = user.id
    LEFT JOIN total_article_scoring_editor_view ON total_article_scoring_editor_view.user_id = user.id
    WHERE
        participant_type = 2 
    GROUP BY
        offer_id,
        user_id;