-- 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 "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 "location" (

    "name" varchar(100) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "latitude" double precision not null,

    "longitude" double precision not null,

    "note" varchar(10240),

    "created_at" timestamp not null,

    "created_by" bigint,

    "address_id" bigint not null,

    "updated_by" bigint

  );

create sequence "location_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";

create table "company_contact" (

    "contact" bigint not null,

    "entity" bigint not null

  );

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 "user_contact" (

    "contact" bigint not null,

    "entity" bigint not null

  );

create table "project" (

    "name" varchar(100) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "description" varchar(40960) not null,

    "responsible" bigint not null,

    "deadline" timestamp not null,

    "ident_s" varchar(256) not null,

    "state" bigint not null,

    "note" varchar(10240),

    "product_line" bigint,

    "created_at" timestamp not null,

    "created_by" bigint,

    "updated_by" bigint

  );

create sequence "project_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 "location" add constraint "locationFK3" foreign key ("address_id") references "address"("id");

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

alter table "company" add constraint "companyFK5" foreign key ("post_adress_id") references "address"("id") on delete set null;

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

alter table "project" add constraint "projectFK11" foreign key ("responsible") references "user"("id");

alter table "project" add constraint "projectFK12" foreign key ("product_line") references "code_list_item"("id") on delete set null;

alter table "project" add constraint "projectFK13" foreign key ("state") references "code_list_item"("id");

alter table "company_contact" add constraint "company_contactFK7" foreign key ("entity") references "company"("id") on delete cascade;

alter table "company_contact" add constraint "company_contactFK8" foreign key ("contact") references "contact"("id") on delete cascade;

alter table "user_contact" add constraint "user_contactFK9" foreign key ("entity") references "user"("id") on delete cascade;

alter table "user_contact" add constraint "user_contactFK10" foreign key ("contact") references "contact"("id") on delete cascade;

-- composite key indexes :

alter table "company_contact" add constraint "company_contactCPK" unique("entity","contact");

alter table "user_contact" add constraint "user_contactCPK" unique("entity","contact");

-- column group indexes :

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