-- 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");

-- attachments

create table "attachment" (

    "name" varchar(100) not null,

    "updated_at" timestamp not null,

    "id" bigint primary key not null,

    "mime_type" varchar(80) not null,

    "note" varchar(10240),

    "content_id" varchar(20) not null,

    "size" bigint not null,

    "created_at" timestamp not null,

    "created_by" bigint,

    "updated_by" bigint

  );

create sequence "attachment_id_seq";

create table "project_attachment" (

    "project" bigint not null,

    "attachment" bigint not null

  );

create table "task_attachment" (

    "task" bigint not null,

    "attachment" bigint not null

  );

alter table "project_attachment" add foreign key ("project") references "project"("id") on delete cascade;

alter table "project_attachment" add foreign key ("attachment") references "attachment"("id") on delete cascade;

alter table "task_attachment" add foreign key ("task") references "task"("id") on delete cascade;

alter table "task_attachment" add foreign key ("attachment") references "attachment"("id") on delete cascade;

alter table "project_attachment" add unique("project","attachment");

alter table "task_attachment" add unique("task","attachment");

-- task number

create sequence task_2012;

create sequence task_2013;

create sequence task_2014;

create sequence task_2015;

create sequence task_2016;

create sequence task_2017;

create sequence task_2018;

create sequence task_2019;

create sequence task_2020;

ALTER TABLE task ADD num integer;

CREATE VIEW vtask AS SELECT * FROM task ORDER BY id;

CREATE RULE rtask AS ON UPDATE TO vtask DO INSTEAD UPDATE task SET num = NEW.num WHERE id = NEW.id;

UPDATE vtask SET num = nextval('task_2012');

DROP RULE rtask ON vtask;

DROP VIEW vtask;

ALTER TABLE task ALTER COLUMN num SET NOT NULL;