



-- KPRA Integration Database Tables
-- Following the same pattern as FBR integration

-- --------------------------------------------------------

--
-- Table structure for table `kpra_config`
--

DROP TABLE IF EXISTS `kpra_config`;
CREATE TABLE IF NOT EXISTS `kpra_config` (
  `id` int NOT NULL AUTO_INCREMENT,
  `is_enabled` tinyint(1) DEFAULT '0',
  `key` varchar(255) DEFAULT NULL,
  `business_id` varchar(50) DEFAULT NULL,
  `api_url` varchar(255) DEFAULT 'http://kpra.gov.pk/rims/integration/',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `kpra_invoices`
--

DROP TABLE IF EXISTS `kpra_invoices`;
CREATE TABLE IF NOT EXISTS `kpra_invoices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale_id` int NOT NULL,
  `kpra_invoice_number` varchar(100) NOT NULL,
  `qr_code_url` text,
  `qr_code_image` text,
  `response_status` varchar(10) DEFAULT NULL,
  `response_message` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `kpra_logs`
--

DROP TABLE IF EXISTS `kpra_logs`;
CREATE TABLE IF NOT EXISTS `kpra_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale_id` int DEFAULT NULL,
  `request_data` text,
  `response_data` text,
  `status_code` varchar(10) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Add KPRA Integration to setting_categories
--

INSERT INTO `setting_categories` (`name`, `code`) VALUES
('KPRA Integration', 'kpra_integration')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- --------------------------------------------------------






-- Add KPRA integration columns to sales table
ALTER TABLE `sales` 
ADD COLUMN `is_kpra_enabled` tinyint(1) DEFAULT '0' AFTER `is_fbr_enabled`,
ADD COLUMN `kpra_invoice_number` varchar(100) DEFAULT NULL AFTER `fbr_invoice_number`,
ADD COLUMN `kpra_qr_code_url` text AFTER `fbr_qr_data`;

-- Add indexes for better performance
ALTER TABLE `sales` 
ADD INDEX `idx_kpra_enabled` (`is_kpra_enabled`),
ADD INDEX `idx_kpra_invoice` (`kpra_invoice_number`);

-- --------------------------------------------------------

UPDATE `kpra_config` SET 
    `api_url` = 'https://kpra.gov.pk/rims/integration/',
    `updated_at` = CURRENT_TIMESTAMP
WHERE `id` = 1;

























CREATE TABLE IF NOT EXISTS `payment_method_tax_rates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `payment_method` varchar(50) NOT NULL,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `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 `payment_method` (`payment_method`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- =====================================================
-- 2. INSERT DEFAULT PAYMENT METHOD TAX RATES
-- =====================================================

INSERT INTO `payment_method_tax_rates` (`payment_method`, `tax_rate`) VALUES
('cash', 10.00),
('card', 6.00),
('upi', 6.00),
('bank', 6.00),
('credit', 0.00)
ON DUPLICATE KEY UPDATE 
  `tax_rate` = VALUES(`tax_rate`),
  `updated_at` = CURRENT_TIMESTAMP;

-- =====================================================
-- 3. ADD TAX SETTINGS CATEGORY
-- =====================================================

INSERT INTO `setting_categories` (`id`, `name`, `code`) VALUES 
(9, 'Tax Settings', 'tax_settings') 
ON DUPLICATE KEY UPDATE name = 'Tax Settings';

-- =====================================================
-- 4. ADD TAX CALCULATION METHOD SETTING
-- =====================================================

INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 
    (SELECT id FROM setting_categories WHERE code = 'tax_settings' LIMIT 1),
    'Tax Calculation Method',
    'tax_calculation_method',
    'select',
    'item_wise',
    'item_wise',
    'Choose how taxes are calculated: item-wise (per item) or payment method based (uniform rate per payment type)',
    1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'tax_calculation_method');

-- =====================================================
-- 5. ADD SETTING OPTIONS FOR TAX CALCULATION METHOD
-- =====================================================

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`) 
SELECT 
    (SELECT id FROM settings WHERE code = 'tax_calculation_method' LIMIT 1),
    'Item-wise Tax (Current)',
    'item_wise'
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options so 
    JOIN settings s ON so.setting_id = s.id 
    WHERE s.code = 'tax_calculation_method' AND so.option_value = 'item_wise'
);

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`) 
SELECT 
    (SELECT id FROM settings WHERE code = 'tax_calculation_method' LIMIT 1),
    'Payment Method Tax',
    'payment_method'
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options so 
    JOIN settings s ON so.setting_id = s.id 
    WHERE s.code = 'tax_calculation_method' AND so.option_value = 'payment_method'
);

