CREATE DATABASE truview;

CONNECT truview;

CREATE TABLE company (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(100)
  -- add any other attribute you may find useful, e.g. for billing purposes...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id bigint unsigned, -- optional, you may need "unaffiliated" users
  login varchar(100),
  password varchar(100),
  -- add any other attribute you may find useful for users...
  FOREIGN KEY (company_id) REFERENCES company(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE site (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id bigint unsigned NOT NULL, 
  base_url varchar(1000), -- any hit.document_url whose leftmost part
                          -- matches this base_url belongs to this site 
  
  -- add any other attribute you may find useful for websites...
  FOREIGN KEY (company_id) REFERENCES company(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table visitor (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cookie varchar(255)
  -- add any other attribute you may find useful for client site users AKA visitors
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE script (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pattern text, -- either a host, a glob expression or a regular expression
  inline bool, -- whether the pattern matches a URL or inline code
  name varchar(200) NOT NULL DEFAULT '???', -- to be modified by a human reviewer
  description text, -- to be entered by human reviewer
  classification enum('unidentified', 'malicious', 'safe', 'not sure') NOT NULL DEFAULT 'unidentified',
  priority bigint unsigned NOT NULL DEFAULT 0, -- matching order
  ts timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  reviewer_id bigint unsigned,
  FOREIGN KEY (reviewer_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table site_script (
  site_id bigint unsigned NOT NULL,
  script_id bigint unsigned NOT NULL,
  -- site-specific script classification, overrides the one provided by the "script" table
  classification enum('unidentified', 'malicious', 'safe', 'not sure') NOT NULL DEFAULT 'unidentified',
  ts timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  note text,
  reviewer_id bigint unsigned,
  PRIMARY KEY(site_id, script_id),
  FOREIGN KEY (site_id) REFERENCES site(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (script_id) REFERENCES script(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (reviewer_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table browser (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table os (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table geo (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code varchar(8)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table lang (
  id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code varchar(8)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table session (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cookie varchar(255),
  client_ip bigint, -- to be extracted from the REMOTE_ADDR CGI environment
  user_agent text, -- to be extracted from the CGI environment
  site_id bigint unsigned NOT NULL, -- see site.base_url
  
  -- the following "xyz_id" columns are references to "xyz" lookup tables which
  -- must populated on the fly if the value to be stored cannot be found there yet
  visitor_id bigint unsigned NOT NULL, -- match per-visitor unique cookie sent by the client script,
                                       -- reference or insert in table "visitor"
  browser_id int unsigned NOT NULL, -- as sent by the client script (extrapolated from navigator props),
                           -- reference or insert in table "browser"
  os_id int unsigned NOT NULL,  -- as sent by the client script (navigator.platform),
                       -- reference or insert in table "os"
  geo_id int unsigned NOT NULL, -- must be computed on the server side from the client IP, possibly lazily,
                       -- reference or insert in table "geo"
  lang_id int unsigned NOT NULL,  -- as sent by the client script (navigator.language),
                         -- reference or insert in table "lang"
  mobile bool NOT NULL default 0, -- sent by the client script
  FOREIGN KEY (visitor_id) REFERENCES visitor(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (site_id) REFERENCES site(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (browser_id) REFERENCES browser(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (os_id) REFERENCES os(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (geo_id) REFERENCES geo(id) ON UPDATE CASCADE ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table document ( -- known site document, used to log hits
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  url text, -- top URL of this document
  UNIQUE KEY(document_url)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table hit ( -- page view, logged asynchronously after page load along with any script instance found
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  session_id bigint unsigned NOT NULL,
  document_id bigint unsigned NOT NULL -- reference to the document generating this hit
  load_time int, -- page load time in milliseconds
  conversion bool, -- whether this is a conversion trigger as defined by a client-side parameter
  logged_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY(conversion),
  FOREIGN KEY (session_id) REFERENCES session(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (document_id) REFERENCES document(id) ON UPDATE CASCADE ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE script_instance (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  script_id bigint unsigned, -- instances with a null script_id get lazily tested against script.pattern rows
    -- in priority order and reassigned to the first match. If no matching pattern is found, a new one gets
    -- created by using the full URL until the query part (excluded)
  src text, -- may be URL or inline code, depending on the inline flag
  inline bool,
  server_ip bigint,
  key(src),
  key(inline),
  FOREIGN KEY (script_id) REFERENCES script(id) ON UPDATE CASCADE ON DELETE SET NULL,
  FOREIGN KEY (hit_id) REFERENCES hit(id) ON UPDATE CASCADE ON DELETE CASCADE
)  ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table script_hit (
  script_instance_id bigint unsigned NOT NULL,
  hit_id bigint unsigned NOT NULL,
  FOREIGN KEY (script_instance_id) REFERENCES script_instance(id) ON UPDATE CASCADE ON DELETE SET NULL,
  FOREIGN KEY (hit_id) REFERENCES hit(id) ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY(script_instance_id, hit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
