
create table ordersupplier (
    os_nr               int             not null,
    os_name             varchar(100)    not null,
    os_part_url         varchar(500)    not null,
    os_needs_webnr      int             not null,
    os_url_nr_regex     varchar(500)    not null,
    os_url_webnr_regex  varchar(500)    not null,
    os_currency         varchar(5)      not null,
    os_del_flag         int             not null,
    constraint ordersupplier_pk primary key ( os_nr )
);

create table ordertag (
    ot_nr               int             not null,
    ot_parent_nr        int             not null,
    ot_name             varchar(100)    not null,
    ot_long_name        varchar(210)    not null,
    constraint ordertag_pk primary key ( ot_nr )
);

create table orderpart (
    op_nr               int             not null,
    os_nr               int             not null,
    op_part_nr          varchar(40)     not null,
    op_web_part_nr      varchar(80)         null,
    op_name             varchar(100)    not null,
    op_descr            varchar(200)        null,
    op_long_descr       text                null,
    op_price            int             not null,
    op_price_drop       int             not null,
    op_price_date       timestamp       not null,
    op_del_flag         int             not null,
    constraint orderpart_pk primary key ( op_nr )
);

create table orderparttag (
    op_nr               int             not null,
    ot_nr               int             not null,
    opt_primary         int             not null,
    constraint orderparttag_pk primary key ( op_nr, ot_nr )
);

create table orderuser (
    ou_nr               int             not null,
    ou_name             varchar(100)    not null,
    ou_del_flag         int             not null,
    constraint orderuser_pk primary key ( ou_nr )
);

create table orderpartuser (
    ou_nr               int             not null,
    op_nr               int             not null,
    oo_nr               int             not null,
    opu_type            int             not null,
    opu_count           int             not null,
    opu_price           int                 null,
    opu_price_chf       int                 null,
    constraint orderpartuser_pk primary key ( ou_nr, op_nr, oo_nr )
);

create table orderorder (
    oo_nr               int             not null,
    os_nr               int             not null,
    oo_order_date       timestamp       not null,
    oo_deliver_date     timestamp           null,
    oo_parts_price      int             not null,
    oo_parts_currency   varchar(5)      not null,
    oo_parts_cur_rate   int                 null,
    oo_ship_price       int                 null,
    oo_ship_currency    varchar(5)          null,
    oo_ship_cur_rate    int                 null,
    oo_taxes_price      int                 null,
    oo_taxes_currency   varchar(5)          null,
    oo_taxes_cur_rate   int                 null,
    oo_total_rate       int                 null,
    constraint orderorder_pk primary key ( oo_nr )
);

create table ordercurrencyrate (
    ocr_currency        varchar(5)      not null,
    ocr_date            timestamp       not null,
    ocr_value_chf       int             not null,
    constraint ordercurrencyrate_pk primary key ( ocr_currency, ocr_date )
);

create table orderorderuser (
    oo_nr               int             not null,
    ou_nr               int             not null,
    oou_parts_price     int             not null,
    oou_stock_flag      int             not null,
    constraint orderorderuser_pk primary key ( oo_nr, ou_nr )
);

create table orderdebt (
    od_nr               int             not null,
    ou_nr_creditor      int             not null,
    ou_nr_debitor       int             not null,
    od_amount           int             not null,
    od_reason           varchar(128)    not null,
    od_date             timestamp       not null,
    od_paid_date        timestamp           null,
    ou_nr_paid          int                 null,
    constraint orderdebt_pk primary key ( od_nr )
);

grant select, insert, update, delete on orderorder to "www-data";
grant select, insert, update, delete on orderorderuser to "www-data";
grant select, insert, update, delete on orderpart to "www-data";
grant select, insert, update, delete on orderparttag to "www-data";
grant select, insert, update, delete on orderpartuser to "www-data";
grant select, insert, update, delete on ordersupplier to "www-data";
grant select, insert, update, delete on ordertag to "www-data";
grant select, insert, update, delete on orderuser to "www-data";
grant select, insert, update, delete on ordercurrencyrate to "www-data";
grant select, insert, update, delete on orderdebt to "www-data";

