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
);
INSERT INTO Project VALUES (1, '(pauze)', 'AFW', 'Lunch, of niet-werkgerelateerde zaken', NULL);

CREATE TABLE ProjectRemarks
( project_remark_id     INTEGER PRIMARY KEY
, project_id            INTEGER NOT NULL    REFERENCES Project
, user_id               INTEGER NOT NULL    REFERENCES User
, remarks               TEXT    NOT NULL
, UNIQUE (project_id, user_id)
);

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
);
INSERT INTO PeopleRole VALUES ('projectleider', NULL);
INSERT INTO PeopleRole VALUES ('contactpersoon', NULL);

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

*/
 
/*
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.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, customer, people, tokenize=unicode61);

-- automatically update the FTS table using a few triggers
CREATE TRIGGER trg_ai_Project
AFTER INSERT ON Project
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id;

END;
CREATE TRIGGER trg_au_Project
AFTER UPDATE ON Project
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id;
END;
CREATE TRIGGER trg_ai_ProjectPeople
AFTER INSERT ON ProjectPeople
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, customer, people )
        SELECT *
          FROM vfts_Project
         WHERE project_id = NEW.project_id;
END;
CREATE TRIGGER trg_au_ProjectPeople
AFTER UPDATE ON ProjectPeople
BEGIN
    INSERT OR REPLACE INTO fts_Project ( docid, name, code, description, 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, 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_ai_Activity
AFTER INSERT 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_Activity
AFTER 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.user_id = NEW.user_id
    ;
END;


-- Query to perform the actual search:
SELECT p.project_id
,      a.activity_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
ORDER BY time_start DESC
,        time_finish DESC
;