const express = require('express');
const router = express.Router();
const db = require('../db');
const { verifyToken, isSystemAdmin } = require('../middleware/auth');

// 定义允许管理的字典表映射
const tableMap = {
    'departments': 'departments',
    'organizations': 'dict_organizations',
    'award-types': 'dict_award_types',
    'award-levels': 'dict_award_levels',
    'paper-types': 'dict_paper_types',
    'standard-types': 'dict_standard_types',
    'project-categories': 'dict_project_categories',
    'leader-departments': 'dict_leader_departments'
};

// 特殊接口：公开获取部门列表 (用于注册页面，无需登录)
router.get('/departments', async (req, res) => {
    try {
        const result = await db.query('SELECT * FROM departments ORDER BY id ASC');
        res.json(result.rows);
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 通用获取接口 (登录用户即可访问，用于下拉框)
router.get('/:type', verifyToken, async (req, res) => {
    const tableName = tableMap[req.params.type];
    if (!tableName) return res.status(404).json({ message: '未知的字典类型' });

    try {
        const result = await db.query(`SELECT * FROM ${tableName} ORDER BY id ASC`);
        res.json(result.rows);
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: '服务器错误' });
    }
});

// 通用添加接口 (系统管理员：超管或维护员)
router.post('/:type', verifyToken, isSystemAdmin, async (req, res) => {
    const tableName = tableMap[req.params.type];
    if (!tableName) return res.status(404).json({ message: '未知的字典类型' });

    const { name } = req.body;
    if (!name) return res.status(400).json({ message: '名称不能为空' });

    try {
        const result = await db.query(
            `INSERT INTO ${tableName} (name) VALUES ($1) RETURNING *`,
            [name]
        );
        res.status(201).json(result.rows[0]);
    } catch (err) {
        if (err.code === '23505') {
            return res.status(400).json({ message: '该项已存在' });
        }
        res.status(500).json({ message: '服务器错误' });
    }
});

// 通用修改接口 (系统管理员：超管或维护员)
router.put('/:type/:id', verifyToken, isSystemAdmin, async (req, res) => {
    const tableName = tableMap[req.params.type];
    if (!tableName) return res.status(404).json({ message: '未知的字典类型' });

    const { id } = req.params;
    const { name } = req.body;
    if (!name) return res.status(400).json({ message: '名称不能为空' });

    try {
        const result = await db.query(
            `UPDATE ${tableName} SET name = $1 WHERE id = $2 RETURNING *`,
            [name, id]
        );
        if (result.rows.length === 0) return res.status(404).json({ message: '记录不存在' });
        res.json(result.rows[0]);
    } catch (err) {
        if (err.code === '23505') {
            return res.status(400).json({ message: '名称已存在' });
        }
        res.status(500).json({ message: '服务器错误' });
    }
});

// 通用删除接口 (系统管理员：超管或维护员)
router.delete('/:type/:id', verifyToken, isSystemAdmin, async (req, res) => {
    const type = req.params.type;
    const tableName = tableMap[type];
    if (!tableName) return res.status(404).json({ message: '未知的字典类型' });

    const { id } = req.params;

    try {
        // 获取名称用于后续逻辑或检查
        const checkResult = await db.query(`SELECT name FROM ${tableName} WHERE id = $1`, [id]);
        if (checkResult.rows.length === 0) return res.status(404).json({ message: '记录不存在' });
        
        const name = checkResult.rows[0].name;

        // 检查是否是“待定”
        if (name === '待定') {
            return res.status(400).json({ message: '系统保留项“待定”不能删除' });
        }

        // 开启事务处理级联更新
        const client = await db.pool.connect();
        try {
            await client.query('BEGIN');

            // 1. 执行级联更新逻辑
            if (type === 'departments') {
                // 更新用户表：部门设为待定，状态设为 pending
                await client.query(
                    "UPDATE users SET department = '待定', dept_status = 'pending' WHERE department = $1",
                    [name]
                );
                // 更新成果表：替换归属部门数组中的项
                await client.query(
                    "UPDATE achievements SET assigned_departments = array_replace(assigned_departments, $1, '待定') WHERE $1 = ANY(assigned_departments)",
                    [name]
                );
            } else if (type === 'organizations') {
                await client.query("UPDATE achievement_award SET award_unit = '待定' WHERE award_unit = $1", [name]);
                await client.query("UPDATE achievement_project SET source = '待定' WHERE source = $1", [name]);
                await client.query("UPDATE achievement_monograph SET publisher = '待定' WHERE publisher = $1", [name]);
                // achievement_plan 表没有 unit 字段，跳过
                // achievement_transformation 表没有 receiver_unit 字段，跳过
                await client.query("UPDATE achievement_software SET owner_unit = '待定' WHERE owner_unit = $1", [name]); // 修正字段名 owner -> owner_unit
                await client.query("UPDATE achievement_paper SET first_unit = '待定' WHERE first_unit = $1", [name]);
            } else if (type === 'award-types') {
                await client.query("UPDATE achievement_award SET award_type = '待定' WHERE award_type = $1", [name]);
            } else if (type === 'award-levels') {
                await client.query("UPDATE achievement_award SET award_level = '待定' WHERE award_level = $1", [name]);
            } else if (type === 'paper-types') {
                await client.query("UPDATE achievement_paper SET paper_type = '待定' WHERE paper_type = $1", [name]);
            } else if (type === 'standard-types') {
                await client.query("UPDATE achievement_standard SET standard_type = '待定' WHERE standard_type = $1", [name]);
            } else if (type === 'project-categories') {
                await client.query("UPDATE achievement_project SET project_category = '待定' WHERE project_category = $1", [name]);
            } else if (type === 'leader-departments') {
                // 领导层部门字典仅用于筛选，删除时不影响其他表数据
            }

            // 2. 执行删除
            await client.query(`DELETE FROM ${tableName} WHERE id = $1`, [id]);

            await client.query('COMMIT');
            res.json({ message: '删除成功，关联数据已同步为“待定”' });
        } catch (err) {
            await client.query('ROLLBACK');
            throw err;
        } finally {
            client.release();
        }
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: '服务器错误' });
    }
});

module.exports = router;