DROP VIEW IF EXISTS historical_payments_list_view;
CREATE VIEW historical_payments_list_view(
id,
participant_id,
user_id,
participant_name,
participant_payment_method,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_project_amount,
payment_status,
total_amount,
job_position,
invoice_number,
invoice_id,
transferwise_transaction_id
) as 
SELECT
    hopb.id,
    COALESCE(a.id, e.id, u.id) AS participant_id,
    hopb.user_id,
    CONCAT(COALESCE(a.first_name, e.first_name, u.first_name_user), ' ', COALESCE(a.last_name, e.last_name, u.last_name_user)) AS participant_name,
    COALESCE(a.payment_method, e.payment_method, 2) AS participant_payment_method,
    COALESCE(a.payment_transferwise_email, e.payment_transferwise_email, u.payment_transferwise_email_user) AS payment_transferwise_email,
    COALESCE(a.payment_transferwise_currency_code, e.payment_transferwise_currency_code, u.payment_transferwise_currency_code_user) AS payment_transferwise_currency_code,
    hp.id AS project_id,
    hp.name AS project_name,
    ROUND( IFNULL(hodb.total_gross_amount, 0), 2) AS total_gross_amount,
    ROUND(IFNULL(hofp.total_copyscape_cost, 0), 2) AS total_copyscape_cost,
    ROUND(IFNULL(hodb.total_gross_amount, 0) - IFNULL(hofp.total_copyscape_cost, 0), 2) AS total_project_amount,
    hopp.status AS payment_status,
    hopp.total_amount,
    hopb.invoice_type AS job_position,
    hopb.invoice_number AS invoice_number,
    hopb.invoice_id AS invoice_id,
    hopp.transferwise_transaction_id
FROM
    historical_projects hp
INNER JOIN historical_offer_participant_bills hopb ON
    hopb.historical_project_id = hp.id
INNER JOIN historical_offer_participant_payments hopp ON
    hopp.historical_offer_id = hopb.id
INNER JOIN USER u ON hopb.user_id = u.id
INNER JOIN(
    SELECT
        SUM(COALESCE(price, 0)) AS total_gross_amount,
        historical_offer_participant_bills_id
    FROM
        historical_offer_deliveries_offer_bills
    GROUP BY
        historical_offer_deliveries_offer_bills.historical_offer_participant_deliveries_id
) AS hodb ON hopb.id = hodb.historical_offer_participant_bills_id

LEFT JOIN (SELECT SUM(hop.copyscape_cost) AS total_copyscape_cost, hop.user_id, hop.participant_type, hpo.historical_project_id
        FROM historical_project_offers AS hpo 
        INNER JOIN historcial_offer_partitipants AS hop ON hpo.id = hop.historical_offer_id
        GROUP BY hop.user_id, hop.participant_type, hpo.historical_project_id
        ) AS hofp ON hopb.user_id = hofp.user_id AND hopb.invoice_type = hofp.participant_type AND hp.id = hofp.historical_project_id
LEFT JOIN authors AS a ON hopb.user_id = a.user_id
LEFT JOIN editors AS e ON hopb.user_id = e.user_id      
GROUP BY
    hopb.id