-- Opening stock log (shown in product stock history)
CREATE TABLE IF NOT EXISTS product_opening_stock (
  product_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NOT NULL,
  variation_id INT UNSIGNED NOT NULL DEFAULT 0,
  quantity DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id, branch_id, variation_id),
  KEY idx_pos_branch (branch_id),
  CONSTRAINT fk_pos_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_pos_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;