import { supabase } from './supabase.js'
import { getAllDecisions, setAllDecisions, clearAllDecisions } from './dataCache.js'
import { writeRawDecisions, clearAllStores, readAllRawDecisions } from './idb.js'
import { filterRowsToNyseTradingDays, countNonTradingDaysBetweenForAsset, countTradingDaysBetweenForAsset } from './marketCalendar.js'
import { computeBuyHoldEquity, computeStrategyEquity, calculateMetricsFromSeries, computeWinRate } from './perf.js'
import { STRATEGIES } from './strategies.js'

/**
 * 全局数据服务
 * 负责从 Supabase 加载数据、缓存管理和数据计算
 */
class DataService {
  constructor() {
    this.loading = false
    this.loaded = false
    this.agents = []
    this.tableRows = []
    this.lastUpdated = null
    this.dateBounds = { min: null, max: null }
    this.listeners = new Set()
    
    // Filter options cache
    this.useDbFilterCache = this._getDefaultUseDbFilterCache()
    this.nameOptions = []
    this.assetOptions = []
    this.modelOptions = []
    this.strategyOptions = []
  }

  _getDefaultUseDbFilterCache() {
    try {
      if (localStorage.getItem('disableDbFilterCache') === '1') return false
    } catch (_) {}
    const envFlag = ((import.meta.env.VITE_USE_DB_FILTER_CACHE || '') + '').toLowerCase()
    return envFlag === '1' || envFlag === 'true' || envFlag === 'yes' || envFlag === 'on'
  }

  /**
   * 订阅数据变化
   */
  subscribe(callback) {
    this.listeners.add(callback)
    return () => this.listeners.delete(callback)
  }

  /**
   * 通知所有订阅者
   */
  _notify() {
    this.listeners.forEach(callback => {
      try {
        callback({
          loading: this.loading,
          loaded: this.loaded,
          agents: this.agents,
          tableRows: this.tableRows,
          lastUpdated: this.lastUpdated,
          dateBounds: this.dateBounds,
          nameOptions: this.nameOptions,
          assetOptions: this.assetOptions,
          modelOptions: this.modelOptions,
          strategyOptions: this.strategyOptions
        })
      } catch (e) {
        console.error('[DataService] Error notifying listener:', e)
      }
    })
  }

  /**
   * 从数据库加载筛选选项
   */
  async loadFilterOptionsFromDb() {
    if (!this.useDbFilterCache) return null

    try {
      const { data, error } = await supabase
        .from('filter_options')
        .select('names, assets, models, strategies, updated_at')
        .eq('id', 'latest')
        .maybeSingle()

      if (error || !data) {
        const msg = (error && error.message) ? String(error.message).toLowerCase() : ''
        const code = (error && error.code) ? String(error.code) : ''
        const isNotFound = msg.includes('not found') || msg.includes('does not exist') || code === '42P01' || code === 'PGRST116'
        
        if (isNotFound) {
          this.useDbFilterCache = false
          try { localStorage.setItem('disableDbFilterCache', '1') } catch (_) {}
        }
        return null
      }

      return {
        names: data.names || [],
        assets: data.assets || [],
        models: data.models || [],
        strategies: data.strategies || []
      }
    } catch (e) {
      const msg = e && e.message ? String(e.message).toLowerCase() : ''
      if (msg.includes('not found') || msg.includes('does not exist')) {
        this.useDbFilterCache = false
        try { localStorage.setItem('disableDbFilterCache', '1') } catch (_) {}
      }
      return null
    }
  }

  /**
   * 保存筛选选项到数据库
   */
  async saveFilterOptionsToDb(names, assets, models, strategies) {
    if (!this.useDbFilterCache) return

    try {
      await supabase
        .from('filter_options')
        .upsert({ 
          id: 'latest', 
          names, 
          assets, 
          models, 
          strategies, 
          updated_at: new Date().toISOString() 
        }, { onConflict: 'id' })
    } catch (e) {
      const msg = e && e.message ? String(e.message).toLowerCase() : ''
      if (msg.includes('not found') || msg.includes('does not exist')) {
        this.useDbFilterCache = false
        try { localStorage.setItem('disableDbFilterCache', '1') } catch (_) {}
      }
    }
  }

