--DROP MATERIALIZED VIEW IF EXISTS count_stats;
--DROP VIEW IF EXISTS __count_stats;

DROP TABLE IF EXISTS texts3_details;
DROP TABLE IF EXISTS texts3;
DROP TABLE IF EXISTS ips3_details;
DROP TABLE IF EXISTS ips3;
DROP TABLE IF EXISTS negatives3_details;
DROP TABLE IF EXISTS negatives3;
--DROP TABLE IF EXISTS negative_types;
--DROP TABLE IF EXISTS rec_types;
--DROP TABLE IF EXISTS sources;
--DROP TABLE IF EXISTS tokens;
--DROP TABLE IF EXISTS users;
--DROP TABLE IF EXISTS blacklist_domain;

--DROP EXTENSION IF EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

--CREATE TABLE blacklist_domain (
--    pattern text NOT NULL PRIMARY KEY
--);

--CREATE TABLE users (
--    id serial UNIQUE,
--    name text NOT NULL PRIMARY KEY,
--    fullname text NOT NULL,
--    valid boolean NOT NULL DEFAULT TRUE
--);

--CREATE TABLE tokens (
--    userid integer NOT NULL REFERENCES users(id),
--    tokenname text NOT NULL,
--    created timestamp NOT NULL DEFAULT NOW(),
--    token text NOT NULL UNIQUE DEFAULT MD5(RANDOM()::text),
--    PRIMARY KEY (userid, tokenname)
--);

--CREATE TABLE rec_types (
--    id integer NOT NULL PRIMARY KEY CHECK (id > 0 AND id < 65536),
--    rtype text NOT NULL
--);

--INSERT INTO rec_types VALUES
--    (1,  'A'),
--    (2,  'NS'),
--    (5,  'CNAME'),
--    (12, 'PTR'),
--    (15, 'MX'),
--    (28, 'AAAA')
--;

--DROP TYPE IF EXISTS source_import_type;
--CREATE TYPE source_import_type AS ENUM ('dnscap', 'json');

--CREATE TABLE sources (
--    id smallint NOT NULL PRIMARY KEY,
--    source text NOT NULL,
--    import_type source_import_type,
--    credibility boolean NOT NULL,	-- Source credibility: 0: untrusted / 1: trusted (performs full recursion)
--    control boolean NOT NULL,		-- Source control: 0: third-party / 1: own
--    acquisition boolean NOT NULL,	-- Data acquisition method: 0: recursion / 1: traffic analysis
--    cache boolean NOT NULL		-- Caching mechanism: 0: no cache / 1: with cache
--);

CREATE TABLE texts3 (
    lvalue text NOT NULL,
    rvalue text NOT NULL,
    rtype integer NOT NULL REFERENCES rec_types(id) CHECK (rtype NOT IN (1, 28)),
    count integer NOT NULL CHECK (count > 0),
    time_first timestamp NOT NULL,
    time_last timestamp NOT NULL CHECK (time_last >= time_first),
    id bigserial UNIQUE,
    PRIMARY KEY (lvalue, rtype, rvalue) INCLUDE (id)
);
CREATE INDEX "texts3_lvalue_idx" ON texts3 USING btree (lvalue);
CREATE INDEX "texts3_rvalue_idx" ON texts3 USING btree (rvalue);
CREATE INDEX "texts3_lvalue_trgm_idx" ON texts3 USING gin (lvalue gin_trgm_ops);
CREATE INDEX "texts3_rvalue_trgm_idx" ON texts3 USING gin (rvalue gin_trgm_ops);

CREATE TABLE texts3_details (
    id bigint NOT NULL REFERENCES texts3(id) ON DELETE CASCADE,
    source smallint NOT NULL REFERENCES sources(id),
    time timestamp NOT NULL,
    ttl integer NOT NULL,
    transactionid bytea NOT NULL,
    x20randomness bytea,
    authenticated_authoritative bit(2) NOT NULL,
    rem jsonb,
    PRIMARY KEY (id, source, time)
);

