Blame for api/v2/_services/paymentApprovalSystem.ts

import { getSupabaseAdmin, getSupabaseClientForRequest } from '../_shared/supabaseAdmin.js';
import { isOrderPaidOnCashfree } from './cashfreeService.js';
import { issueCertificate } from './certificationSystem.js';
import { sendCertificateIssuedEmail } from './emailService.js';
import { backendLogger as logger } from '../_shared/backendLogger.js';
import type {
  ApprovedPaymentContext,
  CertificatePayload,
  CertificationTrack,
  PaymentApprovalInput,
  PaymentReviewStatus,
  PaymentRequest,
  PaymentRequestCreateInput,
  PaymentRequestStatus,
} from '../_types.js';

const PAYMENT_REQUESTS_TABLE = 'payment_requests';

function parseBoolean(value: string | undefined, fallback: boolean): boolean {
  if (value === undefined) return fallback;
  return value.toLowerCase() === 'true';
}

export function isPaywallEnabled(): boolean {
  return parseBoolean(process.env.CERTIFICATE_PAYWALL_ENABLED, true);
}

export function getCertificateFeeInr(track?: CertificationTrack): number {
  if (track === 'ytcert') {
    const parsed = Number(process.env.YTCERT_FEE_INR || 59);
    return Number.isFinite(parsed) && parsed > 0 ? Math.round(parsed) : 59;
  }
  if (track === 'quickcert') {
    const parsed = Number(process.env.QUICKCERT_FEE_INR || 79);
    return Number.isFinite(parsed) && parsed > 0 ? Math.round(parsed) : 79;
  }
  const parsed = Number(process.env.VERIFIEDCERT_FEE_INR || process.env.CERTIFICATE_FEE_INR || 149);
  return Number.isFinite(parsed) && parsed > 0 ? Math.round(parsed) : 149;
}

function mapPaymentRequest(row: any): PaymentRequest {
  return {
    id: row.id,
    userId: row.user_id,
    txnId: row.txn_id,
    skill: row.skill,
    projectTitle: row.project_title,
    status: row.status as PaymentRequestStatus,
    certificateIssued: Boolean(row.certificate_issued),
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    track: (row.track || 'verifiedcert') as CertificationTrack,
  };
}

async function findPaymentRequestForReview(input: {
  paymentRequestId?: string;
  txnId?: string;
  authToken?: string | null;
}): Promise<{ id: string; certificate_issued: boolean }> {
  const supabase = getSupabaseClientForRequest(input.authToken);

  if (!input.paymentRequestId && !input.txnId) {
    throw new Error('paymentRequestId or txnId is required for payment review.');
  }

  let query = supabase.from(PAYMENT_REQUESTS_TABLE).select('id, certificate_issued').limit(1);

  if (input.paymentRequestId) {
    query = query.eq('id', input.paymentRequestId);
  }

  if (input.txnId) {
    query = query.eq('txn_id', input.txnId);
  }

  const { data, error } = await query.maybeSingle();

  if (error || !data) {
    throw new Error('Payment request not found for review.');
  }

  return data;
}

function mapCertificate(row: any): CertificatePayload {
  return {
    certificateId: row.metadata?.certificate_id || row.id,
    userId: row.user_id,
    name: row.metadata?.username || row.username || 'Student',
    skill: row.topic,
    projectTitle: row.project_title || '',
    githubUrl: row.metadata?.github_repo_url || row.metadata?.github_url || '',
    proofUrl: row.metadata?.live_demo_url || row.metadata?.proof_url || '',
    issuedAt: row.issued_at,
    track: (row.metadata?.certification_track || 'verifiedcert') as CertificationTrack,
  };
}

async function findCertificateByPaymentRequestId(
  paymentRequestId: string,
  authToken?: string | null
): Promise<CertificatePayload | null> {
  const supabase = getSupabaseClientForRequest(authToken);
  const { data, error } = await supabase
    .from('certificates')
    .select('id, user_id, topic, project_title, issued_at, metadata')
    .eq('metadata->>payment_request_id', paymentRequestId)
    .order('issued_at', { ascending: false })
    .limit(1)
    .maybeSingle();

  if (error || !data) {
    return null;
  }

  return mapCertificate(data);
}

