thrilltrack-explorer/src-old/lib/moderation/actions.ts

/**
 * Moderation Actions
 * 
 * Business logic for performing moderation actions on submissions.
 * Handles approval, rejection, and deletion workflows with proper
 * error handling and database updates.
 */

import { SupabaseClient } from '@supabase/supabase-js';
import { createTableQuery } from '@/lib/supabaseHelpers';
import type { ModerationItem } from '@/types/moderation';
import { handleError, handleNonCriticalError, getErrorMessage } from '@/lib/errorHandler';
import { invokeWithTracking, invokeBatchWithTracking } from '@/lib/edgeFunctionTracking';

/**
 * Type-safe update data for review moderation
 * Note: These types document the expected structure. Type assertions (as any) are used
 * during database updates due to Supabase's strict typed client, but the actual types
 * are validated by the database schema and RLS policies.
 */
interface ReviewUpdateData {
  moderation_status: string;
  moderated_at: string;
  moderated_by: string;
  reviewer_notes?: string;
  locked_until?: null;
  locked_by?: null;
}

/**
 * Type-safe update data for submission moderation
 * Note: These types document the expected structure. Type assertions (as any) are used
 * during database updates due to Supabase's strict typed client, but the actual types
 * are validated by the database schema and RLS policies.
 */
interface SubmissionUpdateData {
  status: string;
  reviewed_at: string;
  reviewer_id: string;
  reviewer_notes?: string;
  locked_until?: null;
  locked_by?: null;
}

/**
 * Discriminated union for moderation updates (documentation purposes)
 */
type ModerationUpdateData = ReviewUpdateData | SubmissionUpdateData;

/**
 * Result of a moderation action
 */
export interface ModerationActionResult {
  success: boolean;
  message: string;
  error?: Error;
  shouldRemoveFromQueue: boolean;
}

/**
 * Configuration for photo approval
 */
interface PhotoApprovalConfig {
  submissionId: string;
  moderatorId: string;
  moderatorNotes?: string;
}

/**
 * Approve a photo submission
 * 
 * Creates photo records in the database and updates submission status.
 * Handles both new approvals and re-approvals (where photos already exist).
 * 
 * @param supabase - Supabase client
 * @param config - Photo approval configuration
 * @returns Action result with success status and message
 */
export async function approvePhotoSubmission(
  supabase: SupabaseClient,
  config: PhotoApprovalConfig
): Promise<ModerationActionResult> {
  try {
    // Fetch photo submission from relational tables
    const { data: photoSubmission, error: fetchError } = await supabase
      .from('photo_submissions')
      .select(`
        *,
        items:photo_submission_items(*),
        submission:content_submissions!inner(user_id, status)
      `)
      .eq('submission_id', config.submissionId)
      .single();

    if (fetchError || !photoSubmission) {
      throw new Error('Failed to fetch photo submission data');
    }

    if (!photoSubmission.items || photoSubmission.items.length === 0) {
      throw new Error('No photos found in submission');
    }

    // Check if photos already exist for this submission (re-approval case)
    const { data: existingPhotos } = await supabase
      .from('photos')
      .select('id')
      .eq('submission_id', config.submissionId);

    if (!existingPhotos || existingPhotos.length === 0) {
      // Create new photo records from photo_submission_items
      const photoRecords = photoSubmission.items.map((item: any) => ({
        entity_id: photoSubmission.entity_id,
        entity_type: photoSubmission.entity_type,
        cloudflare_image_id: item.cloudflare_image_id,
        cloudflare_image_url: item.cloudflare_image_url,
        title: item.title || null,
        caption: item.caption || null,
        date_taken: item.date_taken || null,
        order_index: item.order_index,
        submission_id: photoSubmission.submission_id,
        submitted_by: photoSubmission.submission?.user_id,
        approved_by: config.moderatorId,
        approved_at: new Date().toISOString(),
      }));

      const { error: insertError } = await supabase
        .from('photos')
        .insert(photoRecords);

      if (insertError) {
        throw insertError;
      }
    }

    // Update submission status
    const { error: updateError } = await supabase
      .from('content_submissions')
      .update({
        status: 'approved' as const,
        reviewer_id: config.moderatorId,
        reviewed_at: new Date().toISOString(),
        reviewer_notes: config.moderatorNotes,
      })
      .eq('id', config.submissionId);

    if (updateError) {
      throw updateError;
    }

    return {
      success: true,
      message: `Successfully approved and published ${photoSubmission.items.length} photo(s)`,
      shouldRemoveFromQueue: true,
    };
  } catch (error: unknown) {
    handleError(error, {
      action: 'Approve Photo Submission',
      userId: config.moderatorId,
      metadata: { submissionId: config.submissionId }
    });
    return {
      success: false,
      message: 'Failed to approve photo submission',
      error: error instanceof Error ? error : new Error(getErrorMessage(error)),
      shouldRemoveFromQueue: false,
    };
  }
}

