package com.yaoai.bootstrap;

import cn.hutool.crypto.digest.BCrypt;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;

@Component
@Profile("dev-h2")
@RequiredArgsConstructor
public class DevSampleDataSeeder implements ApplicationRunner {

    private final JdbcTemplate jdbcTemplate;

    @Override
    public void run(ApplicationArguments args) {
        seedTenants();
        seedProjects();
        seedBillingRecords();
        seedReportUpstreamData();
        seedCustomerTickets();
        seedCustomerScripts();
        seedContentArticles();
        ensurePlatformSettingColumns();
        seedPlatformSettings();
    }

    private void seedTenants() {
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM tenants", Integer.class);
        if (count != null && count > 0) {
            return;
        }
        String passwordHash = BCrypt.hashpw("Dramix@123", BCrypt.gensalt());
        jdbcTemplate.update("""
                INSERT INTO tenants (id, name, slug, plan, status, credits, frozen_credits, notes, created_at, updated_at)
                VALUES
                (1001, '星火互动文化', 'spark-media', 'pro', 1, 12880, 0, '专业版试用转正客户', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (1002, '环声传媒', 'echo-media', 'team', 1, 42860, 0, '团队版重点客户', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (1003, '青石影像工作室', 'qingshi-studio', 'free', 0, 320, 0, '资质待补充，暂时冻结', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                """);
        jdbcTemplate.update("""
                INSERT INTO tenant_balances (id, tenant_id, balance, total_recharged, total_spent, version, updated_at)
                VALUES
                (2001, 1001, 12880, 20000, 7120, 0, CURRENT_TIMESTAMP),
                (2002, 1002, 42860, 50000, 7140, 0, CURRENT_TIMESTAMP),
                (2003, 1003, 320, 1000, 680, 0, CURRENT_TIMESTAMP)
                """);
        jdbcTemplate.update("""
                INSERT INTO users (id, tenant_id, username, email, phone, password_hash, role, status, last_login_at, created_at, updated_at)
                VALUES
                (3001, 1001, 'spark_admin', 'admin@spark.example', '13800001001', ?, 'owner', 1, DATEADD('HOUR', -2, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (3002, 1001, 'spark_editor', 'editor@spark.example', '13800001002', ?, 'member', 1, DATEADD('DAY', -1, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (3003, 1002, 'echo_admin', 'admin@echo.example', '13800002001', ?, 'owner', 1, DATEADD('HOUR', -5, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
                (3004, 1003, 'qingshi_admin', 'admin@qingshi.example', '13800003001', ?, 'owner', 0, DATEADD('DAY', -12, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                """, passwordHash, passwordHash, passwordHash, passwordHash);
        jdbcTemplate.update("""
                INSERT INTO tenant_members (id, tenant_id, user_id, role, position, joined_at)
                VALUES
                (4001, 1001, 3001, 'owner', '企业管理员', CURRENT_TIMESTAMP),
                (4002, 1001, 3002, 'member', '短剧制作', CURRENT_TIMESTAMP),
                (4003, 1002, 3003, 'owner', '企业管理员', CURRENT_TIMESTAMP),
                (4004, 1003, 3004, 'owner', '企业管理员', CURRENT_TIMESTAMP)
                """);
        jdbcTemplate.update("""
                INSERT INTO tenant_quotas (tenant_id, max_members, max_projects, max_monthly_credits, max_concurrent_tasks, monthly_budget_limit, notes, updated_at, updated_by)
                VALUES
                (1001, 5, 20, 3000, 3, 50000, '专业版默认额度', CURRENT_TIMESTAMP, 1),
                (1002, 20, 100, 20000, 5, 200000, '团队版默认额度', CURRENT_TIMESTAMP, 1),
                (1003, 1, 3, 100, 1, 1000, '免费版额度', CURRENT_TIMESTAMP, 1)
                """);
    }

    private void seedProjects() {
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM projects", Integer.class);
        if (count != null && count > 0) {
            return;
        }
        jdbcTemplate.update("""
                INSERT INTO projects (id, tenant_id, user_id, name, description, status, style, aspect_ratio, resolution, created_at, updated_at)
                VALUES
                (5001, 1001, 3001, '都市逆袭短剧', '试投放项目', 'active', 'realistic', '16:9', '720p', DATEADD('DAY', -3, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP),
                (5002, 1001, 3002, '悬疑反转合集', '分镜制作中', 'draft', 'cinematic', '9:16', '1080p', DATEADD('DAY', -1, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP),
                (5003, 1002, 3003, '国风奇遇记', '团队版样片', 'active', 'anime', '16:9', '1080p', DATEADD('DAY', -5, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
                """);
    }

