採用データベース

:LiTarget: 用途

応募者・選考・面接記録の構造化データベース。

:LiSparkle: 特徴

• 応募者管理
• 選考フロー
• 面接記録
• 評価データ

:LiCode: 実コード（SCALE Base より自動抽出）

:LiInfo: lib/recruit-database.ts の中身そのもの。コピペ即可。

const isSupabaseConfigured = false; const supabase: any = null
import { mockCandidates, mockInterviews, mockEmailTemplates, mockDashboardStats } from './recruit-mock-data'
import type { Candidate, PipelineStageId, SourcePlatform, Interview, EmailTemplate, DashboardStats } from './recruit-types'

// ============================================
// Candidates
// ============================================

export async function getCandidates(filters?: {
  stage?: PipelineStageId | 'all'
  platform?: SourcePlatform | 'all'
  search?: string
  sortBy?: 'applied_at' | 'ai_screening_score'
}): Promise<Candidate[]> {
  if (!isSupabaseConfigured || !supabase) {
    let result = [...mockCandidates]
    if (filters?.stage && filters.stage !== 'all') {
      result = result.filter((c: any) => c.pipeline_stage === filters.stage)
    }
    if (filters?.platform && filters.platform !== 'all') {
      result = result.filter((c: any) => c.source_platform === filters.platform)
    }
    if (filters?.search) {
      const q = filters.search.toLowerCase()
      result = result.filter((c: any) =>
        c.name.toLowerCase().includes(q) ||
        c.email.toLowerCase().includes(q) ||
        c.phone.includes(q)
      )
    }
    result.sort((a, b) => {
      if (filters?.sortBy === 'ai_screening_score') {
        return (b.ai_screening_score ?? 0) - (a.ai_screening_score ?? 0)
      }
      return new Date(b.applied_at).getTime() - new Date(a.applied_at).getTime()
    })
    return result
  }

  let query = supabase.from('candidates').select('*, candidate_tags(*)')

  if (filters?.stage && filters.stage !== 'all') {
    query = query.eq('pipeline_stage', filters.stage)
  }
  if (filters?.platform && filters.platform !== 'all') {
    query = query.eq('source_platform', filters.platform)
  }
  if (filters?.search) {
    query = query.or(`name.ilike.%${filters.search}%,email.ilike.%${filters.search}%,phone.ilike.%${filters.search}%`)
  }
  if (filters?.sortBy === 'ai_screening_score') {
    query = query.order('ai_screening_score', { ascending: false, nullsFirst: false })
  } else {
    query = query.order('applied_at', { ascending: false })
  }

  const { data, error } = await query
  if (error) throw error
  return (data || []).map((d: any) => ({ ...d, tags: d.candidate_tags }))
}

export async function getCandidate(id: string): Promise<Candidate | null> {
  if (!isSupabaseConfigured || !supabase) {
    return mockCandidates.find(c => c.id === id) || null
  }

  const { data, error } = await supabase
    .from('candidates')
    .select('*, candidate_tags(*), candidate_notes(*)')
    .eq('id', id)
    .single()

  if (error) return null
  return { ...data, tags: data.candidate_tags, notes: data.candidate_notes }
}

export async function createCandidate(candidate: Partial<Candidate>): Promise<Candidate> {
  if (!isSupabaseConfigured || !supabase) {
    const newCandidate: Candidate = {
      id: crypto.randomUUID(),
      name: candidate.name || '',
      email: candidate.email || '',
      phone: candidate.phone || '',
      age: candidate.age || null,
      current_occupation: candidate.current_occupation || null,
      sales_experience_years: candidate.sales_experience_years || null,
      teleapo_experience: candidate.teleapo_experience || null,
      monthly_hours: candidate.monthly_hours || null,
      preferred_compensation: candidate.preferred_compensation || null,
      self_pr: candidate.self_pr || null,
      resume_url: candidate.resume_url || null,
      source_platform: candidate.source_platform || 'direct',
      source_url: candidate.source_url || null,
      utm_source: candidate.utm_source || null,
      utm_medium: candidate.utm_medium || null,
      ai_screening_score: null,
      ai_screening_reason: null,
      ai_matching_score: null,
      pipeline_stage: 'new',
      assigned_to: null,
      applied_at: new Date().toISOString(),
      created_at: new Date().toISOString(),
      updated_at: new Date().toISOString(),
    }
    mockCandidates.unshift(newCandidate)
    return newCandidate
  }

  const { data, error } = await supabase
    .from('candidates')
    .insert({
      ...candidate,
      pipeline_stage: 'new',
    })
    .select()
    .single()

  if (error) throw error
  return data
}

