DROP VIEW IF EXISTS paid_payment_bill_view;
CREATE VIEW paid_payment_bill_view (id,
participant_id,
user_id,
participant_name,
participant_payment_method,
payment_paypal_email,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_project_amount,
bills_id,
invoice_number,
bills_status,
payment_method,
payment_status,
paypal_payout_batch_id,
transferwise_transaction_id,
paypal_status,
total_amount,
project_amount,
copyscape_cost,
job_position,
regional_manager_id,
project_status,
paid_date,
invoice_id
) AS
SELECT  pp.id                                                                                                                            AS id,
        COALESCE(a.id, e.id, us.id)                                                                                                      AS participant_id,
        COALESCE(a.user_id, e.user_id, us.id)                                                                                            AS user_id,
        CONCAT(COALESCE(a.first_name, e.first_name, us.first_name_user), ' ', COALESCE(a.last_name, e.last_name, us.last_name_user))     AS participant_name,
        COALESCE(a.payment_method, e.payment_method, 2)                                                                                  AS participant_payment_method,
        COALESCE(a.payment_paypal_email, e.payment_paypal_email)                                                                         AS payment_paypal_email,
        COALESCE(a.payment_transferwise_email, e.payment_transferwise_email, us.payment_transferwise_email_user)                         AS payment_transferwise_email,
        COALESCE(a.payment_transferwise_currency_code, e.payment_transferwise_currency_code, us.payment_transferwise_currency_code_user) AS payment_transferwise_currency_code,
        p.id                                                                                                                             AS project_id,
        p.name                                                                                                                           AS project_name,
        ROUND(IFNULL(odb.total_gross_amount, 0), 2)                                                                                      AS total_gross_amount,
        ROUND(IFNULL(ofp.total_copyscape_cost, 0), 2)                                                                                    AS total_copyscape_cost,
        ROUND(IFNULL(odb.total_gross_amount, 0) - IFNULL(ofp.total_copyscape_cost, 0), 2)                                                AS total_project_amount,
        pb.id                                                                                                                            AS bills_id,
        pb.invoice_number                                                                                                                AS invoice_number,
        pb.status                                                                                                                        AS bills_status,
        pp.payment_method                                                                                                                AS payment_method,
        pp.status                                                                                                                        AS payment_status,
        pp.paypal_payout_batch_id,
        pp.transferwise_transaction_id,
        pp.paypal_status,
        pp.total_amount,
        pp.project_amount,
        pp.copyscape_cost,
        pb.invoice_type                                                                                                                  AS job_position,
        p.user_id                                                                                                                        AS regional_manager_id,
        p.status                                                                                                                         AS project_status,
        pp.created_at                                                                                                                   AS paid_date,
        pb.invoice_id                                                                                                                   AS invoice_id
FROM    projects AS p 
INNER JOIN offer_participant_bills AS pb ON pb.project_id = p.id
INNER JOIN offer_participant_payments AS pp ON pp.offer_id = pb.id
INNER JOIN user as us ON pb.user_id = us.id
INNER JOIN (SELECT SUM(price) AS total_gross_amount, offer_participant_bills_id 
        FROM offer_deliveries_offer_bills 
        GROUP BY offer_participant_bills_id
        ) AS odb ON pb.id = odb.offer_participant_bills_id
LEFT JOIN (SELECT SUM(op.copyscape_cost) AS total_copyscape_cost, op.user_id, op.participant_type, po.project_id 
        FROM project_offers AS po 
        INNER JOIN offer_partitipants AS op ON po.id = op.offer_id
        GROUP BY op.user_id, op.participant_type, po.project_id
        ) AS ofp ON pb.user_id = ofp.user_id AND pb.invoice_type = ofp.participant_type AND p.id = ofp.project_id
LEFT JOIN authors AS a ON pb.user_id = a.user_id
LEFT JOIN editors AS e ON pb.user_id = e.user_id
WHERE pp.status = 1 and pb.status = 13
GROUP BY pb.id, pp.id