// ✅ Optimized query patterns (from packages/database/queries/users.ts)
import { eq, count } from 'drizzle-orm';
import { getDb } from '../lib/connection';
import { users } from '../schema/users';

// Efficient single record lookup with limit
export async function getUserById(id: string): Promise<User | null> {
  const database = await getRequiredDb();
  const result = await database
    .select()
    .from(users)
    .where(eq(users.id, id))
    .limit(1); // Always limit single record queries
  return result[0] || null;
}

// Paginated queries for large datasets
export async function getUsers(limit = 50, offset = 0): Promise<User[]> {
  const database = await getRequiredDb();
  return await database
    .select()
    .from(users)
    .limit(limit) // Always limit large queries
    .offset(offset);
}

// Efficient count queries
export async function getUserCount(): Promise<number> {
  const database = await getRequiredDb();
  const result = await database
    .select({ count: count(users.id) })
    .from(users);
  return result[0].count;
}

// ✅ Prepared statements (automatic)
const user = await database
  .select()
  .from(users)
  .where(eq(users.email, userEmail))
  .limit(1);

// ❌ Raw SQL (avoid unless necessary)
const user = await database.execute(sql`
  SELECT * FROM users WHERE email = ${userEmail}
`);

// ✅ Select only needed columns
const userProfile = await database
  .select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users)
  .where(eq(users.id, userId));

// ❌ Select all columns
const user = await database
  .select()
  .from(users)
  .where(eq(users.id, userId));

// ✅ Optimized JOIN with selected columns
const userWithPreferences = await database
  .select({
    userId: users.id,
    userName: users.name,
    userEmail: users.email,
    theme: userPreferences.theme,
    emailNotifications: userPreferences.emailNotifications,
  })
  .from(users)
  .leftJoin(userPreferences, eq(users.id, userPreferences.userId))
  .where(eq(users.id, userId))
  .limit(1);

// packages/database/lib/repositories/users.ts
export class UserRepository {
  constructor(private db: DatabaseInstance) {}

  /**
   * Optimized single user lookup with caching potential
   */
  async findById(id: string): Promise<User | null> {
    const [user] = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id))
      .limit(1); // Performance: always limit single lookups
    return user || null;
  }

  /**
   * Efficient paginated listing
   */
  async list(limit: number = 10, offset: number = 0): Promise<User[]> {
    return await this.db
      .select()
      .from(users)
      .limit(limit) // Performance: always limit collections
      .offset(offset);
  }
}

export const users = pgTable(
  'users',
  {
    // ... column definitions
  },
  (table) => ({
    // Performance indexes
    emailIdx: index('users_email_idx').on(table.email), // Login lookups
    createdAtIdx: index('users_created_at_idx').on(table.createdAt), // Sorting
    updatedAtIdx: index('users_updated_at_idx').on(table.updatedAt), // Recent activity
  })
);

export const transactions = pgTable(
  'transactions', 
  {
    // ... columns
  },
  (table) => ({
    // Query-optimized indexes
    userIdIdx: index('transactions_user_id_idx').on(table.userId), // User queries
    statusIdx: index('transactions_status_idx').on(table.status), // Status filtering
    createdAtIdx: index('transactions_created_at_idx').on(table.createdAt), // Time-based queries
    typeIdx: index('transactions_type_idx').on(table.type), // Transaction type filtering
  })
);

export const verificationTokens = pgTable(
  'verification_tokens',
  {
    // ... columns
  },
  (table) => ({
    // Performance indexes
    tokenIdx: index('verification_tokens_token_idx').on(table.token), // Token lookup
    expiresAtIdx: index('verification_tokens_expires_at_idx').on(table.expiresAt), // Cleanup queries
    userIdTypeIdx: index('verification_tokens_user_id_type_idx').on(
      table.userId,
      table.type
    ), // Composite lookup
  })
);

-- For exact match queries
CREATE INDEX users_email_idx ON users (email);
CREATE INDEX users_created_at_idx ON users (created_at);