    private void seedBillingRecords() {
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM billing_records", Integer.class);
        if (count != null && count > 0) {
            return;
        }
        Object[][] rows = {
                {6001L, 1001L, 5001L, 3001L, null, "recharge", "RECHARGE", "admin", "manual_recharge", "运营充值", 1, true, new BigDecimal("20000"), "SOFT", "ORDER-O202605260091"},
                {6002L, 1001L, 5001L, 3001L, null, "outline_generate", "TEXT", "volcengine", "doubao-seed-2-0-code-preview-260215", "豆包 Pro 文本生成", 1, true, new BigDecimal("120"), "SOFT", "TASK-OUTLINE-001"},
                {6003L, 1001L, 5001L, 3002L, null, "video_generate", "VIDEO", "volcengine", "doubao-seedance-2-0-fast-260128", "Seedance 图生视频", 4, true, new BigDecimal("4200"), "STRICT", "TASK-VIDEO-001"},
                {6004L, 1002L, 5003L, 3003L, null, "recharge", "RECHARGE", "admin", "manual_recharge", "运营充值", 1, true, new BigDecimal("50000"), "SOFT", "ORDER-O202605250218"},
                {6005L, 1002L, 5003L, 3003L, null, "image_generate", "IMAGE", "volcengine", "doubao-seedream-5-0-260128", "Seedream 文生图", 12, true, new BigDecimal("2880"), "STRICT", "TASK-IMAGE-001"},
                {6006L, 1003L, null, 3004L, null, "recharge", "RECHARGE", "admin", "trial_opening", "试用赠送", 1, true, new BigDecimal("1000"), "SOFT", "TRIAL-OPENING-001"},
                {6007L, 1003L, null, 3004L, null, "storyboard_generate", "TEXT", "volcengine", "doubao-seed-2-0-code-preview-260215", "豆包 Pro 文本生成", 1, false, BigDecimal.ZERO, "SOFT", "TASK-FAIL-001"}
        };
        for (Object[] row : rows) {
            jdbcTemplate.update("""
                    INSERT INTO billing_records
                    (id, tenant_id, project_id, user_id, created_by_admin_id, operation, modality, model_provider, model_id, model_name,
                     unit_count, success, credits, billing_mode, ref_id, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, DATEADD('HOUR', -?, CURRENT_TIMESTAMP))
                    """, row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9],
                    row[10], row[11], row[12], row[13], row[14], ((Number) row[0]).longValue() - 6000);
        }
    }

    private void seedReportUpstreamData() {
        String passwordHash = BCrypt.hashpw("Dramix@123", BCrypt.gensalt());
        insertIfMissing("tenants", 1004L, """
                INSERT INTO tenants (id, name, slug, plan, status, credits, frozen_credits, notes, created_at, updated_at)
                VALUES (1004, '灵感短剧社', 'inspire-drama', 'free', 1, 780, 0, '入驻后尚未产生真实充值', DATEADD('DAY', -18, CURRENT_TIMESTAMP), DATEADD('DAY', -12, CURRENT_TIMESTAMP))
                """);
        insertIfMissing("tenants", 1005L, """
                INSERT INTO tenants (id, name, slug, plan, status, credits, frozen_credits, notes, created_at, updated_at)
                VALUES (1005, '南风影业', 'southwind-film', 'free', 1, 420, 0, '内容试用客户，待商业转化', DATEADD('DAY', -11, CURRENT_TIMESTAMP), DATEADD('DAY', -8, CURRENT_TIMESTAMP))
                """);
        insertIfMissing("tenants", 1006L, """
                INSERT INTO tenants (id, name, slug, plan, status, credits, frozen_credits, notes, created_at, updated_at)
                VALUES (1006, '星轨传媒', 'starlane-media', 'pro', 1, 15880, 0, '专业版复购客户', DATEADD('DAY', -28, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
                """);

        insertIfMissing("tenant_balances", 2004L, """
                INSERT INTO tenant_balances (id, tenant_id, balance, total_recharged, total_spent, version, updated_at)
                VALUES (2004, 1004, 780, 0, 220, 0, CURRENT_TIMESTAMP)
                """);
        insertIfMissing("tenant_balances", 2005L, """
                INSERT INTO tenant_balances (id, tenant_id, balance, total_recharged, total_spent, version, updated_at)
                VALUES (2005, 1005, 420, 0, 580, 0, CURRENT_TIMESTAMP)
                """);
        insertIfMissing("tenant_balances", 2006L, """
                INSERT INTO tenant_balances (id, tenant_id, balance, total_recharged, total_spent, version, updated_at)
                VALUES (2006, 1006, 15880, 26000, 10120, 0, CURRENT_TIMESTAMP)
                """);

        insertIfMissing("users", 3005L, """
                INSERT INTO users (id, tenant_id, username, email, phone, password_hash, role, status, last_login_at, created_at, updated_at)
                VALUES (3005, 1004, 'inspire_admin', 'admin@inspire.example', '13800004001', ?, 'owner', 1, DATEADD('DAY', -35, CURRENT_TIMESTAMP), DATEADD('DAY', -18, CURRENT_TIMESTAMP), DATEADD('DAY', -12, CURRENT_TIMESTAMP))
                """, passwordHash);
        insertIfMissing("users", 3006L, """
                INSERT INTO users (id, tenant_id, username, email, phone, password_hash, role, status, last_login_at, created_at, updated_at)
                VALUES (3006, 1005, 'southwind_admin', 'admin@southwind.example', '13800005001', ?, 'owner', 1, DATEADD('DAY', -9, CURRENT_TIMESTAMP), DATEADD('DAY', -11, CURRENT_TIMESTAMP), DATEADD('DAY', -8, CURRENT_TIMESTAMP))
                """, passwordHash);
        insertIfMissing("users", 3007L, """
                INSERT INTO users (id, tenant_id, username, email, phone, password_hash, role, status, last_login_at, created_at, updated_at)
                VALUES (3007, 1006, 'starlane_admin', 'admin@starlane.example', '13800006001', ?, 'owner', 1, DATEADD('HOUR', -6, CURRENT_TIMESTAMP), DATEADD('DAY', -28, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
                """, passwordHash);

        insertIfMissing("tenant_members", 4005L, """
                INSERT INTO tenant_members (id, tenant_id, user_id, role, position, joined_at)
                VALUES (4005, 1004, 3005, 'owner', '企业管理员', DATEADD('DAY', -18, CURRENT_TIMESTAMP))
                """);
        insertIfMissing("tenant_members", 4006L, """
                INSERT INTO tenant_members (id, tenant_id, user_id, role, position, joined_at)
                VALUES (4006, 1005, 3006, 'owner', '企业管理员', DATEADD('DAY', -11, CURRENT_TIMESTAMP))
                """);
        insertIfMissing("tenant_members", 4007L, """
                INSERT INTO tenant_members (id, tenant_id, user_id, role, position, joined_at)
                VALUES (4007, 1006, 3007, 'owner', '企业管理员', DATEADD('DAY', -28, CURRENT_TIMESTAMP))
                """);

        insertTenantQuotaIfMissing(1004L, 1, 3, 1000, 1, new BigDecimal("1000"), "免费版内容试用额度");
        insertTenantQuotaIfMissing(1005L, 1, 3, 1000, 1, new BigDecimal("1000"), "免费版内容试用额度");
        insertTenantQuotaIfMissing(1006L, 5, 20, 3000, 3, new BigDecimal("50000"), "专业版复购客户额度");

        insertIfMissing("projects", 5004L, """
                INSERT INTO projects (id, tenant_id, user_id, name, description, status, style, aspect_ratio, resolution, created_at, updated_at)
                VALUES (5004, 1004, 3005, '校园甜宠样片', '试用客户创建但长期未推进', 'draft', 'cinematic', '9:16', '720p', DATEADD('DAY', -17, CURRENT_TIMESTAMP), DATEADD('DAY', -35, CURRENT_TIMESTAMP))
                """);
        insertIfMissing("projects", 5005L, """
                INSERT INTO projects (id, tenant_id, user_id, name, description, status, style, aspect_ratio, resolution, created_at, updated_at)
                VALUES (5005, 1005, 3006, '民国悬疑预告', '内容试作项目', 'active', 'realistic', '16:9', '1080p', DATEADD('DAY', -10, CURRENT_TIMESTAMP), DATEADD('DAY', -8, CURRENT_TIMESTAMP))
                """);
        insertIfMissing("projects", 5006L, """
                INSERT INTO projects (id, tenant_id, user_id, name, description, status, style, aspect_ratio, resolution, created_at, updated_at)
                VALUES (5006, 1006, 3007, '职场逆袭第二季', '复购客户批量生产项目', 'active', 'cinematic', '9:16', '1080p', DATEADD('DAY', -20, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
                """);

        Object[][] billingRows = {
                {6010L, 1001L, 5001L, 3001L, "recharge", "RECHARGE", "admin", "manual_recharge", "运营充值", 1, true, new BigDecimal("8000"), "SOFT", "ORDER-O202605270101", 10},
                {6011L, 1006L, 5006L, 3007L, "recharge", "RECHARGE", "admin", "manual_recharge", "运营充值", 1, true, new BigDecimal("12000"), "SOFT", "ORDER-O202605190301", 72},
                {6012L, 1006L, 5006L, 3007L, "recharge", "RECHARGE", "admin", "manual_recharge", "运营充值", 1, true, new BigDecimal("14000"), "SOFT", "ORDER-O202605260302", 16},
                {6013L, 1006L, 5006L, 3007L, "video_generate", "VIDEO", "volcengine", "doubao-seedance-2-0-fast-260128", "Seedance 图生视频", 6, true, new BigDecimal("6300"), "STRICT", "TASK-VIDEO-STARLANE-001", 12},
                {6014L, 1006L, 5006L, 3007L, "image_generate", "IMAGE", "volcengine", "doubao-seedream-5-0-260128", "Seedream 文生图", 18, true, new BigDecimal("4320"), "STRICT", "TASK-IMAGE-STARLANE-001", 11},
                {6015L, 1005L, 5005L, 3006L, "outline_generate", "TEXT", "volcengine", "doubao-seed-2-0-code-preview-260215", "豆包 Pro 文本生成", 1, true, new BigDecimal("160"), "SOFT", "TASK-OUTLINE-SOUTHWIND-001", 100},
                {6016L, 1005L, 5005L, 3006L, "image_generate", "IMAGE", "volcengine", "doubao-seedream-5-0-260128", "Seedream 文生图", 3, false, BigDecimal.ZERO, "STRICT", "TASK-IMAGE-SOUTHWIND-FAIL", 8}
        };
        for (Object[] row : billingRows) {
            insertIfMissing("billing_records", ((Number) row[0]).longValue(), """
                    INSERT INTO billing_records
                    (id, tenant_id, project_id, user_id, created_by_admin_id, operation, modality, model_provider, model_id, model_name,
                     unit_count, success, credits, billing_mode, ref_id, created_at)
                    VALUES (?, ?, ?, ?, null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, DATEADD('HOUR', -?, CURRENT_TIMESTAMP))
                    """, row);
        }

        Object[][] taskRows = {
                {7001L, 1001L, 5001L, "text_to_video", "succeeded", "EXT-SPARK-VIDEO-001", "都市逆袭短剧 8 秒镜头", "https://cdn.example.com/spark-001.mp4", null, 8, "9:16", true, 12},
                {7002L, 1002L, 5003L, "ref_image_to_video", "succeeded", "EXT-ECHO-IMAGE-001", "国风奇遇人物入画", "https://cdn.example.com/echo-001.mp4", null, 6, "16:9", false, 20},
                {7003L, 1005L, 5005L, "structured_video", "failed", "EXT-SOUTH-FAIL-001", "民国悬疑预告失败任务", null, "素材分辨率低于模型要求", 5, "16:9", true, 8},
                {7004L, 1006L, 5006L, "structured_video", "succeeded", "EXT-STAR-VIDEO-001", "职场逆袭第二季主视觉", "https://cdn.example.com/starlane-001.mp4", null, 8, "9:16", true, 10}
        };
        for (Object[] row : taskRows) {
            insertIfMissing("ai_tasks", ((Number) row[0]).longValue(), """
                    INSERT INTO ai_tasks
                    (id, tenant_id, project_id, task_type, status, external_task_id, prompt, result_video_url, error_message,
                     video_duration, video_ratio, generate_audio, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, DATEADD('HOUR', -?, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
                    """, row);
        }
    }

    private void insertTenantQuotaIfMissing(Long tenantId, int maxMembers, int maxProjects, int monthlyCredits,
                                            int concurrentTasks, BigDecimal budget, String notes) {
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM tenant_quotas WHERE tenant_id = ?", Integer.class, tenantId);
        if (count != null && count > 0) {
            return;
        }
        jdbcTemplate.update("""
                INSERT INTO tenant_quotas (tenant_id, max_members, max_projects, max_monthly_credits, max_concurrent_tasks, monthly_budget_limit, notes, updated_at, updated_by)
                VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, 1)
                """, tenantId, maxMembers, maxProjects, monthlyCredits, concurrentTasks, budget, notes);
    }

    private void seedCustomerTickets() {
        Object[][] rows = {
                {8001L, "TK20260527001", 1001L, 3001L, "资源", "紧急", "处理中", "林客服", "在线客服",
                        "充值后余额未及时刷新", "客户完成线下充值后企业端余额刷新延迟，需要核对资源流水与余额版本。", "ORDER-O202605270101", 25, null, 2, 3},
                {8002L, "TK20260527002", 1005L, 3006L, "生成任务", "加急", "待分配", null, "企业微信",
                        "视频任务连续失败", "民国悬疑预告项目图片生成失败，需要客服确认素材格式并转技术复核。", "TASK-IMAGE-SOUTHWIND-FAIL", null, null, -2, 6},
                {8003L, "TK20260526018", 1002L, 3003L, "功能", "普通", "已解决", "陈客服", "在线客服",
                        "团队成员无法看到项目资产", "团队版客户反馈成员权限看不到素材库，已说明 Owner 和成员权限差异。", "PROJECT-5003", 15, 1, 20, 28},
                {8004L, "TK20260526021", 1006L, 3007L, "发票", "普通", "待客户确认", "赵财务", "邮件",
                        "需要补充专票开户地址", "客户申请专业版充值发票，缺少开户地址和开户行信息，等待客户补充。", "ORDER-O202605260302", 40, null, 18, 22},
                {8005L, "TK20260525009", 1004L, 3005L, "账号", "普通", "已关闭", "周客服", "在线客服",
                        "管理员手机号换绑", "试用客户管理员手机号换绑咨询，已引导其在企业设置内完成验证。", "USER-3005", 12, 3, 24, 48},
                {8006L, "TK20260527006", 2059311712494563329L, null, "入驻审核", "紧急", "待分配", null, "运营代提",
                        "闭环验证企业需要人工复核", "入驻审核页发现 Owner 关系和证照状态需要人工确认，需分配运营处理。", "TENANT-2059311712494563329", null, null, 1, 4}
        };
        for (Object[] row : rows) {
            insertIfMissing("customer_tickets", ((Number) row[0]).longValue(), """
                    INSERT INTO customer_tickets
                    (id, ticket_no, tenant_id, requester_user_id, category, priority, status, assignee, source,
                     subject, description, related_ref, first_response_at, resolved_at, due_at, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                            CASE WHEN ? IS NULL THEN NULL ELSE DATEADD('MINUTE', ?, DATEADD('HOUR', -?, CURRENT_TIMESTAMP)) END,
                            CASE WHEN ? IS NULL THEN NULL ELSE DATEADD('HOUR', -?, CURRENT_TIMESTAMP) END,
                            DATEADD('HOUR', ?, CURRENT_TIMESTAMP),
                            DATEADD('HOUR', -?, CURRENT_TIMESTAMP),
                            CURRENT_TIMESTAMP)
                    """,
                    row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11],
                    row[12], row[12], row[15], row[13], row[13], row[14], row[15]);
        }
    }

    private void seedCustomerScripts() {
        Object[][] rows = {
                {8101L, "入驻资料补件说明", "入驻审核", "证照/法人资料缺失", "企业管理员",
                        "营业执照、法人身份证、授权书、补件",
                        "您好，当前入驻资料还缺少必要核验材料。请补充营业执照清晰图片、法人身份证正反面或经办人授权书。资料提交后运营会在 1 个工作小时内复核，审核通过后会自动开通试用资源。",
                        "涉及证照主体不一致时转运营审核；客户催办超过 30 分钟转客服主管。", "新客、风险", "林客服", "已发布", 48},
                {8102L, "积分消耗异常解释", "计费结算", "客户质疑积分扣减", "企业管理员",
                        "积分少了、扣费、消耗异常、账单不对",
                        "您好，平台积分会按实际调用的模型、输入素材与输出时长记录流水。您可以在企业后台的资源流水中按项目、模型和日期核对。若发现重复扣减，请提供项目名和任务时间，我们会提交财务与技术联合复核。",
                        "单笔差异超过 5000 积分或涉及退款时转财务值班。", "财务、售后", "陈客服", "已发布", 44},
                {8103L, "视频生成排队说明", "生成任务", "视频任务等待过久", "企业成员",
                        "排队、生成慢、一直等待、任务卡住",
                        "您好，视频生成会根据当前并发额度和模型队列依次处理。企业版/团队版支持更高并发；如果任务超过 30 分钟没有进入执行，请提供项目 ID，我们会检查任务状态并协助重试。",
                        "任务失败或等待超过 30 分钟转技术值班。", "技术、售后", "周客服", "已发布", 40},
                {8104L, "模型生成失败安抚与重试", "生成任务", "图片/视频/剧本生成失败", "企业成员",
                        "失败、报错、重试、生成不了",
                        "您好，生成失败通常与素材格式、提示词安全策略或上游模型临时波动有关。系统不会对失败任务重复扣减成功消耗积分。请先检查素材格式和提示词；如仍失败，我们可以协助发起一次任务重试。",
                        "连续失败 2 次以上转技术值班并关联任务 ID。", "技术、风险", "周客服", "已发布", 36},
                {8105L, "发票开具与退款进度说明", "订单发票", "开票/退款咨询", "企业管理员",
                        "发票、退款、付款、订单、抬头",
                        "您好，发票申请会在支付核验后进入财务处理，专票需确认纳税人识别号、注册地址和开户行信息。退款申请需要先冻结对应资源额度，再由财务在 1-3 个工作日内处理。",
                        "退款中状态超过 2 个工作日转财务值班。", "财务", "赵财务", "已发布", 32},
                {8106L, "成员邀请失败处理", "账号组织", "组织成员无法加入", "企业管理员",
                        "邀请失败、成员、邮箱、权限、加入团队",
                        "您好，请确认成员邮箱或手机号未加入其他企业，并检查当前套餐成员上限。若成员上限不足，可先移除不再使用的账号或升级套餐。我们也可以协助您核对账号归属。",
                        "跨租户账号归属问题转运营人员处理。", "账号、组织", "陈客服", "已发布", 30},
                {8107L, "套餐升级权益说明", "套餐资源", "升级/扩容咨询", "企业管理员",
                        "升级、套餐、并发、成员上限、资源包",
                        "您好，套餐升级后会同步提升成员数、项目数、月积分和并发任务上限。升级前已购买的资源余额会保留，系统会按新套餐规则继续累计消耗。",
                        "企业版报价或合同条款转商务跟进。", "高价值客户、商务", "周商务", "待审核", 18},
                {8108L, "内容安全拦截说明", "风控合规", "提示词或素材被拦截", "企业成员",
                        "敏感、违规、审核不通过、内容安全",
                        "您好，平台会对提示词、素材和生成结果进行内容安全校验。若出现拦截，请调整涉及暴力、色情、侵权、真实人物冒用等内容。若您认为误判，可提交项目 ID 由运营复核。",
                        "客户申诉误判时转内容运营复核。", "风险、内容", "内容运营", "待审核", 12},
                {8109L, "API 调用额度说明", "开放接口", "API 接入/额度咨询", "技术管理员",
                        "API、接口、密钥、额度、限流",
                        "您好，API 调用与企业后台任务共用积分余额，并受套餐并发和频控限制。请在企业后台查看 API Key、调用日志和模型消耗明细；若需要更高限流，请联系商务配置企业版额度。",
                        "接口报错附 requestId 转技术值班。", "技术、高价值客户", "技术支持", "已发布", 8}
        };
        for (Object[] row : rows) {
            insertIfMissing("customer_scripts", ((Number) row[0]).longValue(), """
                    INSERT INTO customer_scripts
                    (id, title, category, scene, target_role, trigger_keywords, reply_template, handoff_rule, tags, updated_by, status, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, DATEADD('HOUR', -?, CURRENT_TIMESTAMP), DATEADD('HOUR', -?, CURRENT_TIMESTAMP))
                    """,
                    row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[11]);
        }
    }

    private void seedContentArticles() {
        Object[][] manuals = {
                {8201L, "manual", "企业端：创建 AI 短剧项目", "创作入门", "企业侧", "v1.0", "内容运营", "手册", 326, "帮助中心文章", "企业管理员",
                        "全部租户", "说明从新建项目、导入剧本到选择画幅和清晰度的完整流程。",
                        "进入项目页创建短剧项目；上传 doc、docx、txt、pdf 或 md 剧本；选择 Agent 模式或人工模式；设置画幅、分辨率和预览风格；创建任务后在项目列表查看生成进度。", "已发布", 40},
                {8202L, "manual", "企业端：剧本生成与改写", "剧本生产", "企业侧", "v1.0", "内容运营", "手册", 248, "步骤指南", "企业成员",
                        "全部租户", "介绍剧本生短剧、人工模式、提示词安全和结果二次编辑。",
                        "支持上传原始剧本后自动拆解剧情、角色和分镜。生成前请避免真实人物冒用、侵权情节和敏感内容。结果可进入人工模式修改分镜、台词和镜头说明。", "已发布", 38},
                {8203L, "manual", "企业端：资产与素材管理", "资产管理", "企业侧", "v1.0", "产品运营", "手册", 139, "帮助中心文章", "企业成员",
                        "全部租户", "说明角色图、配音素材、品牌素材和项目资产的复用规则。",
                        "资产可按项目沉淀并复用。建议统一命名角色、场景、Logo 和配音文件。素材上传后会进入项目资产库，视频生成时可作为输入素材参与模型调用。", "已发布", 34},
                {8204L, "manual", "企业端：积分消耗与模型计费", "计费结算", "企业侧", "v1.1", "财务运营", "手册", 412, "政策说明", "企业管理员",
                        "付费企业", "解释资源流水、模型维度消耗、失败任务和发票申请。",
                        "积分按实际模型调用、素材输入和输出时长计量。可在资源流水中按项目、用户、模型、日期筛选。失败任务不会按成功结果重复计费；开票需在订单支付完成后提交发票信息。", "已发布", 30},
                {8205L, "manual", "企业端：成员与权限配置", "团队协作", "企业侧", "v1.0", "产品运营", "手册", 108, "帮助中心文章", "企业管理员",
                        "全部租户", "说明 Owner、管理员、成员的权限差异和邀请限制。",
                        "Owner 可管理套餐、资源和成员；管理员可管理项目和资产；成员主要负责创作任务。邀请成员前请确认套餐成员上限，跨企业账号需先解除原企业绑定。", "已发布", 28},
                {8206L, "manual", "运营端：入驻审核处理规范", "运营审核", "运营侧", "v0.9", "运营主管", "手册", 64, "步骤指南", "运营人员",
                        "运营人员", "规范营业执照、法人身份、授权书、风险结论和试用开通动作。",
                        "审核需检查统一社会信用代码、营业执照主体、法人姓名和经办人授权。低风险客户可开通试用包；证照不一致或授权缺失需退回补件；审核结果写入审计日志。", "待发布", 20},
                {8207L, "manual", "运营端：订单发票与退款处理", "订单计费", "运营侧", "v0.8", "财务运营", "手册", 51, "步骤指南", "运营人员",
                        "财务运营", "规范线下订单、支付核验、资源入账、发票和退款流程。",
                        "线下订单需上传支付凭证并核对企业名称。支付确认后资源入账；专票需校验税号、开户行和地址电话；退款需冻结对应资源额度并记录退款原因。", "待发布", 18},
                {8208L, "manual", "运营端：预警与客服工单联动", "客户服务", "运营侧", "v1.0", "客服主管", "手册", 97, "帮助中心文章", "运营人员",
                        "客服人员", "说明资源不足、沉睡客户、超时工单和话术库调用规则。",
                        "资源不足客户进入续费提醒；沉睡客户进入回访队列；超时工单同步预警表。客服处理时优先调用话术库，无法解决时按升级规则转交运营、财务或技术。", "已发布", 16}
        };
        for (Object[] row : manuals) {
            insertContentArticle(row);
        }

        Object[][] announcements = {
                {8301L, "announcement", "视频生成计费规则更新说明", "平台政策", "全部用户", "v1.0", "财务运营", "政策", 586, "强制确认弹窗", "全部用户",
                        "全部租户、企业管理员", "视频生成在使用视频素材作为输入时，积分计算逻辑已更新。",
                        "针对不同的视频输出时长，现设有最低积分消耗限制。企业管理员登录后需确认新计费说明，确认后方可继续发起视频生成任务。", "已发布", 24, "强制确认"},
                {8302L, "announcement", "端午节服务保障公告", "平台新闻", "全部用户", "v1.0", "客服主管", "新闻", 184, "新闻资讯卡片", "全部用户",
                        "全部租户", "端午节期间客服、模型任务和财务开票服务安排。",
                        "节日期间平台生成服务正常运行，客服响应时间可能略有延迟。紧急工单仍按 SLA 升级处理，发票与退款将在节后第一个工作日集中处理。", "定时中", -120, "普通阅读"},
                {8303L, "announcement", "企业版套餐权益调整", "套餐政策", "企业管理员", "v1.0", "计费负责人", "政策", 233, "政策说明", "企业管理员",
                        "付费企业、企业管理员", "企业版套餐成员、项目和并发任务上限调整。",
                        "企业版将增加项目容量与并发生成额度，既有客户在合同周期内保留原权益并可申请平滑升级。具体权益以资源包配置和合同约定为准。", "已发布", 60, "强制确认"}
        };
        for (Object[] row : announcements) {
            insertContentArticle(row);
        }
    }

    private void insertContentArticle(Object[] row) {
        insertIfMissing("content_articles", ((Number) row[0]).longValue(), """
                INSERT INTO content_articles
                (id, content_type, title, category, scope, version, owner, article_type, read_count, front_template,
                 front_audience, publish_targets, summary, content, status, publish_at, read_mode, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, DATEADD('HOUR', -?, CURRENT_TIMESTAMP), ?,
                        DATEADD('HOUR', -?, CURRENT_TIMESTAMP), DATEADD('HOUR', -?, CURRENT_TIMESTAMP))
                """,
                row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11],
                row[12], row[13], row[14], row[15], row.length > 16 ? row[16] : "普通阅读", Math.abs(((Number) row[15]).intValue()), Math.abs(((Number) row[15]).intValue()));
    }

    private void seedPlatformSettings() {
        Object[][] rows = {
                {8401L, "platform.warning.credit_low_percent", "资源不足预警阈值", "预警", "20", "percent", "全平台", "启用", "余额低于月度额度 20% 时进入运营预警。", "保障客户生成任务不中断"},
                {8402L, "platform.warning.credit_critical_percent", "资源严重不足阈值", "预警", "10", "percent", "全平台", "启用", "低于 10% 时升级为严重预警并通知财务值班。", "严重资源风险升级"},
                {8403L, "platform.warning.expire_remind_days", "资源到期提前提醒", "预警", "7", "days", "付费租户", "启用", "套餐或资源包到期前 7 天提醒企业管理员。", "到期续费提醒"},
                {8404L, "platform.warning.second_remind_days", "二次到期提醒", "预警", "3", "days", "付费租户", "启用", "到期前 3 天触发二次提醒并同步商务负责人。", "到期二次提醒"},
                {8405L, "platform.billing.credit_unit_price", "积分单价基准", "计费", "0.01", "currency", "全平台", "启用", "运营端线下充值、退款与报表客单价的基础换算口径。", "用于财务口径校准"},
                {8406L, "platform.gray.billing_order_v2", "新版计费订单灰度", "灰度", "企业版白名单", "text", "付费租户", "启用", "仅企业版白名单租户可看到新版订单与发票流程。", "灰度开关"},
                {8407L, "platform.gray.manual_preview", "手册前端预览模板", "灰度", "运营侧可预览", "text", "运营人员", "启用", "使用手册和政策公告发布前支持运营端预览前端模板。", "内容运营灰度"},
                {8408L, "platform.security.force_read_policy", "强制阅读确认策略", "安全", "启用", "boolean", "全部用户", "启用", "计费规则和高危政策发布后，企业管理员需确认后继续使用。", "政策合规"},
                {8409L, "platform.security.content_block_times", "内容安全拦截升级次数", "安全", "3", "number", "全平台", "启用", "同租户连续触发内容安全拦截达到 3 次后进入预警。", "内容风控"},
                {8410L, "platform.task.fail_rate_threshold", "任务失败率预警阈值", "预警", "15", "percent", "全平台", "启用", "近 24 小时任务失败率超过该值时提醒技术值班。", "模型任务健康"}
        };
        for (Object[] row : rows) {
            insertSystemSettingIfMissing(row);
        }
    }

    private void ensurePlatformSettingColumns() {
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS setting_name VARCHAR(160)");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS setting_group VARCHAR(64)");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS value_type VARCHAR(32) DEFAULT 'text'");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS scope VARCHAR(80) DEFAULT '全平台'");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS status VARCHAR(32) DEFAULT '启用'");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS effective_at TIMESTAMP");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS change_reason CLOB");
        jdbcTemplate.execute("ALTER TABLE system_settings ADD COLUMN IF NOT EXISTS description CLOB");
    }

    private void insertSystemSettingIfMissing(Object[] row) {
        Long id = ((Number) row[0]).longValue();
        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM system_settings WHERE id = ? OR setting_key = ?",
                Integer.class,
                id,
                row[1]);
        if (count != null && count > 0) {
            jdbcTemplate.update("""
                    UPDATE system_settings
                       SET setting_key = ?,
                           setting_name = ?,
                           setting_group = ?,
                           setting_value = ?,
                           value_type = ?,
                           scope = ?,
                           status = ?,
                           description = ?,
                           change_reason = ?,
                           updated_at = CURRENT_TIMESTAMP
                     WHERE id = ?
                    """,
                    row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], id);
            return;
        }
        jdbcTemplate.update("""
                INSERT INTO system_settings
                (id, setting_key, setting_name, setting_group, setting_value, value_type, scope, status,
                 description, change_reason, effective_at, updated_at, updated_by)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1)
                """, row);
    }

    private void insertIfMissing(String table, Long id, String sql, Object... args) {
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM " + table + " WHERE id = ?", Integer.class, id);
        if (count != null && count > 0) {
            return;
        }
        if (args.length == 1 && args[0] instanceof Object[] row) {
            jdbcTemplate.update(sql, row);
            return;
        }
        jdbcTemplate.update(sql, args);
    }
}
