-- Create audit trail table for sales operations
CREATE TABLE IF NOT EXISTS `{branch_code}_sales_audit` (
  `audit_id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `action_type` enum('void','return','delete','edit') NOT NULL,
  `original_total` decimal(10,2) NOT NULL,
  `return_amount` decimal(10,2) DEFAULT 0.00,
  `reason` varchar(500) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sale_data_snapshot` json DEFAULT NULL COMMENT 'Complete sale data before action',
  PRIMARY KEY (`audit_id`),
  KEY `sale_id` (`sale_id`),
  KEY `performed_by` (`performed_by`),
  KEY `action_type` (`action_type`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `{branch_code}_sales_audit_user_fk` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add status column to sales table to track sale state
ALTER TABLE `{branch_code}_sales` 
ADD COLUMN `status` enum('active','voided','returned','deleted') NOT NULL DEFAULT 'active' AFTER `created_at`,
ADD COLUMN `voided_at` datetime DEFAULT NULL AFTER `status`,
ADD COLUMN `voided_by` int(11) DEFAULT NULL AFTER `voided_at`,
ADD COLUMN `void_reason` varchar(500) DEFAULT NULL AFTER `voided_by`,
ADD CONSTRAINT `{branch_code}_sales_voided_by_fk` FOREIGN KEY (`voided_by`) REFERENCES `users` (`id`);

-- Create sales return details table for partial returns
CREATE TABLE IF NOT EXISTS `{branch_code}_sales_returns` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `original_sale_id` int(11) NOT NULL,
  `return_sale_id` int(11) DEFAULT NULL COMMENT 'New sale ID for return transaction',
  `return_type` enum('full','partial') NOT NULL,
  `return_amount` decimal(10,2) NOT NULL,
  `reason` varchar(500) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`return_id`),
  KEY `original_sale_id` (`original_sale_id`),
  KEY `return_sale_id` (`return_sale_id`),
  KEY `performed_by` (`performed_by`),
  CONSTRAINT `{branch_code}_sales_returns_original_fk` FOREIGN KEY (`original_sale_id`) REFERENCES `{branch_code}_sales` (`sale_id`),
  CONSTRAINT `{branch_code}_sales_returns_return_fk` FOREIGN KEY (`return_sale_id`) REFERENCES `{branch_code}_sales` (`sale_id`),
  CONSTRAINT `{branch_code}_sales_returns_user_fk` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create return item details table
CREATE TABLE IF NOT EXISTS `{branch_code}_return_items` (
  `return_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `original_quantity` decimal(10,2) NOT NULL,
  `return_quantity` decimal(10,2) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `return_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`return_item_id`),
  KEY `return_id` (`return_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `{branch_code}_return_items_return_fk` FOREIGN KEY (`return_id`) REFERENCES `{branch_code}_sales_returns` (`return_id`) ON DELETE CASCADE,
  CONSTRAINT `{branch_code}_return_items_item_fk` FOREIGN KEY (`item_id`) REFERENCES `{branch_code}_items` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update cash_movements table to add void_sale movement type if it doesn't exist
ALTER TABLE `{branch_code}_cash_movements` 
MODIFY COLUMN `movement_type` ENUM('opening_fund', 'sale', 'expense', 'credit_settlement', 'cash_count', 'adjustment', 'withdrawal', 'deposit', 'void_sale') NOT NULL; 