-- Branch Stocks Table
CREATE TABLE `branch_stocks` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED NOT NULL,
    `stock_quantity` INT NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_stock` (`product_id`, `branch_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Migrate existing stock to Head Office (Branch ID 1)
INSERT INTO
    `branch_stocks` (
        `product_id`,
        `branch_id`,
        `stock_quantity`
    )
SELECT `id`, 1, `stock_current`
FROM `products`
WHERE
    `product_type` = 'Barang';