export async function submitPaymentRequest(input: PaymentRequestCreateInput & { id?: string }, authToken?: string | null): Promise<PaymentRequest> {
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
  const supabase = (!authToken && serviceRoleKey) ? getSupabaseAdmin() : getSupabaseClientForRequest(authToken);

  const { data, error } = await supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .insert({
      id: input.id,
      user_id: input.userId,
      txn_id: input.txnId,
      skill: input.skill,
      project_title: input.projectTitle,
      track: input.track || 'verifiedcert',
      status: 'pending',
      certificate_issued: false,
    })
    .select('*')
    .single();

  if (!error && data) {
    return mapPaymentRequest(data);
  }

  if (error?.code === '23505') {
    const { data: existing, error: existingError } = await supabase
      .from(PAYMENT_REQUESTS_TABLE)
      .select('*')
      .eq('txn_id', input.txnId)
      .maybeSingle();

    if (existingError || !existing) {
      throw new Error(`Transaction ID already exists and cannot be resolved: ${error.message}`);
    }

    if (existing.user_id !== input.userId) {
      throw new Error('This transaction ID is already used by another account.');
    }

    return mapPaymentRequest(existing);
  }

  throw new Error(`Failed to submit payment request: ${error?.message || 'unknown error'}`);
}

export async function getLatestPaymentRequest(userId: string): Promise<PaymentRequest | null> {
  const supabase = getSupabaseClientForRequest(null);
  const { data, error } = await supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .select('*')
    .eq('user_id', userId)
    .order('created_at', { ascending: false })
    .limit(1)
    .maybeSingle();

  if (error || !data) {
    return null;
  }

  return mapPaymentRequest(data);
}

export async function getPaymentRequestForUser(input: {
  userId: string;
  paymentRequestId?: string;
  txnId?: string;
  skill?: string;
  projectTitle?: string;
}, authToken?: string | null): Promise<PaymentRequest | null> {
  const supabase = getSupabaseClientForRequest(authToken);

  let query = supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .select('*')
    .eq('user_id', input.userId)
    .order('created_at', { ascending: false })
    .limit(1);

  if (input.paymentRequestId) {
    query = query.eq('id', input.paymentRequestId);
  } else {
    if (input.txnId) {
      query = query.eq('txn_id', input.txnId);
    }
    if (input.skill) {
      query = query.eq('skill', input.skill);
    }
    if (input.projectTitle) {
      query = query.eq('project_title', input.projectTitle);
    }
  }

  const { data, error } = await query.maybeSingle();

  if (error || !data) {
    return null;
  }

  return mapPaymentRequest(data);
}

async function resolvePaymentRequest(input: PaymentApprovalInput, authToken?: string | null): Promise<PaymentRequest> {
  const supabase = getSupabaseClientForRequest(authToken);

  if (input.paymentRequestId) {
    const { data, error } = await supabase
      .from(PAYMENT_REQUESTS_TABLE)
      .select('*')
      .eq('id', input.paymentRequestId)
      .eq('user_id', input.userId)
      .maybeSingle();

    if (error || !data) {
      throw new Error('Payment request not found for this user.');
    }

    return mapPaymentRequest(data);
  }

  let query = supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .select('*')
    .eq('user_id', input.userId)
    .order('created_at', { ascending: false })
    .limit(1);

  if (input.txnId) {
    query = query.eq('txn_id', input.txnId);
  }
  if (input.skill) {
    query = query.eq('skill', input.skill);
  }
  if (input.projectTitle) {
    query = query.eq('project_title', input.projectTitle);
  }

  const { data, error } = await query.maybeSingle();

  if (error || !data) {
    throw new Error('No matching payment request found for certification.');
  }

  return mapPaymentRequest(data);
}

export async function assertApprovedPaymentForCertificate(
  input: PaymentApprovalInput,
  authToken?: string | null
): Promise<ApprovedPaymentContext> {
  let request = await resolvePaymentRequest(input, authToken);

  if (request.status !== 'paid') {
    // If pending, attempt self-healing against Cashfree directly
    if (request.status === 'pending') {
      const isUuid = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(request.id);
      if (isUuid) {
        try {
          const isPaid = await isOrderPaidOnCashfree(request.id);
          if (isPaid) {
            logger.info(`[assertApprovedPaymentForCertificate] Reconciled Cashfree order ${request.id} as PAID.`);
            const updated = await updatePaymentRequestStatus(request.id, 'paid', null);
            request = updated;
          }
        } catch (pollError) {
          logger.warn('[assertApprovedPaymentForCertificate] Cashfree reconciliation check failed:', pollError);
        }
      }
    }
  }

  if (request.status !== 'paid') {
    throw new Error('Payment is not approved yet. Please wait for payment confirmation.');
  }

  if (!request.certificateIssued) {
    const existing = await findCertificateByPaymentRequestId(request.id, authToken);
    if (existing) {
      return {
        request,
        alreadyIssuedCertificate: existing,
      };
    }

    return {
      request,
      alreadyIssuedCertificate: null,
    };
  }

  const issued = await findCertificateByPaymentRequestId(request.id, authToken);
  if (!issued) {
    throw new Error('Payment request is marked as issued, but certificate record is missing.');
  }

  return {
    request,
    alreadyIssuedCertificate: issued,
  };
}

