🏗️ Schema-Level Features (Enums, Constraints, UUIDs)
    ↓
📊 Query-Level Features (Complex Queries, Joins, Aggregations)
    ↓
🔄 Transaction Features (Atomic Operations, Concurrency)
    ↓
⚡ Performance Features (Advanced Indexing, Query Optimization)

// packages/database/schema/users.ts
import { pgEnum } from 'drizzle-orm/pg-core';

// User role enum - restricts values to specific options
export const userRoleEnum = pgEnum('user_role', ['admin', 'user']);

export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => randomUUID()),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  
  // Type-safe role assignment with default value
  role: userRoleEnum('role').notNull().default('user'),
  
  // ... other columns
});

// packages/database/schema/verification-tokens.ts
// Define the enum type for verification token types
export const verificationTokenTypeEnum = pgEnum('verification_token_type', [
  'email_verification',
  'password_reset',
]);

export const verificationTokens = pgTable('verification_tokens', {
  id: uuid('id').primaryKey().defaultRandom(),
  token: text('token').notNull().unique(),
  
  // Type-safe token type using enum
  type: verificationTokenTypeEnum('type').notNull(),
  
  userId: text('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  
  // ... other columns
});

// Type-safe enum queries
export async function getUsersByRole(role: 'admin' | 'user'): Promise<User[]> {
  const database = await getRequiredDb();
  return await database
    .select()
    .from(users)
    .where(eq(users.role, role)); // Type-safe enum comparison
}

// Admin-only operations
export async function getAdminUsers(): Promise<User[]> {
  const database = await getRequiredDb();
  return await database
    .select()
    .from(users)
    .where(eq(users.role, 'admin')); // Compile-time verified enum value
}

// packages/database/schema/users.ts
import { check, sql } from 'drizzle-orm';

export const users = pgTable(
  'users',
  {
    id: text('id').primaryKey().$defaultFn(() => randomUUID()),
    email: text('email').notNull().unique(),
    name: text('name').notNull(),
    // ... other columns
  },
  (table) => ({
    // Performance indexes
    emailIdx: index('users_email_idx').on(table.email),
    
    // Database-level email validation constraint
    validEmail: check(
      'valid_email',
      sql`email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'`
    ),
  })
);

// Apply same validation across multiple tables
const emailValidationConstraint = (tableName: string) => 
  check(
    `valid_${tableName}_email`,
    sql`email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'`
  );

// Used tokens table with same email validation
export const usedTokens = pgTable(
  'used_tokens',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    email: text('email').notNull(),
    // ... other columns
  },
  (_table) => ({
    validEmail: emailValidationConstraint('used_token'),
  })
);

// Verification tokens table with same validation
export const verificationTokens = pgTable(
  'verification_tokens', 
  {
    // ... columns
    email: text('email').notNull(),
  },
  (_table) => ({
    validEmail: emailValidationConstraint('verification'),
  })
);

export const verificationTokens = pgTable('verification_tokens', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: text('user_id')
    .notNull()
    .references(() => users.id, { 
      onDelete: 'cascade' // Automatically delete tokens when user is deleted
    }),
  // ... other columns
});

export const userPreferences = pgTable('user_preferences', {
  id: text('id').primaryKey().$defaultFn(() => randomUUID()),
  userId: text('user_id')
    .notNull()
    .unique()
    .references(() => users.id, { 
      onDelete: 'cascade' // Maintain referential integrity
    }),
  // ... other columns
});

// packages/database/schema/verification-tokens.ts
export const verificationTokens = pgTable(
  'verification_tokens',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: text('user_id').notNull(),
    type: verificationTokenTypeEnum('type').notNull(),
    expiresAt: timestamp('expires_at', { mode: 'date' }).notNull(),
    // ... other columns
  },
  (table) => ({
    // Single column indexes
    tokenIdx: index('verification_tokens_token_idx').on(table.token),
    expiresAtIdx: index('verification_tokens_expires_at_idx').on(table.expiresAt),
    
    // Composite index for common query patterns
    userIdTypeIdx: index('verification_tokens_user_id_type_idx').on(
      table.userId,
      table.type
    ),
  })
);

// packages/database/schema/transactions.ts
export const transactions = pgTable(
  'transactions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: text('user_id').notNull(),
    status: text('status').notNull(),
    type: text('type').notNull(),
    createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
    // ... other 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
  })
);

