CREATE TABLE `business_client` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `business_id` (`business_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `business_client_ibfk_1` FOREIGN KEY (`business_id`) REFERENCES `business` (`business_id`) ON DELETE CASCADE,
  CONSTRAINT `business_client_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

ALTER TABLE `employee` ADD `commission_percentage` DECIMAL(4,2) NOT NULL DEFAULT '0.00' COMMENT '0.00 to 99.99' AFTER `phone`;
ALTER TABLE `booking` ADD `commission_employee` FLOAT NOT NULL DEFAULT '0' COMMENT 'Calculated amount' AFTER `commission`;

-- Insert existing data from users table to business_client pivot table
INSERT INTO `business_client` (`business_id`, `user_id`)
SELECT `added_by`, `id`
FROM `users`
WHERE `added_by` IS NOT NULL;

CREATE TABLE `employee_attendances` (
    `id` bigint(20) AUTO_INCREMENT PRIMARY KEY,
    `emp_id` int(10) NOT NULL,
    `date` DATE NOT NULL,
    `is_present` tinyint(1) DEFAULT '0' NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL
);

ALTER TABLE `employee_attendances`
    ADD FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
    ADD UNIQUE KEY `unique_emp_date` (`emp_id`, `date`);


-- Drop the added_by column from the users table
ALTER TABLE `users` DROP `added_by`;