CREATE TABLE users (
       id	text primary key,
       created	integer,
       user	text not null,
       password	blob not null,
       salt	blob not null,
       totp_secret text
);

CREATE TABLE tokens (
       id	text primary key,
       uid	text not null,
       token	text not null,
       expires	integer default 0,
       FOREIGN KEY(uid) REFERENCES user(id)
);

CREATE TABLE database (
       id	text primary key,
       host	text not null,
       port	integer default 5432,
       name	text not null,
       user	text not null,
       password	text not null
);

CREATE TABLE registrations (
       id	text primary key,
       code	text not null
);

CREATE TABLE roles (
       id	text primary key,
       role	text not null
);

CREATE TABLE user_roles (
	id	text primary key,
	uid	text not null,
	rid	text not null,
	FOREIGN KEY(uid) REFERENCES user(id),
	FOREIGN KEY(rid) REFERENCES roles(id)
);

-- Add permissions table
CREATE TABLE permissions (
    id TEXT PRIMARY KEY,
    resource TEXT NOT NULL,
    action TEXT NOT NULL,
    description TEXT
);

-- Link roles to permissions
CREATE TABLE role_permissions (
    id TEXT PRIMARY KEY,
    rid TEXT NOT NULL,
    pid TEXT NOT NULL,
    FOREIGN KEY(rid) REFERENCES roles(id),
    FOREIGN KEY(pid) REFERENCES permissions(id)
);

-- Add default permissions
INSERT INTO permissions (id, resource, action, description) VALUES 
    ('perm_db_read', 'database_credentials', 'read', 'Read database credentials'),
    ('perm_db_write', 'database_credentials', 'write', 'Modify database credentials'),
    ('perm_user_manage', 'users', 'manage', 'Manage user accounts'),
    ('perm_token_manage', 'tokens', 'manage', 'Manage authentication tokens');

-- Add default roles
INSERT INTO roles (id, role) VALUES 
    ('role_admin', 'admin'),
    ('role_db_operator', 'db_operator'),
    ('role_user', 'user');

-- Grant permissions to admin role
INSERT INTO role_permissions (id, rid, pid) VALUES 
    ('rp_admin_db_read', 'role_admin', 'perm_db_read'),
    ('rp_admin_db_write', 'role_admin', 'perm_db_write'),
    ('rp_admin_user_manage', 'role_admin', 'perm_user_manage'),
    ('rp_admin_token_manage', 'role_admin', 'perm_token_manage');

-- Grant database access to db_operator role
INSERT INTO role_permissions (id, rid, pid) VALUES 
    ('rp_dbop_db_read', 'role_db_operator', 'perm_db_read');