// Partial indexes for specific conditions
export const users = pgTable(
  'users',
  {
    email: text('email').notNull().unique(),
    emailVerified: boolean('email_verified').default(false),
    role: userRoleEnum('role').notNull().default('user'),
    // ... other columns
  },
  (table) => ({
    // Standard indexes
    emailIdx: index('users_email_idx').on(table.email),
    
    // Advanced indexing patterns (implemented via raw SQL in migrations)
    // Partial index for unverified users only
    // CREATE INDEX users_unverified_email_idx ON users (email) WHERE email_verified = false;
    
    // Functional index for case-insensitive searches
    // CREATE INDEX users_email_lower_idx ON users (lower(email));
    
    // Partial index for admin users only
    // CREATE INDEX users_admin_idx ON users (id) WHERE role = 'admin';
  })
);

// Complex join queries for user dashboard data
export async function getUserDashboardData(userId: string) {
  const database = await getRequiredDb();
  
  // Multi-table join with aggregations
  const result = await database
    .select({
      // User data
      userId: users.id,
      userName: users.name,
      userEmail: users.email,
      userRole: users.role,
      
      // Preferences
      theme: userPreferences.theme,
      emailNotifications: userPreferences.emailNotifications,
      
      // Subscription data
      subscriptionStatus: subscriptions.status,
      subscriptionId: subscriptions.stripeSubscriptionId,
      
      // Transaction counts
      transactionCount: count(transactions.id),
    })
    .from(users)
    .leftJoin(userPreferences, eq(users.id, userPreferences.userId))
    .leftJoin(subscriptions, eq(users.id, subscriptions.userId))
    .leftJoin(transactions, eq(users.id, transactions.userId))
    .where(eq(users.id, userId))
    .groupBy(
      users.id,
      users.name, 
      users.email,
      users.role,
      userPreferences.theme,
      userPreferences.emailNotifications,
      subscriptions.status,
      subscriptions.stripeSubscriptionId
    )
    .limit(1);

  return result[0];
}

// Subquery patterns for complex filtering
export async function getUsersWithRecentActivity(days: number = 7): Promise<User[]> {
  const database = await getRequiredDb();
  const cutoffDate = new Date(Date.now() - days * 24 * 60 * 60 * 1000);

  return await database
    .select()
    .from(users)
    .where(
      exists(
        database
          .select()
          .from(transactions)
          .where(
            and(
              eq(transactions.userId, users.id),
              gte(transactions.createdAt, cutoffDate)
            )
          )
      )
    )
    .orderBy(users.updatedAt);
}

// Window functions for ranking and analytics
export async function getTopUsersByTransactionVolume(limit: number = 10) {
  const database = await getRequiredDb();

  return await database.execute(sql`
    SELECT 
      u.id,
      u.name,
      u.email,
      COUNT(t.id) as transaction_count,
      SUM(t.amount) as total_amount,
      AVG(t.amount) as average_amount,
      ROW_NUMBER() OVER (ORDER BY COUNT(t.id) DESC) as rank
    FROM users u
    LEFT JOIN transactions t ON u.id = t.user_id
    WHERE t.status = 'completed'
    GROUP BY u.id, u.name, u.email
    ORDER BY transaction_count DESC
    LIMIT ${limit}
  `);
}

// Time series analysis with window functions
export async function getMonthlyTransactionTrends() {
  const database = await getRequiredDb();

  return await database.execute(sql`
    SELECT 
      DATE_TRUNC('month', created_at) as month,
      COUNT(*) as transaction_count,
      SUM(amount) as total_amount,
      AVG(amount) as average_amount,
      LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)) as previous_month_count,
      ROUND(
        (COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at))) * 100.0 / 
        NULLIF(LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)), 0),
        2
      ) as growth_percentage
    FROM transactions
    WHERE status = 'completed'
      AND created_at >= NOW() - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', created_at)
    ORDER BY month DESC
  `);
}

// CTE for complex data processing
export async function getUserEngagementMetrics(userId: string) {
  const database = await getRequiredDb();

  return await database.execute(sql`
    WITH user_activity AS (
      SELECT 
        u.id,
        u.name,
        u.created_at as user_since,
        COUNT(t.id) as total_transactions,
        MAX(t.created_at) as last_transaction,
        AVG(t.amount) as avg_transaction_amount
      FROM users u
      LEFT JOIN transactions t ON u.id = t.user_id
      WHERE u.id = ${userId}
      GROUP BY u.id, u.name, u.created_at
    ),
    engagement_score AS (
      SELECT 
        *,
        CASE 
          WHEN total_transactions = 0 THEN 0
          WHEN total_transactions <= 5 THEN 1
          WHEN total_transactions <= 20 THEN 2
          ELSE 3
        END as engagement_level,
        EXTRACT(DAYS FROM NOW() - last_transaction) as days_since_last_activity
      FROM user_activity
    )
    SELECT 
      *,
      CASE engagement_level
        WHEN 0 THEN 'New'
        WHEN 1 THEN 'Low'
        WHEN 2 THEN 'Medium' 
        WHEN 3 THEN 'High'
      END as engagement_category
    FROM engagement_score
  `);
}

