-- Shared Todo — initial schema.
-- pgcrypto provides gen_random_uuid() which is used for PKs.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS sharedtodo;

CREATE TABLE IF NOT EXISTS sharedtodo.lists (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title       TEXT NOT NULL CHECK (char_length(title) BETWEEN 1 AND 200),
  icon        TEXT,
  color       TEXT,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by  TEXT NOT NULL CHECK (created_by IN ('adam', 'jenna'))
);

CREATE TABLE IF NOT EXISTS sharedtodo.todos (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  list_id       UUID NOT NULL REFERENCES sharedtodo.lists(id) ON DELETE CASCADE,
  text          TEXT NOT NULL CHECK (char_length(text) BETWEEN 1 AND 500),
  done          BOOLEAN NOT NULL DEFAULT false,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at  TIMESTAMPTZ,
  created_by    TEXT NOT NULL CHECK (created_by IN ('adam', 'jenna')),
  updated_by    TEXT NOT NULL CHECK (updated_by IN ('adam', 'jenna'))
);

CREATE INDEX IF NOT EXISTS todos_list_done_updated_idx
  ON sharedtodo.todos (list_id, done, updated_at DESC);

CREATE INDEX IF NOT EXISTS todos_all_pending_idx
  ON sharedtodo.todos (done, updated_at DESC)
  WHERE done = false;

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

DROP TRIGGER IF EXISTS lists_touch ON sharedtodo.lists;
CREATE TRIGGER lists_touch
  BEFORE UPDATE ON sharedtodo.lists
  FOR EACH ROW EXECUTE FUNCTION sharedtodo.touch_updated_at();

DROP TRIGGER IF EXISTS todos_touch ON sharedtodo.todos;
CREATE TRIGGER todos_touch
  BEFORE UPDATE ON sharedtodo.todos
  FOR EACH ROW EXECUTE FUNCTION sharedtodo.touch_updated_at();
