DROP VIEW IF EXISTS report_payments_view;
CREATE VIEW report_payments_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,
total_copyscape_cost,
total_project_amount,
payment_status,
total_amount,
job_position,
invoice_id,
invoice_number,
transferwise_transaction_id,
paid_date
) AS 
select
id,
author_id as participant_id,
user_id,
author_name as participant_name,
payment_method as participant_payment_method,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_net_amount as total_project_amount,
payment_status,
total_amount,
job_position,
invoice_id,
invoice_number,
transferwise_transaction_id,
paid_date
from payment_bill_view
UNION ALL 
select 
id,
participant_id,
user_id,
participant_name,
participant_payment_method,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_project_amount,
payment_status,
total_amount,
job_position,
invoice_id,
invoice_number,
transferwise_transaction_id,
paid_date
from paid_payment_bill_view 
UNION ALL 
select 
id,
participant_id,
user_id,
participant_name,
participant_payment_method,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_project_amount,
payment_status,
total_amount,
job_position,
invoice_id,
invoice_number,
transferwise_transaction_id,
paid_date
from 
historical_payments_list_view;