-- =========================================================
-- 106_purchase_orders_supplier_customer.sql
-- Nullable supplier relation for purchase orders
-- =========================================================

SET @purchase_orders_has_supplier_customer_id := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND column_name = 'supplier_customer_id'
);
SET @purchase_orders_add_supplier_customer_id_sql := IF(
  @purchase_orders_has_supplier_customer_id = 0,
  'ALTER TABLE purchase_orders ADD COLUMN supplier_customer_id BIGINT UNSIGNED DEFAULT NULL AFTER order_date',
  'SELECT 1'
);
PREPARE purchase_orders_add_supplier_customer_id_stmt FROM @purchase_orders_add_supplier_customer_id_sql;
EXECUTE purchase_orders_add_supplier_customer_id_stmt;
DEALLOCATE PREPARE purchase_orders_add_supplier_customer_id_stmt;

SET @purchase_orders_supplier_customer_idx_exists := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND index_name = 'idx_purchase_orders_supplier_customer'
);
SET @purchase_orders_add_supplier_customer_idx_sql := IF(
  @purchase_orders_supplier_customer_idx_exists = 0,
  'ALTER TABLE purchase_orders ADD KEY idx_purchase_orders_supplier_customer (supplier_customer_id)',
  'SELECT 1'
);
PREPARE purchase_orders_add_supplier_customer_idx_stmt FROM @purchase_orders_add_supplier_customer_idx_sql;
EXECUTE purchase_orders_add_supplier_customer_idx_stmt;
DEALLOCATE PREPARE purchase_orders_add_supplier_customer_idx_stmt;