-- Migration: Add refund tracking fields to returns table
-- Description: Adds fields to track refund method, amount, date, and replacement products
-- Date: 2026-01-19

-- Add refund tracking fields
ALTER TABLE `returns`
ADD COLUMN `refund_method` ENUM('CASH', 'CARD', 'MPESA', 'CREDIT_NOTE', 'REPLACEMENT') NULL AFTER `total_amount`,
ADD COLUMN `refund_amount` DECIMAL(12, 2) NULL AFTER `refund_method`,
ADD COLUMN `refunded_at` DATETIME NULL AFTER `refund_amount`,
ADD COLUMN `replacement_product_id` BIGINT NULL AFTER `refunded_at`,
ADD COLUMN `refund_reference` VARCHAR(150) NULL AFTER `replacement_product_id`;

-- Add foreign key for replacement product
ALTER TABLE `returns`
ADD CONSTRAINT `fk_returns_replacement_product`
FOREIGN KEY (`replacement_product_id`) REFERENCES `products`(`id`)
ON UPDATE CASCADE;

-- Add index on refund_method for filtering
CREATE INDEX `idx_returns_refund_method` ON `returns`(`refund_method`);

-- Add index on refunded_at for date filtering
CREATE INDEX `idx_returns_refunded_at` ON `returns`(`refunded_at`);
