-- Stock transfers between branches (audit + ledger movements)

CREATE TABLE IF NOT EXISTS stock_transfers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  from_branch_id BIGINT UNSIGNED NOT NULL,
  to_branch_id BIGINT UNSIGNED NOT NULL,
  note VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_transfer_from (from_branch_id, created_at),
  KEY idx_transfer_to (to_branch_id, created_at),
  CONSTRAINT fk_stock_transfers_from_branch FOREIGN KEY (from_branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_stock_transfers_to_branch FOREIGN KEY (to_branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_stock_transfers_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_transfer_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  stock_transfer_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty DECIMAL(14,3) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_transfer_items_transfer (stock_transfer_id),
  KEY idx_transfer_items_product (product_id),
  CONSTRAINT fk_transfer_items_transfer FOREIGN KEY (stock_transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE,
  CONSTRAINT fk_transfer_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;