const { Pool } = require('pg');
const bcrypt = require('bcryptjs');
const path = require('path');
const fs = require('fs');
require('dotenv').config({ path: path.join(__dirname, '../.env') });

const pool = new Pool({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE || process.env.DB_NAME, // 兼容 .env 中的不同命名
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT,
});

// 随机数据源
const DEPARTMENTS = ['研发部', '市场部', '人事部', '财务部', '销售部', '运维部'];
const SURNAMES = ['赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩', '杨'];
const NAMES = ['伟', '芳', '娜', '敏', '静', '强', '磊', '军', '洋', '勇', '艳', '杰', '娟', '涛', '明', '超'];
const TITLES_PREFIX = ['基于AI的', '高性能', '分布式', '新一代', '云原生', '跨平台', '智能', '自动化'];
const TITLES_SUFFIX = ['系统设计', '算法研究', '平台开发', '应用实践', '优化方案', '技术白皮书', '分析报告'];

// 字典数据
const DICTS = {
    dict_organizations: ['科技大学', '软件研究所', '创新中心', '数据实验室'],
    dict_paper_types: ['SCI', 'EI', '核心期刊', '会议论文'],
    dict_award_types: ['科技进步奖', '技术发明奖', '自然科学奖'],
    dict_award_levels: ['一等奖', '二等奖', '三等奖', '特等奖'],
    dict_project_categories: ['国家重点研发计划', '自然科学基金', '省部级项目', '横向课题'],
    dict_standard_types: ['国家标准', '行业标准', '团体标准', '企业标准']
};

const ACHIEVEMENT_TYPES = [
    'paper', 'project', 'award', 'standard', 'monograph', 'report', 'plan', 'patent', 'transformation', 'software'
];

const STATUSES = ['pending', 'approved', 'rejected'];

// 辅助函数
const randomInt = (min, max) => Math.floor(Math.random() * (max - min + 1)) + min;
const randomChoice = (arr) => arr[randomInt(0, arr.length - 1)];
const generatePhone = () => `1${randomChoice(['3', '5', '7', '8', '9'])}${randomInt(100000000, 999999999)}`;
const generateName = () => `${randomChoice(SURNAMES)}${randomChoice(NAMES)}${Math.random() > 0.5 ? randomChoice(NAMES) : ''}`;
const generateTitle = () => `${randomChoice(TITLES_PREFIX)}${randomChoice(TITLES_SUFFIX)}`;
const generateDate = (startYear, endYear) => {
    const year = randomInt(startYear, endYear);
    const month = randomInt(0, 11);
    const day = randomInt(1, 28);
    return new Date(year, month, day);
};

