-- 1 -- create table
CREATE TABLE `event` (
  id INT (11) NOT NULL,
  booking_date_time timestamp NOT NULL DEFAULT current_timestamp(),
  event_type_id INT (11) NOT NULL,
  package_id INT (11) NULL,
  package_name VARCHAR(255) NULL,
  client_name VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  groom VARCHAR(255) NULL,
  bride VARCHAR(255) NULL,
  birthday_person VARCHAR(255) NULL,
  org_name VARCHAR(255) NULL,
  venue VARCHAR(255) NOT NULL,
  event_date_time DATETIME NOT NULL,
  song VARCHAR(255) NULL,
  preference VARCHAR(255) NULL,
  amount INT(11) NOT NULL DEFAULT 0,
  update_by VARCHAR(255) NOT NULL DEFAULT 'sys',
  update_at timestamp NOT NULL DEFAULT current_timestamp(),
  status ENUM('open', 'completed', 'cancelled', 'postponed') NOT NULL DEFAULT 'open'
);

-- 2 -- check and delete junk file

# before delete check some table for REFERENCE
# event_payment WHERE booking_id
# assign_employee WHERE event_id

SELECT * FROM `vw_booking_with_payment` WHERE event_type='Select Event Type';

SELECT * FROM `vw_booking_with_payment` 
WHERE client_name LIKE '%test%' OR email LIKE '%test%' 
OR location LIKE '%test%' OR groom LIKE '%test%' 
OR bride LIKE '%test%' OR birthday_person LIKE '%test%' 
OR org_name LIKE '%test%' OR song LIKE '%test%';

SELECT * FROM `vw_booking_with_payment` 
WHERE client_name LIKE '%abc%' OR email LIKE '%abc%' 
OR location LIKE '%abc%' OR groom LIKE '%abc%' 
OR bride LIKE '%abc%' OR birthday_person LIKE '%abc%' 
OR org_name LIKE '%abc%' OR song LIKE '%abc%';

SELECT * FROM `vw_booking_with_payment` 
WHERE phone LIKE '%0000%';

SELECT * FROM `vw_booking_with_payment` 
WHERE package_amount <= 0 OR due_amount < 0
ORDER BY id ASC;

SELECT * FROM `vw_booking_with_payment` 
WHERE due_amount > 0 AND event_date_time < '2025-01-01'
ORDER BY id ASC;

SELECT event_id FROM event_payment WHERE event_id NOT IN (SELECT id FROM wp_tablesome_table_3209);

SELECT event_id FROM assign_employee WHERE event_id NOT IN (SELECT id FROM wp_tablesome_table_3209);

-- 3 -- CONVERT
ALTER TABLE `event_payment` CHANGE `booking_id` `event_id` INT(11) NOT NULL;
ALTER TABLE `event_payment` ADD `pay_method` ENUM('Cash', 'Bank Transfer', 'Mobile Banking', 'Cheque') NOT NULL DEFAULT 'Cash' AFTER `payment_date`;


INSERT INTO `event` (
  id,
  booking_date_time,
  event_type_id,
  package_id,
  package_name,
  client_name,
  phone,
  email,
  groom,
  bride,
  birthday_person,
  org_name,
  venue,
  event_date_time,
  song,
  preference,
  amount,
  update_by,
  update_at,
  status
)
SELECT
  v.id,
  v.booking_date_time,
  CASE
    WHEN v.event_type='Wedding' THEN 1
    WHEN v.event_type='Reception' THEN 2
    WHEN v.event_type='Engagement' THEN 3
    WHEN v.event_type='Halud' THEN 4
    WHEN v.event_type='Mehadi' THEN 5
    WHEN v.event_type='Akhd' THEN 6
    WHEN v.event_type='Anniversary' THEN 7
    WHEN v.event_type='Rong Khela' THEN 8
    WHEN v.event_type='Ashirbad' THEN 9
    WHEN v.event_type='Birthday' THEN 10
    WHEN v.event_type='Architectural' THEN 11
    WHEN v.event_type='Corporate' THEN 12
    WHEN v.event_type='Fashion & Lifetyle' THEN 13
    ELSE 0
  END AS event_type_id,
  CASE
    WHEN v.package_type = 'Standard - | - ' THEN 1
    WHEN v.package_type = 'Standard - || - ' THEN 2
    WHEN v.package_type = 'Standard - ||| - ' THEN 3
    WHEN v.package_type = 'Premium - | - ' THEN 4
    WHEN v.package_type = 'Premium - || - ' THEN 5
    WHEN v.package_type = 'Premium - ||| - ' THEN 6
    WHEN v.package_type = 'Elite - | - ' THEN 7
    WHEN v.package_type = 'Elite - || - ' THEN 8
    WHEN v.package_type = 'Sonaton - Standrad - ' THEN 9
    WHEN v.package_type = 'Sonaton - Premium - ' THEN 10
    WHEN v.package_type = 'Only Photography - ' THEN 11
    WHEN v.package_type = 'Only Photography - Premium - ' THEN 12
    WHEN v.package_type = 'Other - ' THEN 13
    ELSE 0
  END AS package_id,
  '' AS package_name,
  v.client_name,
  v.phone,
  v.email,
  v.groom,
  v.bride,
  v.birthday_person,
  v.org_name,
  v.location AS venue,
  v.event_date_time,
  v.song,
  v.preference,
  ifnull(v.package_amount, 0) AS amount,
  'old' AS update_by,
  CURRENT_TIMESTAMP AS update_at,
  'completed' AS status
