CREATE OR REPLACE VIEW vw_event AS 
SELECT e.*, et.name AS event_name, 
CASE 
    WHEN e.event_type_id IN (10,11,12) THEN e.client_name
    ELSE CONCAT(e.bride, ' & ', e.groom)
END AS name,
ifnull(aat.duration_hours, 0) AS duration_hours,
ifnull(aat.amount, 0) AS hours_amt,
ifnull(ad.amount, 0) AS add_ons_amt, 
(ifnull(e.amount, 0)+ifnull(aat.amount, 0)+ifnull(ad.amount, 0)) AS total_amt,
ifnull(p.payment_amt, 0) AS total_pay,
((ifnull(e.amount, 0)+ifnull(aat.amount, 0)+ifnull(ad.amount, 0))-ifnull(p.payment_amt, 0)) AS due_amount
FROM event AS e
LEFT JOIN (
    SELECT 
    eat.event_id,
    eat.duration_hours,
    eat.amount
    FROM event_addition_time AS eat
) AS aat ON e.id = aat.event_id
LEFT JOIN (
    SELECT 
    ead.event_id, 
    sum(ifnull(ead.amount, 0)) AS amount 
    FROM event_addons AS ead GROUP BY ead.event_id
) AS ad ON e.id = ad.event_id
LEFT JOIN event_type AS et ON e.event_type_id = et.id
LEFT JOIN (
    SELECT
    pi.event_id AS event_id,
    sum(ifnull(pi.payment_amt, 0)) AS payment_amt
    FROM event_payment AS pi GROUP BY pi.event_id
) AS p ON e.id = p.event_id;