-- Add soft-delete support for tasks
ALTER TABLE "public"."tasks"
  ADD COLUMN IF NOT EXISTS "deleted_at" timestamp with time zone;

COMMENT ON COLUMN "public"."tasks"."deleted_at" IS 'Timestamp when the task was soft-deleted';

-- Index used by tablo-scoped cleanup and active-task reads
CREATE INDEX IF NOT EXISTS "tasks_tablo_deleted_at_idx"
  ON "public"."tasks" USING btree ("tablo_id", "deleted_at");

-- Keep tasks_with_assignee limited to active (not soft-deleted) tasks
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
WHERE t.deleted_at IS NULL;

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

COMMENT ON VIEW "public"."tasks_with_assignee" IS 'View that returns active (non-deleted) tasks with assignee information from profiles';
