-- Migration: Create sale_returns and sale_return_items tables
-- Tracks individual return events with full audit trail

CREATE TABLE IF NOT EXISTS sale_returns (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  sale_id BIGINT NOT NULL,
  return_number VARCHAR(255),
  return_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  return_type ENUM('full', 'partial') NOT NULL,
  reason TEXT,
  refund_amount DECIMAL(11, 2) DEFAULT 0.00,
  refund_method VARCHAR(255) DEFAULT 'Cash',
  total_qty_returned INT DEFAULT 0,
  processed_by BIGINT,
  shop_id BIGINT,
  notes TEXT,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (sale_id) REFERENCES product_orders(id),
  FOREIGN KEY (processed_by) REFERENCES users(id),
  FOREIGN KEY (shop_id) REFERENCES shops(id)
);

CREATE TABLE IF NOT EXISTS sale_return_items (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  return_id BIGINT NOT NULL,
  sale_item_id BIGINT,
  product_id BIGINT NOT NULL,
  product_name VARCHAR(255),
  quantity_returned INT NOT NULL,
  unit_price DECIMAL(11, 2) DEFAULT 0.00,
  total DECIMAL(11, 2) DEFAULT 0.00,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (return_id) REFERENCES sale_returns(id),
  FOREIGN KEY (sale_item_id) REFERENCES order_items(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);