-- Migration to fix all remaining database functions that reference old column names
-- This ensures all functions use the new smart_prompts_used/fast_prompts_used column names

-- Drop all existing increment_usage function variants
DROP FUNCTION IF EXISTS increment_usage(UUID, TEXT, DECIMAL);
DROP FUNCTION IF EXISTS increment_usage(UUID, TEXT, DECIMAL, JSONB);
DROP FUNCTION IF EXISTS increment_usage(UUID, TEXT, DECIMAL, JSONB, BOOLEAN);

-- Create the correct increment_usage function with proper column references
CREATE OR REPLACE FUNCTION increment_usage(
    user_uuid UUID,
    feature_type_param TEXT,
    amount_param DECIMAL DEFAULT 1,
    metadata_param JSONB DEFAULT '{}'::jsonb,
    skip_limit_check BOOLEAN DEFAULT FALSE
)
RETURNS user_usage
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    usage_record user_usage;
    current_value DECIMAL;
    new_value DECIMAL;
BEGIN
    -- Get or create current usage record
    SELECT * INTO usage_record FROM get_or_create_current_usage(user_uuid);
    
    -- Get current value for the feature type using NEW column names
    CASE feature_type_param
        WHEN 'smart_prompts' THEN
            current_value := usage_record.smart_prompts_used;
        WHEN 'fast_prompts' THEN
            current_value := usage_record.fast_prompts_used;
        WHEN 'storage_gb' THEN
            current_value := usage_record.storage_used_gb;
        WHEN 'custom_templates' THEN
            current_value := usage_record.custom_templates_created;
        WHEN 'books' THEN
            current_value := usage_record.books_created;
        ELSE
            RAISE EXCEPTION 'Invalid feature_type: %', feature_type_param;
    END CASE;
    
    new_value := current_value + amount_param;
    
    -- Prevent negative values (except for storage which can decrease)
    IF new_value < 0 AND feature_type_param != 'storage_gb' THEN
        RAISE EXCEPTION 'Cannot decrement % below zero', feature_type_param;
    END IF;
    
    -- Log the usage increment
    INSERT INTO usage_logs (user_id, feature_type, action, amount, metadata)
    VALUES (user_uuid, feature_type_param, 'increment', amount_param, metadata_param);
    
    -- Update the appropriate counter using NEW column names
    CASE feature_type_param
        WHEN 'smart_prompts' THEN
            UPDATE user_usage 
            SET smart_prompts_used = smart_prompts_used + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'fast_prompts' THEN
            UPDATE user_usage 
            SET fast_prompts_used = fast_prompts_used + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'storage_gb' THEN
            UPDATE user_usage 
            SET storage_used_gb = storage_used_gb + amount_param,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'custom_templates' THEN
            UPDATE user_usage 
            SET custom_templates_created = custom_templates_created + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'books' THEN
            UPDATE user_usage 
            SET books_created = books_created + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        ELSE
            RAISE EXCEPTION 'Invalid feature_type: %', feature_type_param;
    END CASE;
    
    RETURN usage_record;
END;
$$ LANGUAGE plpgsql;

-- Update get_user_usage_stats function to use new column names
CREATE OR REPLACE FUNCTION get_user_usage_stats(p_user_id UUID)
RETURNS JSON
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    result JSON;
    current_usage_record user_usage;
BEGIN
    -- Get current usage record
    SELECT * INTO current_usage_record 
    FROM get_or_create_current_usage(p_user_id);
    
    -- Build result using NEW column names
    SELECT json_build_object(
        'current_month', json_build_object(
            'smart', json_build_object(
                'count', current_usage_record.smart_prompts_used,
                'tokens', 0,
                'cost', 0
            ),
            'fast', json_build_object(
                'count', current_usage_record.fast_prompts_used,
                'tokens', 0,
                'cost', 0
            )
        ),
        'total_books', (
            SELECT COUNT(*) FROM books WHERE user_id = p_user_id
        ),
        'total_words', (
            SELECT COALESCE(SUM(word_count), 0) FROM books WHERE user_id = p_user_id
        )
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Update can_user_make_request function to use new column names
CREATE OR REPLACE FUNCTION can_user_make_request(
    p_user_id UUID, 
    p_tier TEXT, 
    p_limit INTEGER
) RETURNS JSON
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    current_usage INTEGER := 0;
    current_usage_record user_usage;
    result JSON;
BEGIN
    -- Get current usage record
    SELECT * INTO current_usage_record 
    FROM get_or_create_current_usage(p_user_id);
    
    -- Get current usage for the specific tier using NEW column names
    CASE p_tier
        WHEN 'smart' THEN
            current_usage := current_usage_record.smart_prompts_used;
        WHEN 'fast' THEN
            current_usage := current_usage_record.fast_prompts_used;
        ELSE
            current_usage := 0;
    END CASE;
    
    -- Return usage info and whether request can proceed
    SELECT json_build_object(
        'can_proceed', (current_usage < p_limit OR p_limit = 0),
        'current_usage', current_usage,
        'limit', p_limit,
        'remaining', GREATEST(0, p_limit - current_usage)
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Add helpful comments
COMMENT ON FUNCTION increment_usage(UUID, TEXT, DECIMAL, JSONB, BOOLEAN) IS 'Fixed to use new smart_prompts_used/fast_prompts_used column names';
COMMENT ON FUNCTION get_user_usage_stats(UUID) IS 'Fixed to use new smart_prompts_used/fast_prompts_used column names';
COMMENT ON FUNCTION can_user_make_request(UUID, TEXT, INTEGER) IS 'Fixed to use new smart_prompts_used/fast_prompts_used column names';