-- ============================================================
-- JesusAI 迁移脚本 v1.4 团契详情+祷告评论+个人资料扩展（幂等版）
-- 执行: mysql -u root jesusai --default-character-set=utf8mb4 < migration_v1.4_group_reply_profile.sql
-- ============================================================

-- 1) 祷告评论表：每条祷告下的 Reply
CREATE TABLE IF NOT EXISTS prayer_comments (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  prayer_id   INT NOT NULL,
  user_id     INT NOT NULL,
  content     VARCHAR(500) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY prayer_id_idx (prayer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) prayers 加 group_id（0=公开祷告墙；>0=团契组内专属）
ALTER TABLE prayers ADD COLUMN IF NOT EXISTS group_id INT NOT NULL DEFAULT 0 AFTER user_id;
ALTER TABLE prayers ADD INDEX IF NOT EXISTS group_id_idx (group_id);

-- prayers.comments_count 统计字段（自动由 prayer_comments 触发器计算）
ALTER TABLE prayers ADD COLUMN IF NOT EXISTS comments_count INT NOT NULL DEFAULT 0 AFTER likes;

-- 3) groups 加双语描述、管理员可见图片；members 计数维护
ALTER TABLE groups ADD COLUMN IF NOT EXISTS name_en        VARCHAR(100) NULL AFTER name;
ALTER TABLE groups ADD COLUMN IF NOT EXISTS description_en VARCHAR(500) NULL AFTER description;
ALTER TABLE groups ADD COLUMN IF NOT EXISTS location       VARCHAR(100) NULL AFTER description_en;
ALTER TABLE groups ADD COLUMN IF NOT EXISTS location_en    VARCHAR(100) NULL AFTER location;

-- 双语团契名（按中文名匹配）
UPDATE groups SET name_en = 'Youth Fellowship',        name = '青年团契'   WHERE name LIKE '%青年%';
UPDATE groups SET name_en = 'Workplace Believers',     name = '职场基督徒' WHERE name LIKE '%职场%';
UPDATE groups SET name_en = 'Marriage & Family',       name = '婚姻家庭'   WHERE name LIKE '%婚姻%';
UPDATE groups SET name_en = 'Prayer Warriors',         name = '代祷勇士'   WHERE name LIKE '%代祷%';
UPDATE groups SET name_en = 'New Believers Home',      name = '初信者之家' WHERE name LIKE '%初信%';
UPDATE groups SET name_en = 'Bible Study Group',       name = '查经小组'   WHERE name LIKE '%查经%';
UPDATE groups SET name_en = 'Worship Team',            name = '敬拜团'     WHERE name LIKE '%敬拜%';
UPDATE groups SET name_en = 'Charity & Service',       name = '慈善服务'   WHERE name LIKE '%慈善%';
UPDATE groups SET name_en = 'Single Believers',        name = '单身信徒'   WHERE name LIKE '%单身%';
UPDATE groups SET name_en = 'Couples Fellowship',      name = '夫妻团契'   WHERE name LIKE '%夫妻%';
UPDATE groups SET name_en = 'Senior Fellowship',       name = '长者团契'   WHERE name LIKE '%长者%';
UPDATE groups SET name_en = 'Campus Fellowship',       name = '校园团契'   WHERE name LIKE '%校园%';

UPDATE groups SET description_en = 'For youth to grow in faith together'                   WHERE name_en = 'Youth Fellowship';
UPDATE groups SET description_en = 'Devotions for workplace challenges'                    WHERE name_en = 'Workplace Believers';
UPDATE groups SET description_en = 'Building Christ-centered marriages and families'        WHERE name_en = 'Marriage & Family';
UPDATE groups SET description_en = 'Lift up prayers for the church and the world'           WHERE name_en = 'Prayer Warriors';
UPDATE groups SET description_en = 'Guiding new believers in their first steps'            WHERE name_en = 'New Believers Home';
UPDATE groups SET description_en = 'Deep dive into Scripture together'                     WHERE name_en = 'Bible Study Group';
UPDATE groups SET description_en = 'Serving through worship and music ministry'            WHERE name_en = 'Worship Team';
UPDATE groups SET description_en = 'Serve the needy with the love of Christ'               WHERE name_en = 'Charity & Service';
UPDATE groups SET description_en = 'Fellowship for single believers seeking God'            WHERE name_en = 'Single Believers';
UPDATE groups SET description_en = 'Couples growing together in faith'                     WHERE name_en = 'Couples Fellowship';
UPDATE groups SET description_en = 'Fellowship for seniors walking with the Lord'          WHERE name_en = 'Senior Fellowship';
UPDATE groups SET description_en = 'Student ministry on campus - grow in faith together'   WHERE name_en = 'Campus Fellowship';

-- 4) 用户资料扩展：性别、所在教会/城市（Email/手机号砍掉）
ALTER TABLE users ADD COLUMN IF NOT EXISTS gender    VARCHAR(10) NULL  AFTER nickname;   -- male / female / other
ALTER TABLE users ADD COLUMN IF NOT EXISTS church    VARCHAR(120) NULL AFTER gender;     -- 所在教会
ALTER TABLE users ADD COLUMN IF NOT EXISTS city      VARCHAR(100) NULL AFTER church;     -- 城市

-- 5) group_posts 表（团契动态墙）——比复用 prayers 更清晰
CREATE TABLE IF NOT EXISTS group_posts (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  group_id   INT NOT NULL,
  user_id    INT NOT NULL,
  content    TEXT NOT NULL,
  likes      INT UNSIGNED NOT NULL DEFAULT 0,
  comments_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY group_id_idx (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;