DROP VIEW IF EXISTS payments_view;
CREATE VIEW payments_view (id, author_id, user_id, author_name, payment_paypal_email, project_id, project_name, offer_id, offer_name, total_gross_amount, total_project_amount, total_copyscape_cost, payment_id, payment_status, paypal_payout_batch_id, paypal_status, total_amount, project_amount, copyscape_cost, job_position, regional_manager_id, bills_id, bills_status) AS
SELECT op.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_paypal_email, e.payment_paypal_email)                              AS payment_paypal_email,
       p.id                                                                                  AS project_id,
       p.name                                                                                AS project_name,
       po.id                                                                                 AS offer_id,
       po.offer_name                                                                         AS offer_name,
       ROUND(IFNULL(SUM(op.gross_amount), 0), 2)                                             AS total_gross_amount,
       ROUND(IFNULL(SUM(op.net_amount), 0), 2)                                               AS total_project_amount,
       ROUND(IFNULL(SUM(op.copyscape_cost), 0), 2)                                           AS total_copyscape_cost,
       pp.id                                                                                 AS payment_id,
       pp.status                                                                             AS payment_status,
       pp.paypal_payout_batch_id,
       pp.paypal_status,
       pp.total_amount,
       pp.project_amount,
       pp.copyscape_cost,
       op.participant_type                                                                   AS job_position,
       p.user_id                                                                             AS regional_manager_id,
       pb.id                                                                                 AS bills_id,
       pb.status                                                                             AS bills_status                       
FROM offer_partitipants AS op
       LEFT JOIN project_offers AS po ON op.offer_id = po.id
       LEFT JOIN projects AS p ON po.project_id = p.id
       LEFT JOIN authors AS a ON op.user_id = a.user_id
       LEFT JOIN editors AS e ON op.user_id = e.user_id
       LEFT JOIN offer_participant_payments AS pp ON pp.offer_id = op.id AND pp.user_id = a.user_id
       LEFT JOIN offer_participant_bills AS pb ON pb.offer_id = po.id and pb.user_id = op.user_id
WHERE op.project_status = 1
  AND (pp.status IS NULL OR pp.status <> 1) 
GROUP BY op.id
HAVING (total_gross_amount > 0);