-- =========================================================
-- 022_warehouse_foundation.sql
-- Sprint 9 foundation: warehouse groups + stock ledger
-- =========================================================

CREATE TABLE IF NOT EXISTS warehouse_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(40) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wg_location (location_id),
  UNIQUE KEY uq_wg_location_name (location_id, name),
  UNIQUE KEY uq_wg_location_code (location_id, code),
  CONSTRAINT fk_wg_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE products
  ADD COLUMN warehouse_group_id BIGINT UNSIGNED NULL AFTER category_id,
  ADD KEY idx_products_warehouse_group (warehouse_group_id),
  ADD CONSTRAINT fk_products_warehouse_group FOREIGN KEY (warehouse_group_id) REFERENCES warehouse_groups(id)
    ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS stock_levels (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty_on_hand DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  last_move_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_stock_levels_location_product (location_id, product_id),
  KEY idx_stock_levels_product (product_id),
  CONSTRAINT fk_stock_levels_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_stock_levels_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_moves (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  warehouse_group_id BIGINT UNSIGNED DEFAULT NULL,
  direction ENUM('in','out','adjust') NOT NULL,
  qty DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  qty_before DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  qty_after DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  unit_snapshot VARCHAR(100) DEFAULT NULL,
  note VARCHAR(255) DEFAULT NULL,
  source_type VARCHAR(40) NOT NULL DEFAULT 'warehouse_manual',
  source_ref_id BIGINT UNSIGNED DEFAULT NULL,
  source_ref_code VARCHAR(80) DEFAULT NULL,
  operator_type VARCHAR(20) DEFAULT NULL,
  operator_ref_id BIGINT UNSIGNED DEFAULT NULL,
  operator_name_snapshot VARCHAR(120) DEFAULT NULL,
  created_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_stock_moves_location_created (location_id, created_at),
  KEY idx_stock_moves_product (product_id),
  KEY idx_stock_moves_group (warehouse_group_id),
  CONSTRAINT fk_stock_moves_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_stock_moves_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_stock_moves_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 warehouse_group_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  warehouse_group_id BIGINT UNSIGNED NOT NULL,
  token VARCHAR(80) NOT NULL,
  label VARCHAR(120) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_used_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wgt_token (token),
  KEY idx_wgt_group (warehouse_group_id),
  CONSTRAINT fk_wgt_group FOREIGN KEY (warehouse_group_id) REFERENCES warehouse_groups(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;