-- Run ONCE on the live database, AFTER 2026_admin_v2_block_column.sql.
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER is_blocked;
ALTER TABLE orders ADD COLUMN admin_note VARCHAR(300) NULL AFTER channel_notified;

-- ---- v3: phone, admin order notes, support tickets, captcha question bank ----
CREATE TABLE IF NOT EXISTS tickets (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  status     ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tk_user (user_id, status),
  KEY idx_tk_upd (updated_at),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ticket_messages (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id  BIGINT UNSIGNED NOT NULL,
  sender     ENUM('user','admin') NOT NULL,
  body       TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tm (ticket_id, id),
  FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Captcha question bank. Wrong answers are separated by |. Add rows any time; no code change needed.
CREATE TABLE IF NOT EXISTS captcha_questions (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  active  TINYINT(1) NOT NULL DEFAULT 1,
  q_fa    VARCHAR(255) NOT NULL,
  q_en    VARCHAR(255) NOT NULL,
  ok_fa   VARCHAR(100) NOT NULL,
  ok_en   VARCHAR(100) NOT NULL,
  bad_fa  VARCHAR(300) NOT NULL,
  bad_en  VARCHAR(300) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO captcha_questions (q_fa, q_en, ok_fa, ok_en, bad_fa, bad_en) VALUES
 ('کدام یک میوه نیست؟', 'Which one is NOT a fruit?', 'هویج', 'Carrot', 'سیب|موز|انبه', 'Apple|Banana|Mango'),
 ('کدام یک حیوان نیست؟', 'Which one is NOT an animal?', 'میز', 'Table', 'گربه|اسب|شیر', 'Cat|Horse|Lion'),
 ('کدام عدد بزرگ‌تر از ۷۹ است؟', 'Which number is greater than 79?', '۸۵', '85', '۱۲|۴۷|۷۹', '12|47|79'),
 ('کدام یک وسیله نقلیه است؟', 'Which one is a vehicle?', 'اتوبوس', 'Bus', 'سیب|کتاب|صندلی', 'Apple|Book|Chair'),
 ('کدام یک شهر ایران نیست؟', 'Which one is NOT a city in Iran?', 'پاریس', 'Paris', 'شیراز|اصفهان|تبریز', 'Shiraz|Isfahan|Tabriz');