DROP VIEW IF EXISTS paid_payment_extra_view;
CREATE VIEW paid_payment_extra_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,
bills_id,
invoice_number,
bills_status,
payment_method,
payment_status,
transferwise_transaction_id,
total_amount,
job_position,
regional_manager_id,
project_status,
paid_date,
invoice_id
) as
WITH
cte_paid_payment_extra 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_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(pe.amount, 0), 2)AS total_gross_amount,
    pe.id AS bills_id,
    pe.invoice_number AS invoice_number,
    pe.status AS bills_status,
    pp.payment_method AS payment_method,
    pp.status AS payment_status,
    pp.transferwise_transaction_id,
    pp.total_amount,
    pe.invoice_position AS job_position,
    p.user_id AS regional_manager_id,
    p.status AS project_status,
    pp.created_at AS paid_date,
    pe.invoice_extra_id as invoice_id
    FROM projects AS p  
    INNER JOIN project_payment_extra AS pe ON pe.project_id = p.id
    INNER JOIN participant_payments_extra AS pp ON pp.extra_id = pe.id
    INNER JOIN user as us ON pe.user_id = us.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
    WHERE pp.status = 1 and pe.status = 13
    GROUP BY pe.id, pp.id
)
SELECT * from cte_paid_payment_extra;