-- For multi-column WHERE clauses
CREATE INDEX verification_tokens_user_id_type_idx 
ON verification_tokens (user_id, type);

-- For ORDER BY with WHERE
CREATE INDEX transactions_user_id_created_at_idx 
ON transactions (user_id, created_at DESC);

-- For filtered queries (PostgreSQL feature)
CREATE INDEX active_users_email_idx 
ON users (email) WHERE is_active = true;

-- For non-null values only
CREATE INDEX users_bio_idx 
ON users (bio) WHERE bio IS NOT NULL;

-- Check index usage statistics
SELECT 
  indexrelname as index_name,
  idx_scan as index_scans,
  idx_tup_read as tuples_read,
  idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes 
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Identify slow queries without indexes
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements 
WHERE mean_time > 100 -- queries taking > 100ms
ORDER BY mean_time DESC 
LIMIT 10;

// packages/database/lib/connection.ts
export async function getDb(): Promise<NeonDatabase | PostgresJsDatabase | null> {
  try {
    if (!databaseUrl) {
      return null; // Graceful degradation
    }

    if (databaseUrl.includes('neon.tech')) {
      // Neon serverless for production
      const client = neon(databaseUrl);
      return drizzle(client);
    } else {
      // postgres.js for development/other providers
      const client = postgres(databaseUrl, {
        max: 10, // Connection pool size
        idle_timeout: 20, // Close idle connections
        connect_timeout: 30, // Connection timeout
        prepare: false, // Better compatibility
      });
      return drizzle(client);
    }
  } catch (error) {
    console.error('Database connection failed:', error);
    return null;
  }
}

const client = postgres(databaseUrl, {
  max: 10, // Maximum 10 concurrent connections
  idle_timeout: 20, // Close idle connections after 20 seconds
  connect_timeout: 30, // 30 second connection timeout
  prepare: false, // Disable prepared statements for compatibility
  transform: postgres.camel, // Transform snake_case to camelCase
});

// API route: app/api/health/database/route.ts
export async function GET() {
  try {
    const startTime = Date.now();
    const database = await getDb();
    
    if (!database) {
      return Response.json(
        { status: 'unhealthy', message: 'Database not configured' },
        { status: 503 }
      );
    }

    // Test query performance
    await database.execute(sql`SELECT 1`);
    const responseTime = Date.now() - startTime;

    return Response.json({
      status: 'healthy',
      responseTime: `${responseTime}ms`,
      timestamp: new Date().toISOString(),
    });
  } catch (error) {
    return Response.json(
      { status: 'unhealthy', error: error.message },
      { status: 503 }
    );
  }
}

# Run performance baseline tests
pnpm perf:baseline

async function testEndpoint(url: string): Promise<PerformanceResult> {
  const times: number[] = [];
  const testRuns = 5;

  for (let i = 0; i < testRuns; i++) {
    try {
      const start = Date.now();
      const response = await fetch(url);
      const end = Date.now();

      if (response.ok) {
        times.push(end - start);
      }
    } catch (error) {
      console.error(`Error testing ${url}:`, error);
    }
  }

  return {
    endpoint: url,
    averageTime: times.reduce((a, b) => a + b) / times.length,
    status: times.length > 0 ? 'success' : 'error',
  };
}

-- Analyze query execution plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Check index usage
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.*, up.theme 
FROM users u 
LEFT JOIN user_preferences up ON u.id = up.user_id 
WHERE u.email = 'user@example.com';

-- Enable pg_stat_statements extension (usually enabled by default)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  stddev_time,
  rows
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

