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

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

create sequence rating_polls_id_seq;

create table rating_polls_questions
(
    id             bigint primary key,
    rating_poll_id bigint references rating_polls (id),
    text           varchar(255) not null,
    type           varchar(64)  not null,
    "order"        integer      not null,
    deleted        boolean default false,
    created_at     timestamptz  NOT NULL,
    updated_at     timestamptz  NOT NULL,
    deleted_at     timestamptz
);

create sequence rating_polls_questions_id_seq;

create table rating_polls_answers
(
    id                      bigint primary key,
    rating_poll_question_id bigint references rating_polls_questions (id),
    text                    varchar(255) not null,
    "order"                 integer      not null,
    deleted                 boolean default false,
    created_at              timestamptz  NOT NULL,
    updated_at              timestamptz  NOT NULL,
    deleted_at              timestamptz
);

create sequence rating_polls_answers_id_seq;

create table rating_polls_client_answers
(
    id                      bigint 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                    varchar(64) not null,
    rating_poll_answer_id   bigint references rating_polls_answers (id),
    answer_input            varchar(255),
    answer_stars            integer default 0,
    created_at              timestamptz NOT NULL,
    updated_at              timestamptz NOT NULL
);

create sequence rating_polls_client_answers_id_seq;

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