OwlCyberSecurity - MANAGER

Edit File: v44.sql

-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-

----
-- Copyright (c) 2010-2017 Apple Inc. All rights reserved.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
----

-----------------
-- Resource ID --
-----------------

create sequence RESOURCE_ID_SEQ;

-------------------------
-- Cluster Bookkeeping --
-------------------------

-- Information about a process connected to this database.

-- Note that this must match the node info schema in twext.enterprise.queue.
create table NODE_INFO (
  HOSTNAME  varchar(255) not null,
  PID       integer      not null,
  PORT      integer      not null,
  TIME      timestamp    not null default timezone('UTC', CURRENT_TIMESTAMP),

primary key (HOSTNAME, PORT)
);

-- Unique named locks.  This table should always be empty, but rows are
-- temporarily created in order to prevent undesirable concurrency.
create table NAMED_LOCK (
    LOCK_NAME varchar(255) primary key
);

--------------------
-- Jobs           --
--------------------

create sequence JOB_SEQ;

create table JOB (
  JOB_ID      integer primary key default nextval('JOB_SEQ') not null, --implicit index
  WORK_TYPE   varchar(255) not null,
  PRIORITY    integer default 0,
  WEIGHT      integer default 0,
  NOT_BEFORE  timestamp not null,
  ASSIGNED    timestamp default null,
  OVERDUE     timestamp default null,
  FAILED      integer default 0
);

-------------------
-- Calendar Home --
-------------------

create table CALENDAR_HOME (
  RESOURCE_ID      integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID        varchar(255) not null unique,                                -- implicit index
  STATUS           integer      default 0 not null,                             -- enum HOME_STATUS
  DATAVERSION      integer      default 0 not null
);

-- Enumeration of statuses

