-- Postgres specific:
-- -- Add projects table, each project has a uuid, token, and a description
-- CREATE TABLE IF NOT EXISTS projects (
--     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
--     token TEXT NOT NULL,
--     description TEXT NOT NULL
-- );

-- -- Every time a prompt is performed, we need to log its metrics to the database
-- -- We need to know which project the prompt belongs to, the timestamp, and the output
-- -- We also need to know the prompt, the model, and the parameters used
-- -- We also need to know the amount of tokens in the prompt and the amount of tokens in the response
-- -- And we also need to know how much time it took to perform the prompt
-- -- Additionally we also need to know the template used for the prompt and which variables were set to which values
-- CREATE TABLE IF NOT EXISTS prompt_metrics (
--     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
--     project_id UUID NOT NULL REFERENCES projects(id),
--     timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
--     prompt TEXT NOT NULL,
--     model TEXT NOT NULL,
--     prompt_tokens INTEGER NOT NULL,
--     completion_tokens INTEGER NOT NULL,
--     total_tokens INTEGER NOT NULL,
--     response_time_ms INTEGER NOT NULL,
--     template_id TEXT NOT NULL REFERENCES templates(id),
--     variables JSONB NOT NULL
-- );

-- -- Every project has a set of templates
-- CREATE TABLE IF NOT EXISTS templates (
--     id TEXT PRIMARY KEY,
--     project_id UUID NOT NULL REFERENCES projects(id),
--     description TEXT NOT NULL,
--     template TEXT NOT NULL,
--     variables TEXT[] NOT NULL
-- );

-- SQLite specific:
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY NOT NULL,
    token TEXT NOT NULL,
    description TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS prompt_metrics (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL REFERENCES projects(id),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    prompt TEXT NOT NULL,
    response TEXT NOT NULL,
    model_name TEXT NOT NULL,
    prompt_token_count INTEGER NOT NULL,
    completion_token_count INTEGER NOT NULL,
    total_token_count INTEGER NOT NULL,
    response_time_ms INTEGER NOT NULL,
    template_id TEXT REFERENCES templates(id),
    variables TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS templates (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL REFERENCES projects(id),
    description TEXT NOT NULL,
    template TEXT NOT NULL,
    variables TEXT NOT NULL
);