/**
 * Approve a submission with submission_items
 * 
 * Uses the edge function to process all pending submission items.
 * 
 * @param supabase - Supabase client
 * @param submissionId - Submission ID
 * @param itemIds - Array of item IDs to approve
 * @returns Action result
 */
/**
 * Approve submission items using atomic transaction RPC.
 * 
 * This function uses PostgreSQL's ACID transaction guarantees to ensure
 * all-or-nothing approval with automatic rollback on any error.
 * 
 * The approval process is handled entirely within a single database transaction
 * via the process_approval_transaction() RPC function, which guarantees:
 * - True atomic transactions (all-or-nothing)
 * - Automatic rollback on ANY error
 * - Network-resilient (edge function crash = auto rollback)
 * - Zero orphaned entities
 */
export async function approveSubmissionItems(
  supabase: SupabaseClient,
  submissionId: string,
  itemIds: string[]
): Promise<ModerationActionResult> {
  try {
    console.log(`[Approval] Processing ${itemIds.length} items via atomic transaction`, {
      submissionId,
      itemCount: itemIds.length
    });

    const { data: approvalData, error: approvalError, requestId } = await invokeWithTracking(
      'process-selective-approval',
      {
        itemIds,
        submissionId,
      }
    );

    if (approvalError) {
      const error = new Error(`Failed to process submission items: ${approvalError.message}`);
      handleError(error, {
        action: 'Approve Submission Items',
        metadata: { submissionId, itemCount: itemIds.length, requestId }
      });
      throw error;
    }


    return {
      success: true,
      message: `Successfully processed ${itemIds.length} item(s)`,
      shouldRemoveFromQueue: true,
    };
  } catch (error: unknown) {
    handleError(error, {
      action: 'Approve Submission Items',
      metadata: { submissionId, itemCount: itemIds.length }
    });
    return {
      success: false,
      message: 'Failed to approve submission items',
      error: error instanceof Error ? error : new Error(getErrorMessage(error)),
      shouldRemoveFromQueue: false,
    };
  }
}

/**
 * Reject a submission with submission_items
 * 
 * Cascades rejection to all pending items.
 * 
 * @param supabase - Supabase client
 * @param submissionId - Submission ID
 * @param rejectionReason - Reason for rejection
 * @returns Action result
 */
export async function rejectSubmissionItems(
  supabase: SupabaseClient,
  submissionId: string,
  rejectionReason?: string
): Promise<ModerationActionResult> {
  try {
    const { error: rejectError } = await supabase
      .from('submission_items')
      .update({
        status: 'rejected' as const,
        rejection_reason: rejectionReason || 'Parent submission rejected',
        updated_at: new Date().toISOString(),
      })
      .eq('submission_id', submissionId)
      .eq('status', 'pending');

    if (rejectError) {
      handleError(rejectError, {
        action: 'Reject Submission Items (Cascade)',
        metadata: { submissionId }
      });
    }

    return {
      success: true,
      message: 'Submission items rejected',
      shouldRemoveFromQueue: false, // Parent rejection will handle removal
    };
  } catch (error: unknown) {
    handleError(error, {
      action: 'Reject Submission Items',
      metadata: { submissionId }
    });
    return {
      success: false,
      message: 'Failed to reject submission items',
      error: error instanceof Error ? error : new Error(getErrorMessage(error)),
      shouldRemoveFromQueue: false,
    };
  }
}

