-- PostgreSQL database schema.

begin work;

-- List of all tasks.
--
-- Note that unsorted tasks (which need to be moved to 'imminent',
-- 'todo', 'ideas', 'retired' lists) are any item which appears in
-- this table but not in one of the other tables.
create table tasks (
    id serial not null primary key,
    description text not null,
    created timestamp without time zone not null default now(),

    -- task source
    rhbz integer,               -- if task comes from RHBZ
    message_id text             -- if task comes from email
);

-- Tasks which must be completed immediately and should take no more
-- than a few minutes (else they should be moved to another list).
create table imminent (
    taskid integer not null references tasks (id),
    added timestamp without time zone not null default now()
);

-- Regular to-do items with deadlines.
create table todo (
    taskid integer not null references tasks (id),
    added timestamp without time zone not null default now(),
    deadline timestamp without time zone not null,
    estimate interval           -- optional
);

-- Ideas.  These have no committed deadline.
create table ideas (
    taskid integer not null references tasks (id),
    added timestamp without time zone not null default now()
);

-- Retired/done items.
create table retired (
    taskid integer not null references tasks (id),
    added timestamp without time zone not null default now()
);

-- Tags (eg 'urgent', 'personal', 'work') which can be added to any
-- task.
create table tags (
    id serial not null primary key,
    name text not null,
    colour text not null references colours (colour),
    unique (name)
);

create table tags_tasks (
    tagid integer not null references tags (id),
    taskid integer not null references tasks (id),
    unique (tagid, taskid)
);

create table colours (
    colour text not null,
    unique (colour)
);
insert into colours (colour) values ('black');
insert into colours (colour) values ('blue');
insert into colours (colour) values ('green');
insert into colours (colour) values ('red');
insert into colours (colour) values ('purple');
insert into colours (colour) values ('cyan');
insert into colours (colour) values ('yellow');

commit;
