Spaces:

Sarvesh213
/

getcert-backend

Running

getcert-backend / api /v2 /_services /paymentApprovalSystem.ts

fix(cert): auto-issue certificate on payment confirmation & sync course to 100% certified

46ab86e 26 days ago

18.4 kB

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);
  }
}

	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);
	}
	}