-- Final fix for the date_trunc function error
-- This addresses the "function date_trunc(unknown, time with time zone) does not exist" error
-- by being very explicit about types and using a simpler approach

-- Drop and recreate the get_current_usage_period function with explicit types
DROP FUNCTION IF EXISTS get_current_usage_period(UUID);

CREATE OR REPLACE FUNCTION get_current_usage_period(user_uuid UUID)
RETURNS TABLE (
    period_start TIMESTAMPTZ,
    period_end TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    subscription_record RECORD;
    now_ts TIMESTAMPTZ;
    month_start TIMESTAMPTZ;
    month_end TIMESTAMPTZ;
BEGIN
    -- Use explicit TIMESTAMPTZ for all time operations
    now_ts := NOW()::TIMESTAMPTZ;
    
    -- Calculate month boundaries using explicit casting
    month_start := DATE_TRUNC('month', now_ts::TIMESTAMP)::TIMESTAMPTZ;
    month_end := (month_start + INTERVAL '1 month')::TIMESTAMPTZ;
    
    -- 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')
    ORDER BY s.created_at DESC
    LIMIT 1;
    
    -- Check if we have an active subscription with valid period
    IF subscription_record IS NOT NULL AND 
       subscription_record.period_start IS NOT NULL AND 
       subscription_record.period_end IS NOT NULL THEN
        
        -- Safely check if subscription is still active
        BEGIN
            IF subscription_record.period_end > now_ts THEN
                -- Return subscription period
                RETURN QUERY 
                SELECT 
                    subscription_record.period_start,
                    subscription_record.period_end;
                RETURN;
            END IF;
        EXCEPTION 
            WHEN OTHERS THEN
                -- If comparison fails, log and fall through to free tier
                RAISE LOG 'Failed to compare subscription periods for user %: %', user_uuid, SQLERRM;
        END;
    END IF;
    
    -- Return current month for free tier or when subscription is not active
    RETURN QUERY
    SELECT 
        month_start,
        month_end;
END;
$$;

-- Also fix the increment_usage function to use the same pattern
DROP FUNCTION IF EXISTS increment_usage(UUID, TEXT, DECIMAL, JSONB, BOOLEAN);

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
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    usage_record user_usage;
    current_value DECIMAL;
    new_value DECIMAL;
    now_ts TIMESTAMPTZ := NOW()::TIMESTAMPTZ;
BEGIN
    -- Get or create current usage record
    usage_record := get_or_create_current_usage(user_uuid);
    
    -- Get current value for the feature type
    current_value := CASE feature_type_param
        WHEN 'advanced_prompts' THEN usage_record.advanced_prompts_used
        WHEN 'auto_prompts' THEN usage_record.auto_prompts_used
        WHEN 'storage_gb' THEN usage_record.storage_used_gb
        WHEN 'custom_templates' THEN usage_record.custom_templates_created
        WHEN 'books' THEN usage_record.books_created
        ELSE 0
    END;
    
    -- Calculate new value
    new_value := current_value + amount_param;
    
    -- Update the specific counter
    CASE feature_type_param
        WHEN 'advanced_prompts' THEN
            UPDATE user_usage 
            SET advanced_prompts_used = new_value::INTEGER,
                updated_at = now_ts
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'auto_prompts' THEN
            UPDATE user_usage 
            SET auto_prompts_used = new_value::INTEGER,
                updated_at = now_ts
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'storage_gb' THEN
            UPDATE user_usage 
            SET storage_used_gb = new_value,
                updated_at = now_ts
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'custom_templates' THEN
            UPDATE user_usage 
            SET custom_templates_created = new_value::INTEGER,
                updated_at = now_ts
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        WHEN 'books' THEN
            UPDATE user_usage 
            SET books_created = new_value::INTEGER,
                updated_at = now_ts
            WHERE id = usage_record.id
            RETURNING * INTO usage_record;
            
        ELSE
            RAISE EXCEPTION 'Invalid feature_type: %', feature_type_param;
    END CASE;
    
    -- 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
    );
    
    RETURN usage_record;
END;
$$;

-- Fix get_or_create_current_usage to use same pattern
DROP FUNCTION IF EXISTS get_or_create_current_usage(UUID);

CREATE OR REPLACE FUNCTION get_or_create_current_usage(user_uuid UUID)
RETURNS user_usage
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
DECLARE
    usage_record user_usage;
    period_info RECORD;
BEGIN
    -- Get current period
    SELECT * INTO period_info FROM get_current_usage_period(user_uuid);
    
    -- Try to get existing usage record
    SELECT * INTO usage_record 
    FROM user_usage 
    WHERE user_id = user_uuid 
    AND period_start = period_info.period_start 
    AND period_end = period_info.period_end;
    
    -- Create if doesn't exist
    IF usage_record IS NULL THEN
        INSERT INTO user_usage (
            user_id, 
            period_start, 
            period_end
        ) VALUES (
            user_uuid,
            period_info.period_start,
            period_info.period_end
        )
        RETURNING * INTO usage_record;
    END IF;
    
    RETURN usage_record;
END;
$$;

-- Add helpful comments
COMMENT ON FUNCTION get_current_usage_period(UUID) IS 'Fixed date_trunc issues with explicit timestamp casting and simplified logic';
COMMENT ON FUNCTION increment_usage(UUID, TEXT, DECIMAL, JSONB, BOOLEAN) IS 'Fixed timestamp handling with explicit TIMESTAMPTZ casting';
COMMENT ON FUNCTION get_or_create_current_usage(UUID) IS 'Gets existing usage record for current period or creates new one';