sql-manage.js

import { Router } from 'express'
import cron from 'node-cron'
import fs from 'fs'
import path from 'path'
import { fileURLToPath } from 'url'
import dayjs from 'dayjs'
import xlsx from 'xlsx'
import { startScriptTask, stopScriptTask, reloadAllScriptTasks, executeScriptTask } from '../scheduler.js'
import { getPool } from '../db.js'
import { buildSqlWithDateRange } from '../condition-builder.js'
import { sendReportMail, loadSmtpConfig } from '../mail.js'
import { validateSelectOnly } from '../sql-analyzer.js'

const __dirname = path.dirname(fileURLToPath(import.meta.url))
const SCRIPTS_FILE = path.join(__dirname, '..', 'sql-scripts.json')

const router = Router()

// 读取脚本列表
function loadScripts() {
  try {
    if (fs.existsSync(SCRIPTS_FILE)) {
      return JSON.parse(fs.readFileSync(SCRIPTS_FILE, 'utf-8'))
    }
  } catch (e) {
    console.error('读取脚本配置失败:', e.message)
  }
  return []
}

// 保存脚本列表
function saveScripts(scripts) {
  fs.writeFileSync(SCRIPTS_FILE, JSON.stringify(scripts, null, 2), 'utf-8')
}

// 获取所有脚本
router.get('/', (req, res) => {
  const scripts = loadScripts()
  res.json({ success: true, data: scripts })
})

// 获取单个脚本
router.get('/:id', (req, res) => {
  const scripts = loadScripts()
  const script = scripts.find(s => s.id === req.params.id)
  if (!script) return res.json({ success: false, message: '脚本不存在' })
  res.json({ success: true, data: script })
})

