const path = require('path');
const express = require('express');
const cors = require('cors');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const sqlite3 = require('sqlite3').verbose();

const PORT = process.env.PORT || 3000;
const JWT_SECRET = process.env.JWT_SECRET || 'CHANGE_ME_TO_A_RANDOM_SECRET';
const DB_PATH = process.env.DB_PATH || path.join(__dirname, 'data.sqlite');

const app = express();
app.use(cors());
app.use(express.json());

// ===== DB =====
const db = new sqlite3.Database(DB_PATH);

function run(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.run(sql, params, function (err) {
            if (err) return reject(err);
            resolve({ lastID: this.lastID, changes: this.changes });
        });
    });
}

function get(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.get(sql, params, (err, row) => {
            if (err) return reject(err);
            resolve(row);
        });
    });
}

function all(sql, params = []) {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
            if (err) return reject(err);
            resolve(rows);
        });
    });
}

async function initDb() {
    await run('PRAGMA foreign_keys = ON;');

    await run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      name TEXT,
      password_hash TEXT NOT NULL,
      role TEXT NOT NULL CHECK(role IN ('admin','leader','staff')),
      phone TEXT,
      email TEXT,
      status TEXT NOT NULL DEFAULT 'active',
      creator_id INTEGER,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY(creator_id) REFERENCES users(id)
    );
  `);

    await run(`
    CREATE TABLE IF NOT EXISTS clients (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      staff_id INTEGER NOT NULL,
      regDate TEXT,              -- YYYY-MM-DD
      customerName TEXT NOT NULL,
      phone TEXT,
      expireDate TEXT,           -- YYYY-MM-DD
      amount REAL NOT NULL DEFAULT 0,
      serviceType TEXT,
      remark TEXT,
      lastRenewAt TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY(staff_id) REFERENCES users(id)
    );
  `);

    const admin = await get('SELECT * FROM users WHERE username = ?', ['admin']);
    if (!admin) {
        const hash = await bcrypt.hash('admin123456', 10);
        await run(
            'INSERT INTO users(username, name, password_hash, role, status) VALUES(?,?,?,?,?)',
            ['admin', '老板', hash, 'admin', 'active']
        );
        console.log('已创建默认管理员账号：xs / xs123456...');
    }

    const staff = await get('SELECT * FROM users WHERE username = ?', ['staff1']);
    if (!staff) {
        const hash = await bcrypt.hash('staff123456', 10);
        await run(
            'INSERT INTO users(username, name, password_hash, role, status) VALUES(?,?,?,?,?)',
            ['staff1', '员工1', hash, 'staff', 'active']
        );
        console.log('已创建默认员工账号：staff1 / staff123456');
    }

    // 创建默认组长账号
    const leader = await get('SELECT * FROM users WHERE username = ?', ['leader1']);
    if (!leader) {
        const hash = await bcrypt.hash('leader123456', 10);
        await run(
            'INSERT INTO users(username, name, password_hash, role, status) VALUES(?,?,?,?,?)',
            ['leader1', '组长1', hash, 'leader', 'active']
        );
        console.log('已创建默认组长账号：leader1 / leader123456');
    }
}

function asyncHandler(fn) {
    return (req, res, next) => Promise.resolve(fn(req, res, next)).catch(next);
}

// ===== Auth middleware =====
function authRequired(req, res, next) {
    const auth = req.headers.authorization || '';
    const token = auth.startsWith('Bearer ') ? auth.slice(7) : '';
    if (!token) return res.status(401).json({ message: '未登录' });
    try {
        const payload = jwt.verify(token, JWT_SECRET);
        req.user = payload; // {id, username, role, name}
        next();
    } catch (e) {
        return res.status(401).json({ message: '登录已失效，请重新登录' });
    }
}

function roleRequired(roles) {
    return (req, res, next) => {
        if (!req.user) return res.status(401).json({ message: '未登录' });
        if (!roles.includes(req.user.role)) return res.status(403).json({ message: '无权限' });
        next();
    };
}

// ===== date helpers =====
function parseDateValue(value) {
    if (!value) return null;
    const d = new Date(value);
    if (Number.isNaN(d.getTime())) return null;
    return d;
}
function isExpired(expireDate) {
    const d = parseDateValue(expireDate);
    if (!d) return false;
    return d.getTime() < Date.now();
}
function isExpiringSoon(expireDate, days = 7) {
    const d = parseDateValue(expireDate);
    if (!d) return false;
    const diff = d.getTime() - Date.now();
    return diff >= 0 && diff <= days * 24 * 60 * 60 * 1000;
}

// ===== Common: build client status (for response) =====
function getClientStatus(expireDate, days = 7) {
    if (isExpired(expireDate)) return 'expired';
    if (isExpiringSoon(expireDate, days)) return 'expiring';
    return 'active';
}

// ===== API: Auth =====
app.post('/api/auth/login', asyncHandler(async (req, res) => {
    const { username, password } = req.body || {};
    if (!username || !password) return res.status(400).json({ message: '缺少账号或密码' });

    const user = await get('SELECT * FROM users WHERE username = ?', [username]);
    if (!user) return res.status(401).json({ message: '账号或密码错误' });

    if (user.status === 'inactive') return res.status(403).json({ message: '该账号已被禁用，请联系管理员' });

    const ok = await bcrypt.compare(password, user.password_hash);
    if (!ok) return res.status(401).json({ message: '账号或密码错误' });

    const token = jwt.sign(
        { id: user.id, username: user.username, role: user.role, name: user.name },
        JWT_SECRET,
        { expiresIn: '7d' }
    );

    res.json({
        token,
        user: { id: user.id, username: user.username, name: user.name, role: user.role }
    });
}));

app.get('/api/auth/me', authRequired, asyncHandler(async (req, res) => {
    const u = await get(
        'SELECT id, username, name, role, phone, email, status FROM users WHERE id=?',
        [req.user.id]
    );
    res.json({ user: u });
}));

// ======================================================================
// ✅ 新增：Dashboard Summary（总览卡片 + 员工统计）
// - admin：返回全局 stats + staffStats 列表
// - staff：返回自己的 stats（不返回全部员工）
// ======================================================================
app.get('/api/dashboard/summary', authRequired, asyncHandler(async (req, res) => {
    const days = Number(req.query.days || 7);

    // 取所有客户（admin） or 取自己的客户（staff）
    let rows;
    if (req.user.role === 'admin') {
        rows = await all(`SELECT staff_id, expireDate, amount FROM clients`);
    } else {
        rows = await all(`SELECT staff_id, expireDate, amount FROM clients WHERE staff_id = ?`, [req.user.id]);
    }

    let totalAmount = 0;
    let totalClients = rows.length;
    let expiringCount = 0;
    let expiredCount = 0;

    for (const r of rows) {
        totalAmount += Number(r.amount || 0);
        if (isExpired(r.expireDate)) expiredCount += 1;
        else if (isExpiringSoon(r.expireDate, days)) expiringCount += 1;
    }

    const stats = { totalAmount, totalClients, expiringCount, expiredCount };

    if (req.user.role !== 'admin') {
        return res.json({ stats });
    }

    // admin：补员工统计
    const staffRows = await all(`
    SELECT u.id, u.username, u.name, u.phone, u.email, u.status, u.created_at, u.creator_id, c.name as creator_name, c.username as creator_username
    FROM users u
    LEFT JOIN users c ON u.creator_id = c.id
    WHERE u.role='staff'
    ORDER BY u.id ASC
  `);

    const map = new Map();
    staffRows.forEach(s => {
        map.set(s.id, {
            id: s.id,
            username: s.username,
            name: s.name,
            phone: s.phone,
            email: s.email,
            status: s.status,
            created_at: s.created_at,
            creator_id: s.creator_id,
            creator_name: s.creator_name || s.creator_username || 'Admin',
            clients: 0,
            total: 0,
            expired: 0,
            expiring: 0
        });
    });

    rows.forEach(c => {
        const t = map.get(c.staff_id);
        if (!t) return;
        t.clients += 1;
        t.total += Number(c.amount || 0);
        if (isExpired(c.expireDate)) t.expired += 1;
        else if (isExpiringSoon(c.expireDate, days)) t.expiring += 1;
    });

    res.json({ stats, staffStats: Array.from(map.values()) });
}));

// ======================================================================
// ✅ 新增：Staff 通用接口（更贴近前端）
// - GET /api/staff      admin:员工列表（含聚合） leader:员工列表（不含聚合） staff:返回自己
// - POST /api/staff     admin/leader:新增员工
// - PUT /api/staff/:id  admin/leader:编辑员工
// - DELETE /api/staff/:id admin/leader:禁用员工
// ======================================================================
app.get('/api/staff', authRequired, asyncHandler(async (req, res) => {
    // staff 只能看自己
    if (req.user.role === 'staff') {
        const me = await get(`
            SELECT id, username, name, phone, email, status, created_at
            FROM users
            WHERE id=? AND status != 'inactive'
        `, [req.user.id]);

        if (!me) {
            return res.json({ staff: [] });
        }

        return res.json({ staff: [me] });
    }

    // admin 和 leader 都可以查看员工列表
    // admin 可以看到所有 staff 和 leader，leader 只能看自己创建的 staff
    const includeInactive = req.query.includeInactive === 'true';
    const statusFilter = includeInactive ? '' : "AND u.status != 'inactive'";

    let staffRows;
    if (req.user.role === 'admin') {
        staffRows = await all(`
            SELECT u.id, u.username, u.name, u.phone, u.email, u.status, u.role, u.creator_id, u.created_at
            FROM users u
            WHERE u.role IN ('staff', 'leader') ${statusFilter}
            ORDER BY u.status ASC, u.id ASC
        `);
    } else {
        // leader 只能看自己创建的 staff
        staffRows = await all(`
            SELECT u.id, u.username, u.name, u.phone, u.email, u.status, u.role, u.creator_id, u.created_at
            FROM users u
            WHERE u.role = 'staff' AND u.creator_id = ? ${statusFilter}
            ORDER BY u.status ASC, u.id ASC
        `, [req.user.id]);
    }

    // leader 也返回员工的客户统计数据
    if (req.user.role === 'leader') {
        const staffIds = staffRows.map(s => s.id);
        if (staffIds.length === 0) {
            return res.json({ staff: [] });
        }
        const clientRows = await all(`SELECT staff_id, expireDate, amount FROM clients WHERE staff_id IN (${staffIds.join(',')})`);
        const byStaff = new Map();
        staffRows.forEach((s) => {
            byStaff.set(s.id, {
                ...s,
                clientsCount: 0,
                totalAmount: 0,
                expiredCount: 0,
                expiringCount: 0
            });
        });
        const now = new Date();
        const expireThreshold = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);
        clientRows.forEach((c) => {
            const staff = byStaff.get(c.staff_id);
            if (!staff) return;
            staff.clientsCount++;
            staff.totalAmount += c.amount || 0;
            if (c.expireDate) {
                const exp = new Date(c.expireDate);
                if (exp < now) staff.expiredCount++;
                else if (exp <= expireThreshold) staff.expiringCount++;
            }
        });
        return res.json({ staff: Array.from(byStaff.values()) });
    }

    // admin 返回完整统计数据
    const clientRows = await all('SELECT staff_id, expireDate, amount FROM clients');
    const byStaff = new Map();
    staffRows.forEach((s) => {
        byStaff.set(s.id, {
            ...s,
            clientsCount: 0,
            totalAmount: 0,
            expiredCount: 0,
            expiringCount: 0
        });
    });

    clientRows.forEach((c) => {
        const target = byStaff.get(c.staff_id);
        if (!target) return;
        target.clientsCount += 1;
        target.totalAmount += Number(c.amount || 0);
        if (isExpired(c.expireDate)) target.expiredCount += 1;
        if (isExpiringSoon(c.expireDate)) target.expiringCount += 1;
    });

    res.json({ staff: Array.from(byStaff.values()) });
}));


app.post('/api/staff', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
    const b = req.body || {};
    if (!b.username || !b.password || !b.name) {
        return res.status(400).json({ message: '用户名、姓名和密码不能为空' });
    }
    const existing = await get('SELECT id FROM users WHERE username = ?', [b.username]);
    if (existing) return res.status(400).json({ message: '用户名已存在' });

    // 只有 admin 可以创建 leader，leader 只能创建 staff
    let role = 'staff';
    if (b.role === 'leader' && req.user.role === 'admin') {
        role = 'leader';
    }

    // 记录创建者 ID
    const creatorId = req.user.id;

    const hash = await bcrypt.hash(b.password, 10);
    const r = await run(
        'INSERT INTO users(username, name, password_hash, role, phone, email, status, creator_id) VALUES(?,?,?,?,?,?,?,?)',
        [b.username, b.name, hash, role, b.phone || null, b.email || null, b.status || 'active', creatorId]
    );
    const row = await get('SELECT id, username, name, phone, email, status, role, creator_id, created_at FROM users WHERE id=?', [r.lastID]);
    res.json({ staff: row });
}));

app.put('/api/staff/:id', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const existing = await get('SELECT * FROM users WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '用户不存在' });

    // 权限检查
    if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
    if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
    // 组长只能操作自己创建的员工
    if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
        return res.status(403).json({ message: '无权操作此员工' });
    }

    const b = req.body || {};
    const updates = [];
    const params = [];
    const fields = ['name', 'phone', 'email', 'username']; // 允许修改用户名

    for (const f of fields) {
        if (Object.prototype.hasOwnProperty.call(b, f)) {
            // 如果修改用户名，检查唯一性
            if (f === 'username' && b.username !== existing.username) {
                const uCheck = await get('SELECT id FROM users WHERE username = ?', [b.username]);
                if (uCheck) return res.status(400).json({ message: '用户名已存在' });
            }
            updates.push(`${f} = ?`);
            params.push(b[f]);
        }
    }

    // 只有 admin 可以修改角色
    if (req.user.role === 'admin' && b.role && ['leader', 'staff'].includes(b.role)) {
        updates.push('role = ?');
        params.push(b.role);
    }

    // 只有 admin 可以修改所属组长 (creator_id)
    // 传入 id 为 leader id，如果为 'admin' 或空则设为当前 admin id
    if (req.user.role === 'admin' && Object.prototype.hasOwnProperty.call(b, 'creator_id')) {
        let newCreatorId = Number(b.creator_id);
        if (!newCreatorId) newCreatorId = req.user.id; // 默认为当前操作者（admin）

        // 验证一下是否存在且是 leader/admin
        if (newCreatorId !== req.user.id) {
            const leaderCheck = await get('SELECT id, role FROM users WHERE id=?', [newCreatorId]);
            if (!leaderCheck) return res.status(400).json({ message: '指定的组长不存在' });
        }

        updates.push('creator_id = ?');
        params.push(newCreatorId);
    }

    if (b.password) {
        const hash = await bcrypt.hash(b.password, 10);
        updates.push('password_hash = ?');
        params.push(hash);
    }

    if (updates.length > 0) {
        params.push(id);
        await run(`UPDATE users SET ${updates.join(', ')} WHERE id = ?`, params);
    }

    const row = await get('SELECT id, username, name, phone, email, status, role, creator_id, created_at FROM users WHERE id=?', [id]);
    res.json({ staff: row });
}));

app.post('/api/staff/:id/reset_password', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const b = req.body || {};
    if (!b.password) return res.status(400).json({ message: '新密码不能为空' });

    const existing = await get('SELECT id, role, creator_id FROM users WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '用户不存在' });
    if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
    if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
    // 组长只能操作自己创建的员工
    if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
        return res.status(403).json({ message: '无权操作此员工' });
    }

    const hash = await bcrypt.hash(b.password, 10);
    await run('UPDATE users SET password_hash = ? WHERE id = ?', [hash, id]);
    res.json({ success: true });
}));

app.delete('/api/staff/:id', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const existing = await get('SELECT * FROM users WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '用户不存在' });
    if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
    if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
    // 组长只能操作自己创建的员工
    if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
        return res.status(403).json({ message: '无权操作此员工' });
    }

    await run('UPDATE users SET status = \'inactive\' WHERE id = ?', [id]);
    res.json({ success: true });
}));

// 启用/禁用用户状态切换
app.post('/api/staff/:id/toggle_status', authRequired, roleRequired(['admin', 'leader']), asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const existing = await get('SELECT * FROM users WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '用户不存在' });
    if (existing.role === 'admin') return res.status(403).json({ message: '不能操作管理员账号' });
    if (existing.role === 'leader' && req.user.role !== 'admin') return res.status(403).json({ message: '无权操作组长' });
    // 组长只能操作自己创建的员工
    if (req.user.role === 'leader' && existing.creator_id !== req.user.id) {
        return res.status(403).json({ message: '无权操作此员工' });
    }

    const newStatus = existing.status === 'active' ? 'inactive' : 'active';
    await run('UPDATE users SET status = ? WHERE id = ?', [newStatus, id]);
    res.json({ success: true, status: newStatus });
}));

// ======================================================================
// ✅ 改造：Clients 列表支持筛选/搜索/分页（你前端筛选栏 & 客户页需要）
// GET /api/clients
// query:
//   staffId / staffName(可选) / status(active|expiring|expired) / serviceType / q
//   dateFrom / dateTo (按 regDate) / page / pageSize / days(默认7,expiring阈值)
// ======================================================================
app.get('/api/clients', authRequired, asyncHandler(async (req, res) => {
    const {
        staffId,
        status = 'all',
        serviceType = 'all',
        q = '',
        dateFrom,
        dateTo,
        page = 1,
        pageSize = 20,
        days = 7
    } = req.query;

    const p = Math.max(1, Number(page || 1));
    const ps = Math.min(200, Math.max(1, Number(pageSize || 20)));
    const expDays = Number(days || 7);

    const where = [];
    const params = [];

    // 权限限制
    if (req.user.role === 'staff') {
        // 员工只能看自己的客户
        where.push('c.staff_id = ?');
        params.push(req.user.id);
    } else if (req.user.role === 'leader') {
        // 组长只能看自己创建的员工的客户
        if (staffId && staffId !== 'all') {
            // 验证该员工是否属于此组长
            const staffUser = await get('SELECT id, creator_id FROM users WHERE id = ?', [Number(staffId)]);
            if (!staffUser || staffUser.creator_id !== req.user.id) {
                return res.status(403).json({ message: '无权查看此员工客户' });
            }
            where.push('c.staff_id = ?');
            params.push(Number(staffId));
        } else {
            // 获取此组长创建的所有员工 ID
            const ownStaff = await all('SELECT id FROM users WHERE creator_id = ? AND role = ?', [req.user.id, 'staff']);
            const staffIds = ownStaff.map(s => s.id);
            if (staffIds.length === 0) {
                return res.json({ clients: [], pagination: { page: 1, pageSize: ps, total: 0, totalPages: 0 } });
            }
            where.push(`c.staff_id IN (${staffIds.join(',')})`);
        }
    } else if (staffId && staffId !== 'all') {
        // admin 可以看任何员工
        where.push('c.staff_id = ?');
        params.push(Number(staffId));
    }

    if (serviceType && serviceType !== 'all') {
        where.push('c.serviceType = ?');
        params.push(serviceType);
    }

    if (q && String(q).trim()) {
        const like = `%${String(q).trim()}%`;
        where.push('(c.customerName LIKE ? OR c.phone LIKE ? OR c.remark LIKE ?)');
        params.push(like, like, like);
    }

    // regDate 范围筛选
    if (dateFrom) {
        where.push('date(c.regDate) >= date(?)');
        params.push(dateFrom);
    }
    if (dateTo) {
        where.push('date(c.regDate) <= date(?)');
        params.push(dateTo);
    }

    // 状态筛选（expireDate）
    // 注意：expireDate 允许为空，空则默认当 active
    if (status === 'expired') {
        where.push("c.expireDate IS NOT NULL AND date(c.expireDate) < date('now')");
    } else if (status === 'expiring') {
        where.push(`c.expireDate IS NOT NULL AND date(c.expireDate) >= date('now') AND date(c.expireDate) <= date('now', ?)`);
        params.push(`+${expDays} day`);
    } else if (status === 'active') {
        // active: expireDate 为空 或 expireDate > now+days 或 expireDate 在未来但>days
        // 简化：not expired and not expiring
        where.push(`(
      c.expireDate IS NULL
      OR date(c.expireDate) > date('now', ?)
    )`);
        params.push(`+${expDays} day`);
    }

    const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';

    // 总数
    const totalRow = await get(`SELECT COUNT(1) AS cnt FROM clients c ${whereSql}`, params);
    const total = totalRow ? totalRow.cnt : 0;

    // 分页数据
    const offset = (p - 1) * ps;
    const list = await all(
        `
      SELECT c.*, u.name AS staff_name, u.username AS staff_username
      FROM clients c
      LEFT JOIN users u ON u.id = c.staff_id
      ${whereSql}
      ORDER BY c.id DESC
      LIMIT ? OFFSET ?
    `,
        [...params, ps, offset]
    );

    // 补一个计算出来的 status 字段，前端更好用
    const clients = list.map(row => ({
        ...row,
        status: getClientStatus(row.expireDate, expDays)
    }));

    res.json({
        page: p,
        pageSize: ps,
        total,
        clients
    });
}));

// ======================================================================
// Staff clients endpoints (for staff page)
// ======================================================================
app.get('/api/staff/clients', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
    const days = Number(req.query.days || 7);
    const rows = await all(
        `SELECT c.*
         FROM clients c
         WHERE c.staff_id = ?
         ORDER BY c.id DESC`,
        [req.user.id]
    );
    const clients = rows.map(row => ({ ...row, status: getClientStatus(row.expireDate, days) }));
    res.json({ clients });
}));

app.post('/api/staff/clients', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
    const b = req.body || {};
    if (!b.customerName) return res.status(400).json({ message: '客户姓名不能为空' });

    const amount = Number(b.amount || 0);
    const now = new Date().toISOString();

    const r = await run(`
    INSERT INTO clients(staff_id, regDate, customerName, phone, expireDate, amount, serviceType, remark, lastRenewAt, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?)
  `, [
        req.user.id,
        b.regDate || null,
        b.customerName,
        b.phone || null,
        b.expireDate || null,
        amount,
        b.serviceType || null,
        b.remark || '',
        b.lastRenewAt || null,
        now,
        now
    ]);

    const row = await get('SELECT * FROM clients WHERE id=?', [r.lastID]);
    res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
}));

app.put('/api/staff/clients/:id', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const existing = await get('SELECT * FROM clients WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '客户不存在' });

    if (req.user.role !== 'admin' && existing.staff_id !== req.user.id) {
        return res.status(403).json({ message: '无权限修改该客户' });
    }

    const b = req.body || {};
    const fields = ['regDate', 'customerName', 'phone', 'expireDate', 'amount', 'serviceType', 'remark', 'lastRenewAt'];
    const updates = [];
    const params = [];

    for (const f of fields) {
        if (!Object.prototype.hasOwnProperty.call(b, f)) continue;
        updates.push(`${f} = ?`);
        if (f === 'amount') params.push(Number(b[f] || 0));
        else params.push(b[f]);
    }

    if (updates.length === 0) {
        const row = await get('SELECT * FROM clients WHERE id=?', [id]);
        return res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
    }

    updates.push('updated_at = ?');
    params.push(new Date().toISOString());
    params.push(id);

    await run(`UPDATE clients SET ${updates.join(', ')} WHERE id = ?`, params);

    const row = await get('SELECT * FROM clients WHERE id=?', [id]);
    res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
}));

// New client
app.post('/api/clients', authRequired, roleRequired(['staff', 'admin']), asyncHandler(async (req, res) => {
    const b = req.body || {};
    const staffId = (req.user.role === 'admin' && b.staff_id) ? Number(b.staff_id) : req.user.id;

    if (!b.customerName) return res.status(400).json({ message: '客户姓名不能为空' });

    const amount = Number(b.amount || 0);
    const now = new Date().toISOString();

    const r = await run(`
    INSERT INTO clients(staff_id, regDate, customerName, phone, expireDate, amount, serviceType, remark, lastRenewAt, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?)
  `, [
        staffId,
        b.regDate || null,
        b.customerName,
        b.phone || null,
        b.expireDate || null,
        amount,
        b.serviceType || null,
        b.remark || '',
        b.lastRenewAt || null,
        now,
        now
    ]);

    const row = await get('SELECT * FROM clients WHERE id=?', [r.lastID]);
    res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
}));

// 更新客户
app.put('/api/clients/:id', authRequired, asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const existing = await get('SELECT * FROM clients WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '客户不存在' });

    if (req.user.role !== 'admin' && existing.staff_id !== req.user.id) {
        return res.status(403).json({ message: '无权限修改该客户' });
    }

    const b = req.body || {};
    const fields = ['regDate', 'customerName', 'phone', 'expireDate', 'amount', 'serviceType', 'remark', 'lastRenewAt', 'staff_id'];
    const updates = [];
    const params = [];

    for (const f of fields) {
        if (!Object.prototype.hasOwnProperty.call(b, f)) continue;

        // staff_id 只有 admin 能改
        if (f === 'staff_id' && req.user.role !== 'admin') continue;

        updates.push(`${f} = ?`);
        if (f === 'amount') params.push(Number(b[f] || 0));
        else if (f === 'staff_id') params.push(Number(b[f]));
        else params.push(b[f]);
    }

    if (updates.length === 0) return res.json({ client: { ...existing, status: getClientStatus(existing.expireDate) } });

    updates.push('updated_at = ?');
    params.push(new Date().toISOString());
    params.push(id);

    await run(`UPDATE clients SET ${updates.join(', ')} WHERE id = ?`, params);

    const row = await get('SELECT * FROM clients WHERE id=?', [id]);
    res.json({ client: { ...row, status: getClientStatus(row.expireDate) } });
}));

// ✅ 新增：删除客户（单个）
app.delete('/api/clients/:id', authRequired, asyncHandler(async (req, res) => {
    const id = Number(req.params.id);
    if (!id) return res.status(400).json({ message: '参数错误' });

    const existing = await get('SELECT * FROM clients WHERE id=?', [id]);
    if (!existing) return res.status(404).json({ message: '客户不存在' });

    if (req.user.role !== 'admin' && existing.staff_id !== req.user.id) {
        return res.status(403).json({ message: '无权限删除该客户' });
    }

    await run('DELETE FROM clients WHERE id=?', [id]);
    res.json({ success: true });
}));

// ======================================================================
// ✅ 新增：到期提醒列表（你左侧“到期提醒”页会用到）
// GET /api/reminders?type=expiring|expired&days=7&staffId=xxx
// ======================================================================
app.get('/api/reminders', authRequired, asyncHandler(async (req, res) => {
    const type = req.query.type || 'expiring';
    const days = Number(req.query.days || 7);
    const staffId = req.query.staffId;

    const where = [];
    const params = [];

    if (req.user.role !== 'admin') {
        where.push('c.staff_id = ?');
        params.push(req.user.id);
    } else if (staffId && staffId !== 'all') {
        where.push('c.staff_id = ?');
        params.push(Number(staffId));
    }

    if (type === 'expired') {
        where.push("c.expireDate IS NOT NULL AND date(c.expireDate) < date('now')");
    } else {
        where.push(`c.expireDate IS NOT NULL AND date(c.expireDate) >= date('now') AND date(c.expireDate) <= date('now', ?)`);
        params.push(`+${days} day`);
    }

    const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';
    const rows = await all(`
    SELECT c.*, u.name AS staff_name, u.username AS staff_username
    FROM clients c
    LEFT JOIN users u ON u.id = c.staff_id
    ${whereSql}
    ORDER BY date(c.expireDate) ASC, c.id DESC
  `, params);

    res.json({
        type,
        days,
        clients: rows.map(r => ({ ...r, status: getClientStatus(r.expireDate, days) }))
    });
}));

// ======================================================================
// ✅ 新增：导出客户 CSV（简单实用，Excel 也能打开）
// GET /api/export/clients?...（同 /api/clients 的筛选参数）
// ======================================================================
app.get('/api/export/clients', authRequired, asyncHandler(async (req, res) => {
    // 复用 /api/clients 的筛选逻辑，但这里直接查全部，不分页
    const {
        staffId,
        status = 'all',
        serviceType = 'all',
        q = '',
        dateFrom,
        dateTo,
        days = 7
    } = req.query;

    const expDays = Number(days || 7);

    const where = [];
    const params = [];

    if (req.user.role !== 'admin') {
        where.push('c.staff_id = ?');
        params.push(req.user.id);
    } else if (staffId && staffId !== 'all') {
        where.push('c.staff_id = ?');
        params.push(Number(staffId));
    }

    if (serviceType && serviceType !== 'all') {
        where.push('c.serviceType = ?');
        params.push(serviceType);
    }

    if (q && String(q).trim()) {
        const like = `%${String(q).trim()}%`;
        where.push('(c.customerName LIKE ? OR c.phone LIKE ? OR c.remark LIKE ?)');
        params.push(like, like, like);
    }

    if (dateFrom) {
        where.push('date(c.regDate) >= date(?)');
        params.push(dateFrom);
    }
    if (dateTo) {
        where.push('date(c.regDate) <= date(?)');
        params.push(dateTo);
    }

    if (status === 'expired') {
        where.push("c.expireDate IS NOT NULL AND date(c.expireDate) < date('now')");
    } else if (status === 'expiring') {
        where.push(`c.expireDate IS NOT NULL AND date(c.expireDate) >= date('now') AND date(c.expireDate) <= date('now', ?)`);
        params.push(`+${expDays} day`);
    } else if (status === 'active') {
        where.push(`(
      c.expireDate IS NULL
      OR date(c.expireDate) > date('now', ?)
    )`);
        params.push(`+${expDays} day`);
    }

    const whereSql = where.length ? `WHERE ${where.join(' AND ')}` : '';

    const rows = await all(`
    SELECT c.*, u.name AS staff_name, u.username AS staff_username
    FROM clients c
    LEFT JOIN users u ON u.id = c.staff_id
    ${whereSql}
    ORDER BY c.id DESC
  `, params);

    // CSV
    const headers = [
        'id', 'staff_name', 'staff_username', 'customerName', 'phone', 'serviceType', 'regDate', 'expireDate', 'amount', 'status', 'remark'
    ];
    const escapeCsv = (v) => {
        if (v === null || v === undefined) return '';
        const s = String(v);
        if (/[",\n]/.test(s)) return `"${s.replace(/"/g, '""')}"`;
        return s;
    };

    const lines = [];
    lines.push(headers.join(','));
    for (const r of rows) {
        const statusText = getClientStatus(r.expireDate, expDays);
        const line = [
            r.id,
            r.staff_name || '',
            r.staff_username || '',
            r.customerName || '',
            r.phone || '',
            r.serviceType || '',
            r.regDate || '',
            r.expireDate || '',
            r.amount ?? 0,
            statusText,
            r.remark || ''
        ].map(escapeCsv).join(',');
        lines.push(line);
    }

    const csv = '\ufeff' + lines.join('\n'); // 带 BOM，Excel 打开中文不乱码
    res.setHeader('Content-Type', 'text/csv; charset=utf-8');
    res.setHeader('Content-Disposition', `attachment; filename="clients_${Date.now()}.csv"`);
    res.send(csv);
}));

