-- Add columns to support parent tasks (Etapes)
ALTER TABLE "public"."tasks"
  ADD COLUMN "is_parent" boolean DEFAULT false NOT NULL;

ALTER TABLE "public"."tasks"
  ADD COLUMN "parent_task_id" text;

ALTER TABLE "public"."tasks"
  ADD CONSTRAINT "tasks_parent_task_id_fkey"
  FOREIGN KEY ("parent_task_id") REFERENCES "public"."tasks"("id") ON DELETE SET NULL;

ALTER TABLE "public"."tasks"
  ADD CONSTRAINT "tasks_parent_self_reference_check"
  CHECK (NOT "is_parent" OR "parent_task_id" IS NULL);

COMMENT ON COLUMN "public"."tasks"."is_parent" IS 'When TRUE, the task represents an Etape (parent task) that can group other tasks';
COMMENT ON COLUMN "public"."tasks"."parent_task_id" IS 'Optional reference to the Etape (parent task) this task belongs to';

-- Index to quickly fetch Etapes per tablo and their ordering, plus children by parent
CREATE INDEX "tasks_parent_scope_idx" ON "public"."tasks" USING btree ("tablo_id", "is_parent", "position");
CREATE INDEX "tasks_parent_task_id_idx" ON "public"."tasks" USING btree ("parent_task_id");

-- Update tasks_with_assignee view to expose the new fields
CREATE OR REPLACE VIEW "public"."tasks_with_assignee" WITH ("security_invoker"='true') AS
SELECT 
    t.id,
    t.tablo_id,
    t.title,
    t.description,
    t.status,
    t.assignee_id,
    t.position,
    t.created_at,
    t.updated_at,
    p.name AS assignee_name,
    p.avatar_url AS assignee_avatar,
    t.is_parent,
    t.parent_task_id
FROM "public"."tasks" t
LEFT JOIN "public"."profiles" p ON t.assignee_id = p.id;

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

COMMENT ON VIEW "public"."tasks_with_assignee" IS 'View that returns tasks with assignee information from profiles';

-- Update RLS policies so only tablo owners can manipulate Etapes
DROP POLICY IF EXISTS "Users can view tasks from their tablos" ON "public"."tasks";
DROP POLICY IF EXISTS "Users can create tasks in their tablos" ON "public"."tasks";
DROP POLICY IF EXISTS "Users can update tasks in their tablos" ON "public"."tasks";
DROP POLICY IF EXISTS "Users can delete tasks in their tablos" ON "public"."tasks";

CREATE POLICY "Users can view tasks from their tablos"
    ON "public"."tasks"
    FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
        )
    );

CREATE POLICY "Users can create tasks in their tablos"
    ON "public"."tasks"
    FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
        )
        AND (
            NOT "tasks"."is_parent"
            OR EXISTS (
                SELECT 1 FROM "public"."tablos" "t"
                WHERE "t"."id" = "tasks"."tablo_id"
                  AND "t"."owner_id" = auth.uid()
            )
        )
    );

CREATE POLICY "Users can update tasks in their tablos"
    ON "public"."tasks"
    FOR UPDATE
    USING (
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
        )
        AND (
            NOT "tasks"."is_parent"
            OR EXISTS (
                SELECT 1 FROM "public"."tablos" "t"
                WHERE "t"."id" = "tasks"."tablo_id"
                  AND "t"."owner_id" = auth.uid()
            )
        )
    )
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
        )
        AND (
            NOT "tasks"."is_parent"
            OR EXISTS (
                SELECT 1 FROM "public"."tablos" "t"
                WHERE "t"."id" = "tasks"."tablo_id"
                  AND "t"."owner_id" = auth.uid()
            )
        )
    );

CREATE POLICY "Users can delete tasks in their tablos"
    ON "public"."tasks"
    FOR DELETE
    USING (
        EXISTS (
            SELECT 1 FROM "public"."tablo_access" "ta"
            WHERE "ta"."tablo_id" = "tasks"."tablo_id"
              AND "ta"."user_id" = auth.uid()
              AND "ta"."is_active" = true
        )
        AND (
            NOT "tasks"."is_parent"
            OR EXISTS (
                SELECT 1 FROM "public"."tablos" "t"
                WHERE "t"."id" = "tasks"."tablo_id"
                  AND "t"."owner_id" = auth.uid()
            )
        )
    );

