-- Creates the users table for the ESP32 backup portal.
-- Passwords are stored twice for convenience: a hashed field (`password_hash`) and a plain-text fallback (`password_plain`).
-- Replace the plain-text values with hashed strings (using PHP password_hash) before going to production.

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL DEFAULT '',
    password_plain VARCHAR(100) NOT NULL DEFAULT '',
    api_token VARCHAR(120) NOT NULL DEFAULT '',
    role ENUM('admin', 'reseller', 'user') NOT NULL DEFAULT 'user',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY users_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (email, password_hash, password_plain, api_token, role)
VALUES
    ('fido', '$2y$10$RjxummSdfswNTbEv3KErLeNxOcCkNaPYm7eYTVf5x5KWsZZWLbRsm', '', '', 'admin'),
    ('reseller', '', 'reseller123', '', 'reseller'),
    ('user', '', 'user123', '', 'user')
ON DUPLICATE KEY UPDATE
    role = VALUES(role);
