--- status-resolutions

create table status_types (
    id bigint primary key,
    name varchar not null,
    editable_resolutions boolean not null default false,
    weigth bigint not null,

    created_at timestamptz not null default now(),
    updated_at timestamptz,
    deleted_at timestamptz
);

create unique index uix_status_type on status_types(name);

CREATE SEQUENCE status_types_id_seq;

insert into status_types (id, name, editable_resolutions, weigth) values 
(nextval('status_types_id_seq'), 'offline', false, 4),
(nextval('status_types_id_seq'), 'active', false, 1),
(nextval('status_types_id_seq'), 'inactive', true, 3),
(nextval('status_types_id_seq'), 'busy', true, 2);

create table status_resolutions (
    id bigint primary key,
    parent_status_type bigint not null references status_types(id),
    deletable boolean default true,
    weigth bigint not null,

    created_at timestamptz not null default now(),
    updated_at timestamptz,
    deleted_at timestamptz
);

create table status_resolution_titles (
    id bigint primary key,
    resolution_id bigint not null references status_resolutions(id),
    lang varchar not null,
    title varchar not null,

    created_at timestamptz not null default now(),
    updated_at timestamptz,
    deleted_at timestamptz
);

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

insert into status_resolutions (id, parent_status_type, deletable, weigth) values 
(nextval('status_resolutions_id_seq'), (select id from status_types where status_types.name = 'offline'), false, 1 ),
(nextval('status_resolutions_id_seq'), (select id from status_types where status_types.name = 'active'), false, 1 ),
(nextval('status_resolutions_id_seq'), (select id from status_types where status_types.name = 'inactive'), false, 1 ),
(nextval('status_resolutions_id_seq'), (select id from status_types where status_types.name = 'busy'), false, 1 );

CREATE SEQUENCE status_resolutions_titles_id_seq;
insert into status_resolution_titles (id, resolution_id, lang, title) values 
(   
    nextval('status_resolutions_titles_id_seq'),
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'offline') limit 1),
    'RU', 
    'Офлайн'
),
(
    nextval('status_resolutions_titles_id_seq'),
    (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'
), 
(
    nextval('status_resolutions_titles_id_seq'),
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'active') limit 1),
    'RU', 
    'Активен'
), 
(
    nextval('status_resolutions_titles_id_seq'),
    (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'
),
(
    nextval('status_resolutions_titles_id_seq'),
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'inactive') limit 1),
    'RU', 
    'Перерыв'
), 
(
    nextval('status_resolutions_titles_id_seq'),
    (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'
), 
(
    nextval('status_resolutions_titles_id_seq'),
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'busy') limit 1),
    'RU', 
    'Не принимаю чаты'
),
(
    nextval('status_resolutions_titles_id_seq'),
    (select id from status_resolutions where status_resolutions.parent_status_type = (select id from status_types where status_types.name = 'busy') limit 1),
    'EN', 
    'Not accepting new tickets'
);
