-- DATABASE SCHEMA CHANGES BETWEEN 0.2 -> 0.3 VERSION

-- project_location
create table "project_location" (
    "location" bigint not null,
    "project" bigint not null
  );
alter table "project_location" add foreign key ("project") references "project"("id") on delete cascade;
alter table "project_location" add foreign key ("location") references "location"("id") on delete cascade;
alter table "project_location" add unique("project","location");

-- service
create table "service" (
    "name" varchar(100) not null,
    "updated_at" timestamp not null,
    "id" bigint primary key not null,
    "to" timestamp,
    "state" bigint not null,
    "company" bigint not null,
    "note" varchar(10240),
    "from" timestamp not null,
    "created_at" timestamp not null,
    "created_by" bigint,
    "updated_by" bigint
  );
create sequence "service_id_seq";
alter table "service" add foreign key ("company") references "company"("id");
alter table "service" add foreign key ("state") references "code_list_item"("id");

-- service payment
create table "service_payment" (
    "id" bigint primary key not null,
    "service" bigint not null,
    "direction" integer not null,
    "period" bigint not null,
    "amount" numeric(16,2) not null,
    "currency" bigint not null
  );
create sequence "service_payment_id_seq";
alter table "service_payment" add foreign key ("service") references "service"("id") on delete cascade;
alter table "service_payment" add foreign key ("period") references "code_list_item"("id");
alter table "service_payment" add foreign key ("currency") references "code_list_item"("id");