-- Fix timestamp comparison error: operator does not exist: timestamp with time zone <= time with time zone
-- This error occurs when there's corrupted TIME WITH TIME ZONE data instead of TIMESTAMP WITH TIME ZONE

-- Step 1: Clean up any corrupted timestamp data in subscriptions table
-- Find and fix any records with invalid timestamp formats
UPDATE subscriptions 
SET 
    current_period_start = CASE 
        WHEN current_period_start IS NOT NULL THEN 
            (DATE_TRUNC('second', NOW()) + (EXTRACT(EPOCH FROM current_period_start::TIME) || ' seconds')::INTERVAL)::TIMESTAMPTZ
        ELSE NULL 
    END,
    current_period_end = CASE 
        WHEN current_period_end IS NOT NULL THEN 
            (DATE_TRUNC('second', NOW()) + (EXTRACT(EPOCH FROM current_period_end::TIME) || ' seconds')::INTERVAL)::TIMESTAMPTZ  
        ELSE NULL 
    END
WHERE 
    -- Only update records where casting to TIME succeeds (indicating corrupted data)
    (current_period_start IS NOT NULL AND 
     EXTRACT(EPOCH FROM current_period_start::TIME) IS NOT NULL) OR
    (current_period_end IS NOT NULL AND 
     EXTRACT(EPOCH FROM current_period_end::TIME) IS NOT NULL);