  /**
   * 从 Supabase 拉取所有数据
   */
  async _fetchAllFromRemote() {
    // First, get the max date from DB to know what we're aiming for
    let dbMaxDate = null
    let dbMinDate = null
    try {
      const [minDateResult, maxDateResult] = await Promise.all([
        supabase
          .from('trading_decisions')
          .select('date')
          .order('date', { ascending: true })
          .limit(1),
        supabase
          .from('trading_decisions')
          .select('date')
          .order('date', { ascending: false })
          .limit(1)
      ])
      
      if (!minDateResult.error && minDateResult.data && minDateResult.data.length > 0) {
        dbMinDate = minDateResult.data[0].date
      }
      if (!maxDateResult.error && maxDateResult.data && maxDateResult.data.length > 0) {
        dbMaxDate = maxDateResult.data[0].date
      }
      console.log(`[DataService] DB date range: min = ${dbMinDate}, max = ${dbMaxDate}`)
    } catch (e) {
      console.error('[DataService] Error getting DB date range:', e)
    }

    const all = []
    
    // Fetch all data using pagination (faster than date range query)
    const pageSize = 1000
    let from = 0
    let pageCount = 0
    
    while (true) {
      const to = from + pageSize - 1
      pageCount++
      console.log(`[DataService] Fetching page ${pageCount}: range(${from}, ${to})`)
      
      const { data, error } = await supabase
        .from('trading_decisions')
        .select('id, agent_name, asset, model, date, price, recommended_action, news_count, sentiment, created_at, updated_at')
        .order('date', { ascending: true })
        .range(from, to)

      if (error) {
        console.error('[DataService] Error fetching data:', error)
        break
      }

      const pageData = data || []
      if (pageData.length === 0) {
        console.log(`[DataService] Page ${pageCount}: 0 rows, stopping`)
        break
      }

      all.push(...pageData)
      
      // Track the last date in this page
      const pageDates = pageData.map(r => r && r.date).filter(Boolean).sort()
      if (pageDates.length > 0) {
        const lastFetchedDate = pageDates[pageDates.length - 1]
        console.log(`[DataService] Page ${pageCount}: ${pageData.length} rows, date range: ${pageDates[0]} to ${lastFetchedDate}`)
      }
      
      // If we got less than pageSize, we're done (no more data)
      if (pageData.length < pageSize) {
        console.log(`[DataService] Last page reached (got ${pageData.length} rows, expected ${pageSize})`)
        break
      }

      from += pageSize
    }

    // Log the final date range and verify - if missing data, fetch it
    if (all.length > 0) {
      const dates = all.map(r => r && r.date).filter(Boolean).sort()
      const fetchedMinDate = dates[0]
      const fetchedMaxDate = dates[dates.length - 1]
      console.log(`[DataService] Total fetched: ${all.length} rows, date range: ${fetchedMinDate} to ${fetchedMaxDate}`)
      console.log(`[DataService] Last 10 dates:`, dates.slice(-10))
      
      // Verify we got the max date - if not, fetch missing data
      if (dbMaxDate) {
        const fetchedMaxDateStr = typeof fetchedMaxDate === 'string' ? fetchedMaxDate.split('T')[0] : fetchedMaxDate
        const dbMaxDateStr = typeof dbMaxDate === 'string' ? dbMaxDate.split('T')[0] : dbMaxDate
        console.log(`[DataService] Verification: DB max date = ${dbMaxDateStr}, Fetched max date = ${fetchedMaxDateStr}`)
        
        if (fetchedMaxDateStr !== dbMaxDateStr && fetchedMaxDateStr < dbMaxDateStr) {
          console.warn(`[DataService] Missing data detected! DB has ${dbMaxDateStr} but we fetched up to ${fetchedMaxDateStr}`)
          console.log(`[DataService] Fetching missing data from ${fetchedMaxDateStr} to ${dbMaxDateStr}...`)
          
          // Fetch missing data by date range
          const nextDay = new Date(fetchedMaxDateStr + 'T00:00:00.000Z')
          nextDay.setUTCDate(nextDay.getUTCDate() + 1)
          const nextDayStr = nextDay.toISOString().split('T')[0]
          
          const { data: missingData, error: missingError } = await supabase
            .from('trading_decisions')
            .select('id, agent_name, asset, model, date, price, recommended_action, news_count, sentiment, created_at, updated_at')
            .gte('date', nextDayStr)
            .lte('date', dbMaxDateStr)
            .order('date', { ascending: true })
          
          if (!missingError && missingData && missingData.length > 0) {
            console.log(`[DataService] Fetched ${missingData.length} missing rows`)
            const existingIds = new Set(all.map(r => r.id))
            const newRows = missingData.filter(r => !existingIds.has(r.id))
            all.push(...newRows)
            console.log(`[DataService] Added ${newRows.length} new rows`)
            
            // Re-sort
            all.sort((a, b) => {
              const dateA = typeof a.date === 'string' ? a.date.split('T')[0] : a.date
              const dateB = typeof b.date === 'string' ? b.date.split('T')[0] : b.date
              return dateA > dateB ? 1 : (dateA < dateB ? -1 : 0)
            })
            
            const finalDates = all.map(r => r && r.date).filter(Boolean).sort()
            console.log(`[DataService] After fetching missing data: ${all.length} total rows, date range: ${finalDates[0]} to ${finalDates[finalDates.length - 1]}`)
          }
        } else if (fetchedMaxDateStr === dbMaxDateStr) {
          console.log(`[DataService] ✓ Successfully fetched all data up to ${dbMaxDateStr}`)
        }
      }
    } else {
      console.warn('[DataService] No data fetched!')
    }

    return all
  }

