-- CHECKLABS POS: update-only database patch
-- Target database: nwkbaebv_hotelbar (MySQL 8.0+)
-- This file alters an existing POS database. It is NOT a full database dump.
-- Back up the database before importing.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Ensure there is a starting branch for all existing data.
INSERT INTO `branches` (`name`, `code`, `is_active`, `created_at`, `updated_at`)
SELECT 'Main Branch', 'MAIN', 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `branches`);

SET @main_branch_id := (SELECT `id` FROM `branches` ORDER BY `id` LIMIT 1);

-- Users can be assigned to one or more branches.
CREATE TABLE IF NOT EXISTS `branch_user` (
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`branch_id`, `user_id`),
  KEY `branch_user_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every product has a branch catalogue assignment. Stock remains in that branch's stores.
CREATE TABLE IF NOT EXISTS `branch_product` (
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`branch_id`, `product_id`),
  KEY `branch_product_product_id_index` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add operational branch columns only when they do not already exist.
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='stores' AND column_name='branch_id')=0,
  'ALTER TABLE `stores` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `stores_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='restaurant_orders' AND column_name='branch_id')=0,
  'ALTER TABLE `restaurant_orders` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `restaurant_orders_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='restaurant_orders' AND column_name='customer_id')=0,
  'ALTER TABLE `restaurant_orders` ADD COLUMN `customer_id` BIGINT UNSIGNED NULL, ADD INDEX `restaurant_orders_customer_id_index` (`customer_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='shifts' AND column_name='branch_id')=0,
  'ALTER TABLE `shifts` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `shifts_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='expenses' AND column_name='branch_id')=0,
  'ALTER TABLE `expenses` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `expenses_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='dining_tables' AND column_name='branch_id')=0,
  'ALTER TABLE `dining_tables` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `dining_tables_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='bar_tabs' AND column_name='branch_id')=0,
  'ALTER TABLE `bar_tabs` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `bar_tabs_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_room_types' AND column_name='branch_id')=0,
  'ALTER TABLE `guest_room_types` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `guest_room_types_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_rooms' AND column_name='branch_id')=0,
  'ALTER TABLE `guest_rooms` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `guest_rooms_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='guest_room_bookings' AND column_name='branch_id')=0,
  'ALTER TABLE `guest_room_bookings` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `guest_room_bookings_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='butchery_batches' AND column_name='branch_id')=0,
  'ALTER TABLE `butchery_batches` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `butchery_batches_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='butchery_sales' AND column_name='branch_id')=0,
  'ALTER TABLE `butchery_sales` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL, ADD INDEX `butchery_sales_branch_id_index` (`branch_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='butchery_sales' AND column_name='payment_method')=0,
  'ALTER TABLE `butchery_sales` ADD COLUMN `payment_method` VARCHAR(50) NOT NULL DEFAULT ''cash''', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='butchery_sales' AND column_name='reference_number')=0,
  'ALTER TABLE `butchery_sales` ADD COLUMN `reference_number` VARCHAR(100) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Backfill every existing record to Main Branch, retaining existing data.
UPDATE `stores` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `restaurant_orders` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `shifts` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `expenses` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `dining_tables` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `bar_tabs` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `guest_room_types` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `guest_rooms` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `guest_room_bookings` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `butchery_batches` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;
UPDATE `butchery_sales` SET `branch_id`=@main_branch_id WHERE `branch_id` IS NULL;

-- Existing users and products remain available in Main Branch after the update.
INSERT IGNORE INTO `branch_user` (`branch_id`,`user_id`,`created_at`,`updated_at`)
SELECT @main_branch_id, `id`, NOW(), NOW() FROM `users`;
INSERT IGNORE INTO `branch_product` (`branch_id`,`product_id`,`created_at`,`updated_at`)
SELECT @main_branch_id, `id`, NOW(), NOW() FROM `products`;

-- Create one stock location for each branch and department, without merging balances.
INSERT INTO `stores` (`name`,`slug`,`department_id`,`branch_id`,`is_active`,`created_at`,`updated_at`)
SELECT CONCAT(b.`name`, ' - ', d.`name`, ' Store'),
       CONCAT('branch-', b.`id`, '-', d.`slug`, '-store'),
       d.`id`, b.`id`, 1, NOW(), NOW()
FROM `branches` b
CROSS JOIN `departments` d
LEFT JOIN `stores` s ON s.`branch_id`=b.`id` AND s.`department_id`=d.`id`
WHERE s.`id` IS NULL;