-- Step 2: Create a robust get_current_usage_period function with better error handling
CREATE OR REPLACE FUNCTION get_current_usage_period(user_uuid UUID)
RETURNS TABLE (
    period_start TIMESTAMPTZ,
    period_end TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_record RECORD;
    now_ts TIMESTAMPTZ;
    month_start TIMESTAMPTZ;
    month_end TIMESTAMPTZ;
    safe_period_start TIMESTAMPTZ;
    safe_period_end TIMESTAMPTZ;
BEGIN
    -- Use explicit TIMESTAMPTZ for all time operations
    now_ts := NOW();
    
    -- Calculate month boundaries using explicit casting
    month_start := DATE_TRUNC('month', now_ts);
    month_end := month_start + INTERVAL '1 month';
    
    -- Get the user's active subscription with extra safety
    BEGIN
        SELECT 
            id,
            user_id, 
            status,
            current_period_start,
            current_period_end,
            created_at
        INTO subscription_record
        FROM subscriptions s
        WHERE s.user_id = user_uuid 
        AND s.status IN ('active', 'trialing', 'past_due')
        ORDER BY s.created_at DESC
        LIMIT 1;
    EXCEPTION 
        WHEN OTHERS THEN
            -- If subscription query fails, log and return free tier
            RAISE LOG 'Failed to query subscription for user %: %', user_uuid, SQLERRM;
            RETURN QUERY SELECT month_start, month_end;
            RETURN;
    END;
    
    -- Check if we have an active subscription with valid period
    IF subscription_record IS NOT NULL AND 
       subscription_record.current_period_start IS NOT NULL AND 
       subscription_record.current_period_end IS NOT NULL THEN
        
        -- Safely convert subscription timestamps with robust error handling
        BEGIN
            -- Try multiple approaches to handle the timestamp conversion
            BEGIN
                safe_period_start := subscription_record.current_period_start::TIMESTAMPTZ;
                safe_period_end := subscription_record.current_period_end::TIMESTAMPTZ;
            EXCEPTION 
                WHEN OTHERS THEN
                    -- If direct casting fails, try parsing as ISO string
                    BEGIN
                        safe_period_start := subscription_record.current_period_start::TEXT::TIMESTAMPTZ;
                        safe_period_end := subscription_record.current_period_end::TEXT::TIMESTAMPTZ;
                    EXCEPTION 
                        WHEN OTHERS THEN
                            -- If all conversion attempts fail, fall back to current month
                            RAISE LOG 'Failed to convert subscription timestamps for user %: start=%, end=%, error=%', 
                                user_uuid, 
                                subscription_record.current_period_start, 
                                subscription_record.current_period_end, 
                                SQLERRM;
                            RETURN QUERY SELECT month_start, month_end;
                            RETURN;
                    END;
            END;
            
            -- Check if subscription is still active with safe comparison
            BEGIN
                IF safe_period_end > now_ts THEN
                    -- Return subscription period
                    RETURN QUERY SELECT safe_period_start, safe_period_end;
                    RETURN;
                END IF;
            EXCEPTION 
                WHEN OTHERS THEN
                    -- If comparison fails, assume subscription is expired
                    RAISE LOG 'Failed to compare subscription period_end for user %: %', user_uuid, SQLERRM;
            END;
        END;
    END IF;
    
    -- Return current month for free tier or when subscription processing fails
    RETURN QUERY SELECT month_start, month_end;
END;
$$;

-- Step 3: Create a safer get_or_create_current_usage function
CREATE OR REPLACE FUNCTION get_or_create_current_usage(user_uuid UUID)
RETURNS user_usage
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    usage_record user_usage;
    period_info RECORD;
    retry_count INTEGER := 0;
    max_retries INTEGER := 3;
BEGIN
    -- Retry loop for handling transient timestamp conversion issues
    WHILE retry_count < max_retries LOOP
        BEGIN
            -- Get current period
            SELECT * INTO period_info FROM get_current_usage_period(user_uuid);
            
            -- Validate period_info
            IF period_info IS NULL OR 
               period_info.period_start IS NULL OR 
               period_info.period_end IS NULL THEN
                RAISE EXCEPTION 'Invalid period info returned: %', period_info;
            END IF;
            
            -- Try to get existing usage record
            SELECT * INTO usage_record 
            FROM user_usage 
            WHERE user_id = user_uuid 
            AND period_start = period_info.period_start 
            AND period_end = period_info.period_end;
            
            -- Create if doesn't exist
            IF usage_record IS NULL THEN
                INSERT INTO user_usage (
                    user_id, 
                    period_start, 
                    period_end
                ) VALUES (
                    user_uuid,
                    period_info.period_start,
                    period_info.period_end
                )
                RETURNING * INTO usage_record;
            END IF;
            
            -- If we got here, everything worked
            RETURN usage_record;
            
        EXCEPTION 
            WHEN OTHERS THEN
                retry_count := retry_count + 1;
                RAISE LOG 'Failed to get/create usage record for user % (attempt %/%): %', 
                    user_uuid, retry_count, max_retries, SQLERRM;
                
                -- If this is the last retry, re-raise the exception
                IF retry_count >= max_retries THEN
                    RAISE EXCEPTION 'Failed to get/create usage record after % attempts: %', max_retries, SQLERRM;
                END IF;
                
                -- Wait a bit before retrying (simulate a brief delay)
                PERFORM pg_sleep(0.1);
        END;
    END LOOP;
    
    -- This should never be reached, but just in case
    RAISE EXCEPTION 'Unexpected exit from retry loop';
END;
$$;

-- Step 4: Add a cleanup function to fix any remaining timestamp issues
CREATE OR REPLACE FUNCTION cleanup_subscription_timestamps()
RETURNS INTEGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    fixed_count INTEGER := 0;
    sub_record RECORD;
BEGIN
    -- Find subscriptions with potentially corrupted timestamps
    FOR sub_record IN 
        SELECT id, user_id, current_period_start, current_period_end
        FROM subscriptions 
        WHERE current_period_start IS NOT NULL OR current_period_end IS NOT NULL
    LOOP
        BEGIN
            -- Test if the timestamps can be used in comparisons
            PERFORM sub_record.current_period_start::TIMESTAMPTZ > NOW();
            PERFORM sub_record.current_period_end::TIMESTAMPTZ > NOW();
        EXCEPTION 
            WHEN OTHERS THEN
                -- This subscription has corrupted timestamps, try to fix it
                BEGIN
                    UPDATE subscriptions 
                    SET 
                        current_period_start = CASE 
                            WHEN current_period_start IS NOT NULL THEN 
                                COALESCE(
                                    -- Try to parse as ISO string
                                    current_period_start::TEXT::TIMESTAMPTZ,
                                    -- Fallback to current month start
                                    DATE_TRUNC('month', NOW())
                                )
                            ELSE NULL 
                        END,
                        current_period_end = CASE 
                            WHEN current_period_end IS NOT NULL THEN 
                                COALESCE(
                                    -- Try to parse as ISO string  
                                    current_period_end::TEXT::TIMESTAMPTZ,
                                    -- Fallback to next month start
                                    DATE_TRUNC('month', NOW()) + INTERVAL '1 month'
                                )
                            ELSE NULL 
                        END,
                        updated_at = NOW()
                    WHERE id = sub_record.id;
                    
                    fixed_count := fixed_count + 1;
                    RAISE LOG 'Fixed corrupted timestamps for subscription %', sub_record.id;
                EXCEPTION 
                    WHEN OTHERS THEN
                        RAISE LOG 'Failed to fix timestamps for subscription %: %', sub_record.id, SQLERRM;
                END;
        END;
    END LOOP;
    
    RETURN fixed_count;
END;
$$;

-- Step 5: Run the cleanup immediately
SELECT cleanup_subscription_timestamps() as fixed_subscriptions;

-- Step 6: Add helpful comments
COMMENT ON FUNCTION get_current_usage_period(UUID) IS 'Fixed timestamp comparison error with robust error handling and multiple conversion attempts';
COMMENT ON FUNCTION get_or_create_current_usage(UUID) IS 'Added retry logic and better error handling for timestamp conversion issues';
COMMENT ON FUNCTION cleanup_subscription_timestamps() IS 'Utility function to fix corrupted timestamp data in subscriptions table';

-- Step 7: Create a monitoring function to detect future timestamp issues
CREATE OR REPLACE FUNCTION check_subscription_timestamp_health()
RETURNS TABLE (
    subscription_id UUID,
    user_id UUID,
    issue_type TEXT,
    current_period_start_raw TEXT,
    current_period_end_raw TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.id as subscription_id,
        s.user_id,
        CASE 
            WHEN s.current_period_start IS NOT NULL AND 
                 s.current_period_start::TEXT !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}' THEN 
                'invalid_start_format'
            WHEN s.current_period_end IS NOT NULL AND 
                 s.current_period_end::TEXT !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}' THEN 
                'invalid_end_format'
            ELSE 'unknown_issue'
        END as issue_type,
        s.current_period_start::TEXT as current_period_start_raw,
        s.current_period_end::TEXT as current_period_end_raw
    FROM subscriptions s
    WHERE s.current_period_start IS NOT NULL OR s.current_period_end IS NOT NULL;
END;
$$;

COMMENT ON FUNCTION check_subscription_timestamp_health() IS 'Diagnostic function to identify subscription records with potential timestamp issues';