-- ============================================================
-- Jesus AI - Migration v1.6
-- 后台管理闭环：举报 / 节日内容 / 敏感词 / 系统设置 / 角色 / 审核与置顶
-- 幂等：可重复执行（MariaDB 10.4）
-- ============================================================
USE jesusai;

DROP PROCEDURE IF EXISTS add_col_if_exists_jesusai_v16;
DELIMITER $$
CREATE PROCEDURE add_col_if_exists_jesusai_v16(
    IN p_table VARCHAR(64), IN p_col VARCHAR(64), IN p_def TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_col
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_col, '` ', p_def);
        PREPARE st FROM @sql;
        EXECUTE st;
        DEALLOCATE PREPARE st;
    END IF;
END$$
DELIMITER ;

-- ---------- 1. users 角色 ----------
CALL add_col_if_exists_jesusai_v16('users', 'role',
    "VARCHAR(20) NOT NULL DEFAULT 'user' AFTER device_id");

-- ---------- 2. settings 键值表（系统设置） ----------
CREATE TABLE IF NOT EXISTS settings (
    `key`       VARCHAR(100) NOT NULL,
    `value`     TEXT NULL,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- 3. reports 举报记录表 ----------
CREATE TABLE IF NOT EXISTS reports (
    id          INT(11) NOT NULL AUTO_INCREMENT,
    reporter_id INT(11) NOT NULL,
    target_type ENUM('prayer','comment','user') NOT NULL,
    target_id   INT(11) NOT NULL,
    reason      VARCHAR(255) NOT NULL,
    status      ENUM('pending','approved','rejected') DEFAULT 'pending',
    handled_at  DATETIME NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY `target` (`target_type`,`target_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- 4. festival_content 节日专属内容表 ----------
CREATE TABLE IF NOT EXISTS festival_content (
    id           INT(11) NOT NULL AUTO_INCREMENT,
    festival_id  INT(11) NOT NULL,
    content_type ENUM('sermon','prayer','verse') NOT NULL,
    title        VARCHAR(200) DEFAULT NULL,
    content      TEXT NOT NULL,
    `language`   VARCHAR(10) DEFAULT 'zh',
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY `festival_id` (`festival_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- 5. sensitive_words 敏感词表 ----------
CREATE TABLE IF NOT EXISTS sensitive_words (
    id         INT(11) NOT NULL AUTO_INCREMENT,
    word       VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- 6. prayers / prayer_comments / group_posts 审核与置顶 ----------
CALL add_col_if_exists_jesusai_v16('prayers', 'pinned',
    "TINYINT(1) NOT NULL DEFAULT 0");
CALL add_col_if_exists_jesusai_v16('prayers', 'status',
    "VARCHAR(20) NOT NULL DEFAULT 'approved'");
CALL add_col_if_exists_jesusai_v16('prayer_comments', 'status',
    "VARCHAR(20) NOT NULL DEFAULT 'approved'");
CALL add_col_if_exists_jesusai_v16('group_posts', 'status',
    "VARCHAR(20) NOT NULL DEFAULT 'approved'");
CALL add_col_if_exists_jesusai_v16('group_post_comments', 'status',
    "VARCHAR(20) NOT NULL DEFAULT 'approved'");

-- ---------- 7. festivals 倒计时开关 ----------
CALL add_col_if_exists_jesusai_v16('festivals', 'show_countdown',
    "TINYINT(1) NOT NULL DEFAULT 1");

-- ---------- 8. settings 种子（默认值） ----------
INSERT IGNORE INTO settings (`key`, `value`) VALUES
('deepseek_model',        'deepseek-chat'),
('verse_auto_rotate',     '1'),
('verse_rotation_mode',   'sequential'),
('moderation_enabled',    '0'),
('sensitive_filter_enabled', '1');

-- admin 后台密码（bcrypt of 'admin123'，可在后台修改）
INSERT IGNORE INTO settings (`key`, `value`)
SELECT 'admin_password', '$2y$10$Xk9Gq0w7Jv8mQZ5h3lN0Y.5jO0KZ6Hq5eRy2S9dL1uM4cB7aVfWnC'
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE `key` = 'admin_password');

-- admin API token（curl/脚本调用 admin/api/ 用的固定令牌）
INSERT IGNORE INTO settings (`key`, `value`)
SELECT 'admin_api_token', CONCAT('jat_', LOWER(SUBSTR(MD5(RAND()), 1, 24)))
WHERE NOT EXISTS (SELECT 1 FROM settings WHERE `key` = 'admin_api_token');

-- ---------- 9. 敏感词示例种子 ----------
INSERT IGNORE INTO sensitive_words (word) VALUES
('赌博'), ('诈骗'), ('色情');

DROP PROCEDURE IF EXISTS add_col_if_exists_jesusai_v16;

SELECT 'migration_v1.6 done' AS status;