create table mailing_patterns
(
    id                  number(19) PRIMARY KEY   NOT NULL,
    infobip_template_id varchar2(256) default '',
    project_id          number(19)               NOT NULL references projects (id),
    channel_id          number(19)               NOT NULL references channels (id),
    name                varchar2(64)             NOT NULL,
    language            varchar2(16)             NOT NULL,
    category            varchar2(64)             NOT NULL,
    header              varchar2(1024),
    body                varchar2(4000)           NOT NULL,
    footer              varchar2(1024),
    buttons             varchar2(1024)           NOT NULL,
    status              varchar2(64)             NOT NULL,
    deleted             number(1)                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 column mailing_pattern_id number(19) references mailing_patterns (id);
alter table mailings
    add column type varchar2(64) default '';
update mailings
set type = 'free_text';

alter table mailings
    add "count" number(19) 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 timestamp with time zone;
alter table mailings
    add total_sent number(19) default 0;
alter table mailings
    add total_read number(19) default 0;

CREATE TABLE mailing_pattern_files
(
    pattern_id NUMBER(19)               NOT NULL,
    file_id    varchar2(255)            NOT NULL references files (id),
    changed_at timestamp with time zone not null,
    deleted    NUMBER(1)                not null
);