// Transaction wrapper for complex operations
export async function transferUserData(
  fromUserId: string,
  toUserId: string,
  transferData: {
    preferences: boolean;
    transactions: boolean;
  }
): Promise<void> {
  const database = await getRequiredDb();

  // Use database transaction to ensure atomicity
  await database.transaction(async (tx) => {
    // Verify both users exist
    const [fromUser, toUser] = await Promise.all([
      tx.select().from(users).where(eq(users.id, fromUserId)).limit(1),
      tx.select().from(users).where(eq(users.id, toUserId)).limit(1),
    ]);

    if (fromUser.length === 0 || toUser.length === 0) {
      throw new Error('One or both users not found');
    }

    // Transfer preferences if requested
    if (transferData.preferences) {
      const [preferences] = await tx
        .select()
        .from(userPreferences)
        .where(eq(userPreferences.userId, fromUserId))
        .limit(1);

      if (preferences) {
        // Delete old preferences for target user
        await tx
          .delete(userPreferences)
          .where(eq(userPreferences.userId, toUserId));

        // Insert transferred preferences
        await tx.insert(userPreferences).values({
          ...preferences,
          id: randomUUID(),
          userId: toUserId,
          createdAt: new Date(),
          updatedAt: new Date(),
        });
      }
    }

    // Transfer transactions if requested
    if (transferData.transactions) {
      await tx
        .update(transactions)
        .set({ userId: toUserId, updatedAt: new Date() })
        .where(eq(transactions.userId, fromUserId));
    }

    // Update user records
    await Promise.all([
      tx
        .update(users)
        .set({ updatedAt: new Date() })
        .where(eq(users.id, fromUserId)),
      tx
        .update(users)
        .set({ updatedAt: new Date() })
        .where(eq(users.id, toUserId)),
    ]);
  });
}

// Optimistic locking with version checking
export async function updateUserWithVersionCheck(
  userId: string,
  updates: Partial<CreateUser>,
  expectedUpdatedAt: Date
): Promise<{ success: boolean; currentVersion?: Date }> {
  const database = await getRequiredDb();

  return await database.transaction(async (tx) => {
    // Check current version
    const [currentUser] = await tx
      .select({ updatedAt: users.updatedAt })
      .from(users)
      .where(eq(users.id, userId))
      .limit(1);

    if (!currentUser) {
      throw new Error('User not found');
    }

    // Version mismatch - someone else updated the record
    if (currentUser.updatedAt.getTime() !== expectedUpdatedAt.getTime()) {
      return {
        success: false,
        currentVersion: currentUser.updatedAt,
      };
    }

    // Safe to update
    await tx
      .update(users)
      .set({ 
        ...updates, 
        updatedAt: new Date() 
      })
      .where(eq(users.id, userId));

    return { success: true };
  });
}

// Multi-tenant data isolation pattern
export async function getUserDataWithTenancy(
  userId: string,
  requestingUserId: string
): Promise<User | null> {
  const database = await getRequiredDb();

  // Get requesting user's role
  const [requestingUser] = await database
    .select({ role: users.role })
    .from(users)
    .where(eq(users.id, requestingUserId))
    .limit(1);

  if (!requestingUser) {
    throw new Error('Requesting user not found');
  }

  // Admin can access any user data
  if (requestingUser.role === 'admin') {
    return await selectUserById(userId);
  }

  // Regular users can only access their own data
  if (requestingUserId === userId) {
    return await selectUserById(userId);
  }

  // Access denied
  return null;
}

// Audit logging for sensitive operations
export async function auditSensitiveOperation(
  operation: string,
  userId: string,
  details: Record<string, any>
) {
  const database = await getRequiredDb();

  await database.execute(sql`
    INSERT INTO audit_logs (
      operation,
      user_id,
      details,
      ip_address,
      user_agent,
      created_at
    ) VALUES (
      ${operation},
      ${userId},
      ${JSON.stringify(details)},
      ${getClientIP()},
      ${getUserAgent()},
      NOW()
    )
  `);
}

