-- ============================================================
-- JesusAI 迁移脚本 v1.2 内容循环 + 问题缓存 + 双语内容（幂等版）
-- 可重复执行：已应用过的变更会自动跳过，不会报 #1060/#1061 错误
-- 执行: mysql -u root jesusai < migration_v1.2_content_loop.sql
-- 或在 phpMyAdmin 中直接导入亦可
-- ============================================================

-- 1) 问题缓存表：相同问题（归一化后）+ 角色 + 语言 直接复用回复，节省 DeepSeek token
CREATE TABLE IF NOT EXISTS chat_cache (
  cache_key CHAR(32) PRIMARY KEY,
  role      VARCHAR(20) NOT NULL,
  lang      VARCHAR(5)  NOT NULL DEFAULT 'zh',
  reply     MEDIUMTEXT  NOT NULL,
  verses    TEXT        NULL,
  hits      INT UNSIGNED NOT NULL DEFAULT 1,
  created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) quizzes 加语言列（MariaDB 支持 IF NOT EXISTS，重复执行自动跳过）
ALTER TABLE quizzes ADD COLUMN IF NOT EXISTS language VARCHAR(5) NOT NULL DEFAULT 'zh' AFTER id;
UPDATE quizzes SET language = 'zh' WHERE language IS NULL OR language = '';

-- date 唯一键阻碍中英文同日各一题，移除（重复执行自动跳过）
ALTER TABLE quizzes DROP INDEX IF EXISTS date;

-- 英文题种子：仅当题库还没有英文题时插入（幂等）
INSERT INTO quizzes (language, question, option_a, option_b, option_c, option_d, correct_answer, date)
SELECT 'en', t.question, t.option_a, t.option_b, t.option_c, t.option_d, t.correct_answer, t.date
FROM (
  SELECT 'How many days and nights did Jesus fast in the wilderness?' AS question, '3 days' AS option_a, '40 days' AS option_b, '7 days' AS option_c, NULL AS option_d, 1 AS correct_answer, '2026-01-01' AS date UNION ALL
  SELECT 'How many people were saved in Noah''s ark?', '4', '6', '8', NULL, 2, '2026-01-02' UNION ALL
  SELECT 'What did David use to defeat Goliath?', 'A sword', 'A sling and a stone', 'A spear', NULL, 1, '2026-01-03' UNION ALL
  SELECT 'Where did Moses receive the Ten Commandments?', 'Mount Sinai', 'Mount Carmel', 'Mount Nebo', NULL, 0, '2026-01-04' UNION ALL
  SELECT 'In John''s Gospel, "the Word" (Logos) refers to whom?', 'The Father', 'Jesus Christ', 'The Holy Spirit', NULL, 1, '2026-01-05' UNION ALL
  SELECT 'Who was Paul before his conversion?', 'A Roman soldier', 'A fisherman', 'A Pharisee who persecuted Christians', NULL, 2, '2026-01-06' UNION ALL
  SELECT 'What was Jesus'' first miracle?', 'Walking on water', 'Turning water into wine at Cana', 'Feeding the 5000', NULL, 1, '2026-01-07' UNION ALL
  SELECT 'What form did the Holy Spirit take at Pentecost?', 'A dove', 'Tongues of fire', 'A cloud', NULL, 1, '2026-01-08' UNION ALL
  SELECT '"Love is as strong as death" comes from which book?', 'Psalms', 'Proverbs', 'Song of Songs', NULL, 2, '2026-01-09' UNION ALL
  SELECT 'Which of the twelve disciples was a tax collector?', 'Matthew', 'Thomas', 'James', NULL, 0, '2026-01-10'
) AS t
WHERE NOT EXISTS (SELECT 1 FROM quizzes WHERE language = 'en');

-- 3) festivals 英文字段（重复执行自动跳过）
ALTER TABLE festivals
  ADD COLUMN IF NOT EXISTS name_en        VARCHAR(100) NULL AFTER name,
  ADD COLUMN IF NOT EXISTS description_en VARCHAR(255) NULL AFTER description;

-- UPDATE 天然幂等：重复执行只是覆盖相同值
UPDATE festivals SET name_en = 'All Saints'' Day',        description_en = 'Commemorating all the saints, known and unknown.' WHERE name = '诸圣节';
UPDATE festivals SET name_en = 'First Sunday of Advent', description_en = 'The beginning of the Advent season of waiting.'    WHERE name LIKE '将临期%';
UPDATE festivals SET name_en = 'Christmas Day',          description_en = 'Celebrating the birth of Jesus Christ.'            WHERE name = '圣诞节';
UPDATE festivals SET name_en = 'Epiphany',               description_en = 'The manifestation of Christ to the Gentiles.'      WHERE name = '主显节';
UPDATE festivals SET name_en = 'Ash Wednesday',          description_en = 'The beginning of Lent, a season of repentance.'    WHERE name LIKE '圣灰%';
UPDATE festivals SET name_en = 'Palm Sunday',            description_en = 'Jesus'' triumphant entry into Jerusalem.'          WHERE name LIKE '棕枝%';
UPDATE festivals SET name_en = 'Good Friday',            description_en = 'Commemorating the crucifixion of Jesus Christ.'    WHERE name LIKE '受难%';
UPDATE festivals SET name_en = 'Easter Sunday',          description_en = 'Celebrating the resurrection of Jesus Christ.'     WHERE name LIKE '复活%';
UPDATE festivals SET name_en = 'Pentecost',              description_en = 'The coming of the Holy Spirit upon the Church.'    WHERE name LIKE '圣灵%';