--- quickreplies

-- folders

CREATE TABLE quick_reply_folders (
    id BIGINT PRIMARY KEY,
    project_id BIGINT NOT NULL REFERENCES projects(id),
    title VARCHAR(64) NOT NULL,
    deleted BOOLEAN NOT NULL,

    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

CREATE SEQUENCE quick_reply_folders_id_seq;

CREATE UNIQUE INDEX quick_reply_folders_project_id_title_uniq
    ON quick_reply_folders(project_id, lower(title))
    WHERE deleted = FALSE;

-- replies

CREATE TABLE quick_replies (
    id BIGINT PRIMARY KEY,
    project_id BIGINT NOT NULL REFERENCES projects(id),
    folder_id BIGINT NOT NULL REFERENCES quick_reply_folders(id),
    text VARCHAR(1024) NOT NULL,
    deleted BOOLEAN NOT NULL,

    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

CREATE SEQUENCE quick_replies_id_seq;

CREATE INDEX quick_replies_project_id_ix ON quick_replies(project_id);
CREATE INDEX quick_replies_folder_id_ix ON quick_replies(folder_id);
