
-- Add display_color column to items table
ALTER TABLE `items` 
ADD COLUMN `display_color` VARCHAR(50) NULL DEFAULT NULL 
COMMENT 'Color for POS item display' 
AFTER `product_image`;

-- Add index for better performance
ALTER TABLE `items` 
ADD INDEX `idx_display_color` (`display_color`);




-- Add display_color column to sub_categories table
ALTER TABLE `sub_categories` 
ADD COLUMN `display_color` VARCHAR(50) NULL DEFAULT NULL 
COMMENT 'Color for POS subcategory display' 
AFTER `name`;

-- Add index for better performance
ALTER TABLE `sub_categories` 
ADD INDEX `idx_display_color` (`display_color`);






-- Dashboard translation updates
INSERT INTO translations (language_code, `key`, value, context)
SELECT new.language_code, new.`key`, new.value, new.context
FROM (
    -- Totals (labels)
    SELECT 'en' AS language_code, 'total_customer_outstanding' AS `key`, 'Total Customer Outstanding' AS value, 'labels' AS context UNION ALL
    SELECT 'ur', 'total_customer_outstanding', 'مجموعی کسٹمر واجبات', 'labels' UNION ALL
    SELECT 'fa', 'total_customer_outstanding', 'کل مطالبات مشتری', 'labels' UNION ALL
    SELECT 'ar', 'total_customer_outstanding', 'إجمالي مستحقات العملاء', 'labels' UNION ALL

    SELECT 'en', 'total_supplier_outstanding', 'Total Supplier Outstanding', 'labels' UNION ALL
    SELECT 'ur', 'total_supplier_outstanding', 'مجموعی سپلائر واجبات', 'labels' UNION ALL
    SELECT 'fa', 'total_supplier_outstanding', 'کل بدهی تأمین‌کننده', 'labels' UNION ALL
    SELECT 'ar', 'total_supplier_outstanding', 'إجمالي مستحقات الموردين', 'labels' UNION ALL

    -- Chart headings (labels)
    SELECT 'en', 'top_customers_sales', 'Top Customers Sales', 'labels' UNION ALL
    SELECT 'ur', 'top_customers_sales', 'سرِفہرست کسٹمر سیلز', 'labels' UNION ALL
    SELECT 'fa', 'top_customers_sales', 'پرفروش‌ترین مشتریان', 'labels' UNION ALL
    SELECT 'ar', 'top_customers_sales', 'أعلى مبيعات العملاء', 'labels' UNION ALL

    SELECT 'en', 'top_expense_categories', 'Top Expense Categories', 'labels' UNION ALL
    SELECT 'ur', 'top_expense_categories', 'سرِفہرست خرچ کی کیٹگریز', 'labels' UNION ALL
    SELECT 'fa', 'top_expense_categories', 'پراستفاده‌ترین دسته‌های هزینه', 'labels' UNION ALL
    SELECT 'ar', 'top_expense_categories', 'أعلى فئات المصروفات', 'labels' UNION ALL

    -- Empty-state messages
    SELECT 'en', 'no_customer_sales_data', 'No customer sales data available.', 'messages' UNION ALL
    SELECT 'ur', 'no_customer_sales_data', 'کسٹمر سیلز کا کوئی ڈیٹا دستیاب نہیں۔', 'messages' UNION ALL
    SELECT 'fa', 'no_customer_sales_data', 'داده‌ای برای فروش مشتری در دسترس نیست.', 'messages' UNION ALL
    SELECT 'ar', 'no_customer_sales_data', 'لا تتوفر بيانات مبيعات العملاء.', 'messages' UNION ALL

    SELECT 'en', 'no_expense_category_data', 'No expense category data available.', 'messages' UNION ALL
    SELECT 'ur', 'no_expense_category_data', 'خرچ کی کیٹگریز کا کوئی ڈیٹا دستیاب نہیں۔', 'messages' UNION ALL
    SELECT 'fa', 'no_expense_category_data', 'هیچ داده‌ای برای دسته‌های هزینه در دسترس نیست.', 'messages' UNION ALL
    SELECT 'ar', 'no_expense_category_data', 'لا تتوفر بيانات فئات المصروفات.', 'messages'
) AS new
ON DUPLICATE KEY UPDATE
    value   = new.value,
    context = new.context;



-- 1) Record both gross and net pricing for completed sales
ALTER TABLE sale_items
  ADD COLUMN original_unit_price DECIMAL(10,2) NULL AFTER unit_price,
  ADD COLUMN discount_type ENUM('none','percent','fixed') NOT NULL DEFAULT 'none' AFTER total_price,
  ADD COLUMN discount_value DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_type,
  ADD COLUMN discount_per_unit DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_value,
  ADD COLUMN discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_per_unit;

