-- Fix for files_to_process() function to prevent duplicate inserts
-- This prevents sj_api_trigger from firing twice when the same file is uploaded
-- Updated: Improved deduplication using exact file path matching and storage object ID
-- Added: NULL safety checks, better error handling, and optimized queries

CREATE OR REPLACE FUNCTION public.files_to_process()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
    payload JSON;
    existing_id INTEGER;
    file_path TEXT;
    path_prefix TEXT;
BEGIN
    -- Validate required fields
    IF NEW.bucket_id IS NULL OR NEW.path_tokens IS NULL OR array_length(NEW.path_tokens, 1) IS NULL THEN
        RETURN NEW; -- Skip processing if required fields are missing
    END IF;

    -- Build the full file path for deduplication
    file_path := array_to_string(NEW.path_tokens, '/');
    path_prefix := NEW.path_tokens[1]; -- First path segment (sjreport, promos, kaching, etc.)
    
    -- Only process files in the 'uploads' bucket
    IF NEW.bucket_id != 'uploads' THEN
        RETURN NEW;
    END IF;
    
    -- Check if bucket_id is 'uploads' and the first element of path_tokens is 'sjreport'
    IF path_prefix = 'sjreport' THEN
        -- Constructing the payload from NEW record data
        payload := json_build_object(
            'bucket_name', NEW.bucket_id,
            'file_name', NEW.name,
            'path_tokens', NEW.path_tokens,
            'storage_id', NEW.id::text,
            'file_path', file_path
        );

        -- Check if a record with the same file path already exists
        -- Check for exact file path match (more reliable than time-based check)
        -- This prevents duplicate inserts from retries or duplicate storage events
        -- Use table alias to avoid ambiguity with payload variable
        SELECT id INTO existing_id
        FROM sjreport_uploads su
        WHERE su.payload->>'file_path' = file_path
          AND su.payload->>'bucket_name' = NEW.bucket_id
        LIMIT 1;

        -- Only insert if no duplicate exists
        IF existing_id IS NULL THEN
            INSERT INTO sjreport_uploads (payload)
            VALUES (payload);
        END IF;
    END IF;

    -- Check if bucket_id is 'uploads' and the first element of path_tokens is 'promos'
    IF path_prefix = 'promos' THEN
        -- Constructing the payload from NEW record data
        payload := json_build_object(
            'bucket_name', NEW.bucket_id,
            'file_name', NEW.name,
            'path_tokens', NEW.path_tokens,
            'storage_id', NEW.id::text,
            'file_path', file_path
        );

        -- Check for duplicates in promo_kaching_uploads using exact file path
        -- Use table alias to avoid ambiguity with payload variable
        SELECT id INTO existing_id
        FROM promo_kaching_uploads pku
        WHERE pku.payload->>'file_path' = file_path
          AND pku.payload->>'bucket_name' = NEW.bucket_id
        LIMIT 1;

        -- Only insert if no duplicate exists
        IF existing_id IS NULL THEN
            INSERT INTO promo_kaching_uploads (payload)
            VALUES (payload);
        END IF;
    END IF;

    -- Check if bucket_id is 'uploads' and the first element of path_tokens is 'kaching'
    IF path_prefix = 'kaching' THEN
        -- Constructing the payload from NEW record data
        payload := json_build_object(
            'bucket_name', NEW.bucket_id,
            'file_name', NEW.name,
            'path_tokens', NEW.path_tokens,
            'storage_id', NEW.id::text,
            'file_path', file_path
        );

        -- Check for duplicates in promo_kaching_uploads using exact file path
        -- Use table alias to avoid ambiguity with payload variable
        SELECT id INTO existing_id
        FROM promo_kaching_uploads pku
        WHERE pku.payload->>'file_path' = file_path
          AND pku.payload->>'bucket_name' = NEW.bucket_id
        LIMIT 1;

        -- Only insert if no duplicate exists
        IF existing_id IS NULL THEN
            INSERT INTO promo_kaching_uploads (payload)
            VALUES (payload);
        END IF;
    END IF;

    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        -- Log error but don't fail the trigger
        -- This ensures storage operations don't fail due to processing errors
        RAISE WARNING 'Error in files_to_process(): %', SQLERRM;
        RETURN NEW;
END;
$function$;

-- Update comment
COMMENT ON FUNCTION public.files_to_process() IS 
'Processes file uploads to storage.objects and inserts into sjreport_uploads or promo_kaching_uploads. 
Includes improved deduplication logic using exact file path matching to prevent duplicate inserts 
when storage triggers fire multiple times. Stores storage_id and file_path in payload for tracking.
Includes NULL safety checks and error handling to prevent trigger failures.';

