-- One-off setup for the app's own database (users, sessions, saved conversations).
-- Run it ONCE as an administrator (e.g. `root` in MySQL Workbench), after changing the password below.
-- It is separate from your portfolio database on purpose: see APP_DB_* in .env.example.
-- Works on MySQL 8 and MariaDB 10.5+.

CREATE DATABASE IF NOT EXISTS athena_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- A least-privilege account that can only touch this one database. It cannot see the portfolio data,
-- and nothing the model writes ever runs as it.
CREATE USER IF NOT EXISTS 'athena_app'@'localhost' IDENTIFIED BY 'CHANGE_ME_to_a_long_random_password';
CREATE USER IF NOT EXISTS 'athena_app'@'127.0.0.1' IDENTIFIED BY 'CHANGE_ME_to_a_long_random_password';

-- DDL rights are needed only by `npm run db:migrate`. If you prefer, run migrations as an admin instead and
-- narrow this to: SELECT, INSERT, UPDATE, DELETE.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, REFERENCES ON athena_app.* TO 'athena_app'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, REFERENCES ON athena_app.* TO 'athena_app'@'127.0.0.1';
FLUSH PRIVILEGES;