// All entities use consistent UUID format
interface User {
  id: string          // UUID: "550e8400-e29b-41d4-a716-446655440000"
  email: string
  organizationId: string  // Also UUID
}

interface Organization {
  id: string          // UUID: "6ba7b810-9dad-11d1-80b4-00c04fd430c8"
  name: string
  ownerId: string     // References User.id (UUID)
}

# Instead of predictable IDs:
GET /api/users/1, /api/users/2, /api/users/3

# We use UUIDs:
GET /api/users/550e8400-e29b-41d4-a716-446655440000

// Schema definition with UUID primary keys
export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 255 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

export const organizations = pgTable('organizations', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: varchar('name', { length: 255 }).notNull(),
  ownerId: text('owner_id').references(() => users.id).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

// Fully typed queries with autocomplete
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    organizations: {
      with: {
        members: true
      }
    }
  }
})
// user is fully typed with nested relations

// Database connection with pooling
import { drizzle } from 'drizzle-orm/neon-http'
import { neon, neonConfig } from '@neondatabase/serverless'

neonConfig.fetchConnectionCache = true

export const db = drizzle(neon(DATABASE_URL), {
  schema: schema,
  logger: process.env.NODE_ENV === 'development'
})

// src/db/models/organization-model.ts
export const organizations = pgTable('organizations', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: varchar('name', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique(),
  ownerId: text('owner_id').references(() => users.id).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

export const members = pgTable('members', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).notNull(),
  organizationId: text('organization_id').references(() => organizations.id).notNull(),
  role: memberRoleEnum('role').default('member').notNull(),
  joinedAt: timestamp('joined_at').defaultNow().notNull(),
})

// src/db/models/project-model.ts
export const projects = pgTable('projects', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: varchar('name', { length: 255 }).notNull(),
  organizationId: text('organization_id').references(() => organizations.id).notNull(),
  ownerId: text('owner_id').references(() => users.id).notNull(),
  status: projectStatusEnum('status').default('active').notNull(),
})

// src/db/models/post-model.ts
export const posts = pgTable('posts', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  published: boolean('published').default(false),
  authorId: text('author_id').references(() => users.id).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  publishedAt: timestamp('published_at'),
})

export const postTranslations = pgTable('post_translations', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  postId: text('post_id').references(() => posts.id).notNull(),
  locale: varchar('locale', { length: 10 }).notNull(),
  title: varchar('title', { length: 500 }).notNull(),
  content: text('content').notNull(),
  description: text('description'),
})

export const hashtags = pgTable('hashtags', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: varchar('name', { length: 100 }).unique().notNull(),
  color: varchar('color', { length: 7 }).default('#3B82F6'),
})

// src/db/models/subscription-model.ts
export const subscriptions = pgTable('subscriptions', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).notNull(),
  stripeSubscriptionId: varchar('stripe_subscription_id', { length: 255 }).unique(),
  stripeCustomerId: varchar('stripe_customer_id', { length: 255 }),
  status: subscriptionStatusEnum('status').notNull(),
  currentPeriodStart: timestamp('current_period_start'),
  currentPeriodEnd: timestamp('current_period_end'),
  planId: varchar('plan_id', { length: 100 }),
})

export const notifications = pgTable('notifications', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).notNull(),
  type: notificationTypeEnum('type').notNull(),
  title: varchar('title', { length: 255 }).notNull(),
  message: text('message'),
  read: boolean('read').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

export const userSettings = pgTable('user_settings', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).unique().notNull(),
  emailNotifications: boolean('email_notifications').default(true),
  marketingEmails: boolean('marketing_emails').default(false),
  theme: themeEnum('theme').default('system'),
})

# Generate migration from schema changes
pnpm db:generate    # Creates migration files in drizzle/migrations/

# Apply migrations to database
pnpm db:migrate     # Runs pending migrations

# Check migration status
pnpm db:status      # Shows applied vs pending migrations

# Validate current schema matches codebase
pnpm db:check

# Push schema changes (development only)
pnpm db:push        # Directly applies schema changes without migrations

# Introspect existing database
pnpm db:introspect  # Generate schema from existing database

# 1. Generate migrations locally
pnpm db:generate

# 2. Commit migration files to version control
git add drizzle/migrations/
git commit -m "feat: add user profile fields"

# 3. Deploy and run migrations
pnpm db:migrate     # Run in production environment

# Seed development data
pnpm db:seed        # Adds demo users, orgs, projects with UUIDs

# Export database data
pg_dump $DATABASE_URL > backup.sql

# Import data from backup
psql $DATABASE_URL < backup.sql

# Reset development environment
pnpm db:reset-seed  # WARNING: Deletes all data!

# Connect to database
psql $DATABASE_URL

# Common UUID queries
SELECT id, email, created_at FROM users LIMIT 5;
SELECT COUNT(*) FROM users WHERE created_at > NOW() - INTERVAL '1 day';

