-- =========================================================
-- 020_pos_payments_upgrade.sql
-- Sprint 4 (POS Order Lanjutan) - Upgrade
--
-- - orders: payment_received_amount, payment_updated_at, notes
-- - order_payments: void/rollback audit fields
-- - pos_settings: VA BCA placeholder
--
-- Jalankan SETELAH 019_pos_payments_and_settings.sql
-- =========================================================

START TRANSACTION;

-- ---------------------------------------------------------
-- NOTE: idempotent upgrade
-- Karena sering dijalankan ulang (dev/staging), migrasi ini
-- dibuat aman: hanya ADD COLUMN / ADD INDEX kalau belum ada.
-- ---------------------------------------------------------

SET @db := DATABASE();

-- ---------------------------------------------------------
-- 1) Orders: extra fields
-- ---------------------------------------------------------

-- payment_received_amount
SET @c1 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='payment_received_amount'
);
SET @sql1 := IF(@c1=0,
  'ALTER TABLE orders ADD COLUMN payment_received_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00',
  'SELECT 1'
);
PREPARE stmt FROM @sql1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- payment_updated_at
SET @c2 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='payment_updated_at'
);
SET @sql2 := IF(@c2=0,
  'ALTER TABLE orders ADD COLUMN payment_updated_at DATETIME NULL',
  'SELECT 1'
);
PREPARE stmt FROM @sql2; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- note_public
SET @c3 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='note_public'
);
SET @sql3 := IF(@c3=0,
  'ALTER TABLE orders ADD COLUMN note_public TEXT NULL',
  'SELECT 1'
);
PREPARE stmt FROM @sql3; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- note_staff
SET @c4 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='note_staff'
);
SET @sql4 := IF(@c4=0,
  'ALTER TABLE orders ADD COLUMN note_staff TEXT NULL',
  'SELECT 1'
);
PREPARE stmt FROM @sql4; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------
-- 2) Order payments: void / audit
-- ---------------------------------------------------------

-- is_void
SET @p1 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='order_payments' AND COLUMN_NAME='is_void'
);
SET @psql1 := IF(@p1=0,
  'ALTER TABLE order_payments ADD COLUMN is_void TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT 1'
);
PREPARE stmt FROM @psql1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- void_reason
SET @p2 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='order_payments' AND COLUMN_NAME='void_reason'
);
SET @psql2 := IF(@p2=0,
  'ALTER TABLE order_payments ADD COLUMN void_reason VARCHAR(255) NULL',
  'SELECT 1'
);
PREPARE stmt FROM @psql2; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- voided_by
SET @p3 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='order_payments' AND COLUMN_NAME='voided_by'
);
SET @psql3 := IF(@p3=0,
  'ALTER TABLE order_payments ADD COLUMN voided_by BIGINT UNSIGNED NULL',
  'SELECT 1'
);
PREPARE stmt FROM @psql3; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- voided_at
SET @p4 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='order_payments' AND COLUMN_NAME='voided_at'
);
SET @psql4 := IF(@p4=0,
  'ALTER TABLE order_payments ADD COLUMN voided_at DATETIME NULL',
  'SELECT 1'
);
PREPARE stmt FROM @psql4; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- index idx_op_is_void
SET @pi := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='order_payments' AND INDEX_NAME='idx_op_is_void'
);
SET @psqli := IF(@pi=0,
  'ALTER TABLE order_payments ADD KEY idx_op_is_void (is_void)',
  'SELECT 1'
);
PREPARE stmt FROM @psqli; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------
-- 3) POS Settings: VA BCA placeholder
-- ---------------------------------------------------------

SET @s1 := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='pos_settings' AND COLUMN_NAME='va_bca_number'
);
SET @ssql1 := IF(@s1=0,
  'ALTER TABLE pos_settings ADD COLUMN va_bca_number VARCHAR(80) NULL',
  'SELECT 1'
);
PREPARE stmt FROM @ssql1; EXECUTE stmt; DEALLOCATE PREPARE stmt;


COMMIT;