DROP VIEW IF EXISTS historical_payment_admin_bill_view;
CREATE VIEW historical_payment_admin_bill_view (
id,
user_id,
author_name,
payment_transferwise_email,
payment_transferwise_currency_code,
historical_project_id,
project_name,
total_gross_amount,
payment_id,
payment_status,
job_position,
bills_status,
project_status,
estimated_payment_date
) AS
SELECT  pb.id                                                                                 AS id,
        us.id                                                                                 AS user_id,
        CONCAT(us.first_name_user, ' ', us.last_name_user)                                    AS author_name,
        us.payment_transferwise_email_user                                                    AS payment_transferwise_email,
        us.payment_transferwise_currency_code_user                                            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,
        pp.id                                                                                 AS payment_id,
        pp.status                                                                             AS payment_status,
        pb.invoice_type                                                                       AS job_position,
        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 user as us ON pb.user_id = us.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
        LEFT JOIN historical_offer_participant_payments AS pp ON pp.historical_offer_id = pb.id AND pp.user_id = us.id
WHERE (pp.status IS NULL OR pp.status <> 1) 
GROUP BY pb.id
HAVING (total_gross_amount > 0)