-- Migration: Deprecate tablo status and compute it from etapes
-- This migration creates a function to compute tablo status from etapes,
-- updates the user_tablos view to use the computed status, and makes
-- the status column nullable for backwards compatibility.

-- Create function to compute tablo status from etapes (parent 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, consider all etapes as done (empty etapes)
  IF total_tasks = 0 THEN
    RETURN 'done';
  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;
$$;

ALTER FUNCTION "public"."compute_tablo_status"("text") OWNER TO "postgres";

COMMENT ON FUNCTION "public"."compute_tablo_status"("text") IS 'Computes the status of a tablo based on its etapes (parent tasks). Returns todo, in_progress, or done.';

-- Update the user_tablos view to use computed status
CREATE OR REPLACE VIEW "public"."user_tablos" WITH ("security_invoker"='true') AS
SELECT DISTINCT 
    "t"."id",
    "ta"."user_id",
    "t"."name",
    "t"."image",
    "t"."color",
    CAST("public"."compute_tablo_status"("t"."id") AS character varying(20)) AS "status",
    "t"."position",
    "t"."created_at",
    "t"."deleted_at",
    CASE
        WHEN ("ta"."is_admin" = true) THEN 'admin'::"text"
        ELSE 'member'::"text"
    END AS "access_level",
    "ta"."is_admin"
FROM ("public"."tablos" "t"
    LEFT JOIN "public"."tablo_access" "ta" ON (("t"."id" = "ta"."tablo_id")))
WHERE (("ta"."is_active" = true) AND ("t"."deleted_at" IS NULL))
ORDER BY "t"."position", "t"."created_at" DESC;

ALTER TABLE "public"."user_tablos" OWNER TO "postgres";

COMMENT ON VIEW "public"."user_tablos" IS 'View that returns all tablos accessible to the current authenticated user, with status computed from etapes';

-- Make the status column nullable in tablos table (for backwards compatibility)
-- Remove the NOT NULL constraint
ALTER TABLE "public"."tablos" 
  ALTER COLUMN "status" DROP NOT NULL;

-- Remove the CHECK constraint on status
ALTER TABLE "public"."tablos" 
  DROP CONSTRAINT IF EXISTS "tablos_status_check";

-- Add a comment to indicate the column is deprecated
COMMENT ON COLUMN "public"."tablos"."status" IS 'DEPRECATED: Status is now computed from etapes. This column is kept for backwards compatibility but should not be used.';

-- Update events_and_tablos view to use computed status as well
CREATE OR REPLACE VIEW "public"."events_and_tablos" WITH ("security_invoker"='true') AS
SELECT DISTINCT 
    "e"."id" AS "event_id",
    "e"."title",
    "e"."start_date",
    "e"."start_time",
    "e"."end_time",
    "e"."description",
    "t"."id" AS "tablo_id",
    "t"."name" AS "tablo_name",
    "t"."color" AS "tablo_color",
    CAST("public"."compute_tablo_status"("t"."id") AS character varying(20)) AS "tablo_status"
FROM "public"."events" "e"
LEFT JOIN "public"."tablos" "t" ON ("e"."tablo_id" = "t"."id")
WHERE ("e"."deleted_at" IS NULL) AND ("t"."deleted_at" IS NULL)
ORDER BY "e"."start_date", "e"."start_time";

ALTER TABLE "public"."events_and_tablos" OWNER TO "postgres";

COMMENT ON VIEW "public"."events_and_tablos" IS 'View combining events with their associated tablo information, with status computed from etapes';