  /**
   * 计算单个分组的指标
   */
  async _computeAgentMetrics(row, allDecisions) {
    try {
      // 构建完整时间序列
      const seq = allDecisions
        .filter(r => r.agent_name === row.agent_name && r.asset === row.asset && r.model === row.model)
        .map(r => ({ ...r, dateNormalized: typeof r.date === 'string' ? r.date.split('T')[0] : r.date }))
        .sort((a, b) => {
          // 确保日期格式一致后再排序
          const dateA = a.dateNormalized || a.date
          const dateB = b.dateNormalized || b.date
          return dateA > dateB ? 1 : (dateA < dateB ? -1 : 0)
        })

      // Debug: 打印原始序列的日期范围
      const originalDates = seq.map(r => r.dateNormalized || (typeof r.date === 'string' ? r.date.split('T')[0] : r.date)).filter(Boolean).sort()
      if (originalDates.length > 0) {
        console.log(`[DataService] STEP1 - ${row.agent_name}|${row.asset}|${row.model}: Original seq has ${seq.length} rows, date range: ${originalDates[0]} to ${originalDates[originalDates.length - 1]}`)
        console.log(`[DataService] STEP1 - Last 5 dates:`, originalDates.slice(-5))
      }

      const isCrypto = row.asset === 'BTC' || row.asset === 'ETH'
      const seqFiltered = isCrypto ? seq : (await filterRowsToNyseTradingDays(seq))

      // Debug: 打印过滤后的序列信息
      if (!isCrypto && seqFiltered.length > 0) {
        const filteredDates = seqFiltered.map(r => r.dateNormalized || (typeof r.date === 'string' ? r.date.split('T')[0] : r.date)).filter(Boolean).sort()
        console.log(`[DataService] STEP2 - ${row.agent_name}|${row.asset}|${row.model}: After filterRowsToNyseTradingDays: ${seqFiltered.length} rows, date range: ${filteredDates[0]} to ${filteredDates[filteredDates.length - 1]}`)
        console.log(`[DataService] STEP2 - Last 5 filtered dates:`, filteredDates.slice(-5))
        if (originalDates.length > 0 && filteredDates.length > 0) {
          const lastOriginal = originalDates[originalDates.length - 1]
          const lastFiltered = filteredDates[filteredDates.length - 1]
          if (lastOriginal !== lastFiltered) {
            console.warn(`[DataService] STEP2 - WARNING: Last original date ${lastOriginal} was filtered out! Last filtered date: ${lastFiltered}`)
            // 检查最后一天是否真的是交易日
            const lastOriginalRow = seq.find(r => (r.dateNormalized || (typeof r.date === 'string' ? r.date.split('T')[0] : r.date)) === lastOriginal)
            if (lastOriginalRow) {
              console.warn(`[DataService] STEP2 - Last original row:`, lastOriginalRow)
            }
          }
        }
      }

      // 计算日期范围 - 直接使用 seqFiltered 的最后一条记录的日期，而不是从日期数组中取
      // 这样可以确保使用的是实际数据的最后一天，而不是排序后的日期数组
      const dates = seqFiltered
        .map(s => {
          const dateStr = s.dateNormalized || (typeof s.date === 'string' ? s.date.split('T')[0] : s.date)
          return dateStr
        })
        .filter(Boolean)
        .sort()
      const start_date = dates[0] || '-'
      
      // 计算 end_date：从已获取的数据中提取，因为数据已经完整
      let end_date = '-'
      if (seqFiltered.length > 0) {
        const lastRow = seqFiltered[seqFiltered.length - 1]
        end_date = lastRow.dateNormalized || (typeof lastRow.date === 'string' ? lastRow.date.split('T')[0] : lastRow.date) || '-'
      } else if (dates.length > 0) {
        end_date = dates[dates.length - 1]
      }
      
      console.log(`[DataService] STEP3 - ${row.agent_name}|${row.asset}|${row.model}: Final end_date = ${end_date}, dates array length = ${dates.length}, seqFiltered length = ${seqFiltered.length}`)
      if (seqFiltered.length > 0) {
        const lastRowDate = seqFiltered[seqFiltered.length - 1].dateNormalized || (typeof seqFiltered[seqFiltered.length - 1].date === 'string' ? seqFiltered[seqFiltered.length - 1].date.split('T')[0] : seqFiltered[seqFiltered.length - 1].date)
        console.log(`[DataService] STEP3 - Last row date: ${lastRowDate}, dates array last: ${dates.length > 0 ? dates[dates.length - 1] : 'N/A'}`)
      }

      let closed_days = 0
      if (dates.length > 1) {
        closed_days = await countNonTradingDaysBetweenForAsset(row.asset, start_date, end_date)
      }
      const trading_days = await countTradingDaysBetweenForAsset(row.asset, start_date, end_date)

      // 为每个策略计算指标
      const results = []
      for (const s of STRATEGIES) {
        const st = computeStrategyEquity(seqFiltered, 100000, s.fee, s.strategy, s.tradingMode)
        const stNoFee = computeStrategyEquity(seqFiltered, 100000, 0, s.strategy, s.tradingMode)
        const metrics = calculateMetricsFromSeries(st, isCrypto ? 'crypto' : 'stock')
        const metricsNoFee = calculateMetricsFromSeries(stNoFee, isCrypto ? 'crypto' : 'stock')
        const { winRate, trades } = computeWinRate(seqFiltered, s.strategy, s.tradingMode)
        const bhSeries = computeBuyHoldEquity(seqFiltered, 100000)
        const buy_hold_return = bhSeries.length ? (bhSeries[bhSeries.length - 1] - bhSeries[0]) / bhSeries[0] : 0

        results.push({
          decision_ids: seqFiltered.map(r => r.id).filter(Boolean),
          agent_name: row.agent_name,
          asset: row.asset,
          model: row.model,
          strategy: s.id,
          strategy_label: s.label,
          balance: st.length ? st[st.length - 1] : 100000,
          return_with_fees: metrics.total_return / 100,
          return_no_fees: metricsNoFee.total_return / 100,
          buy_hold_return,
          sharpe_ratio: metrics.sharpe_ratio,
          win_rate: winRate,
          trades,
          start_date,
          end_date,
          closed_days,
          trading_days,
          fee: s.fee,
          tradingMode: s.tradingMode,
          series: seqFiltered.map(r => ({ 
            id: r.id, 
            date: r.date, 
            price: r.price, 
            recommended_action: r.recommended_action 
          })),
          key: `${row.agent_name}|${row.asset}|${row.model}|${s.id}`
        })
      }

      return results
    } catch (e) {
      console.error('[DataService] Error computing metrics:', e)
      return []
    }
  }

