-- Fix timestamp casting issue
-- The problem is that current_time is being interpreted as time with time zone
-- Solution: Use CURRENT_TIMESTAMP explicitly and cast properly

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_ts TIMESTAMP := CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
BEGIN
    -- Get the user's active subscription
    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 an active subscription
    IF subscription_record IS NOT NULL AND subscription_record.current_period_end IS NOT NULL THEN
        BEGIN
            IF subscription_record.current_period_end::TIMESTAMPTZ > CURRENT_TIMESTAMP THEN
                RETURN QUERY 
                SELECT 
                    subscription_record.current_period_start::TIMESTAMPTZ,
                    subscription_record.current_period_end::TIMESTAMPTZ;
                RETURN;
            END IF;
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE LOG 'Failed to convert subscription period_end for user %: %', user_uuid, SQLERRM;
        END;
    END IF;
    
    -- Return current month for free tier
    -- Use TIMESTAMP variable for DATE_TRUNC, then convert back to TIMESTAMPTZ
    RETURN QUERY
    SELECT 
        (DATE_TRUNC('month', current_ts) AT TIME ZONE 'UTC')::TIMESTAMPTZ,
        ((DATE_TRUNC('month', current_ts) + INTERVAL '1 month') AT TIME ZONE 'UTC')::TIMESTAMPTZ;
END;
$$ LANGUAGE plpgsql;