CREATE TABLE IF NOT EXISTS device (
    did INTEGER PRIMARY KEY,
    dname TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS result (
    rid INTEGER PRIMARY KEY,
    rname TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS test (
    tid INTEGER PRIMARY KEY AUTOINCREMENT,
    tnumber TEXT, tdate TEXT,
    tbuildnr INTEGER,
    trid INTEGER REFERENCES result (rid),
    tdid INTEGER REFERENCES device (did),
    UNIQUE (tnumber, tdid) ON CONFLICT REPLACE
);

CREATE VIEW IF NOT EXISTS currentstatus AS
    SELECT
        test.tnumber AS "SR",
        test.tdate AS "Date",
        test.tbuildnr AS "Build nr",
        device.dname AS "Device",
        result.rname AS "Status"
    FROM test
    INNER JOIN device ON test.tdid = device.did
    INNER JOIN result ON result.rid = test.trid
    ORDER BY SR DESC
;

CREATE VIEW IF NOT EXISTS currentstatus3 AS
    SELECT
        test.tnumber AS "SR",
        test.tdate AS "Date",
        test.tbuildnr AS "Build nr",
        device.dname AS "Device",
        result.rname AS "Status"
    FROM test
    INNER JOIN device ON test.tdid = device.did
    INNER JOIN result ON result.rid = test.trid
    WHERE SR LIKE "%3.0-common%"
    ORDER BY SR DESC
;

CREATE VIEW IF NOT EXISTS currentstatus4 AS
    SELECT
        test.tnumber AS "SR",
        test.tdate AS "Date",
        test.tbuildnr AS "Build nr",
        device.dname AS "Device",
        result.rname AS "Status"
    FROM test
    INNER JOIN device ON test.tdid = device.did
    INNER JOIN result ON result.rid = test.trid
    WHERE SR LIKE "%4.0-unified%"
    ORDER BY SR DESC
;