DROP VIEW IF EXISTS payment_bill_view;
CREATE VIEW payment_bill_view (id,
author_id,
user_id,
author_name,
payment_method,
payment_paypal_email,
payment_transferwise_email,
payment_transferwise_currency_code,
project_id,
project_name,
total_gross_amount,
total_copyscape_cost,
total_net_amount,
payment_id,
payment_status,
paypal_payout_batch_id,
paypal_status,
total_amount,
project_amount,
copyscape_cost,
job_position,
regional_manager_id,
bills_status,
project_status,
estimated_payment_date,
invoice_id,
invoice_number,
transferwise_transaction_id,
paid_date
) as 
SELECT
    pb.id AS id,
    COALESCE(a.id, e.id, u.id) AS author_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 author_name,
    COALESCE(
        a.payment_method,
        e.payment_method,
        u.payment_method
    ) AS payment_method,
    COALESCE(
        a.payment_paypal_email,
        e.payment_paypal_email
    ) AS payment_paypal_email,
    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(odb.total_gross_amount, 0),
        2
    ) AS total_gross_amount,
    ROUND(
        IFNULL(ofp.total_copyscape_cost, 0),
        2
    ) AS total_copyscape_cost,
    ROUND(
        IFNULL(odb.total_gross_amount, 0) - IFNULL(ofp.total_copyscape_cost, 0),
        2
    ) AS total_net_amount,
    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,
    pb.invoice_type AS job_position,
    p.user_id AS regional_manager_id,
    pb.status AS bills_status,
    p.status AS project_status,
    pb.estimated_payment_date AS estimated_payment_date,
    pb.invoice_id AS invoice_id,
    pb.invoice_number,
    pp.transferwise_transaction_id,
    pp.created_at AS paid_date
FROM
    offer_participant_bills AS pb
INNER JOIN projects AS p
ON
    pb.project_id = p.id
LEFT JOIN(
    SELECT
        SUM(price) AS total_gross_amount,
        offer_participant_bills_id
    FROM
        offer_deliveries_offer_bills
    GROUP BY
        offer_participant_bills_id
) AS odb
ON
    pb.id = odb.offer_participant_bills_id
INNER JOIN(
    SELECT
        SUM(op.copyscape_cost) AS total_copyscape_cost,
        op.user_id,
        op.participant_type,
        po.project_id
    FROM
        project_offers AS po
    INNER JOIN offer_partitipants AS op
    ON
        po.id = op.offer_id
    GROUP BY
        op.user_id,
        op.participant_type,
        po.project_id
) AS ofp
ON
    pb.user_id = ofp.user_id AND pb.invoice_type = ofp.participant_type AND p.id = ofp.project_id
LEFT JOIN authors AS a
ON
    pb.user_id = a.user_id
LEFT JOIN editors AS e
ON
    pb.user_id = e.user_id
LEFT JOIN user AS u
ON
    pb.user_id = u.id
LEFT JOIN offer_participant_payments AS pp
ON
    pp.offer_id = pb.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
    pb.id
HAVING
    (total_gross_amount > 0)
UNION ALL
SELECT
    pb.id AS id,
    COALESCE(u.id) AS author_id,
    COALESCE(u.id) AS user_id,
    CONCAT(
        COALESCE(u.first_name_user),
        ' ',
        COALESCE(u.last_name_user)
    ) AS author_name,
    u.payment_method AS payment_method,
    '' AS payment_paypal_email,
    u.payment_transferwise_email_user AS payment_transferwise_email,
    u.payment_transferwise_currency_code_user AS payment_transferwise_currency_code,
    p.id AS project_id,
    p.name AS project_name,
    pb.price AS total_gross_amount,
    '0' AS total_copyscape_cost,
    ROUND(pb.price, 2) AS total_net_amount,
    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,
    pb.invoice_type AS job_position,
    p.user_id AS regional_manager_id,
    pb.status AS bills_status,
    p.status AS project_status,
    pb.estimated_payment_date AS estimated_payment_date,
    pb.invoice_id AS invoice_id,
    pb.invoice_number,
    pp.transferwise_transaction_id,
    pp.created_at AS paid_date
FROM
    offer_participant_bills AS pb
INNER JOIN projects AS p
ON
    pb.project_id = p.id
LEFT JOIN user AS u
ON
    pb.user_id = u.id
LEFT JOIN offer_participant_payments AS pp
ON
    pp.offer_id = pb.id AND(pp.user_id = u.id)
WHERE
    (
        pp.status IS NULL OR pp.status <> 1
    ) AND pb.invoice_type = 12
GROUP BY
    pb.id
HAVING
    (total_gross_amount > 0)