BEGIN;

INSERT INTO version_history (component, from_version, to_version, author)
VALUES ('KOANS', 0, 2, 'Initial Creation');

CREATE TABLE koans(
  koanid INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  communityid INTEGER NOT NULL REFERENCES communities(communityid) ON DELETE CASCADE ON UPDATE CASCADE,
  content TEXT NOT NULL,
  created_by INTEGER REFERENCES user_profiles(profileid) ON UPDATE CASCADE ON DELETE NO ACTION,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  _timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TRIGGER koans_timestamp BEFORE UPDATE ON koans
  FOR EACH ROW EXECUTE FUNCTION update_timestamp_column();

CREATE VIEW
  koans_info
AS
  SELECT
    *,
    (deleted_at is not NULL) AS is_deleted,
    (row_number() OVER (PARTITION BY communityid ORDER BY created_at ASC)) as koanlabel
  FROM
    koans
  ORDER BY (communityid, created_at);


COMMIT;