-- 数据库名称: jesus_ai
CREATE DATABASE IF NOT EXISTS jesus_ai DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jesus_ai;

-- 1. 用户表
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(100) NOT NULL COMMENT '设备唯一标识',
  `faith` varchar(50) DEFAULT '基督教' COMMENT '信仰背景',
  `bible_version` varchar(50) DEFAULT '和合本' COMMENT '偏好译本',
  `remind_time` time DEFAULT '07:00:00' COMMENT '每日提醒时间',
  `is_pro` tinyint(1) DEFAULT 0 COMMENT '是否高级用户',
  `streak_days` int(11) DEFAULT 0 COMMENT '连续打卡天数',
  `last_prayer_date` date DEFAULT NULL COMMENT '上次打卡日期',
  `total_chats` int(11) DEFAULT 0 COMMENT '总对话次数',
  `total_encouragements` int(11) DEFAULT 0 COMMENT '获得鼓励数（点赞）',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 经文表（用于RAG检索）
CREATE TABLE `scriptures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book_name` varchar(50) NOT NULL,
  `chapter` int(5) NOT NULL,
  `verse` int(5) NOT NULL,
  `content` text NOT NULL,
  `version` varchar(20) DEFAULT '和合本',
  `language` varchar(10) DEFAULT 'zh',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_content` (`content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 对话历史
CREATE TABLE `chat_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role` enum('user','assistant') NOT NULL,
  `message` text NOT NULL,
  `verses` text COMMENT '引用的经文JSON数组',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_time` (`user_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 每日经文缓存（可每日更新）
CREATE TABLE `daily_verse` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `verse_text` text NOT NULL,
  `ref` varchar(100) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 宗教节日表
