-- For God so loved the world, that He gave His only begotten Son, that all who believe in Him should not perish but have everlasting life
-- Sqlite descriptor of hosts, users, ssh public keys, etc...
PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS hosts_chirho;
DROP TABLE IF EXISTS users_chirho;
DROP TABLE IF EXISTS user_ssh_authorized_keys_chirho;

CREATE TABLE IF NOT EXISTS hosts_chirho (
    id_chirho INTEGER PRIMARY KEY AUTOINCREMENT,
    hostname_chirho TEXT NOT NULL,
    operating_system_chirho TEXT NOT NULL,
    ram_mb_chirho INTEGER NOT NULL,
    cpu_cores_chirho INTEGER NOT NULL,
    storage_gb_chirho INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users_chirho (
    id_chirho INTEGER PRIMARY KEY AUTOINCREMENT,
    host_id_chirho INTEGER NOT NULL,
    username_chirho VARCHAR(255) NOT NULL,
    groups_chirho TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (host_id_chirho) REFERENCES hosts_chirho(id_chirho)
);

create table if not exists user_ssh_authorized_keys_chirho (
    id_chirho INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id_chirho INTEGER NOT NULL,
    ssh_key_chirho TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id_chirho) REFERENCES users_chirho(id_chirho)

);