-- =====================================================
-- 6. ADD DISPLAY SETTINGS
-- =====================================================

-- Show payment method tax rates on POS
INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 
    (SELECT id FROM setting_categories WHERE code = 'tax_settings' LIMIT 1),
    'Show Payment Method Tax Rates',
    'show_payment_method_tax_rates',
    'boolean',
    '1',
    '1',
    'Display tax rates for different payment methods on POS interface',
    1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'show_payment_method_tax_rates');

-- Enable tax preview on payment method selection
INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 
    (SELECT id FROM setting_categories WHERE code = 'tax_settings' LIMIT 1),
    'Enable Tax Preview on Payment Selection',
    'enable_tax_preview_on_payment',
    'boolean',
    '1',
    '1',
    'Show updated tax amount when payment method is selected (for payment method tax mode)',
    1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'enable_tax_preview_on_payment');

-- =====================================================
-- 7. VERIFICATION QUERIES
-- =====================================================

-- Check if payment method tax rates were created
SELECT 'Payment Method Tax Rates' as check_name, COUNT(*) as count FROM payment_method_tax_rates;

-- Check if tax settings were created
SELECT 'Tax Settings' as check_name, COUNT(*) as count FROM settings WHERE code LIKE '%tax%';

-- Check if setting options were created
SELECT 'Tax Setting Options' as check_name, COUNT(*) as count 
FROM setting_options so 
JOIN settings s ON so.setting_id = s.id 
WHERE s.code = 'tax_calculation_method';


















-- Void reasons table
CREATE TABLE void_reasons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reason_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Simple void orders table
CREATE TABLE void_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sale_id INT NOT NULL,
    void_reason_id INT NOT NULL,
    voided_by INT NOT NULL,
    voided_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    original_amount DECIMAL(10,2),
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (void_reason_id) REFERENCES void_reasons(id),
    FOREIGN KEY (voided_by) REFERENCES users(id)
);

-- Simple addition to sales table
ALTER TABLE sales ADD COLUMN void_id INT NULL;











INSERT INTO `setting_categories` (`id`, `name`, `code`) VALUES (NULL, 'KDS', 'kds');

INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 10, 'KDS Record Retention Days', 'kds_record_retention_days', 'number', '7', '7', 'Number of days to keep KDS records before automatic cleanup (1-90 days)', 1
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'kds_record_retention_days');

-- Add enable automatic cleanup setting
INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 10, 'Enable KDS Auto Cleanup', 'kds_auto_cleanup_enabled', 'boolean', '1', '1', 'Enable automatic cleanup of old KDS records', 1
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'kds_auto_cleanup_enabled');

