


-- Insert Profit & Loss Report right
-- Using ID 51 (49 is Serial Tracking Report, 50 is FBR Sales Report)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (51, 'Profit & Loss', 'View profit and loss statement report', 'Reports')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign Profit & Loss Report right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 51)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);


ALTER TABLE `sales` 
CHANGE COLUMN `delivery_charges` `service_charges` DECIMAL(10,2) DEFAULT '0.00';






INSERT INTO `translations` (`language_code`, `key`, `value`, `context`) VALUES
-- English translations
('en', 'hold_orders', 'Hold Orders', 'buttons'),
('en', 'hold', 'Hold', 'buttons'),
('en', 'transaction', 'Transaction', 'buttons'),
('en', 'transaction_id', 'Transaction ID', 'labels'),
('en', 'products', 'Products', 'labels'),
('en', 'subcategories', 'Subcategories', 'labels'),
('en', 'order_tax', 'Order Tax', 'labels'),
('en', 'service_charges', 'Service Charges', 'labels'),
('en', 'withholding', 'Withholding', 'labels'),
('en', 'walk_in_customer', 'Walk-in Customer', 'labels'),
('en', 'select_customer', 'Select Customer', 'labels'),
('en', 'select_category_view_subcategories', 'Select a category to view subcategories', 'messages'),
('en', 'payment', 'Payment', 'buttons'),
('en', 'void', 'Void', 'buttons'),

-- Farsi (Persian) translations
('fa', 'hold_orders', 'سفارشات نگه‌داشته', 'buttons'),
('fa', 'hold', 'نگه‌داری', 'buttons'),
('fa', 'transaction', 'تراکنش', 'buttons'),
('fa', 'transaction_id', 'شناسه تراکنش', 'labels'),
('fa', 'products', 'محصولات', 'labels'),
('fa', 'subcategories', 'زیر دسته‌ها', 'labels'),
('fa', 'order_tax', 'مالیات سفارش', 'labels'),
('fa', 'service_charges', 'هزینه خدمات', 'labels'),
('fa', 'withholding', 'کسر مالیات', 'labels'),
('fa', 'walk_in_customer', 'مشتری بدون ثبت نام', 'labels'),
('fa', 'select_customer', 'انتخاب مشتری', 'labels'),
('fa', 'select_category_view_subcategories', 'برای مشاهده زیر دسته‌ها، یک دسته انتخاب کنید', 'messages'),
('fa', 'payment', 'پرداخت', 'buttons'),
('fa', 'void', 'لغو', 'buttons'),

-- Urdu translations
('ur', 'hold_orders', 'ہولڈ آرڈرز', 'buttons'),
('ur', 'hold', 'ہولڈ', 'buttons'),
('ur', 'transaction', 'ٹرانزیکشن', 'buttons'),
('ur', 'transaction_id', 'ٹرانزیکشن آئی ڈی', 'labels'),
('ur', 'products', 'پروڈکٹس', 'labels'),
('ur', 'subcategories', 'ذیلی کیٹگریز', 'labels'),
('ur', 'order_tax', 'آرڈر ٹیکس', 'labels'),
('ur', 'service_charges', 'سروس چارجز', 'labels'),
('ur', 'withholding', 'وائٹ ہولڈنگ', 'labels'),
('ur', 'walk_in_customer', 'واک ان کسٹمر', 'labels'),
('ur', 'select_customer', 'کسٹمر منتخب کریں', 'labels'),
('ur', 'select_category_view_subcategories', 'ذیلی کیٹگریز دیکھنے کے لیے ایک کیٹگری منتخب کریں', 'messages'),
('ur', 'payment', 'پیمنٹ', 'buttons'),
('ur', 'void', 'وائڈ', 'buttons'),

