-- =============================================================================
-- Manual DB: online refunds use columns on `refunds` only (current app code).
-- Database: your schema name. Run each ALTER; ignore "Duplicate column" errors.
-- File: app/Database/manual_refunds_online_columns.sql
-- =============================================================================

ALTER TABLE `refunds` ADD COLUMN `geidea_order_id` VARCHAR(100) NULL DEFAULT NULL AFTER `detail`;
ALTER TABLE `refunds` ADD COLUMN `customer_mobile` VARCHAR(50) NULL DEFAULT NULL AFTER `geidea_order_id`;
ALTER TABLE `refunds` ADD COLUMN `online_ticket_amount` DECIMAL(12,2) NULL DEFAULT NULL AFTER `customer_mobile`;
ALTER TABLE `refunds` ADD COLUMN `online_net_amount` DECIMAL(12,2) NULL DEFAULT NULL AFTER `online_ticket_amount`;
ALTER TABLE `refunds` ADD COLUMN `online_payout_status` VARCHAR(20) NULL DEFAULT NULL AFTER `online_net_amount`;
ALTER TABLE `refunds` ADD COLUMN `refund_sent_by` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `online_payout_status`;
ALTER TABLE `refunds` ADD COLUMN `refund_sent_at` DATETIME NULL DEFAULT NULL AFTER `refund_sent_by`;
ALTER TABLE `refunds` ADD COLUMN `refund_payout_note` TEXT NULL DEFAULT NULL AFTER `refund_sent_at`;

-- Column meanings:
--   online_payout_status  NULL = office refund (not shown on Online Refund List).
--                         'pending' = awaiting payout / Money sent.
--                         'done' or 'complete' = finished row.
--   online_ticket_amount / online_net_amount = snapshot amounts for the online list.
--   geidea_order_id, customer_mobile = snapshot for Geidea / SMS.
--   refund_sent_by, refund_sent_at, refund_payout_note = after "Money sent".

-- =============================================================================
-- OPTIONAL — only if table `online_manual_refunds` exists (old installs)
-- Uncomment, run, then uncomment DROP at the end.
-- =============================================================================

-- UPDATE `refunds` r
-- INNER JOIN `online_manual_refunds` om ON om.refund_id = r.id AND om.refund_id > 0
-- SET
--   r.geidea_order_id      = om.geidea_order_id,
--   r.customer_mobile      = om.customer_mobile,
--   r.online_ticket_amount = om.ticket_amount,
--   r.online_net_amount    = om.refund_amount,
--   r.online_payout_status = om.status,
--   r.refund_sent_by       = om.refund_sent_by,
--   r.refund_sent_at       = om.refund_sent_at,
--   r.refund_payout_note   = om.refund_note;

-- UPDATE `refunds` r
-- INNER JOIN `online_manual_refunds` om
--   ON r.booking_id = om.booking_id AND r.track_table_id = CAST(om.ticket_id AS CHAR) AND r.type = 'ticket'
-- SET
--   r.geidea_order_id      = COALESCE(r.geidea_order_id, om.geidea_order_id),
--   r.customer_mobile      = COALESCE(r.customer_mobile, om.customer_mobile),
--   r.online_ticket_amount = COALESCE(r.online_ticket_amount, om.ticket_amount),
--   r.online_net_amount    = COALESCE(r.online_net_amount, om.refund_amount),
--   r.online_payout_status = COALESCE(r.online_payout_status, om.status),
--   r.refund_sent_by       = COALESCE(r.refund_sent_by, om.refund_sent_by),
--   r.refund_sent_at       = COALESCE(r.refund_sent_at, om.refund_sent_at),
--   r.refund_payout_note   = COALESCE(r.refund_payout_note, om.refund_note)
-- WHERE (om.refund_id IS NULL OR om.refund_id = 0);

-- DROP TABLE IF EXISTS `online_manual_refunds`;

-- =============================================================================
-- OPTIONAL — menus/permissions (only if missing). Parent = menu `ticket_booking`.
-- =============================================================================

-- SET @pid := (SELECT id FROM menus WHERE menu_title = 'ticket_booking' AND deleted_at IS NULL LIMIT 1);
-- INSERT INTO menus (menu_title, page_url, module_name, parent_menu_id, have_chield, created_by, created_at, updated_at, deleted_at)
-- SELECT 'refund_online_ticket','ticket/booking/refund-online-ticket','Ticket',CAST(@pid AS CHAR),NULL,'1',NOW(),NOW(),NULL
-- FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM menus WHERE menu_title='refund_online_ticket' AND deleted_at IS NULL);
-- SET @rid := (SELECT id FROM menus WHERE menu_title='refund_online_ticket' AND deleted_at IS NULL LIMIT 1);
-- INSERT INTO permissions (role_id,user_id,menu_id,menu_title,`create`,`read`,`edit`,`delete`,created_at,updated_at,deleted_at)
-- SELECT 1,1,@rid,'refund_online_ticket','1','1','0','0',NOW(),NOW(),NULL FROM DUAL
-- WHERE @rid IS NOT NULL AND NOT EXISTS (SELECT 1 FROM permissions WHERE menu_id=@rid AND role_id=1 AND deleted_at IS NULL);

-- SET @pid2 := (SELECT id FROM menus WHERE menu_title = 'ticket_booking' AND deleted_at IS NULL LIMIT 1);
-- INSERT INTO menus (menu_title, page_url, module_name, parent_menu_id, have_chield, created_by, created_at, updated_at, deleted_at)
-- SELECT 'manage_online_refunds','ticket/booking/onlinerefunds','Ticket',CAST(@pid2 AS CHAR),NULL,'1',NOW(),NOW(),NULL
-- FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM menus WHERE menu_title='manage_online_refunds' AND deleted_at IS NULL);
-- SET @mid := (SELECT id FROM menus WHERE menu_title='manage_online_refunds' AND deleted_at IS NULL LIMIT 1);
-- INSERT INTO permissions (role_id,user_id,menu_id,menu_title,`create`,`read`,`edit`,`delete`,created_at,updated_at,deleted_at)
-- SELECT 1,1,@mid,'manage_online_refunds','1','1','1','0',NOW(),NOW(),NULL FROM DUAL
-- WHERE @mid IS NOT NULL AND NOT EXISTS (SELECT 1 FROM permissions WHERE menu_id=@mid AND role_id=1 AND deleted_at IS NULL);
