const { Pool } = require('pg');
const bcrypt = require('bcryptjs');
const path = require('path');
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,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT,
});

const USER_COUNT = 20000;
const ACHIEVEMENT_COUNT = 500000;
const BATCH_SIZE = 1000;
const RUN_CLEAN = process.argv.includes('--clean');

const dictTables = [
    'dict_organizations',
    'dict_award_types',
    'dict_award_levels',
    'dict_paper_types',
    'dict_standard_types',
    'dict_project_categories',
];

const achievementTypes = [
    'paper',
    'award',
    'project',
    'standard',
    'monograph',
    'report',
    'plan',
    'patent',
    'transformation',
    'software',
];
const USERNAME_PREFIX = '176';
const SEED_REMARK = 'seed_500k';
const STATUSES = ['pending', 'approved', 'rejected'];
const PATENT_TYPES = ['invention', 'utility', 'design'];
const REPORT_TYPES = ['technical', 'inspection', 'analysis', 'feasibility'];
const TRANS_METHODS = ['transfer', 'license', 'cooperation', 'self-use'];
const ACQUISITION_METHODS = ['independent', 'co-development', 'transfer'];
const SOFTWARE_SCOPES = ['national', 'industry', 'department'];

const randomInt = (min, max) => Math.floor(Math.random() * (max - min + 1)) + min;
const randomChoice = (arr) => arr[randomInt(0, arr.length - 1)];
const pad = (num, size) => String(num).padStart(size, '0');
const randomDateInYears = (years) =>
    new Date(randomChoice(years), randomInt(0, 11), randomInt(1, 28));

