const express = require('express');
const { requireRole } = require('../middleware/auth');

const router = express.Router();
router.use(requireRole('rd'));

function getDB(req) { return req.app.locals.db; }

// GET /api/rd/clients — active clients for dropdown
router.get('/clients', (req, res) => {
    const db = getDB(req);
    const clients = db.all("SELECT c.id, c.name, c.service_types, u.realname as sales_name FROM clients c JOIN users u ON c.sales_id = u.id WHERE c.status = 1 ORDER BY c.name");
    const result = clients.map(c => ({ ...c, service_types: JSON.parse(c.service_types || '[]') }));
    res.json({ code: 200, data: result });
});

// GET /api/rd/project-types — for dropdown in timesheet form
router.get('/project-types', (req, res) => {
    const db = getDB(req);
    res.json({ code: 200, data: db.all('SELECT id, name FROM project_types ORDER BY id') });
});

// GET /api/rd/holidays — retrieve holiday data for a specific year
router.get('/holidays', async (req, res) => {
    const db = getDB(req);
    const year = parseInt(req.query.year) || new Date().getFullYear();
    const { getHolidays } = require('../utils/holiday');
    const holidayMap = await getHolidays(db, year);
    res.json({ code: 200, data: holidayMap || {} });
});

// GET /api/rd/timesheets?date=YYYY-MM-DD or ?month=YYYY-MM
router.get('/timesheets', (req, res) => {
    const db = getDB(req);
    const { date, month } = req.query;

    if (date) {
        const records = db.all(`SELECT t.id, t.user_id, t.client_id, t.work_date, t.hours, t.remark, t.service_type, t.project_type, t.created_at, c.name as client_name 
      FROM timesheets t JOIN clients c ON t.client_id = c.id WHERE t.user_id = ? AND t.work_date = ? ORDER BY t.created_at`,
            [req.user.id, date]);
        const dayTotal = db.get('SELECT COALESCE(SUM(hours), 0) as total FROM timesheets WHERE user_id = ? AND work_date = ?', [req.user.id, date]);
        return res.json({ code: 200, data: { records, day_total: dayTotal ? dayTotal.total : 0 } });
    }

    if (month) {
        const records = db.all(`SELECT t.id, t.user_id, t.client_id, t.work_date, t.hours, t.remark, t.service_type, t.project_type, t.created_at, c.name as client_name 
      FROM timesheets t JOIN clients c ON t.client_id = c.id WHERE t.user_id = ? AND t.work_date LIKE ? ORDER BY t.work_date DESC, t.created_at`,
            [req.user.id, month + '%']);
        const monthTotal = db.get("SELECT COALESCE(SUM(hours), 0) as total FROM timesheets WHERE user_id = ? AND work_date LIKE ?", [req.user.id, month + '%']);
        return res.json({ code: 200, data: { records, month_total: monthTotal ? monthTotal.total : 0 } });
    }

    res.status(400).json({ code: 400, message: '请提供 date 或 month 参数' });
});