CREATE TABLE ips3 (
    domain text NOT NULL,
    ip inet NOT NULL,
    rtype integer NOT NULL REFERENCES rec_types(id) CHECK (rtype IN (1, 28)),
    count integer NOT NULL CHECK (count > 0),
    time_first timestamp NOT NULL,
    time_last timestamp NOT NULL CHECK (time_last >= time_first),
    id bigserial UNIQUE,
    PRIMARY KEY (ip, rtype, domain) INCLUDE (id)
);
CREATE INDEX "ips3_domain_idx" ON ips3 USING btree (domain);
CREATE INDEX "ips3_domain_trgm_idx" ON ips3 USING gin (domain gin_trgm_ops);

CREATE TABLE ips3_details (
    id bigint NOT NULL REFERENCES ips3(id) ON DELETE CASCADE,
    source smallint NOT NULL REFERENCES sources(id),
    time timestamp NOT NULL,
    ttl integer NOT NULL,
    transactionid bytea NOT NULL,
    x20randomness bytea,
    authenticated_authoritative bit(2) NOT NULL,
    rem jsonb,
    PRIMARY KEY (id, source, time)
);

--CREATE TABLE negative_types (
--    id smallint NOT NULL PRIMARY KEY,
--    ntype text NOT NULL
--);

--INSERT INTO negative_types VALUES
--    (0, 'NODATA'),
--    (1, 'Format error'),
--    (2, 'Server failure'),
--    (3, 'NXDOMAIN'),
--    (4, 'Not implemented'),
--    (5, 'Refused')
--;

CREATE TABLE negatives3 (
    qname text NOT NULL,
    rtype integer NOT NULL REFERENCES rec_types(id),
    count integer NOT NULL CHECK (count > 0),
    time_first timestamp NOT NULL,
    time_last timestamp NOT NULL CHECK (time_last >= time_first),
    id bigserial UNIQUE,
    PRIMARY KEY (qname, rtype) INCLUDE (id)
);
CREATE INDEX "negatives3_qname_idx" ON negatives3 USING btree (qname);
CREATE INDEX "negatives3_qname_trgm_idx" ON negatives3 USING gin (qname gin_trgm_ops);

CREATE TABLE negatives3_details (
    id bigint REFERENCES negatives3(id) ON DELETE CASCADE,
    source smallint NOT NULL REFERENCES sources(id),
    time timestamp NOT NULL,
    reply smallint NOT NULL REFERENCES negative_types(id),
    PRIMARY KEY (id, source, time, reply)
);

--CREATE VIEW __count_stats AS (
--SELECT *, ("A"+"AAAA"+"NS"+"CNAME"+"PTR"+"MX"+"NODATA"+"NXDOMAIN"+"Format error"+"Server failure"+"Not implemented"+"Refused") AS "Total" FROM (SELECT date(time) AS day, COUNT(*) FILTER (WHERE rtype=1) AS "A", COUNT(*) FILTER (WHERE rtype=28) AS "AAAA" FROM ips3_details INNER JOIN ips3 USING (id) GROUP BY day) AS ip FULL JOIN (SELECT date(time) AS day, COUNT(*) FILTER (WHERE rtype=2) AS "NS", COUNT(*) FILTER (WHERE rtype=5) AS "CNAME", COUNT(*) FILTER (WHERE rtype=12) AS "PTR", COUNT(*) FILTER (WHERE rtype=15) AS "MX" FROM texts3_details INNER JOIN texts3 USING (id) GROUP BY day) AS text USING (day) FULL JOIN (SELECT date(time) AS day, COUNT(*) FILTER (WHERE reply=0) AS "NODATA", COUNT(*) FILTER (WHERE reply=3) AS "NXDOMAIN", COUNT(*) FILTER (WHERE reply=1) AS "Format error", COUNT(*) FILTER (WHERE reply=2) AS "Server failure", COUNT(*) FILTER (WHERE reply=4) AS "Not implemented", COUNT(*) FILTER (WHERE reply=5) AS "Refused" FROM negatives3_details INNER JOIN negatives3 USING (id) GROUP BY day) AS negative USING (day) WHERE day < date(NOW()) AND day > date(NOW()) - INTERVAL '3 mons' ORDER BY day DESC
--);