-- Migration: Retur Barang (Non-Food: Konveksi & Buku)
-- Run this SQL on your database to add return/refund support.

CREATE TABLE IF NOT EXISTS `returns` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `return_code` VARCHAR(50) NOT NULL UNIQUE,
    `transaction_id` INT UNSIGNED NOT NULL,
    `store_id` INT UNSIGNED NOT NULL,
    `return_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `reason` TEXT DEFAULT NULL,
    `total_refund` DECIMAL(15,2) NOT NULL DEFAULT 0,
    `processed_by` INT UNSIGNED NOT NULL COMMENT 'User yang memproses retur',
    `approved_by` INT UNSIGNED DEFAULT NULL COMMENT 'User yang menyetujui (jika perlu approval)',
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved',
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_returns_transaction` (`transaction_id`),
    INDEX `idx_returns_store` (`store_id`),
    INDEX `idx_returns_processed_by` (`processed_by`),
    INDEX `idx_returns_date` (`return_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `return_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `return_id` INT UNSIGNED NOT NULL,
    `transaction_item_id` INT UNSIGNED DEFAULT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `product_unit_id` INT UNSIGNED NOT NULL,
    `product_name` VARCHAR(255) NOT NULL,
    `unit_name` VARCHAR(50) NOT NULL,
    `quantity` DECIMAL(10,2) NOT NULL,
    `unit_price` DECIMAL(15,2) NOT NULL,
    `subtotal` DECIMAL(15,2) NOT NULL,
    `reason` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_return_items_return` (`return_id`),
    INDEX `idx_return_items_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;