src/db/tickets.ts

import { supabase } from './client';
import { logger } from '../utils/logger';
import type { TurnitinJob } from './jobs';

export interface CreateJobParams {
  userId: string;
  assignmentTargetId: string;
  mode: string;
  filters: Record<string, unknown>;
  inputFileName: string;
  inputStoragePath: string;
  inputFileSize?: number;
  inputFileSha256?: string;
  submissionRequestId: string;
}

export interface CreateJobResult {
  jobId: string;
  created: boolean;
}

export interface UserProfile {
  id: string;
  email: string;
  display_name: string | null;
  ticket_balance: number;
  role: string;
  created_at: string;
  updated_at: string;
}

/**
 * Create a new Turnitin job while atomically deducting a ticket.
 * Uses the database RPC to ensure ticket balance is checked and decremented in one transaction.
 * Returns the new job ID.
 */
export async function createJobWithTicket(params: CreateJobParams): Promise<CreateJobResult> {
  const { data, error } = await supabase.rpc('create_job_with_ticket_idempotent', {
    p_user_id: params.userId,
    p_assignment_target_id: params.assignmentTargetId,
    p_mode: params.mode,
    p_filters: params.filters,
    p_input_file_name: params.inputFileName,
    p_input_file_path: params.inputStoragePath,
    p_input_file_size: params.inputFileSize ?? null,
    p_input_file_sha256: params.inputFileSha256 ?? null,
    p_submission_request_id: params.submissionRequestId,
  });

  if (error) {
    logger.error('Failed to create job with ticket', { error: error.message, userId: params.userId });
    throw error;
  }

  const row = Array.isArray(data) ? data[0] : data;
  if (!row?.job_id) {
    throw new Error('Idempotent job creation returned no job ID');
  }

  return {
    jobId: row.job_id as string,
    created: row.created === true,
  };
}

export async function getJobBySubmissionRequestId(
  userId: string,
  submissionRequestId: string,
): Promise<TurnitinJob | null> {
  const { data, error } = await supabase
    .from('turnitin_jobs')
    .select('*')
    .eq('user_id', userId)
    .eq('submission_request_id', submissionRequestId)
    .maybeSingle();

  if (error) {
    logger.error('Failed to find job by submission request ID', {
      userId,
      error: error.message,
    });
    throw error;
  }

  return data as TurnitinJob | null;
}

/**
 * Fetch a user's profile including their current ticket balance.
 */
export async function getUserProfile(userId: string): Promise<UserProfile | null> {
  const { data, error } = await supabase
    .from('user_profiles')
    .select('*')
    .eq('id', userId)
    .single();

  if (error) {
    if (error.code === 'PGRST116') return null; // Row not found
    logger.error('Failed to get user profile', { userId, error: error.message });
    throw error;
  }

  return data as UserProfile;
}

/**
 * Admin operation: top up a user's ticket balance.
 * Uses database RPC for atomic increment. Returns the new balance.
 */
export async function adminTopupTickets(
  adminId: string,
  targetUserId: string,
  amount: number,
): Promise<number> {
  const { data, error } = await supabase.rpc('admin_topup_tickets', {
    p_admin_id: adminId,
    p_target_user_id: targetUserId,
    p_amount: amount,
  });

  if (error) {
    logger.error('Failed to top up tickets', {
      adminId,
      targetUserId,
      amount,
      error: error.message,
    });
    throw error;
  }

  return data as number;
}

/**
 * Cancel a pending job and refund the ticket.
 * Uses database RPC for atomic status change + ticket refund.
 * Returns true if the job was successfully cancelled.
 */
export async function cancelJob(userId: string, jobId: string): Promise<boolean> {
  const { data, error } = await supabase.rpc('cancel_job', {
    p_user_id: userId,
    p_job_id: jobId,
  });

  if (error) {
    logger.error('Failed to cancel job', { userId, jobId, error: error.message });
    throw error;
  }

  return data as boolean;
}

/**
 * Refund a terminal failed job exactly once.
 *
 * The database RPC locks the job row and checks the ticket ledger before
 * incrementing balance, so this remains idempotent across multiple workers,
 * stale recovery, and manual/admin retries.
 */
export async function refundFailedJob(jobId: string, reason: string): Promise<boolean> {
  const { data, error } = await supabase.rpc('refund_failed_job', {
    p_job_id: jobId,
    p_reason: reason,
  });

  if (error) {
    logger.error('Failed to refund failed job', {
      jobId,
      reason,
      error: error.message,
    });
    throw error;
  }

  return data as boolean;
}