-- table declarations :

create table "code_list_item" (

    "l3" bigint not null,

    "l1" bigint not null,

    "i18n" boolean not null,

    "name" varchar(100) not null,

    "ol2" bigint,

    "s3" varchar(200) not null,

    "id" bigint primary key not null,

    "oi1" integer,

    "rank" integer not null,

    "code_list" varchar(40) not null,

    "dflt" boolean not null,

    "s2" varchar(200) not null,

    "i3" integer not null,

    "os1" varchar(200),

    "ol1" bigint,

    "oi2" integer,

    "i1" integer not null,

    "s1" varchar(200) not null,

    "note" varchar(10240),

    "os2" varchar(200),

    "i2" integer not null,

    "oi3" integer,

    "l2" bigint not null,

    "ol3" bigint,

    "os3" varchar(200),

    "deleted" boolean not null

  );

create sequence "code_list_item_id_seq";

-- indexes on code_list_item

create index "code_list_item_code_list_idx" on "code_list_item" ("code_list");

create table "user" (

    "name" varchar(100) not null,

    "id" bigint primary key not null,

    "note" varchar(10240),

    "login" varchar(40) not null,

    "deleted" boolean not null,

    "active" boolean not null,

    "password" varchar(128) not null

  );

create sequence "user_id_seq";

-- indexes on user

create index "user_login_idx" on "user" ("login");

create table "city" (

    "name" varchar(100) not null,

    "id" bigint primary key not null,

    "country_id" bigint not null,

  );

create sequence "s_city_id";

create table "address" (

    "id" bigint primary key not null,

    "city_id" bigint not null,

    "zip_code" varchar(100) not null,

    "street_name" varchar(100) not null,

    "street_num" varchar(100) not null

  );

create sequence "s_address_id";

create table "country" (

    "iso3" varchar(3) not null,

    "name" varchar(100) not null,

    "id" bigint primary key not null,

    "iso2" varchar(2) not null,

  );

create sequence "s_country_id";

create table "contact" (

    "name" varchar(100) not null,

    "id" bigint primary key not null,

    "work_mobile" varchar(40) not null,

    "private_mail" varchar(256),

    "last_name" varchar(80) not null,

    "work_mail" varchar(256) not null,

    "first_name" varchar(80) not null,

    "fax" varchar(40),

    "note" varchar(10240),

    "other_mail" varchar(256),

    "position" varchar(40),

    "other_mobile" varchar(40),

    "private_mobile" varchar(40),

    "private_phone" varchar(40),

    "work_phone" varchar(40)

  );

create sequence "contact_id_seq";

create table "company" (

    "name" varchar(100) not null,

    "id" bigint primary key not null,

    "partner" integer not null,

    "ico" varchar(40) not null,

    "note" varchar(10240),

    "corresp_address_id" bigint,

    "pin" integer not null,

    "address_id" bigint not null

  );

create sequence "s_company_id";

create table "bank_account" (

    "number" bigint not null,

    "id" bigint primary key not null,

    "prefix" bigint not null,

    "bank_code" varchar(100) not null,

    "company_id" bigint not null

  );

create sequence "s_bank_account_id";

-- foreign key constraints :

alter table "address" add constraint "addressFK1" foreign key ("city_id") references "city"("id");

alter table "city" add constraint "cityFK2" foreign key ("country_id") references "country"("id");

alter table "company" add constraint "companyFK3" foreign key ("address_id") references "address"("id");

alter table "company" add constraint "companyFK4" foreign key ("corresp_address_id") references "address"("id");

alter table "bank_account" add constraint "bank_accountFK5" foreign key ("company_id") references "company"("id");

-- column group indexes :

create index "user_deleted_active_idx" on "user" ("deleted","active");