import { DataSource } from 'typeorm';

/**
 * DEBUGGING SCRIPT: Check Subscription Status in Database
 * Add this to your backend to troubleshoot subscription issues
 *
 * Usage:
 * 1. Create this file in backseo6_226even/src/debug/check-subscriptions.ts
 * 2. Import and run it in main.ts or any endpoint temporarily
 * 3. Check console logs for subscription data
 */

export async function debugSubscriptions(
  dataSource: DataSource,
  userId?: number,
) {
  console.log('\n');
  console.log('═══════════════════════════════════════════════════════════');
  console.log('🔍 SUBSCRIPTION DEBUG REPORT');
  console.log('═══════════════════════════════════════════════════════════');

  try {
    // 1. Check if user_subscriptions table exists
    const tables = await dataSource.query(`
      SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_subscriptions'
    `);

    if (tables.length === 0) {
      console.log('❌ TABLE MISSING: user_subscriptions table does not exist!');
      return;
    }
    console.log('✅ TABLE EXISTS: user_subscriptions');

    // 2. Check total subscriptions
    const [totalSubsCount] = await dataSource.query(`
      SELECT COUNT(*) as count FROM user_subscriptions
    `);
    console.log(`\n📊 Total Subscriptions: ${totalSubsCount.count}`);

    // 3. Check subscriptions by status
    const statusCounts = await dataSource.query(`
      SELECT status, COUNT(*) as count 
      FROM user_subscriptions 
      GROUP BY status
    `);
    console.log('\n📈 Subscriptions by Status:');
    statusCounts.forEach((row: any) => {
      console.log(`   ${row.status.toUpperCase()}: ${row.count}`);
    });

    // 4. Check active subscriptions (date range check)
    const now = new Date().toISOString().slice(0, 19).replace('T', ' ');
    const [activeCount] = await dataSource.query(
      `
      SELECT COUNT(*) as count FROM user_subscriptions 
      WHERE status = 'active' 
      AND starts_at <= ?
      AND ends_at >= ?
    `,
      [now, now],
    );
    console.log(
      `\n⏰ Subscriptions Active NOW (by date): ${activeCount.count}`,
    );

    // 5. Get all subscriptions with details
    const allSubs = await dataSource.query(`
      SELECT 
        us.id,
        us.user_id,
        us.plan_id,
        us.status,
        us.starts_at,
        us.ends_at,
        DATEDIFF(us.ends_at, NOW()) as days_remaining,
        p.name as plan_name,
        p.type as plan_type
      FROM user_subscriptions us
      LEFT JOIN plans p ON us.plan_id = p.id
      ORDER BY us.user_id, us.ends_at DESC
    `);

    if (allSubs.length === 0) {
      console.log('\n⚠️ NO SUBSCRIPTIONS FOUND in database!');
    } else {
      console.log('\n📋 All Subscriptions:');
      allSubs.forEach((sub: any, index: number) => {
        console.log(`\n  [${index + 1}] User #${sub.user_id}`);
        console.log(
          `      Plan: ${sub.plan_name || 'UNKNOWN'} (#${sub.plan_id})`,
        );
        console.log(`      Status: ${sub.status}`);
        console.log(`      Starts: ${sub.starts_at}`);
        console.log(`      Ends: ${sub.ends_at}`);
        console.log(`      Days Left: ${sub.days_remaining}`);

        const now_date = new Date();
        const ends_at = new Date(sub.ends_at);
        const is_date_valid = ends_at >= now_date ? '✅' : '❌';
        console.log(`      Date Valid: ${is_date_valid}`);
      });
    }

    // 6. If userId provided, check that specific user
    if (userId) {
      console.log(`\n🔎 Detailed Check for User #${userId}:`);

      const userSubs = await dataSource.query(
        `
        SELECT 
          us.id,
          us.status,
          us.starts_at,
          us.ends_at,
          p.name as plan_name,
          p.type as plan_type,
          p.code as plan_code
        FROM user_subscriptions us
        LEFT JOIN plans p ON us.plan_id = p.id
        WHERE us.user_id = ?
        ORDER BY us.ends_at DESC
      `,
        [userId],
      );

      if (userSubs.length === 0) {
        console.log(`   ❌ User #${userId} has NO subscriptions`);
      } else {
        console.log(
          `   ✅ User #${userId} has ${userSubs.length} subscription(s):`,
        );
        userSubs.forEach((sub: any, idx: number) => {
          const now = new Date();
          const starts = new Date(sub.starts_at);
          const ends = new Date(sub.ends_at);
          const is_active =
            sub.status === 'active' && starts <= now && ends >= now;

          console.log(`\n      [${idx + 1}] ${sub.plan_name || 'UNKNOWN'}`);
          console.log(`          Plan Type: ${sub.plan_type}`);
          console.log(`          Plan Code: ${sub.plan_code}`);
          console.log(`          Status: ${sub.status}`);
          console.log(`          Start Date: ${sub.starts_at}`);
          console.log(`          End Date: ${sub.ends_at}`);
          console.log(
            `          🟢 IS ACTIVE NOW: ${is_active ? 'YES' : 'NO'}`,
          );
        });
      }

      // Check wallet for user
      const [wallet] = await dataSource.query(
        `
        SELECT * FROM wallets WHERE user_id = ?
      `,
        [userId],
      );

      if (wallet) {
        console.log(`\n   💰 Wallet Balance:`);
        console.log(`      Job Credits: ${wallet.job_credits}`);
        console.log(`      AI Credits: ${wallet.ai_credits}`);
        console.log(`      Database Credits: ${wallet.database_credits}`);
      }
    }

    // 7. Check plans table
    const allPlans = await dataSource.query(`
      SELECT id, name, code, type FROM plans LIMIT 10
    `);

    console.log(`\n📋 Available Plans (showing first 10):`);
    if (allPlans.length === 0) {
      console.log('   ⚠️ NO PLANS FOUND - This might be the issue!');
    } else {
      allPlans.forEach((plan: any) => {
        console.log(`   - ${plan.name} (${plan.code}) [Type: ${plan.type}]`);
      });
    }

    console.log(
      '\n═══════════════════════════════════════════════════════════\n',
    );
  } catch (error) {
    console.error('❌ Error during subscription debug:', error);
  }
}

/**
 * Fix suggestions based on debug output:
 *
 * If "NO SUBSCRIPTIONS FOUND":
 * - Make sure test users have subscriptions created in user_subscriptions table
 *
 * If "NO PLANS FOUND":
 * - Create plans in the plans table first
 *
 * If subscriptions exist but "IS ACTIVE NOW: NO":
 * - Check if dates are in correct format (YYYY-MM-DD HH:MM:SS)
 * - Verify starts_at is in the past and ends_at is in the future
 *
 * If status is not "active":
 * - Update the status to 'active' for subscriptions you want to test
 */
