-- create database-schema
CREATE TABLE `employeelist2` (
  `eid` integer  NOT NULL PRIMARY KEY AUTOINCREMENT
,  `firstName` varchar(31) NOT NULL DEFAULT ''
,  `lastName` varchar(31) NOT NULL DEFAULT ''
,  `Email_id` varchar(31) NOT NULL DEFAULT ''
,  `Email2` varchar(31) DEFAULT NULL
,  `Email3` varchar(31) DEFAULT NULL
,  `EMail4` varchar(31) DEFAULT NULL
,  `folder` varchar(31) NOT NULL DEFAULT ''
,  `status` varchar(50) DEFAULT NULL
,  UNIQUE (`Email_id`)
);
CREATE TABLE `message2` (
  `mid` integer NOT NULL DEFAULT '0'
,  `sender` varchar(127) NOT NULL DEFAULT ''
,  `date` datetime DEFAULT NULL
,  `message_id` varchar(127) DEFAULT NULL
-- ,  `subject` text
-- ,  `body` text
,  `folder` varchar(127) NOT NULL DEFAULT ''
,  PRIMARY KEY (`mid`)
);
CREATE TABLE `recipientinfo2` (
  `rid` integer NOT NULL DEFAULT '0'
,  `mid` integer  NOT NULL DEFAULT '0'
,  `rtype` text  DEFAULT NULL
,  `rvalue` varchar(127) DEFAULT NULL
,  `dater` datetime DEFAULT NULL
,  PRIMARY KEY (`rid`)
,  FOREIGN KEY (mid) REFERENCES message2(mid)
);
CREATE TABLE `referenceinfo2` (
  `rfid` integer NOT NULL DEFAULT '0'
,  `mid` integer NOT NULL DEFAULT '0'
,  `reference` text
,  PRIMARY KEY (`rfid`)
,  FOREIGN KEY (mid) REFERENCES message2(mid)
);
CREATE INDEX "idx_message_sender2" ON "message2" (`sender`);
CREATE VIRTUAL TABLE messages_ft2 USING fts4(subject, body);

-- insert tables
-- employeelist2
-- message2
-- recipientinfo2
-- referenceinfo2
-- message_ft
SELECT 'employeelist', COUNT(*) FROM employeelist;
SELECT 'message', COUNT(*) FROM message;
SELECT 'recipientinfo', COUNT(*) FROM recipientinfo;
SELECT 'referenceinfo', COUNT(*) FROM referenceinfo;
SELECT 'messages_ft', COUNT(*) FROM messages_ft;

INSERT INTO message2 SELECT * FROM
    (
    SELECT
        message.mid,
        message.sender,
        message.date,
        message.message_id,
        -- message.subject,
        -- message.body,
        message.folder
    FROM
    (
    SELECT DISTINCT message.mid
    FROM message
    INNER JOIN recipientinfo ON recipientinfo.mid = message.mid
    INNER JOIN referenceinfo ON referenceinfo.mid = message.mid
    ) AS tmp1
    INNER JOIN message ON message.mid = tmp1.mid
    ORDER BY tmp1.mid
    LIMIT 10000
    ) AS tmp1;

INSERT INTO employeelist2 SELECT * FROM employeelist;
INSERT INTO recipientinfo2 SELECT
        tmp1.rid,
        tmp1.mid,
        tmp1.rtype,
        tmp1.rvalue,
        tmp1.dater
    FROM message2
    LEFT JOIN recipientinfo AS tmp1 ON tmp1.mid = message2.mid
    ORDER BY tmp1.rid;
INSERT INTO referenceinfo2 SELECT
        tmp1.rfid,
        tmp1.mid,
        tmp1.reference
    FROM message2
    LEFT JOIN referenceinfo AS tmp1 ON tmp1.mid = message2.mid
    ORDER BY tmp1.rfid;
INSERT INTO messages_ft2 (
    rowid, subject, body
)
SELECT
        tmp1.rowid, tmp1.subject, tmp1.body
    FROM message2
    INNER JOIN messages_ft AS tmp1 ON tmp1.rowid = message2.mid
    ORDER BY tmp1.rowid;
VACUUM;
SELECT 'employeelist2', COUNT(*) FROM employeelist2;
SELECT 'message2', COUNT(*) FROM message2;
SELECT 'recipientinfo2', COUNT(*) FROM recipientinfo2;
SELECT 'referenceinfo2', COUNT(*) FROM referenceinfo2;
SELECT 'messages_ft2', COUNT(*) FROM messages_ft2;
--*/

SELECT * FROM messages_ft2
INNER JOIN recipientinfo2 ON recipientinfo2.mid = messages_ft2.rowid
INNER JOIN referenceinfo2 ON referenceinfo2.mid = messages_ft2.rowid
WHERE body MATCH 'energy NEAR oil';