Quickback API

import { feature, q } from "@quickback/compiler";

export default feature("candidates", {
  columns: {
    id:             q.id(),
    name:           q.text().required(),
    email:          q.text().required(),
    phone:          q.text().optional(),
    resumeUrl:      q.text().optional(),
    source:         q.enum(["linkedin", "referral", "careers-page", "other"]).default("other").required(),
    internalNotes:  q.text().optional(),
    organizationId: q.scope("organization"),
  },
  // firewall auto-derived from q.scope("organization") + audit-injected deletedAt
  guards: {
    createable: ["name", "email", "phone", "resumeUrl", "source", "internalNotes"],
    updatable:  ["name", "email", "phone", "resumeUrl", "source", "internalNotes"],
  },
  masking: {
    email:         { type: "email", show: { roles: ["owner", "admin"] } },
    phone:         { type: "phone", show: { roles: ["owner", "admin"] } },
    internalNotes: { type: "redact", show: { roles: ["owner", "admin"] } },
  },
  read: {
    access: { roles: ["owner", "admin", "member"] },
    views: {
      pipeline: {
        fields: ["id", "name", "source"],
        access: { roles: ["owner", "admin", "member"] },
      },
      full: {
        fields: ["id", "name", "email", "phone", "resumeUrl", "source", "internalNotes"],
        access: { roles: ["owner", "admin"] },
      },
    },
  },
  crud: {
    create: { access: { roles: ["owner", "admin"] } },
    update: { access: { roles: ["owner", "admin"] } },
    delete: { access: { roles: ["owner"] }, mode: "soft" },
  },
});

/**
 * Generated by quickback.dev
 *
 * This file is managed by the Quickback Compiler — do not edit directly.
 * To make changes, update your config and feature definitions in the quickback/ folder,
 * then run `quickback compile` to regenerate.
 */
import { Hono } from 'hono';
import { eq, and, or, gt, gte, lt, lte, ne, like, inArray, asc, desc, count, type SQL } from 'drizzle-orm';
import { candidates } from './schema';
import type { AppContext } from '../../lib/types';
import type { CloudflareBindings } from '../../env';
import { getOrgMemberRole } from '../../lib/org-access';
import { 
  buildFirewallConditions,
  validateCreate,
  validateUpdate,
  maskCandidate,
  maskCandidates,
  VIEWS_CONFIG,
  CRUD_ACCESS
} from './candidates.resource';
import { jsonWithEtag } from '../../lib/etag';
import { evaluateAccess } from '../../lib/access';
import { AuthErrors, FirewallErrors, AccessErrors, GuardErrors, BatchErrors } from '../../lib/errors';
import { z } from 'zod';
import { parseJsonWithSchema } from '../../lib/request-validation';

// Helper function to parse filter values from query strings
function parseFilterValue(value: string): string | number | boolean {
  // Boolean
  if (value === 'true') return true;
  if (value === 'false') return false;

  // Number
  const num = Number(value);
  if (!isNaN(num) && value !== '') return num;

  // Date (ISO format) - return as string for SQL comparison
  if (/^\d{4}-\d{2}-\d{2}/.test(value)) return value;

  // Default to string
  return value;
}

// Request body schemas — derived from the q DSL column Zods.
const CreateBodySchema = z.object({
  name: z.string(),
  email: z.string(),
  phone: z.string().optional(),
  resumeUrl: z.string().optional(),
  source: z.enum(["linkedin", "referral", "careers-page", "other"] as const).optional(),
  internalNotes: z.string().optional(),
}).passthrough();
const UpdateBodySchema = z.object({
  name: z.string().optional(),
  email: z.string().optional(),
  phone: z.string().optional(),
  resumeUrl: z.string().optional(),
  source: z.enum(["linkedin", "referral", "careers-page", "other"] as const).optional(),
  internalNotes: z.string().optional(),
}).passthrough();
const BatchOptionsSchema = z.object({ atomic: z.boolean() }).passthrough().optional();
const BatchCreateBodySchema = z.object({ records: z.array(CreateBodySchema), options: BatchOptionsSchema }).passthrough();
const BatchUpdateBodySchema = z.object({ records: z.array(UpdateBodySchema), options: BatchOptionsSchema }).passthrough();
const BatchDeleteBodySchema = z.object({ ids: z.array(z.string()), options: BatchOptionsSchema }).passthrough();

const app = new Hono<{ Bindings: CloudflareBindings; Variables: { ctx: AppContext; db: any; services: any; authDb: any } }>();

