
CREATE TABLE IF NOT EXISTS `purchase_returns` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `return_no` varchar(20) NOT NULL COMMENT 'PRT-YYYYMMDD-XXXX',
  `total_refund` bigint(20) unsigned NOT NULL,
  `reason` text DEFAULT NULL,
  `voucher_id` int(10) unsigned DEFAULT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_return_no` (`return_no`),
  KEY `fk_purchase_returns_purchase` (`purchase_id`),
  KEY `fk_pret_created_by` (`created_by`),
  CONSTRAINT `fk_pret_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `fk_purchase_returns_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
