PRAGMA journal_mode=WAL;


CREATE TABLE IF NOT EXISTS customer (
    id TEXT NOT NULL PRIMARY KEY
    ,max_nkode_len INTEGER NOT NULL
    ,min_nkode_len INTEGER NOT NULL
    ,distinct_sets INTEGER NOT NULL
    ,distinct_attributes INTEGER NOT NULL
    ,lock_out INTEGER NOT NULL
    ,expiration INTEGER NOT NULL
    ,attribute_values BLOB NOT NULL
    ,set_values BLOB NOT NULL
    ,last_renew TEXT NOT NULL
    ,created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS user (
    id TEXT NOT NULL PRIMARY KEY
    ,email TEXT NOT NULL
--	first_name TEXT NOT NULL
--	last_name TEXT NOT NULL
    ,renew INT NOT NULL
    ,refresh_token TEXT
    ,customer_id TEXT NOT NULL

-- Enciphered Passcode
    ,code TEXT NOT NULL
    ,mask TEXT NOT NULL

-- Keypad Dimensions
    ,attributes_per_key INT NOT NULL
    ,number_of_keys INT NOT NULL

-- User Keys
    ,alpha_key BLOB NOT NULL
    ,set_key BLOB NOT NULL
    ,pass_key BLOB NOT NULL
    ,mask_key BLOB NOT NULL
    ,salt BLOB NOT NULL
    ,max_nkode_len INT NOT NULL

-- User Interface
    ,idx_interface BLOB NOT NULL
    ,svg_id_interface BLOB NOT NULL

    ,last_login TEXT NULL
    ,created_at TEXT

    ,FOREIGN KEY (customer_id) REFERENCES customer(id)
    ,UNIQUE(customer_id, email)
);

CREATE TABLE IF NOT EXISTS svg_icon (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    ,svg TEXT NOT NULL
);


CREATE TABLE IF NOT EXISTS user_permission (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    ,user_id TEXT NOT NULL
    ,permission TEXT NOT NULL
    ,FOREIGN KEY (user_id) REFERENCES user(id)
    ,UNIQUE(user_id, permission)
);


---- go-oidc

CREATE TABLE IF NOT EXISTS clients (
    id TEXT PRIMARY KEY
    ,name TEXT NOT NULL
    ,owner TEXT NOT NULL
    ,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ,FOREIGN KEY (owner) REFERENCES user (id)
    ,UNIQUE(name, owner)
    );

CREATE TABLE IF NOT EXISTS client_redirects (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    ,uri TEXT NOT NULL
    ,client_id TEXT NOT NULL
    ,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ,FOREIGN KEY (client_id) REFERENCES clients (id)
    ,UNIQUE(uri, client_id)
    );

CREATE TABLE IF NOT EXISTS authorization_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    ,code TEXT NOT NULL UNIQUE
    ,code_challenge TEXT NOT NULL UNIQUE
    ,code_challenge_method TEXT NOT NULL CHECK (code_challenge_method IN ('S256', 'plain'))
    ,user_id TEXT NOT NULL
    ,client_id TEXT NOT NULL
    ,scope TEXT
    ,redirect_uri TEXT NOT NULL
    ,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ,expires_at DATETIME NOT NULL
    ,used_at DATETIME
    ,FOREIGN KEY (user_id) REFERENCES user (id)
    ,FOREIGN KEY (client_id) REFERENCES client (id)
    );

CREATE TABLE IF NOT EXISTS tokens (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    ,token_type TEXT NOT NULL CHECK (token_type IN ('access', 'refresh'))
    ,token_value TEXT NOT NULL UNIQUE
    ,user_id TEXT NOT NULL
    ,client_id TEXT NOT NULL
    ,scope TEXT
    ,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ,expires_at DATETIME NOT NULL
    ,FOREIGN KEY (user_id) REFERENCES user (id)
    ,FOREIGN KEY (client_id) REFERENCES clients (id)
    );

CREATE TABLE IF NOT EXISTS client_approvals (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    ,user_id TEXT NOT NULL
    ,client_id TEXT NOT NULL
    ,UNIQUE(user_id, client_id)
    ,FOREIGN KEY (user_id) REFERENCES users (id)
    ,FOREIGN KEY (client_id) REFERENCES clients (id)
);