const mysql = require('mysql2/promise');

async function generateTestData() {
  const connection = await mysql.createConnection({
    host: '129.211.213.226',
    port: 9527,
    user: 'root',
    password: 'aiotAiot123!',
    database: 'nxxmdata'
  });

  try {
    console.log('=== 生成测试数据 ===');

    // 1. 插入保险类型数据
    console.log('插入保险类型数据...');
    await connection.execute(`
      INSERT IGNORE INTO insurance_types (name, description, coverage_amount_min, coverage_amount_max, premium_rate, status) VALUES
      ('生猪养殖保险', '针对生猪养殖的综合保险，覆盖疾病、意外死亡等风险', 1000.00, 500000.00, 0.0350, 'active'),
      ('肉牛养殖保险', '针对肉牛养殖的保险产品，保障牲畜健康和意外损失', 2000.00, 800000.00, 0.0280, 'active'),
      ('奶牛养殖保险', '专为奶牛养殖设计的保险，包含产奶量保障', 3000.00, 1000000.00, 0.0320, 'active'),
      ('羊群养殖保险', '山羊、绵羊等小型反刍动物的养殖保险', 500.00, 200000.00, 0.0400, 'active'),
      ('家禽养殖保险', '鸡、鸭、鹅等家禽的养殖风险保险', 300.00, 100000.00, 0.0450, 'active'),
      ('水产养殖保险', '鱼类、虾类等水产品的养殖保险', 1000.00, 300000.00, 0.0380, 'active'),
      ('综合农业保险', '涵盖多种农业生产活动的综合性保险产品', 5000.00, 2000000.00, 0.0250, 'active')
    `);

    // 2. 插入保险申请数据
    console.log('插入保险申请数据...');
    const applications = [];
    const statuses = ['pending', 'approved', 'rejected', 'under_review'];
    const insuranceTypes = [1, 2, 3, 4, 5, 6, 7];
    const livestockTypes = ['生猪', '肉牛', '奶牛', '山羊', '绵羊', '鸡', '鸭', '鹅', '鱼', '虾'];
    
    for (let i = 1; i <= 150; i++) {
      const applicationNo = `APP${new Date().getFullYear()}${String(i).padStart(6, '0')}`;
      const customerName = `客户${i}`;
      const customerIdCard = `${Math.floor(Math.random() * 900000) + 100000}${Math.floor(Math.random() * 90000000) + 10000000}`;
      const customerPhone = `1${Math.floor(Math.random() * 9) + 3}${Math.floor(Math.random() * 900000000) + 100000000}`;
      const customerAddress = `某省某市某区某街道${i}号`;
      const insuranceTypeId = insuranceTypes[Math.floor(Math.random() * insuranceTypes.length)];
      const livestockType = livestockTypes[Math.floor(Math.random() * livestockTypes.length)];
      const livestockCount = Math.floor(Math.random() * 500) + 10;
      const applicationAmount = (Math.random() * 400000 + 10000).toFixed(2);
      const status = statuses[Math.floor(Math.random() * statuses.length)];
      const applicationDate = new Date(Date.now() - Math.random() * 90 * 24 * 60 * 60 * 1000);
      const reviewerId = Math.random() > 0.5 ? Math.floor(Math.random() * 13) + 1 : null;
      
      applications.push([
        applicationNo, customerName, customerIdCard, customerPhone, customerAddress,
        insuranceTypeId, '畜牧养殖', livestockType, livestockCount, applicationAmount,
        status, applicationDate, '系统生成测试数据', reviewerId, 
        status !== 'pending' ? new Date(applicationDate.getTime() + Math.random() * 7 * 24 * 60 * 60 * 1000) : null
      ]);
    }

    for (const app of applications) {
      await connection.execute(`
        INSERT IGNORE INTO insurance_applications 
        (application_no, customer_name, customer_id_card, customer_phone, customer_address,
         insurance_type_id, insurance_category, application_quantity, application_amount,
         status, application_date, review_notes, reviewer_id, review_date)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        app[0], app[1], app[2], app[3], app[4], app[5], app[6], app[7], app[8], 
        app[9], app[10], app[11], app[12], app[13]
      ]);
    }

    // 3. 插入保单数据（基于已批准的申请）
    console.log('插入保单数据...');
    const [approvedApps] = await connection.execute(`
      SELECT id, application_no, customer_name, customer_id_card, customer_phone, 
             insurance_type_id, application_amount, application_date
      FROM insurance_applications 
      WHERE status = 'approved'
    `);

    for (const app of approvedApps) {
      const policyNo = `POL${new Date().getFullYear()}${String(app.id).padStart(6, '0')}`;
      const coverageAmount = app.application_amount;
      const premiumAmount = (app.application_amount * 0.035).toFixed(2);
      const startDate = new Date(app.application_date);
      startDate.setDate(startDate.getDate() + Math.floor(Math.random() * 30) + 1);
      const endDate = new Date(startDate);
      endDate.setFullYear(endDate.getFullYear() + 1);
      const policyStatus = Math.random() > 0.1 ? 'active' : 'expired';
      const paymentStatus = Math.random() > 0.2 ? 'paid' : 'unpaid';
      const paymentDate = paymentStatus === 'paid' ? startDate : null;
      
      await connection.execute(`
        INSERT IGNORE INTO policies 
        (policy_no, application_id, insurance_type_id, customer_id, 
         coverage_amount, premium_amount, start_date, end_date, 
         policy_status, payment_status, payment_date, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        policyNo, app.id, app.insurance_type_id, 1, 
        coverageAmount, premiumAmount, startDate, endDate, 
        policyStatus, paymentStatus, paymentDate, 1
      ]);
    }

    // 4. 插入理赔数据（基于有效保单）
    console.log('插入理赔数据...');
    const [activePolicies] = await connection.execute(`
      SELECT id, policy_no, customer_id, coverage_amount, start_date, end_date
      FROM policies 
      WHERE policy_status = 'active' AND payment_status = 'paid'
    `);

    const claimStatuses = ['pending', 'approved', 'rejected', 'processing', 'paid'];
    const incidents = [
      '牲畜疾病导致死亡', '自然灾害造成损失', '意外事故导致伤亡', 
      '饲料中毒事件', '设备故障造成损失', '盗窃事件', '火灾事故'
    ];

    for (let i = 0; i < Math.min(activePolicies.length * 0.3, 80); i++) {
      const policy = activePolicies[Math.floor(Math.random() * activePolicies.length)];
      const claimNo = `CLM${new Date().getFullYear()}${String(i + 1).padStart(6, '0')}`;
      const claimAmount = (Math.random() * policy.coverage_amount * 0.8).toFixed(2);
      const claimDate = new Date(policy.start_date.getTime() + Math.random() * (policy.end_date.getTime() - policy.start_date.getTime()));
      const incidentDescription = incidents[Math.floor(Math.random() * incidents.length)];
      const claimStatus = claimStatuses[Math.floor(Math.random() * claimStatuses.length)];
      const reviewerId = Math.random() > 0.3 ? Math.floor(Math.random() * 13) + 1 : null;
      const reviewDate = claimStatus !== 'pending' ? new Date(claimDate.getTime() + Math.random() * 14 * 24 * 60 * 60 * 1000) : null;
      const paymentDate = claimStatus === 'paid' ? new Date(reviewDate.getTime() + Math.random() * 7 * 24 * 60 * 60 * 1000) : null;
      
      await connection.execute(`
        INSERT IGNORE INTO claims 
        (claim_no, policy_id, customer_id, claim_amount, claim_date, 
         claim_status, review_notes, reviewer_id, review_date, payment_date, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        claimNo, policy.id, policy.customer_id, claimAmount, claimDate,
        claimStatus, '系统生成测试数据', reviewerId, reviewDate, paymentDate, 1
      ]);
    }

    console.log('\n=== 测试数据生成完成 ===');
    
    // 显示统计信息
    const [typeCount] = await connection.execute('SELECT COUNT(*) as count FROM insurance_types');
    const [appCount] = await connection.execute('SELECT COUNT(*) as count FROM insurance_applications');
    const [policyCount] = await connection.execute('SELECT COUNT(*) as count FROM policies');
    const [claimCount] = await connection.execute('SELECT COUNT(*) as count FROM claims');
    
    console.log(`保险类型: ${typeCount[0].count} 条`);
    console.log(`保险申请: ${appCount[0].count} 条`);
    console.log(`保单: ${policyCount[0].count} 条`);
    console.log(`理赔: ${claimCount[0].count} 条`);

  } catch (error) {
    console.error('生成测试数据错误:', error);
  } finally {
    await connection.end();
  }
}

generateTestData();