-- Fix compute_tablo_status to return 'todo' when there are no tasks
-- This migration updates the function to return 'todo' instead of 'done'
-- when a tablo has etapes but no child tasks

CREATE OR REPLACE FUNCTION "public"."compute_tablo_status"("tablo_id_param" "text")
RETURNS "text"
LANGUAGE "plpgsql"
STABLE
AS $$
DECLARE
  etape_count INTEGER;
  total_tasks INTEGER;
  done_tasks INTEGER;
  in_progress_tasks INTEGER;
  computed_status TEXT;
BEGIN
  -- Count total etapes for this tablo
  SELECT COUNT(*)
  INTO etape_count
  FROM "public"."tasks"
  WHERE "tablo_id" = tablo_id_param
    AND "is_parent" = true;

  -- If no etapes exist, return 'todo'
  IF etape_count = 0 THEN
    RETURN 'todo';
  END IF;

  -- Count tasks across all etapes (excluding parent tasks)
  SELECT 
    COUNT(*),
    COUNT(CASE WHEN "status" = 'done' THEN 1 END),
    COUNT(CASE WHEN "status" IN ('in_progress', 'in_review') THEN 1 END)
  INTO total_tasks, done_tasks, in_progress_tasks
  FROM "public"."tasks"
  WHERE "tablo_id" = tablo_id_param
    AND "is_parent" = false;

  -- If no child tasks exist, return 'todo' (no tasks present)
  IF total_tasks = 0 THEN
    RETURN 'todo';
  END IF;

  -- Determine status based on task counts
  -- Priority order: done > in_progress > todo
  IF done_tasks = total_tasks THEN
    -- All tasks are done
    computed_status := 'done';
  ELSIF in_progress_tasks > 0 THEN
    -- At least one task is actively in progress or in review
    computed_status := 'in_progress';
  ELSIF done_tasks > 0 THEN
    -- Some tasks are done but none are in progress (showing progress)
    computed_status := 'in_progress';
  ELSE
    -- All tasks are todo (no progress has been made)
    computed_status := 'todo';
  END IF;

  RETURN computed_status;
END;
$$;