create table HOME_STATUS (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into HOME_STATUS values (0, 'normal' );
insert into HOME_STATUS values (1, 'external');
insert into HOME_STATUS values (2, 'purging');

--------------
-- Calendar --
--------------

create table CALENDAR (
  RESOURCE_ID integer   primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
);

----------------------------
-- Calendar Home Metadata --
----------------------------

create table CALENDAR_HOME_METADATA (
  RESOURCE_ID              integer     primary key references CALENDAR_HOME on delete cascade, -- implicit index
  QUOTA_USED_BYTES         integer     default 0 not null,
  DEFAULT_EVENTS           integer     default null references CALENDAR on delete set null,
  DEFAULT_TASKS            integer     default null references CALENDAR on delete set null,
  DEFAULT_POLLS            integer     default null references CALENDAR on delete set null,
  ALARM_VEVENT_TIMED       text        default null,
  ALARM_VEVENT_ALLDAY      text        default null,
  ALARM_VTODO_TIMED        text        default null,
  ALARM_VTODO_ALLDAY       text        default null,
  AVAILABILITY             text        default null,
  CREATED                  timestamp   default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                 timestamp   default timezone('UTC', CURRENT_TIMESTAMP)
);

create index CALENDAR_HOME_METADATA_DEFAULT_EVENTS on
  CALENDAR_HOME_METADATA(DEFAULT_EVENTS);
create index CALENDAR_HOME_METADATA_DEFAULT_TASKS on
  CALENDAR_HOME_METADATA(DEFAULT_TASKS);
create index CALENDAR_HOME_METADATA_DEFAULT_POLLS on
  CALENDAR_HOME_METADATA(DEFAULT_POLLS);

-----------------------
-- Calendar Metadata --
-----------------------

create table CALENDAR_METADATA (
  RESOURCE_ID           integer      primary key references CALENDAR on delete cascade, -- implicit index
  SUPPORTED_COMPONENTS  varchar(255) default null,
  CREATED               timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED              timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

---------------------------
-- Sharing Notifications --
---------------------------

create table NOTIFICATION_HOME (
  RESOURCE_ID integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID   varchar(255) not null unique,                                -- implicit index
  STATUS      integer      default 0 not null,                             -- enum HOME_STATUS
  DATAVERSION integer      default 0 not null
);

create table NOTIFICATION (
  RESOURCE_ID                   integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  NOTIFICATION_HOME_RESOURCE_ID integer      not null references NOTIFICATION_HOME,
  NOTIFICATION_UID              varchar(255) not null,
  NOTIFICATION_TYPE             varchar(255) not null,
  NOTIFICATION_DATA             text         not null,
  MD5                           char(32)     not null,
  CREATED                       timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

unique (NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
);

create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
  NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);

-------------------
-- Calendar Bind --
-------------------

-- Joins CALENDAR_HOME and CALENDAR

create table CALENDAR_BIND (
  CALENDAR_HOME_RESOURCE_ID integer      not null references CALENDAR_HOME,
  CALENDAR_RESOURCE_ID      integer      not null references CALENDAR on delete cascade,
  EXTERNAL_ID               integer      default null,
  CALENDAR_RESOURCE_NAME    varchar(255) not null,
  BIND_MODE                 integer      not null, -- enum CALENDAR_BIND_MODE
  BIND_STATUS               integer      not null, -- enum CALENDAR_BIND_STATUS
  BIND_REVISION             integer      default 0 not null,
  MESSAGE                   text,
  TRANSP                    integer      default 0 not null, -- enum CALENDAR_TRANSP
  ALARM_VEVENT_TIMED        text         default null,
  ALARM_VEVENT_ALLDAY       text         default null,
  ALARM_VTODO_TIMED         text         default null,
  ALARM_VTODO_ALLDAY        text         default null,
  TIMEZONE                  text         default null,

primary key (CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
  unique (CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME)     -- implicit index
);

create index CALENDAR_BIND_RESOURCE_ID on
  CALENDAR_BIND(CALENDAR_RESOURCE_ID);

-- Enumeration of calendar bind modes

create table CALENDAR_BIND_MODE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_BIND_MODE values (0, 'own'  );
insert into CALENDAR_BIND_MODE values (1, 'read' );
insert into CALENDAR_BIND_MODE values (2, 'write');
insert into CALENDAR_BIND_MODE values (3, 'direct');
insert into CALENDAR_BIND_MODE values (4, 'indirect');

-- Enumeration of statuses

create table CALENDAR_BIND_STATUS (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_BIND_STATUS values (0, 'invited' );
insert into CALENDAR_BIND_STATUS values (1, 'accepted');
insert into CALENDAR_BIND_STATUS values (2, 'declined');
insert into CALENDAR_BIND_STATUS values (3, 'invalid');
insert into CALENDAR_BIND_STATUS values (4, 'deleted');

-- Enumeration of transparency

create table CALENDAR_TRANSP (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_TRANSP values (0, 'opaque' );
insert into CALENDAR_TRANSP values (1, 'transparent');

---------------------
-- Calendar Object --
---------------------

create table CALENDAR_OBJECT (
  RESOURCE_ID          integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  CALENDAR_RESOURCE_ID integer      not null references CALENDAR on delete cascade,
  RESOURCE_NAME        varchar(255) not null,
  ICALENDAR_TEXT       text         not null,
  ICALENDAR_UID        varchar(255) not null,
  ICALENDAR_TYPE       varchar(255) not null,
  ATTACHMENTS_MODE     integer      default 0 not null, -- enum CALENDAR_OBJ_ATTACHMENTS_MODE
  DROPBOX_ID           varchar(255),
  ORGANIZER            varchar(255),
  RECURRANCE_MIN       date,        -- minimum date that recurrences have been expanded to.
  RECURRANCE_MAX       date,        -- maximum date that recurrences have been expanded to.
  ACCESS               integer      default 0 not null,
  SCHEDULE_OBJECT      boolean      default false,
  SCHEDULE_TAG         varchar(36)  default null,
  SCHEDULE_ETAGS       text         default null,
  PRIVATE_COMMENTS     boolean      default false not null,
  MD5                  char(32)     not null,
  CREATED              timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED             timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

unique (CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index

-- since the 'inbox' is a 'calendar resource' for the purpose of storing
  -- calendar objects, this constraint has to be selectively enforced by the
  -- application layer.

-- unique (CALENDAR_RESOURCE_ID, ICALENDAR_UID)
);

create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
  CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);

create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
  CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);

create index CALENDAR_OBJECT_ICALENDAR_UID on
  CALENDAR_OBJECT(ICALENDAR_UID);

create index CALENDAR_OBJECT_DROPBOX_ID on
  CALENDAR_OBJECT(DROPBOX_ID);

-- Enumeration of attachment modes

create table CALENDAR_OBJ_ATTACHMENTS_MODE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_OBJ_ATTACHMENTS_MODE values (0, 'none' );
insert into CALENDAR_OBJ_ATTACHMENTS_MODE values (1, 'read' );
insert into CALENDAR_OBJ_ATTACHMENTS_MODE values (2, 'write');

-- Enumeration of calendar access types

create table CALENDAR_ACCESS_TYPE (
  ID          integer     primary key,
  DESCRIPTION varchar(32) not null unique
);

insert into CALENDAR_ACCESS_TYPE values (0, ''             );
insert into CALENDAR_ACCESS_TYPE values (1, 'public'       );
insert into CALENDAR_ACCESS_TYPE values (2, 'private'      );
insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
insert into CALENDAR_ACCESS_TYPE values (4, 'restricted'   );

-----------------
-- Instance ID --
-----------------

create sequence INSTANCE_ID_SEQ;

----------------
-- Time Range --
----------------

create table TIME_RANGE (
  INSTANCE_ID                 integer        primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
  CALENDAR_RESOURCE_ID        integer        not null references CALENDAR on delete cascade,
  CALENDAR_OBJECT_RESOURCE_ID integer        not null references CALENDAR_OBJECT on delete cascade,
  FLOATING                    boolean        not null,
  START_DATE                  timestamp      not null,
  END_DATE                    timestamp      not null,
  FBTYPE                      integer        not null,
  TRANSPARENT                 boolean        not null
);

create index TIME_RANGE_CALENDAR_RESOURCE_ID on
  TIME_RANGE(CALENDAR_RESOURCE_ID);
create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
  TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);

-- Enumeration of free/busy types

create table FREE_BUSY_TYPE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into FREE_BUSY_TYPE values (0, 'unknown'         );
insert into FREE_BUSY_TYPE values (1, 'free'            );
insert into FREE_BUSY_TYPE values (2, 'busy'            );
insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
insert into FREE_BUSY_TYPE values (4, 'busy-tentative'  );

-------------------
-- Per-user data --
-------------------

create table PERUSER (
  TIME_RANGE_INSTANCE_ID      integer      not null references TIME_RANGE on delete cascade,
  USER_ID                     varchar(255) not null,
  TRANSPARENT                 boolean      not null,
  ADJUSTED_START_DATE         timestamp    default null,
  ADJUSTED_END_DATE           timestamp    default null
);

create index PERUSER_TIME_RANGE_INSTANCE_ID on
  PERUSER(TIME_RANGE_INSTANCE_ID);

----------------
-- Attachment --
----------------

create sequence ATTACHMENT_ID_SEQ;

create table ATTACHMENT (
  ATTACHMENT_ID               integer           primary key default nextval('ATTACHMENT_ID_SEQ'), -- implicit index
  CALENDAR_HOME_RESOURCE_ID   integer           not null references CALENDAR_HOME,
  DROPBOX_ID                  varchar(255),
  CONTENT_TYPE                varchar(255)      not null,
  SIZE                        integer           not null,
  MD5                         char(32)          not null,
  CREATED                     timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                    timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  PATH                        varchar(1024)     not null
);

create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
  ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);

create index ATTACHMENT_DROPBOX_ID on
  ATTACHMENT(DROPBOX_ID);

-- Many-to-many relationship between attachments and calendar objects
create table ATTACHMENT_CALENDAR_OBJECT (
  ATTACHMENT_ID                  integer      not null references ATTACHMENT on delete cascade,
  MANAGED_ID                     varchar(255) not null,
  CALENDAR_OBJECT_RESOURCE_ID    integer      not null references CALENDAR_OBJECT on delete cascade,

primary key (ATTACHMENT_ID, CALENDAR_OBJECT_RESOURCE_ID), -- implicit index
  unique (MANAGED_ID, CALENDAR_OBJECT_RESOURCE_ID) --implicit index
);

create index ATTACHMENT_CALENDAR_OBJECT_CALENDAR_OBJECT_RESOURCE_ID on
  ATTACHMENT_CALENDAR_OBJECT(CALENDAR_OBJECT_RESOURCE_ID);

-----------------------
-- Resource Property --
-----------------------

create table RESOURCE_PROPERTY (
  RESOURCE_ID integer      not null, -- foreign key: *.RESOURCE_ID
  NAME        varchar(255) not null,
  VALUE       text         not null, -- FIXME: xml?
  VIEWER_UID  varchar(255),

primary key (RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
);

----------------------
-- AddressBook Home --
----------------------

create table ADDRESSBOOK_HOME (
  RESOURCE_ID                   integer         primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  ADDRESSBOOK_PROPERTY_STORE_ID integer         default nextval('RESOURCE_ID_SEQ') not null,    -- implicit index
  OWNER_UID                     varchar(255)    not null unique,                                -- implicit index
  STATUS                        integer         default 0 not null,                             -- enum HOME_STATUS
  DATAVERSION                   integer         default 0 not null
);

-------------------------------
-- AddressBook Home Metadata --
-------------------------------

create table ADDRESSBOOK_HOME_METADATA (
  RESOURCE_ID      integer      primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
  QUOTA_USED_BYTES integer      default 0 not null,
  CREATED          timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED         timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

-----------------------------
-- Shared AddressBook Bind --
-----------------------------

-- Joins sharee ADDRESSBOOK_HOME and owner ADDRESSBOOK_HOME

create table SHARED_ADDRESSBOOK_BIND (
  ADDRESSBOOK_HOME_RESOURCE_ID          integer         not null references ADDRESSBOOK_HOME,
  OWNER_HOME_RESOURCE_ID                integer         not null references ADDRESSBOOK_HOME on delete cascade,
  EXTERNAL_ID                           integer         default null,
  ADDRESSBOOK_RESOURCE_NAME             varchar(255)    not null,
  BIND_MODE                             integer         not null, -- enum CALENDAR_BIND_MODE
  BIND_STATUS                           integer         not null, -- enum CALENDAR_BIND_STATUS
  BIND_REVISION                         integer         default 0 not null,
  MESSAGE                               text,                     -- FIXME: xml?

primary key (ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME)     -- implicit index
);

create index SHARED_ADDRESSBOOK_BIND_RESOURCE_ID on
  SHARED_ADDRESSBOOK_BIND(OWNER_HOME_RESOURCE_ID);

------------------------
-- AddressBook Object --
------------------------

create table ADDRESSBOOK_OBJECT (
  RESOURCE_ID                   integer         primary key default nextval('RESOURCE_ID_SEQ'),    -- implicit index
  ADDRESSBOOK_HOME_RESOURCE_ID  integer         not null references ADDRESSBOOK_HOME on delete cascade,
  RESOURCE_NAME                 varchar(255)    not null,
  VCARD_TEXT                    text            not null,
  VCARD_UID                     varchar(255)    not null,
  KIND                          integer         not null,  -- enum ADDRESSBOOK_OBJECT_KIND
  MD5                           char(32)        not null,
  CREATED                       timestamp       default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                      timestamp       default timezone('UTC', CURRENT_TIMESTAMP),

unique (ADDRESSBOOK_HOME_RESOURCE_ID, RESOURCE_NAME), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, VCARD_UID)      -- implicit index
);

-----------------------------
-- AddressBook Object kind --
-----------------------------

create table ADDRESSBOOK_OBJECT_KIND (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into ADDRESSBOOK_OBJECT_KIND values (0, 'person');
insert into ADDRESSBOOK_OBJECT_KIND values (1, 'group' );
insert into ADDRESSBOOK_OBJECT_KIND values (2, 'resource');
insert into ADDRESSBOOK_OBJECT_KIND values (3, 'location');

----------------------------------
-- Revisions, forward reference --
----------------------------------

create sequence REVISION_SEQ;

---------------------------------
-- Address Book Object Members --
---------------------------------

create table ABO_MEMBERS (
  GROUP_ID        integer     not null, -- references ADDRESSBOOK_OBJECT on delete cascade,   -- AddressBook Object's (kind=='group') RESOURCE_ID
  ADDRESSBOOK_ID  integer     not null references ADDRESSBOOK_HOME on delete cascade,
  MEMBER_ID       integer     not null, -- references ADDRESSBOOK_OBJECT,                     -- member AddressBook Object's RESOURCE_ID
  REVISION        integer     default nextval('REVISION_SEQ') not null,
  REMOVED         boolean     default false not null,
  MODIFIED        timestamp   default timezone('UTC', CURRENT_TIMESTAMP),

primary key (GROUP_ID, MEMBER_ID, REVISION) -- implicit index
);

create index ABO_MEMBERS_ADDRESSBOOK_ID on
  ABO_MEMBERS(ADDRESSBOOK_ID);
create index ABO_MEMBERS_MEMBER_ID on
  ABO_MEMBERS(MEMBER_ID);

------------------------------------------
-- Address Book Object Foreign Members  --
------------------------------------------

create table ABO_FOREIGN_MEMBERS (
  GROUP_ID           integer      not null references ADDRESSBOOK_OBJECT on delete cascade,  -- AddressBook Object's (kind=='group') RESOURCE_ID
  ADDRESSBOOK_ID     integer      not null references ADDRESSBOOK_HOME on delete cascade,
  MEMBER_ADDRESS     varchar(255) not null,                                                  -- member AddressBook Object's 'calendar' address

primary key (GROUP_ID, MEMBER_ADDRESS) -- implicit index
);

create index ABO_FOREIGN_MEMBERS_ADDRESSBOOK_ID on
  ABO_FOREIGN_MEMBERS(ADDRESSBOOK_ID);

-----------------------
-- Shared Group Bind --
-----------------------

-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK_OBJECT (kind == group)

create table SHARED_GROUP_BIND (
  ADDRESSBOOK_HOME_RESOURCE_ID      integer      not null references ADDRESSBOOK_HOME,
  GROUP_RESOURCE_ID                 integer      not null references ADDRESSBOOK_OBJECT on delete cascade,
  EXTERNAL_ID                       integer      default null,
  GROUP_ADDRESSBOOK_NAME            varchar(255) not null,
  BIND_MODE                         integer      not null, -- enum CALENDAR_BIND_MODE
  BIND_STATUS                       integer      not null, -- enum CALENDAR_BIND_STATUS
  BIND_REVISION                     integer      default 0 not null,
  MESSAGE                           text,                  -- FIXME: xml?

primary key (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_RESOURCE_ID), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_ADDRESSBOOK_NAME)  -- implicit index
);

create index SHARED_GROUP_BIND_RESOURCE_ID on
  SHARED_GROUP_BIND(GROUP_RESOURCE_ID);

---------------
-- Revisions --
---------------

-- create sequence REVISION_SEQ;

-------------------------------
-- Calendar Object Revisions --
-------------------------------

create table CALENDAR_OBJECT_REVISIONS (
  CALENDAR_HOME_RESOURCE_ID integer      not null references CALENDAR_HOME,
  CALENDAR_RESOURCE_ID      integer      references CALENDAR,
  CALENDAR_NAME             varchar(255) default null,
  RESOURCE_NAME             varchar(255),
  REVISION                  integer      default nextval('REVISION_SEQ') not null,
  DELETED                   boolean      not null,
  MODIFIED                  timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

create index CALENDAR_OBJECT_REVISIONS_HOME_RESOURCE_ID_CALENDAR_RESOURCE_ID
  on CALENDAR_OBJECT_REVISIONS(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID);

create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_RESOURCE_NAME_DELETED_REVISION
  on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, RESOURCE_NAME, DELETED, REVISION);

create index CALENDAR_OBJECT_REVISIONS_RESOURCE_ID_REVISION
  on CALENDAR_OBJECT_REVISIONS(CALENDAR_RESOURCE_ID, REVISION);

----------------------------------
-- AddressBook Object Revisions --
----------------------------------

create table ADDRESSBOOK_OBJECT_REVISIONS (
  ADDRESSBOOK_HOME_RESOURCE_ID  integer      not null references ADDRESSBOOK_HOME,
  OWNER_HOME_RESOURCE_ID        integer      references ADDRESSBOOK_HOME,
  ADDRESSBOOK_NAME              varchar(255) default null,
  OBJECT_RESOURCE_ID            integer      default 0,
  RESOURCE_NAME                 varchar(255),
  REVISION                      integer      default nextval('REVISION_SEQ') not null,
  DELETED                       boolean      not null,
  MODIFIED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

create index ADDRESSBOOK_OBJECT_REVISIONS_HOME_RESOURCE_ID_OWNER_HOME_RESOURCE_ID
  on ADDRESSBOOK_OBJECT_REVISIONS(ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID);

create index ADDRESSBOOK_OBJECT_REVISIONS_OWNER_HOME_RESOURCE_ID_RESOURCE_NAME_DELETED_REVISION
  on ADDRESSBOOK_OBJECT_REVISIONS(OWNER_HOME_RESOURCE_ID, RESOURCE_NAME, DELETED, REVISION);

create index ADDRESSBOOK_OBJECT_REVISIONS_OWNER_HOME_RESOURCE_ID_REVISION
  on ADDRESSBOOK_OBJECT_REVISIONS(OWNER_HOME_RESOURCE_ID, REVISION);

-----------------------------------
-- Notification Object Revisions --
-----------------------------------

create table NOTIFICATION_OBJECT_REVISIONS (
  NOTIFICATION_HOME_RESOURCE_ID integer      not null references NOTIFICATION_HOME on delete cascade,
  RESOURCE_NAME                 varchar(255),
  REVISION                      integer      default nextval('REVISION_SEQ') not null,
  DELETED                       boolean      not null,
  MODIFIED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

unique (NOTIFICATION_HOME_RESOURCE_ID, RESOURCE_NAME) -- implicit index
);

create index NOTIFICATION_OBJECT_REVISIONS_RESOURCE_ID_REVISION
  on NOTIFICATION_OBJECT_REVISIONS(NOTIFICATION_HOME_RESOURCE_ID, REVISION);

-------------------------------------------
-- Apple Push Notification Subscriptions --
-------------------------------------------

create table APN_SUBSCRIPTIONS (
  TOKEN                         varchar(255) not null,
  RESOURCE_KEY                  varchar(255) not null,
  MODIFIED                      integer      not null,
  SUBSCRIBER_GUID               varchar(255) not null,
  USER_AGENT                    varchar(255) default null,
  IP_ADDR                       varchar(255) default null,

primary key (TOKEN, RESOURCE_KEY) -- implicit index
);

create index APN_SUBSCRIPTIONS_RESOURCE_KEY
  on APN_SUBSCRIPTIONS(RESOURCE_KEY);

-----------------
-- IMIP Tokens --
-----------------

create table IMIP_TOKENS (
  TOKEN                         varchar(255) not null,
  ORGANIZER                     varchar(255) not null,
  ATTENDEE                      varchar(255) not null,
  ICALUID                       varchar(255) not null,
  ACCESSED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

primary key (ORGANIZER, ATTENDEE, ICALUID) -- implicit index
);

create index IMIP_TOKENS_TOKEN
  on IMIP_TOKENS(TOKEN);

----------------
-- Work Items --
----------------

create sequence WORKITEM_SEQ;

---------------------------
-- IMIP Inivitation Work --
---------------------------

create table IMIP_INVITATION_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  FROM_ADDR                     varchar(255) not null,
  TO_ADDR                       varchar(255) not null,
  ICALENDAR_TEXT                text         not null
);

create index IMIP_INVITATION_WORK_JOB_ID on
  IMIP_INVITATION_WORK(JOB_ID);

-----------------------
-- IMIP Polling Work --
-----------------------

create table IMIP_POLLING_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null
);

create index IMIP_POLLING_WORK_JOB_ID on
  IMIP_POLLING_WORK(JOB_ID);

---------------------
-- IMIP Reply Work --
---------------------

create table IMIP_REPLY_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  ORGANIZER                     varchar(255) not null,
  ATTENDEE                      varchar(255) not null,
  ICALENDAR_TEXT                text         not null
);

create index IMIP_REPLY_WORK_JOB_ID on
  IMIP_REPLY_WORK(JOB_ID);

------------------------
-- Push Notifications --
------------------------

create table PUSH_NOTIFICATION_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  PUSH_ID                       varchar(255) not null,
  PUSH_PRIORITY                 integer      not null -- 1:low 5:medium 10:high
);

