Untitled

create schema if not exists raeicei;

create sequence raeicei.revinfo_seq
    increment by 50;

create table raeicei.devices
(
    id          uuid         not null
        primary key,
    create_date timestamp(6),
    created_by  varchar(255),
    last_update timestamp(6),
    updated_by  varchar(255),
    version     bigint,
    description varchar(255),
    name        varchar(255) not null,
    type        varchar(255) not null
);

create table raeicei.eid_administrator
(
    id          uuid         not null
        primary key,
    create_date timestamp(6),
    created_by  varchar(255),
    last_update timestamp(6),
    updated_by  varchar(255),
    version     bigint,
    contact     varchar(255),
    eik_number  varchar(255) not null,
    is_active   boolean,
    name        varchar(255) not null
);

create table raeicei.administrator_front_office
(
    id                   uuid         not null
        primary key,
    create_date          timestamp(6),
    created_by           varchar(255),
    last_update          timestamp(6),
    updated_by           varchar(255),
    version              bigint,
    contact              varchar(255) not null,
    is_active            boolean,
    location             varchar(255) not null,
    name                 varchar(255) not null,
    region               varchar(255) not null,
    eid_administrator_id uuid         not null
        constraint fkaa7ai8y75cdlk4eit2m97vujj
            references raeicei.eid_administrator
);

create table raeicei.device_tariffs
(
    id                   uuid             not null
        primary key,
    create_date          timestamp(6),
    created_by           varchar(255),
    last_update          timestamp(6),
    updated_by           varchar(255),
    version              bigint,
    price                double precision not null,
    start_date           date             not null,
    device_id            uuid             not null
        constraint uk_lkp5h2uqn905sff9f9xkdy6js
            unique
        constraint fkiat50ixx0swp53sr81adc4wcg
            references devices,
    eid_administrator_id uuid             not null
        constraint fklnofaawx21hvodutubxfg2sg3
            references raeicei.eid_administrator
);

create table raeicei.discounts
(
    id                   uuid             not null
        primary key,
    create_date          timestamp(6),
    created_by           varchar(255),
    last_update          timestamp(6),
    updated_by           varchar(255),
    version              bigint,
    age_from             integer          not null,
    age_until            integer          not null,
    disability           boolean,
    discount             double precision not null,
    start_date           date             not null,
    eid_administrator_id uuid             not null
        constraint fkld0xlun3u81jfkiagrbtutag1
            references raeicei.eid_administrator
);

create table raeicei.tariffs
(
    id                       uuid             not null
        primary key,
    create_date              timestamp(6),
    created_by               varchar(255),
    last_update              timestamp(6),
    updated_by               varchar(255),
    version                  bigint,
    currency                 varchar(255)
        constraint tariffs_currency_check
            check ((currency)::text = ANY ((ARRAY ['BGN'::character varying, 'EUR'::character varying])::text[])),
    resume_application_price double precision not null,
    revoke_application_price double precision not null,
    start_application_price  double precision not null,
    start_date               date             not null,
    stop_application_price   double precision not null,
    eid_administrator_id     uuid             not null
        constraint fkobrmkn30xfhtoek0v91icoybs
            references raeicei.eid_administrator
);

create table raeicei.administrator_front_office_devices
(
    administrator_front_office_id uuid not null
        constraint fk2el8uygbiguylh28tuovtdswx
            references raeicei.administrator_front_office,
    devices_id                    uuid not null
        constraint uk_j5g08t98vllgff2p8fmqxoggs
            unique
        constraint fkw6j5gyfhb3kpi93a0vxra75a
            references raeicei.devices
);

create table raeicei.revinfo
(
    rev      integer not null
        primary key,
    revtstmp bigint
);

create table raeicei.administrator_front_office_aud
(
    id                   uuid    not null,
    rev                  integer not null
        constraint fkhc3vr2qvvsengi1m7f87ekoi
            references raeicei.revinfo,
    revtype              smallint,
    contact              varchar(255),
    is_active            boolean,
    location             varchar(255),
    name                 varchar(255),
    region               varchar(255)
        constraint administrator_front_office_aud_region_check
            check ((region >= 0) AND (region <= 28)),
    eid_administrator_id uuid,
    primary key (rev, id)
);

create table raeicei.device_tariffs_aud
(
    id                   uuid    not null,
    rev                  integer not null
        constraint fk3dc8gm082upwq6x2jxw6l1oy2
            references raeicei.revinfo,
    revtype              smallint,
    price                double precision,
    start_date           date,
    device_id            uuid,
    eid_administrator_id uuid,
    primary key (rev, id)
);

create table devices_aud
(
    id          uuid    not null,
    rev         integer not null
        constraint fk334l0xy7gy0qjjfr5pe1wwxie
            references raeicei.revinfo,
    revtype     smallint,
    description varchar(255),
    name        varchar(255),
    type        varchar(255),
    primary key (rev, id)
);

create table raeicei.discounts_aud
(
    id                   uuid    not null,
    rev                  integer not null
        constraint fkn2d7xmo6yoj2m4nso3eta8cdu
            references raeicei.revinfo,
    revtype              smallint,
    age_from             integer,
    age_until            integer,
    disability           boolean,
    discount             double precision,
    start_date           date,
    eid_administrator_id uuid,
    primary key (rev, id)
);

create table raeicei.eid_administrator_aud
(
    id         uuid    not null,
    rev        integer not null
        constraint fk8rh5kc77fqy9h2ebea77nibfv
            references raeicei.revinfo,
    revtype    smallint,
    contact    varchar(255),
    eik_number varchar(255),
    is_active  boolean,
    name       varchar(255),
    primary key (rev, id)
);

create table raeicei.tariffs_aud
(
    id                       uuid    not null,
    rev                      integer not null
        constraint fk5jarao91dpmdsml4dvdnywne4
            references raeicei.revinfo,
    revtype                  smallint,
    currency                 varchar(255)
        constraint tariffs_aud_currency_check
            check ((currency)::text = ANY ((ARRAY ['BGN'::character varying, 'EUR'::character varying])::text[])),
    resume_application_price double precision,
    revoke_application_price double precision,
    start_application_price  double precision,
    start_date               date,
    stop_application_price   double precision,
    eid_administrator_id     uuid,
    primary key (rev, id)
);

create table raeicei.administrator_front_office_devices_aud
(
    rev                           integer not null
        constraint fk7xb7vhu9tffsosugmf6uwg62c
            references raeicei.revinfo,
    administrator_front_office_id uuid    not null,
    devices_id                    uuid    not null,
    revtype                       smallint,
    primary key (administrator_front_office_id, rev, devices_id)
);