export async function markPaymentRequestCertificateIssued(requestId: string, authToken?: string | null): Promise<boolean> {
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
  const supabase = serviceRoleKey ? getSupabaseAdmin() : getSupabaseClientForRequest(authToken);
  const { error } = await supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .update({
      certificate_issued: true,
      updated_at: new Date().toISOString(),
    })
    .eq('id', requestId);

  if (error) {
    logger.warn(`Failed to mark payment request as certificate issued for ${requestId}:`, error);
    return false;
  }

  return true;
}

export async function reviewPaymentRequest(input: {
  paymentRequestId?: string;
  txnId?: string;
  status: PaymentReviewStatus;
  reviewedBy?: string;
  reviewNotes?: string;
}): Promise<PaymentRequest> {
  const existing = await findPaymentRequestForReview({
    paymentRequestId: input.paymentRequestId,
    txnId: input.txnId,
    authToken: null,
  });

  if (existing.certificate_issued && input.status === 'rejected') {
    throw new Error('Cannot reject a payment request after certificate issuance.');
  }

  const supabase = getSupabaseAdmin();
  const now = new Date().toISOString();

  const nextValues: Record<string, unknown> = {
    status: input.status,
    reviewed_at: now,
    review_notes: input.reviewNotes?.trim() || null,
    updated_at: now,
  };

  if (input.reviewedBy?.trim()) {
    nextValues.reviewed_by = input.reviewedBy.trim();
  }

  if (input.status === 'rejected') {
    nextValues.certificate_issued = false;
  }

  const { data, error } = await supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .update(nextValues)
    .eq('id', existing.id)
    .select('*')
    .single();

  if (error || !data) {
    throw new Error(`Failed to review payment request: ${error?.message || 'unknown error'}`);
  }

  return mapPaymentRequest(data);
}

export async function updatePaymentRequestStatus(
  requestId: string,
  status: PaymentRequestStatus,
  authToken?: string | null
): Promise<PaymentRequest> {
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
  const supabase = serviceRoleKey ? getSupabaseAdmin() : getSupabaseClientForRequest(authToken);

  const { data, error } = await supabase
    .from(PAYMENT_REQUESTS_TABLE)
    .update({
      status,
      updated_at: new Date().toISOString(),
    })
    .eq('id', requestId)
    .select('*')
    .single();

  if (error || !data) {
    const isRlsIssue = !serviceRoleKey;
    const rlsHint = isRlsIssue 
      ? ' (This usually happens because SUPABASE_SERVICE_ROLE_KEY is missing in your .env and RLS blocks regular users from updating payment requests)' 
      : '';
    throw new Error(`Failed to update payment request status: ${error?.message || 'unknown error'}${rlsHint}`);
  }

  if (status === 'paid') {
    // Automatically issue certificate and set my_courses progress to 100%
    void processApprovedPaymentAndIssueCertificate(requestId).catch((err) => {
      logger.error('[Auto-Issuance Trigger Failed in updatePaymentRequestStatus]', err);
    });
  }

  return mapPaymentRequest(data);
}

// Concurrency mutex to prevent duplicate simultaneous issuances for the same payment request
const inFlightIssuancesByOrderId = new Map<string, Promise<CertificatePayload | null>>();

export async function syncCourseProgressToCertified(
  userId: string,
  courseName: string,
  track: string,
  issuedAt: string,
  score: number = 100
): Promise<void> {
  const supabase = getSupabaseAdmin();
  const nowIso = issuedAt || new Date().toISOString();

  try {
    // 1. Upsert into my_courses
    const { error: myCourseErr } = await supabase
      .from('my_courses')
      .upsert({
        user_id: userId,
        course_name: courseName,
        status: 'certified',
        current_step: 'certified',
        progress_percentage: 100,
        assessment_score: score,
        certification_track: track,
        completed_at: nowIso,
        last_activity: nowIso,
        updated_at: nowIso,
      }, { onConflict: 'user_id,course_name' });

    if (myCourseErr) {
      logger.warn('[Auto-Issuance] my_courses upsert error:', myCourseErr);
    }

    // 2. Update course_sessions
    await supabase
      .from('course_sessions')
      .update({
        current_step: 'certified',
        progress_percentage: 100,
        updated_at: nowIso,
      })
      .eq('user_id', userId)
      .eq('course_name', courseName);
  } catch (syncErr) {
    logger.warn(`[Auto-Issuance] Could not sync course progress to certified for ${courseName}:`, syncErr);
  }
}

