-- HOTFIX: hanya membuat tabel products (SPRINT 3 - Master Produk)
-- Jalankan ini jika tabel lain sudah ada tapi products belum terbentuk.

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(191) NOT NULL,
  sku VARCHAR(64) NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  product_type ENUM('goods','service') NOT NULL DEFAULT 'goods',
  manage_stock TINYINT(1) NOT NULL DEFAULT 0,
  stock_alert_level DECIMAL(18,4) NULL,
  purchase_price DECIMAL(18,2) NULL,
  price_public DECIMAL(18,2) NOT NULL DEFAULT 0,
  price_agent DECIMAL(18,2) NOT NULL DEFAULT 0,
  image_path VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_tenant_sku (tenant_id, sku),
  KEY idx_tenant (tenant_id),
  KEY idx_cat (tenant_id, category_id),
  KEY idx_unit (tenant_id, unit_id),
  KEY idx_active (tenant_id, is_active),
  KEY idx_name (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
