


INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT 
    NULL,
    6,
    'List Item Order',
    'pos_list_item_order',
    'select',
    'descending',
    'descending',
    'Select the order in which items appear in the POS list: Descending (newest at top) or Ascending (newest at bottom)',
    1
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'pos_list_item_order');

-- Get the setting ID for adding options
SET @setting_id = (SELECT id FROM settings WHERE code = 'pos_list_item_order' LIMIT 1);

-- Add setting options for POS List Item Order
INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`) 
SELECT @setting_id, 'Descending (Newest at Top)', 'descending'
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options 
    WHERE setting_id = @setting_id AND option_value = 'descending'
);

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`) 
SELECT @setting_id, 'Ascending (Newest at Bottom)', 'ascending'
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options 
    WHERE setting_id = @setting_id AND option_value = 'ascending'
);



-- Insert Bank Ledger right (ID 87)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (87, 'Bank Ledger', 'View bank account ledger with all transactions', 'Accounts')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign Bank Ledger right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 87)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);

-- Insert Cash Ledger right (ID 88)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (88, 'Cash Ledger', 'View cash account ledger with all transactions', 'Accounts')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign Cash Ledger right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 88)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);






-- Add Opening Balance Video Tutorial
INSERT INTO video_help (page_name, page_title, video_url, video_title, description, is_active) 
VALUES (
    'opening_balance',
    'Opening Balance Entry',
    'https://youtu.be/1aHUrWLW6QY?si=ITMxUjnbILqDUQPa',
    'Opening Balance Tutorial',
    'Learn how to enter opening balances for items, import from Excel, and manage your opening inventory.',
    1
)
ON DUPLICATE KEY UPDATE
    page_title = 'Opening Balance Entry',
    video_url = 'https://youtu.be/1aHUrWLW6QY?si=ITMxUjnbILqDUQPa',
    video_title = 'Opening Balance Tutorial',
    description = 'Learn how to enter opening balances for items, import from Excel, and manage your opening inventory.',
    is_active = 1;


-- Add Purchase List Video Tutorial
INSERT INTO video_help (page_name, page_title, video_url, video_title, description, is_active) 
VALUES (
    'purchase_list',
    'Purchase List',
    'https://youtu.be/z1tYF4LGdmI?si=4ai4ZhASvA_WeCc_',
    'Purchase List Tutorial',
    'Learn how to view, manage, and filter your purchase list, view purchase details, and print purchase receipts.',
    1
)
ON DUPLICATE KEY UPDATE
    page_title = 'Purchase List',
    video_url = 'https://youtu.be/z1tYF4LGdmI?si=4ai4ZhASvA_WeCc_',
    video_title = 'Purchase List Tutorial',
    description = 'Learn how to view, manage, and filter your purchase list, view purchase details, and print purchase receipts.',
    is_active = 1;



-- Add KPRA Integration Video Tutorial
INSERT INTO video_help (page_name, page_title, video_url, video_title, description, is_active) 
VALUES (
    'kpra_integration',
    'KPRA Integration',
    'https://youtu.be/vyQz6fpAU9I?si=jY9RubtBOnMCTmDs',
    'KPRA Integration Tutorial',
    'Learn how to configure KPRA integration settings, set up POS ID and private key, and manage KPRA invoice submissions.',
    1
)
ON DUPLICATE KEY UPDATE
    page_title = 'KPRA Integration',
    video_url = 'https://youtu.be/vyQz6fpAU9I?si=jY9RubtBOnMCTmDs',
    video_title = 'KPRA Integration Tutorial',
    description = 'Learn how to configure KPRA integration settings, set up POS ID and private key, and manage KPRA invoice submissions.',
    is_active = 1;







-- Insert General Ledger right (ID 106)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (106, 'General Ledger', 'View detailed transaction history for accounts', 'Financials')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign General Ledger right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 106)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);

