/*** Define table schema ***/

/* Crawler Tables */
CREATE TABLE IF NOT EXISTS task (
    task_id INTEGER PRIMARY KEY,
    start_time TIMESTAMPTZ DEFAULT (now() at time zone 'utc'),
    manager_params TEXT NOT NULL,
    openwpm_version TEXT NOT NULL,
    browser_version TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS crawl (
    id INTEGER PRIMARY KEY,
    task_id SMALLINT,
    start_time TIMESTAMPTZ DEFAULT (now() at time zone 'utc'),
    browser_params TEXT NOT NULL,
    screen_res TEXT,
    ua_string TEXT,
    finished SMALLINT,
    FOREIGN KEY(task_id) REFERENCES task(task_id)
);

CREATE TABLE IF NOT EXISTS crawl_history (
    crawl_id SMALLINT,
    command TEXT,
    arguments TEXT,
    success SMALLINT,
    dtg TIMESTAMPTZ DEFAULT (now() at time zone 'utc'),
    FOREIGN KEY(crawl_id) REFERENCES crawl(id)
);

CREATE TABLE IF NOT EXISTS site_visits (
    id INTEGER PRIMARY KEY,
    crawl_id SMALLINT NOT NULL,
    top_url TEXT NOT NULL
);

/* HTTP Data */
CREATE TABLE IF NOT EXISTS urls (
    id INTEGER PRIMARY KEY,
    public_suffix TEXT,
    url TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS http_requests (
    id INTEGER PRIMARY KEY,
    visit_id INTEGER NOT NULL,
    url_id INTEGER NOT NULL,
    method TEXT NOT NULL,
    referrer_id INTEGER,
    time_stamp TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS http_responses (
    id INTEGER PRIMARY KEY,
    visit_id INTEGER NOT NULL,
    url_id INTEGER NOT NULL,
    method TEXT NOT NULL,
    referrer_id INTEGER,
    response_status SMALLINT NOT NULL,
    location_id INTEGER,
    time_stamp TEXT NOT NULL,
    content_hash TEXT
);

/* HTTP Headers */

CREATE TABLE IF NOT EXISTS http_header_names (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS http_header_values (
    id INTEGER PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS http_request_headers (
    id INTEGER PRIMARY KEY,
    request_id INTEGER NOT NULL,
    name_id INTEGER NOT NULL,
    value_id INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS http_response_headers (
    id INTEGER PRIMARY KEY,
    response_id INTEGER NOT NULL,
    name_id INTEGER NOT NULL,
    value_id INTEGER NOT NULL
);

/* HTTP Cookies */

CREATE TABLE IF NOT EXISTS http_cookie_names (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS http_cookie_values (
    id INTEGER PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS http_request_cookies (
    id INTEGER PRIMARY KEY,
    request_id INTEGER NOT NULL,
    name_id INTEGER NOT NULL,
    value_id INTEGER NOT NULL,
    accessed TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS http_response_cookies (
    id INTEGER PRIMARY KEY,
    response_id INTEGER NOT NULL,
    name_id INTEGER NOT NULL,
    value_id INTEGER NOT NULL,
    domain TEXT,
    path TEXT,
    expires TIMESTAMPTZ,
    max_age REAL,
    httponly BOOLEAN,
    secure BOOLEAN,
    comment TEXT,
    version TEXT,
    accessed TIMESTAMPTZ
);

/* Javascript Instrumentation */
CREATE TABLE IF NOT EXISTS javascript_values (
    id INTEGER PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS javascript_symbols (
    id INTEGER PRIMARY KEY,
    symbol TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS javascript_parameters (
    id INTEGER PRIMARY KEY,
    pindex INTEGER NOT NULL,
    pvalue TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS javascript (
    id INTEGER PRIMARY KEY,
    visit_id INTEGER NOT NULL,
    url_id INTEGER,
    symbol_id INTEGER NOT NULL,
    operation TEXT NOT NULL,
    value_id INTEGER,
    parameter_id INTEGER
);

/* Disk Instrumentation */

CREATE TABLE IF NOT EXISTS flash_cookie_content (
    id INTEGER PRIMARY KEY,
    key TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS flash_cookie_path (
    id INTEGER PRIMARY KEY,
    filename TEXT NOT NULL,
    local_path TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS flash_cookies (
    id INTEGER PRIMARY KEY,
    visit_id INTEGER NOT NULL,
    domain TEXT NOT NULL,
    path_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL
);


CREATE TABLE IF NOT EXISTS javascript_cookie_values (
    id INTEGER PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS javascript_cookie_names (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS javascript_cookies(
    id INTEGER PRIMARY KEY,
    visit_id INTEGER NOT NULL,
    host TEXT,
    raw_host TEXT,
    change TEXT,
    name_id INTEGER NOT NULL,
    value_id INTEGER NOT NULL,
    path TEXT,
    expires BIGINT,
    is_http_only BOOLEAN,
    is_session BOOLEAN,
    is_domain BOOLEAN,
    is_secure BOOLEAN,
    policy INTEGER,
    status INTEGER,
    creationTime TIMESTAMPTZ,
    expiry TIMESTAMPTZ,
    last_accessed TIMESTAMPTZ
);

/*** Creates and validates all foreign keys ****/

/* HTTP Requests */
ALTER TABLE http_requests ADD CONSTRAINT requests_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id);
ALTER TABLE http_requests ADD CONSTRAINT requests_urls_fk FOREIGN KEY(url_id) REFERENCES urls(id);
ALTER TABLE http_requests ADD CONSTRAINT requests_refs_fk FOREIGN KEY(referrer_id) REFERENCES urls(id);
ALTER TABLE http_requests VALIDATE CONSTRAINT requests_visit_fk;
ALTER TABLE http_requests VALIDATE CONSTRAINT requests_urls_fk;
ALTER TABLE http_requests VALIDATE CONSTRAINT requests_refs_fk;

/* HTTP Responses */
ALTER TABLE http_responses ADD CONSTRAINT responses_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id);
ALTER TABLE http_responses ADD CONSTRAINT responses_urls_fk FOREIGN KEY(url_id) REFERENCES urls(id);
ALTER TABLE http_responses ADD CONSTRAINT responses_locs_fk FOREIGN KEY(location_id) REFERENCES urls(id);
ALTER TABLE http_responses VALIDATE CONSTRAINT responses_visit_fk;
ALTER TABLE http_responses VALIDATE CONSTRAINT responses_urls_fk;
ALTER TABLE http_responses VALIDATE CONSTRAINT responses_locs_fk;

/* HTTP Request Headers */
ALTER TABLE http_request_headers ADD CONSTRAINT reqh_req_fk FOREIGN KEY(request_id) REFERENCES http_requests(id);
ALTER TABLE http_request_headers ADD CONSTRAINT reqh_name_fk FOREIGN KEY(name_id) REFERENCES http_header_names(id);
ALTER TABLE http_request_headers ADD CONSTRAINT reqh_value_fk FOREIGN KEY(value_id) REFERENCES http_header_values(id);
ALTER TABLE http_request_headers VALIDATE CONSTRAINT reqh_req_fk;
ALTER TABLE http_request_headers VALIDATE CONSTRAINT reqh_name_fk;
ALTER TABLE http_request_headers VALIDATE CONSTRAINT reqh_value_fk;

/* HTTP Response Headers */
ALTER TABLE http_response_headers ADD CONSTRAINT resph_resp_fk FOREIGN KEY(response_id) REFERENCES http_responses(id);
ALTER TABLE http_response_headers ADD CONSTRAINT resph_name_fk FOREIGN KEY(name_id) REFERENCES http_header_names(id);
ALTER TABLE http_response_headers ADD CONSTRAINT resph_value_fk FOREIGN KEY(value_id) REFERENCES http_header_values(id);
ALTER TABLE http_response_headers VALIDATE CONSTRAINT resph_resp_fk;
ALTER TABLE http_response_headers VALIDATE CONSTRAINT resph_name_fk;
ALTER TABLE http_response_headers VALIDATE CONSTRAINT resph_value_fk;

/* HTTP Request Cookies */
ALTER TABLE http_request_cookies ADD CONSTRAINT reqc_header_fk FOREIGN KEY(request_id) REFERENCES http_requests(id);
ALTER TABLE http_request_cookies ADD CONSTRAINT reqc_name_fk FOREIGN KEY(name_id) REFERENCES http_cookie_names(id);
ALTER TABLE http_request_cookies ADD CONSTRAINT reqc_value_fk FOREIGN KEY(value_id) REFERENCES http_cookie_values(id);
ALTER TABLE http_request_cookies VALIDATE CONSTRAINT reqc_header_fk;
ALTER TABLE http_request_cookies VALIDATE CONSTRAINT reqc_name_fk;
ALTER TABLE http_request_cookies VALIDATE CONSTRAINT reqc_value_fk;

/* HTTP Response Cookies */
ALTER TABLE http_response_cookies ADD CONSTRAINT respc_header_fk FOREIGN KEY(response_id) REFERENCES http_requests(id);
ALTER TABLE http_response_cookies ADD CONSTRAINT respc_name_fk FOREIGN KEY(name_id) REFERENCES http_cookie_names(id);
ALTER TABLE http_response_cookies ADD CONSTRAINT respc_value_fk FOREIGN KEY(value_id) REFERENCES http_cookie_values(id);
ALTER TABLE http_response_cookies VALIDATE CONSTRAINT respc_header_fk;
ALTER TABLE http_response_cookies VALIDATE CONSTRAINT respc_name_fk;
ALTER TABLE http_response_cookies VALIDATE CONSTRAINT respc_value_fk;

/* Javascript */
ALTER TABLE javascript ADD CONSTRAINT javascript_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id);
ALTER TABLE javascript ADD CONSTRAINT javascript_url_fk FOREIGN KEY(url_id) REFERENCES urls(id);
ALTER TABLE javascript ADD CONSTRAINT javascript_symbol_fk FOREIGN KEY(symbol_id) REFERENCES javascript_symbols(id);
ALTER TABLE javascript ADD CONSTRAINT javascript_value_fk FOREIGN KEY(value_id) REFERENCES javascript_values(id);
ALTER TABLE javascript ADD CONSTRAINT javascript_parameter_fk FOREIGN KEY(parameter_id) REFERENCES javascript_parameters(id);
ALTER TABLE javascript VALIDATE CONSTRAINT javascript_visit_fk;
ALTER TABLE javascript VALIDATE CONSTRAINT javascript_url_fk;
ALTER TABLE javascript VALIDATE CONSTRAINT javascript_symbol_fk;
ALTER TABLE javascript VALIDATE CONSTRAINT javascript_value_fk;
ALTER TABLE javascript VALIDATE CONSTRAINT javascript_parameter_fk;

/* Flash Cookies */
ALTER TABLE flash_cookies ADD CONSTRAINT flash_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id);
ALTER TABLE flash_cookies ADD CONSTRAINT flash_path_fk FOREIGN KEY(path_id) REFERENCES flash_cookie_path(id);
ALTER TABLE flash_cookies ADD CONSTRAINT flash_content_fk FOREIGN KEY(content_id) REFERENCES flash_cookie_content(id);
ALTER TABLE flash_cookies VALIDATE CONSTRAINT flash_visit_fk;
ALTER TABLE flash_cookies VALIDATE CONSTRAINT flash_path_fk;
ALTER TABLE flash_cookies VALIDATE CONSTRAINT flash_content_fk;

/* Javascript Cookies */
ALTER TABLE javascript_cookies ADD CONSTRAINT jscookies_visit_fk FOREIGN KEY(visit_id) REFERENCES site_visits(id);
ALTER TABLE javascript_cookies ADD CONSTRAINT jscookies_name_fk FOREIGN KEY(name_id) REFERENCES javascript_cookie_names(id);
ALTER TABLE javascript_cookies ADD CONSTRAINT jscookies_value_fk FOREIGN KEY(value_id) REFERENCES javascript_cookie_values(id);
ALTER TABLE javascript_cookies VALIDATE CONSTRAINT jscookies_visit_fk;
ALTER TABLE javascript_cookies VALIDATE CONSTRAINT jscookies_name_fk;
ALTER TABLE javascript_cookies VALIDATE CONSTRAINT jscookies_value_fk;

/*** Creates indexes on all necessary columns ***/

/* HTTP Requests */
CREATE INDEX requests_visit_id ON http_requests(visit_id);
CREATE INDEX requests_url_id ON http_requests(url_id);
CREATE INDEX requests_referrer_id ON http_requests(referrer_id);

/* HTTP Request Headers */
CREATE INDEX request_headers_request_id ON http_request_headers(request_id);
CREATE INDEX request_headers_name_id ON http_request_headers(name_id);
CREATE INDEX request_headers_value_id ON http_request_headers(value_id);

/* HTTP Request Cookies */
CREATE INDEX request_cookies_header_id ON http_request_cookies(request_id);
CREATE INDEX request_cookies_name_id ON http_request_cookies(name_id);
CREATE INDEX request_cookies_value_id ON http_request_cookies(value_id);

/* HTTP Responses */
CREATE INDEX responses_visit_id ON http_responses(visit_id);
CREATE INDEX responses_url_id ON http_responses(url_id);
CREATE INDEX responses_referrer_id ON http_responses(referrer_id);
CREATE INDEX responses_location_id ON http_responses(location_id);

/* HTTP Response Headers */ 
CREATE INDEX response_headers_request_id ON http_response_headers(response_id);
CREATE INDEX response_headers_name_id ON http_response_headers(name_id);
CREATE INDEX response_headers_value_id ON http_response_headers(value_id);

/* HTTP Response Cookies */
CREATE INDEX response_cookies_header_id ON http_response_cookies(response_id);
CREATE INDEX response_cookies_name_id ON http_response_cookies(name_id);
CREATE INDEX response_cookies_value_id ON http_response_cookies(value_id);

/* Javascript */
CREATE INDEX javascript_visit_id ON javascript(visit_id);
CREATE INDEX javascript_url_id ON javascript(url_id);
CREATE INDEX javascript_symbol_id ON javascript(symbol_id);
CREATE INDEX javascript_value_id ON javascript(value_id);
CREATE INDEX javascript_parameter_id ON javascript(parameter_id);

/* Javascript Cookies */
CREATE INDEX flash_cookies_visit_id ON flash_cookies(visit_id);
CREATE INDEX flash_cookies_path_id ON flash_cookies(path_id);
CREATE INDEX flash_cookies_content_id ON flash_cookies(content_id);

/* Flash Cookies */
CREATE INDEX javascript_cookies_visit_id ON javascript_cookies(visit_id);
CREATE INDEX javascript_cookies_name_id ON javascript_cookies(name_id);
CREATE INDEX javascript_cookies_value_id ON javascript_cookies(value_id);

/*** Create views to make tables easier to query 

Note: These views should be similar to OpenWPM's sqlite schema
      but may not always match exactly.
***/

CREATE OR REPLACE VIEW
    javascript_view (id, top_url, script_url, symbol, value, pindex, pvalue) AS
    SELECT j.id, sv.top_url, u.url, s.symbol, v.value, p.pindex, p.pvalue
    FROM
        javascript AS j
        LEFT JOIN
            javascript_values as v
            ON v.id = j.value_id
        LEFT JOIN
            javascript_symbols as s
            ON s.id = j.symbol_id
        LEFT JOIN
            javascript_parameters as p
            ON p.id = j.parameter_id
        LEFT JOIN
            site_visits as sv
            ON sv.id = j.visit_id
        LEFT JOIN
            urls as u
            ON u.id = j.url_id;

CREATE OR REPLACE VIEW
    http_requests_view (id, top_url, url, method, referrer, time_stamp) AS
    SELECT r.id, sv.top_url, u1.url, r.method, u2.url, r.time_stamp
    FROM
        http_requests as r
        LEFT JOIN
            urls as u1
            ON r.url_id = u1.id
        LEFT JOIN
            urls as u2
            ON r.referrer_id = u2.id
        LEFT JOIN
            site_visits as sv
            ON r.visit_id = sv.id;

CREATE OR REPLACE VIEW
    http_request_headers_view (id, request_id, name, value) AS
    SELECT h.id, h.request_id, n.name, v.value
    FROM
        http_request_headers as h
        LEFT JOIN
            http_header_names as n
            ON h.name_id = n.id
        LEFT JOIN
            http_header_values as v
            ON h.value_id = v.id;

CREATE OR REPLACE VIEW
    http_responses_view (id, top_url, url, method, referrer,
                        response_status, location, time_stamp,
                        content_hash) AS
    SELECT r.id, sv.top_url, u1.url, r.method, u2.url,
           r.response_status, u3.url, r.time_stamp,
           r.content_hash
    FROM
        http_responses as r
        LEFT JOIN
            urls as u1
            ON r.url_id = u1.id
        LEFT JOIN
            urls as u2
            ON r.referrer_id = u2.id
        LEFT JOIN
            urls as u3
            ON r.location_id = u3.id
        LEFT JOIN
            site_visits as sv
            ON r.visit_id = sv.id;

CREATE OR REPLACE VIEW
    http_response_headers_view (id, response_id, name, value) AS
    SELECT h.id, h.response_id, n.name, v.value
    FROM
        http_response_headers as h
        LEFT JOIN
            http_header_names as n
            ON h.name_id = n.id
        LEFT JOIN
            http_header_values as v
            ON h.value_id = v.id;

CREATE OR REPLACE VIEW
    http_request_cookies_view (id, request_id, name, value, accessed) AS
    SELECT rc.id, rc.request_id, n.name, v.value, rc.accessed
    FROM
        http_request_cookies AS rc
        LEFT JOIN
            http_cookie_names AS n
            ON n.id = rc.name_id
        LEFT JOIN
            http_cookie_values AS v
            ON v.id = rc.value_id;

CREATE OR REPLACE VIEW
    http_response_cookies_view (id, response_id, name, value,
                                domain, path, expires, max_age,
                                httponly, secure, comment, version,
                                accessed) AS
    SELECT rc.id, rc.response_id, n.name, v.value, rc.domain,
            rc.path, rc.expires, rc.max_age, rc.httponly,
            rc.secure, rc.comment, rc.version, rc.accessed
    FROM
        http_response_cookies AS rc
        LEFT JOIN
            http_cookie_names AS n
            ON n.id = rc.name_id
        LEFT JOIN
            http_cookie_values AS v
            ON v.id = rc.value_id;