-- Arabic translations
('ar', 'hold_orders', 'الطلبات المحفوظة', 'buttons'),
('ar', 'hold', 'حفظ', 'buttons'),
('ar', 'transaction', 'المعاملة', 'buttons'),
('ar', 'transaction_id', 'رقم المعاملة', 'labels'),
('ar', 'products', 'المنتجات', 'labels'),
('ar', 'subcategories', 'الفئات الفرعية', 'labels'),
('ar', 'order_tax', 'ضريبة الطلب', 'labels'),
('ar', 'service_charges', 'رسوم الخدمة', 'labels'),
('ar', 'withholding', 'الاستقطاع', 'labels'),
('ar', 'walk_in_customer', 'عميل بدون تسجيل', 'labels'),
('ar', 'select_customer', 'اختر العميل', 'labels'),
('ar', 'select_category_view_subcategories', 'اختر فئة لعرض الفئات الفرعية', 'messages'),
('ar', 'payment', 'الدفع', 'buttons'),
('ar', 'void', 'إلغاء', 'buttons')

ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- English: Subtotal → Net Total
UPDATE `translations` 
SET `value` = 'Net Total' 
WHERE `language_code` = 'en' AND `key` = 'subtotal' AND `context` = 'labels';

-- Arabic: المجموع الفرعي → المجموع الصافي (Net Total)
UPDATE `translations` 
SET `value` = 'المجموع الصافي' 
WHERE `language_code` = 'ar' AND `key` = 'subtotal' AND `context` = 'labels';

-- Farsi/Persian: زیرمجموع → مجموع خالص (Net Total)
UPDATE `translations` 
SET `value` = 'مجموع خالص' 
WHERE `language_code` = 'fa' AND `key` = 'subtotal' AND `context` = 'labels';

-- Urdu: ذیلی کل → خالص کل (Net Total)
UPDATE `translations` 
SET `value` = 'خالص کل' 
WHERE `language_code` = 'ur' AND `key` = 'subtotal' AND `context` = 'labels';


UPDATE `settings` SET `value` = 'IT Resources | 0311-391-7777' WHERE `settings`.`id` = 25;






CREATE TABLE IF NOT EXISTS `deals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item_id` int NOT NULL COMMENT 'The deal item itself (e.g., abc deal)',
  `deal_name` varchar(100) NOT NULL,
  `deal_price` decimal(10,2) NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_id` (`item_id`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE IF NOT EXISTS `deal_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deal_id` int NOT NULL,
  `selection_type` enum('category','subcategory','item') NOT NULL,
  `category_id` int DEFAULT NULL,
  `sub_category_id` int DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `deal_id` (`deal_id`),
  KEY `category_id` (`category_id`),
  KEY `sub_category_id` (`sub_category_id`),
  KEY `item_id` (`item_id`),
  KEY `selection_type` (`selection_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================
-- 3. Add deal columns to hold_items table
-- ============================================
ALTER TABLE `hold_items` 
ADD COLUMN `is_deal` tinyint(1) DEFAULT '0' COMMENT '1 if this item is a deal',
ADD COLUMN `deal_selections` text COMMENT 'JSON array of selected deal items';

-- ============================================
-- 4. Add deal columns to sale_items table
-- ============================================
ALTER TABLE `sale_items` 
ADD COLUMN `is_deal` tinyint(1) DEFAULT '0' COMMENT '1 if this item is a deal',
ADD COLUMN `deal_selections` text COMMENT 'JSON array of selected deal items';

-- ============================================
-- 5. Add indexes for performance
-- ============================================
ALTER TABLE `hold_items` ADD INDEX `idx_is_deal` (`is_deal`);
ALTER TABLE `sale_items` ADD INDEX `idx_is_deal` (`is_deal`);



ALTER TABLE `deal_items` 
ADD COLUMN `min_selection` int DEFAULT '1' COMMENT 'Minimum number of items to select',
ADD COLUMN `max_selection` int DEFAULT '1' COMMENT 'Maximum number of items to select';


ALTER TABLE `expenses` 
ADD COLUMN `shift_id` INT NULL AFTER `store_id`;

ALTER TABLE `expenses`
ADD INDEX `idx_expenses_shift_id` (`shift_id`);


INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
VALUES (NULL, 1, 'KOT Font Size', 'kot_font_size', 'number', '10', '10', 'Font size for Kitchen Order Tickets (KOT) when printing to LAN printers', 1);