-- Task templates
CREATE TABLE app.task_template (
  template_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT,
  category app.task_category NOT NULL DEFAULT 'operations',
  is_claimable BOOLEAN NOT NULL DEFAULT false,
  default_location TEXT,
  estimated_minutes INT,
  recurrence_cron TEXT,  -- cron expression, e.g. '0 8 * * 1,3,5'
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Task instances
CREATE TABLE app.task_instance (
  task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID REFERENCES app.task_template(template_id),
  title TEXT NOT NULL,
  description TEXT,
  category app.task_category NOT NULL DEFAULT 'operations',
  location TEXT,
  linked_retreat_id UUID,
  linked_stay_id UUID REFERENCES app.stay(stay_id),
  scheduled_start_at TIMESTAMPTZ,
  scheduled_end_at TIMESTAMPTZ,
  status app.task_status NOT NULL DEFAULT 'planned',
  created_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_task_instance_status ON app.task_instance(status);
CREATE INDEX idx_task_instance_category ON app.task_instance(category);
CREATE INDEX idx_task_instance_template ON app.task_instance(template_id);

-- Task assignments
CREATE TABLE app.task_assignment (
  assignment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_instance_id UUID NOT NULL REFERENCES app.task_instance(task_id),
  assigned_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  assigned_by_user_id UUID NOT NULL REFERENCES app."user"(user_id),
  status app.task_assignment_status NOT NULL DEFAULT 'proposed',
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_task_assignment_task ON app.task_assignment(task_instance_id);
CREATE INDEX idx_task_assignment_user ON app.task_assignment(assigned_user_id);

SELECT app.enable_audit('task_template', 'template_id');
SELECT app.enable_audit('task_instance', 'task_id');
SELECT app.enable_audit('task_assignment', 'assignment_id');