// ═══════════════════════════════════════════════════════
// VIEW: GET /candidates/views/pipeline
// Fields: id, name, source
// Supports: ?limit=10&offset=0&sort=createdAt,-name&total=true&search=text&field=value&field.gt=100
// ═══════════════════════════════════════════════════════
app.get('/views/pipeline', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  // View field configuration
  const viewFields = ["id","name","source"];

  // Parse query parameters
  const url = new URL(c.req.url);
  const params = Object.fromEntries(url.searchParams.entries());

  // Get organization ID from query param or fall back to session's active org
  const requestedOrgId = params.organizationId || ctx.activeOrgId;
  if (!requestedOrgId) {
    return c.json({
      error: 'Organization required',
      code: 'ORG_REQUIRED',
      hint: 'Pass organizationId query param or set active organization in session'
    }, 400);
  }

  // Use the requested org for access checks
  let effectiveCtx = params.organizationId ? { ...ctx, activeOrgId: requestedOrgId } : ctx;

  if (params.organizationId && ctx.authenticated) {
    if (ctx.userRole !== 'sysadmin') {
      const authDb = c.get('authDb');
      if (!authDb) {
        return c.json({
          error: 'Auth database unavailable',
          code: 'AUTH_DB_UNAVAILABLE',
          hint: 'Set authDb in request context to validate organization membership'
        }, 500);
      }
      const memberRole = await getOrgMemberRole(authDb, ctx.userId!, requestedOrgId);
      if (!memberRole) {
        return c.json(AccessErrors.roleRequired(['owner', 'admin', 'member'], ctx.roles), 403);
      }
      effectiveCtx = { ...effectiveCtx, roles: [memberRole] };
    }
  }

  // Access check
  if (!await evaluateAccess(VIEWS_CONFIG['pipeline'].access, effectiveCtx)) {
    return c.json(AccessErrors.roleRequired((VIEWS_CONFIG['pipeline'].access as any).roles || [], ctx.roles), 403);
  }

  // Pagination
  const limit = Math.min(Math.max(parseInt(params.limit) || 50, 1), 100);
  const offset = Math.max(parseInt(params.offset) || 0, 0);

  // Multi-sort: ?sort=createdAt,-name (comma-separated, - prefix = desc)
  // Backwards compatible: ?sort=createdAt&order=desc still works
  const sortOrders: { column: any; direction: typeof asc }[] = [];
  const sortParam = params.sort || 'createdAt';
  if (sortParam.includes(',') || sortParam.startsWith('-')) {
    // New multi-sort format
    for (const part of sortParam.split(',')) {
      const trimmed = part.trim();
      if (!trimmed) continue;
      const isDesc = trimmed.startsWith('-');
      const fieldName = isDesc ? trimmed.slice(1) : trimmed;
      if (fieldName in candidates) {
        const col = candidates[fieldName as keyof typeof candidates] as any;
        if (typeof col === 'object' && col) {
          sortOrders.push({ column: col, direction: isDesc ? desc : asc });
        }
      }
    }
  } else {
    // Legacy single-sort format: ?sort=field&order=desc
    const sortOrder = params.order === 'asc' ? asc : desc;
    if (sortParam in candidates) {
      const col = candidates[sortParam as keyof typeof candidates] as any;
      if (typeof col === 'object' && col) {
        sortOrders.push({ column: col, direction: sortOrder });
      }
    }
  }

  // Build filter conditions
  const filterConditions: SQL[] = [];
  const firewallCond = buildFirewallConditions(effectiveCtx);
  if (firewallCond) filterConditions.push(firewallCond);

  // Process filters and operators
  const reservedParams = new Set(['limit', 'offset', 'sort', 'order', 'organizationId', 'search']);
  for (const [key, value] of Object.entries(params)) {
    if (reservedParams.has(key)) continue;

    // Check for operator suffix (field.gt, field.lt, etc)
    const [field, op] = key.split('.');
    if (!(field in candidates)) continue;

    const column = candidates[field as keyof typeof candidates] as any;
    if (typeof column !== 'object' || !column) continue;

    switch (op) {
      case 'gt':
        filterConditions.push(gt(column, parseFilterValue(value)));
        break;
      case 'gte':
        filterConditions.push(gte(column, parseFilterValue(value)));
        break;
      case 'lt':
        filterConditions.push(lt(column, parseFilterValue(value)));
        break;
      case 'lte':
        filterConditions.push(lte(column, parseFilterValue(value)));
        break;
      case 'ne':
        filterConditions.push(ne(column, parseFilterValue(value)));
        break;
      case 'like':
        filterConditions.push(like(column, `%${value}%`));
        break;
      case 'in':
        filterConditions.push(inArray(column, value.split(',')));
        break;
      case undefined:
        // No operator = exact match
        filterConditions.push(eq(column, parseFilterValue(value)));
        break;
    }
  }

  // Search: ?search=text (OR'd LIKE across text columns)
  const searchableColumns = ["name","email","phone","resumeUrl","source","internalNotes"];
  if (params.search && searchableColumns.length > 0) {
    const searchTerm = `%${params.search}%`;
    const searchConditions = searchableColumns
      .filter((col: string) => col in candidates)
      .map((col: string) => like(candidates[col as keyof typeof candidates] as any, searchTerm));
    if (searchConditions.length > 0) {
      filterConditions.push(or(...searchConditions)!);
    }
  }

  // Build select object for only the view fields
  const selectFields: Record<string, any> = {};
  for (const field of viewFields) {
    if (field in candidates) {
      selectFields[field] = candidates[field as keyof typeof candidates];
    }
  }

  // Build and execute query
  let query = db.select(selectFields).from(candidates);

  if (filterConditions.length > 0) {
    query = query.where(and(...filterConditions));
  }

  // Apply sorting
  if (sortOrders.length > 0) {
    query = query.orderBy(...sortOrders.map(s => s.direction(s.column)));
  }

  // Total count (always included for pagination)
  let countQuery = db.select({ value: count() }).from(candidates);
  if (filterConditions.length > 0) {
    countQuery = countQuery.where(and(...filterConditions));
  }
  const [countResult] = await countQuery;
  const total = countResult?.value ?? 0;

  // Apply pagination
  query = query.limit(limit).offset(offset);

  const results = await query;

  // Return with pagination metadata
  const totalPages = Math.ceil(total / limit);
  const currentPage = Math.floor(offset / limit) + 1;
  const pagination = {
    total,
    count: results.length,
    page: currentPage,
    pageSize: limit,
    totalPages,
    hasMore: currentPage < totalPages,
  };

  return jsonWithEtag(c, {
    data: maskCandidates(results, ctx),
    view: 'pipeline',
    fields: viewFields,
    pagination,
  });
});

