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

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

create sequence rating_polls_id_seq;

create table rating_polls_questions
(
    id             number(19) primary key,
    rating_poll_id number(19) references rating_polls (id),
    text           varchar2(255) not null,
    type           varchar2(64)  not null,
    "order"        number(7)      not null,
    deleted        number(1) default 0,
    created_at     TIMESTAMP WITH TIME ZONE  NOT NULL,
    updated_at     TIMESTAMP WITH TIME ZONE  NOT NULL,
    deleted_at     TIMESTAMP WITH TIME ZONE
);

create sequence rating_polls_questions_id_seq;

create table rating_polls_answers
(
    id                      number(19) primary key,
    rating_poll_question_id number(19) references rating_polls_questions (id),
    text                    varchar2(255) not null,
    "order"                 number(7)      not null,
    deleted                 number(1) default 0,
    created_at              TIMESTAMP WITH TIME ZONE  NOT NULL,
    updated_at              TIMESTAMP WITH TIME ZONE  NOT NULL,
    deleted_at              TIMESTAMP WITH TIME ZONE
);

create sequence rating_polls_answers_id_seq;

create table rating_polls_client_answers
(
    id                      number(19) primary key,
    project_id              number(19) references projects (id),
    client_id               number(19) references clients (id),
    ticket_id               number(19) references tickets (id),
    rating_id               number(19) references ratings (id),
    rating_poll_question_id number(19) references rating_polls_questions (id),
    type                    varchar2(64) not null,
    rating_poll_answer_id   number(19) references rating_polls_answers (id),
    answer_input            varchar2(255),
    answer_stars            number(7) default 0,
    created_at              TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at              TIMESTAMP WITH TIME ZONE NOT NULL
);

create sequence rating_polls_client_answers_id_seq;

alter table ratings add rating_poll_id number(19) references rating_polls(id);
