create table mailing_patterns
(
    id                  bigint PRIMARY KEY       NOT NULL,
    infobip_template_id varchar(256) default '',
    project_id          bigint                   NOT NULL references projects (id),
    channel_id          bigint                   NOT NULL references channels (id),
    name                varchar(64)              NOT NULL,
    language            varchar(16)              NOT NULL,
    category            varchar(64)              NOT NULL,
    header              varchar(1024),
    body                varchar(4000)            NOT NULL,
    footer              varchar(1024),
    buttons             varchar(1024)            NOT NULL,
    status              varchar(64)              NOT NULL,
    deleted             boolean                  NOT NULL,
    created_at          timestamp with time zone NOT NULL,
    updated_at          timestamp with time zone
);

create sequence mailing_patterns_id_seq;

alter table mailings
    add mailing_pattern_id bigint references mailing_patterns (id);
alter table mailings
    add type varchar(64) default '';
update mailings
set type = 'free_text';

alter table mailings
    add "count" bigint default 0;

UPDATE mailings m
SET "count" = (SELECT COUNT(*) FROM mailing_messages ms WHERE m.id = ms.mailing_id)
WHERE EXISTS (SELECT 1 FROM mailing_messages ms WHERE m.id = ms.mailing_id);

alter table mailings
    add sent_at timestamptz;
alter table mailings
    add total_sent bigint default 0;
alter table mailings
    add total_read bigint default 0;

CREATE TABLE mailing_pattern_files
(
    pattern_id BIGINT                   NOT NULL,
    file_id    varchar(255)             NOT NULL references files (id),
    changed_at timestamp with time zone not null,
    deleted    boolean                  not null
);

create table mailings_messages_infobip
(
    id         bigint primary key,
    mailing_id bigint      not null references mailings (id),
    client_id  bigint      not null references clients (id),
    sent       bool default false,
    read       bool default false,

    created_at timestamptz not null,
    read_at    timestamptz,
    sent_at    timestamptz
);

create sequence mailings_msgs_infobip_id_seq;
