+-------------------+     +------------------+
|     profiles      |     |   auth.users     |
+-------------------+     +------------------+
| user_id (UUID) PK |---->| id (UUID)    PK  |
| public_id (nanoid)|     |                  |
+-------------------+     +------------------+

-- Hybrid approach: UUID as PK, nanoid as public_id
CREATE TABLE public.profiles (
  -- Primary key is also foreign key to auth.users
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Public identifier for URLs/APIs
  public_id TEXT UNIQUE NOT NULL DEFAULT nanoid('usr_'),

  -- Profile data
  display_name TEXT,
  avatar_url TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Constraints
  CONSTRAINT profiles_public_id_format CHECK (public_id ~ '^usr_[0-9a-zA-Z]{17}$')
);

COMMENT ON COLUMN public.profiles.user_id IS 'Internal ID - use for RLS and joins';
COMMENT ON COLUMN public.profiles.public_id IS 'Public ID - use in URLs and APIs';

-- Index for public_id lookups (user_id already indexed as PK)
CREATE INDEX profiles_public_id_idx ON public.profiles(public_id);

CREATE TABLE public.products (
  -- nanoid as primary key
  id TEXT PRIMARY KEY DEFAULT nanoid('prd_'),

  -- Data
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,

  -- Ownership (if needed)
  created_by UUID REFERENCES auth.users(id),

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Constraints
  CONSTRAINT products_id_format CHECK (id ~ '^prd_[0-9a-zA-Z]{17}$')
);

-- Migration: Add nanoid to existing table
-- Step 1: Add the column
ALTER TABLE public.customers
ADD COLUMN public_id TEXT;

-- Step 2: Generate IDs for existing rows
UPDATE public.customers
SET public_id = nanoid('cus_')
WHERE public_id IS NULL;

-- Step 3: Add constraints
ALTER TABLE public.customers
ALTER COLUMN public_id SET NOT NULL,
ADD CONSTRAINT customers_public_id_unique UNIQUE (public_id),
ADD CONSTRAINT customers_public_id_format CHECK (public_id ~ '^cus_[0-9a-zA-Z]{17}$');

-- Step 4: Set default for new rows
ALTER TABLE public.customers
ALTER COLUMN public_id SET DEFAULT nanoid('cus_');

// Good: Return public_id
return {
  id: profile.public_id,    // usr_V1StGXR8_Z5jdHi
  name: profile.display_name,
  // Never expose user_id (UUID) in API
}

// Bad: Exposing internal UUID
return {
  id: profile.user_id,      // Don't do this!
  ...
}

// Lookup by public_id (for API routes)
const { data } = await supabase
  .from('profiles')
  .select('*')
  .eq('public_id', 'usr_V1StGXR8_Z5jdHi')
  .single()

// Current user's profile (RLS handles auth)
const { data } = await supabase
  .from('profiles')
  .select('*')
  .single()  // RLS filters to current user

// Type-safe prefixed IDs
type UserPublicId = `usr_${string}`
type OrgId = `org_${string}`
type OrderId = `ord_${string}`

interface Profile {
  user_id: string        // UUID - internal use only
  public_id: UserPublicId  // nanoid - for APIs
  displayName: string
}

// Validation helper
function isValidUserPublicId(id: string): id is UserPublicId {
  return /^usr_[0-9a-zA-Z]{17}$/.test(id)
}

// API response type (excludes internal IDs)
interface ProfileResponse {
  id: UserPublicId  // Map public_id to 'id' in response
  displayName: string
}