// ═══════════════════════════════════════════════════════
// VIEW: GET /candidates/views/full
// Fields: id, name, email, phone, resumeUrl, source, internalNotes
// Supports: ?limit=10&offset=0&sort=createdAt,-name&total=true&search=text&field=value&field.gt=100
// ═══════════════════════════════════════════════════════
app.get('/views/full', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  // View field configuration
  const viewFields = ["id","name","email","phone","resumeUrl","source","internalNotes"];

  // Parse query parameters
  const url = new URL(c.req.url);
  const params = Object.fromEntries(url.searchParams.entries());

  // Get organization ID from query param or fall back to session's active org
  const requestedOrgId = params.organizationId || ctx.activeOrgId;
  if (!requestedOrgId) {
    return c.json({
      error: 'Organization required',
      code: 'ORG_REQUIRED',
      hint: 'Pass organizationId query param or set active organization in session'
    }, 400);
  }

  // Use the requested org for access checks
  let effectiveCtx = params.organizationId ? { ...ctx, activeOrgId: requestedOrgId } : ctx;

  if (params.organizationId && ctx.authenticated) {
    if (ctx.userRole !== 'sysadmin') {
      const authDb = c.get('authDb');
      if (!authDb) {
        return c.json({
          error: 'Auth database unavailable',
          code: 'AUTH_DB_UNAVAILABLE',
          hint: 'Set authDb in request context to validate organization membership'
        }, 500);
      }
      const memberRole = await getOrgMemberRole(authDb, ctx.userId!, requestedOrgId);
      if (!memberRole) {
        return c.json(AccessErrors.roleRequired(['owner', 'admin', 'member'], ctx.roles), 403);
      }
      effectiveCtx = { ...effectiveCtx, roles: [memberRole] };
    }
  }

  // Access check
  if (!await evaluateAccess(VIEWS_CONFIG['full'].access, effectiveCtx)) {
    return c.json(AccessErrors.roleRequired((VIEWS_CONFIG['full'].access as any).roles || [], ctx.roles), 403);
  }

  // Pagination
  const limit = Math.min(Math.max(parseInt(params.limit) || 50, 1), 100);
  const offset = Math.max(parseInt(params.offset) || 0, 0);

  // Multi-sort: ?sort=createdAt,-name (comma-separated, - prefix = desc)
  // Backwards compatible: ?sort=createdAt&order=desc still works
  const sortOrders: { column: any; direction: typeof asc }[] = [];
  const sortParam = params.sort || 'createdAt';
  if (sortParam.includes(',') || sortParam.startsWith('-')) {
    // New multi-sort format
    for (const part of sortParam.split(',')) {
      const trimmed = part.trim();
      if (!trimmed) continue;
      const isDesc = trimmed.startsWith('-');
      const fieldName = isDesc ? trimmed.slice(1) : trimmed;
      if (fieldName in candidates) {
        const col = candidates[fieldName as keyof typeof candidates] as any;
        if (typeof col === 'object' && col) {
          sortOrders.push({ column: col, direction: isDesc ? desc : asc });
        }
      }
    }
  } else {
    // Legacy single-sort format: ?sort=field&order=desc
    const sortOrder = params.order === 'asc' ? asc : desc;
    if (sortParam in candidates) {
      const col = candidates[sortParam as keyof typeof candidates] as any;
      if (typeof col === 'object' && col) {
        sortOrders.push({ column: col, direction: sortOrder });
      }
    }
  }

  // Build filter conditions
  const filterConditions: SQL[] = [];
  const firewallCond = buildFirewallConditions(effectiveCtx);
  if (firewallCond) filterConditions.push(firewallCond);

  // Process filters and operators
  const reservedParams = new Set(['limit', 'offset', 'sort', 'order', 'organizationId', 'search']);
  for (const [key, value] of Object.entries(params)) {
    if (reservedParams.has(key)) continue;

    // Check for operator suffix (field.gt, field.lt, etc)
    const [field, op] = key.split('.');
    if (!(field in candidates)) continue;

    const column = candidates[field as keyof typeof candidates] as any;
    if (typeof column !== 'object' || !column) continue;

    switch (op) {
      case 'gt':
        filterConditions.push(gt(column, parseFilterValue(value)));
        break;
      case 'gte':
        filterConditions.push(gte(column, parseFilterValue(value)));
        break;
      case 'lt':
        filterConditions.push(lt(column, parseFilterValue(value)));
        break;
      case 'lte':
        filterConditions.push(lte(column, parseFilterValue(value)));
        break;
      case 'ne':
        filterConditions.push(ne(column, parseFilterValue(value)));
        break;
      case 'like':
        filterConditions.push(like(column, `%${value}%`));
        break;
      case 'in':
        filterConditions.push(inArray(column, value.split(',')));
        break;
      case undefined:
        // No operator = exact match
        filterConditions.push(eq(column, parseFilterValue(value)));
        break;
    }
  }

  // Search: ?search=text (OR'd LIKE across text columns)
  const searchableColumns = ["name","email","phone","resumeUrl","source","internalNotes"];
  if (params.search && searchableColumns.length > 0) {
    const searchTerm = `%${params.search}%`;
    const searchConditions = searchableColumns
      .filter((col: string) => col in candidates)
      .map((col: string) => like(candidates[col as keyof typeof candidates] as any, searchTerm));
    if (searchConditions.length > 0) {
      filterConditions.push(or(...searchConditions)!);
    }
  }

  // Build select object for only the view fields
  const selectFields: Record<string, any> = {};
  for (const field of viewFields) {
    if (field in candidates) {
      selectFields[field] = candidates[field as keyof typeof candidates];
    }
  }

  // Build and execute query
  let query = db.select(selectFields).from(candidates);

  if (filterConditions.length > 0) {
    query = query.where(and(...filterConditions));
  }

  // Apply sorting
  if (sortOrders.length > 0) {
    query = query.orderBy(...sortOrders.map(s => s.direction(s.column)));
  }

  // Total count (always included for pagination)
  let countQuery = db.select({ value: count() }).from(candidates);
  if (filterConditions.length > 0) {
    countQuery = countQuery.where(and(...filterConditions));
  }
  const [countResult] = await countQuery;
  const total = countResult?.value ?? 0;

  // Apply pagination
  query = query.limit(limit).offset(offset);

  const results = await query;

  // Return with pagination metadata
  const totalPages = Math.ceil(total / limit);
  const currentPage = Math.floor(offset / limit) + 1;
  const pagination = {
    total,
    count: results.length,
    page: currentPage,
    pageSize: limit,
    totalPages,
    hasMore: currentPage < totalPages,
  };

  return jsonWithEtag(c, {
    data: maskCandidates(results, ctx),
    view: 'full',
    fields: viewFields,
    pagination,
  });
});

