import { createPool } from 'mariadb';
import { fetch as fetchUSDToCNYRate } from '../fetchers/usd-cny-rate.fetcher.js';
import fs from 'fs';
import path from 'path';

let pool;

function loadDatabaseConfig() {
  const envPath = path.join(process.cwd(), '.env');
  if (fs.existsSync(envPath)) {
    console.log('读取 .env 文件...');
    const envContent = fs.readFileSync(envPath, 'utf-8');
    const lines = envContent.split('\n');
    
    for (const line of lines) {
      const trimmedLine = line.trim();
      if (trimmedLine.startsWith('DATABASE_URL=')) {
        const url = trimmedLine.substring('DATABASE_URL='.length).replace(/^"|"$/g, '');
        console.log('从 .env 文件获取数据库配置成功!');
        console.log(`DATABASE_URL: ${url}`);
        
        // 解析URL
        const parts = url.split('://')[1].split('@');
        const auth = parts[0].split(':');
        const hostAndDb = parts[1].split('/');
        const hostAndPort = hostAndDb[0].split(':');
        
        return {
          user: auth[0],
          password: auth[1],
          host: hostAndPort[0],
          port: parseInt(hostAndPort[1]),
          database: hostAndDb[1]
        };
      }
    }
  }
  
  console.log('未找到 .env 文件或 DATABASE_URL 配置，使用默认配置');
  return {
    host: '192.168.111.111',
    port: 3306,
    user: 'root',
    password: 'fullstack',
    database: 'iboard'
  };
}

async function initPool() {
  if (!pool) {
    console.log('初始化数据库连接池...');
    const config = loadDatabaseConfig();
    
    console.log('数据库配置:');
    console.log(`  主机: ${config.host}`);
    console.log(`  端口: ${config.port}`);
    console.log(`  用户: ${config.user}`);
    console.log(`  数据库: ${config.database}`);
    
    pool = createPool({
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password,
      database: config.database,
      connectionLimit: 5
    });
    
    try {
      const conn = await pool.getConnection();
      console.log('数据库连接成功!');
      conn.release();
    } catch (error) {
      console.error('数据库连接失败:', error.message);
      throw error;
    }
  }
  return pool;
}

async function checkAndFillUSDCNYRate(startDate = null, endDate = null) {
  try {
    await initPool();
    
    console.log('='.repeat(60));
    console.log('开始检查和补充人民币美元汇率数据...');
    console.log('='.repeat(60));

    const currentDate = new Date();
    
    if (!startDate) {
      startDate = new Date(currentDate);
      startDate.setDate(startDate.getDate() - 30);
      startDate = startDate.toISOString().split('T')[0];
    }

    if (!endDate) {
      endDate = currentDate.toISOString().split('T')[0];
    }

    console.log(`检查时间范围: ${startDate} 至 ${endDate}`);

    // 1. 检查数据库中是否存在汇率数据表
    const hasTable = await checkTableExists();
    if (!hasTable) {
      console.log('创建汇率数据表...');
      await createExchangeRateTable();
    }

    // 2. 检查数据库中已有的数据
    const existingData = await getExistingExchangeRates(startDate, endDate);
    console.log(`数据库中已有 ${existingData.length} 条数据`);

    // 3. 计算缺失的日期
    const missingDates = calculateMissingDates(startDate, endDate, existingData);
    console.log(`缺失 ${missingDates.length} 天的数据`);

    // 4. 如果有缺失，调用 fetcher 补足
    if (missingDates.length > 0) {
      console.log('\n开始获取缺失的汇率数据...');
      
      const firstMissingDate = missingDates[0];
      const lastMissingDate = missingDates[missingDates.length - 1];
      
      console.log(`获取范围: ${firstMissingDate} 至 ${lastMissingDate}`);
      
      const fetchedData = await fetchUSDToCNYRate(firstMissingDate, lastMissingDate);
      
      console.log(`\n获取到 ${fetchedData.count} 条数据`);
      
      // 5. 存储新数据到数据库
      const storedCount = await storeExchangeRates(fetchedData.data);
      console.log(`成功存储 ${storedCount} 条数据`);
    } else {
      console.log('\n数据库数据完整，无需补充');
    }

    // 6. 验证最终数据完整性
    const finalData = await getExistingExchangeRates(startDate, endDate);
    console.log(`\n验证后的数据总量: ${finalData.length} 条`);

    return {
      startDate,
      endDate,
      existingCount: existingData.length,
      missingCount: missingDates.length,
      finalCount: finalData.length,
      success: true
    };

  } catch (error) {
    console.error('检查和补充汇率数据失败:', error.message);
    throw error;
  } finally {
    if (pool) {
      await pool.end();
      console.log('数据库连接池已关闭');
    }
  }
}

async function checkTableExists() {
  try {
    console.log('检查汇率数据表是否存在...');
    const result = await pool.query('SHOW TABLES LIKE ?', ['econ_ExchangeRate']);
    return result.length > 0;
  } catch (error) {
    console.error('检查表存在失败:', error.message);
    return false;
  }
}

