-- Fix the usage increment error caused by timestamp casting issues
-- This addresses the "function date_trunc(unknown, time with time zone) does not exist" error

-- First, fix the get_current_usage_period function to handle timestamp casting 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_time TIMESTAMPTZ := NOW();
    safe_period_start TIMESTAMPTZ;
    safe_period_end TIMESTAMPTZ;
BEGIN
    -- Get the user's active subscription with safe timestamp handling
    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 a subscription and handle the timestamp conversion safely
    IF subscription_record IS NOT NULL AND 
       subscription_record.current_period_start IS NOT NULL AND 
       subscription_record.current_period_end IS NOT NULL THEN
        
        -- Try to safely convert timestamps
        BEGIN
            -- Handle both TIMESTAMPTZ and TIME WITH TIME ZONE formats
            safe_period_start := subscription_record.current_period_start;
            safe_period_end := subscription_record.current_period_end;
            
            -- Check if this subscription is still active
            IF safe_period_end > current_time THEN
                -- Return subscription period
                RETURN QUERY 
                SELECT 
                    safe_period_start,
                    safe_period_end;
                RETURN;
            END IF;
        EXCEPTION 
            WHEN OTHERS THEN
                -- If timestamp conversion fails, log and fall through to free tier
                RAISE LOG 'Failed to convert subscription periods for user %: %', user_uuid, SQLERRM;
        END;
    END IF;
    
    -- Return current month for free tier (or when subscription processing fails)
    RETURN QUERY
    SELECT 
        DATE_TRUNC('month', current_time)::TIMESTAMPTZ,
        (DATE_TRUNC('month', current_time) + INTERVAL '1 month')::TIMESTAMPTZ;
END;
$$ LANGUAGE plpgsql;

-- Update the increment_usage function to be more robust with error handling
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 with error handling
    BEGIN
        SELECT * INTO usage_record FROM get_or_create_current_usage(user_uuid);
    EXCEPTION 
        WHEN OTHERS THEN
            -- If get_or_create fails, create a basic monthly record
            RAISE LOG 'Failed to get_or_create usage for user %, creating fallback: %', user_uuid, SQLERRM;
            
            -- Create a fallback monthly record
            INSERT INTO user_usage (
                user_id, 
                period_start, 
                period_end
            ) VALUES (
                user_uuid,
                DATE_TRUNC('month', NOW())::TIMESTAMPTZ,
                (DATE_TRUNC('month', NOW()) + INTERVAL '1 month')::TIMESTAMPTZ
            )
            ON CONFLICT (user_id, period_start, period_end) 
            DO UPDATE SET updated_at = NOW()
            RETURNING * INTO usage_record;
    END;
    
    -- Get current value for the feature type
    CASE feature_type_param
        WHEN 'advanced_prompts' THEN
            current_value := usage_record.advanced_prompts_used;
        WHEN 'auto_prompts' THEN
            current_value := usage_record.auto_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
    CASE feature_type_param
        WHEN 'advanced_prompts' THEN
            UPDATE user_usage 
            SET advanced_prompts_used = advanced_prompts_used + amount_param::INTEGER,
                updated_at = NOW()
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'auto_prompts' THEN
            UPDATE user_usage 
            SET auto_prompts_used = auto_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;

-- Also update user_has_active_subscription to be more robust
CREATE OR REPLACE FUNCTION user_has_active_subscription(user_uuid UUID)
RETURNS BOOLEAN
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_count INTEGER;
    current_time TIMESTAMPTZ := NOW();
BEGIN
    -- Use a safer approach that doesn't rely on timestamp casting
    SELECT COUNT(*) INTO subscription_count
    FROM subscriptions 
    WHERE user_id = user_uuid 
    AND status IN ('active', 'trialing')
    AND (
        current_period_end IS NULL 
        OR (
            current_period_end IS NOT NULL 
            AND current_period_end > current_time
        )
    );
    
    RETURN subscription_count > 0;
EXCEPTION 
    WHEN OTHERS THEN
        -- If there's any error, assume no active subscription
        RAISE LOG 'Error checking subscription for user %: %', user_uuid, SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Create a function to refresh subscription cache (for frontend issues)
CREATE OR REPLACE FUNCTION refresh_user_subscription_status(user_uuid UUID)
RETURNS JSON
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_record RECORD;
    usage_record RECORD;
    result JSON;
BEGIN
    -- Get current subscription
    SELECT 
        price_id, 
        status, 
        current_period_start, 
        current_period_end,
        cancel_at_period_end
    INTO subscription_record
    FROM subscriptions 
    WHERE user_id = user_uuid 
    AND status IN ('active', 'trialing', 'past_due')
    ORDER BY created_at DESC
    LIMIT 1;
    
    -- Get current usage
    SELECT * INTO usage_record FROM get_or_create_current_usage(user_uuid);
    
    -- Return comprehensive status
    SELECT json_build_object(
        'has_subscription', subscription_record IS NOT NULL,
        'subscription', CASE 
            WHEN subscription_record IS NOT NULL THEN 
                json_build_object(
                    'price_id', subscription_record.price_id,
                    'status', subscription_record.status,
                    'period_start', subscription_record.current_period_start,
                    'period_end', subscription_record.current_period_end,
                    'cancel_at_period_end', subscription_record.cancel_at_period_end
                )
            ELSE NULL
        END,
        'usage', json_build_object(
            'id', usage_record.id,
            'period_start', usage_record.period_start,
            'period_end', usage_record.period_end,
            'advanced_prompts_used', usage_record.advanced_prompts_used,
            'auto_prompts_used', usage_record.auto_prompts_used
        )
    ) INTO result;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Add helpful comments
COMMENT ON FUNCTION get_current_usage_period(UUID) IS 'Fixed timestamp casting issues with robust error handling';
COMMENT ON FUNCTION increment_usage(UUID, TEXT, DECIMAL, JSONB, BOOLEAN) IS 'Fixed timestamp casting issues with fallback record creation';
COMMENT ON FUNCTION refresh_user_subscription_status(UUID) IS 'Returns comprehensive subscription and usage status for debugging frontend issues';