-- table declarations :
create table "code_list_item" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "i18n" boolean not null,
    "rank" integer not null,
    "dflt" boolean not null,
    "code_list" varchar(40) not null,
    "note" varchar(10240),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint,
    "i1" integer not null,
    "i2" integer not null,
    "i3" integer not null,
    "l1" bigint not null,
    "l2" bigint not null,
    "l3" bigint not null,
    "s1" varchar(200) not null,
    "s2" varchar(200) not null,
    "s3" varchar(200) not null,
    "oi1" integer,
    "oi2" integer,
    "oi3" integer,
    "ol1" bigint,
    "ol2" bigint,
    "ol3" bigint,
    "os1" varchar(200),
    "os2" varchar(200),
    "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" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "note" varchar(10240),
    "country_id" bigint not null,
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "city_id_seq";
create table "address" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "street_name" varchar(100) not null,
    "street_num" varchar(100) not null,
    "zip_code" varchar(100) not null,
    "city_id" bigint not null,
    "note" varchar(10240),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "address_id_seq";
create table "country" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "iso2" varchar(2) not null,
    "iso3" varchar(3) not null,
    "note" varchar(10240),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "country_id_seq";
create table "location" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "address_id" bigint not null,
    "note" varchar(10240),
    "latitude" double precision not null,
    "longitude" double precision not null,
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "location_id_seq";
create table "contact" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "first_name" varchar(80) not null,
    "last_name" varchar(80) not null,
    "position" varchar(40),
    "note" varchar(10240),
    "work_mail" varchar(256) not null,
    "work_phone" varchar(40),
    "work_mobile" varchar(40) not null,
    "private_mail" varchar(256),
    "private_phone" varchar(40),
    "private_mobile" varchar(40),
    "other_mail" varchar(256),
    "other_mobile" varchar(40),
    "fax" varchar(40),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "contact_id_seq";
create table "company" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "dic" varchar(40) not null,
    "ico" varchar(40) not null,
    "note" varchar(10240),
    "status" bigint not null,
    "address_id" bigint not null,
    "post_adress_id" bigint,
    "pin" integer not null,
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "company_id_seq";
create table "bank_account" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "prefix" bigint not null,
    "number" 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_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "bank_account_id_seq";
create table "company_contact" (
    "contact" bigint not null,
    "entity" bigint not null
  );
create table "user" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "login" varchar(40) not null,
    "note" varchar(10240),
    "active" boolean not null,
    "password" varchar(128) not null,
    "deleted" boolean not null,
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
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" (
    "id" bigint primary key not null,
    "name" varchar(100) 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,
    "product_line" bigint,
    "note" varchar(10240),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "project_id_seq";
create table "task" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "project_id" bigint not null,
    "responsible" bigint not null,
    "deadline" timestamp not null,
    "state" bigint not null,
    "task_type" bigint,
    "note" varchar(10240),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "task_id_seq";
create table "comment" (
    "id" bigint primary key not null,
    "name" varchar(100) not null,
    "task_id" bigint not null,
    "note" varchar(10240),
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_at" timestamp not null,
    "updated_by" bigint
  );
create sequence "comment_id_seq";
create table "project_company" (
    "project" bigint not null,
    "company" bigint not null
  );
-- foreign key constraints :
alter table "address" add foreign key ("city_id") references "city"("id");
alter table "city" add foreign key ("country_id") references "country"("id");
alter table "location" add foreign key ("address_id") references "address"("id");
alter table "company" add foreign key ("status") references "code_list_item"("id");
alter table "company" add foreign key ("address_id") references "address"("id");
alter table "company" add foreign key ("post_adress_id") references "address"("id") on delete set null;
alter table "bank_account" add foreign key ("company_id") references "company"("id") on delete cascade;
alter table "project" add foreign key ("responsible") references "user"("id");
alter table "project" add foreign key ("product_line") references "code_list_item"("id") on delete set null;
alter table "project" add foreign key ("state") references "code_list_item"("id");
alter table "task" add foreign key ("responsible") references "user"("id");
alter table "task" add foreign key ("task_type") references "code_list_item"("id");
alter table "task" add foreign key ("state") references "code_list_item"("id");
alter table "task" add foreign key ("project_id") references "project"("id");
alter table "comment" add foreign key ("task_id") references "task"("id") on delete cascade;
alter table "company_contact" add foreign key ("entity") references "company"("id") on delete cascade;
alter table "company_contact" add foreign key ("contact") references "contact"("id") on delete cascade;
alter table "user_contact" add foreign key ("entity") references "user"("id") on delete cascade;
alter table "user_contact" add foreign key ("contact") references "contact"("id") on delete cascade;
alter table "project_company" add foreign key ("project") references "project"("id") on delete cascade;
alter table "project_company" add foreign key ("company") references "company"("id") on delete cascade;
-- composite key indexes :
alter table "company_contact" add unique("entity","contact");
alter table "user_contact" add unique("entity","contact");
alter table "project_company" add unique("project","company");
-- column group indexes :
create index "user_deleted_active_idx" on "user" ("deleted","active");