-- SPDX-License-Identifier: Apache-2.0

CREATE TYPE encryptionkey_algorithm AS ENUM('rc4', 'xor', 'aes128');
CREATE TYPE search_pagination_type AS ENUM('search', 'similaritysearch');

CREATE TABLE encryptionkey (
    id serial NOT NULL,
    name encryptionkey_algorithm NOT NULL,
    bytes bytea NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE filetype (
    id serial NOT NULL,
    name text NOT NULL UNIQUE,
    description text,
    magic bytea[] NOT NULL UNIQUE,
    executable boolean,
    PRIMARY KEY (id)
);


INSERT INTO filetype(name,description,magic,executable) VALUES
('Unknown', 'Files of known types', ARRAY[E''::bytea], NULL),
('PE32', '.EXEs and other Windows executables', ARRAY[E'\\x4d5a'::bytea, E'\\x5a4d'::bytea], true),
('ELF', 'Executable and Linkable Format, used for applications and libraries on Linux, BSD, Solaris, Haiku, and other OSes', ARRAY[E'\\x7f454c46'::bytea], true),
('Mach-O', 'mac OS executable format for applications, frameworks, and libraries', ARRAY[E'\\xFEEDFACE'::bytea, E'\\xFEEDFACF', E'\\xCEFAEDFE'::bytea, E'\\xCFFAEDFE'::bytea], true),
('Fat Mach-O', 'mac OS executable format for applications, frameworks, and libraries', ARRAY[E'\\xCAFEBABE'::bytea], true),
('PDF', 'Adobe Portable Document Format (PDF)', ARRAY[E'\\x25504446'::bytea], false),
('Office97', 'Microsoft Office 97 and other types (Compound Document Format)', ARRAY[E'\\xD0CF11E0'::bytea], false),
('RTF', 'Rich Text Format (RTF)', ARRAY[E'\\x7b5c7274'::bytea], false);


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


CREATE TABLE executable (
    fileid bigint NOT NULL REFERENCES file(id),
    architecture text,
    operatingsystem text,
    pehash uuid, -- MD5
    importhash uuid, -- MD5
    importhashfuzzy text,
    packed boolean,
    sections integer NOT NULL,
    sectionnames text[],
    sectionentropies real[],
    sectionexec boolean[],
    PRIMARY KEY (fileid)
);


CREATE TABLE pdf (
    fileid bigint NOT NULL REFERENCES file(id),
    title text,
    author text,
    pages int,
    streams int,
    javascript boolean,
    openAction boolean,
    forms boolean,
    largeColourSpace boolean,
    embeddedFiles int,
    PRIMARY KEY (fileid)
);


CREATE TABLE label (
    id bigserial NOT NULL,
    name text NOT NULL UNIQUE,
    parent bigint references label(id),
    PRIMARY KEY (id)
);


CREATE TABLE source (
    id serial NOT NULL,
    name text NOT NULL UNIQUE,
    description text,
    url text,
    firstacquisition timestamp with time zone NOT NULL,
    releasable boolean NOT NULL,
    malicious boolean,
    parent integer references source(id),
    PRIMARY KEY (id)
);


CREATE TABLE filelabel (
    fileid bigint NOT NULL REFERENCES file(id),
    labelid bigint NOT NULL REFERENCES label(id),
    added timestamp with time zone NOT NULL DEFAULT timezone('utc', now()),
    PRIMARY KEY (fileid, labelid)
);


CREATE TABLE sourcelabel (
    sourceid int NOT NULL REFERENCES source(id),
    labelid bigint NOT NULL REFERENCES label(id),
    added timestamp with time zone NOT NULL DEFAULT timezone('utc', now()),
    PRIMARY KEY (sourceid, labelid)
);


CREATE TABLE grp (
    id serial NOT NULL,
    name text NOT NULL CONSTRAINT name_unique UNIQUE,
    description text,
    parent integer references grp(id),
    PRIMARY KEY (id)
);


CREATE TABLE person (
    id serial NOT NULL,
    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 timestamp with time zone NOT NULL DEFAULT timezone('utc', now()),
    readonly boolean NOT NULL DEFAULT false,
    PRIMARY KEY (id)
);


CREATE TABLE filesource (
    fileid bigint NOT NULL REFERENCES file(id),
    sourceid int NOT NULL REFERENCES source(id),
    userid int NOT NULL REFERENCES person(id),
    filename text[],
    firstseen timestamp with time zone NOT NULL DEFAULT timezone('utc', now()), -- 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)
);


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 int references source(id),
    defaultKey int references encryptionkey(id),
    anonymous_user_id int references person(id)
);


INSERT INTO mdbconfig(version, name) VALUES('0', 'MalwareDB'); -- Version will be updated on the first run.


CREATE TABLE pagination (
    id uuid NOT NULL,  -- Unique identifier for this paginated search
    type search_pagination_type NOT NULL, -- Plain or similarity hash-based search
    query json NOT NULL, -- Serialized form of the query structure
    userid int NOT NULL REFERENCES person(id), -- User who initiated the search
    next_fileid bigint NOT NULL REFERENCES file(id), -- File ID of the next file to return
    total_results bigint NOT NULL, -- Total results expected for this search
    created timestamp with time zone NOT NULL DEFAULT timezone('utc', now()), -- When the search was initiated so it can be deleted
    PRIMARY KEY (id)
);

CREATE TABLE yara_search (
    id uuid NOT NULL,  -- Unique identifier for this paginated search
    yara_text text NOT NULL,
    yara_compiled bytea,
    userid int NOT NULL REFERENCES person(id), -- User who initiated the search
    results text[], -- Yara name|Hash
    last_fileid bigint 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 timezone('utc', now()), -- 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 bigint NOT NULL REFERENCES file(id),
    filetypeid integer NOT NULL REFERENCES filetype(id),
    explanation text,
    PRIMARY KEY (fileid, filetypeid)
);

CREATE TABLE groupsource (
     gid integer NOT NULL REFERENCES grp(id),
     sourceid int NOT NULL REFERENCES source(id),
     added timestamp DEFAULT timezone('utc', now()) NOT NULL,
     PRIMARY KEY(gid, sourceid)
);

CREATE TABLE usergroup (
    pid integer NOT NULL REFERENCES person(id),
    gid integer NOT NULL REFERENCES grp(id),
    added timestamp with time zone NOT NULL default timezone('utc', now()),
    PRIMARY KEY (pid, gid)
);

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


CREATE TABLE vtdata (
    fileid bigint NOT NULL REFERENCES file(id),
    tstamp timestamp with time zone NOT NULL,
    hits integer NOT NULL,
    total integer NOT NULL,
    vtdetail json,
    PRIMARY KEY (fileid, tstamp)
);


