



INSERT INTO `setting_categories` (`id`, `name`, `code`) VALUES (NULL, 'POS', 'pos');
UPDATE `settings` SET `category_id` = '6' WHERE `settings`.`id` = 10;


-- SAFE PAYMENT METHODS UPDATE SCRIPT
-- This script enhances the payment_methods table without breaking existing functionality
-- Run this script carefully in production

-- Step 1: Add new columns to payment_methods table (safe - won't break existing data)
ALTER TABLE `payment_methods` 
ADD COLUMN `display_name` varchar(100) DEFAULT NULL AFTER `method_name`,
ADD COLUMN `icon_class` varchar(50) DEFAULT NULL AFTER `display_name`,
ADD COLUMN `color_class` varchar(50) DEFAULT NULL AFTER `icon_class`,
ADD COLUMN `is_active` tinyint(1) DEFAULT '1' AFTER `color_class`,
ADD COLUMN `requires_bank_account` tinyint(1) DEFAULT '0' AFTER `is_active`,
ADD COLUMN `sort_order` int DEFAULT '0' AFTER `requires_bank_account`,
ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP AFTER `sort_order`;

-- Step 2: Update existing payment methods with proper data
UPDATE `payment_methods` SET 
    `display_name` = 'Cash Payment',
    `icon_class` = 'fas fa-money-bill-wave',
    `color_class` = 'btn-outline-success',
    `is_active` = 1,
    `requires_bank_account` = 0,
    `sort_order` = 1
WHERE `method_name` = 'cash';

UPDATE `payment_methods` SET 
    `display_name` = 'Card Payment',
    `icon_class` = 'fas fa-credit-card',
    `color_class` = 'btn-outline-primary',
    `is_active` = 1,
    `requires_bank_account` = 0,
    `sort_order` = 2
WHERE `method_name` = 'card';

-- Step 3: Insert missing payment methods that are used in the system
INSERT IGNORE INTO `payment_methods` (`method_name`, `display_name`, `icon_class`, `color_class`, `is_active`, `requires_bank_account`, `sort_order`) VALUES
('upi', 'UPI Payment', 'fas fa-mobile-alt', 'btn-outline-info', 1, 0, 3),
('bank', 'Bank Transfer', 'fas fa-university', 'btn-outline-warning', 1, 1, 4),
('credit', 'Credit Sale', 'fas fa-clock', 'btn-outline-secondary', 1, 0, 5);

-- Step 4: Create a backup of current payment methods (for safety)
CREATE TABLE IF NOT EXISTS `payment_methods_backup` AS SELECT * FROM `payment_methods`;

-- Verification query - run this to check the results
SELECT * FROM `payment_methods` ORDER BY `sort_order`;


ALTER TABLE `sales` MODIFY COLUMN `payment_method` VARCHAR(50) DEFAULT 'cash';






INSERT INTO setting_categories (name, code) 
VALUES ('System Settings', 'system_settings') 
ON DUPLICATE KEY UPDATE name = 'System Settings';



INSERT INTO settings (category_id, name, code, type, value, default_value, description, is_active) 
SELECT 
    (SELECT id FROM setting_categories WHERE code = 'system_settings' LIMIT 1),
    'System Timezone',
    'system_timezone',
    'select',
    'Asia/Karachi',
    'Asia/Karachi',
    'Set the timezone for all date and time operations in the system. This affects reports, logs, and all time-sensitive operations.',
    1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'system_timezone');





-- First, get the setting ID
SET @timezone_setting_id = (SELECT id FROM settings WHERE code = 'system_timezone' LIMIT 1);

