-- Hotel POS database repair for the current product/department/section code.
-- Run this ONCE in phpMyAdmin against the existing hotel POS database.
-- It fixes the exact "Unknown column section_id in products" error and the
-- related nullable SKU / purchase expiry changes required by the current ZIP.

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `products`
  ADD COLUMN `section_id` BIGINT UNSIGNED NULL AFTER `department_id`;

ALTER TABLE `products`
  MODIFY `sku` VARCHAR(255) NULL;

ALTER TABLE `products`
  ADD KEY `products_section_id_foreign` (`section_id`);

ALTER TABLE `products`
  ADD CONSTRAINT `products_section_id_foreign`
  FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`) ON DELETE SET NULL;

ALTER TABLE `purchase_order_items`
  ADD COLUMN `expiry_date` DATE NULL AFTER `received_quantity`;

-- Main/default section assignment.
UPDATE `products` p
JOIN `departments` d ON d.id = p.department_id
SET p.section_id = (SELECT id FROM `sections` WHERE type = 'bar' AND is_main = 1 ORDER BY id LIMIT 1)
WHERE d.slug = 'bar' AND p.section_id IS NULL;

UPDATE `products` p
SET p.section_id = (SELECT id FROM `sections` WHERE type = 'kitchen' AND is_main = 1 ORDER BY id LIMIT 1)
WHERE p.is_ingredient = 1 AND p.section_id IS NULL;

UPDATE `products` p
JOIN `departments` d ON d.id = p.department_id
SET p.section_id = (SELECT id FROM `sections` WHERE type = 'restaurant' AND is_main = 1 ORDER BY id LIMIT 1)
WHERE d.slug = 'restaurant' AND p.is_ingredient = 0 AND p.section_id IS NULL;

-- Restaurant menu items use ingredient/recipe stock, not finished-product stock.
UPDATE `products` p
JOIN `departments` d ON d.id = p.department_id
SET p.track_stock = 0, p.min_stock_level = 0, p.reorder_level = 0
WHERE d.slug = 'restaurant' AND p.is_ingredient = 0;

-- Add recipe units if they do not already exist.
INSERT INTO `units` (`name`, `abbreviation`, `base_unit_id`, `conversion_factor`, `created_at`, `updated_at`)
SELECT 'Milligrams', 'mg', u.id, 0.001, NOW(), NOW()
FROM `units` u
WHERE u.abbreviation = 'g'
  AND NOT EXISTS (SELECT 1 FROM `units` x WHERE x.abbreviation = 'mg')
LIMIT 1;

INSERT INTO `units` (`name`, `abbreviation`, `base_unit_id`, `conversion_factor`, `created_at`, `updated_at`)
SELECT 'Litres', 'L', u.id, 1000, NOW(), NOW()
FROM `units` u
WHERE u.abbreviation = 'ml'
  AND NOT EXISTS (SELECT 1 FROM `units` x WHERE x.abbreviation = 'L')
LIMIT 1;

SET FOREIGN_KEY_CHECKS=1;