--- mailings

create table mailings (
    id number(19) primary key,
    project_id number(19) not null references projects(id),
    state varchar2(255 char) not null,

    title varchar2(255 char) not null,
    user_id number(19) references users(id),
    channel_ids varchar2(4000 char),

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

create sequence mailings_id_seq;

create index mailings_project_ix 
on mailings(project_id);

-- messages

create table mailing_messages (
    id number(19) primary key,
    mailing_id number(19) not null references mailings(id),
    
    user_id number(19) not null references users(id),
    sender_id number(19) not null references users(id),
    state varchar2(255 char) not null,

    "type" varchar2(255 char) not null,
    "text" varchar2(2000 char) not null,
    file_id varchar2(255 char) references files(id),

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

create sequence mailing_messages_id_seq;

create index mailing_messages_mailing_ix on mailing_messages(mailing_id, created_at desc);
