-- Fix timestamp comparison issues in functions
-- The search_path restriction may be causing operator resolution issues

-- Fix user_has_active_subscription function with explicit type casting
CREATE OR REPLACE FUNCTION user_has_active_subscription(user_uuid UUID)
RETURNS BOOLEAN
SECURITY DEFINER
SET search_path = public, 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;

-- Fix get_current_usage_period function with explicit type 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_temp
AS $$
DECLARE
    subscription_record RECORD;
    current_time TIMESTAMPTZ := NOW()::timestamptz;
BEGIN
    -- Get the user's active subscription
    SELECT * INTO subscription_record
    FROM subscriptions s
    WHERE s.user_id = user_uuid 
    AND s.status = 'active'
    AND s.current_period_end::timestamptz > current_time
    ORDER BY s.created_at DESC
    LIMIT 1;
    
    IF subscription_record IS NOT NULL THEN
        -- Return subscription period
        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' - INTERVAL '1 second')::timestamptz;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Alternative approach: Use SQL language instead of plpgsql for simpler functions
-- This avoids some of the search_path issues

CREATE OR REPLACE FUNCTION user_has_active_subscription_alt(user_uuid UUID)
RETURNS BOOLEAN
LANGUAGE SQL
SECURITY DEFINER
AS $$
    SELECT 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 > NOW())
    );
$$;

-- Test if the alternative SQL function works and then we can switch to it
-- For now, keep both versions