  /**
   * 加载数据（主方法）
   */
  async load(forceRefresh = false) {
    if (this.loading) {
      console.log('[DataService] Already loading, skipping...')
      return
    }

    this.loading = true
    this._notify()

    try {
      // 1. 尝试从数据库加载筛选选项
      await this.loadFilterOptionsFromDb()

      // 2. 获取所有决策数据
      let all = null

      if (forceRefresh) {
        // 强制刷新：清除所有缓存，从远程拉取
        await clearAllStores()
        clearAllDecisions()
        all = await this._fetchAllFromRemote()
        setAllDecisions(all)
        await writeRawDecisions(all)
      } else {
        // 正常加载：先尝试内存缓存
        all = getAllDecisions()

        if (!all) {
          // 再尝试 IndexedDB 缓存
          const cached = await readAllRawDecisions()
          if (cached && cached.length) {
            all = cached
            setAllDecisions(all)
          }
        }

        // 检查缓存数据是否是最新的：从 Supabase 查询最新日期，如果缓存中没有最新日期，则强制刷新
        if (all && all.length > 0) {
          try {
            // 获取缓存中的最新日期
            const cachedDates = all.map(r => r && r.date).filter(Boolean).sort()
            const cachedMaxDate = cachedDates.length > 0 ? cachedDates[cachedDates.length - 1] : null
            
            // 从 Supabase 查询最新日期（只查询日期，不查询所有数据）
            const { data: latestData, error } = await supabase
              .from('trading_decisions')
              .select('date')
              .order('date', { ascending: false })
              .limit(1)
            
            if (!error && latestData && latestData.length > 0) {
              const dbMaxDate = latestData[0].date
              const cachedMaxDateStr = cachedMaxDate ? (typeof cachedMaxDate === 'string' ? cachedMaxDate.split('T')[0] : cachedMaxDate) : null
              const dbMaxDateStr = typeof dbMaxDate === 'string' ? dbMaxDate.split('T')[0] : dbMaxDate
              
              console.log(`[DataService] Cache check: cached max date = ${cachedMaxDateStr}, DB max date = ${dbMaxDateStr}`)
              
              // 如果数据库中的最新日期比缓存中的新，强制刷新
              if (cachedMaxDateStr && dbMaxDateStr && dbMaxDateStr > cachedMaxDateStr) {
                console.log(`[DataService] Cache is stale (${cachedMaxDateStr} < ${dbMaxDateStr}), fetching fresh data...`)
                await clearAllStores()
                clearAllDecisions()
                all = await this._fetchAllFromRemote()
                setAllDecisions(all)
                await writeRawDecisions(all)
              }
            }
          } catch (e) {
            console.error('[DataService] Error checking cache freshness:', e)
            // 如果检查失败，继续使用缓存
          }
        }

        if (!all) {
          // 最后从远程拉取
          all = await this._fetchAllFromRemote()
          setAllDecisions(all)
          await writeRawDecisions(all)
        }
      }

      // 3. 计算全局日期范围 - 直接从 Supabase 读取，避免时区问题
      // 始终从数据库读取最新的日期范围，而不是从已获取的数据计算
      let dbMinDate = null
      let dbMaxDate = null
      try {
        const [minDateResult, maxDateResult] = await Promise.all([
          supabase
            .from('trading_decisions')
            .select('date')
            .order('date', { ascending: true })
            .limit(1),
          supabase
            .from('trading_decisions')
            .select('date')
            .order('date', { ascending: false })
            .limit(1)
        ])
        
        if (!minDateResult.error && minDateResult.data && minDateResult.data.length > 0) {
          dbMinDate = minDateResult.data[0].date
        }
        if (!maxDateResult.error && maxDateResult.data && maxDateResult.data.length > 0) {
          dbMaxDate = maxDateResult.data[0].date
        }
      } catch (e) {
        console.error('[DataService] Error getting DB date range for dateBounds:', e)
      }
      
      // 从 Supabase 读取的日期字符串，提取日期部分（YYYY-MM-DD）
      const normalizeDateStr = (dateStr) => {
        if (!dateStr) return null
        if (typeof dateStr === 'string') {
          return dateStr.split('T')[0] // 提取 YYYY-MM-DD 部分
        }
        return null
      }
      
      const dbMinDateStr = normalizeDateStr(dbMinDate)
      const dbMaxDateStr = normalizeDateStr(dbMaxDate)
      
      // 使用 UTC 时间创建 Date 对象，避免时区问题
      // 格式：YYYY-MM-DDTHH:mm:ss.sssZ (UTC midnight)
      this.dateBounds = {
        min: dbMinDateStr ? new Date(dbMinDateStr + 'T00:00:00.000Z') : null,
        max: dbMaxDateStr ? new Date(dbMaxDateStr + 'T00:00:00.000Z') : null
      }
      
      console.log(`[DataService] dateBounds set from DB: min = ${dbMinDateStr}, max = ${dbMaxDateStr}`)
      console.log(`[DataService] dateBounds Date objects: min = ${this.dateBounds.min?.toISOString()}, max = ${this.dateBounds.max?.toISOString()}`)

      // 4. 按 agent_name|asset|model 分组去重
      const keyToRow = new Map()
      for (const row of all) {
        const key = `${row.agent_name}|${row.asset}|${row.model}`
        if (!keyToRow.has(key)) {
          keyToRow.set(key, row)
        }
      }

      // Debug: 检查所有数据的日期范围
      const allDatesInData = all.map(r => r && r.date).filter(Boolean).sort()
      if (allDatesInData.length > 0) {
        console.log(`[DataService] STEP0 - All data fetched: ${all.length} total rows, date range: ${allDatesInData[0]} to ${allDatesInData[allDatesInData.length - 1]}`)
        console.log(`[DataService] STEP0 - Last 5 dates in all data:`, allDatesInData.slice(-5))
      }

      // 5. 计算每个分组的指标
      const agents = []
      for (const row of keyToRow.values()) {
        const metrics = await this._computeAgentMetrics(row, all)
        agents.push(...metrics)
      }

      this.agents = agents

      // 6. 转换为表格行
      this.tableRows = agents.map(a => {
        // Debug: 记录每个 agent 的 end_date
        if (a.asset === 'BTC' || a.asset === 'TSLA' || a.asset === 'BMRN') {
          console.log(`[DataService] STEP4 - Converting to tableRow: ${a.agent_name}|${a.asset}|${a.model} - end_date = ${a.end_date}`)
        }
        return {
        agent_name: a.agent_name,
        asset: a.asset,
        model: a.model,
        strategy: a.strategy,
        strategy_label: a.strategy_label,
        balance: a.balance,
        ret_with_fees: a.return_with_fees,
        ret_no_fees: a.return_no_fees,
        buy_hold: a.buy_hold_return,
        vs_bh_with_fees: a.return_with_fees - a.buy_hold_return,
        sharpe: a.sharpe_ratio,
        trading_days: a.trading_days,
        win_rate: a.win_rate,
        trades: a.trades,
        start_date: a.start_date,
        end_date: a.end_date,
        closed_date: a.closed_days,
        decision_ids: a.decision_ids,
        fee: a.fee,
        tradingMode: a.tradingMode,
        series: a.series,
        key: a.key
        }
      })

      // 7. 生成筛选选项
      this.nameOptions = Array.from(new Set(agents.map(a => a.agent_name)))
        .map(v => ({ label: v, value: v }))
      this.assetOptions = Array.from(new Set(agents.map(a => a.asset)))
        .map(v => ({ label: v, value: v }))
      this.modelOptions = Array.from(new Set(agents.map(a => a.model)))
        .map(v => ({ label: v, value: v }))
      this.strategyOptions = STRATEGIES.map(s => ({ label: s.label, value: s.id }))

      // 8. 保存筛选选项到数据库
      await this.saveFilterOptionsToDb(
        this.nameOptions.map(o => o.value),
        this.assetOptions.map(o => o.value),
        this.modelOptions.map(o => o.value),
        this.strategyOptions.map(o => o.value)
      )

      this.lastUpdated = Date.now()
      this.loaded = true

      console.log('[DataService] Data loaded successfully:', {
        agents: this.agents.length,
        tableRows: this.tableRows.length,
        names: this.nameOptions.length,
        assets: this.assetOptions.length
      })
    } catch (e) {
      console.error('[DataService] Error loading data:', e)
    } finally {
      this.loading = false
      this._notify()
    }
  }

