--- quickreplies

-- folders

CREATE TABLE quick_reply_folders (
    id BIGINT PRIMARY KEY IDENTITY(1, 1),
    project_id BIGINT NOT NULL REFERENCES projects(id),
    title NVARCHAR(64) NOT NULL,
    title_lower AS lower(title),
    deleted BIT NOT NULL,

    created_at DATETIMEOFFSET NOT NULL,
    updated_at DATETIMEOFFSET NOT NULL
);

CREATE UNIQUE INDEX quick_reply_folders_project_id_title_uniq
    ON quick_reply_folders(project_id, title_lower)
    WHERE deleted = 0;

-- replies

CREATE TABLE quick_replies (
    id BIGINT PRIMARY KEY IDENTITY(1, 1),
    project_id BIGINT NOT NULL REFERENCES projects(id),
    folder_id BIGINT NOT NULL references quick_reply_folders(id),
    text NVARCHAR(1024) NOT NULL,
    deleted BIT NOT NULL,

    created_at DATETIMEOFFSET NOT NULL,
    updated_AT DATETIMEOFFSET NOT NULL
);

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