-- Export owned clip analysis data to S3 for efficient bulk processing
-- Leverages p_date clustering for optimal performance

CREATE OR REPLACE PROCEDURE EXPORT_OWNED_CLIP_ANALYSIS_TO_S3(
    START_DATE STRING,
    END_DATE STRING
)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    current_date DATE;
    export_query STRING;
    stage_path STRING;
    result_message STRING DEFAULT '';
    total_days INTEGER DEFAULT 0;
    error_message STRING;
BEGIN
    -- Validate date parameters
    IF (START_DATE IS NULL OR END_DATE IS NULL) THEN
        RETURN 'Error: START_DATE and END_DATE parameters are required';
    END IF;
    
    -- Initialize current_date to start_date
    current_date := TO_DATE(START_DATE, 'YYYY-MM-DD');
    
    -- Log start of export
    result_message := 'Starting export of owned clip analysis data from ' || START_DATE || ' to ' || END_DATE || '\n';
    
    -- Loop through each date
    WHILE (current_date <= TO_DATE(END_DATE, 'YYYY-MM-DD')) DO
        BEGIN
            -- Construct S3 stage path for this date partition
            stage_path := '@SUNO_DATABASE_EVENTS/owned_clip_analysis_export/partition_date=' || TO_VARCHAR(current_date, 'YYYY-MM-DD') || '/';
            
            -- Remove existing files in this partition to ensure clean export
            BEGIN
                EXECUTE IMMEDIATE 'REMOVE ' || stage_path;
            EXCEPTION
                WHEN OTHER THEN
                    -- Ignore errors if no files exist to remove
                    NULL;
            END;
            
            -- Construct and execute COPY INTO command with HEADER = TRUE
            -- Uses p_date clustering for optimal performance
            -- HEADER = TRUE preserves column names from AS aliases in Parquet files
            export_query := 'COPY INTO ' || stage_path || 
                ' FROM (' ||
                ' SELECT ' ||
                '   backup_clip_id AS BACKUP_CLIP_ID,' ||
                '   ownership_status AS OWNERSHIP_STATUS,' ||
                '   ownership_reason AS OWNERSHIP_REASON,' ||
                '   clip_id AS CLIP_ID,' ||
                '   user_id AS USER_ID,' ||
                '   user_usage_plan AS USER_USAGE_PLAN,' ||
                '   clip_created_at::timestamp_ntz AS CLIP_CREATED_AT,' ||
                '   p_date AS P_DATE' ||
                ' FROM owned_clip_analysis' ||
                ' WHERE p_date = ''' || TO_VARCHAR(current_date, 'YYYY-MM-DD') || '''' ||
                '   AND clip_created_at <= TO_TIMESTAMP(''' || END_DATE || ' 23:59:59'', ''YYYY-MM-DD HH24:MI:SS'')' ||
                ') ' ||
                'FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ' ||
                'HEADER = TRUE ' ||
                'MAX_FILE_SIZE = 268435456 ' ||  -- 256MB max per file for optimal Spark processing
                'OVERWRITE = TRUE';
            
            -- Execute the export
            EXECUTE IMMEDIATE export_query;
            
            total_days := total_days + 1;
            result_message := result_message || 'Exported data for ' || TO_VARCHAR(current_date, 'YYYY-MM-DD') || '\n';
            
        EXCEPTION
            WHEN OTHER THEN
                error_message := 'Error exporting data for ' || TO_VARCHAR(current_date, 'YYYY-MM-DD') || ': ' || SQLERRM;
                result_message := result_message || error_message || '\n';
                -- Continue with next date rather than failing entire export
        END;
        
        -- Move to next date
        current_date := DATEADD('day', 1, current_date);
    END WHILE;
    
    -- Final summary
    result_message := result_message || '\nExport completed. Processed ' || total_days || ' days.';
    
    RETURN result_message;
END;
$$