// ═══════════════════════════════════════════════════════
// LIST: GET /candidates
// Supports: ?limit=10&offset=0&sort=createdAt,-name&fields=id,name&total=true&search=text
// Also supports: ?organizationId=xxx to query a specific org (user must be a member)
// ═══════════════════════════════════════════════════════
app.get('/', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  // Parse query parameters
  const url = new URL(c.req.url);
  const params = Object.fromEntries(url.searchParams.entries());

  // Get organization ID from query param or fall back to session's active org
  const requestedOrgId = params.organizationId || ctx.activeOrgId;
  if (!requestedOrgId) {
    return c.json({
      error: 'Organization required',
      code: 'ORG_REQUIRED',
      hint: 'Pass organizationId query param or set active organization in session'
    }, 400);
  }

  // Use the requested org for access checks
  let effectiveCtx = params.organizationId ? { ...ctx, activeOrgId: requestedOrgId } : ctx;

  if (params.organizationId && ctx.authenticated) {
    if (ctx.userRole !== 'sysadmin') {
      const authDb = c.get('authDb');
      if (!authDb) {
        return c.json({
          error: 'Auth database unavailable',
          code: 'AUTH_DB_UNAVAILABLE',
          hint: 'Set authDb in request context to validate organization membership'
        }, 500);
      }
      const memberRole = await getOrgMemberRole(authDb, ctx.userId!, requestedOrgId);
      if (!memberRole) {
        return c.json(AccessErrors.roleRequired(['owner', 'admin', 'member'], ctx.roles), 403);
      }
      effectiveCtx = { ...effectiveCtx, roles: [memberRole] };
    }
  }

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.list.access, effectiveCtx)) {
    return c.json(AccessErrors.roleRequired((CRUD_ACCESS.list.access as any).roles || [], effectiveCtx.roles, (CRUD_ACCESS.list.access as any).userRole, effectiveCtx.userRole), 403);
  }

  // Pagination
  const limit = Math.min(Math.max(parseInt(params.limit) || 50, 1), 100);
  const offset = Math.max(parseInt(params.offset) || 0, 0);

  // Multi-sort: ?sort=createdAt,-name (comma-separated, - prefix = desc)
  // Backwards compatible: ?sort=createdAt&order=desc still works
  const sortOrders: { column: any; direction: typeof asc }[] = [];
  const sortParam = params.sort || 'createdAt';
  if (sortParam.includes(',') || sortParam.startsWith('-')) {
    // New multi-sort format
    for (const part of sortParam.split(',')) {
      const trimmed = part.trim();
      if (!trimmed) continue;
      const isDesc = trimmed.startsWith('-');
      const fieldName = isDesc ? trimmed.slice(1) : trimmed;
      if (fieldName in candidates) {
        const col = candidates[fieldName as keyof typeof candidates] as any;
        if (typeof col === 'object' && col) {
          sortOrders.push({ column: col, direction: isDesc ? desc : asc });
        }
      }
    }
  } else {
    // Legacy single-sort format: ?sort=field&order=desc
    const sortOrder = params.order === 'asc' ? asc : desc;
    if (sortParam in candidates) {
      const col = candidates[sortParam as keyof typeof candidates] as any;
      if (typeof col === 'object' && col) {
        sortOrders.push({ column: col, direction: sortOrder });
      }
    }
  }

  // Field selection: ?fields=id,name,email
  let selectFields: Record<string, any> | undefined;
  if (params.fields) {
    const requested: Record<string, any> = {};
    for (const f of params.fields.split(',')) {
      const fieldName = f.trim();
      if (fieldName && fieldName in candidates) {
        requested[fieldName] = candidates[fieldName as keyof typeof candidates];
      }
    }
    if (Object.keys(requested).length > 0) {
      selectFields = requested;
    }
  }

  // Build filter conditions
  const filterConditions: SQL[] = [];
  const firewallCond = buildFirewallConditions(effectiveCtx);
  if (firewallCond) filterConditions.push(firewallCond);

  // Process filters and operators
  // Note: organizationId is handled by firewall conditions when hasFirewall=true
  const reservedParams = new Set(['limit', 'offset', 'sort', 'order', 'organizationId', 'fields', 'search']);
  for (const [key, value] of Object.entries(params)) {
    if (reservedParams.has(key)) continue;

    // Check for operator suffix (field.gt, field.lt, etc)
    const [field, op] = key.split('.');
    if (!(field in candidates)) continue;

    const column = candidates[field as keyof typeof candidates] as any;
    if (typeof column !== 'object' || !column) continue;

    switch (op) {
      case 'gt':
        filterConditions.push(gt(column, parseFilterValue(value)));
        break;
      case 'gte':
        filterConditions.push(gte(column, parseFilterValue(value)));
        break;
      case 'lt':
        filterConditions.push(lt(column, parseFilterValue(value)));
        break;
      case 'lte':
        filterConditions.push(lte(column, parseFilterValue(value)));
        break;
      case 'ne':
        filterConditions.push(ne(column, parseFilterValue(value)));
        break;
      case 'like':
        filterConditions.push(like(column, `%${value}%`));
        break;
      case 'in':
        filterConditions.push(inArray(column, value.split(',')));
        break;
      case undefined:
        // No operator = exact match
        filterConditions.push(eq(column, parseFilterValue(value)));
        break;
    }
  }

  // Search: ?search=text (OR'd LIKE across text columns)
  const searchableColumns = ["name","email","phone","resumeUrl","source","internalNotes"];
  if (params.search && searchableColumns.length > 0) {
    const searchTerm = `%${params.search}%`;
    const searchConditions = searchableColumns
      .filter((col: string) => col in candidates)
      .map((col: string) => like(candidates[col as keyof typeof candidates] as any, searchTerm));
    if (searchConditions.length > 0) {
      filterConditions.push(or(...searchConditions)!);
    }
  }

  // Build and execute query
  let query = selectFields ? db.select(selectFields).from(candidates) : db.select().from(candidates);

  if (filterConditions.length > 0) {
    query = query.where(and(...filterConditions));
  }

  // Apply sorting
  if (sortOrders.length > 0) {
    query = query.orderBy(...sortOrders.map(s => s.direction(s.column)));
  }

  // Total count (always included for pagination)
  let countQuery = db.select({ value: count() }).from(candidates);
  if (filterConditions.length > 0) {
    countQuery = countQuery.where(and(...filterConditions));
  }
  const [countResult] = await countQuery;
  const total = countResult?.value ?? 0;

  // Apply pagination
  query = query.limit(limit).offset(offset);

  const results = await query;

  // Return with pagination metadata
  const totalPages = Math.ceil(total / limit);
  const currentPage = Math.floor(offset / limit) + 1;
  const pagination = {
    total,
    count: results.length,
    page: currentPage,
    pageSize: limit,
    totalPages,
    hasMore: currentPage < totalPages,
  };

  return jsonWithEtag(c, {
    data: maskCandidates(results, ctx),
    pagination,
  });
});

