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

-- project extensions
UPDATE code_list_item SET s1 = 'closed', i1 = 0 WHERE code_list = 'project_state' AND i1 = 1;
UPDATE code_list_item SET s1 = 'assigned' WHERE code_list = 'project_state' AND name LIKE '%assigned';
ALTER TABLE project ADD COLUMN phase bigint;
alter table "project" add foreign key ("phase") references "code_list_item"("id");
UPDATE project SET phase = (select id from code_list_item where name = 'project.phase.offer');
ALTER TABLE project ALTER COLUMN phase SET NOT NULL;
ALTER TABLE project ADD COLUMN "location_a" varchar(1024);
ALTER TABLE project ADD COLUMN "location_b" varchar(1024);
UPDATE project SET location_a = '', location_b = '';
ALTER TABLE project ALTER COLUMN "location_a" SET NOT NULL ;
ALTER TABLE project ALTER COLUMN "location_b" SET NOT NULL ;