-- Add last cleanup timestamp setting
INSERT INTO `settings` (`category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 10, 'KDS Last Cleanup Date', 'kds_last_cleanup_date', 'text', '', '', 'Timestamp of last KDS cleanup operation (system managed)', 0
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'kds_last_cleanup_date');

-- Create KDS cleanup log table for tracking cleanup operations
CREATE TABLE IF NOT EXISTS `kds_cleanup_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cleanup_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `retention_days` int DEFAULT 7,
  `orders_deleted` int DEFAULT 0,
  `order_items_deleted` int DEFAULT 0,
  `activity_logs_deleted` int DEFAULT 0,
  `total_records_deleted` int DEFAULT 0,
  `execution_time_ms` int DEFAULT 0,
  `status` enum('success','failed','partial') DEFAULT 'success',
  `message` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cleanup_date` (`cleanup_date`),
  KEY `idx_status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Insert initial cleanup log entry
INSERT INTO `kds_cleanup_log` (`cleanup_date`, `retention_days`, `status`, `message`) 
VALUES (NOW(), 7, 'success', 'KDS retention system initialized');


























-- --------------------------------------------------------
-- Table structure for table `item_modifier_groups`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `item_modifier_groups`;
CREATE TABLE IF NOT EXISTS `item_modifier_groups` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `description` text,
  `is_required` tinyint(1) DEFAULT '0' COMMENT '1 = customer must choose an option',
  `is_multiple_choice` tinyint(1) DEFAULT '0' COMMENT '1 = customer can select multiple options',
  `max_selections` int DEFAULT NULL COMMENT 'Maximum selections allowed when multiple choice',
  `min_selections` int DEFAULT '1' COMMENT 'Minimum selections required',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `is_active` (`is_active`),
  KEY `sort_order` (`sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- Table structure for table `item_modifiers`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `item_modifiers`;
CREATE TABLE IF NOT EXISTS `item_modifiers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `modifier_group_id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `short_name` varchar(50) DEFAULT NULL COMMENT 'For kitchen display',
  `price_adjustment` decimal(10,2) DEFAULT '0.00' COMMENT 'Price to add/subtract from base item',
  `cost_adjustment` decimal(10,2) DEFAULT '0.00' COMMENT 'Cost impact for inventory',
  `is_default` tinyint(1) DEFAULT '0' COMMENT '1 = selected by default',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int DEFAULT '0',
  `prep_time_adjustment` int DEFAULT '0' COMMENT 'Additional prep time in minutes',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `modifier_group_id` (`modifier_group_id`),
  KEY `is_active` (`is_active`),
  KEY `sort_order` (`sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- Table structure for table `item_modifier_group_assignments`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `item_modifier_group_assignments`;
CREATE TABLE IF NOT EXISTS `item_modifier_group_assignments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item_id` int NOT NULL,
  `modifier_group_id` int NOT NULL,
  `is_required` tinyint(1) DEFAULT '0' COMMENT 'Override group default for this item',
  `sort_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_modifier_unique` (`item_id`, `modifier_group_id`),
  KEY `item_id` (`item_id`),
  KEY `modifier_group_id` (`modifier_group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- Table structure for table `sale_item_modifiers`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sale_item_modifiers`;
CREATE TABLE IF NOT EXISTS `sale_item_modifiers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale_item_id` int NOT NULL,
  `modifier_id` int NOT NULL,
  `modifier_group_id` int NOT NULL,
  `modifier_name` varchar(100) NOT NULL COMMENT 'Store name for historical records',
  `price_adjustment` decimal(10,2) DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `sale_item_id` (`sale_item_id`),
  KEY `modifier_id` (`modifier_id`),
  KEY `modifier_group_id` (`modifier_group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



ALTER TABLE `hold_items` ADD COLUMN `modifiers` TEXT NULL COMMENT 'JSON array of selected modifiers' AFTER `tax_rate`;










-- For sale orders
ALTER TABLE sale_items ADD COLUMN remarks TEXT DEFAULT NULL;

-- For hold orders  
ALTER TABLE hold_items ADD COLUMN remarks TEXT DEFAULT NULL;





















-- Stock Transfer System Database Schema
-- This file contains all the necessary database changes for implementing stock transfer between stores

-- Add stores table
CREATE TABLE IF NOT EXISTS `stores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_name` varchar(100) NOT NULL,
  `store_code` varchar(20) NOT NULL UNIQUE,
  `address` text,
  `phone` varchar(20),
  `email` varchar(100),
  `manager_name` varchar(100),
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Add store-specific inventory table
CREATE TABLE IF NOT EXISTS `store_inventory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `store_id` int NOT NULL,
  `item_id` int NOT NULL,
  `quantity` decimal(12,6) DEFAULT '0.000000',
  `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `store_item_unique` (`store_id`, `item_id`),
  KEY `store_id` (`store_id`),
  KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Add stock transfers table
CREATE TABLE IF NOT EXISTS `stock_transfers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transfer_number` varchar(50) NOT NULL UNIQUE,
  `from_store_id` int NOT NULL,
  `to_store_id` int NOT NULL,
  `transfer_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` enum('pending','approved','in_transit','received','cancelled') DEFAULT 'pending',
  `total_items` int DEFAULT '0',
  `total_quantity` decimal(12,6) DEFAULT '0.000000',
  `notes` text,
  `requested_by` int DEFAULT NULL,
  `approved_by` int DEFAULT NULL,
  `received_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `from_store_id` (`from_store_id`),
  KEY `to_store_id` (`to_store_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Add stock transfer items table
CREATE TABLE IF NOT EXISTS `stock_transfer_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transfer_id` int NOT NULL,
  `item_id` int NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` decimal(12,6) NOT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `remarks` text,
  PRIMARY KEY (`id`),
  KEY `transfer_id` (`transfer_id`),
  KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Add store_id to inventory_movements table
ALTER TABLE `inventory_movements` 
ADD COLUMN `store_id` int DEFAULT NULL AFTER `item_id`,
ADD KEY `store_id` (`store_id`);

-- Extend inventory_movements reference_type enum to include transfer types
ALTER TABLE `inventory_movements` 
MODIFY COLUMN `reference_type` enum('purchase','sale','manual','other','sale_return','purchase_return','stock_transfer_out','stock_transfer_in') DEFAULT 'manual';

-- Insert default store (current store)
INSERT INTO `stores` (`store_name`, `store_code`, `address`, `phone`, `email`, `manager_name`, `is_active`) 
VALUES ('Main Store', 'MAIN', 'Main Store Address', '', '', 'Store Manager', 1);

-- Initialize store inventory for existing items
INSERT INTO `store_inventory` (`store_id`, `item_id`, `quantity`)
SELECT 1, i.id, 
COALESCE(SUM(CASE WHEN im.movement_type = 'in' THEN im.quantity ELSE 0 END), 0) -
COALESCE(SUM(CASE WHEN im.movement_type = 'out' THEN im.quantity ELSE 0 END), 0) +
COALESCE(SUM(CASE WHEN im.movement_type = 'adjustment' THEN im.quantity ELSE 0 END), 0) AS balance_qty
FROM items i
LEFT JOIN inventory_movements im ON i.id = im.item_id
WHERE i.item_role IN ('stock', 'material')
GROUP BY i.id;

-- Update existing inventory_movements to assign to main store
UPDATE `inventory_movements` SET `store_id` = 1 WHERE `store_id` IS NULL;














-- Multi-Store System Database Modifications (NO FOREIGN KEYS)
-- This file modifies the existing database to support complete multi-store functionality
-- ONLY includes actual tables (not views) from itrpos.sql database
-- NO FOREIGN KEY CONSTRAINTS to avoid stores table reference issues

-- 1. Add store_id to branch_info table to link branches with their default store
ALTER TABLE branch_info ADD COLUMN default_store_id INT NULL AFTER id;

-- 2. Add store_id to sales table for store-specific sales tracking
ALTER TABLE sales ADD COLUMN store_id INT NULL AFTER id;

-- 3. Add store_id to purchases table for store-specific purchase tracking
ALTER TABLE purchases ADD COLUMN store_id INT NULL AFTER id;

-- 4. Add store_id to purchase_items table for store-specific purchase items
ALTER TABLE purchase_items ADD COLUMN store_id INT NULL AFTER id;

-- 5. Add store_id to sale_items table for store-specific sales items
ALTER TABLE sale_items ADD COLUMN store_id INT NULL AFTER id;

-- 6. Add store_id to users table for store-specific user access
ALTER TABLE users ADD COLUMN assigned_store_id INT NULL AFTER id;

-- 7. Add store_id to customers table for store-specific customer tracking
ALTER TABLE customers ADD COLUMN primary_store_id INT NULL AFTER id;

-- 8. Add store_id to vendors table for store-specific vendor relationships
ALTER TABLE vendors ADD COLUMN primary_store_id INT NULL AFTER id;

-- 9. Add store_id to expenses table for store-specific expense tracking
ALTER TABLE expenses ADD COLUMN store_id INT NULL AFTER id;

-- 10. Add store_id to cus_payments table for store-specific customer payment tracking
ALTER TABLE cus_payments ADD COLUMN store_id INT NULL AFTER id;

-- 11. Add store_id to sup_payments table for store-specific supplier payment tracking
ALTER TABLE sup_payments ADD COLUMN store_id INT NULL AFTER id;

-- 12. Add store_id to sale_returns table for store-specific return tracking
ALTER TABLE sale_returns ADD COLUMN store_id INT NULL AFTER id;

-- 13. Add store_id to purchase_returns table for store-specific purchase return tracking
ALTER TABLE purchase_returns ADD COLUMN store_id INT NULL AFTER id;

-- 14. SKIP stock_transfers table - already has from_store_id and to_store_id columns

-- 15. Add store_id to stock_transfer_items table for store-specific transfer items
ALTER TABLE stock_transfer_items ADD COLUMN from_store_id INT NULL AFTER id;
ALTER TABLE stock_transfer_items ADD COLUMN to_store_id INT NULL AFTER id;

-- 16. Add store_id to settings table for store-specific settings
ALTER TABLE settings ADD COLUMN store_id INT NULL AFTER id;

-- 17. SKIP inventory_movements table - already has store_id column

-- 18. Add store_id to account_transactions table for store-specific account tracking
ALTER TABLE account_transactions ADD COLUMN store_id INT NULL AFTER id;

-- 19. Add store_id to shifts table for store-specific shift tracking
ALTER TABLE shifts ADD COLUMN store_id INT NULL AFTER id;

-- 20. Add store_id to shift_logs table for store-specific shift log tracking
ALTER TABLE shift_logs ADD COLUMN store_id INT NULL AFTER id;

-- 21. Add store_id to order_takers table for store-specific order taker tracking
ALTER TABLE order_takers ADD COLUMN assigned_store_id INT NULL AFTER id;

-- 22. SKIP order_taker_summary - this is a VIEW, not a table

-- 23. Add store_id to dinein_tables table for store-specific table tracking
ALTER TABLE dinein_tables ADD COLUMN store_id INT NULL AFTER id;

-- 24. Add store_id to dinein_floors table for store-specific floor tracking
ALTER TABLE dinein_floors ADD COLUMN store_id INT NULL AFTER id;

-- 25. Add store_id to delivery_orders table for store-specific delivery tracking
ALTER TABLE delivery_orders ADD COLUMN store_id INT NULL AFTER id;

-- 26. Add store_id to delivery_riders table for store-specific rider tracking
ALTER TABLE delivery_riders ADD COLUMN assigned_store_id INT NULL AFTER id;

-- 27. Add store_id to hold_orders table for store-specific hold tracking
ALTER TABLE hold_orders ADD COLUMN store_id INT NULL AFTER id;

-- 28. Add store_id to hold_items table for store-specific hold item tracking
ALTER TABLE hold_items ADD COLUMN store_id INT NULL AFTER id;

-- 29. Add store_id to kds_orders table for store-specific KDS tracking
ALTER TABLE kds_orders ADD COLUMN store_id INT NULL AFTER id;

-- 30. Add store_id to kds_order_items table for store-specific KDS item tracking
ALTER TABLE kds_order_items ADD COLUMN store_id INT NULL AFTER id;

-- 31. Add store_id to kds_stations table for store-specific KDS station tracking
ALTER TABLE kds_stations ADD COLUMN store_id INT NULL AFTER id;

-- 32. Add store_id to kds_station_items table for store-specific KDS station item tracking
ALTER TABLE kds_station_items ADD COLUMN store_id INT NULL AFTER id;

-- 33. Add store_id to kds_settings table for store-specific KDS settings
ALTER TABLE kds_settings ADD COLUMN store_id INT NULL AFTER id;

-- 34. Add store_id to kds_activity_log table for store-specific KDS activity tracking
ALTER TABLE kds_activity_log ADD COLUMN store_id INT NULL AFTER id;

-- 35. Add store_id to void_orders table for store-specific void tracking
ALTER TABLE void_orders ADD COLUMN store_id INT NULL AFTER id;

-- Create indexes for better performance
CREATE INDEX idx_sales_store_id ON sales(store_id);
CREATE INDEX idx_purchases_store_id ON purchases(store_id);
CREATE INDEX idx_sale_items_store_id ON sale_items(store_id);
CREATE INDEX idx_purchase_items_store_id ON purchase_items(store_id);
CREATE INDEX idx_users_store_id ON users(assigned_store_id);
CREATE INDEX idx_customers_store_id ON customers(primary_store_id);
CREATE INDEX idx_vendors_store_id ON vendors(primary_store_id);
CREATE INDEX idx_expenses_store_id ON expenses(store_id);
CREATE INDEX idx_cus_payments_store_id ON cus_payments(store_id);
CREATE INDEX idx_sup_payments_store_id ON sup_payments(store_id);
CREATE INDEX idx_sale_returns_store_id ON sale_returns(store_id);
CREATE INDEX idx_purchase_returns_store_id ON purchase_returns(store_id);
-- SKIP stock_transfers indexes - already exist
CREATE INDEX idx_stock_transfer_items_from_store_id ON stock_transfer_items(from_store_id);
CREATE INDEX idx_stock_transfer_items_to_store_id ON stock_transfer_items(to_store_id);
CREATE INDEX idx_settings_store_id ON settings(store_id);
-- SKIP inventory_movements indexes - already exist
CREATE INDEX idx_account_transactions_store_id ON account_transactions(store_id);
CREATE INDEX idx_shifts_store_id ON shifts(store_id);
CREATE INDEX idx_shift_logs_store_id ON shift_logs(store_id);
CREATE INDEX idx_order_takers_store_id ON order_takers(assigned_store_id);
-- SKIP order_taker_summary indexes - this is a view
CREATE INDEX idx_dinein_tables_store_id ON dinein_tables(store_id);
CREATE INDEX idx_dinein_floors_store_id ON dinein_floors(store_id);
CREATE INDEX idx_delivery_orders_store_id ON delivery_orders(store_id);
CREATE INDEX idx_delivery_riders_store_id ON delivery_riders(assigned_store_id);
CREATE INDEX idx_hold_orders_store_id ON hold_orders(store_id);
CREATE INDEX idx_hold_items_store_id ON hold_items(store_id);
CREATE INDEX idx_kds_orders_store_id ON kds_orders(store_id);
CREATE INDEX idx_kds_order_items_store_id ON kds_order_items(store_id);
CREATE INDEX idx_kds_stations_store_id ON kds_stations(store_id);
CREATE INDEX idx_kds_station_items_store_id ON kds_station_items(store_id);
CREATE INDEX idx_kds_settings_store_id ON kds_settings(store_id);
CREATE INDEX idx_kds_activity_log_store_id ON kds_activity_log(store_id);
CREATE INDEX idx_void_orders_store_id ON void_orders(store_id);

-- Update existing records to link with default store (assuming store_id = 1 is the main store)
UPDATE branch_info SET default_store_id = 1 WHERE id = 1;

-- Update existing sales records to link with default store
UPDATE sales SET store_id = 1 WHERE store_id IS NULL;

-- Update existing purchase records to link with default store
UPDATE purchases SET store_id = 1 WHERE store_id IS NULL;

-- Update existing sale_items records to link with default store
UPDATE sale_items SET store_id = 1 WHERE store_id IS NULL;

-- Update existing purchase_items records to link with default store
UPDATE purchase_items SET store_id = 1 WHERE store_id IS NULL;

-- Update existing users to link with default store
UPDATE users SET assigned_store_id = 1 WHERE assigned_store_id IS NULL;

-- Update existing customers to link with default store
UPDATE customers SET primary_store_id = 1 WHERE primary_store_id IS NULL;

-- Update existing vendors to link with default store
UPDATE vendors SET primary_store_id = 1 WHERE primary_store_id IS NULL;

-- Update existing expenses to link with default store
UPDATE expenses SET store_id = 1 WHERE store_id IS NULL;

-- Update existing cus_payments to link with default store
UPDATE cus_payments SET store_id = 1 WHERE store_id IS NULL;

-- Update existing sup_payments to link with default store
UPDATE sup_payments SET store_id = 1 WHERE store_id IS NULL;

-- Update existing sale_returns to link with default store
UPDATE sale_returns SET store_id = 1 WHERE store_id IS NULL;

-- Update existing purchase_returns to link with default store
UPDATE purchase_returns SET store_id = 1 WHERE store_id IS NULL;

-- SKIP stock_transfers - already have store columns

-- Update existing stock_transfer_items to link with default store
UPDATE stock_transfer_items SET from_store_id = 1, to_store_id = 1 WHERE from_store_id IS NULL OR to_store_id IS NULL;

-- SKIP inventory_movements - already have store columns

-- Update existing account_transactions to link with default store
UPDATE account_transactions SET store_id = 1 WHERE store_id IS NULL;

-- Update existing shifts to link with default store
UPDATE shifts SET store_id = 1 WHERE store_id IS NULL;

-- Update existing shift_logs to link with default store
UPDATE shift_logs SET store_id = 1 WHERE store_id IS NULL;

-- Update existing order_takers to link with default store
UPDATE order_takers SET assigned_store_id = 1 WHERE assigned_store_id IS NULL;

-- SKIP order_taker_summary - this is a view

-- Update existing dinein_tables to link with default store
UPDATE dinein_tables SET store_id = 1 WHERE store_id IS NULL;

-- Update existing dinein_floors to link with default store
UPDATE dinein_floors SET store_id = 1 WHERE store_id IS NULL;

-- Update existing delivery_orders to link with default store
UPDATE delivery_orders SET store_id = 1 WHERE store_id IS NULL;

-- Update existing delivery_riders to link with default store
UPDATE delivery_riders SET assigned_store_id = 1 WHERE assigned_store_id IS NULL;

-- Update existing hold_orders to link with default store
UPDATE hold_orders SET store_id = 1 WHERE store_id IS NULL;

-- Update existing hold_items to link with default store
UPDATE hold_items SET store_id = 1 WHERE store_id IS NULL;

-- Update existing kds_orders to link with default store
UPDATE kds_orders SET store_id = 1 WHERE store_id IS NULL;

-- Update existing kds_order_items to link with default store
UPDATE kds_order_items SET store_id = 1 WHERE store_id IS NULL;

-- Update existing kds_stations to link with default store
UPDATE kds_stations SET store_id = 1 WHERE store_id IS NULL;

-- Update existing kds_station_items to link with default store
UPDATE kds_station_items SET store_id = 1 WHERE store_id IS NULL;

-- Update existing kds_settings to link with default store
UPDATE kds_settings SET store_id = 1 WHERE store_id IS NULL;

-- Update existing kds_activity_log to link with default store
UPDATE kds_activity_log SET store_id = 1 WHERE store_id IS NULL;

-- Update existing void_orders to link with default store
UPDATE void_orders SET store_id = 1 WHERE store_id IS NULL;








-- Simple script to add default store functionality
-- This script works without any subquery issues

-- Step 1: Add is_default column to stores table
ALTER TABLE stores ADD COLUMN is_default TINYINT(1) DEFAULT 0 AFTER is_active;

-- Step 2: Set the first store (ID = 1) as default
UPDATE stores SET is_default = 1 WHERE id = 1;

-- Step 3: Update branch_info to link with the default store
UPDATE branch_info SET default_store_id = 1 WHERE id = 1;











ALTER TABLE customers ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;





ALTER TABLE sales ADD COLUMN delivery_charges DECIMAL(10,2) DEFAULT 0.00 AFTER discount_value;