// POST /api/rd/timesheets
router.post('/timesheets', (req, res) => {
    const db = getDB(req);
    const { client_id, work_date, hours, remark, service_type, project_type } = req.body;
    if (!client_id || !work_date || !hours) return res.status(400).json({ code: 400, message: '客户、日期和工时不能为空' });
    if (hours <= 0 || hours > 8) return res.status(400).json({ code: 400, message: '单项工时必须在0~8小时之间' });

    // Backfill 14-days check
    const todayStr = new Date().toLocaleDateString('sv-SE', { timeZone: 'Asia/Shanghai' });
    const todayDate = new Date(todayStr + "T00:00:00+08:00");
    const minDate = new Date(todayDate);
    minDate.setDate(minDate.getDate() - 40);
    const wDate = new Date(work_date + "T00:00:00+08:00");
    
    if (wDate < minDate || wDate > todayDate) {
        return res.status(400).json({ code: 400, message: '只能填报今天及前40天内的工时' });
    }

    // Daily limit check
    const dayTotal = db.get('SELECT COALESCE(SUM(hours), 0) as total FROM timesheets WHERE user_id = ? AND work_date = ?', [req.user.id, work_date]);
    const currentDayTotal = dayTotal ? dayTotal.total : 0;
    if (currentDayTotal + hours > 8) {
        return res.status(400).json({ code: 400, message: `当日已填报 ${currentDayTotal} 小时，本次填报 ${hours} 小时将超过8小时上限` });
    }

    // Monthly limit check
    const yearMonth = work_date.substring(0, 7);
    const year = parseInt(work_date.substring(0, 4));
    const month = parseInt(work_date.substring(5, 7));
    const workdayConfig = db.get('SELECT days FROM workdays WHERE year = ? AND month = ?', [year, month]);
    if (workdayConfig) {
        const monthlyLimit = workdayConfig.days * 8;
        const monthTotal = db.get("SELECT COALESCE(SUM(hours), 0) as total FROM timesheets WHERE user_id = ? AND work_date LIKE ?", [req.user.id, yearMonth + '%']);
        const currentMonthTotal = monthTotal ? monthTotal.total : 0;
        if (currentMonthTotal + hours > monthlyLimit) {
            return res.status(400).json({ code: 400, message: `当月已填报 ${currentMonthTotal} 小时，上限 ${monthlyLimit} 小时（${workdayConfig.days}个工作日×8小时），无法继续填报` });
        }
    }

    // Duplicate check
    const existing = db.get('SELECT id FROM timesheets WHERE user_id = ? AND client_id = ? AND work_date = ? AND project_type = ?', [req.user.id, parseInt(client_id), work_date, project_type || '']);
    if (existing) return res.status(400).json({ code: 400, message: '该子项目在当日已有工时记录，请修改原有记录，而非重复提交' });

    const result = db.run('INSERT INTO timesheets (user_id, client_id, work_date, hours, remark, service_type, project_type) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [req.user.id, parseInt(client_id), work_date, hours, remark || '', service_type || '售前', project_type || '']);
    res.json({ code: 200, message: '提交成功', data: { id: result.lastInsertRowid } });
});

// PUT /api/rd/timesheets/:id
router.put('/timesheets/:id', (req, res) => {
    const db = getDB(req);
    const { id } = req.params;
    const { hours, remark, service_type, project_type } = req.body;
    const record = db.get('SELECT * FROM timesheets WHERE id = ? AND user_id = ?', [parseInt(id), req.user.id]);
    if (!record) return res.status(404).json({ code: 404, message: '记录不存在或无权操作' });

    // Cannot edit if work_date is out of 14-days range (strict enforcement)
    const todayStr = new Date().toLocaleDateString('sv-SE', { timeZone: 'Asia/Shanghai' });
    const todayDate = new Date(todayStr + "T00:00:00+08:00");
    const minDate = new Date(todayDate);
    minDate.setDate(minDate.getDate() - 40);
    const wDate = new Date(record.work_date + "T00:00:00+08:00");
    if (wDate < minDate || wDate > todayDate) {
        return res.status(400).json({ code: 400, message: '该记录已超出40天填报期限，无法修改' });
    }

    if (hours !== undefined) {
        if (hours <= 0 || hours > 8) return res.status(400).json({ code: 400, message: '工时必须在0~8小时之间' });
        const dayTotal = db.get('SELECT COALESCE(SUM(hours), 0) as total FROM timesheets WHERE user_id = ? AND work_date = ? AND id != ?', [req.user.id, record.work_date, parseInt(id)]);
        const otherDayTotal = dayTotal ? dayTotal.total : 0;
        if (otherDayTotal + hours > 8) {
            return res.status(400).json({ code: 400, message: `当日其他记录已有 ${otherDayTotal} 小时，修改为 ${hours} 小时将超过8小时上限` });
        }
        const yearMonth = record.work_date.substring(0, 7);
        const year = parseInt(record.work_date.substring(0, 4));
        const month = parseInt(record.work_date.substring(5, 7));
        const workdayConfig = db.get('SELECT days FROM workdays WHERE year = ? AND month = ?', [year, month]);
        if (workdayConfig) {
            const monthlyLimit = workdayConfig.days * 8;
            const monthTotal = db.get("SELECT COALESCE(SUM(hours), 0) as total FROM timesheets WHERE user_id = ? AND work_date LIKE ? AND id != ?", [req.user.id, yearMonth + '%', parseInt(id)]);
            const otherMonthTotal = monthTotal ? monthTotal.total : 0;
            if (otherMonthTotal + hours > monthlyLimit) {
                return res.status(400).json({ code: 400, message: `当月上限 ${monthlyLimit} 小时，修改后将超出限制` });
            }
        }
    }

    const updates = [], params = [];
    if (hours !== undefined) { updates.push('hours = ?'); params.push(hours); }
    if (remark !== undefined) { updates.push('remark = ?'); params.push(remark); }
    if (service_type !== undefined) { updates.push('service_type = ?'); params.push(service_type); }
    if (project_type !== undefined) { updates.push('project_type = ?'); params.push(project_type); }
    if (updates.length === 0) return res.status(400).json({ code: 400, message: '没有可更新的字段' });
    params.push(parseInt(id));
    db.run(`UPDATE timesheets SET ${updates.join(', ')} WHERE id = ?`, params);
    res.json({ code: 200, message: '更新成功' });
});

// DELETE /api/rd/timesheets/:id
router.delete('/timesheets/:id', (req, res) => {
    const db = getDB(req);
    const record = db.get('SELECT id FROM timesheets WHERE id = ? AND user_id = ?', [parseInt(req.params.id), req.user.id]);
    if (!record) return res.status(404).json({ code: 404, message: '记录不存在或无权操作' });
    db.run('DELETE FROM timesheets WHERE id = ?', [parseInt(req.params.id)]);
    res.json({ code: 200, message: '删除成功' });
});

// GET /api/rd/stats
router.get('/stats', (req, res) => {
    const db = getDB(req);
    const { start_date, end_date } = req.query;
    
    let tCond = '';
    const tParams = [req.user.id];
    
    if (start_date) { tCond += ' AND t.work_date >= ?'; tParams.push(start_date); }
    if (end_date) { tCond += ' AND t.work_date <= ?'; tParams.push(end_date); }

    const projects = db.all(`
    SELECT c.id as client_id, c.name as client_name, COALESCE(SUM(t.hours), 0) as total_hours,
      COUNT(DISTINCT t.work_date) as work_days, MIN(t.work_date) as first_date, MAX(t.work_date) as last_date
    FROM timesheets t JOIN clients c ON t.client_id = c.id WHERE t.user_id = ? ${tCond} GROUP BY c.id ORDER BY total_hours DESC
  `, tParams);

    const total = db.get(`SELECT COALESCE(SUM(hours), 0) as total_hours FROM timesheets t WHERE t.user_id = ? ${tCond}`, tParams);

    res.json({ code: 200, data: { projects, total_hours: total ? total.total_hours : 0 } });
});

module.exports = router;