DROP VIEW IF EXISTS historical_payment_bill_view;
CREATE VIEW historical_payment_bill_view (id,
author_id,
user_id,
author_name,
payment_method,
payment_paypal_email,
payment_transferwise_email,
payment_transferwise_currency_code,
historical_project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_net_amount,
historical_payment_id,
payment_status,
paypal_payout_batch_id,
paypal_status,
total_amount,
project_amount,
copyscape_cost,
job_position,
regional_manager_id,
bills_status,
project_status,
estimated_payment_date
) AS
SELECT  pb.id                                                                                 AS id,
        COALESCE(a.id, e.id)                                                                  AS author_id,
        COALESCE(a.user_id, e.user_id)                                                        AS user_id,
        CONCAT(COALESCE(a.first_name, e.first_name), ' ', COALESCE(a.last_name, e.last_name)) AS author_name,
        COALESCE(a.payment_method, e.payment_method)                                          AS payment_method,
        COALESCE(a.payment_paypal_email, e.payment_paypal_email)                              AS payment_paypal_email,
        COALESCE(a.payment_transferwise_email, e.payment_transferwise_email)                  AS payment_transferwise_email,
        COALESCE(a.payment_transferwise_currency_code, e.payment_transferwise_currency_code)  AS payment_transferwise_currency_code,
        p.id                                                                                  AS historical_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_net_amount,
        pp.id                                                                                 AS historical_payment_id,
        pp.status                                                                             AS payment_status,
        pp.paypal_payout_batch_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,
        pb.status                                                                             AS bills_status,
        p.status                                                                              AS project_status,
        pb.estimated_payment_date                                                             AS estimated_payment_date
FROM historical_offer_participant_bills AS pb
        INNER JOIN historical_projects AS p ON pb.historical_project_id = p.id
        INNER JOIN (SELECT SUM(price) AS total_gross_amount, historical_offer_participant_bills_id 
        FROM historical_offer_deliveries_offer_bills 
        GROUP BY historical_offer_participant_bills_id
        ) AS odb ON pb.id = odb.historical_offer_participant_bills_id
        INNER JOIN (SELECT SUM(op.copyscape_cost) AS total_copyscape_cost, op.user_id, op.participant_type, po.historical_project_id 
        FROM historical_project_offers AS po 
        INNER JOIN historcial_offer_partitipants AS op ON po.id = op.historical_offer_id
        GROUP BY op.user_id, op.participant_type, po.historical_project_id
        ) AS ofp ON pb.user_id = ofp.user_id AND pb.invoice_type = ofp.participant_type AND p.id = ofp.historical_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
        LEFT JOIN historical_offer_participant_payments AS pp ON pp.historical_offer_id = pb.id AND (pp.user_id = a.user_id OR pp.user_id = e.user_id)
WHERE (pp.status IS NULL OR pp.status <> 1) 
GROUP BY pb.id
HAVING (total_gross_amount > 0)