create index PUSH_NOTIFICATION_WORK_JOB_ID on
  PUSH_NOTIFICATION_WORK(JOB_ID);

-----------------
-- GroupCacher --
-----------------

create table GROUP_CACHER_POLLING_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null
);

create index GROUP_CACHER_POLLING_WORK_JOB_ID on
  GROUP_CACHER_POLLING_WORK(JOB_ID);

create table GROUP_REFRESH_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  GROUP_UID                     varchar(255) not null
);

create index GROUP_REFRESH_WORK_JOB_ID on
  GROUP_REFRESH_WORK(JOB_ID);

create table GROUP_ATTENDEE_RECONCILE_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  RESOURCE_ID                   integer,
  GROUP_ID                      integer
);

create index GROUP_ATTENDEE_RECONCILE_WORK_JOB_ID on
  GROUP_ATTENDEE_RECONCILE_WORK(JOB_ID);

create table GROUPS (
  GROUP_ID                      integer      primary key default nextval('RESOURCE_ID_SEQ'),    -- implicit index
  NAME                          varchar(255) not null,
  GROUP_UID                     varchar(255) not null,
  MEMBERSHIP_HASH               varchar(255) not null,
  EXTANT                        integer default 1,
  CREATED                       timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                      timestamp default timezone('UTC', CURRENT_TIMESTAMP)
);
create index GROUPS_GROUP_UID on
  GROUPS(GROUP_UID);

