
ALTER TABLE `customers` 
ADD COLUMN `withholding_rate` DECIMAL(5,2) DEFAULT 0.00 AFTER `discount_rate`;

-- 2. Add withholding_amount column to sales table
ALTER TABLE `sales` 
ADD COLUMN `withholding_amount` DECIMAL(10,2) DEFAULT 0.00 AFTER `discount_amount`;

-- 3. Update existing customers to have default withholding values (optional)
UPDATE `customers` SET `withholding_rate` = 0.00 WHERE `withholding_rate` IS NULL;



-- 1) Manual customer balance ledger
CREATE TABLE IF NOT EXISTS `customer_balance_entries` (
    `id` int NOT NULL AUTO_INCREMENT,
    `entry_number` varchar(30) NOT NULL,
    `customer_id` int NOT NULL,
    `store_id` int DEFAULT 1,
    `entry_type` enum('receivable','advance') NOT NULL DEFAULT 'receivable',
    `amount` decimal(12,2) NOT NULL,
    `remaining_amount` decimal(12,2) NOT NULL,
    `reference_no` varchar(50) DEFAULT NULL,
    `remarks` varchar(255) DEFAULT NULL,
    `map_account_id` int DEFAULT NULL,
    `journal_entry_id` int DEFAULT NULL,
    `status` enum('posted','void') NOT NULL DEFAULT 'posted',
    `created_by` int NOT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_entry_number` (`entry_number`),
    KEY `idx_customer_balance_customer` (`customer_id`),
    KEY `idx_customer_balance_status` (`status`),
    KEY `idx_customer_balance_type` (`entry_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 2) Link customer payments to manual balance entries
ALTER TABLE `cus_payments`
ADD COLUMN `balance_entry_id` int DEFAULT NULL AFTER `sale_id`;

ALTER TABLE `cus_payments`
ADD KEY `idx_cus_payments_balance_entry_id` (`balance_entry_id`);



ALTER TABLE `items`
ADD COLUMN `is_third_schedule` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_serialized`;

ALTER TABLE `sales` 
DROP COLUMN `fbr_invoice_number`;


INSERT INTO `rights` (`id`, `name`, `description`, `category`) 
VALUES (50, 'FBR Sales Report', 'View FBR integrated sales report', 'Reports')
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `category` = VALUES(`category`);

-- Assign FBR Sales Report right to Admin role (role_id = 1)
INSERT INTO `role_rights` (`role_id`, `right_id`) 
VALUES (1, 50)
ON DUPLICATE KEY UPDATE 
    `role_id` = VALUES(`role_id`), 
    `right_id` = VALUES(`right_id`);



ALTER TABLE `sales` 
DROP COLUMN `kpra_invoice_number`;

ALTER TABLE `sales` 
DROP COLUMN `kpra_qr_code_url`;

ALTER TABLE `sales` 
DROP COLUMN `fbr_qr_data`;



CREATE TABLE IF NOT EXISTS `terminals` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `terminal_name` VARCHAR(100) NOT NULL,
  `computer_name` VARCHAR(100) NULL,
  `ip_address` VARCHAR(50) NULL,
  `terminal_type` VARCHAR(50) NULL DEFAULT 'POS',
  `branch_id` INT(11) NULL,
  `store_id` INT(11) NULL,
  `description` TEXT NULL,
  `is_active` TINYINT(1) DEFAULT '1',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_computer_name` (`computer_name`),
  INDEX `idx_branch_id` (`branch_id`),
  INDEX `idx_store_id` (`store_id`),
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


ALTER TABLE `print_devices` 
ADD COLUMN `terminal_id` INT(11) NULL AFTER `print_type`,
ADD INDEX `idx_terminal_id` (`terminal_id`);


ALTER TABLE `sales` 
ADD COLUMN `terminal_id` INT(11) NULL AFTER `id`,
ADD INDEX `idx_sales_terminal_id` (`terminal_id`);




ALTER TABLE `branch_info` 
ADD COLUMN `facebook_url` VARCHAR(255) NULL AFTER `branch_phone`,
ADD COLUMN `instagram_url` VARCHAR(255) NULL AFTER `facebook_url`,
ADD COLUMN `youtube_url` VARCHAR(255) NULL AFTER `instagram_url`;




-- Add pos_id column to kpra_config if it doesn't exist
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'kpra_config' 
     AND COLUMN_NAME = 'pos_id') = 0,
    'ALTER TABLE `kpra_config` ADD COLUMN `pos_id` varchar(100) DEFAULT NULL AFTER `key`',
    'SELECT "Column pos_id already exists" as message'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Update default API URL to new endpoint
UPDATE `kpra_config` 
SET `api_url` = 'https://kpra.gov.pk/api/rims-integration' 
WHERE `api_url` = 'http://kpra.gov.pk/rims/integration/' 
   OR `api_url` LIKE 'http://%';




-- Add Receipt Print Format Setting
-- This setting allows users to choose between Thermal or A4 format for receipt printing

-- Step 1: Insert the receipt print format setting into settings table
-- Only insert if it doesn't already exist
INSERT INTO `settings` (`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`) 
SELECT NULL, 1, 'Receipt Print Format', 'receipt_print_format', 'select', 'thermal', 'thermal', 'Choose receipt print format: Thermal (80mm) or A4 (Letter Size)', 1
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE code = 'receipt_print_format');

-- Step 2: Get the setting ID for the receipt_print_format setting
SET @receipt_format_setting_id = (SELECT id FROM settings WHERE code = 'receipt_print_format' ORDER BY id DESC LIMIT 1);

-- Step 3: Insert options for receipt print format
-- Only insert if options don't already exist
INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`)
SELECT @receipt_format_setting_id, 'Thermal (80mm)', 'thermal'
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options 
    WHERE setting_id = @receipt_format_setting_id AND option_value = 'thermal'
)
AND @receipt_format_setting_id IS NOT NULL;

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`)
SELECT @receipt_format_setting_id, 'A4 (Letter Size)', 'a4'
WHERE NOT EXISTS (
    SELECT 1 FROM setting_options 
    WHERE setting_id = @receipt_format_setting_id AND option_value = 'a4'
)
AND @receipt_format_setting_id IS NOT NULL;







-- 1) Manual supplier balance ledger
CREATE TABLE IF NOT EXISTS `supplier_balance_entries` (
    `id` int NOT NULL AUTO_INCREMENT,
    `entry_number` varchar(30) NOT NULL,
    `vendor_id` int NOT NULL,
    `store_id` int DEFAULT 1,
    `entry_type` enum('payable','advance') NOT NULL DEFAULT 'payable',
    `amount` decimal(12,2) NOT NULL,
    `remaining_amount` decimal(12,2) NOT NULL,
    `reference_no` varchar(50) DEFAULT NULL,
    `remarks` varchar(255) DEFAULT NULL,
    `map_account_id` int DEFAULT NULL,
    `journal_entry_id` int DEFAULT NULL,
    `status` enum('posted','void') NOT NULL DEFAULT 'posted',
    `created_by` int NOT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_entry_number` (`entry_number`),
    KEY `idx_supplier_balance_vendor` (`vendor_id`),
    KEY `idx_supplier_balance_status` (`status`),
    KEY `idx_supplier_balance_type` (`entry_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 2) Link supplier payments to manual balance entries
ALTER TABLE `sup_payments`
ADD COLUMN `balance_entry_id` int DEFAULT NULL AFTER `purchase_id`;

ALTER TABLE `sup_payments`
ADD KEY `idx_sup_payments_balance_entry_id` (`balance_entry_id`);