-- Insert timezone options
INSERT INTO setting_options (setting_id, option_label, option_value) VALUES
(@timezone_setting_id, 'Pakistan (Karachi)', 'Asia/Karachi'),
(@timezone_setting_id, 'Pakistan (Lahore)', 'Asia/Karachi'),
(@timezone_setting_id, 'Pakistan (Islamabad)', 'Asia/Karachi'),
(@timezone_setting_id, 'India (Mumbai)', 'Asia/Kolkata'),
(@timezone_setting_id, 'India (Delhi)', 'Asia/Kolkata'),
(@timezone_setting_id, 'Bangladesh (Dhaka)', 'Asia/Dhaka'),
(@timezone_setting_id, 'Sri Lanka (Colombo)', 'Asia/Colombo'),
(@timezone_setting_id, 'Nepal (Kathmandu)', 'Asia/Kathmandu'),
(@timezone_setting_id, 'UAE (Dubai)', 'Asia/Dubai'),
(@timezone_setting_id, 'Saudi Arabia (Riyadh)', 'Asia/Riyadh'),
(@timezone_setting_id, 'Qatar (Doha)', 'Asia/Qatar'),
(@timezone_setting_id, 'Kuwait (Kuwait City)', 'Asia/Kuwait'),
(@timezone_setting_id, 'Bahrain (Manama)', 'Asia/Bahrain'),
(@timezone_setting_id, 'Oman (Muscat)', 'Asia/Muscat'),
(@timezone_setting_id, 'UK (London)', 'Europe/London'),
(@timezone_setting_id, 'USA (New York)', 'America/New_York'),
(@timezone_setting_id, 'USA (Los Angeles)', 'America/Los_Angeles'),
(@timezone_setting_id, 'Canada (Toronto)', 'America/Toronto'),
(@timezone_setting_id, 'Australia (Sydney)', 'Australia/Sydney'),
(@timezone_setting_id, 'UTC (Universal Time)', 'UTC');








INSERT INTO settings (category_id, name, code, type, value, default_value, description, is_active) 
SELECT 
    (SELECT id FROM setting_categories WHERE code = 'system_settings' LIMIT 1),
    'Date Display Format',
    'date_display_format',
    'select',
    'Y-m-d',
    'Y-m-d',
    'Choose how dates are displayed throughout the system (YYYY-MM-DD, DD/MM/YYYY, etc.)',
    1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'date_display_format');

-- Get the date format setting ID
SET @date_format_setting_id = (SELECT id FROM settings WHERE code = 'date_display_format' LIMIT 1);

-- Insert date format options
INSERT INTO setting_options (setting_id, option_label, option_value) VALUES
(@date_format_setting_id, 'YYYY-MM-DD (ISO)', 'Y-m-d'),
(@date_format_setting_id, 'DD/MM/YYYY', 'd/m/Y'),
(@date_format_setting_id, 'MM/DD/YYYY', 'm/d/Y'),
(@date_format_setting_id, 'DD-MM-YYYY', 'd-m-Y'),
(@date_format_setting_id, 'MM-DD-YYYY', 'm-d-Y'),
(@date_format_setting_id, 'DD.MM.YYYY', 'd.m.Y'),
(@date_format_setting_id, 'MM.DD.YYYY', 'm.d.Y');






-- Fix account_transactions table to support dynamic payment methods
-- This will allow any payment method to be saved, not just the hardcoded ENUM values

-- Step 1: Change account_type from ENUM to VARCHAR to support dynamic payment methods
ALTER TABLE `account_transactions` 
MODIFY COLUMN `account_type` VARCHAR(50) NOT NULL;

-- Step 2: Add an index on account_type for better performance
ALTER TABLE `account_transactions` 
ADD INDEX `idx_account_type` (`account_type`);

-- Step 3: Update any existing NULL account_type values to 'cash' as fallback
UPDATE `account_transactions` 
SET `account_type` = 'cash' 
WHERE `account_type` IS NULL OR `account_type` = '';

-- Step 4: Also update the sales table payment_method to support dynamic values
ALTER TABLE `sales` 
MODIFY COLUMN `payment_method` VARCHAR(50) DEFAULT 'cash';

-- Step 5: Add index on sales payment_method for better performance
ALTER TABLE `sales` 
ADD INDEX `idx_payment_method` (`payment_method`);

-- Step 6: Update any existing NULL payment_method values in sales table
UPDATE `sales` 
SET `payment_method` = 'cash' 
WHERE `payment_method` IS NULL OR `payment_method` = '';