create table GROUP_MEMBERSHIP (
  GROUP_ID                     integer not null references GROUPS on delete cascade,
  MEMBER_UID                   varchar(255) not null,
  
  primary key (GROUP_ID, MEMBER_UID)
);

create index GROUP_MEMBERSHIP_MEMBER on
  GROUP_MEMBERSHIP(MEMBER_UID);

create table GROUP_ATTENDEE (
  GROUP_ID                      integer not null references GROUPS on delete cascade,
  RESOURCE_ID                   integer not null references CALENDAR_OBJECT on delete cascade,
  MEMBERSHIP_HASH               varchar(255) not null,
  
  primary key (GROUP_ID, RESOURCE_ID)
);
create index GROUP_ATTENDEE_RESOURCE_ID on
  GROUP_ATTENDEE(RESOURCE_ID);

---------------
-- Delegates --
---------------

create table DELEGATES (
  DELEGATOR                     varchar(255) not null,
  DELEGATE                      varchar(255) not null,
  READ_WRITE                    integer      not null, -- 1 = ReadWrite, 0 = ReadOnly

primary key (DELEGATOR, READ_WRITE, DELEGATE)
);
create index DELEGATE_TO_DELEGATOR on
  DELEGATES(DELEGATE, READ_WRITE, DELEGATOR);

