DROP VIEW IF EXISTS payment_extra_view;
CREATE VIEW payment_extra_view (
id,
participan_id,
user_id,
participan_name,
payment_method,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
payment_id,
payment_status,
total_amount,
job_position,
regional_manager_id,
bills_status,
project_status,
estimated_payment_date,
invoice_id
) AS
WITH
cte_payment_extra as (
SELECT  pe.id as id,
     COALESCE(a.id, e.id, u.id) AS participan_id,
     COALESCE(a.user_id, e.user_id, u.id)AS 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 participan_name,
     COALESCE(a.payment_method, e.payment_method, u.payment_method) AS 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,
  p.id AS project_id,
  p.name AS project_name,
      ROUND(IFNULL(pe.amount, 0), 2) AS total_gross_amount,
 pp.id AS payment_id,
 pp.status AS payment_status,
 pp.total_amount as total_amount,
 pe.invoice_position AS job_position,
 p.user_id  AS regional_manager_id,
 pe.status AS bills_status,
 p.status AS project_status,
 pe.estimated_payment_date AS estimated_payment_date,
pe.invoice_extra_id as invoice_id
FROM project_payment_extra as pe
	INNER JOIN projects AS p ON pe.project_id = p.id
	LEFT JOIN authors AS a ON pe.user_id = a.user_id
    LEFT JOIN editors AS e ON pe.user_id = e.user_id
    LEFT JOIN user AS u ON pe.user_id = u.id
    
LEFT JOIN participant_payments_extra AS pp ON pp.extra_id = pe.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 pe.id
HAVING (total_gross_amount > 0)
)
select * from cte_payment_extra;