-- SPDX-FileCopyrightText: Heiko Schaefer <heiko@schaefer.name>
-- SPDX-License-Identifier: CC0-1.0

PRAGMA foreign_keys = ON;

-- index of certs that exist in the store, and their status in the lookup cache
CREATE TABLE `certs`
(
    `id`                 INTEGER NOT NULL PRIMARY KEY,
    `fp`                 TEXT    NOT NULL,
    `cached_tag`         TEXT,
    `needs_cache_update` BOOL    NOT NULL,

    CONSTRAINT certs_fp_unique UNIQUE (fp)
);

-- lookup table by subkey fingerprint
CREATE TABLE `subkeys`
(
    `id`      INTEGER NOT NULL PRIMARY KEY,
    `fp`      TEXT    NOT NULL,
    `cert_id` INTEGER NOT NULL,

    FOREIGN KEY (cert_id) REFERENCES certs (id),

    -- subkey fingerprints may occur multiple times, but only once per certificate
    CONSTRAINT subkey_unique UNIQUE (fp, cert_id)
);

-- subkeys.fp is used for lookups, so we generate an index
CREATE INDEX idx_subkeys_fp
    ON subkeys (fp);

-- lookup table by key id (primary or subkey)
CREATE TABLE `keyids`
(
    `id`      INTEGER NOT NULL PRIMARY KEY,
    `keyid`   TEXT    NOT NULL,
    `cert_id` INTEGER NOT NULL,

    FOREIGN KEY (cert_id) REFERENCES certs (id),

    -- key ids may occur multiple times, but only once per certificate
    CONSTRAINT keyid_unique UNIQUE (keyid, cert_id)
);

-- keyids.keyid is used for lookups, so we generate an index
CREATE INDEX idx_keyids_keyid
    ON keyids (keyid);

-- lookup table by email string
CREATE TABLE `emails`
(
    `id`      INTEGER NOT NULL PRIMARY KEY,
    `email`   TEXT    NOT NULL,
    `cert_id` INTEGER NOT NULL,

    FOREIGN KEY (cert_id) REFERENCES certs (id),

    -- email strings may occur multiple times, but only once per certificate
    CONSTRAINT email_unique UNIQUE (email, cert_id)
);

-- emails.email is used for lookups, so we generate an index
CREATE INDEX idx_emails_email
    ON emails (email);

-- lookup table by user id string
CREATE TABLE `userids`
(
    `id`      INTEGER NOT NULL PRIMARY KEY,
    `userid`  TEXT    NOT NULL,
    `cert_id` INTEGER NOT NULL,

    FOREIGN KEY (cert_id) REFERENCES certs (id),

    -- user id strings may occur multiple times, but only once per certificate
    CONSTRAINT userid_unique UNIQUE (userid, cert_id)
);

-- userids.userid is used for lookups, so we generate an index
CREATE INDEX idx_userids_userid
    ON userids (userid);