// ✅ SAFE: Drizzle uses prepared statements automatically
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)) // Automatically parameterized
    .limit(1);
  return result[0] || null;
}

// ✅ SAFE: All Drizzle queries use prepared statements
export async function getUserByEmail(email: string): Promise<User | null> {
  const database = await getRequiredDb();
  const result = await database
    .select()
    .from(users)
    .where(eq(users.email, email)) // Safe parameterized query
    .limit(1);
  return result[0] || null;
}

import { sql } from 'drizzle-orm';

// ✅ SAFE: Parameterized raw SQL
const result = await database.execute(
  sql`SELECT * FROM users WHERE email = ${email} AND role = ${role}`
);

// ❌ DANGEROUS: Never concatenate user input
// const result = await database.execute(
//   sql`SELECT * FROM users WHERE email = '${email}'`
// );

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

// packages/database/schema/verification-tokens.ts
export const verificationTokens = pgTable(
  'verification_tokens',
  {
    // ... columns
    email: text('email').notNull(),
    // ... other columns
  },
  (_table) => ({
    // Database-level email validation constraint
    validEmail: check(
      'valid_verification_token_email',
      sql`email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'`
    ),
  })
);

// packages/database/schema/used-tokens.ts  
export const usedTokens = pgTable(
  'used_tokens',
  {
    // ... columns
    email: text('email').notNull(),
    // ... other columns
  },
  (_table) => ({
    // Database-level email validation constraint
    validEmail: check(
      'valid_used_token_email',
      sql`email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'`
    ),
  })
);

// Database schema with roles
export const userRoleEnum = pgEnum('user_role', ['admin', 'user']);

export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => randomUUID()),
  email: text('email').notNull().unique(),
  role: userRoleEnum('role').notNull().default('user'), // Default to user
  // ... other columns
});

// Admin-only operations
export async function adminOnly<T>(
  userId: string, 
  operation: () => Promise<T>
): Promise<T> {
  const user = await getUserById(userId);
  
  if (!user || user.role !== 'admin') {
    throw new Error('Access denied: Admin privileges required');
  }
  
  return await operation();
}

// Usage example
export async function deleteAnyUser(adminId: string, targetUserId: string) {
  return await adminOnly(adminId, async () => {
    const database = await getRequiredDb();
    const result = await database
      .delete(users)
      .where(eq(users.id, targetUserId))
      .returning({ id: users.id });
    
    return result.length > 0;
  });
}

// apps/web/lib/auth.ts
export const authOptions: BetterAuthOptions = {
  // Session-based authentication (more secure than JWT-only)
  session: {
    expiresIn: 60 * 60 * 24 * 7, // 7 days
    updateAge: 60 * 60 * 24, // 1 day - extends on activity
    freshAge: 60 * 15, // 15 minutes for sensitive operations
    storeSessionInDatabase: true, // Sessions are revocable
    
    // Performance optimization with security
    cookieCache: {
      enabled: true,
      maxAge: 5 * 60, // 5 minutes - short cache for security
    },
  },
  
  // Enhanced cookie security
  advanced: {
    useSecureCookies: process.env.NODE_ENV === 'production',
    cookies: {
      sessionToken: {
        name: 'better-auth.session_token',
        attributes: {
          secure: process.env.NODE_ENV === 'production', // HTTPS only in production
          sameSite: 'lax', // CSRF protection
          path: '/',
          httpOnly: true, // Prevent XSS attacks
        },
      },
    },
  },
  
  // Trusted origins for CORS protection
  trustedOrigins: [authConfig.baseURL],
};

// apps/web/lib/utils/password.ts
export async function hashPassword(password: string): Promise<string> {
  // Use Argon2 for new passwords (industry standard)
  return await hash(password, {
    memoryCost: 65536, // 64 MB
    timeCost: 3, // 3 iterations
    parallelism: 4, // 4 parallel threads
    hashLength: 32, // 32 bytes output
  });
}

export async function verifyLegacyPassword(
  password: string,
  hash: string
): Promise<{ isValid: boolean; needsRehash: boolean }> {
  // Support migration from bcrypt/scrypt
  if (hash.startsWith('$argon2')) {
    // Already Argon2
    const isValid = await verify(hash, password);
    return { isValid, needsRehash: false };
  } else if (hash.startsWith('$2b$')) {
    // Legacy bcrypt - verify and mark for rehash
    const isValid = await bcrypt.compare(password, hash);
    return { isValid, needsRehash: isValid };
  }
  
  // Unknown format
  return { isValid: false, needsRehash: false };
}

# .env.local (never committed)
DATABASE_URL="postgresql://user:password@host:5432/db?sslmode=require"

