-- table declarations :

create table "code_list_item" (

    "l3" bigint not null,

    "l1" bigint not null,

    "i18n" boolean not null,

    "name" varchar(100) not null,

    "updated_at" timestamp 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),

    "created_at" timestamp not null,

    "created_by" bigint,

    "i2" integer not null,

    "oi3" integer,

    "l2" bigint not null,

    "ol3" bigint,

    "updated_by" 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,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "login" varchar(40) not null,

    "updated_by" bigint,

    "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,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "country_id" bigint not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "updated_by" bigint

  );

create sequence "city_id_seq";

create table "address" (

    "city_id" bigint not null,

    "name" varchar(100) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "zip_code" varchar(100) not null,

    "street_name" varchar(100) not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "street_num" varchar(100) not null,

    "updated_by" bigint

  );

create sequence "address_id_seq";

create table "country" (

    "iso3" varchar(3) not null,

    "name" varchar(100) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "iso2" varchar(2) not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "updated_by" bigint

  );

create sequence "country_id_seq";

create table "contact" (

    "name" varchar(100) not null,

    "updated_at" timestamp 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),

    "created_at" timestamp not null,

    "created_by" bigint,

    "other_mail" varchar(256),

    "position" varchar(40),

    "other_mobile" varchar(40),

    "updated_by" bigint,

    "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,

    "dic" varchar(40) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "ico" varchar(40) not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "address_id" bigint not null,

    "post_adress_id" bigint,

    "updated_by" bigint,

    "pin" integer not null

  );

create sequence "company_id_seq";

create table "bank_account" (

    "number" bigint not null,

    "name" varchar(100) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "prefix" bigint not null,

    "bank_code" varchar(100) not null,

    "company_id" bigint not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "updated_by" bigint

  );

create sequence "bank_account_id_seq";

-- 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 ("post_adress_id") references "address"("id") on delete set null;

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

-- column group indexes :

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