create table DELEGATE_GROUPS (
  DELEGATOR                     varchar(255) not null,
  GROUP_ID                      integer      not null references GROUPS on delete cascade,
  READ_WRITE                    integer      not null, -- 1 = ReadWrite, 0 = ReadOnly
  IS_EXTERNAL                   integer      not null, -- 1 = ReadWrite, 0 = ReadOnly

primary key (DELEGATOR, READ_WRITE, GROUP_ID)
);
create index DELEGATE_GROUPS_GROUP_ID on
  DELEGATE_GROUPS(GROUP_ID);

create table EXTERNAL_DELEGATE_GROUPS (
  DELEGATOR                     varchar(255) primary key not null,
  GROUP_UID_READ                varchar(255),
  GROUP_UID_WRITE               varchar(255)
);

--------------------------
-- Object Splitter Work --
--------------------------

create table CALENDAR_OBJECT_SPLITTER_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  RESOURCE_ID                   integer      not null references CALENDAR_OBJECT on delete cascade
);

create index CALENDAR_OBJECT_SPLITTER_WORK_RESOURCE_ID on
  CALENDAR_OBJECT_SPLITTER_WORK(RESOURCE_ID);
create index CALENDAR_OBJECT_SPLITTER_WORK_JOB_ID on
  CALENDAR_OBJECT_SPLITTER_WORK(JOB_ID);

