CREATE TABLE vk_publics (
                                  id NUMBER(19) PRIMARY KEY,
                                  title VARCHAR2(255) not null,
                                  vk_link varchar2(512) not null,
                                  project_id NUMBER(19) NOT NULL references projects(id),
                                  vk_group_id NUMBER(19) not null,
                                  channel_id NUMBER(19) NOT NULL references channels (id),
                                  user_token varchar2(2000),
                                  deleted NUMBER(1) NOT NULL,

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

CREATE SEQUENCE vk_publics_id_seq;

CREATE UNIQUE INDEX vk_publics_uniq ON vk_publics (
                                                               CASE WHEN deleted = 0
                                                               THEN vk_link
                                                               END,
                                                               CASE WHEN deleted = 0
                                                               THEN channel_id
                                                               END
    );

create table vk_posts (
                                id number(19),
                                public_id number(19) not null references vk_publics(id),
                                vk_id number(19),
                                poll_id number(19),
                                poll_end_date number(19),
                                user_id number(19) not null references users(id),
                                state varchar2(255) not null,

                                "type" varchar2(255) not null,
                                "text" varchar2(2000) not null,
                                header varchar2(500) not null,
                                background_id number(7),
                                background_image_id varchar2(255) references files(id),
                                is_multiple number(1),
                                has_files number(1) not null,
                                deleted number(1) not null,

                                created_at timestamp with time zone not null,
                                updated_at timestamp with time zone not null,
                                sent_at timestamp with time zone,
                                deleted_at timestamp with time zone
);

CREATE SEQUENCE vk_posts_id_seq;

create index vk_posts_public_ix on vk_posts(public_id, created_at desc);