// Efficient pagination with cursor-based approach
export async function getUsersPaginated(
  cursor?: string,
  limit: number = 20,
  direction: 'forward' | 'backward' = 'forward'
) {
  const database = await getRequiredDb();

  let query = database
    .select()
    .from(users)
    .orderBy(
      direction === 'forward' ? asc(users.createdAt) : desc(users.createdAt),
      direction === 'forward' ? asc(users.id) : desc(users.id)
    )
    .limit(limit + 1); // Get one extra to check for next page

  if (cursor) {
    const cursorDate = new Date(cursor);
    query = query.where(
      direction === 'forward'
        ? gt(users.createdAt, cursorDate)
        : lt(users.createdAt, cursorDate)
    );
  }

  const results = await query;
  const hasNextPage = results.length > limit;
  const users_page = hasNextPage ? results.slice(0, -1) : results;

  const nextCursor = hasNextPage
    ? users_page[users_page.length - 1].createdAt.toISOString()
    : null;

  return {
    users: users_page,
    nextCursor,
    hasNextPage,
  };
}

// Efficient batch operations
export async function batchUpdateUsers(
  updates: { id: string; data: Partial<CreateUser> }[]
): Promise<void> {
  const database = await getRequiredDb();

  // Process in batches to avoid memory issues
  const batchSize = 100;
  
  for (let i = 0; i < updates.length; i += batchSize) {
    const batch = updates.slice(i, i + batchSize);
    
    await database.transaction(async (tx) => {
      await Promise.all(
        batch.map(({ id, data }) =>
          tx
            .update(users)
            .set({ ...data, updatedAt: new Date() })
            .where(eq(users.id, id))
        )
      );
    });
  }
}

// Connection pool monitoring and management
export class AdvancedConnectionManager {
  private poolStats = new Map<string, ConnectionStats>();

  async getOptimalConnection(): Promise<DatabaseInstance> {
    const database = await getDb();
    if (!database) {
      throw new Error('Database not configured');
    }

    // Monitor connection usage
    this.recordConnectionUsage();

    return database;
  }

  private recordConnectionUsage() {
    const timestamp = Date.now();
    // Record connection metrics for monitoring
    console.log(`Database connection requested at ${timestamp}`);
  }

  async getConnectionMetrics() {
    const database = await getDb();
    if (!database) {
      return null;
    }

    // Get PostgreSQL connection statistics
    const stats = await database.execute(sql`
      SELECT 
        count(*) as total_connections,
        count(*) FILTER (WHERE state = 'active') as active_connections,
        count(*) FILTER (WHERE state = 'idle') as idle_connections,
        count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction,
        max(now() - backend_start) as max_connection_age
      FROM pg_stat_activity 
      WHERE datname = current_database()
    `);

    return stats[0];
  }
}

interface ConnectionStats {
  timestamp: number;
  activeConnections: number;
  totalRequests: number;
}

// Comprehensive database health assessment
export async function advancedHealthCheck(): Promise<HealthCheckResult> {
  const database = await getDb();
  if (!database) {
    return {
      status: 'not_configured',
      timestamp: new Date(),
      checks: {},
    };
  }

  const checks = await Promise.allSettled([
    // Basic connectivity
    database.execute(sql`SELECT 1`),
    
    // Table accessibility
    database.select().from(users).limit(1),
    
    // Write capability
    database.execute(sql`SELECT pg_is_in_recovery()`),
    
    // Extension availability
    database.execute(sql`SELECT * FROM pg_extension WHERE extname = 'uuid-ossp'`),
  ]);

  const healthStatus = {
    connectivity: checks[0].status === 'fulfilled',
    readAccess: checks[1].status === 'fulfilled',
    writeAccess: checks[2].status === 'fulfilled',
    extensions: checks[3].status === 'fulfilled',
  };

  const overallHealth = Object.values(healthStatus).every(Boolean);

  return {
    status: overallHealth ? 'healthy' : 'degraded',
    timestamp: new Date(),
    checks: healthStatus,
    details: checks.map((check, i) => ({
      test: ['connectivity', 'readAccess', 'writeAccess', 'extensions'][i],
      passed: check.status === 'fulfilled',
      error: check.status === 'rejected' ? check.reason.message : undefined,
    })),
  };
}

interface HealthCheckResult {
  status: 'healthy' | 'degraded' | 'not_configured';
  timestamp: Date;
  checks: Record<string, boolean>;
  details?: Array<{
    test: string;
    passed: boolean;
    error?: string;
  }>;
}