FROM vw_booking_with_payment AS v
ORDER BY v.id ASC;

-- 4 -- update event table package name
UPDATE `event` AS e
JOIN `package` AS p ON p.id = e.package_id
SET e.package_name = p.name;

-- 5 -- CREATE SOME KEY
ALTER TABLE `event`
  ADD PRIMARY KEY (`id`),
  ADD KEY `event_type_id_fk` (`event_type_id`);

ALTER TABLE `event` 
ADD CONSTRAINT `event_type_id_fk` FOREIGN KEY (`event_type_id`) REFERENCES `event_type` (`id`);

ALTER TABLE `event` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;


ALTER TABLE `event_payment` DROP FOREIGN KEY `event_payment_event_id_fk`;
ALTER TABLE `event_payment` DROP INDEX `event_payment_event_id_fk`;

ALTER TABLE `event_payment` ADD KEY `event_payment_event_id_fk` (`event_id`);
ALTER TABLE `event_payment` ADD CONSTRAINT `event_payment_event_id_fk` FOREIGN KEY (`event_id`) REFERENCES `event`(`id`);

ALTER TABLE `assign_employee` DROP FOREIGN KEY `assign_employee_event_id_fk`;
ALTER TABLE `assign_employee` DROP INDEX `assign_employee_event_id_fk`;

ALTER TABLE `assign_employee` ADD KEY `assign_employee_event_id_fk` (`event_id`);
ALTER TABLE `assign_employee` ADD CONSTRAINT `assign_employee_event_id_fk` FOREIGN KEY (`event_id`) REFERENCES `event`(`id`);

-- 6 -- create table
CREATE TABLE event_addons (
  id INT NOT NULL AUTO_INCREMENT,
  event_id INT (11) NOT NULL,
  addons_id INT (11) NOT NULL,
  addons_name VARCHAR (255) NOT NULL,
  amount INT (11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  CONSTRAINT `event_id_fk` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),
  CONSTRAINT `addons_id_fk` FOREIGN KEY (`addons_id`) REFERENCES `addons` (`id`)
);

CREATE TABLE `bill_mst` (
  `bill_num` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `bill_amt` INT NOT NULL,
  `discount` INT NOT NULL DEFAULT 0,
  `ttl_bill_amt` INT NOT NULL,
  `create_by` VARCHAR(255) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_by` VARCHAR(255) NOT NULL,
  `update_at` timestamp NULL,
  PRIMARY KEY (`bill_num`)
);
-- //////////////////////

CREATE TABLE `bill_dtl` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `bill_num` INT NOT NULL,
  `event_id` INT NOT NULL,
  `event_name` VARCHAR(255) NULL,
  `event_date_time` DATETIME NOT NULL,
  `package_id` INT (11) NULL,
  `package_name` VARCHAR(255) NULL,
  `package_dtl` VARCHAR(255) NULL,
  `duration` VARCHAR(255) NOT NULL,
  `add_time` VARCHAR(50) NULL DEFAULT 0,
  `addons` VARCHAR(255) NULL DEFAULT '',
  `amount` INT NOT NULL,
  `total_amt` INT NOT NULL,
  `payment` INT NOT NULL DEFAULT 0,
  `due_amount` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `bill_dtl_bill_num_fk` FOREIGN KEY (`bill_num`) REFERENCES `bill_mst` (`bill_num`),
  CONSTRAINT `bill_dtl_event_id_fk` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),
  CONSTRAINT `bill_dtl_package_id_fk` FOREIGN KEY (`package_id`) REFERENCES `package` (`id`),
  UNIQUE `bill_dtl_event_id_uk` (`event_id`)
);

ALTER TABLE `event_payment` ADD `bill_num` INT NOT NULL DEFAULT 0 AFTER `invoice`;
ALTER TABLE `event_payment` ADD `pay_method` ENUM('Cash','Bank Transfer','Mobile Banking','Cheque','Discount') NOT NULL DEFAULT 'Cash';
ALTER TABLE `event_payment` ADD `received_from` VARCHAR(255) NOT NULL DEFAULT '' AFTER `pay_method`;
ALTER TABLE `event_payment` ADD `received_by` VARCHAR(255) NOT NULL DEFAULT '' AFTER `received_from`;

CREATE TABLE `event_addition_time` (
  `event_id` INT NOT NULL,
  `bill_num` INT NOT NULL,
  `duration_hours` FLOAT NOT NULL,
  `amount` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`event_id`),
  CONSTRAINT `event_a_t_event_id_fk` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),
  CONSTRAINT `event_a_t_bill_num_fk` FOREIGN KEY (`bill_num`) REFERENCES `bill_mst` (`bill_num`)
);

ALTER TABLE `assign_employee` CHANGE `shift_count` `shift_count` FLOAT NOT NULL DEFAULT '1';
ALTER TABLE `assign_employee` CHANGE `hour_count` `hour_count` FLOAT NOT NULL DEFAULT '0';

ALTER TABLE `package_details` ADD `order` INT NOT NULL DEFAULT '1' AFTER `details`;