-- Insert Trial Balance right (ID 107)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (107, 'Trial Balance', 'View trial balance report showing account balances', 'Financials')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign Trial Balance right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 107)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);

-- Insert Account Ledger right (ID 108)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (108, 'Account Ledger', 'View all cash and bank transactions with running balance', 'Financials')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign Account Ledger right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 108)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);

-- Insert Profit Chart right (ID 109)
INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (109, 'Profit Chart', 'View monthly profit analysis chart', 'Financials')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

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



INSERT INTO video_help (page_name, page_title, video_url, video_title, description, is_active)
VALUES (
    'profit_loss',
    'Profit & Loss',
    'https://youtu.be/E3-A0z5hu6I',
    'Profit & Loss Report Training',
    'Learn how to use the Profit & Loss report, filter by period or date range, and compare with previous period.',
    1
)
ON DUPLICATE KEY UPDATE
    page_title = 'Profit & Loss',
    video_url = 'https://youtu.be/E3-A0z5hu6I',
    video_title = 'Profit & Loss Report Training',
    description = 'Learn how to use the Profit & Loss report, filter by period or date range, and compare with previous period.',
    is_active = 1;



-- purchase_returns: purchase_id can be NULL for "direct return to supplier"
ALTER TABLE purchase_returns MODIFY COLUMN purchase_id INT NULL;

-- sup_outstanding: purchase_id can be NULL for vendor credit from direct returns
ALTER TABLE sup_outstanding MODIFY COLUMN purchase_id INT NULL;



INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`)
SELECT
    NULL,
    4,
    'Display Number of Guests (Dine-In)',
    'dine_in_show_number_of_guests',
    'boolean',
    '1',
    '1',
    'When enabled, selecting a dine-in table will ask for number of guests and display it. When disabled, the prompt and guest count are hidden.',
    1
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'dine_in_show_number_of_guests');





  ALTER TABLE `users`
    ADD COLUMN `pin_login_enabled` TINYINT(1) NOT NULL DEFAULT 0
    AFTER `pin_required_for_void`;





INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`)
SELECT NULL, 1, 'Next Invoice Number', 'next_invoice_number', 'number',
       COALESCE((SELECT MAX(id) FROM sales), 0) + 1,
       '1',
       'Internal: next sequential invoice number (do not edit manually)',
       0
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'next_invoice_number');



INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`)
SELECT NULL, 6, 'Reset invoice number on new shift', 'pos_reset_invoice_on_shift_start', 'boolean', '0', '0',
       'When enabled, invoice count starts from 1 (e.g. INV-000001) each time a new shift is opened. When disabled, invoice numbers continue in sequence across shifts.',
       1
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'pos_reset_invoice_on_shift_start');


ALTER TABLE hold_orders
ADD COLUMN reserved_invoice_number VARCHAR(20) DEFAULT NULL AFTER number_of_guests;



-- Index on sales.sale_date for date-range queries and reporting
ALTER TABLE `sales` ADD INDEX `idx_sales_sale_date` (`sale_date`);

-- Composite index on sale_items (item_id, sale_id) for fast "item used in sales?" check
ALTER TABLE `sale_items` ADD INDEX `idx_sale_items_item_sale` (`item_id`, `sale_id`);


-- account_transactions: date-range and sale+type lookups
ALTER TABLE `account_transactions` ADD INDEX `idx_account_transactions_created_at` (`created_at`);
ALTER TABLE `account_transactions` ADD INDEX `idx_account_transactions_sale_account` (`sale_id`, `account_type`);

-- inventory_movements: lookups by reference (e.g. movements for a sale) and by date
ALTER TABLE `inventory_movements` ADD INDEX `idx_inventory_movements_reference` (`reference_type`, `reference_id`);
ALTER TABLE `inventory_movements` ADD INDEX `idx_inventory_movements_created_at` (`created_at`);
ALTER TABLE `inventory_movements` ADD INDEX `idx_inventory_movements_item_reference` (`item_id`, `reference_type`, `reference_id`);



