-- =========================================================
-- 105_purchase_orders_public_landing_cancel.sql
-- Purchase Order public landing hardening + cancel support
-- =========================================================

ALTER TABLE purchase_orders
  MODIFY status ENUM('draft','finalized','ordered','partial_receiving','sent','completed','cancelled') NOT NULL DEFAULT 'draft';

SET @purchase_orders_has_landing_token := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND column_name = 'landing_token'
);
SET @purchase_orders_add_landing_token_sql := IF(
  @purchase_orders_has_landing_token = 0,
  'ALTER TABLE purchase_orders ADD COLUMN landing_token VARCHAR(80) DEFAULT NULL AFTER code',
  'SELECT 1'
);
PREPARE purchase_orders_add_landing_token_stmt FROM @purchase_orders_add_landing_token_sql;
EXECUTE purchase_orders_add_landing_token_stmt;
DEALLOCATE PREPARE purchase_orders_add_landing_token_stmt;

SET @purchase_orders_has_canceled_by_user_id := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND column_name = 'canceled_by_user_id'
);
SET @purchase_orders_add_canceled_by_user_id_sql := IF(
  @purchase_orders_has_canceled_by_user_id = 0,
  'ALTER TABLE purchase_orders ADD COLUMN canceled_by_user_id BIGINT UNSIGNED DEFAULT NULL AFTER ordered_at',
  'SELECT 1'
);
PREPARE purchase_orders_add_canceled_by_user_id_stmt FROM @purchase_orders_add_canceled_by_user_id_sql;
EXECUTE purchase_orders_add_canceled_by_user_id_stmt;
DEALLOCATE PREPARE purchase_orders_add_canceled_by_user_id_stmt;

SET @purchase_orders_has_canceled_by_name_snapshot := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND column_name = 'canceled_by_name_snapshot'
);
SET @purchase_orders_add_canceled_by_name_snapshot_sql := IF(
  @purchase_orders_has_canceled_by_name_snapshot = 0,
  'ALTER TABLE purchase_orders ADD COLUMN canceled_by_name_snapshot VARCHAR(120) DEFAULT NULL AFTER canceled_by_user_id',
  'SELECT 1'
);
PREPARE purchase_orders_add_canceled_by_name_snapshot_stmt FROM @purchase_orders_add_canceled_by_name_snapshot_sql;
EXECUTE purchase_orders_add_canceled_by_name_snapshot_stmt;
DEALLOCATE PREPARE purchase_orders_add_canceled_by_name_snapshot_stmt;

SET @purchase_orders_has_canceled_at := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND column_name = 'canceled_at'
);
SET @purchase_orders_add_canceled_at_sql := IF(
  @purchase_orders_has_canceled_at = 0,
  'ALTER TABLE purchase_orders ADD COLUMN canceled_at DATETIME DEFAULT NULL AFTER canceled_by_name_snapshot',
  'SELECT 1'
);
PREPARE purchase_orders_add_canceled_at_stmt FROM @purchase_orders_add_canceled_at_sql;
EXECUTE purchase_orders_add_canceled_at_stmt;
DEALLOCATE PREPARE purchase_orders_add_canceled_at_stmt;

SET @purchase_orders_has_cancel_note := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND column_name = 'cancel_note'
);
SET @purchase_orders_add_cancel_note_sql := IF(
  @purchase_orders_has_cancel_note = 0,
  'ALTER TABLE purchase_orders ADD COLUMN cancel_note TEXT DEFAULT NULL AFTER canceled_at',
  'SELECT 1'
);
PREPARE purchase_orders_add_cancel_note_stmt FROM @purchase_orders_add_cancel_note_sql;
EXECUTE purchase_orders_add_cancel_note_stmt;
DEALLOCATE PREPARE purchase_orders_add_cancel_note_stmt;

SET @purchase_orders_landing_token_index_exists := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'purchase_orders'
    AND index_name = 'idx_purchase_orders_landing_token'
);
SET @purchase_orders_landing_token_index_sql := IF(
  @purchase_orders_landing_token_index_exists = 0,
  'ALTER TABLE purchase_orders ADD KEY idx_purchase_orders_landing_token (landing_token)',
  'SELECT 1'
);
PREPARE purchase_orders_landing_token_index_stmt FROM @purchase_orders_landing_token_index_sql;
EXECUTE purchase_orders_landing_token_index_stmt;
DEALLOCATE PREPARE purchase_orders_landing_token_index_stmt;

UPDATE purchase_orders
SET landing_token = LOWER(HEX(RANDOM_BYTES(16)))
WHERE COALESCE(landing_token, '') = '';

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
('purchase_orders.cancel', 'Batalkan Purchase Orders', 'purchase_orders', NOW());

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN (
  'purchase_orders.cancel'
)
WHERE r.code IN ('owner', 'admin', 'kepala_toko');