/**
 * Configuration for standard moderation actions
 */
export interface ModerationConfig {
  item: ModerationItem;
  action: 'approved' | 'rejected';
  moderatorId: string;
  moderatorNotes?: string;
}

/**
 * Perform a standard moderation action (approve/reject)
 * 
 * Updates the submission or review status in the database.
 * Handles both content_submissions and reviews.
 * 
 * @param supabase - Supabase client
 * @param config - Moderation configuration
 * @returns Action result
 */
export async function performModerationAction(
  supabase: SupabaseClient,
  config: ModerationConfig
): Promise<ModerationActionResult> {
  const { item, action, moderatorId, moderatorNotes } = config;

  try {
    // Handle photo submissions specially
    if (
      action === 'approved' &&
      item.type === 'content_submission' &&
      item.submission_type === 'photo'
    ) {
      return await approvePhotoSubmission(supabase, {
        submissionId: item.id,
        moderatorId,
        moderatorNotes,
      });
    }

    // Check if this submission has submission_items
    if (item.type === 'content_submission') {
      const { data: submissionItems, error: itemsError } = await supabase
        .from('submission_items')
        .select('id, status')
        .eq('submission_id', item.id)
        .in('status', ['pending', 'rejected']);

      if (!itemsError && submissionItems && submissionItems.length > 0) {
        if (action === 'approved') {
          return await approveSubmissionItems(
            supabase,
            item.id,
            submissionItems.map(i => i.id)
          );
        } else if (action === 'rejected') {
          await rejectSubmissionItems(supabase, item.id, moderatorNotes);
        }
      }
    }

    // Standard moderation flow - Build update object with type-appropriate fields
    let error: any = null;
    let data: any = null;

    // Use type-safe table queries based on item type
    if (item.type === 'review') {
      const reviewUpdate: {
        moderation_status: 'approved' | 'rejected' | 'pending';
        moderated_at: string;
        moderated_by: string;
        reviewer_notes?: string;
      } = {
        moderation_status: action,
        moderated_at: new Date().toISOString(),
        moderated_by: moderatorId,
        ...(moderatorNotes && { reviewer_notes: moderatorNotes }),
      };
      
      const result = await createTableQuery('reviews')
        .update(reviewUpdate)
        .eq('id', item.id)
        .select();
      error = result.error;
      data = result.data;
    } else {
      const submissionUpdate: {
        status: 'approved' | 'rejected' | 'pending';
        reviewed_at: string;
        reviewer_id: string;
        reviewer_notes?: string;
      } = {
        status: action,
        reviewed_at: new Date().toISOString(),
        reviewer_id: moderatorId,
        ...(moderatorNotes && { reviewer_notes: moderatorNotes }),
      };
      
      const result = await createTableQuery('content_submissions')
        .update(submissionUpdate)
        .eq('id', item.id)
        .select();
      error = result.error;
      data = result.data;
    }

    if (error) {
      throw error;
    }

    // Check if the update actually affected any rows
    if (!data || data.length === 0) {
      throw new Error(
        'Failed to update item - no rows affected. You might not have permission to moderate this content.'
      );
    }

    return {
      success: true,
      message: `Content ${action}`,
      shouldRemoveFromQueue: action === 'approved' || action === 'rejected',
    };
  } catch (error: unknown) {
    handleError(error, {
      action: `${config.action === 'approved' ? 'Approve' : 'Reject'} Content`,
      userId: config.moderatorId,
      metadata: { itemType: item.type, itemId: item.id }
    });
    return {
      success: false,
      message: `Failed to ${config.action} content`,
      error: error instanceof Error ? error : new Error(getErrorMessage(error)),
      shouldRemoveFromQueue: false,
    };
  }
}

/**
 * Configuration for submission deletion
 */
export interface DeleteSubmissionConfig {
  item: ModerationItem;
  deletePhotos?: boolean;
}