-- 2) Store the same details for held orders so “resume” restores discounts faithfully
ALTER TABLE hold_items
  ADD COLUMN original_unit_price DECIMAL(10,2) NULL AFTER unit_price,
  ADD COLUMN discount_type ENUM('none','percent','fixed') NOT NULL DEFAULT 'none' AFTER original_unit_price,
  ADD COLUMN discount_value DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_type,
  ADD COLUMN discount_per_unit DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_value,
  ADD COLUMN discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_per_unit;






  ALTER TABLE `hold_orders`
       ADD COLUMN `discount_type` ENUM('percent','fixed','none') DEFAULT 'none' AFTER `discount_id`,
       ADD COLUMN `discount_value` DECIMAL(10,2) DEFAULT 0.00 AFTER `discount_type`,
       ADD COLUMN `discount_amount` DECIMAL(10,2) DEFAULT 0.00 AFTER `discount_value`;



ALTER TABLE print_devices
    ADD COLUMN copies INT NOT NULL DEFAULT 1 AFTER print_mode;

UPDATE print_devices
    SET copies = 1
    WHERE copies IS NULL;


ALTER TABLE sales
    MODIFY COLUMN customer_name varchar(100) DEFAULT NULL;





-- Add Delivery Challan right (run once)
INSERT IGNORE INTO rights (name, description, category)
VALUES ('Delivery Challan', 'Access delivery challan listing and printing', 'Sales');

-- Grant right to Admin role (id 1); adjust role_id as needed
INSERT IGNORE INTO role_rights (role_id, right_id)
SELECT 1 AS role_id, r.id
FROM rights r
WHERE r.name = 'Delivery Challan';

-- Seed English labels (duplicate for other languages as required)
INSERT INTO translations (language_code, `key`, `value`, `context`)
VALUES
('en', 'delivery_challan', 'Delivery Challan', 'navigation'),
('en', 'delivery_challan_subtitle', 'Generate challans for delivered invoices', 'navigation'),
('en', 'print_selected', 'Print Selected', 'general'),
('en', 'search_invoice_placeholder', 'Search invoice #', 'general'),
('en', 'available_invoices', 'Available Invoices', 'general'),
('en', 'records', 'records', 'general'),
('en', 'delivery_address', 'Delivery Address', 'general'),
('en', 'delivery_status', 'Delivery Status', 'general'),
('en', 'no_invoices_found', 'No invoices found for the selected filters.', 'general'),
('en', 'print_challan', 'Print Challan', 'general')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);








-- Allow fractional quantities everywhere they flow through POS/KDS
ALTER TABLE hold_items
    MODIFY COLUMN quantity DECIMAL(12,3) DEFAULT NULL;

ALTER TABLE kds_order_items
    MODIFY COLUMN quantity        DECIMAL(12,3) NOT NULL,
    MODIFY COLUMN served_quantity DECIMAL(12,3) DEFAULT 0.000,
    MODIFY COLUMN total_quantity  DECIMAL(12,3) DEFAULT 0.000;

ALTER TABLE purchase_items
    MODIFY COLUMN quantity DECIMAL(12,3) NOT NULL;

ALTER TABLE purchase_return_items
    MODIFY COLUMN quantity DECIMAL(12,3) NOT NULL;

ALTER TABLE quotation_items
    MODIFY COLUMN quantity DECIMAL(12,3) NOT NULL;

ALTER TABLE sale_items
    MODIFY COLUMN quantity DECIMAL(12,3) NOT NULL;

ALTER TABLE sale_return_items
    MODIFY COLUMN quantity DECIMAL(12,3) NOT NULL;

ALTER TABLE void_items
    MODIFY COLUMN quantity DECIMAL(12,3) NOT NULL;


-- Insert report right (starting from ID 49)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) VALUES
(49, 'Serial Tracking Report', 'View serial number tracking report', 'Reports')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign the new report right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 49)
ON DUPLICATE KEY UPDATE `role_id` = VALUES(`role_id`), `right_id` = VALUES(`right_id`);


-- Add payment_method column to sale_returns table
ALTER TABLE `sale_returns` 
ADD COLUMN `payment_method` ENUM('cash', 'bank', 'card', 'upi') DEFAULT 'cash' AFTER `customer_remarks`,
ADD COLUMN `bank_account_id` INT NULL DEFAULT NULL AFTER `payment_method`,
ADD INDEX `idx_sale_returns_bank_account` (`bank_account_id`);




-- Add discount tax sequence setting (before/after GST)
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),
    'Discount Tax Sequence',
    'discount_tax_sequence',
    'select',
    'before_tax',
    'before_tax',
    'Choose whether discounts apply before GST (reduce taxable base) or after GST (on tax-inclusive total).',
    1
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'discount_tax_sequence');

-- Add setting options for discount tax sequence
INSERT INTO setting_options (setting_id, option_label, option_value)
SELECT 
    (SELECT id FROM settings WHERE code = 'discount_tax_sequence' LIMIT 1),
    'Before GST (reduce taxable amount)',
    'before_tax'
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options so 
    JOIN settings s ON so.setting_id = s.id 
    WHERE s.code = 'discount_tax_sequence' AND so.option_value = 'before_tax'
);

INSERT INTO setting_options (setting_id, option_label, option_value)
SELECT 
    (SELECT id FROM settings WHERE code = 'discount_tax_sequence' LIMIT 1),
    'After GST (discount tax-inclusive total)',
    'after_tax'
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options so 
    JOIN settings s ON so.setting_id = s.id 
    WHERE s.code = 'discount_tax_sequence' AND so.option_value = 'after_tax'
);