-- Verification queries to run after the fix:
-- SELECT * FROM account_transactions ORDER BY created_at DESC LIMIT 5;
-- SELECT * FROM sales ORDER BY created_at DESC LIMIT 5; 









-- Add POS Image Display Setting

   INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
   VALUES (6, 'Display Images on POS', 'display_images_on_pos', 'boolean', '1', '1', 'Show product images on POS item cards', 1);









-- Kitchen Display System (KDS) Database Tables
-- This file contains all the necessary tables for a professional KDS

-- KDS Orders Table - Stores kitchen orders
CREATE TABLE IF NOT EXISTS `kds_orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale_id` int NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `order_number` varchar(50) NOT NULL,
  `table_number` varchar(20) DEFAULT NULL,
  `customer_name` varchar(100) DEFAULT NULL,
  `order_type` enum('dine_in','take_away','delivery') NOT NULL DEFAULT 'dine_in',
  `order_status` enum('pending','preparing','ready','served','cancelled') NOT NULL DEFAULT 'pending',
  `priority` enum('normal','urgent','rush') NOT NULL DEFAULT 'normal',
  `estimated_time` int DEFAULT NULL COMMENT 'Estimated preparation time in minutes',
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `prepared_at` timestamp NULL DEFAULT NULL,
  `served_at` timestamp NULL DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `prepared_by` int DEFAULT NULL,
  `served_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `sale_id` (`sale_id`),
  KEY `order_status` (`order_status`),
  KEY `priority` (`priority`),
  KEY `created_at` (`created_at`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- KDS Order Items Table - Stores individual items in kitchen orders
CREATE TABLE IF NOT EXISTS `kds_order_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kds_order_id` int NOT NULL,
  `sale_item_id` int NOT NULL,
  `item_id` int NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `item_status` enum('pending','preparing','ready','served') NOT NULL DEFAULT 'pending',
  `special_instructions` text,
  `preparation_notes` text,
  `prepared_at` timestamp NULL DEFAULT NULL,
  `served_at` timestamp NULL DEFAULT NULL,
  `prepared_by` int DEFAULT NULL,
  `served_by` int DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `kds_order_id` (`kds_order_id`),
  KEY `sale_item_id` (`sale_item_id`),
  KEY `item_id` (`item_id`),
  KEY `item_status` (`item_status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- KDS Stations Table - Different kitchen stations/preparation areas
CREATE TABLE IF NOT EXISTS `kds_stations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `color` varchar(7) DEFAULT '#007bff',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- KDS Station Items Table - Links items to specific stations
CREATE TABLE IF NOT EXISTS `kds_station_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `station_id` int NOT NULL,
  `item_id` int NOT NULL,
  `preparation_time` int DEFAULT NULL COMMENT 'Estimated preparation time in minutes',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `station_item_unique` (`station_id`, `item_id`),
  KEY `station_id` (`station_id`),
  KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- KDS Settings Table - KDS-specific settings
