-- V3.2 - Önce veritabanı yedeğini alın; InnoDB / MySQL 8 önerilir.
CREATE TABLE IF NOT EXISTS stock_balances (
 product_id BIGINT UNSIGNED NOT NULL,
 location_name VARCHAR(120) NOT NULL,
 quantity DECIMAL(16,3) NOT NULL DEFAULT 0,
 PRIMARY KEY (product_id,location_name),
 CONSTRAINT chk_stock_balance CHECK (quantity >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS stock_ledger (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 source_location VARCHAR(120) NULL,
 target_location VARCHAR(120) NULL,
 quantity DECIMAL(16,3) NOT NULL,
 movement_type ENUM('receipt','transfer') NOT NULL,
 receipt_item_id BIGINT UNSIGNED NULL,
 note VARCHAR(500) NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_ledger_receipt (receipt_item_id,target_location),
 INDEX idx_ledger_product (product_id,created_at),
 CONSTRAINT chk_ledger_qty CHECK (quantity > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Tarihsel irsaliyeleri stok defterine yalnızca bir defa alır.
INSERT IGNORE INTO stock_ledger(product_id,source_location,target_location,quantity,movement_type,receipt_item_id,note,created_by,created_at)
SELECT gi.product_id,NULL,'Mal Kabul',gi.received_qty-gi.damaged_qty,'receipt',gi.id,'V3.2 tarihsel mal kabul aktarımı',gr.created_by,gr.created_at
FROM goods_receipt_items gi JOIN goods_receipts gr ON gr.id=gi.receipt_id
WHERE gi.product_id IS NOT NULL AND gi.received_qty > gi.damaged_qty;
INSERT IGNORE INTO stock_ledger(product_id,source_location,target_location,quantity,movement_type,receipt_item_id,note,created_by,created_at)
SELECT gi.product_id,NULL,'İade Bekleme',gi.damaged_qty,'receipt',gi.id,'V3.2 tarihsel hasarlı ürün aktarımı',gr.created_by,gr.created_at
FROM goods_receipt_items gi JOIN goods_receipts gr ON gr.id=gi.receipt_id
WHERE gi.product_id IS NOT NULL AND gi.damaged_qty > 0;
-- Bu aktarım ilk kurulumda, yeni transferlerden ÖNCE yapılmalıdır.
INSERT INTO stock_balances(product_id,location_name,quantity)
SELECT product_id,target_location,SUM(quantity) FROM stock_ledger WHERE movement_type='receipt'
GROUP BY product_id,target_location
ON DUPLICATE KEY UPDATE quantity=stock_balances.quantity;