-- Add due_date column to tasks table for roadmap feature
ALTER TABLE "public"."tasks"
  ADD COLUMN "due_date" date;

COMMENT ON COLUMN "public"."tasks"."due_date" IS 'Optional due date for tasks and Etapes, used in roadmap views';

-- Index for roadmap queries (filtering/sorting by due_date within a tablo)
CREATE INDEX "tasks_tablo_due_date_idx" ON "public"."tasks" USING btree ("tablo_id", "due_date");

-- Update tasks_with_assignee view to include due_date
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,
    t.due_date
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';
