-- Run once to add accounts + link to users in existing DBs

CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_md5 CHAR(32) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add account_id to users if it doesn't exist
ALTER TABLE users ADD COLUMN account_id INT NULL;
ALTER TABLE users ADD UNIQUE KEY uq_users_account (account_id);
ALTER TABLE users ADD CONSTRAINT fk_users_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL;

