-- SPDX-License-Identifier: Apache-2.0

PRAGMA foreign_keys = ON;

CREATE TABLE encryptionkey (
       id INTEGER PRIMARY KEY,
       name text NOT NULL, -- Options: rc4, xor, aes128
       bytes text NOT NULL UNIQUE, -- hex bytes
       CHECK (name in ('aes128', 'xor', 'rc4'))
);

CREATE TABLE filetype (
      id INTEGER PRIMARY KEY,
      name text NOT NULL UNIQUE,
      description text,
      magic text NOT NULL UNIQUE,
      executable INTEGER
);

INSERT INTO filetype(name,description,magic,executable) VALUES
('Unknown', 'Files of known types', '', NULL),
('PE32', '.EXEs and other Windows executables', '4D5A|5A4D', true),
('ELF', 'Executable and Linkable Format, used for applications and libraries on Linux, BSD, Solaris, Haiku, and other OSes', '7f454c46', true),
('Mach-O', 'mac OS executable format for applications, frameworks, and libraries', 'FEEDFACE|FEEDFACF|CEFAEDFE|CFFAEDFE', true),
('Fat Mach-O', 'mac OS executable format for applications, frameworks, and libraries', 'CAFEBABE', true),
('PDF', 'Adobe Portable Document Format (PDF)', '25504446', false),
('Office97', 'Microsoft Office 97 and other types (Compound Document Format)', 'D0CF11E0', false),
('RTF', 'Rich Text Format (RTF)', '7b5c7274', false);

CREATE TABLE file (
    id INTEGER PRIMARY KEY,
    sha1 text NOT NULL,
    sha256 text NOT NULL,
    sha384 text NOT NULL,
    sha512 text NOT NULL UNIQUE,
    md5 text NOT NULL,
    lzjd text,
    ssdeep text,
    tlsh text,
    humanhash text,
    filecommand text,
    createddate timestamp, -- If the file knows when it was compiled
    addeddate timestamp with time zone NOT NULL DEFAULT current_timestamp, -- When the file was added to MalwareDB
    filetypeid integer NOT NULL REFERENCES filetype(id),
    size bigint NOT NULL,
    entropy real NOT NULL,
    confirmedmalicious integer, -- boolean
    nonce text, -- hex bytes
    key int REFERENCES encryptionkey(id),
    parent int REFERENCES file(id)
);


CREATE TABLE executable (
    fileid INTEGER PRIMARY KEY,
    architecture text,
    operatingsystem text,
    pehash text,
    importhash text,
    importhashfuzzy text,
    packed INTEGER, -- boolean
    sections INTEGER NOT NULL,
    sectionnames text,
    sectionentropies text,
    sectionexec text,
    FOREIGN KEY (fileid) REFERENCES file(id)
);


CREATE TABLE pdf (
    fileid INTEGER UNIQUE,
    title text,
    author text,
    pages INTEGER,
    streams INTEGER,
    javascript INTEGER,
    openAction INTEGER,
    forms INTEGER,
    largeColourSpace INTEGER,
    embeddedFiles INTEGER,
    PRIMARY KEY (fileid),
    FOREIGN KEY (fileid) REFERENCES file(id)
);


CREATE TABLE label (
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE,
    parent INTEGER,
    FOREIGN KEY (parent) REFERENCES label(id)
);


CREATE TABLE source (
    id INTEGER PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    url text,
    firstacquisition DEFAULT CURRENT_TIMESTAMP NOT NULL,
    releasable int NOT NULL,
    malicious int,
    parent INTEGER,
    FOREIGN KEY (parent) REFERENCES source(id)
);


CREATE TABLE filelabel (
    fileid INTEGER NOT NULL,
    labelid INTEGER NOT NULL,
    added DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (fileid, labelid),
    FOREIGN KEY (fileid) REFERENCES file(id),
    FOREIGN KEY (labelid) REFERENCES label(id)
);


CREATE TABLE sourcelabel (
    sourceid INTEGER NOT NULL REFERENCES source(id),
    labelid INTEGER NOT NULL REFERENCES label(id),
    added DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (sourceid, labelid),
    FOREIGN KEY (sourceid) REFERENCES source(id),
    FOREIGN KEY (labelid) REFERENCES label(id)
);


CREATE TABLE grp (
    id INTEGER PRIMARY KEY,
    name text NOT NULL UNIQUE,
    description text,
    parent INTEGER,
    FOREIGN KEY (parent) REFERENCES grp(id)
);


CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    email text NOT NULL UNIQUE,
    uname text NOT NULL UNIQUE,
    firstname text NOT NULL,
    lastname text NOT NULL,
    organisation text,
    phone text,
    password text,
    apikey text UNIQUE,
    created DEFAULT CURRENT_TIMESTAMP NOT NULL,
    readonly boolean DEFAULT FALSE NOT NULL
);


CREATE TABLE filesource (
    fileid INTEGER NOT NULL,
    sourceid INTEGER NOT NULL,
    userid INTEGER NOT NULL,
    filename text,
    firstseen DEFAULT CURRENT_TIMESTAMP NOT NULL, -- When MalwareDB first saw this file from this source
    dataset_firstseen timestamp with time zone, -- If available, when the file origin first saw this file
    PRIMARY KEY (fileid, sourceid),
    FOREIGN KEY (userid) REFERENCES person(id),
    FOREIGN KEY (fileid) REFERENCES file(id),
    FOREIGN KEY (sourceid) REFERENCES source(id)
);


CREATE TABLE mdbconfig (
    version text NOT NULL,
    name text NOT NULL,
    compress bool NOT NULL default FALSE,
    send_samples_to_vt bool NOT NULL default FALSE,
    keep_unknown_files bool NOT NULL default FALSE,
    defaultFileSource INTEGER,
    defaultKey INTEGER,
    anonymous_user_id INTEGER,
    FOREIGN KEY (defaultFileSource) REFERENCES source(id),
    FOREIGN KEY (defaultKey) REFERENCES encryptionkey(id),
    FOREIGN KEY (anonymous_user_id) REFERENCES person(id)
);

CREATE UNIQUE INDEX mdbconfig_one_row_index ON mdbconfig (( true ));
INSERT INTO mdbconfig(version, name) VALUES('0', 'MalwareDB');


CREATE TABLE pagination (
    id text NOT NULL,  -- Unique identifier for this paginated search
    type text NOT NULL, -- Plain or similarity hash-based search
    query text NOT NULL, -- JSON serialized form of the query structure
    userid INTEGER NOT NULL REFERENCES person(id), -- User who initiated the search
    next_fileid INTEGER NOT NULL REFERENCES file(id), -- File ID of the next file to return
    total_results integer NOT NULL,  -- Total results expected for this search
    created timestamp with time zone NOT NULL DEFAULT current_timestamp, -- When the search was initiated so it can be deleted
    PRIMARY KEY (id),
    CHECK (type in ('search', 'similaritysearch'))
);

CREATE TABLE yara_search (
     id text NOT NULL,  -- UUID Unique identifier for this paginated search
     yara_text text NOT NULL,
     yara_compiled text, -- Bytes as hex
     userid INTEGER NOT NULL REFERENCES person(id), -- User who initiated the search
     results text, -- Yara name|Hash
     last_fileid INTEGER REFERENCES file(id), -- File ID of the last file in the search of allowed files
     completed timestamp with time zone,
     created timestamp with time zone NOT NULL DEFAULT current_timestamp, -- When the search was initiated so it can be deleted
     PRIMARY KEY (id)
);

INSERT INTO PERSON VALUES(0, 'admin@example.com', 'admin', 'ADMIN', 'USER', NULL, NULL, NULL, NULL, CURRENT_TIMESTAMP, false);

CREATE TABLE polyglot (
    fileid integer NOT NULL,
    filetypeid integer NOT NULL,
    explanation text,
    PRIMARY KEY (fileid, filetypeid),
    FOREIGN KEY (fileid) REFERENCES file(id),
    FOREIGN KEY (filetypeid) REFERENCES filetype(id)
);

CREATE TABLE groupsource (
     gid integer NOT NULL,
     sourceid int NOT NULL,
     added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
     PRIMARY KEY(gid, sourceid),
     FOREIGN KEY (gid) REFERENCES grp(id),
     FOREIGN KEY (sourceid) REFERENCES source(id)
);

CREATE TABLE usergroup (
    pid integer NOT NULL,
    gid integer NOT NULL,
    added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (pid, gid),
    FOREIGN KEY (pid) REFERENCES person(id),
    FOREIGN KEY (gid) REFERENCES grp(id)
);

INSERT INTO grp(id, name, description) VALUES(0, 'admin', 'Administrative users');
INSERT INTO usergroup VALUES(0, 0, CURRENT_TIMESTAMP);


CREATE TABLE vtdata (
    fileid integer NOT NULL,
    tstamp text NOT NULL,
    hits integer NOT NULL,
    total integer NOT NULL,
    vtdetail text, -- JSON
    PRIMARY KEY (fileid, tstamp),
    FOREIGN KEY (fileid) REFERENCES file(id)
);