---------------------------
-- Revision Cleanup Work --
---------------------------

create table FIND_MIN_VALID_REVISION_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null
);

create index FIND_MIN_VALID_REVISION_WORK_JOB_ID on
  FIND_MIN_VALID_REVISION_WORK(JOB_ID);

create table REVISION_CLEANUP_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null
);

create index REVISION_CLEANUP_WORK_JOB_ID on
  REVISION_CLEANUP_WORK(JOB_ID);

------------------------
-- Inbox Cleanup Work --
------------------------

create table INBOX_CLEANUP_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null
);

create index INBOX_CLEANUP_WORK_JOB_ID on
   INBOX_CLEANUP_WORK(JOB_ID);

create table CLEANUP_ONE_INBOX_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  HOME_ID                       integer      not null unique references CALENDAR_HOME on delete cascade
);

create index CLEANUP_ONE_INBOX_WORK_JOB_ID on
  CLEANUP_ONE_INBOX_WORK(JOB_ID);

-------------------
-- Schedule Work --
-------------------

create table SCHEDULE_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  ICALENDAR_UID                 varchar(255) not null,
  WORK_TYPE                     varchar(255) not null
);

create index SCHEDULE_WORK_JOB_ID on
  SCHEDULE_WORK(JOB_ID);
create index SCHEDULE_WORK_ICALENDAR_UID on
  SCHEDULE_WORK(ICALENDAR_UID);