async function seed() {
    const client = await pool.connect();
    try {
        console.log('🚀 开始注入测试数据...');

        // 0. 初始化数据库架构
        console.log('🏗️ 初始化数据库架构...');
        const schemaPath = path.join(__dirname, '../../database/schema.sql');
        if (fs.existsSync(schemaPath)) {
            const schemaSql = fs.readFileSync(schemaPath, 'utf8');
            await client.query(schemaSql);
            console.log('✅ 数据库架构已应用');
        } else {
            console.warn('⚠️ 未找到 schema.sql，跳过架构初始化');
        }

        // 1. 清理数据 (保留特定账号)
        console.log('🧹 清理旧数据...');
        await client.query('BEGIN');
        
        // 级联删除所有成果 (这将自动删除详情表和附件表)
        await client.query('TRUNCATE achievements CASCADE');
        
        // 删除除保留账号外的所有用户
        const keepUsers = ['18888888888', '19999999999'];
        await client.query('DELETE FROM users WHERE username != $1 AND username != $2', [keepUsers[0], keepUsers[1]]);

        console.log('✅ 旧数据清理完成');

        // 2. 填充字典和部门
        console.log('📚 填充字典数据...');
        for (const [table, values] of Object.entries(DICTS)) {
            for (const val of values) {
                await client.query(`INSERT INTO ${table} (name) VALUES ($1) ON CONFLICT (name) DO NOTHING`, [val]);
            }
        }

        for (const dept of DEPARTMENTS) {
            await client.query(
                'INSERT INTO departments (name) VALUES ($1) ON CONFLICT (name) DO NOTHING',
                [dept]
            );
        }

        // 3. 生成新用户
        console.log('👥 生成测试用户...');
        const passwordHash = await bcrypt.hash('123456', 10);
        const userIds = [];
        
        // 获取保留用户的ID
        const existingUsers = await client.query('SELECT id FROM users WHERE username = $1 OR username = $2', [keepUsers[0], keepUsers[1]]);
        existingUsers.rows.forEach(row => userIds.push(row.id));

        // 生成 20 个新用户
        for (let i = 0; i < 20; i++) {
            const username = generatePhone();
            const realName = generateName();
            const dept = randomChoice(DEPARTMENTS);
            
            try {
                const res = await client.query(
                    'INSERT INTO users (username, password_hash, role, real_name, department, dept_status) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id',
                    [username, passwordHash, 'user', realName, dept, 'verified']
                );
                userIds.push(res.rows[0].id);
            } catch (e) {
                if (e.code !== '23505') console.error(e);
            }
        }
        console.log(`✅ 已生成 ${userIds.length} 个用户`);

        // 4. 生成成果数据
        console.log('📊 生成成果数据 (目标 500+ 条)...');
        const targetCount = 550;
        let insertedCount = 0;

        for (let i = 0; i < targetCount; i++) {
            const userId = randomChoice(userIds);
            const type = randomChoice(ACHIEVEMENT_TYPES);
            const status = randomChoice(STATUSES);
            const date = generateDate(2023, 2026);
            const title = generateTitle();
            const dept = randomChoice(DEPARTMENTS);
            
            // 构造 contributors JSONB
            const contributors = [
                { name: generateName(), phone: generatePhone(), isMain: true },
                { name: generateName(), phone: generatePhone(), isMain: false }
            ];

            // 插入主表
            const res = await client.query(
                `INSERT INTO achievements 
                (user_id, type, name, contributors, assigned_departments, achievement_date, status, remarks, created_at) 
                VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`,
                [
                    userId,
                    type,
                    title,
                    JSON.stringify(contributors),
                    [dept], // 数组格式
                    date,
                    status,
                    '自动生成的数据',
                    date
                ]
            );
            const achievementId = res.rows[0].id;

            // 插入附件
            await client.query(
                'INSERT INTO achievement_attachments (achievement_id, file_name, file_path) VALUES ($1, $2, $3)',
                [achievementId, 'test_report.pdf', 'uploads/mock_file.pdf']
            );

            // 插入详情表 (根据类型)
            switch (type) {
                case 'paper':
                    await client.query(
                        'INSERT INTO achievement_paper (achievement_id, paper_type, journal_name, publish_date) VALUES ($1, $2, $3, $4)',
                        [achievementId, randomChoice(DICTS.dict_paper_types), '计算机学报', date]
                    );
                    break;
                case 'award':
                    await client.query(
                        'INSERT INTO achievement_award (achievement_id, award_type, award_level, award_unit) VALUES ($1, $2, $3, $4)',
                        [achievementId, randomChoice(DICTS.dict_award_types), randomChoice(DICTS.dict_award_levels), '科技部']
                    );
                    break;
                case 'project':
                    await client.query(
                        'INSERT INTO achievement_project (achievement_id, project_category, source) VALUES ($1, $2, $3)',
                        [achievementId, randomChoice(DICTS.dict_project_categories), '国家自然科学基金委员会']
                    );
                    break;
                case 'standard':
                    await client.query(
                        'INSERT INTO achievement_standard (achievement_id, standard_type, standard_no, implement_date) VALUES ($1, $2, $3, $4)',
                        [achievementId, randomChoice(DICTS.dict_standard_types), `GB/T ${randomInt(1000, 9999)}-2025`, date]
                    );
                    break;
                // 其他类型暂略，或插入空记录
            }

            insertedCount++;
            if (insertedCount % 100 === 0) process.stdout.write('.');
        }
        
        await client.query('COMMIT');
        console.log(`\n✅ 成功注入 ${insertedCount} 条成果数据！`);
        console.log('🎉 数据注入完成！');

    } catch (e) {
        await client.query('ROLLBACK');
        console.error('❌ 数据注入失败:', e);
    } finally {
        client.release();
        await pool.end();
    }
}

seed();