-- AWV + RPM Pilot — Users & Roles
-- Run after schema.sql  (same database: awv_rpm_pilot)

USE awv_rpm_pilot;

CREATE TABLE IF NOT EXISTS users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(80)  NOT NULL UNIQUE,
    email         VARCHAR(180) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,       -- bcrypt
    role          ENUM('admin','provider','medical_assistant') NOT NULL,
    full_name     VARCHAR(180),
    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,
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Seed: one admin account (password = Admin@1234 — CHANGE BEFORE PRODUCTION)
INSERT IGNORE INTO users (username, email, password_hash, role, full_name)
VALUES (
    'admin',
    'admin@clinic.local',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- Admin@1234
    'admin',
    'System Administrator'
);
