BEGIN TRANSACTION

BEGIN TRY

    ------------------------------------------------------------------------
    -- 1. Preview records before update
    ------------------------------------------------------------------------
    SELECT 
        RowID,
        site_cd,
        file_name AS old_file_name,
        CASE 
            WHEN file_name LIKE '%.JPG' THEN REPLACE(file_name, '.JPG', '.png')
            WHEN file_name LIKE '%.Jpg' THEN REPLACE(file_name, '.Jpg', '.png')
            WHEN file_name LIKE '%.jpg' THEN REPLACE(file_name, '.jpg', '.png')
            ELSE file_name
        END AS new_file_name
    FROM wko_ref
    WHERE file_name LIKE '%Acknowledge_sign%'
	  AND column2 = 'SIGN'
     

    ------------------------------------------------------------------------
    -- 2. Update file extension
    ------------------------------------------------------------------------
   -- UPDATE wko_ref
   -- SET file_name =
   --     CASE 
   --         WHEN file_name LIKE '%.JPG' THEN REPLACE(file_name, '.JPG', '.png')
   --         WHEN file_name LIKE '%.Jpg' THEN REPLACE(file_name, '.Jpg', '.png')
   --         WHEN file_name LIKE '%.jpg' THEN REPLACE(file_name, '.jpg', '.png')
   --         ELSE file_name
   --     END
   -- WHERE file_name LIKE '%Acknowledge_sign%'
	  --AND column2 = 'SIGN'
     

    ------------------------------------------------------------------------
    -- 3. Verify updated records
    ------------------------------------------------------------------------
    SELECT 
        RowID,
        site_cd,
        file_name
    FROM wko_ref
    WHERE file_name LIKE '%Acknowledge_sign%'
	  AND column2 = 'SIGN'
     
    COMMIT TRANSACTION;
    PRINT 'Update completed successfully.';

END TRY
BEGIN CATCH

    ROLLBACK TRANSACTION;

    PRINT 'Error occurred: ' + ERROR_MESSAGE();

END CATCH