schema.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS btree_gin;


CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

-- 1) Utenti applicativi collegati a Keycloak
CREATE TABLE IF NOT EXISTS app_user (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kc_user_id       uuid NOT NULL,
  realm            text NOT NULL,
  display_name     text,
  locale           text,
  marketing_opt_in boolean DEFAULT false,
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (realm, kc_user_id)
);

CREATE TRIGGER app_user_updated_at
BEFORE UPDATE ON app_user
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 2) Liste
CREATE TABLE IF NOT EXISTS todo_list (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id uuid NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  title         text NOT NULL,
  is_archived   boolean NOT NULL DEFAULT false,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER todo_list_updated_at
BEFORE UPDATE ON todo_list
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE INDEX IF NOT EXISTS idx_todo_list_owner ON todo_list(owner_user_id) WHERE is_archived = false;

-- 3) Task
CREATE TABLE IF NOT EXISTS todo_item (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  list_id       uuid NOT NULL REFERENCES todo_list(id) ON DELETE CASCADE,
  title         text NOT NULL,
  description   text,
  status        text NOT NULL DEFAULT 'open' CHECK (status IN ('open','in_progress','done','cancelled')),
  priority      smallint NOT NULL DEFAULT 0,
  order_index   numeric(12,6) NOT NULL DEFAULT 1000,
  due_date      date,
  completed_at  timestamptz,
  created_by    uuid NOT NULL REFERENCES app_user(id) ON DELETE SET NULL,
  assigned_to   uuid REFERENCES app_user(id) ON DELETE SET NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  CHECK ((status <> 'done' AND completed_at IS NULL) OR (status = 'done'))
);
CREATE TRIGGER todo_item_updated_at
BEFORE UPDATE ON todo_item
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX IF NOT EXISTS idx_todo_item_list_order  ON todo_item(list_id, order_index);
CREATE INDEX IF NOT EXISTS idx_todo_item_status      ON todo_item(status);
CREATE INDEX IF NOT EXISTS idx_todo_item_due_date    ON todo_item(due_date);
CREATE INDEX IF NOT EXISTS idx_todo_item_assigned_to ON todo_item(assigned_to);
CREATE INDEX IF NOT EXISTS idx_todo_item_list_status ON todo_item(list_id, status);

CREATE TABLE IF NOT EXISTS label (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  list_id   uuid NOT NULL REFERENCES todo_list(id) ON DELETE CASCADE,
  name      text NOT NULL,
  color_hex text,
  UNIQUE (list_id, name)
);

CREATE TABLE IF NOT EXISTS todo_item_label (
  item_id  uuid NOT NULL REFERENCES todo_item(id) ON DELETE CASCADE,
  label_id uuid NOT NULL REFERENCES label(id) ON DELETE CASCADE,
  PRIMARY KEY (item_id, label_id)
);

CREATE TABLE IF NOT EXISTS list_share (
  list_id  uuid NOT NULL REFERENCES todo_list(id) ON DELETE CASCADE,
  user_id  uuid NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  role     text NOT NULL CHECK (role IN ('viewer','editor')),
  added_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (list_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_list_share_user ON list_share(user_id);

CREATE TABLE IF NOT EXISTS activity_log (
  id        bigserial PRIMARY KEY,
  at        timestamptz NOT NULL DEFAULT now(),
  actor_id  uuid REFERENCES app_user(id) ON DELETE SET NULL,
  entity    text NOT NULL CHECK (entity IN ('list','item','label','share')),
  entity_id uuid NOT NULL,
  action    text NOT NULL,
  details   jsonb
);
CREATE INDEX IF NOT EXISTS idx_activity_log_entity ON activity_log(entity, entity_id, at DESC);

CREATE OR REPLACE VIEW my_open_tasks AS
SELECT
  i.id, i.title, i.status, i.priority, i.due_date,
  i.list_id, l.title AS list_title, i.assigned_to,
  i.created_at, i.updated_at
FROM todo_item i
JOIN todo_list l ON l.id = i.list_id
WHERE i.status IN ('open','in_progress');