// ======================================================================
// 兼容保留：你原来的 admin 接口（不想改前端也能用）
// ======================================================================
app.get('/api/admin/staff', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
    // 直接代理到 /api/staff
    req.user.role = 'admin';
    return app._router.handle(req, res, () => { }, 'get', '/api/staff');
}));

app.post('/api/admin/staff', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
    return app._router.handle(req, res, () => { }, 'post', '/api/staff');
}));

app.put('/api/admin/staff/:id', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
    return app._router.handle(req, res, () => { }, 'put', `/api/staff/${req.params.id}`);
}));

app.delete('/api/admin/staff/:id', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
    return app._router.handle(req, res, () => { }, 'delete', `/api/staff/${req.params.id}`);
}));

app.get('/api/admin/stats', authRequired, roleRequired(['admin']), asyncHandler(async (req, res) => {
    // 兼容：用新 summary 的 stats
    const fakeReq = { ...req, query: { ...req.query } };
    // 直接再算一次（避免 hack router）
    const rows = await all('SELECT expireDate, amount FROM clients');
    let totalAmount = 0;
    let totalClients = rows.length;
    let activeClients = 0;
    let expiringSoonClients = 0;
    for (const r of rows) {
        totalAmount += Number(r.amount || 0);
        if (!isExpired(r.expireDate)) activeClients += 1;
        if (isExpiringSoon(r.expireDate)) expiringSoonClients += 1;
    }
    res.json({ stats: { totalAmount, totalClients, activeClients, expiringSoonClients } });
}));

// ===== 静态前端文件 =====
const publicDir = path.join(__dirname, 'public');
app.use(express.static(publicDir));
app.get('/', (req, res) => res.redirect('/index.html'));

// ===== error handler =====
app.use((err, req, res, next) => {
    console.error(err);
    res.status(500).json({ message: '服务器错误' });
});

async function main() {
    if (process.argv.includes('--initdb')) {
        await initDb();
        process.exit(0);
    }
    await initDb();
    app.listen(PORT, () => {
        console.log(`CRM running on http://0.0.0.0:${PORT}`);
    });
}

main().catch(err => {
    console.error(err);
    process.exit(1);
});