-- Verify UUID format consistency
SELECT
  table_name,
  COUNT(*) as total_records,
  COUNT(CASE WHEN id ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' THEN 1 END) as valid_uuids
FROM (
  SELECT 'users' as table_name, id FROM users
  UNION ALL
  SELECT 'organizations', id FROM organizations
) t
GROUP BY table_name;

# Error: function uuid_generate_v4() does not exist
psql $DATABASE_URL -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"

# Reset migration state (development only)
rm -rf drizzle/migrations/
pnpm db:generate    # Regenerate from current schema

# Test connection
pnpm db:check

# Check environment variables
echo $DATABASE_URL
echo $NEON_DATABASE_URL

-- Check slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
WHERE query LIKE '%users%'
ORDER BY mean_time DESC
LIMIT 5;

-- Add indexes for UUID lookups
CREATE INDEX CONCURRENTLY idx_users_organization_id ON users(organization_id);
CREATE INDEX CONCURRENTLY idx_projects_owner_id ON projects(owner_id);

-- Check for orphaned records
SELECT u.id, u.email
FROM users u
LEFT JOIN organizations o ON u.organization_id = o.id
WHERE u.organization_id IS NOT NULL AND o.id IS NULL;

-- Verify foreign key constraints
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE contype = 'f'
AND conrelid::regclass::text LIKE '%users%';

// Step 1: Add new optional columns first
export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 255 }),
  // New optional field
  profileCompletedAt: timestamp('profile_completed_at'), // nullable initially
})

// Step 2: Migrate data if needed
// Step 3: Make required if necessary in future migration

// Instead of changing column types directly:
// 1. Add new column with new type
  newFieldName: text('new_field_name'), // UUID format

// 2. Migrate data in application code
// 3. Switch application to use new field
// 4. Remove old field in future migration
  // oldFieldName: integer('old_field_name'), // Remove later

// src/db/seed.ts
const demoUsers = await db.insert(users).values([
  {
    id: '550e8400-e29b-41d4-a716-446655440000',
    email: 'admin@example.com',
    name: 'Admin User',
  },
  {
    id: '6ba7b810-9dad-11d1-80b4-00c04fd430c8',
    email: 'user@example.com',
    name: 'Regular User',
  },
]).returning()

// Create related data with proper UUID references
await db.insert(organizations).values([
  {
    id: '123e4567-e89b-12d3-a456-426614174000',
    name: 'Acme Corporation',
    ownerId: demoUsers[0].id, // Reference UUID
  },
])

// Test UUID-based queries
describe('User Repository', () => {
  const testUserId = '550e8400-e29b-41d4-a716-446655440000'

  beforeEach(async () => {
    await db.insert(users).values({
      id: testUserId,
      email: 'test@example.com',
      name: 'Test User',
    })
  })

  it('should find user by UUID', async () => {
    const user = await getUserById(testUserId)
    expect(user?.id).toBe(testUserId)
    expect(user?.email).toBe('test@example.com')
  })
})

// Simple UUID lookup
const user = await db.query.users.findFirst({
  where: eq(users.id, '550e8400-e29b-41d4-a716-446655440000')
})

// Multi-table joins with UUIDs
const userWithOrganization = await db
  .select({
    userId: users.id,
    userName: users.name,
    orgId: organizations.id,
    orgName: organizations.name,
  })
  .from(users)
  .innerJoin(members, eq(members.userId, users.id))
  .innerJoin(organizations, eq(organizations.id, members.organizationId))
  .where(eq(users.id, userId))

// Complex filtering with UUID arrays
const projectMembers = await db.query.users.findMany({
  where: inArray(users.id, [
    '550e8400-e29b-41d4-a716-446655440000',
    '6ba7b810-9dad-11d1-80b4-00c04fd430c8',
  ]),
  with: { projects: true },
})

// Pagination with UUIDs
const paginatedUsers = await db.query.users.findMany({
  where: gt(users.id, lastSeenUserId), // Cursor-based pagination
  orderBy: asc(users.id),
  limit: 20,
})

// Generate UUID for new records
import { randomUUID } from 'crypto'

const newUserId = randomUUID() // '123e4567-e89b-12d3-a456-426614174000'

// Validate UUID format
function isValidUUID(str: string): boolean {
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i
  return uuidRegex.test(str)
}

// UUID-based URL patterns
// /api/users/[userId] where userId is always UUID
export async function GET(request: Request, { params }: { params: { userId: string } }) {
  if (!isValidUUID(params.userId)) {
    return new Response('Invalid user ID', { status: 400 })
  }

  const user = await getUserById(params.userId)
  return Response.json(user)
}

// Batch operations with UUIDs
async function bulkCreateUsers(usersData: CreateUserType[]) {
  const usersWithIds = usersData.map(userData => ({
    ...userData,
    id: randomUUID(),
  }))

  return await db.insert(users).values(usersWithIds).returning()
}

// UUID relationships in forms
interface CreateProjectForm {
  name: string
  description: string
  organizationId: string // UUID from dropdown/selection
  memberIds: string[]    // Array of member UUIDs
}