CREATE TABLE Attachments (id INTEGER PRIMARY KEY,event_id INTEGER NOT NULL,path TEXT NOT NULL,desc TEXT);
CREATE TABLE EventTypes (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE,plugin_id INTEGER,desc TEXT);
CREATE TABLE Events (id INTEGER PRIMARY KEY,service_id INTEGER NOT NULL,event_type_id INTEGER NOT NULL,storage_time INTEGER NOT NULL,start_time INTEGER NOT NULL,end_time INTEGER,is_read INTEGER DEFAULT 0,outgoing BOOL DEFAULT 0,flags INTEGER DEFAULT 0,bytes_sent INTEGER DEFAULT 0,bytes_received INTEGER DEFAULT 0,local_uid TEXT,local_name TEXT,remote_uid TEXT,channel TEXT,free_text TEXT,group_uid TEXT);
CREATE TABLE Flags (id INTEGER PRIMARY KEY,service_id INTEGER NOT NULL,name TEXT NOT NULL UNIQUE,value INTEGER NOT NULL,desc TEXT);
CREATE TABLE GroupCache (event_id INTEGER UNIQUE NOT NULL,service_id INTEGER NOT NULL,group_uid TEXT NOT NULL,total_events INTEGER DEFAULT 0,read_events INTEGER DEFAULT 0,flags INTEGER DEFAULT 0,CONSTRAINT factor UNIQUE(service_id, group_uid));
CREATE TABLE Headers (id INTEGER PRIMARY KEY,event_id INTEGER NOT NULL,name TEXT NOT NULL,value TEXT NOT NULL,UNIQUE(event_id, name));
CREATE TABLE Plugins (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE,desc TEXT NOT NULL);
CREATE TABLE Remotes (local_uid TEXT NOT NULL,remote_uid TEXT NOT NULL,remote_name TEXT,abook_uid TEXT,UNIQUE(local_uid,remote_uid));
CREATE TABLE Services (id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE,plugin_id INTEGER,desc TEXT);
CREATE TABLE call_duration (inbound_gsm INTEGER CHECK(inbound_gsm >= 0) DEFAULT 0, outbound_gsm INTEGER CHECK(outbound_gsm >= 0) DEFAULT 0, inbound_voip INTEGER CHECK(inbound_voip >= 0) DEFAULT 0, outbound_voip INTEGER CHECK(outbound_voip >= 0) DEFAULT 0);
CREATE TABLE chat_group_info (group_uid TEXT UNIQUE NOT NULL, group_title TEXT);
CREATE INDEX idx_att_event_id ON Attachments(event_id);
CREATE INDEX idx_cgi_group_uid ON chat_group_info(group_uid);
CREATE INDEX idx_et_plugin_id ON EventTypes(plugin_id);
CREATE INDEX idx_ev_event_type_id ON Events(event_type_id);
CREATE INDEX idx_ev_group_uid ON Events(group_uid);
CREATE INDEX idx_ev_remote_uid ON Events(remote_uid);
CREATE INDEX idx_ev_service_id ON Events(service_id);
CREATE INDEX idx_gc_group_uid ON GroupCache(group_uid);
CREATE INDEX idx_hdr_event_id ON Headers(event_id);
CREATE INDEX idx_srv_plugin_id ON Services(plugin_id);
CREATE TRIGGER fkd_flags_service_id BEFORE DELETE ON Services FOR EACH ROW BEGIN DELETE FROM Flags WHERE service_id = OLD.id; END;
CREATE TRIGGER fkd_headers_atts_event_id BEFORE DELETE ON Events FOR EACH ROW BEGIN DELETE FROM Headers WHERE event_id = OLD.id; DELETE FROM Attachments WHERE event_id = OLD.id; END;
CREATE TRIGGER fkd_services_event_types_plugin_id BEFORE DELETE ON Plugins FOR EACH ROW BEGIN DELETE FROM Services WHERE plugin_id = OLD.id; DELETE FROM EventTypes WHERE plugin_id = OLD.id; END;
CREATE TRIGGER gc_update_ev_add1 BEFORE INSERT ON Events FOR EACH ROW WHEN NEW.group_uid IS NOT NULL BEGIN INSERT OR IGNORE INTO GroupCache (event_id, service_id, group_uid, total_events, read_events, flags) VALUES (0, NEW.service_id, NEW.group_uid, 0, 0, 0); END;
CREATE TRIGGER gc_update_ev_add4 AFTER INSERT ON Events FOR EACH ROW WHEN NEW.group_uid IS NOT NULL BEGIN UPDATE GroupCache SET event_id = NEW.id, total_events = total_events + 1, read_events = read_events + NEW.is_read, flags = flags | NEW.flags WHERE group_uid = NEW.group_uid; END;
CREATE TRIGGER gc_update_ev_update AFTER UPDATE ON Events FOR EACH ROW WHEN NEW.group_uid IS NOT NULL BEGIN UPDATE GroupCache SET read_events = read_events - OLD.is_read + NEW.is_read, flags = (flags & (~OLD.flags)) | NEW.flags WHERE group_uid = NEW.group_uid; END;
