-- =========================================================
-- 103_order_customer_facing_publication.sql
-- Phase 2
-- - Customer-facing publish moment untuk quote/final
-- - Snapshot payload untuk landing / quotation / invoice
-- - Draft tidak lagi otomatis punya token publik
-- =========================================================

START TRANSACTION;

SET @db := DATABASE();

SET @col_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'orders'
    AND COLUMN_NAME = 'customer_facing_published_at'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE orders ADD COLUMN customer_facing_published_at DATETIME NULL AFTER locked_at',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'orders'
    AND COLUMN_NAME = 'customer_facing_source'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE orders ADD COLUMN customer_facing_source VARCHAR(20) NOT NULL DEFAULT '' AFTER customer_facing_published_at",
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'order_tokens'
    AND COLUMN_NAME = 'published_status'
);
SET @sql := IF(
  @col_exists = 0,
  "ALTER TABLE order_tokens ADD COLUMN published_status VARCHAR(20) NOT NULL DEFAULT '' AFTER token",
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'order_tokens'
    AND COLUMN_NAME = 'published_at'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE order_tokens ADD COLUMN published_at DATETIME NULL AFTER published_status',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'order_tokens'
    AND COLUMN_NAME = 'snapshot_updated_at'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE order_tokens ADD COLUMN snapshot_updated_at DATETIME NULL AFTER published_at',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 'order_tokens'
    AND COLUMN_NAME = 'snapshot_json'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE order_tokens ADD COLUMN snapshot_json LONGTEXT NULL AFTER snapshot_updated_at',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

COMMIT;