---------------------------
-- Schedule Refresh Work --
---------------------------

create table SCHEDULE_REFRESH_WORK (
  WORK_ID                       integer      primary key references SCHEDULE_WORK on delete cascade, -- implicit index
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade,
  RESOURCE_ID                   integer      not null references CALENDAR_OBJECT on delete cascade,
  ATTENDEE_COUNT                integer
);

create index SCHEDULE_REFRESH_WORK_HOME_RESOURCE_ID on
  SCHEDULE_REFRESH_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_REFRESH_WORK_RESOURCE_ID on
  SCHEDULE_REFRESH_WORK(RESOURCE_ID);

create table SCHEDULE_REFRESH_ATTENDEES (
  RESOURCE_ID                   integer      not null references CALENDAR_OBJECT on delete cascade,
  ATTENDEE                      varchar(255) not null,
  
  primary key (RESOURCE_ID, ATTENDEE)
);

------------------------------
-- Schedule Auto Reply Work --
------------------------------

create table SCHEDULE_AUTO_REPLY_WORK (
  WORK_ID                       integer      primary key references SCHEDULE_WORK on delete cascade, -- implicit index
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade,
  RESOURCE_ID                   integer      not null references CALENDAR_OBJECT on delete cascade,
  PARTSTAT                      varchar(255) not null
);

create index SCHEDULE_AUTO_REPLY_WORK_HOME_RESOURCE_ID on
  SCHEDULE_AUTO_REPLY_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_AUTO_REPLY_WORK_RESOURCE_ID on
  SCHEDULE_AUTO_REPLY_WORK(RESOURCE_ID);

-----------------------------
-- Schedule Organizer Work --
-----------------------------

create table SCHEDULE_ORGANIZER_WORK (
  WORK_ID                       integer      primary key references SCHEDULE_WORK on delete cascade, -- implicit index
  SCHEDULE_ACTION               integer      not null, -- Enum SCHEDULE_ACTION
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade,
  RESOURCE_ID                   integer,     -- this references a possibly non-existent CALENDR_OBJECT
  ICALENDAR_TEXT_OLD            text,
  ICALENDAR_TEXT_NEW            text,
  ATTENDEE_COUNT                integer,
  SMART_MERGE                   boolean
);

create index SCHEDULE_ORGANIZER_WORK_HOME_RESOURCE_ID on
  SCHEDULE_ORGANIZER_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_ORGANIZER_WORK_RESOURCE_ID on
  SCHEDULE_ORGANIZER_WORK(RESOURCE_ID);

