-- Clean up existing duplicate user_usage records
-- This migration finds users who have both free tier and subscription usage records
-- and consolidates them properly

-- Function to clean up duplicate usage records for a specific user
CREATE OR REPLACE FUNCTION cleanup_duplicate_usage_for_user(user_uuid UUID)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_record RECORD;
    current_time TIMESTAMPTZ := NOW();
    free_tier_start TIMESTAMPTZ;
    free_tier_end TIMESTAMPTZ;
    free_tier_record user_usage;
    subscription_record_usage user_usage;
    cleanup_result TEXT := 'No action needed';
BEGIN
    -- Get the user's active subscription
    SELECT 
        id,
        user_id, 
        status,
        current_period_start::TIMESTAMPTZ as period_start,
        current_period_end::TIMESTAMPTZ as period_end,
        created_at
    INTO subscription_record
    FROM subscriptions s
    WHERE s.user_id = user_uuid 
    AND s.status IN ('active', 'trialing', 'past_due')
    AND s.current_period_end > current_time
    ORDER BY s.created_at DESC
    LIMIT 1;
    
    -- Only proceed if user has an active subscription
    IF subscription_record IS NULL THEN
        RETURN 'No active subscription';
    END IF;
    
    -- Calculate free tier period for current month
    free_tier_start := DATE_TRUNC('month', current_time::TIMESTAMP)::TIMESTAMPTZ;
    free_tier_end := (free_tier_start + INTERVAL '1 month')::TIMESTAMPTZ;
    
    -- Look for free tier usage record
    SELECT * INTO free_tier_record
    FROM user_usage 
    WHERE user_id = user_uuid 
    AND period_start = free_tier_start 
    AND period_end = free_tier_end;
    
    -- Look for subscription period usage record
    SELECT * INTO subscription_record_usage
    FROM user_usage 
    WHERE user_id = user_uuid 
    AND period_start = subscription_record.period_start
    AND period_end = subscription_record.period_end;
    
    -- Check if we have both records (the duplicate scenario)
    IF free_tier_record IS NOT NULL AND subscription_record_usage IS NOT NULL THEN
        -- Merge the free tier usage into the subscription record
        UPDATE user_usage 
        SET 
            advanced_prompts_used = advanced_prompts_used + free_tier_record.advanced_prompts_used,
            auto_prompts_used = auto_prompts_used + free_tier_record.auto_prompts_used,
            storage_used_gb = storage_used_gb + free_tier_record.storage_used_gb,
            custom_templates_created = custom_templates_created + free_tier_record.custom_templates_created,
            books_created = books_created + free_tier_record.books_created,
            updated_at = NOW()
        WHERE id = subscription_record_usage.id;
        
        -- Delete the free tier record
        DELETE FROM user_usage WHERE id = free_tier_record.id;
        
        -- Log the cleanup
        INSERT INTO usage_logs (user_id, feature_type, action, amount, metadata)
        VALUES (user_uuid, 'advanced_prompts', 'cleanup', 0, 
                jsonb_build_object(
                    'cleanup_type', 'merge_duplicate_records',
                    'merged_advanced_prompts', free_tier_record.advanced_prompts_used,
                    'merged_auto_prompts', free_tier_record.auto_prompts_used,
                    'merged_books', free_tier_record.books_created,
                    'deleted_record_id', free_tier_record.id::text,
                    'kept_record_id', subscription_record_usage.id::text
                ));
        
        cleanup_result := 'Merged ' || free_tier_record.advanced_prompts_used || ' advanced + ' || 
                         free_tier_record.auto_prompts_used || ' auto prompts, ' ||
                         free_tier_record.books_created || ' books';
                         
    ELSIF free_tier_record IS NOT NULL AND subscription_record_usage IS NULL THEN
        -- Only free tier record exists, convert it to subscription period
        UPDATE user_usage 
        SET 
            period_start = subscription_record.period_start,
            period_end = subscription_record.period_end,
            updated_at = NOW()
        WHERE id = free_tier_record.id;
        
        -- Log the conversion
        INSERT INTO usage_logs (user_id, feature_type, action, amount, metadata)
        VALUES (user_uuid, 'advanced_prompts', 'convert', 0, 
                jsonb_build_object(
                    'convert_type', 'free_to_subscription_period',
                    'old_start', free_tier_start,
                    'old_end', free_tier_end,
                    'new_start', subscription_record.period_start,
                    'new_end', subscription_record.period_end
                ));
        
        cleanup_result := 'Converted free tier record to subscription period';
        
    ELSIF free_tier_record IS NULL AND subscription_record_usage IS NOT NULL THEN
        cleanup_result := 'Already has correct subscription record';
    ELSE
        cleanup_result := 'No usage records found';
    END IF;
    
    RETURN cleanup_result;
END;
$$;

-- Run cleanup for all users with active subscriptions
DO $$
DECLARE
    user_record RECORD;
    cleanup_result TEXT;
    total_users INTEGER := 0;
    fixed_users INTEGER := 0;
BEGIN
    RAISE NOTICE 'Starting cleanup of duplicate usage records...';
    
    -- Process all users with active subscriptions
    FOR user_record IN 
        SELECT DISTINCT user_id 
        FROM subscriptions 
        WHERE status IN ('active', 'trialing', 'past_due')
        AND current_period_end > NOW()
    LOOP
        total_users := total_users + 1;
        
        -- Run cleanup for each user
        BEGIN
            SELECT cleanup_duplicate_usage_for_user(user_record.user_id) INTO cleanup_result;
            
            IF cleanup_result != 'No action needed' AND cleanup_result != 'No active subscription' THEN
                fixed_users := fixed_users + 1;
                RAISE NOTICE 'User %: %', user_record.user_id, cleanup_result;
            END IF;
            
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'ERROR cleaning up user %: %', user_record.user_id, SQLERRM;
        END;
    END LOOP;
    
    RAISE NOTICE 'Cleanup complete: % users processed, % users fixed', total_users, fixed_users;
END $$;

-- Function to check for remaining duplicate records (diagnostic)
CREATE OR REPLACE FUNCTION find_users_with_duplicate_usage()
RETURNS TABLE (
    user_id UUID,
    subscription_periods TEXT,
    free_tier_periods TEXT,
    total_records INTEGER
)
LANGUAGE SQL
AS $$
    SELECT 
        uu.user_id,
        STRING_AGG(DISTINCT uu.period_start::TEXT || ' to ' || uu.period_end::TEXT, '; ') as subscription_periods,
        STRING_AGG(DISTINCT 
            CASE 
                WHEN uu.period_start = DATE_TRUNC('month', NOW()) 
                THEN uu.period_start::TEXT || ' to ' || uu.period_end::TEXT 
                ELSE NULL 
            END, '; ') as free_tier_periods,
        COUNT(*) as total_records
    FROM user_usage uu
    WHERE EXISTS (
        SELECT 1 FROM subscriptions s 
        WHERE s.user_id = uu.user_id 
        AND s.status IN ('active', 'trialing', 'past_due')
        AND s.current_period_end > NOW()
    )
    GROUP BY uu.user_id
    HAVING COUNT(*) > 1;
$$;

-- Clean up the diagnostic function after use
COMMENT ON FUNCTION cleanup_duplicate_usage_for_user(UUID) IS 'Fixes duplicate usage records for a user by merging or converting periods';
COMMENT ON FUNCTION find_users_with_duplicate_usage() IS 'Diagnostic function to find users who still have duplicate usage records';