async function createExchangeRateTable() {
  try {
    console.log('执行创建汇率数据表SQL...');
    await pool.query(`
      CREATE TABLE IF NOT EXISTS econ_ExchangeRate (
        id INT AUTO_INCREMENT PRIMARY KEY,
        date DATE UNIQUE NOT NULL,
        currency VARCHAR(10) NOT NULL,
        centerPrice DECIMAL(10,4) NOT NULL,
        sellingRate DECIMAL(10,4) NOT NULL,
        buyingRate DECIMAL(10,4) NOT NULL,
        source VARCHAR(100) NOT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);
    console.log('汇率数据表创建成功');
  } catch (error) {
    console.error('创建汇率数据表失败:', error.message);
    throw error;
  }
}

async function getExistingExchangeRates(startDate, endDate) {
  try {
    console.log('查询已有汇率数据...');
    const result = await pool.query(
      'SELECT date FROM econ_ExchangeRate WHERE date BETWEEN ? AND ? ORDER BY date',
      [startDate, endDate]
    );
    const dates = result.map(row => {
      if (row.date instanceof Date) {
        return row.date.toISOString().split('T')[0];
      } else if (typeof row.date === 'string') {
        return row.date;
      }
      return null;
    }).filter(Boolean);
    console.log(`查询到 ${dates.length} 条数据`);
    return dates;
  } catch (error) {
    console.error('查询已有汇率数据失败:', error.message);
    return [];
  }
}

function calculateMissingDates(startDate, endDate, existingDates) {
  const existingSet = new Set(existingDates);
  const missingDates = [];
  
  const currentDate = new Date(startDate);
  const finalDate = new Date(endDate);
  
  while (currentDate <= finalDate) {
    const dateStr = currentDate.toISOString().split('T')[0];
    const dayOfWeek = currentDate.getDay();
    
    // 跳过周末（周末没有汇率数据）
    if (dayOfWeek !== 0 && dayOfWeek !== 6) {
      if (!existingSet.has(dateStr)) {
        missingDates.push(dateStr);
      }
    }
    
    currentDate.setDate(currentDate.getDate() + 1);
  }
  
  return missingDates;
}

async function storeExchangeRates(rates) {
  let storedCount = 0;
  
  for (const rate of rates) {
    try {
      await pool.query(
        'INSERT INTO econ_ExchangeRate (date, currency, centerPrice, sellingRate, buyingRate, source) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE centerPrice = VALUES(centerPrice), sellingRate = VALUES(sellingRate), buyingRate = VALUES(buyingRate), source = VALUES(source)',
        [rate.date, rate.currency, rate.centerPrice, rate.sellingRate, rate.buyingRate, rate.source]
      );
      storedCount++;
    } catch (error) {
      console.error(`存储汇率数据 ${rate.date} 失败:`, error.message);
    }
  }
  
  return storedCount;
}

export async function trigger(startDate = null, endDate = null) {
  return await checkAndFillUSDCNYRate(startDate, endDate);
}

function parseCommandLineArgs() {
  const args = process.argv.slice(2);
  let startDate = null;
  let endDate = null;

  for (let i = 0; i < args.length; i++) {
    if (args[i] === '--start' && i + 1 < args.length) {
      startDate = args[i + 1];
      i++;
    } else if (args[i] === '--end' && i + 1 < args.length) {
      endDate = args[i + 1];
      i++;
    } else if (args[i] === '--help') {
      console.log('使用方法:');
      console.log('  bun usd-cny-rate.scraper.js [选项]');
      console.log('');
      console.log('选项:');
      console.log('  --start YYYY-MM-DD  指定开始日期');
      console.log('  --end YYYY-MM-DD    指定结束日期');
      console.log('  --help              显示帮助信息');
      console.log('');
      console.log('示例:');
      console.log('  bun usd-cny-rate.scraper.js');
      console.log('  bun usd-cny-rate.scraper.js --start 2024-01-01 --end 2024-01-31');
      process.exit(0);
    }
  }

  return { startDate, endDate };
}

async function runTest() {
  console.log('='.repeat(60));
  console.log('测试 usd-cny-rate.scraper.js');
  console.log('='.repeat(60));
  
  const { startDate, endDate } = parseCommandLineArgs();
  
  if (startDate) {
    console.log(`\n使用命令行参数:`);
    console.log(`  开始日期: ${startDate}`);
    console.log(`  结束日期: ${endDate || '默认'}`);
  }
  
  console.log('\n测试数据库配置读取...');
  const config = loadDatabaseConfig();
  console.log('配置读取结果:');
  console.log(`  主机: ${config.host}`);
  console.log(`  端口: ${config.port}`);
  console.log(`  用户: ${config.user}`);
  console.log(`  数据库: ${config.database}`);
  console.log('配置读取测试完成!');
  
  try {
    const result = await trigger(startDate, endDate);
    console.log('\n' + '='.repeat(60));
    console.log('测试结果:');
    console.log(`  开始日期: ${result.startDate}`);
    console.log(`  结束日期: ${result.endDate}`);
    console.log(`  原有数据: ${result.existingCount} 条`);
    console.log(`  缺失数据: ${result.missingCount} 条`);
    console.log(`  最终数据: ${result.finalCount} 条`);
    console.log(`  操作成功: ${result.success}`);
    console.log('='.repeat(60));
  } catch (error) {
    console.error('测试失败:', error);
    process.exit(1);
  }
}

runTest();