// ═══════════════════════════════════════════════════════
// BATCH CREATE: POST /candidates/batch
// ═══════════════════════════════════════════════════════
app.post('/batch', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  // Access check
  const batchCreateAccess = {"roles":["owner","admin"]};
  if (!await evaluateAccess(batchCreateAccess, ctx)) {
    return c.json(AccessErrors.roleRequired((batchCreateAccess as any).roles || [], ctx.roles), 403);
  }

  if (!ctx.activeOrgId) {
    return c.json({
      error: 'Organization required',
      code: 'ORG_REQUIRED',
      hint: 'Set active organization in session before creating records'
    }, 400);
  }
const parseResult = await parseJsonWithSchema(c.req.raw, BatchCreateBodySchema);
  if (!parseResult.ok) {
    return c.json({ error: parseResult.error, code: parseResult.code, layer: 'validation', fields: parseResult.fields }, 400);
  }
  const body = parseResult.data as any;
  const records = body.records || [];
  const options = body.options || {};
  const atomic = options.atomic === true;

  // Validate batch size
  if (records.length > 100) {
    return c.json(BatchErrors.sizeExceeded(100, records.length), 400);
  }

  // Empty batch - return empty success
  if (records.length === 0) {
    return c.json({
      success: [],
      errors: [],
      meta: { total: 0, succeeded: 0, failed: 0, atomic: false }
    }, 200);
  }

  const now = new Date().toISOString();
  const validRecords: any[] = [];
  const errors: any[] = [];

  // Process each record
  for (let i = 0; i < records.length; i++) {
    const record = records[i];

      // Guards validation
      const validation = validateCreate(record);
      if (!validation.valid) {
        // Prioritize error type: system-managed > protected > not createable
        let error: any;
        if (validation.systemManaged.length > 0) {
          error = GuardErrors.systemManaged(validation.systemManaged);
        } else if (validation.protected.length > 0) {
          const firstProtected = validation.protected[0];
          error = GuardErrors.fieldProtected([firstProtected.field], firstProtected.actions);
        } else if (validation.notCreateable.length > 0) {
          error = GuardErrors.fieldNotCreateable(validation.notCreateable);
        }
        errors.push({ index: i, record, error });
        continue;
      }

    // Apply defaults, ownership, computed fields, and audit fields
    const data = {
      id: 'cnd_' + crypto.randomUUID().replace(/-/g, ''),
      ...record,
      ...{},
    organizationId: ctx.activeOrgId,
    createdAt: now,
    modifiedAt: now,
    };

    validRecords.push(data);
  }

  // If atomic mode and any errors, fail the entire batch
  if (atomic && errors.length > 0) {
    return c.json(BatchErrors.atomicFailed(errors[0].index, errors[0].error), 400);
  }

  let success: any[] = [];

  // Execute bulk insert if we have valid records
  if (validRecords.length > 0) {
    try {
      success = await db.insert(candidates).values(validRecords).returning();
    } catch (error: any) {
      // Search full error chain (D1/Drizzle may nest the real error in cause)
      const errMsg = [error?.message, error?.cause?.message, String(error)].filter(Boolean).join(' ');
      const isNotNull = errMsg.includes('NOT NULL constraint failed');
      const isUnique = errMsg.includes('UNIQUE constraint failed');
      const code = isNotNull ? 'NOT_NULL_VIOLATION' : isUnique ? 'UNIQUE_VIOLATION' : 'INSERT_FAILED';
      const label = isNotNull ? 'Missing required field' : isUnique ? 'Duplicate value' : 'Database insert failed';

      if (atomic) {
        return c.json({
          error: label,
          layer: 'database',
          code: 'BATCH_' + code,
        }, 400);
      }
      for (let i = 0; i < validRecords.length; i++) {
        errors.push({
          index: i,
          record: records[i],
          error: { error: label, layer: 'database', code }
        });
      }
    }
  }

  // Apply masking to success array
  const maskedSuccess = maskCandidates(success, ctx);

  const meta = {
    total: records.length,
    succeeded: success.length,
    failed: errors.length,
    atomic
  };

  const statusCode = errors.length > 0 ? 207 : 201;
  return c.json({ success: maskedSuccess, errors, meta }, statusCode);
});

