-- Fix timestamp comparison operator issue in get_current_usage_period function
-- Error: operator does not exist: timestamp with time zone > time with time zone
-- This happens when PostgreSQL can't find the right comparison operator due to type ambiguity

-- Fix the get_current_usage_period function with explicit timestamp casting
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();
BEGIN
    -- Get the user's active subscription
    -- Use explicit TIMESTAMPTZ casting to avoid operator resolution issues
    SELECT * INTO subscription_record
    FROM subscriptions s
    WHERE s.user_id = user_uuid 
    AND s.status IN ('active', 'trialing', 'past_due')
    AND s.current_period_end::TIMESTAMPTZ > current_time::TIMESTAMPTZ
    ORDER BY s.created_at DESC
    LIMIT 1;
    
    IF subscription_record IS NOT NULL THEN
        -- Return subscription period with explicit casting
        RETURN QUERY 
        SELECT 
            subscription_record.current_period_start::TIMESTAMPTZ,
            subscription_record.current_period_end::TIMESTAMPTZ;
    ELSE
        -- Return current month for free tier
        RETURN QUERY
        SELECT 
            DATE_TRUNC('month', current_time)::TIMESTAMPTZ,
            (DATE_TRUNC('month', current_time) + INTERVAL '1 month')::TIMESTAMPTZ;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Also fix user_has_active_subscription function with the same issue
CREATE OR REPLACE FUNCTION user_has_active_subscription(user_uuid UUID)
RETURNS BOOLEAN
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
BEGIN
    RETURN EXISTS(
        SELECT 1 
        FROM subscriptions 
        WHERE user_id = user_uuid 
        AND status IN ('active', 'trialing')
        AND (current_period_end IS NULL OR current_period_end::TIMESTAMPTZ > NOW()::TIMESTAMPTZ)
    );
END;
$$ LANGUAGE plpgsql;

-- Add helpful comment explaining the fix
COMMENT ON FUNCTION get_current_usage_period(UUID) IS 'Fixed timestamp comparison operator issue with explicit TIMESTAMPTZ casting';
COMMENT ON FUNCTION user_has_active_subscription(UUID) IS 'Fixed timestamp comparison operator issue with explicit TIMESTAMPTZ casting';