-- Seed admin account for Eric Bigelow in users table.
-- Password: password12
-- Uses password_hash output compatible with api/login.php password_verify().

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    email VARCHAR(190) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'member',
    password_hash VARCHAR(255) NOT NULL,
    pin VARCHAR(20) DEFAULT NULL,
    `2fa` TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_users_username (username),
    UNIQUE KEY uniq_users_email (email),
    INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backfill for existing users table: add pin column only when missing.
SET @has_pin_column := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'pin'
);
SET @alter_users_sql := IF(
    @has_pin_column = 0,
    'ALTER TABLE users ADD COLUMN pin VARCHAR(20) DEFAULT NULL AFTER password_hash',
    'SELECT 1'
);
PREPARE alter_users_stmt FROM @alter_users_sql;
EXECUTE alter_users_stmt;
DEALLOCATE PREPARE alter_users_stmt;

INSERT INTO users (username, first_name, last_name, email, role, password_hash, `2fa`)
VALUES (
    'eric.bigelow',
    'Eric',
    'Bigelow',
    'eric.bigelow@me.com',
    'admin',
    '$2y$10$w26BYzUlPh8IhoR/UxBNFerq3UB0q2tsU1e.nprpLUDjdAzUzrM/q',
    1
)
ON DUPLICATE KEY UPDATE
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    email = VALUES(email),
    role = VALUES(role),
    password_hash = VALUES(password_hash),
    `2fa` = VALUES(`2fa`);