Turso Database Querying

const axios = require('axios');
const { createClient } = require('@libsql/client');

console.log('🔧 Turso Database Query with Proper Authentication');
console.log('===================================================\n');

const platformToken = process.env.KEYBOARD_TURSO_DATABASE_TOKEN;
if (!platformToken) {
  console.error('❌ KEYBOARD_TURSO_DATABASE_TOKEN environment variable not found');
  console.error('Please ensure your Platform API token is properly configured');
  return;
}

// Configuration from variables
const maxRows = {{maxRowsPerTable}} || 5;
const showSchema = {{showTableSchema}} !== false;
const targetOrg = '{{organizationName}}' || null;
const targetDb = '{{databaseName}}' || null;

try {
  // Step 1: Get user information
  console.log('🔍 Step 1: Authenticating with Platform API...');
  const userResponse = await axios.get('https://api.turso.tech/v1/current-user', {
    headers: { 'Authorization': `Bearer ${platformToken}` }
  });
  
  const username = userResponse.data.user.username;
  const orgToUse = targetOrg || username;
  
  console.log(`✅ Platform API access successful!`);
  console.log(`👤 User: ${userResponse.data.user.name} (@${username})`);
  console.log(`📧 Email: ${userResponse.data.user.email}`);
  console.log(`🏢 Using organization: ${orgToUse}\n`);
  
  // Step 2: List databases
  console.log('📊 Step 2: Listing available databases...');
  const dbResponse = await axios.get(`https://api.turso.tech/v1/organizations/${orgToUse}/databases`, {
    headers: { 'Authorization': `Bearer ${platformToken}` }
  });
  
  const databases = dbResponse.data.databases;
  console.log(`✅ Found ${databases.length} database(s):`);
  
  databases.forEach((db, index) => {
    console.log(`   ${index + 1}. ${db.Name} (${db.regions.join(', ')})`);
  });
  
  if (databases.length === 0) {
    console.log('❌ No databases found in organization');
    return;
  }
  
  // Step 3: Select database to query
  let databaseToQuery;
  if (targetDb) {
    databaseToQuery = databases.find(db => db.Name === targetDb);
    if (!databaseToQuery) {
      console.log(`❌ Database '${targetDb}' not found. Available databases:`);
      databases.forEach(db => console.log(`   • ${db.Name}`));
      return;
    }
  } else {
    databaseToQuery = databases[0]; // Use first database
  }
  
  console.log(`\n🎯 Selected database: ${databaseToQuery.Name}`);
  console.log(`   URL: libsql://${databaseToQuery.Hostname}`);
  console.log(`   Regions: ${databaseToQuery.regions.join(', ')}\n`);
  
  // Step 4: Generate database-specific token
  console.log('🔑 Step 3: Creating database-specific authentication token...');
  const tokenResponse = await axios.post(
    `https://api.turso.tech/v1/organizations/${orgToUse}/databases/${databaseToQuery.Name}/auth/tokens`,
    {},
    { headers: { 'Authorization': `Bearer ${platformToken}` } }
  );
  
  const databaseToken = tokenResponse.data.jwt;
  console.log('✅ Database token generated successfully!\n');
  
  // Step 5: Connect and query database
  console.log('📊 Step 4: Connecting to database and querying data...');
  const client = createClient({
    url: `libsql://${databaseToQuery.Hostname}`,
    authToken: databaseToken
  });
  
  // List all user tables
  const tablesResult = await client.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';");
  
  console.log(`✅ Database connection successful!`);
  console.log(`📋 Found ${tablesResult.rows.length} user table(s):\n`);
  
  if (tablesResult.rows.length === 0) {
    console.log('   📝 Database contains no user tables (empty database)');
  } else {
    // Query each table
    for (const tableRow of tablesResult.rows) {
      const tableName = tableRow.name;
      console.log(`🗂️  Table: ${tableName}`);
      
      try {
        // Get table schema if requested
        if (showSchema) {
          const schemaResult = await client.execute(`PRAGMA table_info("${tableName}");`);
          console.log(`   📝 Schema:`);
          schemaResult.rows.forEach(col => {
            const nullable = col.notnull ? 'NOT NULL' : 'NULLABLE';
            const pk = col.pk ? ' (PRIMARY KEY)' : '';
            console.log(`      ${col.name}: ${col.type} ${nullable}${pk}`);
          });
        }
        
        // Get row count
        const countResult = await client.execute(`SELECT COUNT(*) as count FROM "${tableName}";`);
        const totalRows = countResult.rows[0].count;
        console.log(`   📊 Total rows: ${totalRows}`);
        
        if (totalRows > 0) {
          // Get sample data
          const dataResult = await client.execute(`SELECT * FROM "${tableName}" LIMIT ${maxRows};`);
          
          console.log(`   📄 Sample data (showing ${Math.min(dataResult.rows.length, maxRows)} of ${totalRows} rows):`);
          
          dataResult.rows.forEach((row, index) => {
            console.log(`      [${index + 1}] ${JSON.stringify(row)}`);
          });
          
          if (totalRows > maxRows) {
            console.log(`      ... and ${totalRows - maxRows} more rows`);
          }
        } else {
          console.log(`   📝 Table is empty`);
        }
        
        console.log(''); // Add spacing between tables
        
      } catch (tableError) {
        console.log(`   ❌ Error querying table ${tableName}: ${tableError.message}\n`);
      }
    }
  }
  
  // Summary
  console.log('🎉 Query completed successfully!\n');
  console.log('📊 Summary:');
  console.log(`   • Database: ${databaseToQuery.Name}`);
  console.log(`   • Tables found: ${tablesResult.rows.length}`);
  console.log(`   • Authentication: Platform API → Database Token → Query`);
  
  console.log('\n💡 Key Points:');
  console.log('   ✅ Platform API token works for database management');
  console.log('   ✅ Database-specific tokens required for queries');
  console.log('   ✅ This is the correct and secure authentication flow');
  
} catch (error) {
  console.error('❌ Error during execution:', error.response?.status || error.message);
  
  if (error.response?.data) {
    console.error('📋 Error details:', JSON.stringify(error.response.data, null, 2));
  }
  
  console.error('\n🔧 Troubleshooting:');
  console.error('   • Ensure KEYBOARD_TURSO_DATABASE_TOKEN is a valid Platform API token');
  console.error('   • Verify you have access to the specified organization/database');
  console.error('   • Check that the database exists and is not archived');
}