export async function updateCandidateStage(
  candidateId: string,
  newStage: PipelineStageId,
  reason?: string
): Promise<void> {
  if (!isSupabaseConfigured || !supabase) {
    const idx = mockCandidates.findIndex(c => c.id === candidateId)
    if (idx >= 0) {
      mockCandidates[idx].pipeline_stage = newStage
      mockCandidates[idx].updated_at = new Date().toISOString()
    }
    return
  }

  const { data: candidate } = await supabase
    .from('candidates')
    .select('pipeline_stage')
    .eq('id', candidateId)
    .single()

  const fromStage = candidate?.pipeline_stage || 'new'

  await supabase
    .from('candidates')
    .update({ pipeline_stage: newStage })
    .eq('id', candidateId)

  await supabase.from('stage_transitions').insert({
    candidate_id: candidateId,
    from_stage: fromStage,
    to_stage: newStage,
    reason: reason || null,
  })
}

export async function addCandidateNote(
  candidateId: string,
  content: string,
  authorId?: string
): Promise<void> {
  if (!isSupabaseConfigured || !supabase) {
    const candidate = mockCandidates.find(c => c.id === candidateId)
    if (candidate) {
      if (!candidate.notes) candidate.notes = []
      candidate.notes.push({
        id: crypto.randomUUID(),
        candidate_id: candidateId,
        author_id: authorId || 'demo',
        author_name: '大串',
        content,
        created_at: new Date().toISOString(),
      })
    }
    return
  }

  await supabase.from('candidate_notes').insert({
    candidate_id: candidateId,
    author_id: authorId,
    content,
  })
}

// ============================================
// Interviews
// ============================================

export async function getInterviews(): Promise<Interview[]> {
  if (!isSupabaseConfigured || !supabase) {
    return mockInterviews
  }

  const { data, error } = await supabase
    .from('interviews')
    .select('*, candidates(name), users(name)')
    .order('scheduled_at', { ascending: true })

  if (error) throw error
  return (data || []).map((d: any) => ({
    ...d,
    candidate_name: d.candidates?.name,
    interviewer_name: d.users?.name,
  }))
}

// ============================================
// Email Templates
// ============================================

export async function getEmailTemplates(): Promise<EmailTemplate[]> {
  if (!isSupabaseConfigured || !supabase) {
    return mockEmailTemplates
  }

  const { data, error } = await supabase
    .from('email_templates')
    .select('*')
    .order('created_at', { ascending: false })

  if (error) throw error
  return data || []
}

export async function createEmailTemplate(template: Partial<EmailTemplate>): Promise<EmailTemplate> {
  if (!isSupabaseConfigured || !supabase) {
    const newTemplate: EmailTemplate = {
      id: crypto.randomUUID(),
      name: template.name || '',
      type: template.type || 'auto_reply',
      subject: template.subject || '',
      body: template.body || '',
      variables_json: template.variables_json || {},
      usage_count: 0,
      created_at: new Date().toISOString(),
    }
    mockEmailTemplates.push(newTemplate)
    return newTemplate
  }

  const { data, error } = await supabase
    .from('email_templates')
    .insert(template)
    .select()
    .single()

  if (error) throw error
  return data
}

// ============================================
// Dashboard Stats
// ============================================

export async function getDashboardStats(): Promise<DashboardStats> {
  if (!isSupabaseConfigured || !supabase) {
    return mockDashboardStats
  }

  // Real implementation would aggregate from DB
  const { data: candidates } = await supabase.from('candidates').select('*')
  const allCandidates = candidates || []

  const now = new Date()
  const weekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000)
  const monthStart = new Date(now.getFullYear(), now.getMonth(), 1)

  const pipelineByStage: Record<string, number> = {}
  allCandidates.forEach((c: any) => {
    pipelineByStage[c.pipeline_stage] = (pipelineByStage[c.pipeline_stage] || 0) + 1
  })

  return {
    total_candidates: allCandidates.length,
    new_this_week: allCandidates.filter((c: any) => new Date(c.applied_at) >= weekAgo).length,
    interviews_this_week: 0, // would need interviews table
    offers_this_month: allCandidates.filter((c: any) =>
      c.pipeline_stage === 'offer' && new Date(c.updated_at) >= monthStart
    ).length,
    avg_screening_score: allCandidates.reduce((sum: number, c: any) => sum + (c.ai_screening_score || 0), 0) / Math.max(allCandidates.length, 1),
    pipeline_by_stage: pipelineByStage as DashboardStats['pipeline_by_stage'],
    funnel: mockDashboardStats.funnel, // simplified
    platform_performance: mockDashboardStats.platform_performance, // simplified
  }
}

:LiFolder: ソースファイルのパス

/Users/oogushiyuuki/Library/CloudStorage/GoogleDrive-y-ogushi@scale-group.co.jp/マイドライブ/AI/scale-base/lib/recruit-database.ts

:LiHandPointer: 使い方

対象プロジェクトに該当ファイルをコピーして、props を流し込むだけ。

:LiAlertCircle: 注意事項

• 依存パッケージを忘れず追加