PRAGMA foreign_keys = ON;

BEGIN;

-- OPTION

CREATE TABLE moop_model_set (
    id      BLOB PRIMARY KEY NOT NULL,
    name    TEXT NOT NULL
);

CREATE TABLE moop_model_type (
    id      BLOB PRIMARY KEY NOT NULL,
    type    TEXT NOT NULL UNIQUE
);

CREATE TABLE moop_model_set_type (
    id      BLOB PRIMARY KEY NOT NULL,
    "set"   BLOB NOT NULL,
    type    BLOB NOT NULL,
    FOREIGN KEY ("set")  REFERENCES moop_model_set(id)   ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (type)   REFERENCES moop_model_type(id)  ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE ("set", type) -- Prevent duplicate entries
);

CREATE TABLE moop_model_type_default (
    id          BLOB PRIMARY KEY NOT NULL,
    type        BLOB NOT NULL UNIQUE,
    set_type    BLOB NOT NULL UNIQUE,
    FOREIGN KEY (type)      REFERENCES moop_model_type(id)      ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (set_type)  REFERENCES moop_model_set_type(id)  ON UPDATE CASCADE ON DELETE CASCADE
);

-- PROVIDER

CREATE TABLE moop_api (
    id          BLOB PRIMARY KEY NOT NULL,
    name        TEXT NOT NULL,
    baseurl     TEXT NOT NULL
);

CREATE TABLE moop_key (
    id          BLOB PRIMARY KEY NOT NULL,
    key         TEXT NOT NULL,
    priority    INTEGER NULL,
    api         BLOB NOT NULL,
    FOREIGN KEY (api) REFERENCES moop_api(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE moop_model (
    id          BLOB PRIMARY KEY NOT NULL,
    api         BLOB NOT NULL,
    "set"       BLOB NOT NULL,
    model       TEXT NOT NULL,
    priority    INTEGER NULL,
    FOREIGN KEY (api)    REFERENCES moop_api(id)        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ("set")  REFERENCES moop_model_set(id)  ON UPDATE CASCADE ON DELETE CASCADE
);

-- INDEXES

CREATE INDEX idx_moop_key_api              ON moop_key(api);
CREATE INDEX idx_moop_key_api_priority     ON moop_key(api, priority);

CREATE INDEX idx_moop_model_api            ON moop_model(api);
CREATE INDEX idx_moop_model_set            ON moop_model("set");

CREATE INDEX idx_moop_model_api_priority   ON moop_model(api, priority);

CREATE INDEX idx_moop_model_set_type_set   ON moop_model_set_type("set");
CREATE INDEX idx_moop_model_set_type_type  ON moop_model_set_type(type);

COMMIT;