  /**
   * 强制刷新数据
   */
  async forceRefresh() {
    return this.load(true)
  }

  /**
   * 更新 dateBounds，直接查询数据库的最新日期
   * 这个方法可以独立调用，不需要重新加载所有数据
   */
  async updateDateBounds() {
    try {
      // 查询数据库的最小和最大日期 - 直接从 Supabase 读取，避免时区问题
      const [minDateResult, maxDateResult] = await Promise.all([
        supabase
          .from('trading_decisions')
          .select('date')
          .order('date', { ascending: true })
          .limit(1),
        supabase
          .from('trading_decisions')
          .select('date')
          .order('date', { ascending: false })
          .limit(1)
      ])

      let dbMinDate = null
      let dbMaxDate = null

      if (!minDateResult.error && minDateResult.data && minDateResult.data.length > 0) {
        dbMinDate = minDateResult.data[0].date
      }
      if (!maxDateResult.error && maxDateResult.data && maxDateResult.data.length > 0) {
        dbMaxDate = maxDateResult.data[0].date
      }

      // 从 Supabase 读取的日期字符串，提取日期部分（YYYY-MM-DD）
      const normalizeDateStr = (dateStr) => {
        if (!dateStr) return null
        if (typeof dateStr === 'string') {
          return dateStr.split('T')[0] // 提取 YYYY-MM-DD 部分
        }
        return null
      }

      const dbMinDateStr = normalizeDateStr(dbMinDate)
      const dbMaxDateStr = normalizeDateStr(dbMaxDate)

      // 如果查询到了新的日期，更新 dateBounds
      if (dbMinDateStr || dbMaxDateStr) {
        const oldMax = this.dateBounds.max ? this.dateBounds.max.toISOString().split('T')[0] : null
        const newMax = dbMaxDateStr
        
        // 只有当新日期确实更新时才更新
        if (newMax && (!oldMax || newMax > oldMax)) {
          // 使用 UTC 时间创建 Date 对象，避免时区问题
          // 格式：YYYY-MM-DDTHH:mm:ss.sssZ (UTC midnight)
          this.dateBounds = {
            min: dbMinDateStr ? new Date(dbMinDateStr + 'T00:00:00.000Z') : this.dateBounds.min,
            max: dbMaxDateStr ? new Date(dbMaxDateStr + 'T00:00:00.000Z') : this.dateBounds.max
          }
          console.log(`[DataService] updateDateBounds: Updated from DB - min = ${dbMinDateStr}, max = ${dbMaxDateStr}`)
          console.log(`[DataService] updateDateBounds: Date objects - min = ${this.dateBounds.min?.toISOString()}, max = ${this.dateBounds.max?.toISOString()}`)
          // 通知所有订阅者
          this._notify()
        } else if (newMax && oldMax && newMax === oldMax) {
          console.log(`[DataService] updateDateBounds: Date bounds already up to date (max = ${newMax})`)
        } else {
          console.log(`[DataService] updateDateBounds: No update needed - oldMax = ${oldMax}, newMax = ${newMax}`)
        }
      } else {
        console.warn(`[DataService] updateDateBounds: No dates found in DB`)
      }
    } catch (e) {
      console.error('[DataService] Error updating dateBounds:', e)
    }
  }

  /**
   * 获取当前状态
   */
  getState() {
    return {
      loading: this.loading,
      loaded: this.loaded,
      agents: this.agents,
      tableRows: this.tableRows,
      lastUpdated: this.lastUpdated,
      dateBounds: this.dateBounds,
      nameOptions: this.nameOptions,
      assetOptions: this.assetOptions,
      modelOptions: this.modelOptions,
      strategyOptions: this.strategyOptions
    }
  }
}

// 导出单例
export const dataService = new DataService()