CREATE TABLE `festivals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `date` date NOT NULL,
  `description` text,
  `icon` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. 圣经问答表
CREATE TABLE `quizzes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` text NOT NULL,
  `option_a` varchar(200) NOT NULL,
  `option_b` varchar(200) NOT NULL,
  `option_c` varchar(200) NOT NULL,
  `option_d` varchar(200) DEFAULT NULL,
  `correct_answer` tinyint(1) NOT NULL COMMENT '0=A,1=B,2=C,3=D',
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. 祷告墙
CREATE TABLE `prayers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `likes` int(11) DEFAULT 0,
  `comments_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. 兴趣小组
CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `member_count` int(11) DEFAULT 0,
  `icon` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. 用户小组关系
CREATE TABLE `user_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `joined_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_group` (`user_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. 灵修计划（预定义或用户自定义）
CREATE TABLE `plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('reading','prayer') NOT NULL,
  `total_items` int(11) DEFAULT 0,
  `completed_items` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. 成就定义
CREATE TABLE `achievements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `icon` varchar(20) NOT NULL,
  `condition_type` enum('streak','chats','prayers','verses') NOT NULL,
  `condition_value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. 用户成就解锁
CREATE TABLE `user_achievements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `achievement_id` int(11) NOT NULL,
  `unlocked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_achievement` (`user_id`,`achievement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13. 用户每日问答记录（防重复答题）
CREATE TABLE `user_quiz_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `quiz_id` int(11) NOT NULL,
  `answer` tinyint(1) NOT NULL,
  `is_correct` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_quiz` (`user_id`,`quiz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 初始数据
-- ============================================================

-- 经文种子数据（和合本，用于 RAG 检索与每日经文兜底）
INSERT INTO `scriptures` (`book_name`,`chapter`,`verse`,`content`,`version`,`language`) VALUES
('约翰福音',3,16,'神爱世人，甚至将他的独生子赐给他们，叫一切信他的，不至灭亡，反得永生。','和合本','zh'),
('约翰福音',14,6,'耶稣说：我就是道路、真理、生命；若不藉着我，没有人能到父那里去。','和合本','zh'),
('约翰福音',14,27,'我留下平安给你们；我将我的平安赐给你们。我所赐的，不像世人所赐的。你们心里不要忧愁，也不要胆怯。','和合本','zh'),
('约翰福音',15,5,'我是葡萄树，你们是枝子。常在我里面的，我也常在他里面，这人就多结果子；因为离了我，你们就不能做什么。','和合本','zh'),
('诗篇',23,1,'耶和华是我的牧者，我必不致缺乏。','和合本','zh'),
('诗篇',23,2,'他使我躺卧在青草地上，领我在可安歇的水边。','和合本','zh'),
('诗篇',23,3,'他使我的灵魂苏醒，为自己的名引导我走义路。','和合本','zh'),
('诗篇',23,4,'我虽然行过死荫的幽谷，也不怕遭害，因为你与我同在；你的杖，你的竿，都安慰我。','和合本','zh'),
('诗篇',46,1,'神是我们的避难所，是我们的力量，是我们在患难中随时的帮助。','和合本','zh'),
('诗篇',119,105,'你的话是我脚前的灯，是我路上的光。','和合本','zh'),
('诗篇',121,1,'我要向山举目；我的帮助从何而来？','和合本','zh'),
('诗篇',121,2,'我的帮助从造天地的耶和华而来。','和合本','zh'),
('腓立比书',4,6,'应当一无挂虑，只要凡事藉着祷告、祈求和感谢，将你们所要的告诉神。','和合本','zh'),
('腓立比书',4,7,'神所赐出人意外的平安，必在基督耶稣里保守你们的心怀意念。','和合本','zh'),
('腓立比书',4,13,'我靠着那加给我力量的，凡事都能做。','和合本','zh'),
('马太福音',11,28,'凡劳苦担重担的人，可以到我这里来，我就使你们得安息。','和合本','zh'),
('马太福音',11,29,'我心里柔和谦卑，你们当负我的轭，学我的样式，这样，你们心里就必得享安息。','和合本','zh'),
('马太福音',6,33,'你们要先求他的国和他的义，这些东西都要加给你们了。','和合本','zh'),
('马太福音',6,34,'所以，不要为明天忧虑，因为明天自有明天的忧虑。一天的难处一天当就够了。','和合本','zh'),
('马太福音',22,37,'耶稣对他说：你要尽心、尽性、尽意爱主你的神。','和合本','zh'),
('马太福音',22,39,'其次也相仿，就是要爱人如己。','和合本','zh'),
('以赛亚书',41,10,'你不要害怕，因为我与你同在；不要惊惶，因为我是你的神。我必坚固你，我必帮助你，我必用我公义的右手扶持你。','和合本','zh'),
('以赛亚书',40,31,'但那等候耶和华的，必重新得力。他们必如鹰展翅上腾，他们奔跑却不困倦，行走却不疲乏。','和合本','zh'),
('耶利米书',29,11,'耶和华说：我知道我向你们所怀的意念，是赐平安的意念，不是降灾祸的意念，要叫你们末后有指望。','和合本','zh'),
('箴言',3,5,'你要专心仰赖耶和华，不可倚靠自己的聪明。','和合本','zh'),
('箴言',3,6,'在你一切所行的事上都要认定他，他必指引你的路。','和合本','zh'),
('罗马书',8,28,'我们晓得万事都互相效力，叫爱神的人得益处，就是按他旨意被召的人。','和合本','zh'),
('罗马书',12,12,'在指望中要喜乐，在患难中要忍耐，祷告要恒切。','和合本','zh'),
('罗马书',1,7,'愿恩惠、平安从我们的父神并主耶稣基督归与你们！','和合本','zh'),
('哥林多前书',13,4,'爱是恒久忍耐，又有恩慈；爱是不嫉妒，爱是不自夸，不张狂。','和合本','zh'),
('哥林多前书',13,7,'凡事包容，凡事相信，凡事盼望，凡事忍耐。','和合本','zh'),
('约书亚记',1,9,'你当刚强壮胆！不要惧怕，也不要惊惶，因为你无论往哪里去，耶和华你的神必与你同在。','和合本','zh'),
('申命记',31,6,'你们当刚强壮胆，不要害怕，也不要畏惧他们，因为耶和华你的神和你同去。他必不撇下你，也不丢弃你。','和合本','zh'),
('提摩太后书',1,7,'因为神赐给我们，不是胆怯的心，乃是刚强、仁爱、谨守的心。','和合本','zh'),
('彼得前书',5,7,'你们要将一切的忧虑卸给神，因为他顾念你们。','和合本','zh'),
('出埃及记',3,14,'神对摩西说：我是自有永有的。又说：你要对以色列人这样说：那自有的打发我到你们这里来。','和合本','zh'),
('出埃及记',20,1,'神吩咐这一切的话说：我是耶和华你的神，曾将你从埃及地为奴之家领出来。','和合本','zh'),
('雅各书',1,5,'你们中间若有缺少智慧的，应当求那厚赐与众人、也不斥责人的神，主就必赐给他。','和合本','zh'),
('以弗所书',2,8,'你们得救是本乎恩，也因着信；这并不是出于自己，乃是神所赐的。','和合本','zh'),
('加拉太书',5,22,'圣灵所结的果子，就是仁爱、喜乐、和平、忍耐、恩慈、良善、信实。','和合本','zh'),
('启示录',21,4,'神要擦去他们一切的眼泪；不再有死亡，也不再有悲哀、哭号、疼痛，因为以前的事都过去了。','和合本','zh'),
('耶利米哀歌',3,22,'我们不致消灭，是出于耶和华诸般的慈爱；是因他的怜悯不致断绝。','和合本','zh'),
('耶利米哀歌',3,23,'每早晨这都是新的；你的诚实极其广大！','和合本','zh'),
('西番雅书',3,17,'耶和华你的神是施行拯救、大有能力的主。他在你中间必因你欢欣喜乐，默然爱你，且因你喜乐而欢呼。','和合本','zh');

-- 宗教节日（2026-08-31 之后）
INSERT INTO `festivals` (`name`,`date`,`description`,`icon`) VALUES
('诸圣节','2026-11-01','纪念历代圣徒，思念在天家得胜的见证人','🕯️'),
('将临期第一主日','2026-11-29','教会新年的开始，预备心等候主的降临','⏳'),
('圣诞节','2026-12-25','纪念耶稣基督降生：今天在大卫的城里，为你们生了救主','🎄'),
('主显节','2027-01-06','纪念东方博士朝拜圣婴，福音光照万邦','⭐'),
('圣灰星期三（大斋期开始）','2027-02-10','四十天斋期开始，省察悔改，专心仰望主','✝️'),
('棕枝主日','2027-03-21','纪念耶稣骑驴荣入耶路撒冷','🌿'),
('受难节','2027-03-26','纪念主耶稣为世人的罪被钉十字架','✝️'),
('复活节','2027-03-28','纪念耶稣基督复活：他不在坟墓里，他已经复活了！','🌅'),
('圣灵降临节','2027-05-16','纪念五旬节圣灵降临，教会的诞生','🔥');

-- 成就定义
INSERT INTO `achievements` (`name`,`icon`,`condition_type`,`condition_value`) VALUES
('初次对话','✝','chats',1),
('坚持7天','🔥','streak',7),
('坚持30天','🌟','streak',30),
('读经10章','📖','verses',10),
('代祷50次','🙏','prayers',50),
('属灵领袖','👑','streak',365);

-- 兴趣小组
INSERT INTO `groups` (`name`,`description`,`member_count`,`icon`) VALUES
('职场基督徒','在职场中活出信仰，见证基督',156,'💼'),
('初信者之家','初信栽培，属灵生命成长',89,'🌱'),
('婚姻家庭','建造基督化的家庭',124,'🏠'),
('青年团契','年轻人的属灵同伴',210,'⚡'),
('查经小组','按卷研读圣经，明白真道',67,'📖'),
('代祷勇士','为教会和肢体恒切代祷',98,'🙏');

-- 每日问答（近十日；quiz.php 找不到当日时会按天轮换）
INSERT INTO `quizzes` (`question`,`option_a`,`option_b`,`option_c`,`option_d`,`correct_answer`,`date`) VALUES
('耶稣在旷野禁食多少昼夜？','20天','30天','40天','70天',2,'2026-08-31'),
('挪亚一家在方舟里共有几个人？','6人','8人','10人','4人',1,'2026-09-01'),
('大卫用什么战胜了歌利亚？','宝剑','长矛','机弦石子','弓箭',2,'2026-09-02'),
('摩西在哪里领受十诫？','迦密山','西奈山','橄榄山','锡安山',1,'2026-09-03'),
('约翰福音中"太初有道"的"道"指谁？','施洗约翰','彼得','保罗','耶稣基督',3,'2026-09-04'),
('保罗悔改之前的身份是？','渔夫','税吏','法利赛人','祭司长',2,'2026-09-05'),
('耶稣所行的第一个神迹是？','水变酒','五饼二鱼','医好瘫子','平静风和海',0,'2026-09-06'),
('五旬节圣灵降临时的形象是？','鸽子','舌头如火焰','云柱','彩虹',1,'2026-09-07'),
('"爱情如死之坚强"出自哪卷书？','箴言','传道书','雅歌','何西阿书',2,'2026-09-08'),
('十二门徒中曾作税吏的是？','彼得','马太','雅各','安得烈',1,'2026-09-09');

-- 每日经文缓存（近三日示例，其余由 daily_verse.php 从 scriptures 轮换生成）
INSERT INTO `daily_verse` (`verse_text`,`ref`,`date`) VALUES
('我留下平安给你们；我将我的平安赐给你们。你们心里不要忧愁，也不要胆怯。','约翰福音 14:27','2026-08-31'),
('凡劳苦担重担的人，可以到我这里来，我就使你们得安息。','马太福音 11:28','2026-09-01'),
('但那等候耶和华的，必重新得力。他们必如鹰展翅上腾。','以赛亚书 40:31','2026-09-02');

-- 祷告墙示例
INSERT INTO `prayers` (`user_id`,`content`,`likes`,`comments_count`) VALUES
(0,'请为我的父亲手术顺利祷告，求主医治，也求主赐平安给全家。',12,2),
(0,'为职场中的弟兄姐妹祷告，愿主赐智慧，让我们的工作成为见证。',8,1),
(0,'为受灾地区的百姓祷告，求主亲自安慰失去亲人的人。',21,0);