--- status-resolutions
create table status_types (
    id number(19) primary key,
    name varchar2(255 char) not null,
    editable_resolutions number(1) not null default 0,
    weigth number(19) not null,

    created_at datetimeoffset not null default SYSDATETIMEOFFSET(),
    updated_at datetimeoffset,
    deleted_at datetimeoffset
)

create unique index uix_status_type on status_types(name);

insert into status_types (name, editable_resolutions, weigth) values 
('offline', 0, 4),
('active', 0, 1),
('inactive', 1, 3),
('busy', 1, 2);

create table status_resolutions (
    id number(19) primary key,
    parent_status_type varchar2(255 char) not null,
    deletable number(1) not null default 0,
    weigth number(19) not null,

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

create table status_resolution_titles (
    id number(19) primary key,
    resolution_id number(19) not null references status_resolutions(id),
    lang varchar2(255 char) not null,
    title varchar2(255 char) not null,

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

create unique index uix_language_resolution ON status_resolution_titles(resolution_id, lang);

insert into status_resolutions (parent_status_type, deletable, created_at, weigth) values 
( (select id from status_types where status_types.name = 'offline'), 0, (select SYSTIMESTAMP from dual), 1 ),
( (select id from status_types where status_types.name = 'active'), 0, (select SYSTIMESTAMP from dual), 1),
( (select id from status_types where status_types.name = 'inactive'), 0, (select SYSTIMESTAMP from dual), 1),
( (select id from status_types where status_types.name = 'busy'), 0, (select SYSTIMESTAMP from dual), 1);

insert into status_resolution_titles (resolution_id, lang, title, created_at) values 
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'offline') limit 1),
    'RU', 
    'Офлайн',  
    (select SYSTIMESTAMP from dual)
),
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'offline') limit 1),
    'EN', 
    'Offline',  
    (select SYSTIMESTAMP from dual)
), 
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'active') limit 1),
    'RU', 
    'Активен',  
    (select SYSTIMESTAMP from dual)
), 
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'active') limit 1),
    'EN', 
    'Active',  
    (select SYSTIMESTAMP from dual)
),
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'inactive') limit 1),
    'RU', 
    'Перерыв',  
    (select SYSTIMESTAMP from dual)
), 
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'inactive') limit 1),
    'EN', 
    'Break',  
    (select SYSTIMESTAMP from dual)
), 
(
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'busy') limit 1),
    'RU', 
    'Не принимаю чаты',  
    (select SYSTIMESTAMP from dual)
),
(
    (select id from status_resolutions where status_resolutions.parent_status_type = 'busy' limit 1),
    'EN', 
    'Not accepting new tickets',  
    (select SYSTIMESTAMP from dual)
);
