CREATE TABLE IF NOT EXISTS hh_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    household_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NULL DEFAULT NULL,
    name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(40) NOT NULL DEFAULT 'adult',
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY email (email),
    KEY household_id (household_id),
    KEY member_id (member_id),
    KEY role_status (role, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO hh_schema_migrations (migration) VALUES ('2026_05_11_000002_auth');