-- Migration: POS System Enhancements
-- Description: Adds support for POS features including product sellability, fulfillment types, split payments, and settings

-- =====================================================
-- 1. Add sell_on_pos column to products table
-- =====================================================
ALTER TABLE `products`
ADD COLUMN `sell_on_pos` BOOLEAN DEFAULT TRUE COMMENT 'Whether product can be sold on POS. Default TRUE for FG, should be FALSE for RM.';

-- Set default based on product_type (RM products should not be sellable by default)
UPDATE `products`
SET `sell_on_pos` = CASE 
  WHEN `product_type` = 'RM' THEN FALSE 
  ELSE TRUE 
END
WHERE `sell_on_pos` IS NULL;

-- Add index for filtering
CREATE INDEX `idx_products_sell_on_pos` ON `products` (`sell_on_pos`);

-- =====================================================
-- 2. Add fulfillment_type to sales table
-- =====================================================
ALTER TABLE `sales`
ADD COLUMN `fulfillment_type` ENUM('POS_SALE', 'ORDER', 'PREORDER', 'LAYAWAY', 'SPLIT_FULFILLMENT') 
  DEFAULT 'POS_SALE' 
  COMMENT 'Type of fulfillment: POS_SALE (immediate), ORDER (backorder), PREORDER (future), LAYAWAY (installment), SPLIT_FULFILLMENT (mixed)';

-- Add index for filtering
CREATE INDEX `idx_sales_fulfillment_type` ON `sales` (`fulfillment_type`);

-- =====================================================
-- 3. Add quantity tracking columns to sale_items
-- =====================================================
ALTER TABLE `sale_items`
ADD COLUMN `quantity_fulfilled` DECIMAL(12, 3) DEFAULT 0 COMMENT 'Quantity sold immediately (stock deducted)',
ADD COLUMN `quantity_preordered` DECIMAL(12, 3) DEFAULT 0 COMMENT 'Quantity preordered (stock not deducted yet)';

-- Initialize existing records
UPDATE `sale_items`
SET `quantity_fulfilled` = `quantity`,
    `quantity_preordered` = 0
WHERE `quantity_fulfilled` IS NULL;

-- =====================================================
-- 4. Add payment tender columns to payments table
-- =====================================================
ALTER TABLE `payments`
ADD COLUMN `tender_number` INT DEFAULT 1 COMMENT 'Tender sequence number (1 or 2 for split payments)',
ADD COLUMN `mpesa_phone_number` VARCHAR(20) DEFAULT NULL COMMENT 'M-Pesa phone number for STK Push',
ADD COLUMN `mpesa_transaction_code` VARCHAR(50) DEFAULT NULL COMMENT 'M-Pesa transaction code (from callback)',
ADD COLUMN `card_reference` VARCHAR(100) DEFAULT NULL COMMENT 'Card transaction reference (manual entry)',
ADD COLUMN `payment_status` ENUM('PENDING', 'CONFIRMED', 'FAILED', 'RETRYING') DEFAULT 'PENDING' COMMENT 'Payment processing status';

-- Add indexes
CREATE INDEX `idx_payments_tender_number` ON `payments` (`sale_id`, `tender_number`);
CREATE INDEX `idx_payments_status` ON `payments` (`payment_status`);
CREATE INDEX `idx_payments_mpesa_code` ON `payments` (`mpesa_transaction_code`);

-- =====================================================
-- 5. Create settings table for configurable policies
-- =====================================================
CREATE TABLE IF NOT EXISTS `settings` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Setting key (e.g., preorder_deposit_percentage)',
  `value` TEXT NOT NULL COMMENT 'Setting value (stored as text, parsed as needed)',
  `description` TEXT COMMENT 'Human-readable description of the setting',
  `data_type` ENUM('STRING', 'NUMBER', 'BOOLEAN', 'JSON') DEFAULT 'STRING' COMMENT 'Data type for value parsing',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_settings_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT INTO `settings` (`key`, `value`, `description`, `data_type`) VALUES
('preorder_deposit_percentage', '50', 'Minimum deposit percentage required for preorders (0-100)', 'NUMBER'),
('mpesa_stk_timeout_seconds', '60', 'STK Push timeout in seconds before retry or fallback', 'NUMBER'),
('mpesa_stk_retry_enabled', 'true', 'Enable automatic retry for failed STK Push requests', 'BOOLEAN'),
('rm_sales_require_manager', 'true', 'Require manager approval for RM product sales', 'BOOLEAN'),
('allow_negative_stock', 'false', 'Allow sales even when inventory is negative (manager override only)', 'BOOLEAN')
ON DUPLICATE KEY UPDATE 
  `value` = VALUES(`value`),
  `updated_at` = CURRENT_TIMESTAMP;

-- =====================================================
-- 6. Create payment_status_history table for audit trail (optional but recommended)
-- =====================================================
CREATE TABLE IF NOT EXISTS `payment_status_history` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `payment_id` BIGINT NOT NULL,
  `old_status` ENUM('PENDING', 'CONFIRMED', 'FAILED', 'RETRYING') DEFAULT NULL,
  `new_status` ENUM('PENDING', 'CONFIRMED', 'FAILED', 'RETRYING') NOT NULL,
  `changed_by` BIGINT COMMENT 'User ID who made the change (for manual confirmations)',
  `notes` TEXT COMMENT 'Additional notes or reason for status change',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  INDEX `idx_payment_status_history_payment_id` (`payment_id`),
  INDEX `idx_payment_status_history_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

