-- V3.1: phpMyAdmin üzerinde yalnızca bir defa çalıştırın.
CREATE TABLE IF NOT EXISTS goods_receipts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_no VARCHAR(70) NOT NULL,
 supplier_id BIGINT UNSIGNED NOT NULL,
 waybill_no VARCHAR(100) NOT NULL,
 waybill_date DATE NOT NULL,
 received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 note TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_gr_supplier_waybill (supplier_id,waybill_no),
 INDEX ix_gr_order (order_no),
 CONSTRAINT fk_gr_order FOREIGN KEY (order_no) REFERENCES purchase_orders(order_no) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS goods_receipt_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 receipt_id BIGINT UNSIGNED NOT NULL,
 order_item_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NULL,
 received_qty DECIMAL(16,3) NOT NULL,
 damaged_qty DECIMAL(16,3) NOT NULL DEFAULT 0,
 note VARCHAR(500) NULL,
 UNIQUE KEY uq_gr_line (receipt_id,order_item_id),
 INDEX ix_gri_order_item (order_item_id),
 CONSTRAINT fk_gri_receipt FOREIGN KEY (receipt_id) REFERENCES goods_receipts(id) ON DELETE RESTRICT,
 CONSTRAINT fk_gri_order_item FOREIGN KEY (order_item_id) REFERENCES purchase_order_items(id) ON DELETE RESTRICT,
 CONSTRAINT chk_gr_received CHECK (received_qty > 0),
 CONSTRAINT chk_gr_damaged CHECK (damaged_qty >= 0 AND damaged_qty <= received_qty)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;