-- Fix ai_usage table model_tier constraint to use smart/fast instead of advanced/auto
-- This aligns with our new AI model naming convention

-- Drop the old check constraint first
ALTER TABLE ai_usage 
DROP CONSTRAINT ai_usage_model_tier_check;

-- Update existing ai_usage records to use new model tier names
UPDATE ai_usage 
SET model_tier = 'smart' 
WHERE model_tier = 'advanced';

UPDATE ai_usage 
SET model_tier = 'fast' 
WHERE model_tier = 'auto';

-- Add the new check constraint with smart/fast values
ALTER TABLE ai_usage 
ADD CONSTRAINT ai_usage_model_tier_check 
CHECK (model_tier IN ('smart', 'fast'));

-- Update the get_monthly_ai_usage function to use new tier names
CREATE OR REPLACE FUNCTION get_monthly_ai_usage(p_user_id UUID, p_tier TEXT DEFAULT NULL)
RETURNS TABLE (
    tier TEXT,
    usage_count BIGINT,
    total_tokens BIGINT,
    total_cost DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        au.model_tier as tier,
        COUNT(*)::BIGINT as usage_count,
        COALESCE(SUM(au.total_tokens), 0)::BIGINT as total_tokens,
        COALESCE(SUM(au.cost_usd), 0)::DECIMAL as total_cost
    FROM ai_usage au
    WHERE au.user_id = p_user_id
        AND au.billing_month = DATE_TRUNC('month', NOW())
        AND au.success = true -- Only count successful requests
        AND (p_tier IS NULL OR au.model_tier = p_tier)
    GROUP BY au.model_tier;
END;
$$ LANGUAGE plpgsql;

-- Add helpful comment
COMMENT ON CONSTRAINT ai_usage_model_tier_check ON ai_usage IS 'Updated to use smart/fast model tiers for consistency with new AI model naming';