// packages/database/lib/auth-cleanup.ts
export async function cleanupExpiredTokens(): Promise<number> {
  try {
    const now = new Date();
    const database = await db;
    
    if (!database) {
      throw new Error('Database not available');
    }
    
    // Efficient deletion with index on expiresAt
    const result = await database
      .delete(verificationTokens)
      .where(lt(verificationTokens.expiresAt, now))
      .returning({ id: verificationTokens.id });

    const deletedCount = result.length;

    if (deletedCount > 0) {
      console.info(`🧹 Cleaned up ${deletedCount} expired verification tokens`);
    }

    return deletedCount;
  } catch (error) {
    console.error('Failed to cleanup expired tokens:', error);
    throw error;
  }
}

// ✅ Batch related queries
async function getUserDashboardData(userId: string) {
  const database = await getRequiredDb();
  
  // Execute queries in parallel
  const [user, preferences, subscriptions, recentTransactions] = await Promise.all([
    database.select().from(users).where(eq(users.id, userId)).limit(1),
    database.select().from(userPreferences).where(eq(userPreferences.userId, userId)).limit(1),
    database.select().from(subscriptions).where(eq(subscriptions.userId, userId)),
    database.select().from(transactions)
      .where(eq(transactions.userId, userId))
      .orderBy(desc(transactions.createdAt))
      .limit(10),
  ]);

  return {
    user: user[0],
    preferences: preferences[0],
    subscriptions,
    recentTransactions,
  };
}

// ✅ Cursor-based pagination (more efficient for large datasets)
async function getUsersWithCursor(cursor?: string, limit = 20) {
  const database = await getRequiredDb();
  
  let query = database
    .select()
    .from(users)
    .orderBy(users.createdAt, users.id) // Stable sort order
    .limit(limit + 1); // Get one extra to check for next page

  if (cursor) {
    query = query.where(gt(users.createdAt, new Date(cursor)));
  }

  const results = await query;
  const hasNextPage = results.length > limit;
  const users = hasNextPage ? results.slice(0, -1) : results;
  
  const nextCursor = hasNextPage 
    ? users[users.length - 1].createdAt.toISOString()
    : null;

  return {
    users,
    nextCursor,
    hasNextPage,
  };
}

// Example caching wrapper (implement with Redis or similar)
class CachedUserRepository {
  constructor(
    private userRepo: UserRepository,
    private cache: Cache // Redis, Upstash, etc.
  ) {}

  async findById(id: string): Promise<User | null> {
    const cacheKey = `user:${id}`;
    
    // Try cache first
    const cached = await this.cache.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    // Fallback to database
    const user = await this.userRepo.findById(id);
    
    if (user) {
      // Cache for 5 minutes
      await this.cache.setex(cacheKey, 300, JSON.stringify(user));
    }

    return user;
  }
}

-- Connection statistics
SELECT 
  state,
  count(*) as connections
FROM pg_stat_activity 
WHERE datname = current_database()
GROUP BY state;

-- Index hit ratio (should be > 0.99)
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats 
WHERE schemaname = 'public'
ORDER BY n_distinct DESC;

-- Table sizes and bloat
SELECT 
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

// Example health check with performance thresholds
export async function checkDatabasePerformance() {
  const startTime = Date.now();
  
  try {
    const database = await getRequiredDb();
    await database.execute(sql`SELECT 1`);
    
    const responseTime = Date.now() - startTime;
    
    // Alert if response time > 1 second
    if (responseTime > 1000) {
      console.warn(`🐌 Slow database response: ${responseTime}ms`);
      // Send alert to monitoring service
    }
    
    return { healthy: true, responseTime };
  } catch (error) {
    console.error('🚨 Database health check failed:', error);
    // Send critical alert
    return { healthy: false, error: error.message };
  }
}

# Use db:push for fast development iteration
pnpm db:push          # Direct schema sync (no migration files)

# Monitor query performance in development
pnpm db:studio        # Visual query analysis

# Run performance baseline tests
pnpm perf:baseline    # Test API endpoint performance

# Use migrations for production deployments
pnpm db:migrate       # Safe, versioned schema changes

# Monitor production health
curl /api/health/database  # Check database health
curl /api/healthz          # General application health