-- ============================================================
-- Jesus AI - 多语言经文扩展（2026-09-01）
-- 1. daily_verse 表加 language 字段 + 调整唯一键
-- 2. 插入 KJV 英文经文种子（20 条经典）
-- ============================================================
USE jesusai;

-- ---------- 1. daily_verse 表 schema 迁移 ----------
ALTER TABLE `daily_verse`
  ADD COLUMN IF NOT EXISTS `language` varchar(10) NOT NULL DEFAULT 'zh' AFTER `ref`;

-- 删除旧唯一键 date，重建 (date, language) 联合唯一
SET @old_key_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'daily_verse'
    AND index_name = 'date'
);
SET @sql := IF(@old_key_exists > 0,
  'ALTER TABLE `daily_verse` DROP INDEX `date`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE `daily_verse`
  ADD UNIQUE KEY `uniq_date_lang` (`date`, `language`);

-- ---------- 2. KJV 英文经文种子 ----------
INSERT INTO `scriptures` (`book_name`, `chapter`, `verse`, `content`, `version`, `language`) VALUES
('John',     3, 16, 'For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.', 'KJV', 'en'),
('John',     11, 25, 'Jesus said unto her, I am the resurrection, and the life: he that believeth in me, though he were dead, yet shall he live:', 'KJV', 'en'),
('John',     14, 6, 'Jesus saith unto him, I am the way, the truth, and the life: no man cometh unto the Father, but by me.', 'KJV', 'en'),
('Matthew',  5, 3, 'Blessed are the poor in spirit: for theirs is the kingdom of heaven.', 'KJV', 'en'),
('Matthew',  6, 33, 'But seek ye first the kingdom of God, and his righteousness; and all these things shall be added unto you.', 'KJV', 'en'),
('Matthew',  11, 28, 'Come unto me, all ye that labour and are heavy laden, and I will give you rest.', 'KJV', 'en'),
('Matthew',  28, 20, 'Teaching them to observe all things whatsoever I have commanded you: and, lo, I am with you alway, even unto the end of the world. Amen.', 'KJV', 'en'),
('Mark',     11, 24, 'Therefore I say unto you, What things soever ye desire, when ye pray, believe that ye receive them, and ye shall have them.', 'KJV', 'en'),
('Luke',     6, 38, 'Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men give into your bosom.', 'KJV', 'en'),
('John',     8, 12, 'Then spake Jesus again unto them, saying, I am the light of the world: he that followeth me shall not walk in darkness, but shall have the light of life.', 'KJV', 'en'),
('Romans',   8, 28, 'And we know that all things work together for good to them that love God, to them who are the called according to his purpose.', 'KJV', 'en'),
('Romans',   12, 2, 'And be not conformed to this world: but be ye transformed by the renewing of your mind, that ye may prove what is that good, and acceptable, and perfect, will of God.', 'KJV', 'en'),
('1 Corinthians', 13, 4, 'Charity suffereth long, and is kind; charity envieth not; charity vaunteth not itself, is not puffed up,', 'KJV', 'en'),
('1 Corinthians', 13, 13, 'And now abideth faith, hope, charity, these three; but the greatest of these is charity.', 'KJV', 'en'),
('Philippians', 4, 6, 'Be careful for nothing; but in every thing by prayer and supplication with thanksgiving let your requests be made known unto God.', 'KJV', 'en'),
('Philippians', 4, 13, 'I can do all things through Christ which strengtheneth me.', 'KJV', 'en'),
('Philippians', 4, 19, 'But my God shall supply all your need according to his riches in glory by Christ Jesus.', 'KJV', 'en'),
('1 Peter',  5, 7, 'Casting all your care upon him; for he careth for you.', 'KJV', 'en'),
('Psalm',    23, 1, 'The LORD is my shepherd; I shall not want.', 'KJV', 'en'),
('Psalm',    46, 1, 'God is our refuge and strength, a very present help in trouble.', 'KJV', 'en'),
('Proverbs', 3, 5, 'Trust in the LORD with all thine heart; and lean not unto thine own understanding.', 'KJV', 'en'),
('Isaiah',   41, 10, 'Fear thou not; for I am with thee: be not dismayed; for I am thy God: I will strengthen thee; yea, I will help thee; yea, I will uphold thee with the right hand of my righteousness.', 'KJV', 'en')
ON DUPLICATE KEY UPDATE `content` = VALUES(`content`);

-- ---------- 3. 为英文 content 全文索引补建（InnoDB FULLTEXT 默认已含） ----------
-- 已有 FULLTEXT KEY ft_content(content)，无需重建