Clients System

import { getDatabase } from '#libs/database'
import type { ClientData } from '#libs/database'

const db = getDatabase()

// Minimal client (only required field)
const minimalClient: ClientData = {
  gender: 'prefer_not_to_say',
  nickname: 'Client A', // Recommended for display in client lists
  created_by: userId,
}

const clientId = await db.insertClient(minimalClient)
console.log(`Client created: ${clientId}`)

// Simple client with name
const clientData: ClientData = {
  first_name: 'John',
  last_name: 'Doe',
  gender: 'male',
  nickname: 'Johnny', // Will be shown in client lists
  created_by: userId, // User UUID from Clerk
}

const namedClientId = await db.insertClient(clientData)

// Client with full contact information
const fullClientData: ClientData = {
  first_name: 'Jane',
  last_name: 'Smith',
  gender: 'female',
  date_of_birth: '1990-03-22',
  nickname: 'Janie',
  phone: '5551234567',
  email: 'jane.smith@example.com',
  street_address: '456 Oak Ave',
  city: 'Los Angeles',
  state: 'CA',
  zip_code: '90001',
  veteran_status: false,
  ethnicity: 'Asian',
  notes: 'Referred by John Doe, prefers morning appointments',
  created_by: userId,
}

const fullClientId = await db.insertClient(fullClientData)

// Get all clients (alphabetical by last name)
const allClients = await db.getClients({
  limit: 50,
  order_by: 'last_name',
})

// Get user's clients
const myClients = await db.getClients({
  created_by: userId,
  limit: 20,
})

// Get recent clients
const recentClients = await db.getClients({
  order_by: 'created_at',
  order_direction: 'desc',
  limit: 10,
})

// Optimized list view (selective fields)
const clientList = await db.getClients({
  fields: ['id', 'first_name', 'last_name', 'phone', 'email'],
  limit: 100,
})

import { getDatabase } from '#libs/database'
import { formatClientName, formatPhoneNumber } from '#utils/clients'

async function listClientsAlphabetically() {
  const db = getDatabase()

  const clients = await db.getClients({
    order_by: 'last_name',
    order_direction: 'asc',
    limit: 100,
  })

  clients.forEach(client => {
    console.log(formatClientName(client)) // "John Doe"
    if (client.phone) {
      console.log(`  Phone: ${formatPhoneNumber(client.phone)}`) // "(555) 123-4567"
    }
    if (client.email) {
      console.log(`  Email: ${client.email}`)
    }
  })
}

async function getUserClients(userId: string) {
  const db = getDatabase()

  return await db.getClients({
    created_by: userId,
    order_by: 'last_name',
    limit: 50,
  })
}

async function getClientDetails(clientId: string) {
  const db = getDatabase()
  const client = await db.getClientById(clientId)

  if (!client) {
    throw new Error('Client not found')
  }

  return client
}

async function updateClientContact(clientId: string, phone: string, email: string) {
  const db = getDatabase()

  const success = await db.updateClient(clientId, {
    phone,
    email,
  })

  if (!success) {
    throw new Error('Client not found or no permission')
  }
}

async function deleteClient(clientId: string) {
  const db = getDatabase()

  const success = await db.deleteClient(clientId)

  if (!success) {
    throw new Error('Client not found or no permission')
  }
}

// Alphabetical by last name (default)
const byLastName = await db.getClients({
  order_by: 'last_name',
  order_direction: 'asc',
})

// By first name
const byFirstName = await db.getClients({
  order_by: 'first_name',
  order_direction: 'asc',
})

// Newest first
const newest = await db.getClients({
  order_by: 'created_at',
  order_direction: 'desc',
})

// Minimal data for list views
const clientList = await db.getClients({
  fields: ['id', 'first_name', 'last_name'],
  limit: 100,
})

// With contact info
const withContacts = await db.getClients({
  fields: ['id', 'first_name', 'last_name', 'phone', 'email'],
  limit: 100,
})

import { formatClientName } from '#utils/clients'

const fullName = formatClientName(client)
// Returns: "John Doe"

import { formatPhoneNumber } from '#utils/clients'

const formatted = formatPhoneNumber('5551234567')
// Returns: "(555) 123-4567"

import { calculateAge } from '#utils/clients'