// ═══════════════════════════════════════════════════════
// BATCH UPDATE: PATCH /candidates/batch
// ═══════════════════════════════════════════════════════
app.patch('/batch', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  const parseResult = await parseJsonWithSchema(c.req.raw, BatchUpdateBodySchema);
  if (!parseResult.ok) {
    return c.json({ error: parseResult.error, code: parseResult.code, layer: 'validation', fields: parseResult.fields }, 400);
  }
  const body = parseResult.data as any;
  const records = body.records || [];
  const options = body.options || {};
  const atomic = options.atomic === true;

  // Validate batch size
  if (records.length > 100) {
    return c.json(BatchErrors.sizeExceeded(100, records.length), 400);
  }

  // Empty batch - return empty success
  if (records.length === 0) {
    return c.json({
      success: [],
      errors: [],
      meta: { total: 0, succeeded: 0, failed: 0, atomic: false }
    }, 200);
  }

  // Validate all records have IDs
  const missingIds: number[] = [];
  for (let i = 0; i < records.length; i++) {
    if (!records[i].id) {
      missingIds.push(i);
    }
  }
  if (missingIds.length > 0) {
    return c.json(BatchErrors.missingIds(missingIds), 400);
  }

  // Extract IDs for batch fetch
  const ids = records.map((r: any) => r.id);

  // Batch fetch existing records with firewall
  const existingRecords = await db.select().from(candidates)
    .where(and(buildFirewallConditions(ctx), inArray(candidates.id, ids)));

  // Create lookup map for O(1) access
  const recordMap = new Map<string, any>();
  for (const record of existingRecords) {
    recordMap.set(record.id, record);
  }

  const now = new Date().toISOString();
  const errors: any[] = [];
  const success: any[] = [];
  const ownershipFields = ["organizationId"];

  // Process each record
  for (let i = 0; i < records.length; i++) {
    const record = records[i];
    const existingRecord = recordMap.get(record.id);

    // Check if record exists
    if (!existingRecord) {
      errors.push({
        index: i,
        record,
        error: { ...FirewallErrors.notFound(), details: { id: record.id } }
      });
      continue;
    }

        // Default: Authenticated access required
    if (!ctx.authenticated) {
      errors.push({
        index: i,
        record,
        error: { error: 'Unauthorized', code: 'UNAUTHORIZED' }
      });
      continue;
    }


      // Block ownership scope reassignment (system-managed by auth context)
      const attemptedOwnershipFields = ownershipFields.filter((field) => Object.prototype.hasOwnProperty.call(record, field));
      if (attemptedOwnershipFields.length > 0) {
        errors.push({
          index: i,
          record,
          error: GuardErrors.systemManaged(attemptedOwnershipFields)
        });
        continue;
      }
      // Guards validation
      const validation = validateUpdate(record);
      if (!validation.valid) {
        // Prioritize error type: system-managed > immutable > protected > not updatable
        let error: any;
        if (validation.systemManaged.length > 0) {
          error = GuardErrors.systemManaged(validation.systemManaged);
        } else if (validation.immutable.length > 0) {
          error = GuardErrors.fieldImmutable(validation.immutable);
        } else if (validation.protected.length > 0) {
          const firstProtected = validation.protected[0];
          error = GuardErrors.fieldProtected([firstProtected.field], firstProtected.actions);
        } else if (validation.notUpdatable.length > 0) {
          error = GuardErrors.fieldNotUpdatable(validation.notUpdatable);
        }
        errors.push({ index: i, record, error });
        continue;
      }

    // Apply audit fields
    const data = {
      ...record,
    modifiedAt: new Date().toISOString(),
    };

    // Execute update
    try {
      const result = await db.update(candidates)
        .set(data)
        .where(and(buildFirewallConditions(ctx), eq(candidates.id, record.id)))
        .returning();

      if (result[0]) {
        success.push(result[0]);
      }
    } catch (error: any) {
      const errMsg = [error?.message, error?.cause?.message, String(error)].filter(Boolean).join(' ');
      const isNotNull = errMsg.includes('NOT NULL constraint failed');
      const isUnique = errMsg.includes('UNIQUE constraint failed');
      const code = isNotNull ? 'NOT_NULL_VIOLATION' : isUnique ? 'UNIQUE_VIOLATION' : 'UPDATE_FAILED';
      const label = isNotNull ? 'Missing required field' : isUnique ? 'Duplicate value' : 'Update failed';
      errors.push({
        index: i,
        record,
        error: { error: label, layer: 'database', code }
      });

      // In atomic mode, fail fast
      if (atomic) {
        return c.json(BatchErrors.atomicFailed(i, errors[errors.length - 1].error), 400);
      }
    }
  }

  // Apply masking to success array
  const maskedSuccess = maskCandidates(success, ctx);

  const meta = {
    total: records.length,
    succeeded: success.length,
    failed: errors.length,
    atomic
  };

  const statusCode = errors.length > 0 ? 207 : 200;
  return c.json({ success: maskedSuccess, errors, meta }, statusCode);
});

