--- quickreplies

-- folders

CREATE TABLE quick_reply_folders (
    id NUMBER(19) PRIMARY KEY,
    project_id NUMBER(19) NOT NULL REFERENCES projects(id),
    title VARCHAR2(64 CHAR) NOT NULL,
    deleted NUMBER(1) NOT NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE quick_reply_folders_id_seq;

CREATE UNIQUE INDEX quick_reply_folders_uniq ON quick_reply_folders(
    CASE WHEN deleted = 0
        THEN project_id
    END,
    CASE WHEN deleted = 0
        THEN lower(title)
    END
);

-- replies

CREATE TABLE quick_replies (
    id NUMBER(19) PRIMARY KEY,
    project_id NUMBER(19) NOT NULL REFERENCES projects(id),
    folder_id NUMBER(19) NOT NULL REFERENCES quick_reply_folders(id),
    text VARCHAR2(1024 CHAR) NOT NULL,
    deleted NUMBER(1) NOT NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE quick_replies_id_seq;

CREATE INDEX quick_replies_project_ix ON quick_replies(project_id);
CREATE INDEX quick_replies_folder_ix ON quick_replies(folder_id);