// 新增脚本
router.post('/', (req, res) => {
  try {
    const {
      name, purpose, usedBy, sql, conditions, connId, category,
      // 邮件配置（脚本维度）
      recipientEmails, emailSubject,
      // 定时配置（脚本维度）
      cronExpression, schedulerEnabled,
    } = req.body
    if (!name || !sql) {
      return res.json({ success: false, message: '脚本名称和SQL内容不能为空' })
    }

    // 校验SQL必须为SELECT语句
    const sqlCheck = validateSelectOnly(sql)
    if (!sqlCheck.allowed) {
      return res.json({ success: false, message: sqlCheck.reason })
    }

    const scripts = loadScripts()
    const id = `script_${Date.now()}`
    const now = new Date().toISOString()
    const newScript = {
      id,
      name,
      purpose: purpose || '',
      usedBy: usedBy || '',
      sql,
      conditions: conditions || [],
      connId: connId || '',
      category: category || '',
      // 邮件配置
      recipientEmails: recipientEmails || '',
      emailSubject: emailSubject || '',
      // 定时配置
      cronExpression: cronExpression || '',
      schedulerEnabled: schedulerEnabled || false,
      // 执行状态
      lastRunAt: null,
      lastRunStatus: null,
      lastRunMessage: '',
      createdAt: now,
      updatedAt: now,
    }
    scripts.push(newScript)
    saveScripts(scripts)

    // 如果启用了定时，启动cron
    if (newScript.schedulerEnabled && newScript.cronExpression) {
      startScriptTask(newScript)
    }

    res.json({ success: true, data: { id } })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

// 更新脚本
router.put('/:id', (req, res) => {
  try {
    const { id } = req.params
    const {
      name, purpose, usedBy, sql, conditions, connId, category,
      recipientEmails, emailSubject,
      cronExpression, schedulerEnabled,
    } = req.body
    const scripts = loadScripts()
    const idx = scripts.findIndex(s => s.id === id)
    if (idx < 0) {
      return res.json({ success: false, message: '脚本不存在' })
    }

    // 校验SQL必须为SELECT语句（仅当sql被更新时校验）
    const newSql = sql !== undefined ? sql : scripts[idx].sql
    if (newSql) {
      const sqlCheck = validateSelectOnly(newSql)
      if (!sqlCheck.allowed) {
        return res.json({ success: false, message: sqlCheck.reason })
      }
    }

    // 先停止旧的定时任务
    stopScriptTask(id)

    const update = (field, val) => val !== undefined ? val : scripts[idx][field]

    scripts[idx] = {
      ...scripts[idx],
      name: update('name', name),
      purpose: update('purpose', purpose),
      usedBy: update('usedBy', usedBy),
      sql: update('sql', sql),
      conditions: update('conditions', conditions),
      connId: update('connId', connId),
      category: update('category', category),
      recipientEmails: update('recipientEmails', recipientEmails),
      emailSubject: update('emailSubject', emailSubject),
      cronExpression: update('cronExpression', cronExpression),
      schedulerEnabled: update('schedulerEnabled', schedulerEnabled),
      updatedAt: new Date().toISOString(),
    }
    saveScripts(scripts)

    // 如果启用了定时，启动新的cron
    if (scripts[idx].schedulerEnabled && scripts[idx].cronExpression) {
      startScriptTask(scripts[idx])
    }

    res.json({ success: true })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

// 删除脚本
router.delete('/:id', (req, res) => {
  try {
    const { id } = req.params
    stopScriptTask(id)
    const scripts = loadScripts()
    const idx = scripts.findIndex(s => s.id === id)
    if (idx >= 0) scripts.splice(idx, 1)
    saveScripts(scripts)
    res.json({ success: true })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

// 手动触发脚本执行（执行SQL + 发邮件）
router.post('/:id/run', async (req, res) => {
  try {
    const scripts = loadScripts()
    const script = scripts.find(s => s.id === req.params.id)
    if (!script) return res.json({ success: false, message: '脚本不存在' })

    // 校验SQL必须为SELECT语句
    const sqlCheck = validateSelectOnly(script.sql)
    if (!sqlCheck.allowed) {
      return res.json({ success: false, message: sqlCheck.reason })
    }

    executeScriptTask(script)
    res.json({ success: true, message: '任务已触发执行' })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

// 手动发送邮件（可指定收件人，支持群发/逐个发送）
router.post('/:id/send-mail', async (req, res) => {
  try {
    const { recipientEmails, emailSubject, sendMode } = req.body
    const scripts = loadScripts()
    const script = scripts.find(s => s.id === req.params.id)
    if (!script) return res.json({ success: false, message: '脚本不存在' })

    const finalRecipients = (recipientEmails || '').trim()
    if (!finalRecipients) {
      return res.json({ success: false, message: '请指定收件人邮箱' })
    }

    // 校验SQL必须为SELECT语句
    const sqlCheck = validateSelectOnly(script.sql)
    if (!sqlCheck.allowed) {
      return res.json({ success: false, message: sqlCheck.reason })
    }

    // 验证SMTP配置
    const smtpConfig = loadSmtpConfig()
    if (!smtpConfig) {
      return res.json({ success: false, message: '未配置SMTP邮件服务，请先在"邮件配置"中设置' })
    }

    // 解析动态条件
    const today = dayjs()
    const dynamicMap = {
      'TODAY': () => today.format('YYYY-MM-DD'),
      'YESTERDAY': () => today.subtract(1, 'day').format('YYYY-MM-DD'),
      'THIS_MONTH_START': () => today.startOf('month').format('YYYY-MM-DD'),
      'THIS_MONTH_END': () => today.endOf('month').format('YYYY-MM-DD'),
      'LAST_MONTH_START': () => today.subtract(1, 'month').startOf('month').format('YYYY-MM-DD'),
      'LAST_MONTH_END': () => today.subtract(1, 'month').endOf('month').format('YYYY-MM-DD'),
      'THIS_YEAR': () => today.format('YYYY'),
      'THIS_YEAR_START': () => today.startOf('year').format('YYYY-MM-DD'),
      'LAST_YEAR': () => today.subtract(1, 'year').format('YYYY'),
    }
    const resolveDynamicValues = (conditions) => {
      return conditions.map(cond => {
        const newCond = { ...cond }
        if (newCond.value && typeof newCond.value === 'string' && dynamicMap[newCond.value.toUpperCase()]) {
          newCond.value = dynamicMap[newCond.value.toUpperCase()]()
        }
        if (newCond.valueStart && typeof newCond.valueStart === 'string' && dynamicMap[newCond.valueStart.toUpperCase()]) {
          newCond.valueStart = dynamicMap[newCond.valueStart.toUpperCase()]()
        }
        if (newCond.valueEnd && typeof newCond.valueEnd === 'string' && dynamicMap[newCond.valueEnd.toUpperCase()]) {
          newCond.valueEnd = dynamicMap[newCond.valueEnd.toUpperCase()]()
        }
        return newCond
      })
    }

    const resolvedConditions = resolveDynamicValues(script.conditions || [])

    // 构建最终SQL
    let sql = script.sql
    if (resolvedConditions.length > 0) {
      sql = buildSqlWithDateRange(script.sql, resolvedConditions)
    }

    // 执行SQL
    const pool = getPool(script.connId)
    const [rows] = await pool.query(sql)

    if (!Array.isArray(rows) || rows.length === 0) {
      return res.json({ success: false, message: '查询结果为空，未发送邮件' })
    }

    // 生成Excel
    const columns = Object.keys(rows[0])
    const wb = xlsx.utils.book_new()
    const wsData = [columns]
    for (const row of rows) {
      const rowData = columns.map(col => {
        const val = row[col]
        if (Buffer.isBuffer(val)) return val.toString('hex')
        if (val instanceof Date) return val.toISOString().replace('T', ' ').replace(/\.\d+Z$/, '')
        return val
      })
      wsData.push(rowData)
    }
    const ws = xlsx.utils.aoa_to_sheet(wsData)
    ws['!cols'] = columns.map(col => ({ wch: Math.max(col.length * 2, 12) }))
    xlsx.utils.book_append_sheet(wb, ws, '查询结果')
    const excelBuffer = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' })

    const fileName = `${script.name}_${dayjs().format('YYYYMMDD_HHmmss')}.xlsx`
    const finalSubject = (emailSubject || '').trim() || script.emailSubject || `[SQL报表] ${script.name} - ${dayjs().format('YYYY-MM-DD')}`
    const execTime = dayjs().format('YYYY-MM-DD HH:mm:ss')
    const html = `
      <div style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; max-width: 600px; margin: 0 auto;">
        <div style="background: #001529; color: #fff; padding: 20px; border-radius: 8px 8px 0 0;">
          <h2 style="margin: 0; font-size: 18px;">📊 ${script.name}</h2>
        </div>
        <div style="background: #fff; padding: 20px; border: 1px solid #e8e8e8; border-top: none;">
          <table style="width: 100%; border-collapse: collapse;">
            <tr><td style="padding: 8px 0; color: #666; width: 100px;">报表名称：</td><td style="padding: 8px 0; font-weight: 600;">${script.name}</td></tr>
            <tr><td style="padding: 8px 0; color: #666;">执行时间：</td><td style="padding: 8px 0;">${execTime}</td></tr>
            <tr><td style="padding: 8px 0; color: #666;">数据行数：</td><td style="padding: 8px 0; font-weight: 600; color: #1890ff;">${rows.length} 条</td></tr>
            ${script.purpose ? `<tr><td style="padding: 8px 0; color: #666;">用途说明：</td><td style="padding: 8px 0;">${script.purpose}</td></tr>` : ''}
          </table>
          <div style="margin-top: 16px; padding: 12px; background: #f6ffed; border: 1px solid #b7eb8f; border-radius: 4px; color: #389e0d; font-size: 13px;">
            ✅ 附件为Excel报表数据，请查收。
          </div>
        </div>
        <div style="background: #fafafa; padding: 12px 20px; border-radius: 0 0 8px 8px; font-size: 12px; color: #999; text-align: center;">
          本邮件由SQL报表工具手动发送，请勿直接回复
        </div>
      </div>
    `

    // 逐个发送 vs 群发
    const recipientList = finalRecipients.split(',').map(e => e.trim()).filter(e => e)
    const isIndividual = (sendMode || 'batch') === 'individual'

    if (isIndividual && recipientList.length > 1) {
      // 逐个发送：每人一封单独邮件
      let successCount = 0
      let failList = []
      for (const rcpt of recipientList) {
        try {
          await sendReportMail({
            to: rcpt,
            subject: finalSubject,
            html,
            excelBuffer,
            fileName,
          })
          successCount++
        } catch (mailErr) {
          failList.push(rcpt)
          console.error(`发送邮件到 ${rcpt} 失败:`, mailErr.message)
        }
      }

      // 更新脚本状态
      const allScripts = loadScripts()
      const idx = allScripts.findIndex(s => s.id === script.id)
      if (idx >= 0) {
        allScripts[idx].lastRunAt = new Date().toISOString()
        allScripts[idx].lastRunStatus = failList.length === 0 ? 'success' : (successCount > 0 ? 'partial' : 'failed')
        allScripts[idx].lastRunMessage = failList.length === 0
          ? `逐个发送邮件成功，共${rows.length}条数据，${successCount}人已收到`
          : `逐个发送完成，成功${successCount}人，失败${failList.length}人：${failList.join(', ')}`
        saveScripts(allScripts)
      }

      if (failList.length === 0) {
        res.json({ success: true, message: `逐个发送邮件成功，共${rows.length}条数据，${successCount}人已收到` })
      } else {
        res.json({ success: true, message: `逐个发送完成，成功${successCount}人，失败${failList.length}人：${failList.join(', ')}` })
      }
    } else {
      // 群发：一封邮件发送给所有人
      await sendReportMail({
        to: finalRecipients,
        subject: finalSubject,
        html,
        excelBuffer,
        fileName,
      })

      // 更新脚本状态
      const allScripts = loadScripts()
      const idx = allScripts.findIndex(s => s.id === script.id)
      if (idx >= 0) {
        allScripts[idx].lastRunAt = new Date().toISOString()
        allScripts[idx].lastRunStatus = 'success'
        allScripts[idx].lastRunMessage = `群发邮件成功，共${rows.length}条数据，收件人：${finalRecipients}`
        saveScripts(allScripts)
      }

      res.json({ success: true, message: `群发邮件成功，共${rows.length}条数据，收件人：${finalRecipients}` })
    }
  } catch (e) {
    // 更新脚本状态为失败
    const allScripts = loadScripts()
    const idx = allScripts.findIndex(s => s.id === req.params.id)
    if (idx >= 0) {
      allScripts[idx].lastRunAt = new Date().toISOString()
      allScripts[idx].lastRunStatus = 'failed'
      allScripts[idx].lastRunMessage = `手动发送邮件失败：${e.message}`
      saveScripts(allScripts)
    }
    res.json({ success: false, message: e.message })
  }
})

// 服务启动时加载所有定时任务
reloadAllScriptTasks()

export default router