create table mailing_patterns
(
    id                  bigint IDENTITY (1, 1) PRIMARY KEY,
    infobip_template_id nvarchar(256) default '',
    project_id          bigint         NOT NULL references projects (id),
    channel_id          bigint         NOT NULL references channels (id),
    name                nvarchar(64)   NOT NULL,
    language            nvarchar(16)   NOT NULL,
    category            nvarchar(64)   NOT NULL,
    header              nvarchar(1024),
    body                nvarchar(4000) NOT NULL,
    footer              nvarchar(1024),
    buttons             nvarchar(1024) NOT NULL,
    status              nvarchar(64)   NOT NULL,
    deleted             bit            NOT NULL,
    created_at          DATETIMEOFFSET NOT NULL,
    updated_at          DATETIMEOFFSET
);

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

alter table mailings
    add "count" bigint default 0;

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

alter table mailings
    add sent_at DATETIMEOFFSET;
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 DATETIMEOFFSET not null,
    deleted    BIT            not null
);

create table mailings_messages_infobip
(
    id         bigint IDENTITY (1, 1) primary key,
    mailing_id bigint         not null references mailings (id),
    client_id  bigint         not null references clients (id),
    sent       bit default 0,
    "read"     bit default 0,

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