CREATE TABLE Project
( project_id            INTEGER PRIMARY KEY
, name                  TEXT    NOT NULL    COLLATE NOCASE  UNIQUE
, code                  TEXT                COLLATE NOCASE
, description           TEXT
, customer              TEXT                COLLATE NOCASE
, remarks               TEXT
);

CREATE TABLE ProjectPeople
( projectpeople_id      INTEGER PRIMARY KEY
, project_id            INTEGER NOT NULL    REFERENCES Project
, person                TEXT    NOT NULL    COLLATE NOCASE
, user_id               INTEGER             REFERENCES User
, role                  TEXT                COLLATE NOCASE  -- REFERENCES PeopleRole ON UPDATE CASCADE
, remarks               TEXT
, UNIQUE (project_id, person, role)
);

CREATE TABLE PeopleRole
( role                  TEXT    NOT NULL    PRIMARY KEY COLLATE NOCASE
, description           TEXT
);

CREATE TABLE User
( user_id               INTEGER PRIMARY KEY
, code                  TEXT    NOT NULL    COLLATE NOCASE
, full_name             TEXT    NOT NULL    COLLATE NOCASE
, remarks               TEXT
);

CREATE TABLE Activity
( activity_id           INTEGER     PRIMARY KEY
, project_id            INTEGER     NOT NULL    REFERENCES Project
, user_id               INTEGER     NOT NULL    REFERENCES User
, remarks               TEXT
, start_time            TIMESTAMP   NOT NULL
, duration              INTEGER     NOT NULL    DEFAULT 0
, active                BOOLEAN     NOT NULL    DEFAULT 1
);

CREATE TABLE Comments
( comment_id            INTEGER     PRIMARY KEY
, text                  TEXT        NOT NULL    COLLATE NOCASE
, date_inserted         TIMESTAMP   NOT NULL
, date_updated          TIMESTAMP
);
/*

When creating tables, make sure project with ID 1 is '(pauze)';

When connecting to the database:
SELECT user_id FROM User WHERE code = :UserName;
if user_id = NULL then
    -- get UserName from login name, FullName from ... ActiveDirectory? Outlook? Windows users?
    -- see http://stackoverflow.com/a/3687647/60590
    INSERT INTO User (code, full_name) VALUES (:UserName, :FullName);
    :UserID = last_insert_rowid();

When entering an activity, check for presence of project
SELECT project_id FROM Project WHERE name = :ProjectName;
if project_id IS NULL then
    INSERT INTO Project (name) VALUES (:ProjectName);
    :ProjectID = last_insert_rowid();

When starting an activity
INSERT INTO Activity (project_id, user_id, remarks, start_time)
    SELECT :ProjectID, :UserID, :Remarks, julianday('now');
:ActivityID = last_insert_rowid();

When stopping an activity
UPDATE Activity
   SET duration = (julianday('now') - start_time) * 86400
   ,   active = 0
 WHERE activity_id = :ActivityID;

When updating an activity's remarks
UPDATE Activity
   SET remarks = :Remarks
 WHERE activity_id = :ActivityID;

When retroactively switching activities:
BEGIN;
-- delete all activities for this user that started after the :StartTime
DELETE FROM Activity
 WHERE user_id = :UserID
   AND start_time > :StartTime;
-- curtail all activities for this user that started *before* the :StartTime, AND EITHER whose 
--  duration extends until *after* the :StartTime OR which are still active.
UPDATE Activity
   SET duration = (julianday(:StartTime, 'utc') - start_time) * 86400
   ,   active = 0
 WHERE user_id = :UserID
   AND start_time <= julianday(:StartTime, 'utc')
   AND (active = 1 OR julianday(start_time, '+' || a.duration || ' seconds') > julianday(:StartTime, 'utc'))
-- and insert the new activity
INSERT INTO Activity (project_id, user_id, remarks, start_time)
    SELECT :ProjectID, :UserID, :Remarks, julianday(:StartTime, 'utc');
:ActivityID = last_insert_rowid();
COMMIT;

*/
 
/*
To allow FTS searching for projects as well as activities, perhaps add an FTS table for each:
*/
CREATE VIEW vfts_Project AS
    SELECT p.project_id
    ,      p.name, p.code, p.description, p.remarks, p.customer
    ,      group_concat(pp.person || ' (' || pp.role || ')' || ifnull(': ' || pp.remarks, ''), '; ')
      FROM Project p
           LEFT JOIN ProjectPeople pp ON p.project_id = pp.project_id
  GROUP BY p.project_id
;

CREATE VIRTUAL TABLE fts_Project USING FTS4 ( name, code, description, remarks, customer, people, tokenize=unicode61);

-- automatically update the FTS table using a few triggers
CREATE TRIGGER trg_aiu_Project
AFTER INSERT OR UPDATE ON Project
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, remarks, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id
    ;
END;
CREATE TRIGGER trg_aiu_ProjectPeople
AFTER INSERT OR UPDATE ON ProjectPeople
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, remarks, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id
    ;
END;
CREATE TRIGGER trg_au_User_Project
AFTER UPDATE OF code, full_name ON User
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, remarks, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id IN (SELECT DISTINCT project_id FROM ProjectPeople WHERE user_id = NEW.user_id)
    ;
END;


CREATE VIRTUAL TABLE fts_Activity USING FTS4 ( remarks, username, tokenize=unicode61 );

-- automatically update the FTS table using a few triggers
CREATE TRIGGER trg_aiu_Activity
AFTER INSERT OR UPDATE OF remarks, user_id ON Activity
BEGIN
    INSERT OR REPLACE INTO fts_Activity ( docid, remarks, username )
        SELECT a.activity_id
        ,      a.remarks
        ,      u.code || ifnull(' (' || u.full_name || ')', '')
          FROM Activity a
               LEFT JOIN User u ON a.user_id = u.user_id
         WHERE a.activity_id = NEW.activity_id
    ;
END;
CREATE TRIGGER trg_au_User_Activity
AFTER UPDATE OF code, full_name ON User
BEGIN
    INSERT OR REPLACE INTO fts_Activity ( docid, remarks, username )
        SELECT a.activity_id
        ,      a.remarks
        ,      u.code || ifnull(' (' || u.full_name || ')', '')
          FROM Activity a
               LEFT JOIN User u ON a.user_id = u.user_id
         WHERE a.user_id = NEW.user_id
    ;
END;


-- Query to perform the actual search:
SELECT p.project_id
,      p.name
,      snippet
,      datetime(a.start_time, 'localtime') AS time_start
,      datetime(a.start_time, 'localtime', '+' || a.duration || ' seconds') AS time_finish
  FROM (
    SELECT docid AS project_id
    ,      NULL AS activity_id
    ,      snippet(fts_Project) AS snippet
      FROM fts_Project
     WHERE fts_Project MATCH :SearchTerms
    UNION
    SELECT NULL AS project_id
    ,      docid AS activity_id
    ,      snippet(fts_Activity) AS snippet
      FROM fts_Activity
     WHERE fts_Activity MATCH :SearchTerms
       ) m
       LEFT JOIN Activity a ON m.activity_id = a.activity_id
       LEFT JOIN Project p ON coalesce(m.project_id, a.project_id) = p.project_id
;