// packages/database/lib/connection.ts
export async function getDb() {
  if (!databaseUrl) {
    return null; // Fail securely
  }

  try {
    if (databaseUrl.includes('neon.tech')) {
      // Neon serverless - built-in security features
      const client = neon(databaseUrl);
      return drizzle(client);
    } else {
      // postgres.js with security configuration
      const client = postgres(databaseUrl, {
        max: 10, // Limit concurrent connections
        idle_timeout: 20, // Close idle connections
        connect_timeout: 30, // Prevent hanging
        prepare: false, // Better compatibility
        ssl: process.env.NODE_ENV === 'production' ? 'require' : false,
      });
      return drizzle(client);
    }
  } catch (error) {
    console.error('Database connection failed:', error);
    return null; // Fail securely
  }
}

// packages/database/drizzle.config.ts
const enhancedDatabaseUrl = () => {
  const baseUrl = process.env.DATABASE_URL!;
  if (!baseUrl) return baseUrl;

  const url = new URL(baseUrl);
  
  // Add security and timeout parameters
  url.searchParams.set('connect_timeout', '30');
  url.searchParams.set('command_timeout', '30');
  url.searchParams.set('application_name', 'drizzle-kit');
  url.searchParams.set('sslmode', 'require'); // Force SSL
  
  return url.toString();
};

// 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');
    }
    
    // Clean expired verification tokens
    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;
  }
}

// User-specific cleanup for security incidents
export async function cleanupUserTokens(
  userId: string,
  tokenType?: 'email_verification' | 'password_reset'
): Promise<number> {
  try {
    const database = await db;
    if (!database) {
      throw new Error('Database not available');
    }
    
    const whereCondition = tokenType
      ? and(
          eq(verificationTokens.userId, userId),
          eq(verificationTokens.type, tokenType)
        )
      : eq(verificationTokens.userId, userId);

    const result = await database
      .delete(verificationTokens)
      .where(whereCondition)
      .returning({ id: verificationTokens.id });

    return result.length;
  } catch (error) {
    console.error('Failed to cleanup user tokens:', error);
    throw error;
  }
}

// packages/database/schema/used-tokens.ts
export const usedTokens = pgTable('used_tokens', {
  id: uuid('id').primaryKey().defaultRandom(),
  jti: text('jti').unique().notNull(), // JWT ID from token
  tokenHash: text('token_hash').notNull(), // Hashed token for security
  usedAt: timestamp('used_at', { mode: 'date' }).notNull().defaultNow(),
  expiresAt: timestamp('expires_at', { mode: 'date' }).notNull(),
  purpose: text('purpose').notNull(), // 'purchase-verification', etc.
  email: text('email').notNull(), // For audit trail
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
});

// Usage example
export async function markTokenAsUsed(
  jti: string, 
  tokenHash: string, 
  purpose: string, 
  email: string
) {
  const database = await getRequiredDb();
  
  await database.insert(usedTokens).values({
    jti,
    tokenHash,
    purpose,
    email,
    expiresAt: new Date(Date.now() + 24 * 60 * 60 * 1000), // 24 hours
  });
}

export async function isTokenUsed(jti: string): Promise<boolean> {
  const database = await getRequiredDb();
  
  const result = await database
    .select({ id: usedTokens.id })
    .from(usedTokens)
    .where(eq(usedTokens.jti, jti))
    .limit(1);
    
  return result.length > 0;
}

# Run comprehensive security scan
pnpm security-scan

// 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',
          security: 'unknown'
        },
        { status: 503 }
      );
    }

    // Test basic connectivity and security
    await database.execute(sql`SELECT 1`);
    const responseTime = Date.now() - startTime;

    // Additional security checks
    const securityStatus = await checkDatabaseSecurity(database);

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

async function checkDatabaseSecurity(database: any) {
  try {
    // Check for common security configurations
    const sslResult = await database.execute(sql`SHOW ssl`);
    const connectionCount = await database.execute(
      sql`SELECT count(*) FROM pg_stat_activity`
    );
    
    return {
      ssl: sslResult[0]?.ssl === 'on',
      activeConnections: connectionCount[0]?.count,
      status: 'secure'
    };
  } catch (error) {
    return { status: 'unknown', error: error.message };
  }
}

// Security event logging
export function logSecurityEvent(
  eventType: 'login' | 'logout' | 'admin_action' | 'token_cleanup',
  userId?: string,
  details?: Record<string, any>
) {
  const timestamp = new Date().toISOString();
  const logEntry = {
    timestamp,
    event: eventType,
    userId: userId || 'anonymous',
    details: details || {},
    ip: getClientIP(), // Implement based on your setup
  };
  
  // Log to secure audit system
  console.info('🔒 SECURITY EVENT:', JSON.stringify(logEntry));
  
  // In production, also send to:
  // - Security monitoring service (Sentry, DataDog, etc.)
  // - Audit database table
  // - SIEM system
}