CREATE TABLE IF NOT EXISTS `kds_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- KDS Activity Log Table - Tracks all KDS activities
CREATE TABLE IF NOT EXISTS `kds_activity_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kds_order_id` int DEFAULT NULL,
  `kds_order_item_id` int DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `description` text,
  `user_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `kds_order_id` (`kds_order_id`),
  KEY `kds_order_item_id` (`kds_order_item_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Insert default KDS settings
INSERT INTO `kds_settings` (`setting_key`, `setting_value`, `description`) VALUES
('auto_refresh_interval', '30', 'Auto refresh interval in seconds'),
('sound_enabled', '1', 'Enable sound notifications'),
('alert_duration', '10', 'Alert display duration in seconds'),
('max_orders_display', '20', 'Maximum orders to display per station'),
('order_timeout_minutes', '45', 'Order timeout in minutes'),
('enable_priority_orders', '1', 'Enable priority order handling'),
('enable_special_instructions', '1', 'Enable special instructions display'),
('enable_preparation_notes', '1', 'Enable preparation notes'),
('enable_estimated_time', '1', 'Enable estimated preparation time'),
('enable_station_filtering', '1', 'Enable station-based filtering');

-- Insert default stations
INSERT INTO `kds_stations` (`name`, `description`, `color`, `sort_order`) VALUES
('Hot Kitchen', 'Main hot food preparation area', '#dc3545', 1),
('Cold Kitchen', 'Salads, cold dishes, and garnishes', '#17a2b8', 2),
('Grill Station', 'Grilled items and BBQ', '#fd7e14', 3),
('Fry Station', 'Deep fried items', '#ffc107', 4),
('Dessert Station', 'Desserts and sweets', '#e83e8c', 5),
('Beverage Station', 'Drinks and beverages', '#6f42c1', 6);

-- Add KDS settings to main settings table
INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) VALUES
(6, 'Enable Kitchen Display System', 'enable_kds', 'boolean', '1', '1', 'Enable Kitchen Display System for order management', 1),
(6, 'KDS Auto Refresh', 'kds_auto_refresh', 'boolean', '1', '1', 'Enable automatic refresh for KDS screens', 1),
(6, 'KDS Sound Notifications', 'kds_sound_notifications', 'boolean', '1', '1', 'Enable sound notifications for new orders', 1);



ALTER TABLE `items` ADD COLUMN `prep_time` INT NULL DEFAULT NULL COMMENT 'Preparation time in minutes for KDS';







-- Fix 1: Add hold_id to kds_orders
ALTER TABLE `kds_orders` 
ADD COLUMN `hold_id` int DEFAULT NULL AFTER `sale_id`,
ADD KEY `hold_id` (`hold_id`);

-- Fix 2: Add hold_item_id to kds_order_items  
ALTER TABLE `kds_order_items` 
ADD COLUMN `hold_item_id` int DEFAULT NULL AFTER `sale_item_id`,
ADD KEY `hold_item_id` (`hold_item_id`);

-- Fix 3: Add created_at to kds_order_items
ALTER TABLE `kds_order_items` 
ADD COLUMN `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP AFTER `sort_order`;

-- Fix 4: Add enable_kds setting
INSERT INTO `kds_settings` (`id`, `setting_key`, `setting_value`, `description`, `created_at`, `updated_at`) 
VALUES (11, 'enable_kds', '1', 'Enable KDS system', NOW(), NOW());

-- Fix 5: Update auto increment
ALTER TABLE `kds_settings` AUTO_INCREMENT = 12;



   ALTER TABLE kds_activity_log ADD COLUMN details TEXT AFTER action;
UPDATE kds_activity_log SET details = description WHERE details IS NULL AND description IS NOT NULL;






-- Order Taker Tracking System Update Script
-- This script adds order taker tracking to existing ITRPOS system

-- Step 1: Create order_takers table (if not already created)
CREATE TABLE IF NOT EXISTS `order_takers` (
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `employee_id` varchar(50) UNIQUE,
    `phone` varchar(20),
    `email` varchar(100),
    `commission_rate` decimal(5,2) DEFAULT 0.00,
    `user_id` int DEFAULT NULL,
    `is_active` tinyint(1) DEFAULT 1,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Step 2: Add order_taker_id columns to existing tables
ALTER TABLE `sales` ADD COLUMN `order_taker_id` INT DEFAULT NULL;
ALTER TABLE `hold_orders` ADD COLUMN `order_taker_id` INT DEFAULT NULL;
ALTER TABLE `kds_orders` ADD COLUMN `order_taker_id` INT DEFAULT NULL;

-- Step 3: Add indexes for better performance
ALTER TABLE `sales` ADD INDEX `idx_order_taker_id` (`order_taker_id`);
ALTER TABLE `hold_orders` ADD INDEX `idx_order_taker_id` (`order_taker_id`);
ALTER TABLE `kds_orders` ADD INDEX `idx_order_taker_id` (`order_taker_id`);

-- Step 4: Insert default order taker
INSERT INTO `order_takers` (`name`, `employee_id`, `phone`, `email`, `commission_rate`, `is_active`) 
VALUES ('Default Order Taker', 'EMP001', '', '', 0.00, 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Step 5: Update existing records with default order taker
-- Get the default order taker ID
SET @default_order_taker_id = (SELECT id FROM order_takers WHERE employee_id = 'EMP001' LIMIT 1);

-- Update existing sales records
UPDATE `sales` SET `order_taker_id` = @default_order_taker_id WHERE `order_taker_id` IS NULL;

-- Update existing hold orders
UPDATE `hold_orders` SET `order_taker_id` = @default_order_taker_id WHERE `order_taker_id` IS NULL;

-- Update existing KDS orders
UPDATE `kds_orders` SET `order_taker_id` = @default_order_taker_id WHERE `order_taker_id` IS NULL;

-- Step 6: Create sample order takers (optional - remove if not needed)
INSERT INTO `order_takers` (`name`, `employee_id`, `phone`, `email`, `commission_rate`, `is_active`) VALUES
('John Smith', 'EMP002', '+1234567890', 'john@example.com', 5.00, 1),
('Sarah Johnson', 'EMP003', '+1234567891', 'sarah@example.com', 3.50, 1),
('Mike Wilson', 'EMP004', '+1234567892', 'mike@example.com', 4.00, 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- Step 7: Verify the updates
SELECT 'Sales records updated:' as info, COUNT(*) as count FROM sales WHERE order_taker_id IS NOT NULL
UNION ALL
SELECT 'Hold orders updated:', COUNT(*) FROM hold_orders WHERE order_taker_id IS NOT NULL
UNION ALL
SELECT 'KDS orders updated:', COUNT(*) FROM kds_orders WHERE order_taker_id IS NOT NULL
UNION ALL
SELECT 'Order takers created:', COUNT(*) FROM order_takers;

-- Step 8: Create view for easy reporting
CREATE OR REPLACE VIEW `order_taker_summary` AS
SELECT 
    ot.id,
    ot.name,
    ot.employee_id,
    ot.commission_rate,
    COUNT(DISTINCT ho.id) as total_hold_orders,
    COUNT(DISTINCT s.id) as total_completed_orders,
    SUM(COALESCE(ho.total_amount, 0)) as total_hold_amount,
    SUM(COALESCE(s.total_amount, 0)) as total_completed_amount,
    AVG(COALESCE(ho.total_amount, 0)) as avg_hold_order,
    AVG(COALESCE(s.total_amount, 0)) as avg_completed_order,
    SUM(COALESCE(s.total_amount, 0) * ot.commission_rate / 100) as total_commission
FROM order_takers ot
LEFT JOIN hold_orders ho ON ot.id = ho.order_taker_id
LEFT JOIN sales s ON ot.id = s.order_taker_id
WHERE ot.is_active = 1
GROUP BY ot.id, ot.name, ot.employee_id, ot.commission_rate;

-- Success message
SELECT 'Order Taker Tracking System successfully installed!' as status;




-- Order Taker Password Update Script
-- This script adds password authentication for order takers

-- Add password column to order_takers table
ALTER TABLE `order_takers` ADD COLUMN `password` VARCHAR(255) DEFAULT NULL AFTER `commission_rate`;

-- Update existing order takers with a default password (change this in production)
-- Default password will be: 123456
UPDATE `order_takers` SET `password` = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi' WHERE `password` IS NULL;

-- Make password required for new entries
ALTER TABLE `order_takers` MODIFY COLUMN `password` VARCHAR(255) NOT NULL;

-- Add index for faster login lookups (employee_id only, since we query by it first)
ALTER TABLE `order_takers` ADD INDEX `idx_employee_id` (`employee_id`);

-- Verification query
SELECT 'Order takers with passwords:' as info, COUNT(*) as count FROM order_takers WHERE password IS NOT NULL;

-- Success message
SELECT 'Order Taker Password System successfully installed!' as status;