function buildInsert(table, columns, rows) {
    const values = [];
    const params = [];
    let paramIndex = 1;

    for (const row of rows) {
        const placeholders = [];
        for (const value of row) {
            params.push(value);
            placeholders.push(`$${paramIndex++}`);
        }
        values.push(`(${placeholders.join(', ')})`);
    }

    const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES ${values.join(', ')}`;
    return { sql, params };
}

async function fetchNames(client, table) {
    const res = await client.query(`SELECT name FROM ${table} ORDER BY id`);
    return res.rows.map((row) => row.name);
}

async function seedUsers(client, departments) {
    const passwordHash = await bcrypt.hash('123456', 10);
    let created = 0;

    for (let offset = 0; offset < USER_COUNT; offset += BATCH_SIZE) {
        const batchSize = Math.min(BATCH_SIZE, USER_COUNT - offset);
        const rows = [];

        for (let i = 0; i < batchSize; i++) {
            const index = offset + i;
            const username = `${USERNAME_PREFIX}${pad(index, 8)}`; // 11 digits, deterministic
            const realName = `Perf User ${index + 1}`;
            const department = randomChoice(departments);
            rows.push([username, passwordHash, 'user', realName, department, 'verified']);
        }

        const { sql, params } = buildInsert(
            'users',
            ['username', 'password_hash', 'role', 'real_name', 'department', 'dept_status'],
            rows
        );

        await client.query(
            `${sql} ON CONFLICT (username) DO UPDATE SET
                password_hash = EXCLUDED.password_hash,
                role = EXCLUDED.role,
                real_name = EXCLUDED.real_name,
                department = EXCLUDED.department,
                dept_status = EXCLUDED.dept_status`
        , params);

        created += batchSize;
        if (created % 5000 === 0) {
            console.log(`[seed-data-500k] users upserted: ${created}`);
        }
    }
}

async function seedAchievements(client, departments, dicts, userIds) {
    let inserted = 0;
    const dateYears = [2023, 2024, 2026];

    for (let offset = 0; offset < ACHIEVEMENT_COUNT; offset += BATCH_SIZE) {
        const batchSize = Math.min(BATCH_SIZE, ACHIEVEMENT_COUNT - offset);
        await client.query('BEGIN');
        try {
            const rows = [];
            const achievementDates = [];

            for (let i = 0; i < batchSize; i++) {
                const index = offset + i;
                const userId = randomChoice(userIds);
                const type = randomChoice(achievementTypes);
                const name = `Perf Achievement ${index + 1}`;
                const assignedDept = randomChoice(departments);
                const date = randomDateInYears(dateYears);
                achievementDates.push(date);
                const contributors = [
                    { name: `Contributor ${index + 1}A`, phone: `130${pad(index % 100000000, 8)}`, isMain: true },
                    { name: `Contributor ${index + 1}B`, phone: `131${pad(index % 100000000, 8)}`, isMain: false },
                ];
                rows.push([
                    userId,
                    type,
                    name,
                    JSON.stringify(contributors),
                    [assignedDept],
                    date,
                    randomChoice(STATUSES),
                    SEED_REMARK,
                    date,
                ]);
            }

            const { sql, params } = buildInsert(
                'achievements',
                [
                    'user_id',
                    'type',
                    'name',
                    'contributors',
                    'assigned_departments',
                    'achievement_date',
                    'status',
                    'remarks',
                    'created_at',
                ],
                rows
            );

            const res = await client.query(`${sql} RETURNING id, type`, params);

            const attachmentsRows = [];
            const paperRows = [];
            const awardRows = [];
            const projectRows = [];
            const standardRows = [];
            const monographRows = [];
            const reportRows = [];
            const planRows = [];
            const patentRows = [];
            const transformationRows = [];
            const softwareRows = [];

            for (let i = 0; i < res.rows.length; i++) {
                const { id, type } = res.rows[i];
                const date = achievementDates[i];
                const idx = offset + i + 1;
                attachmentsRows.push([id, `attachment_${idx}.pdf`, 'uploads/mock_file.pdf']);

                if (type === 'paper') {
                    paperRows.push([id, randomChoice(dicts.dict_paper_types), 'Perf Journal', randomDateInYears(dateYears)]);
                } else if (type === 'award') {
                    awardRows.push([
                        id,
                        randomChoice(dicts.dict_award_types),
                        randomChoice(dicts.dict_award_levels),
                        randomChoice(dicts.dict_organizations),
                    ]);
                } else if (type === 'project') {
                    projectRows.push([id, randomChoice(dicts.dict_project_categories), 'Perf Project Source']);
                } else if (type === 'standard') {
                    const standardYear = date.getFullYear();
                    standardRows.push([
                        id,
                        randomChoice(dicts.dict_standard_types),
                        `STD-${standardYear}-${pad(idx, 6)}`,
                        date,
                    ]);
                } else if (type === 'monograph') {
                    monographRows.push([id, 'Perf Press', `ISBN-${pad(idx, 9)}`]);
                } else if (type === 'report') {
                    reportRows.push([id, randomChoice(REPORT_TYPES), 'Recipient Unit', null, `Approver ${idx}`]);
                } else if (type === 'plan') {
                    planRows.push([id]);
                } else if (type === 'patent') {
                    patentRows.push([id, `PAT-${pad(idx, 8)}`, randomChoice(PATENT_TYPES), 'Perf Assignee']);
                } else if (type === 'transformation') {
                    transformationRows.push([
                        id,
                        randomChoice(TRANS_METHODS),
                        randomInt(50, 5000) * 1000 + randomInt(0, 99) / 100,
                        randomChoice(dicts.dict_project_categories),
                    ]);
                } else if (type === 'software') {
                    softwareRows.push([
                        id,
                        `SW-${pad(idx, 8)}`,
                        randomChoice(ACQUISITION_METHODS),
                        randomChoice(SOFTWARE_SCOPES),
                        'Perf Owner Unit',
                    ]);
                }
            }

            if (attachmentsRows.length) {
                const insert = buildInsert(
                    'achievement_attachments',
                    ['achievement_id', 'file_name', 'file_path'],
                    attachmentsRows
                );
                await client.query(insert.sql, insert.params);
            }

            if (paperRows.length) {
                const insert = buildInsert(
                    'achievement_paper',
                    ['achievement_id', 'paper_type', 'journal_name', 'publish_date'],
                    paperRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (awardRows.length) {
                const insert = buildInsert(
                    'achievement_award',
                    ['achievement_id', 'award_type', 'award_level', 'award_unit'],
                    awardRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (projectRows.length) {
                const insert = buildInsert(
                    'achievement_project',
                    ['achievement_id', 'project_category', 'source'],
                    projectRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (standardRows.length) {
                const insert = buildInsert(
                    'achievement_standard',
                    ['achievement_id', 'standard_type', 'standard_no', 'implement_date'],
                    standardRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (monographRows.length) {
                const insert = buildInsert(
                    'achievement_monograph',
                    ['achievement_id', 'publisher', 'isbn'],
                    monographRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (reportRows.length) {
                const insert = buildInsert(
                    'achievement_report',
                    ['achievement_id', 'report_type', 'recipient', 'approver_id', 'approver_name'],
                    reportRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (planRows.length) {
                const insert = buildInsert(
                    'achievement_plan',
                    ['achievement_id'],
                    planRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (patentRows.length) {
                const insert = buildInsert(
                    'achievement_patent',
                    ['achievement_id', 'patent_no', 'patent_type', 'assignee'],
                    patentRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (transformationRows.length) {
                const insert = buildInsert(
                    'achievement_transformation',
                    ['achievement_id', 'trans_method', 'trans_amount', 'project_category'],
                    transformationRows
                );
                await client.query(insert.sql, insert.params);
            }
            if (softwareRows.length) {
                const insert = buildInsert(
                    'achievement_software',
                    ['achievement_id', 'reg_no', 'acquisition_method', 'scope', 'owner_unit'],
                    softwareRows
                );
                await client.query(insert.sql, insert.params);
            }

            await client.query('COMMIT');
        } catch (err) {
            await client.query('ROLLBACK');
            throw err;
        }
        inserted += batchSize;
        if (inserted % 10000 === 0) {
                console.log(`[seed-data-500k] achievements inserted: ${inserted}`);
        }
    }
}

async function cleanSeedData(client) {
    console.log('[seed-data-500k] cleaning existing seeded data...');
    await client.query('BEGIN');
    try {
        const usersRes = await client.query(
            'SELECT id FROM users WHERE username LIKE $1',
            [`${USERNAME_PREFIX}%`]
        );
        const userIds = usersRes.rows.map((row) => row.id);

        await client.query(
            'DELETE FROM achievements WHERE remarks = $1 OR user_id = ANY($2)',
            [SEED_REMARK, userIds.length ? userIds : [0]]
        );

        await client.query(
            'DELETE FROM users WHERE username LIKE $1',
            [`${USERNAME_PREFIX}%`]
        );

        await client.query('COMMIT');
        console.log('[seed-data-500k] clean done');
    } catch (err) {
        await client.query('ROLLBACK');
        throw err;
    }
}

async function main() {
    const client = await pool.connect();
    try {
        console.log('[seed-data-500k] start');

        if (RUN_CLEAN) {
            await cleanSeedData(client);
        }

        const departments = await fetchNames(client, 'departments');
        if (departments.length === 0) {
            throw new Error('No departments found. Run "npm run init-db" first.');
        }

        const dicts = {};
        for (const table of dictTables) {
            const values = await fetchNames(client, table);
            if (values.length === 0) {
                throw new Error(`No data in ${table}. Run "npm run init-db" first.`);
            }
            dicts[table] = values;
        }

        await client.query('BEGIN');
        await seedUsers(client, departments);
        await client.query('COMMIT');

        const userIdsRes = await client.query(
            'SELECT id FROM users WHERE username LIKE $1 ORDER BY id',
            [`${USERNAME_PREFIX}%`]
        );
        const userIds = userIdsRes.rows.map((row) => row.id);
        if (userIds.length === 0) {
            throw new Error('No seeded users found.');
        }

        await seedAchievements(client, departments, dicts, userIds);

        console.log('[seed-data-500k] done');
    } catch (err) {
        await client.query('ROLLBACK');
        console.error('[seed-data-500k] failed:', err.message || err);
        process.exitCode = 1;
    } finally {
        client.release();
        await pool.end();
    }
}

main();