the_order/packages/database/src/court-filings.ts

/**
 * Court Filing Management
 * Handles e-filing, court submissions, and filing tracking
 */

import { query } from './client';
import { z } from 'zod';

export const CourtFilingSchema = z.object({
  id: z.string().uuid(),
  matter_id: z.string().uuid(),
  document_id: z.string().uuid(),
  filing_type: z.string(),
  court_name: z.string(),
  court_system: z.string().optional(),
  case_number: z.string().optional(),
  docket_number: z.string().optional(),
  filing_date: z.date().optional(),
  filing_deadline: z.date().optional(),
  status: z.enum(['draft', 'submitted', 'accepted', 'rejected', 'filed']),
  filing_reference: z.string().optional(),
  filing_confirmation: z.string().optional(),
  submitted_by: z.string().uuid().optional(),
  submitted_at: z.date().optional(),
  accepted_at: z.date().optional(),
  rejection_reason: z.string().optional(),
  metadata: z.record(z.unknown()).optional(),
  created_at: z.date(),
  updated_at: z.date(),
});

export type CourtFiling = z.infer<typeof CourtFilingSchema>;

export type FilingType =
  | 'pleading'
  | 'motion'
  | 'brief'
  | 'exhibit'
  | 'affidavit'
  | 'order'
  | 'judgment'
  | 'notice'
  | 'response'
  | 'reply'
  | 'other';

export type CourtSystem = 'federal' | 'state' | 'municipal' | 'administrative' | 'other';

export interface CreateCourtFilingInput {
  matter_id: string;
  document_id: string;
  filing_type: FilingType;
  court_name: string;
  court_system?: CourtSystem;
  case_number?: string;
  docket_number?: string;
  filing_date?: Date | string;
  filing_deadline?: Date | string;
  status?: 'draft' | 'submitted' | 'accepted' | 'rejected' | 'filed';
  metadata?: Record<string, unknown>;
}

/**
 * Create a court filing record
 */
export async function createCourtFiling(input: CreateCourtFilingInput): Promise<CourtFiling> {
  const result = await query<CourtFiling>(
    `INSERT INTO court_filings 
     (matter_id, document_id, filing_type, court_name, court_system, 
      case_number, docket_number, filing_date, filing_deadline, status, metadata)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
     RETURNING *`,
    [
      input.matter_id,
      input.document_id,
      input.filing_type,
      input.court_name,
      input.court_system || null,
      input.case_number || null,
      input.docket_number || null,
      input.filing_date ? new Date(input.filing_date) : null,
      input.filing_deadline ? new Date(input.filing_deadline) : null,
      input.status || 'draft',
      input.metadata ? JSON.stringify(input.metadata) : null,
    ]
  );

  return result.rows[0]!;
}

/**
 * Get filing by ID
 */
export async function getCourtFiling(id: string): Promise<CourtFiling | null> {
  const result = await query<CourtFiling>(
    `SELECT * FROM court_filings WHERE id = $1`,
    [id]
  );
  return result.rows[0] || null;
}

/**
 * Get filings for a matter
 */
export async function getMatterFilings(
  matter_id: string,
  status?: string
): Promise<CourtFiling[]> {
  const conditions = ['matter_id = $1'];
  const params: unknown[] = [matter_id];
  let paramIndex = 2;

  if (status) {
    conditions.push(`status = $${paramIndex++}`);
    params.push(status);
  }

  const result = await query<CourtFiling>(
    `SELECT * FROM court_filings 
     WHERE ${conditions.join(' AND ')}
     ORDER BY filing_date DESC NULLS LAST, created_at DESC`,
    params
  );

  return result.rows;
}

/**
 * Get filings for a document
 */
export async function getDocumentFilings(document_id: string): Promise<CourtFiling[]> {
  const result = await query<CourtFiling>(
    `SELECT * FROM court_filings 
     WHERE document_id = $1 
     ORDER BY filing_date DESC NULLS LAST`,
    [document_id]
  );
  return result.rows;
}

/**
 * Update filing status
 */