export async function processApprovedPaymentAndIssueCertificate(
  paymentRequestId: string
): Promise<CertificatePayload | null> {
  const existingPromise = inFlightIssuancesByOrderId.get(paymentRequestId);
  if (existingPromise) {
    return existingPromise;
  }

  const executionPromise = (async () => {
    const supabase = getSupabaseAdmin();

    // 1. Fetch the payment request
    const { data: request, error: reqErr } = await supabase
      .from(PAYMENT_REQUESTS_TABLE)
      .select('*')
      .eq('id', paymentRequestId)
      .maybeSingle();

    if (reqErr || !request) {
      logger.warn(`[Auto-Issuance] Payment request not found for ID: ${paymentRequestId}`, reqErr);
      return null;
    }

    if (request.status !== 'paid') {
      logger.warn(`[Auto-Issuance] Payment request ${paymentRequestId} is not marked paid (status: ${request.status})`);
      return null;
    }

    const track = (request.track || 'quickcert') as CertificationTrack;

    // 2. Check if certificate is already issued
    const existingCert = await findCertificateByPaymentRequestId(request.id, null);
    if (existingCert) {
      if (!request.certificate_issued) {
        await markPaymentRequestCertificateIssued(request.id, null);
      }
      await syncCourseProgressToCertified(request.user_id, request.skill, track, existingCert.issuedAt);
      return existingCert;
    }

    // 3. Resolve student name & email safely from public.users & Supabase auth
    let studentName = '';
    let studentEmail = '';
    try {
      const { data: dbUser } = await supabase
        .from('users')
        .select('full_name, username, email')
        .eq('id', request.user_id)
        .maybeSingle();

      const { data: authUserData } = await supabase.auth.admin.getUserById(request.user_id);

      studentEmail = dbUser?.email || authUserData?.user?.email || '';
      studentName =
        dbUser?.full_name ||
        dbUser?.username ||
        authUserData?.user?.user_metadata?.full_name ||
        authUserData?.user?.user_metadata?.name ||
        (studentEmail ? studentEmail.split('@')[0] : '') ||
        'GetCert Student';
    } catch (userErr) {
      logger.warn(`[Auto-Issuance] Failed to resolve user info for ${request.user_id}:`, userErr);
      studentName = 'GetCert Student';
    }

    // 4. Resolve assessment score from course_sessions
    let score = 90;
    try {
      const { data: sessionData } = await supabase
        .from('course_sessions')
        .select('flow_state')
        .eq('user_id', request.user_id)
        .eq('course_name', request.skill)
        .order('updated_at', { ascending: false })
        .limit(1)
        .maybeSingle();

      if (sessionData?.flow_state?.assessmentScore) {
        score = sessionData.flow_state.assessmentScore;
      }
    } catch {
      // Default to 90
    }

    // 5. Issue official certificate
    const issuedCertificate = await issueCertificate({
      userId: request.user_id,
      name: studentName,
      email: studentEmail,
      skill: request.skill,
      projectTitle: request.project_title || `${request.skill} Quick Certification`,
      githubUrl: '',
      proofUrl: '',
      finalScore: score,
      paymentRequestId: request.id,
      paymentTxnId: request.txn_id,
      track,
      authToken: null,
    });

    // 6. Mark payment request as certificate issued
    await markPaymentRequestCertificateIssued(request.id, null);

    // 7. Update my_courses and course_sessions to 100% and certified
    await syncCourseProgressToCertified(request.user_id, request.skill, track, issuedCertificate.issuedAt, score);

    // 8. Send notification email asynchronously
    if (studentEmail) {
      sendCertificateIssuedEmail({
        to: studentEmail,
        name: studentName,
        certificate: issuedCertificate,
      }).catch((emailErr) => {
        logger.warn('[Auto-Issuance] Email dispatch notice:', emailErr);
      });
    }

    logger.info(`[Auto-Issuance] Successfully issued certificate ${issuedCertificate.certificateId} and set my_courses progress to 100% for user ${request.user_id}`);
    return issuedCertificate;
  })();

  inFlightIssuancesByOrderId.set(paymentRequestId, executionPromise);

  try {
    return await executionPromise;
  } finally {
    inFlightIssuancesByOrderId.delete(paymentRequestId);
  }
}