-- Fix DATE_TRUNC casting issues properly
-- The issue is that DATE_TRUNC expects (text, timestamp) but we're passing (text, timestamptz)
-- Solution: Cast NOW() to timestamp before using DATE_TRUNC

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
    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_time 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
    -- Fix: Cast NOW() to timestamp before DATE_TRUNC, then cast result back to TIMESTAMPTZ
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', current_time::timestamp)::TIMESTAMPTZ,
        (DATE_TRUNC('month', current_time::timestamp) + INTERVAL '1 month')::TIMESTAMPTZ;
END;
$$ LANGUAGE plpgsql;