export async function updateFilingStatus(
  id: string,
  status: 'draft' | 'submitted' | 'accepted' | 'rejected' | 'filed',
  updates?: {
    filing_reference?: string;
    filing_confirmation?: string;
    submitted_by?: string;
    submitted_at?: Date;
    accepted_at?: Date;
    rejection_reason?: string;
  }
): Promise<CourtFiling | null> {
  const fields: string[] = [`status = $1`];
  const values: unknown[] = [status];
  let paramIndex = 2;

  if (updates) {
    if (updates.filing_reference !== undefined) {
      fields.push(`filing_reference = $${paramIndex++}`);
      values.push(updates.filing_reference);
    }

    if (updates.filing_confirmation !== undefined) {
      fields.push(`filing_confirmation = $${paramIndex++}`);
      values.push(updates.filing_confirmation);
    }

    if (updates.submitted_by !== undefined) {
      fields.push(`submitted_by = $${paramIndex++}`);
      values.push(updates.submitted_by);
    }

    if (updates.submitted_at !== undefined) {
      fields.push(`submitted_at = $${paramIndex++}`);
      values.push(updates.submitted_at);
    } else if (status === 'submitted') {
      fields.push(`submitted_at = NOW()`);
    }

    if (updates.accepted_at !== undefined) {
      fields.push(`accepted_at = $${paramIndex++}`);
      values.push(updates.accepted_at);
    } else if (status === 'accepted' || status === 'filed') {
      fields.push(`accepted_at = NOW()`);
    }

    if (updates.rejection_reason !== undefined) {
      fields.push(`rejection_reason = $${paramIndex++}`);
      values.push(updates.rejection_reason);
    }
  }

  fields.push(`updated_at = NOW()`);
  values.push(id);

  const result = await query<CourtFiling>(
    `UPDATE court_filings 
     SET ${fields.join(', ')}
     WHERE id = $${paramIndex}
     RETURNING *`,
    values
  );

  return result.rows[0] || null;
}

/**
 * Get upcoming filing deadlines
 */
export async function getUpcomingFilingDeadlines(
  days_ahead = 30,
  matter_id?: string
): Promise<CourtFiling[]> {
  const conditions = [
    `filing_deadline IS NOT NULL`,
    `filing_deadline >= CURRENT_DATE`,
    `filing_deadline <= CURRENT_DATE + INTERVAL '${days_ahead} days'`,
    `status IN ('draft', 'submitted')`,
  ];
  const params: unknown[] = [];
  let paramIndex = 1;

  if (matter_id) {
    conditions.push(`matter_id = $${paramIndex++}`);
    params.push(matter_id);
  }

  const result = await query<CourtFiling>(
    `SELECT * FROM court_filings 
     WHERE ${conditions.join(' AND ')}
     ORDER BY filing_deadline ASC`,
    params
  );

  return result.rows;
}

/**
 * Get filings by court
 */
export async function getFilingsByCourt(
  court_name: string,
  case_number?: string
): Promise<CourtFiling[]> {
  const conditions = ['court_name = $1'];
  const params: unknown[] = [court_name];
  let paramIndex = 2;

  if (case_number) {
    conditions.push(`case_number = $${paramIndex++}`);
    params.push(case_number);
  }

  const result = await query<CourtFiling>(
    `SELECT * FROM court_filings 
     WHERE ${conditions.join(' AND ')}
     ORDER BY filing_date DESC NULLS LAST`,
    params
  );

  return result.rows;
}

/**
 * Get filing statistics
 */
export interface FilingStatistics {
  total: number;
  by_status: Record<string, number>;
  by_type: Record<string, number>;
  upcoming_deadlines: number;
  overdue: number;
}

export async function getFilingStatistics(
  matter_id?: string
): Promise<FilingStatistics> {
  const conditions: string[] = [];
  const params: unknown[] = [];
  let paramIndex = 1;

  if (matter_id) {
    conditions.push(`matter_id = $${paramIndex++}`);
    params.push(matter_id);
  }

  const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // Get total and by status
  const statusResult = await query<{ status: string; count: string }>(
    `SELECT status, COUNT(*) as count 
     FROM court_filings 
     ${whereClause}
     GROUP BY status`,
    params
  );

  // Get by type
  const typeResult = await query<{ filing_type: string; count: string }>(
    `SELECT filing_type, COUNT(*) as count 
     FROM court_filings 
     ${whereClause}
     GROUP BY filing_type`,
    params
  );

  // Get upcoming deadlines
  const upcomingResult = await query<{ count: string }>(
    `SELECT COUNT(*) as count 
     FROM court_filings 
     ${whereClause}
     AND filing_deadline IS NOT NULL
     AND filing_deadline >= CURRENT_DATE
     AND filing_deadline <= CURRENT_DATE + INTERVAL '30 days'
     AND status IN ('draft', 'submitted')`,
    params
  );

  // Get overdue
  const overdueResult = await query<{ count: string }>(
    `SELECT COUNT(*) as count 
     FROM court_filings 
     ${whereClause}
     AND filing_deadline IS NOT NULL
     AND filing_deadline < CURRENT_DATE
     AND status IN ('draft', 'submitted')`,
    params
  );

  const stats: FilingStatistics = {
    total: 0,
    by_status: {},
    by_type: {},
    upcoming_deadlines: parseInt(upcomingResult.rows[0]?.count || '0', 10),
    overdue: parseInt(overdueResult.rows[0]?.count || '0', 10),
  };

  for (const row of statusResult.rows) {
    const count = parseInt(row.count, 10);
    stats.total += count;
    stats.by_status[row.status] = count;
  }

  for (const row of typeResult.rows) {
    stats.by_type[row.filing_type] = parseInt(row.count, 10);
  }

  return stats;
}

// Aliases for route compatibility
export const listCourtFilings = getMatterFilings;
export const getFilingDeadlines = getUpcomingFilingDeadlines;