-- Phase 09 - Sales Returns (Phase 1)
-- Creates sales return tables, permissions, and admin assignments.

CREATE TABLE IF NOT EXISTS `sales_returns` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `return_no` VARCHAR(30) NOT NULL,
  `return_date` DATETIME NOT NULL,
  `sales_invoice_id` BIGINT UNSIGNED NOT NULL,
  `customer_id` BIGINT UNSIGNED NULL,
  `party_id` BIGINT UNSIGNED NOT NULL,
  `warehouse_id` BIGINT UNSIGNED NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
  `settlement_type` VARCHAR(20) NOT NULL DEFAULT 'credit_note',
  `cashbox_id` BIGINT UNSIGNED NULL,
  `subtotal` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `total_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `reason` VARCHAR(255) NULL,
  `notes` TEXT NULL,
  `posted_by` BIGINT UNSIGNED NULL,
  `posted_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sales_returns_no` (`return_no`),
  KEY `idx_sales_returns_invoice` (`sales_invoice_id`),
  KEY `idx_sales_returns_party` (`party_id`),
  KEY `idx_sales_returns_wh` (`warehouse_id`),
  KEY `idx_sales_returns_status` (`status`),
  KEY `idx_sales_returns_cashbox` (`cashbox_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `sales_return_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sales_return_id` BIGINT UNSIGNED NOT NULL,
  `sales_invoice_item_id` BIGINT UNSIGNED NOT NULL,
  `line_no` INT NOT NULL DEFAULT 1,
  `item_id` BIGINT UNSIGNED NOT NULL,
  `qty` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `unit_price` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `line_total` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sri_return` (`sales_return_id`),
  KEY `idx_sri_item` (`item_id`),
  KEY `idx_sri_source_line` (`sales_invoice_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO `permissions` (`code`, `name`, `module`) VALUES
('sales_returns.view', 'عرض مرتجعات البيع', 'sales'),
('sales_returns.create', 'إنشاء مرتجع بيع', 'sales'),
('sales_returns.post', 'ترحيل مرتجع بيع', 'sales'),
('sales_returns.cancel', 'إلغاء مرتجع بيع', 'sales');

INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, p.id
FROM permissions p
WHERE p.code IN ('sales_returns.view','sales_returns.create','sales_returns.post','sales_returns.cancel');