create table rating_polls
(
    id                   bigint IDENTITY (1, 1) primary key,
    project_id           bigint references projects (id),
    status               nvarchar(64) default 'draft',
    deleted              bit         default 0,
    title                nvarchar(64)    not null,
    feedback_thanks      bit         default 0,
    feedback_thanks_text nvarchar(255),
    client_group_filter  bit         default 0,
    client_group_id      bigint references client_groups (id),
    channel_ids          nvarchar(4000),
    channel_filter       bit         default 0,
    category_ids         nvarchar(4000),
    category_filter      bit         default 0,
    marker_types         nvarchar(4000),
    marker_filter        bit         default 0,

    created_at           DATETIMEOFFSET NOT NULL,
    updated_at           DATETIMEOFFSET NOT NULL,
    deleted_at           DATETIMEOFFSET
);

create table rating_polls_questions
(
    id             bigint IDENTITY (1, 1) primary key,
    rating_poll_id bigint references rating_polls (id),
    text           nvarchar(255)   not null,
    type           nvarchar(64)    not null,
    [order] integer not null,
    deleted        bit default 0,
    created_at     DATETIMEOFFSET NOT NULL,
    updated_at     DATETIMEOFFSET NOT NULL,
    deleted_at     DATETIMEOFFSET
);

create table rating_polls_answers
(
    id                      bigint IDENTITY (1, 1) primary key,
    rating_poll_question_id bigint references rating_polls_questions (id),
    text                    nvarchar(255)   not null,
    "order"                 integer        not null,
    deleted                 bit default 0,
    created_at              DATETIMEOFFSET NOT NULL,
    updated_at              DATETIMEOFFSET NOT NULL,
    deleted_at              DATETIMEOFFSET
);

create table rating_polls_client_answers
(
    id                      bigint IDENTITY (1, 1) primary key,
    project_id              bigint references projects (id),
    client_id               bigint references clients (id),
    ticket_id               bigint references tickets (id),
    rating_id               bigint references ratings (id),
    rating_poll_question_id bigint references rating_polls_questions (id),
    type                    nvarchar(64)    not null,
    rating_poll_answer_id   bigint references rating_polls_answers (id),
    answer_input            nvarchar(255),
    answer_stars            integer default 0,
    created_at              DATETIMEOFFSET NOT NULL,
    updated_at              DATETIMEOFFSET NOT NULL
);

alter table ratings
    add rating_poll_id bigint references rating_polls (id);