// ═══════════════════════════════════════════════════════
// BATCH DELETE: DELETE /candidates/batch
// ═══════════════════════════════════════════════════════
app.delete('/batch', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  const parseResult = await parseJsonWithSchema(c.req.raw, BatchDeleteBodySchema);
  if (!parseResult.ok) {
    return c.json({ error: parseResult.error, code: parseResult.code, layer: 'validation', fields: parseResult.fields }, 400);
  }
  const body = parseResult.data as any;
  const ids = body.ids || [];
  const options = body.options || {};
  const atomic = options.atomic === true;

  // Validate batch size
  if (ids.length > 100) {
    return c.json(BatchErrors.sizeExceeded(100, ids.length), 400);
  }

  // Empty batch - return empty success
  if (ids.length === 0) {
    return c.json({
      success: [],
      errors: [],
      meta: { total: 0, succeeded: 0, failed: 0, atomic: false }
    }, 200);
  }

  // Batch fetch existing records with firewall
  const existingRecords = await db.select().from(candidates)
    .where(and(buildFirewallConditions(ctx), inArray(candidates.id, ids)));

  // Create lookup map for O(1) access
  const recordMap = new Map<string, any>();
  for (const record of existingRecords) {
    recordMap.set(record.id, record);
  }

  const errors: any[] = [];
  const validIds: string[] = [];

  // Process each ID
  for (let i = 0; i < ids.length; i++) {
    const id = ids[i];
    const existingRecord = recordMap.get(id);

    // Check if record exists
    if (!existingRecord) {
      errors.push({
        index: i,
        id,
        error: { ...FirewallErrors.notFound(), details: { id } }
      });
      continue;
    }

    // Default: Authenticated access required
    if (!ctx.authenticated) {
      errors.push({
        index: i,
        id,
        error: { error: 'Unauthorized', code: 'UNAUTHORIZED' }
      });
      continue;
    }

    validIds.push(id);
  }

  // If atomic mode and any errors, fail the entire batch
  if (atomic && errors.length > 0) {
    return c.json(BatchErrors.atomicFailed(errors[0].index, errors[0].error), 400);
  }

  let success: any[] = [];

    // Soft delete - batch update
    if (validIds.length > 0) {
      try {
        const result = await db.update(candidates)
          .set({
    deletedAt: new Date().toISOString(),
    modifiedAt: new Date().toISOString(),
          })
          .where(and(buildFirewallConditions(ctx), inArray(candidates.id, validIds)))
          .returning();

        success = result;
      } catch (error: any) {
        // Database error during delete
        if (atomic) {
          return c.json({
            error: 'Batch delete failed',
            layer: 'validation',
            code: 'BATCH_DELETE_FAILED',
            details: { reason: error.message }
          }, 400);
        }
        // In partial success mode, treat all as errors
        for (let i = 0; i < validIds.length; i++) {
          errors.push({
            index: i,
            id: validIds[i],
            error: {
              error: 'Database delete failed',
              layer: 'validation',
              code: 'DELETE_FAILED',
              details: { reason: error.message }
            }
          });
        }
      }
    }

  const meta = {
    total: ids.length,
    succeeded: success.length,
    failed: errors.length,
    atomic
  };

  const statusCode = errors.length > 0 ? 207 : 200;
  return c.json({ success, errors, meta }, statusCode);
});

// ═══════════════════════════════════════════════════════
// GET: GET /candidates/:id
// Supports: ?fields=id,name,email
// ═══════════════════════════════════════════════════════
app.get('/:id', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');
  const id = c.req.param('id');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  // Field selection: ?fields=id,name,email
  const url = new URL(c.req.url);
  const fieldsParam = url.searchParams.get('fields');
  let selectFields: Record<string, any> | undefined;
  if (fieldsParam) {
    const requested: Record<string, any> = {};
    for (const f of fieldsParam.split(',')) {
      const fieldName = f.trim();
      if (fieldName && fieldName in candidates) {
        requested[fieldName] = candidates[fieldName as keyof typeof candidates];
      }
    }
    if (Object.keys(requested).length > 0) {
      selectFields = requested;
    }
  }

  // Query with firewall
  const baseQuery = selectFields ? db.select(selectFields).from(candidates) : db.select().from(candidates);
  const [record] = await baseQuery.where(and(buildFirewallConditions(ctx), eq(candidates.id, id)));

  if (!record) {
    return c.json(FirewallErrors.notFound(), 403);
  }

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.get.access, ctx)) {
    return c.json(AccessErrors.roleRequired((CRUD_ACCESS.get.access as any).roles || [], ctx.roles, (CRUD_ACCESS.get.access as any).userRole, ctx.userRole), 403);
  }

  // Apply masking
  return jsonWithEtag(c, maskCandidate(record, ctx));
});

// ═══════════════════════════════════════════════════════
// CREATE: POST /candidates
// ═══════════════════════════════════════════════════════
app.post('/', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.create.access, ctx)) {
    return c.json(AccessErrors.roleRequired((CRUD_ACCESS.create.access as any).roles || [], ctx.roles, (CRUD_ACCESS.create.access as any).userRole, ctx.userRole), 403);
  }

  if (!ctx.activeOrgId) {
    return c.json({
      error: 'Organization required',
      code: 'ORG_REQUIRED',
      hint: 'Set active organization in session before creating records'
    }, 400);
  }
const parseResult = await parseJsonWithSchema(c.req.raw, CreateBodySchema);
  if (!parseResult.ok) {
    return c.json({ error: parseResult.error, code: parseResult.code, layer: 'validation', fields: parseResult.fields }, 400);
  }
  const body = parseResult.data;
  
  // Guards validation
  const validation = validateCreate(body);
  if (!validation.valid) {
    // Prioritize error type: system-managed > protected > not createable
    if (validation.systemManaged.length > 0) {
      return c.json(GuardErrors.systemManaged(validation.systemManaged), 400);
    }
    if (validation.protected.length > 0) {
      const firstProtected = validation.protected[0];
      return c.json(GuardErrors.fieldProtected([firstProtected.field], firstProtected.actions), 400);
    }
    if (validation.notCreateable.length > 0) {
      return c.json(GuardErrors.fieldNotCreateable(validation.notCreateable), 400);
    }
  }

  // Apply defaults, ownership, and audit fields
  const now = new Date().toISOString();
  const data = {
    id: 'cnd_' + crypto.randomUUID().replace(/-/g, ''),
    ...body,
    ...{},
    organizationId: ctx.activeOrgId,
    createdAt: now,
    modifiedAt: now,
  };

  try {
    const result = await db.insert(candidates).values(data).returning();
    return c.json(maskCandidate(result[0], ctx), 201);
  } catch (error: any) {
    // Search full error chain (D1/Drizzle may nest the real error in cause)
    const errMsg = [error?.message, error?.cause?.message, String(error)].filter(Boolean).join(' ');
    if (errMsg.includes('NOT NULL constraint failed')) {
      const match = errMsg.match(/NOT NULL constraint failed: \w+\.(\w+)/);
      const column = match?.[1];
      return c.json({
        error: 'Missing required field',
        layer: 'database',
        code: 'NOT_NULL_VIOLATION',
        details: { column },
        hint: column ? `The field "${column}" is required and cannot be null` : 'A required field is missing',
      }, 400);
    }
    if (errMsg.includes('UNIQUE constraint failed')) {
      const match = errMsg.match(/UNIQUE constraint failed: \w+\.(\w+)/);
      const column = match?.[1];
      return c.json({
        error: 'Duplicate value',
        layer: 'database',
        code: 'UNIQUE_VIOLATION',
        details: { column },
        hint: column ? `A record with this "${column}" already exists` : 'A unique constraint was violated',
      }, 409);
    }
    console.error('[CREATE] Database error:', error);
    return c.json({
      error: 'Insert failed',
      layer: 'database',
      code: 'INSERT_FAILED',
    }, 500);
  }
});

