-- ────────────────────────────────────────────────────────────────────────────
--  MySQL Schema   –  jwt_auth_db
--  Run this once before starting the app:
--  $ mysql -u root -p < schema.sql
-- ────────────────────────────────────────────────────────────────────────────

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

USE jwt_auth_db;

-- ── Roles ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS roles (
  id   TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

INSERT IGNORE INTO roles (name) VALUES ('admin'), ('user');

-- ── Users ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100)    NOT NULL,
  email         VARCHAR(191)    NOT NULL UNIQUE,
  password_hash VARCHAR(255)    NOT NULL,
  role_id       TINYINT UNSIGNED NOT NULL DEFAULT 2,   -- 2 = user
  is_active     TINYINT(1)      NOT NULL DEFAULT 1,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT fk_users_role FOREIGN KEY (role_id)
    REFERENCES roles(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- ── Seed: default admin account ────────────────────────────────────────────
-- Password: Admin@123  (bcrypt hash — change this immediately in production!)
INSERT IGNORE INTO users (name, email, password_hash, role_id)
VALUES (
  'Super Admin',
  'admin@example.com',
  '$2a$12$qBxSAMgJr3xzGnAEr1N2mOMT8LHBiXI.1.TdNhbpOFfMqfHumNLoe',
  1
);