-- Enumeration of schedule actions

create table SCHEDULE_ACTION (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into SCHEDULE_ACTION values (0, 'create');
insert into SCHEDULE_ACTION values (1, 'modify');
insert into SCHEDULE_ACTION values (2, 'modify-cancelled');
insert into SCHEDULE_ACTION values (3, 'remove');

----------------------------------
-- Schedule Organizer Send Work --
----------------------------------

create table SCHEDULE_ORGANIZER_SEND_WORK (
  WORK_ID                       integer      primary key references SCHEDULE_WORK on delete cascade, -- implicit index
  SCHEDULE_ACTION               integer      not null, -- Enum SCHEDULE_ACTION
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade,
  RESOURCE_ID                   integer,     -- this references a possibly non-existent CALENDAR_OBJECT
  ATTENDEE                      varchar(255) not null,
  ITIP_MSG                      text,
  NO_REFRESH                    boolean
);

create index SCHEDULE_ORGANIZER_SEND_WORK_HOME_RESOURCE_ID on
  SCHEDULE_ORGANIZER_SEND_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_ORGANIZER_SEND_WORK_RESOURCE_ID on
  SCHEDULE_ORGANIZER_SEND_WORK(RESOURCE_ID);

-------------------------
-- Schedule Reply Work --
-------------------------

create table SCHEDULE_REPLY_WORK (
  WORK_ID                       integer      primary key references SCHEDULE_WORK on delete cascade, -- implicit index
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade,
  RESOURCE_ID                   integer      not null references CALENDAR_OBJECT on delete cascade,
  CHANGED_RIDS                  text
);

create index SCHEDULE_REPLY_WORK_HOME_RESOURCE_ID on
  SCHEDULE_REPLY_WORK(HOME_RESOURCE_ID);
create index SCHEDULE_REPLY_WORK_RESOURCE_ID on
  SCHEDULE_REPLY_WORK(RESOURCE_ID);

--------------------------------
-- Schedule Reply Cancel Work --
--------------------------------

create table SCHEDULE_REPLY_CANCEL_WORK (
  WORK_ID                       integer      primary key references SCHEDULE_WORK on delete cascade, -- implicit index
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade,
  ICALENDAR_TEXT                text         not null
);

create index SCHEDULE_REPLY_CANCEL_WORK_HOME_RESOURCE_ID on
  SCHEDULE_REPLY_CANCEL_WORK(HOME_RESOURCE_ID);

----------------------------------
-- Principal Purge Polling Work --
----------------------------------

create table PRINCIPAL_PURGE_POLLING_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null
);

create index PRINCIPAL_PURGE_POLLING_WORK_JOB_ID on
  PRINCIPAL_PURGE_POLLING_WORK(JOB_ID);

--------------------------------
-- Principal Purge Check Work --
--------------------------------

create table PRINCIPAL_PURGE_CHECK_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  UID                           varchar(255) not null
);

create index PRINCIPAL_PURGE_CHECK_WORK_JOB_ID on
  PRINCIPAL_PURGE_CHECK_WORK(JOB_ID);

--------------------------
-- Principal Purge Work --
--------------------------

create table PRINCIPAL_PURGE_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  UID                           varchar(255) not null
);

create index PRINCIPAL_PURGE_WORK_JOB_ID on
  PRINCIPAL_PURGE_WORK(JOB_ID);

--------------------------------
-- Principal Home Remove Work --
--------------------------------

create table PRINCIPAL_PURGE_HOME_WORK (
  WORK_ID                       integer      primary key default nextval('WORKITEM_SEQ') not null, -- implicit index
  JOB_ID                        integer      references JOB not null,
  HOME_RESOURCE_ID              integer      not null references CALENDAR_HOME on delete cascade
);

create index PRINCIPAL_PURGE_HOME_WORK_JOB_ID on
  PRINCIPAL_PURGE_HOME_WORK(JOB_ID);
create index PRINCIPAL_PURGE_HOME_HOME_RESOURCE_ID on
  PRINCIPAL_PURGE_HOME_WORK(HOME_RESOURCE_ID);

--------------------
-- Schema Version --
--------------------

create table CALENDARSERVER (
  NAME                          varchar(255) primary key, -- implicit index
  VALUE                         varchar(255)
);

insert into CALENDARSERVER values ('VERSION', '44');
insert into CALENDARSERVER values ('CALENDAR-DATAVERSION', '6');
insert into CALENDARSERVER values ('ADDRESSBOOK-DATAVERSION', '2');
insert into CALENDARSERVER values ('NOTIFICATION-DATAVERSION', '1');
insert into CALENDARSERVER values ('MIN-VALID-REVISION', '1');