// Usage examples
logSecurityEvent('login', userId, { method: 'email' });
logSecurityEvent('admin_action', adminId, { action: 'user_delete', targetUserId });
logSecurityEvent('token_cleanup', undefined, { expiredCount: 15 });

# ✅ GOOD: .env.local (never committed)
DATABASE_URL="postgresql://user:secure_password@db.example.com:5432/prod_db?sslmode=require"

# ✅ GOOD: .env (safe defaults, committed)
EMAIL_PROVIDER=console
PAYMENTS_PROVIDER=disabled

# ❌ NEVER: Commit real credentials to git

// Validate critical security settings
function validateSecurityEnvironment() {
  const errors = [];
  
  if (process.env.NODE_ENV === 'production') {
    if (!process.env.DATABASE_URL?.includes('sslmode=require')) {
      errors.push('Production database must use SSL');
    }
    
    if (!process.env.BETTER_AUTH_SECRET || process.env.BETTER_AUTH_SECRET.length < 32) {
      errors.push('Production must have secure auth secret (32+ characters)');
    }
  }
  
  return errors;
}

// Authorization middleware pattern
export function requireRole(allowedRoles: string[]) {
  return async (userId: string) => {
    const user = await getUserById(userId);
    
    if (!user) {
      throw new Error('Authentication required');
    }
    
    if (!allowedRoles.includes(user.role)) {
      throw new Error(`Access denied. Required roles: ${allowedRoles.join(', ')}`);
    }
    
    return user;
  };
}

// Usage
const requireAdmin = requireRole(['admin']);
const requireUser = requireRole(['user', 'admin']);

export async function deleteUser(currentUserId: string, targetUserId: string) {
  await requireAdmin(currentUserId);
  
  // Admin-only operation
  const database = await getRequiredDb();
  return await database.delete(users).where(eq(users.id, targetUserId));
}

import { z } from 'zod';

// Schema validation for security
const userUpdateSchema = z.object({
  name: z.string().min(1).max(100),
  email: z.string().email(),
  bio: z.string().max(500).optional(),
});

export async function updateUser(userId: string, updates: unknown) {
  // Validate input before database operation
  const validatedUpdates = userUpdateSchema.parse(updates);
  
  const database = await getRequiredDb();
  return await database
    .update(users)
    .set(validatedUpdates)
    .where(eq(users.id, userId))
    .returning();
}

// Rate limiting schema
export const rateLimits = pgTable('rate_limits', {
  id: uuid('id').primaryKey().defaultRandom(),
  identifier: text('identifier').notNull(), // IP or user ID
  action: text('action').notNull(), // 'login', 'signup', etc.
  count: integer('count').notNull().default(1),
  windowStart: timestamp('window_start', { mode: 'date' }).notNull().defaultNow(),
  createdAt: timestamp('created_at', { mode: 'date' }).notNull().defaultNow(),
});

// Rate limiting implementation
export async function checkRateLimit(
  identifier: string,
  action: string,
  maxAttempts: number = 5,
  windowMinutes: number = 15
): Promise<{ allowed: boolean; remaining: number }> {
  const database = await getRequiredDb();
  const windowStart = new Date(Date.now() - windowMinutes * 60 * 1000);
  
  // Count recent attempts
  const [existing] = await database
    .select({ count: count() })
    .from(rateLimits)
    .where(
      and(
        eq(rateLimits.identifier, identifier),
        eq(rateLimits.action, action),
        gte(rateLimits.windowStart, windowStart)
      )
    );
  
  const currentCount = existing?.count || 0;
  
  if (currentCount >= maxAttempts) {
    return { allowed: false, remaining: 0 };
  }
  
  // Record this attempt
  await database.insert(rateLimits).values({
    identifier,
    action,
    count: 1,
  });
  
  return { 
    allowed: true, 
    remaining: maxAttempts - currentCount - 1 
  };
}

import { createCipher, createDecipher } from 'crypto';

const ENCRYPTION_KEY = process.env.ENCRYPTION_KEY!; // 32-byte key

export function encryptSensitive(data: string): string {
  const cipher = createCipher('aes-256-cbc', ENCRYPTION_KEY);
  let encrypted = cipher.update(data, 'utf8', 'hex');
  encrypted += cipher.final('hex');
  return encrypted;
}

export function decryptSensitive(encryptedData: string): string {
  const decipher = createDecipher('aes-256-cbc', ENCRYPTION_KEY);
  let decrypted = decipher.update(encryptedData, 'hex', 'utf8');
  decrypted += decipher.final('utf8');
  return decrypted;
}

// Usage for sensitive data
export async function storeSensitiveUserData(userId: string, sensitiveData: string) {
  const encrypted = encryptSensitive(sensitiveData);
  
  const database = await getRequiredDb();
  await database
    .update(users)
    .set({ encryptedField: encrypted })
    .where(eq(users.id, userId));
}