CREATE OR REPLACE VIEW vw_booking_with_payment AS SELECT
    `wp_tablesome_table_3209`.`id` AS `id`,
    DATE_FORMAT(
        `wp_tablesome_table_3209`.`created_at`,
        '%d-%b-%Y'
    ) AS `Booking_Date`,
    DATE_FORMAT(
        `wp_tablesome_table_3209`.`created_at`,
        '%h:%i %p'
    ) AS `Booking_Time`,
    `wp_tablesome_table_3209`.`column_2` AS `Client_Name`,
    `wp_tablesome_table_3209`.`column_3` AS `Phone`,
    `wp_tablesome_table_3209`.`column_4` AS `Email`,
    `wp_tablesome_table_3209`.`column_5` AS `Event_Type`,
    SUBSTRING_INDEX(
        `wp_tablesome_table_3209`.`column_10`,
        '&#x09F3;',
        1
    ) 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
        (
            SUBSTRING_INDEX(
                `wp_tablesome_table_3209`.`column_10`,
                '&#x09F3;',
                -1
            ),
            ',',
            ''
        ) AS SIGNED
)
END AS `Package_Amount`,
DATE_FORMAT(
    FROM_UNIXTIME(
        FLOOR(
            (
                `wp_tablesome_table_3209`.`column_12` + 18000000
            ) / 1000
        )
    ),
    '%M-%Y'
) AS `Event_Month/Year`,
DATE_FORMAT(
    FROM_UNIXTIME(
        FLOOR(
            (
                `wp_tablesome_table_3209`.`column_12` + 18000000
            ) / 1000
        )
    ),
    '%d-%b-%Y'
) AS `Event_Date`,
DATE_FORMAT(FROM_UNIXTIME(FLOOR((`wp_tablesome_table_3209`.`column_12` + 18000000) / 1000)),'%h:%i %p') AS `Event_Time`,
FROM_UNIXTIME(FLOOR((`wp_tablesome_table_3209`.`column_12` + 18000000) / 1000)) AS `Event_DateTime`,
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
        (
            SUBSTRING_INDEX(
                `wp_tablesome_table_3209`.`column_10`,
                '&#x09F3;',
                -1
            ),
            ',',
            ''
        ) 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`.`booking_id` AS `booking_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
    `payment_info` `pt`
    ) `p`
ON
    (
        `wp_tablesome_table_3209`.`id` = `p`.`booking_id`
    )
)
GROUP BY
    `wp_tablesome_table_3209`.`id`
ORDER BY
    FLOOR(
        `wp_tablesome_table_3209`.`column_12` + 18000000
    )
DESC;