-- =========================================================
-- 023_stock_opname.sql
-- Sprint 9: stock opname header + items
-- =========================================================

CREATE TABLE IF NOT EXISTS stock_opnames (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  warehouse_group_id BIGINT UNSIGNED DEFAULT NULL,
  title VARCHAR(160) NOT NULL,
  status ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'posted',
  note VARCHAR(255) DEFAULT NULL,
  counted_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  counted_by_name_snapshot VARCHAR(120) DEFAULT NULL,
  posted_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  posted_at DATETIME DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_stock_opnames_location_posted (location_id, posted_at),
  KEY idx_stock_opnames_group (warehouse_group_id),
  CONSTRAINT fk_stock_opnames_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_stock_opnames_group FOREIGN KEY (warehouse_group_id) REFERENCES warehouse_groups(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_opname_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  opname_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty_system DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  qty_physical DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  qty_diff DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  adjustment_move_id BIGINT UNSIGNED DEFAULT NULL,
  note VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_stock_opname_items_opname (opname_id),
  KEY idx_stock_opname_items_product (product_id),
  CONSTRAINT fk_stock_opname_items_opname FOREIGN KEY (opname_id) REFERENCES stock_opnames(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_stock_opname_items_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;