const age = calculateAge('1985-06-15')
// Returns: 39 (as of 2025-01-15)

import { formatAddress } from '#utils/clients'

const address = formatAddress(client)
// Returns: "123 Main St, San Francisco, CA 94102"
// Or partial: "San Francisco, CA" (if street missing)
// Or empty string if no address fields

async function getClientsPage(userId: string, page: number, pageSize: number = 10) {
  const db = getDatabase()
  const offset = (page - 1) * pageSize

  const [clients, totalCount] = await Promise.all([
    db.getClients({
      created_by: userId,
      limit: pageSize,
      offset,
      order_by: 'last_name',
    }),
    db.getClientsCount({ created_by: userId }),
  ])

  const totalPages = Math.ceil(totalCount / pageSize)

  return {
    clients,
    pagination: {
      currentPage: page,
      pageSize,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPrevPage: page > 1,
    },
  }
}

const result = await getClientsPage(userId, 1, 10)
console.log(`Page ${result.pagination.currentPage} of ${result.pagination.totalPages}`)
console.log(`Total clients: ${result.pagination.totalCount}`)
result.clients.forEach(client => {
  console.log(formatClientName(client))
})

type Props = {
  csrfToken: string // CSRF token for form submission
  client?: Client | null // For edit mode (optional)
  apiEndpoint?: string // Custom API endpoint (optional)
}

// Checkbox using Controller pattern
<Controller
  name="veteran_status"
  control={control}
  render={({ field }) => (
    <Checkbox
      id="veteran_status"
      label="Veteran Status"
      checked={field.value ?? false}
      onChange={field.onChange}
      size="lg"
    />
  )}
/>

type Props = {
  clients: Client[]
  lastVisits?: Record<string, string>
  view?: 'grid' | 'list'
  canEdit?: boolean
  emptyMessage?: string
  now?: Date | undefined
}

---
import { ClientsGrid, ClientsGridPagination, ClientsToolbar } from '#components/react/clients'
import { getDatabase } from '#libs/database'

const params = Astro.url.searchParams
const query = (params.get('q') ?? '').trim()
const view = params.get('view') === 'list' ? 'list' : 'grid'

const db = getDatabase()
const totalCount = await db.getClientsCount(query ? { search: query } : {})
const clients = await db.getClients({
  limit: 10,
  order_by: 'last_name',
  ...(query ? { search: query } : {}),
})
const lastVisits = await db.getClientLastVisits(clients.map(c => c.id))
---

<ClientsToolbar query={query} view={view} matchCount={totalCount} />
<ClientsGrid clients={clients} lastVisits={lastVisits} view={view} canEdit />
<ClientsGridPagination currentPage={1} totalPages={Math.ceil(totalCount / 10)} totalClients={totalCount} />

{
  "first_name": "John",
  "last_name": "Doe",
  "gender": "male",
  "phone": "5551234567",
  "email": "john@example.com",
  "csrfToken": "token-here"
}

{
  "success": true,
  "clientId": "uuid-string"
}

{
  "success": true,
  "client": {
    "id": "uuid",
    "first_name": "John",
    "last_name": "Doe",
    "gender": "male",
    "phone": "5551234567",
    "email": "john@example.com",
    "created_at": "2025-01-14T10:30:00Z",
    "updated_at": "2025-01-14T10:30:00Z"
  }
}

{
  "phone": "5559876543",
  "email": "newemail@example.com",
  "csrfToken": "token-here"
}

{
  "success": true,
  "clientId": "uuid-string"
}

// ✅ FAST - Minimal data transfer
const clients = await db.getClients({
  fields: ['id', 'first_name', 'last_name'],
  limit: 100,
})

// ⚠️ SLOWER - All fields returned
const clients = await db.getClients({
  limit: 100,
})

// ✅ GOOD - Limited result set
const clients = await db.getClients({
  limit: 10,
  offset: 0,
})

// ❌ BAD - Fetches all records
const allClients = await db.getClients({
  limit: 999999,
})

// ✅ FAST - Uses idx_clients_created_by
const myClients = await db.getClients({
  created_by: userId,
})

// ✅ FAST - Uses idx_clients_last_name
const sorted = await db.getClients({
  order_by: 'last_name',
})