-- 009-files

CREATE TABLE files (
  id              VARCHAR2(255 CHAR) PRIMARY KEY,
  type            VARCHAR2(255 CHAR)       NOT NULL,
  owner           VARCHAR2(255 CHAR)       NOT NULL,
  owner_client_id NUMBER(19) REFERENCES clients (id),
  actor           VARCHAR2(255 CHAR)       NOT NULL,
  actor_client_id NUMBER(19) REFERENCES clients (id),
  actor_user_id   NUMBER(19) REFERENCES users (id),

  name            VARCHAR2(255 CHAR)       NOT NULL,
  path            VARCHAR2(255 CHAR)       NOT NULL,
  "size"          NUMBER(19)               NOT NULL,

  image_width     NUMBER(19),
  image_height    NUMBER(19),

  content_type    VARCHAR2(128 CHAR)       NOT NULL,
  created_at      TIMESTAMP WITH TIME ZONE NOT NULL
);


CREATE INDEX files_actor_client_ix
  ON files (
    CASE WHEN actor_client_id IS NOT NULL
      THEN actor_client_id END
  );

CREATE INDEX files_actor_user_ix
  ON files (
    CASE WHEN actor_user_id IS NOT NULL
      THEN actor_user_id END
  );