/**
 * Delete a submission and its associated photos
 * 
 * Extracts photo IDs, deletes them from Cloudflare, then deletes the submission.
 * 
 * @param supabase - Supabase client
 * @param config - Deletion configuration
 * @returns Action result
 */
export async function deleteSubmission(
  supabase: SupabaseClient,
  config: DeleteSubmissionConfig
): Promise<ModerationActionResult> {
  const { item, deletePhotos = true } = config;

  if (item.type !== 'content_submission') {
    return {
      success: false,
      message: 'Can only delete content submissions',
      shouldRemoveFromQueue: false,
    };
  }

  try {
    let deletedPhotoCount = 0;
    let skippedPhotoCount = 0;

    // Extract and delete photos if requested
    if (deletePhotos) {
      const photosArray = item.content?.content?.photos || item.content?.photos;

      if (photosArray && Array.isArray(photosArray)) {
        const validImageIds: string[] = [];

        for (const photo of photosArray) {
          let imageId = '';

          if (photo.imageId) {
            imageId = photo.imageId;
          } else if (photo.url && !photo.url.startsWith('blob:')) {
            // Try to extract from URL
            const uuidRegex =
              /^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$/i;

            if (uuidRegex.test(photo.url)) {
              imageId = photo.url;
            } else {
              const cloudflareMatch = photo.url.match(
                /imagedelivery\.net\/[^\/]+\/([a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12})/i
              );
              if (cloudflareMatch) {
                imageId = cloudflareMatch[1];
              }
            }
          }

          if (imageId) {
            validImageIds.push(imageId);
          } else {
            skippedPhotoCount++;
          }
        }

        // Delete photos from Cloudflare
        if (validImageIds.length > 0) {
          const deleteResults = await invokeBatchWithTracking(
            validImageIds.map(imageId => ({
              functionName: 'upload-image',
              payload: { action: 'delete', imageId },
            })),
            undefined
          );

          // Count successful deletions
          const successfulDeletions = deleteResults.filter(r => !r.error);
          deletedPhotoCount = successfulDeletions.length;

          // Log any failures silently (background operation)
          const failedDeletions = deleteResults.filter(r => r.error);
          if (failedDeletions.length > 0) {
            handleNonCriticalError(
              new Error(`Failed to delete ${failedDeletions.length} of ${validImageIds.length} photos`),
              {
                action: 'Delete Submission Photos',
                metadata: {
                  failureCount: failedDeletions.length,
                  totalAttempted: validImageIds.length,
                  failedRequestIds: failedDeletions.map(r => r.requestId)
                }
              }
            );
          }
        }
      }
    }

    // Delete the submission from the database
    const { error } = await supabase
      .from('content_submissions')
      .delete()
      .eq('id', item.id);

    if (error) {
      throw error;
    }

    // Verify deletion
    const { data: checkData, error: checkError } = await supabase
      .from('content_submissions')
      .select('id')
      .eq('id', item.id)
      .single();

    if (checkData && !checkError) {
      throw new Error('Deletion failed - item still exists in database');
    }

    // Build result message
    let message = 'The submission has been permanently deleted';
    if (deletedPhotoCount > 0 && skippedPhotoCount > 0) {
      message = `The submission and ${deletedPhotoCount} photo(s) have been deleted. ${skippedPhotoCount} photo(s) could not be deleted from storage`;
    } else if (deletedPhotoCount > 0) {
      message = `The submission and ${deletedPhotoCount} associated photo(s) have been permanently deleted`;
    } else if (skippedPhotoCount > 0) {
      message = `The submission has been deleted. ${skippedPhotoCount} photo(s) could not be deleted from storage`;
    }

    return {
      success: true,
      message,
      shouldRemoveFromQueue: true,
    };
  } catch (error: unknown) {
    handleError(error, {
      action: 'Delete Submission',
      metadata: { submissionId: item.id, deletePhotos }
    });
    return {
      success: false,
      message: 'Failed to delete submission',
      error: error instanceof Error ? error : new Error('Unknown error'),
      shouldRemoveFromQueue: false,
    };
  }
}