-- V3.5/V3.6; execute once after backup on MySQL 8 InnoDB.
CREATE TABLE IF NOT EXISTS supplier_returns (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 supplier_id BIGINT UNSIGNED NOT NULL,
 order_no VARCHAR(70) NULL,
 document_no VARCHAR(100) NOT NULL,
 return_date DATE NOT NULL,
 reason VARCHAR(500) NOT NULL,
 status ENUM('recorded','cancelled') NOT NULL DEFAULT 'recorded',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_supplier_return_document(supplier_id,document_no),
 INDEX idx_supplier_return_date(return_date), INDEX idx_supplier_return_order(order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS supplier_return_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 return_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 source_location VARCHAR(120) NOT NULL,
 quantity DECIMAL(16,3) NOT NULL,
 CONSTRAINT fk_return_header FOREIGN KEY(return_id) REFERENCES supplier_returns(id),
 CONSTRAINT chk_return_positive CHECK(quantity>0),
 INDEX idx_return_product(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS purchase_line_cancellations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_item_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(16,3) NOT NULL,
 reason VARCHAR(500) NOT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_cancel_item(order_item_id),
 CONSTRAINT chk_cancel_positive CHECK(quantity>0),
 CONSTRAINT fk_cancel_po_item FOREIGN KEY(order_item_id) REFERENCES purchase_order_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Extend stock ledger movement types without dropping history.
ALTER TABLE stock_ledger MODIFY movement_type ENUM('receipt','transfer','supplier_return') NOT NULL;
CREATE TABLE IF NOT EXISTS user_notification_reads (
 user_id BIGINT UNSIGNED NOT NULL,
 alert_key VARCHAR(190) NOT NULL,
 read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(user_id,alert_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;