// ═══════════════════════════════════════════════════════
// UPDATE: PATCH /candidates/:id
// ═══════════════════════════════════════════════════════
app.patch('/:id', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');
  const id = c.req.param('id');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  // Fetch with firewall
  const [record] = await db.select().from(candidates)
    .where(and(buildFirewallConditions(ctx), eq(candidates.id, id)));

  if (!record) {
    return c.json(FirewallErrors.notFound(), 403);
  }

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.update.access, ctx)) {
    return c.json(AccessErrors.roleRequired((CRUD_ACCESS.update.access as any).roles || [], ctx.roles, (CRUD_ACCESS.update.access as any).userRole, ctx.userRole), 403);
  }

  const parseResult = await parseJsonWithSchema(c.req.raw, UpdateBodySchema);
  if (!parseResult.ok) {
    return c.json({ error: parseResult.error, code: parseResult.code, layer: 'validation', fields: parseResult.fields }, 400);
  }
  const body = parseResult.data;
  // Block ownership scope reassignment (system-managed by auth context)
  const ownershipFields = ["organizationId"];
  const attemptedOwnershipFields = ownershipFields.filter((field) => Object.prototype.hasOwnProperty.call(body, field));
  if (attemptedOwnershipFields.length > 0) {
    return c.json(GuardErrors.systemManaged(attemptedOwnershipFields), 400);
  }
  
  // Guards validation
  const validation = validateUpdate(body);
  if (!validation.valid) {
    // Prioritize error type: system-managed > immutable > protected > not updatable
    if (validation.systemManaged.length > 0) {
      return c.json(GuardErrors.systemManaged(validation.systemManaged), 400);
    }
    if (validation.immutable.length > 0) {
      return c.json(GuardErrors.fieldImmutable(validation.immutable), 400);
    }
    if (validation.protected.length > 0) {
      const firstProtected = validation.protected[0];
      return c.json(GuardErrors.fieldProtected([firstProtected.field], firstProtected.actions), 400);
    }
    if (validation.notUpdatable.length > 0) {
      return c.json(GuardErrors.fieldNotUpdatable(validation.notUpdatable), 400);
    }
  }

  // Apply audit fields
  const data = {
    ...body,
    modifiedAt: new Date().toISOString(),
  };

  try {
    const result = await db.update(candidates).set(data)
      .where(and(buildFirewallConditions(ctx), eq(candidates.id, id))).returning();

    return c.json(maskCandidate(result[0], ctx));
  } catch (error: any) {
    // Search full error chain (D1/Drizzle may nest the real error in cause)
    const errMsg = [error?.message, error?.cause?.message, String(error)].filter(Boolean).join(' ');
    if (errMsg.includes('NOT NULL constraint failed')) {
      const match = errMsg.match(/NOT NULL constraint failed: \w+\.(\w+)/);
      const column = match?.[1];
      return c.json({
        error: 'Missing required field',
        layer: 'database',
        code: 'NOT_NULL_VIOLATION',
        details: { column },
        hint: column ? `The field "${column}" is required and cannot be null` : 'A required field is missing',
      }, 400);
    }
    if (errMsg.includes('UNIQUE constraint failed')) {
      const match = errMsg.match(/UNIQUE constraint failed: \w+\.(\w+)/);
      const column = match?.[1];
      return c.json({
        error: 'Duplicate value',
        layer: 'database',
        code: 'UNIQUE_VIOLATION',
        details: { column },
        hint: column ? `A record with this "${column}" already exists` : 'A unique constraint was violated',
      }, 409);
    }
    console.error('[UPDATE] Database error:', error);
    return c.json({
      error: 'Update failed',
      layer: 'database',
      code: 'UPDATE_FAILED',
    }, 500);
  }
});

// ═══════════════════════════════════════════════════════
// DELETE: DELETE /candidates/:id
// ═══════════════════════════════════════════════════════
app.delete('/:id', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');
  const id = c.req.param('id');

  if (!ctx.authenticated) {
    return c.json({ error: 'Unauthorized', code: 'UNAUTHORIZED' }, 401);
  }

  // Fetch with firewall
  const [record] = await db.select().from(candidates)
    .where(and(buildFirewallConditions(ctx), eq(candidates.id, id)));

  if (!record) {
    return c.json(FirewallErrors.notFound(), 403);
  }

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.delete.access, ctx)) {
    return c.json(AccessErrors.roleRequired((CRUD_ACCESS.delete.access as any).roles || [], ctx.roles, (CRUD_ACCESS.delete.access as any).userRole, ctx.userRole), 403);
  }
  
  // Soft delete
  await db.update(candidates).set({
    deletedAt: new Date().toISOString(),
    modifiedAt: new Date().toISOString(),
  }).where(and(buildFirewallConditions(ctx), eq(candidates.id, id)));

  return c.json({ success: true });
});

export default app;

/**
 * Generated by quickback.dev
 *
 * THE FILE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
 * THE AUTHORS OR COPYRIGHT HOLDERS OR DISTRIBUTORS BE LIABLE FOR ANY CLAIM,
 * DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR
 * OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE FILE OR THE USE
 * OR OTHER DEALINGS IN THE FILE.
 */