CREATE TABLE `users` (

 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

 `username` varchar(50) NOT NULL COMMENT 'User Name',

 `active` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT 'Set if the user account is active and allows logins',

 `email` varchar(255) NOT NULL COMMENT 'User email address',

 `first_name` varchar(255) NOT NULL COMMENT 'User First Name',

 `last_name` varchar(255) NOT NULL COMMENT 'User Last Name',

 `full_name` varchar(255) GENERATED ALWAYS AS (concat(`first_name`,' ',`last_name`)) VIRTUAL COMMENT 'Calculated full name field',

 `phone` varchar(150) DEFAULT NULL COMMENT 'User''s phone number',

 `email_confirmed` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'True if the email address provided has been validated',

 `email_confirmation_hash` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Verification hash for validating email addresses',

 `pwhash` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Password Hash',

 `password_reset_pwhash` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL COMMENT 'New hash to reset password to, if the user confirms the reset via email',

 `password_last_changed` date DEFAULT NULL COMMENT 'Date of last password change',

 `permissions` longtext DEFAULT NULL COMMENT 'User permissions',

 `company_id` bigint(20) unsigned NOT NULL COMMENT 'Company ID FK',

 `reminders` tinyint(1) unsigned NOT NULL DEFAULT 1 COMMENT 'True if the user receives email reminders',

 `data` longtext DEFAULT NULL COMMENT 'Metadata',

 PRIMARY KEY (`id`),

 UNIQUE KEY `unique_usernames` (`username`),

 KEY `company_id_idx` (`company_id`),

 KEY `username_idx` (`username`),

 KEY `reminders` (`reminders`),

 KEY `password_last_changed` (`password_last_changed`),

 KEY `pwhash` (`pwhash`),

 KEY `password_reset_pwhash` (`password_reset_pwhash`),

 KEY `email_confirmed` (`email_confirmed`),

 KEY `email_confirmation_hash` (`email_confirmation_hash`),

 KEY `email` (`email`),

 KEY `active` (`active`),

 KEY `first_name` (`first_name`),

 KEY `last_name` (`last_name`),

 KEY `phone` (`phone`),

 KEY `first_name_last_name` (`first_name`,`last_name`) USING BTREE,

 KEY `full_name` (`full_name`),

 CONSTRAINT `users_companies` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),

 CONSTRAINT `data` CHECK (`data` is null or json_valid(`data`)),

 CONSTRAINT `permissions_json` CHECK (`permissions` is null or json_valid(`permissions`))

) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COMMENT='Username List'