-- ZT Mobile - WhatsApp Billing Upgrade (Idempotent)
-- Run in phpMyAdmin on your ZT Mobile database

SET @db = DATABASE();

-- 1) parties: whatsapp_phone + whatsapp_opt_in
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='parties' AND COLUMN_NAME='whatsapp_phone');
SET @sql := IF(@col=0,'ALTER TABLE parties ADD COLUMN whatsapp_phone VARCHAR(30) NULL AFTER phone','SELECT "parties.whatsapp_phone exists"');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='parties' AND COLUMN_NAME='whatsapp_opt_in');
SET @sql := IF(@col=0,'ALTER TABLE parties ADD COLUMN whatsapp_opt_in TINYINT(1) NOT NULL DEFAULT 0 AFTER whatsapp_phone','SELECT "parties.whatsapp_opt_in exists"');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) sales: share_token (for public receipt link)
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales' AND COLUMN_NAME='share_token');
SET @sql := IF(@col=0,'ALTER TABLE sales ADD COLUMN share_token VARCHAR(64) NULL AFTER due_amount','SELECT "sales.share_token exists"');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) used_purchases: share_token (for public slip link)
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='used_purchases' AND COLUMN_NAME='share_token');
SET @sql := IF(@col=0,'ALTER TABLE used_purchases ADD COLUMN share_token VARCHAR(64) NULL AFTER due_amount','SELECT "used_purchases.share_token exists"');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 4) whatsapp_log: send attempts log
CREATE TABLE IF NOT EXISTS whatsapp_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  txn_type VARCHAR(30) NOT NULL,
  txn_id BIGINT UNSIGNED NULL,
  to_number VARCHAR(30) NOT NULL,
  message_type VARCHAR(20) NOT NULL DEFAULT 'text',
  payload_json MEDIUMTEXT NOT NULL,
  response_json MEDIUMTEXT NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  error_msg VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  KEY idx_txn (txn_type, txn_id),
  KEY idx_to (to_number),
  KEY idx_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