-- Independent room and butchery transaction records.
CREATE TABLE IF NOT EXISTS `guest_room_payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `method` VARCHAR(50) NOT NULL DEFAULT 'cash',
  `reference_number` VARCHAR(100) NULL,
  `received_by` BIGINT UNSIGNED NULL,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `guest_room_payments_booking_id_index` (`booking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `guest_room_expenses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `expense_date` DATE NOT NULL,
  `category` VARCHAR(120) NOT NULL,
  `description` VARCHAR(255) NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method` VARCHAR(50) NOT NULL DEFAULT 'cash',
  `user_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `guest_room_expenses_branch_date_index` (`branch_id`,`expense_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `butchery_wastages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `batch_id` BIGINT UNSIGNED NOT NULL,
  `quantity_kg` DECIMAL(12,3) NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `butchery_wastages_branch_id_index` (`branch_id`), KEY `butchery_wastages_batch_id_index` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `butchery_expenses` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `expense_date` DATE NOT NULL,
  `category` VARCHAR(120) NOT NULL,
  `description` VARCHAR(255) NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method` VARCHAR(50) NOT NULL DEFAULT 'cash',
  `user_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `butchery_expenses_branch_date_index` (`branch_id`,`expense_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Make room numbers and meat batch numbers unique within a branch, not globally.
SET @old_room_unique := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='guest_rooms' AND index_name='room_number' AND non_unique=0);
SET @new_room_unique := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='guest_rooms' AND index_name='guest_rooms_branch_room_unique');
SET @sql := IF(@old_room_unique>0, 'ALTER TABLE `guest_rooms` DROP INDEX `room_number`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(@new_room_unique=0, 'ALTER TABLE `guest_rooms` ADD UNIQUE KEY `guest_rooms_branch_room_unique` (`branch_id`,`room_number`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @old_batch_unique := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='butchery_batches' AND index_name='batch_number' AND non_unique=0);
SET @new_batch_unique := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='butchery_batches' AND index_name='butchery_batches_branch_batch_unique');
SET @sql := IF(@old_batch_unique>0, 'ALTER TABLE `butchery_batches` DROP INDEX `batch_number`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(@new_batch_unique=0, 'ALTER TABLE `butchery_batches` ADD UNIQUE KEY `butchery_batches_branch_batch_unique` (`branch_id`,`batch_number`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Enable both modules after the supporting operational tables are in place.
INSERT INTO `hotel_feature_settings` (`feature`,`is_enabled`,`created_at`,`updated_at`)
VALUES ('guest_rooms',1,NOW(),NOW()), ('butchery',1,NOW(),NOW())
ON DUPLICATE KEY UPDATE `is_enabled`=VALUES(`is_enabled`), `updated_at`=NOW();

-- Grant module access to the standard Manager role and read-only access to Accountants.
-- These additions preserve all existing customised permission keys and are idempotent.
SET @manager_permissions := CAST((SELECT `permissions` FROM `roles` WHERE `slug`='manager' LIMIT 1) AS JSON);
SET @manager_permissions := IF(JSON_CONTAINS(@manager_permissions, JSON_QUOTE('rooms.view')), @manager_permissions, JSON_ARRAY_APPEND(@manager_permissions, '$', 'rooms.view'));
SET @manager_permissions := IF(JSON_CONTAINS(@manager_permissions, JSON_QUOTE('rooms.manage')), @manager_permissions, JSON_ARRAY_APPEND(@manager_permissions, '$', 'rooms.manage'));
SET @manager_permissions := IF(JSON_CONTAINS(@manager_permissions, JSON_QUOTE('butchery.view')), @manager_permissions, JSON_ARRAY_APPEND(@manager_permissions, '$', 'butchery.view'));
SET @manager_permissions := IF(JSON_CONTAINS(@manager_permissions, JSON_QUOTE('butchery.manage')), @manager_permissions, JSON_ARRAY_APPEND(@manager_permissions, '$', 'butchery.manage'));
UPDATE `roles` SET `permissions`=CAST(@manager_permissions AS CHAR), `updated_at`=NOW() WHERE `slug`='manager' AND JSON_VALID(CAST(@manager_permissions AS CHAR));

SET @accountant_permissions := CAST((SELECT `permissions` FROM `roles` WHERE `slug`='accountant' LIMIT 1) AS JSON);
SET @accountant_permissions := IF(JSON_CONTAINS(@accountant_permissions, JSON_QUOTE('rooms.view')), @accountant_permissions, JSON_ARRAY_APPEND(@accountant_permissions, '$', 'rooms.view'));
SET @accountant_permissions := IF(JSON_CONTAINS(@accountant_permissions, JSON_QUOTE('butchery.view')), @accountant_permissions, JSON_ARRAY_APPEND(@accountant_permissions, '$', 'butchery.view'));
UPDATE `roles` SET `permissions`=CAST(@accountant_permissions AS CHAR), `updated_at`=NOW() WHERE `slug`='accountant' AND JSON_VALID(CAST(@accountant_permissions AS CHAR));

SET FOREIGN_KEY_CHECKS = 1;
-- End update-only patch. Verify branch assignments in Settings > Users after import.
