scheduler.js

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 { getPool } from './db.js'
import { buildSqlWithDateRange } from './condition-builder.js'
import { sendReportMail } 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')

// 运行中的cron任务 { scriptId: cronTask }
const runningTasks = new Map()

// 读取脚本列表
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')
}

// ====== 动态日期变量处理 ======
function resolveDynamicValues(conditions) {
  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'),
  }

  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
  })
}

// ====== 执行单个脚本任务 ======
export async function executeScriptTask(script) {
  console.log(`[定时任务] 开始执行: ${script.name} (${new Date().toLocaleString()})`)

  try {
    // 校验SQL必须为SELECT语句
    const sqlCheck = validateSelectOnly(script.sql)
    if (!sqlCheck.allowed) {
      updateScriptStatus(script.id, 'failed', sqlCheck.reason)
      console.error(`[定时任务] ${script.name}: SQL校验失败 - ${sqlCheck.reason}`)
      return
    }

    // 1. 解析动态条件
    const resolvedConditions = resolveDynamicValues(script.conditions || [])

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

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

    if (!Array.isArray(rows) || rows.length === 0) {
      updateScriptStatus(script.id, 'success', '查询结果为空，未发送邮件')
      return
    }

    // 4. 如果配置了收件人，生成Excel并发邮件
    if (script.recipientEmails && script.recipientEmails.trim()) {
      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 html = generateEmailHtml(script, rows.length, dayjs().format('YYYY-MM-DD HH:mm:ss'))

      await sendReportMail({
        to: script.recipientEmails,
        subject: script.emailSubject || `[SQL报表] ${script.name} - ${dayjs().format('YYYY-MM-DD')}`,
        html,
        excelBuffer,
        fileName,
      })

      updateScriptStatus(script.id, 'success', `成功发送，共${rows.length}条数据`)
    } else {
      updateScriptStatus(script.id, 'success', `执行成功，${rows.length}条数据（未配置收件人，未发送邮件）`)
    }

    console.log(`[定时任务] ${script.name}: 执行成功`)
  } catch (e) {
    updateScriptStatus(script.id, 'failed', e.message)
    console.error(`[定时任务] ${script.name}: 执行失败 - ${e.message}`)
  }
}

// 生成邮件正文
function generateEmailHtml(script, rowCount, execTime) {
  return `
    <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;">${rowCount} 条</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>
  `
}

// 更新脚本执行状态
function updateScriptStatus(scriptId, status, message) {
  const scripts = loadScripts()
  const idx = scripts.findIndex(s => s.id === scriptId)
  if (idx >= 0) {
    scripts[idx].lastRunAt = new Date().toISOString()
    scripts[idx].lastRunStatus = status
    scripts[idx].lastRunMessage = message || ''
    saveScripts(scripts)
  }
}

// ====== 启动/停止脚本定时任务 ======

export function startScriptTask(script) {
  if (runningTasks.has(script.id)) {
    runningTasks.get(script.id).stop()
  }

  if (!script.schedulerEnabled || !script.cronExpression) return

  if (!cron.validate(script.cronExpression)) {
    console.error(`[定时任务] ${script.name}: cron表达式无效 - ${script.cronExpression}`)
    return
  }

  const cronTask = cron.schedule(script.cronExpression, () => {
    // 每次执行时重新读取最新脚本配置（确保条件值是最新的）
    const scripts = loadScripts()
    const latest = scripts.find(s => s.id === script.id)
    if (latest && latest.schedulerEnabled) {
      executeScriptTask(latest)
    }
  }, { scheduled: true })

  runningTasks.set(script.id, cronTask)
  console.log(`[定时任务] 已启动: ${script.name} (${script.cronExpression})`)
}

export function stopScriptTask(scriptId) {
  const cronTask = runningTasks.get(scriptId)
  if (cronTask) {
    cronTask.stop()
    runningTasks.delete(scriptId)
    console.log(`[定时任务] 已停止: ${scriptId}`)
  }
}

// 服务启动时，加载所有启用定时的脚本
export function reloadAllScriptTasks() {
  // 先清理所有运行中的任务
  for (const [id, task] of runningTasks) {
    task.stop()
  }
  runningTasks.clear()

  const scripts = loadScripts()
  const enabled = scripts.filter(s => s.schedulerEnabled && s.cronExpression)
  for (const script of enabled) {
    startScriptTask(script)
  }
  console.log(`[定时任务] 已加载 ${enabled.length} 个脚本定时任务`)
}

export function getRunningScriptIds() {
  return Array.from(runningTasks.keys())
}