CREATE schema oauth;

CREATE TABLE oauth.scope (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        UNIQUE (name),
        PRIMARY KEY (id)
);

INSERT INTO oauth.scope (name, description)
     VALUES ('profile', 'View your public account information')
          , ('tag', 'View and modify your private tags')
          , ('rating', 'View and modify your private ratings');

CREATE TABLE oauth.client (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        client_id TEXT NOT NULL,
        client_secret TEXT,
        owner_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        website TEXT,
        redirect_uris TEXT[] NOT NULL,
        client_id_issued_at TIMESTAMP WITH TIME ZONE NOT NULL,
        PRIMARY KEY (id)
-- no FK for now as user data lives in MB db
--         FOREIGN KEY(owner_id) REFERENCES "user" (id) ON DELETE CASCADE
);

CREATE TABLE oauth.code (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        user_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        code TEXT NOT NULL,
        redirect_uri TEXT NOT NULL,
        code_challenge TEXT,
        code_challenge_method TEXT,
        issued_at TIMESTAMP WITH TIME ZONE NOT NULL,
        expires_in INTEGER,
        revoked BOOLEAN,
        PRIMARY KEY (id),
-- no FK for now as user data lives in MB db
--         FOREIGN KEY(user_id) REFERENCES "user" (id) ON DELETE CASCADE,
        FOREIGN KEY(client_id) REFERENCES oauth.client (id) ON DELETE CASCADE,
        UNIQUE (code)
);

CREATE TABLE oauth.access_token (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        user_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        authorization_code_id INTEGER,
        access_token TEXT NOT NULL,
        issued_at TIMESTAMP WITH TIME ZONE,
        expires_in INTEGER,
        revoked BOOLEAN,
        PRIMARY KEY (id),
-- no FK for now as user data lives in MB db
--         FOREIGN KEY(user_id) REFERENCES "user" (id) ON DELETE CASCADE,
        FOREIGN KEY (client_id) REFERENCES oauth.client (id) ON DELETE CASCADE,
        FOREIGN KEY (authorization_code_id) REFERENCES oauth.code (id) ON DELETE CASCADE,
        UNIQUE (access_token)
);

CREATE TABLE oauth.refresh_token (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        user_id INTEGER NOT NULL,
        client_id INTEGER NOT NULL,
        authorization_code_id INTEGER,
        refresh_token TEXT NOT NULL,
        issued_at TIMESTAMP WITH TIME ZONE,
        expires_in INTEGER,
        revoked BOOLEAN,
        PRIMARY KEY (id),
-- no FK for now as user data lives in MB db
--         FOREIGN KEY(user_id) REFERENCES "user" (id) ON DELETE CASCADE,
        FOREIGN KEY (client_id) REFERENCES oauth.client (id) ON DELETE CASCADE,
        FOREIGN KEY (authorization_code_id) REFERENCES oauth.code (id) ON DELETE CASCADE,
        UNIQUE (refresh_token)
);

CREATE TABLE oauth.l_access_token_scope (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        access_token_id INTEGER NOT NULL,
        scope_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(access_token_id) REFERENCES oauth.access_token (id) ON DELETE CASCADE,
        FOREIGN KEY(scope_id) REFERENCES oauth.scope (id) ON DELETE CASCADE
);

CREATE TABLE oauth.l_refresh_token_scope (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        refresh_token_id INTEGER NOT NULL,
        scope_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(refresh_token_id) REFERENCES oauth.refresh_token (id) ON DELETE CASCADE,
        FOREIGN KEY(scope_id) REFERENCES oauth.scope (id) ON DELETE CASCADE
);


CREATE TABLE oauth.l_code_scope (
        id INTEGER GENERATED BY DEFAULT AS IDENTITY,
        code_id INTEGER NOT NULL,
        scope_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(code_id) REFERENCES oauth.code (id) ON DELETE CASCADE,
        FOREIGN KEY(scope_id) REFERENCES oauth.scope (id) ON DELETE CASCADE
);
