-- =========================================================
-- 006_master_products_location.sql
-- Master Produk (scope: location_id)
-- =========================================================

CREATE TABLE IF NOT EXISTS units (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,

  name VARCHAR(100) NOT NULL,
  short_name VARCHAR(30) DEFAULT NULL,
  allow_decimal TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),
  KEY idx_units_location (location_id),
  UNIQUE KEY uq_units_location_name (location_id, name),
  CONSTRAINT fk_units_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,

  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),
  KEY idx_categories_location (location_id),
  UNIQUE KEY uq_categories_location_name (location_id, name),
  CONSTRAINT fk_categories_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,

  name VARCHAR(180) NOT NULL,
  sku VARCHAR(80) DEFAULT NULL,

  unit_id BIGINT UNSIGNED DEFAULT NULL,
  category_id BIGINT UNSIGNED DEFAULT NULL,

  product_type ENUM('service','goods') NOT NULL DEFAULT 'service',
  manage_stock TINYINT(1) NOT NULL DEFAULT 0,
  allow_price_edit TINYINT(1) NOT NULL DEFAULT 0,

  base_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  agent_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,

  thumbnail VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),
  KEY idx_products_location (location_id),
  KEY idx_products_unit (unit_id),
  KEY idx_products_category (category_id),
  UNIQUE KEY uq_products_location_sku (location_id, sku),

  CONSTRAINT fk_products_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_products_unit FOREIGN KEY (unit_id) REFERENCES units(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_flags (
  product_id BIGINT UNSIGNED NOT NULL,

  show_in_pos TINYINT(1) NOT NULL DEFAULT 1,
  warehouse_only TINYINT(1) NOT NULL DEFAULT 0,
  need_spk TINYINT(1) NOT NULL DEFAULT 0,
  can_loan TINYINT(1) NOT NULL DEFAULT 0,
  vendor_enabled TINYINT(1) NOT NULL DEFAULT 0,

  channel_marketplace TINYINT(1) NOT NULL DEFAULT 0,
  channel_landing TINYINT(1) NOT NULL DEFAULT 0,

  PRIMARY KEY (product_id),
  CONSTRAINT fk_pf_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_variants (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_pv_product (product_id),
  CONSTRAINT fk_pv_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_price_tiers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id BIGINT UNSIGNED NOT NULL,
  min_qty INT NOT NULL,
  max_qty INT DEFAULT NULL,
  price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_pt_product (product_id),
  CONSTRAINT fk_pt_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;