CREATE
OR REPLACE VIEW vw_booking_with_payment AS
SELECT
    `wp_tablesome_table_3209`.`id` AS `id`,
    `wp_tablesome_table_3209`.`created_at` AS `booking_date_time`,
    `wp_tablesome_table_3209`.`column_2` AS `client_name`,
    `wp_tablesome_table_3209`.`column_3` AS `phone`,
    `wp_tablesome_table_3209`.`column_6` AS `groom`,
    `wp_tablesome_table_3209`.`column_7` AS `bride`,
    `wp_tablesome_table_3209`.`column_8` AS `birthday_person`,
    `wp_tablesome_table_3209`.`column_9` AS `org_name`,
    `wp_tablesome_table_3209`.`column_13` AS `preference`,
    `wp_tablesome_table_3209`.`column_16` AS `song`,
    `wp_tablesome_table_3209`.`column_4` AS `email`,
    `wp_tablesome_table_3209`.`column_5` AS `event_type`,
    CASE
        WHEN column_10 LIKE '%&#x09F3;%' THEN substring_index (
            `wp_tablesome_table_3209`.`column_10`,
            '&#x09F3;',
            1
        )
        WHEN column_10 LIKE '%৳%' THEN substring_index (`wp_tablesome_table_3209`.`column_10`, '৳', 1)
        ELSE `wp_tablesome_table_3209`.`column_10`
    END AS `package_type`,
    `wp_tablesome_table_3209`.`column_11` AS `location`,
    case
        when cast(
            replace (`wp_tablesome_table_3209`.`column_15`, ',', '') as signed
        ) > 0 then cast(
            replace (`wp_tablesome_table_3209`.`column_15`, ',', '') as signed
        )
        else cast(
            REPLACE (
                CASE
                    WHEN `wp_tablesome_table_3209`.`column_10` LIKE '%&#x09F3;%' THEN substring_index (
                        `wp_tablesome_table_3209`.`column_10`,
                        '&#x09F3;',
                        -1
                    )
                    WHEN `wp_tablesome_table_3209`.`column_10` LIKE '%৳%' THEN substring_index (`wp_tablesome_table_3209`.`column_10`, '৳', -1)
                END,
                ',',
                ''
            ) AS SIGNED
        )
    end AS `package_amount`,
    from_unixtime (
        floor(
            (`wp_tablesome_table_3209`.`column_12` + 18000000) / 1000
        )
    ) AS `event_date_time`,
    sum(ifnull (`p`.`payment_amt`, 0)) AS `total_pay`,
    sum(ifnull (`p`.`Discount`, 0)) AS `discount`,
    case
        when cast(
            replace (`wp_tablesome_table_3209`.`column_15`, ',', '') as signed
        ) > 0 then cast(
            replace (`wp_tablesome_table_3209`.`column_15`, ',', '') as signed
        )
        else cast(
            replace (
                CASE
                    WHEN `wp_tablesome_table_3209`.`column_10` LIKE '%&#x09F3;%' THEN substring_index (
                        `wp_tablesome_table_3209`.`column_10`,
                        '&#x09F3;',
                        -1
                    )
                    WHEN `wp_tablesome_table_3209`.`column_10` LIKE '%৳%' THEN substring_index (`wp_tablesome_table_3209`.`column_10`, '৳', -1)
                END,
                ',',
                ''
            ) as signed
        )
    end - sum(ifnull (`p`.`payment_amt`, 0)) AS `due_amount`
from
    (
        `wp_tablesome_table_3209`
        left join (
            select
                `pt`.`id` AS `id`,
                `pt`.`invoice` AS `invoice`,
                `pt`.`event_id` AS `event_id`,
                `pt`.`payment_date` AS `payment_date`,
                `pt`.`payment_amt` AS `payment_amt`,
                `pt`.`comment` AS `comment`,
                `pt`.`update_by` AS `update_by`,
                case
                    when `pt`.`invoice` = 0 then `pt`.`payment_amt`
                    else 0
                end AS `Discount`
            from
                `event_payment` `pt`
        ) `p` on (`wp_tablesome_table_3209`.`id` = `p`.`event_id`)
    )
group by
    `wp_tablesome_table_3209`.`id`
order by
    floor(`wp_tablesome_table_3209`.`column_12` + 18000000) desc;