/*
You are managing a user accounts database for a platform that enforces strict username and email validation. The `users` table has the following schema:  

| Column Name  | Data Type     | Description                                  |  
|--------------|---------------|----------------------------------------------|  
| user_id      | INT           | Unique ID for each user                     |  
| username     | VARCHAR(50)   | The username chosen by the user             |  
| email        | VARCHAR(100)  | The user's email address                    |  
| created_at   | DATE          | The date when the account was created       |  

Task:  

1. Extract Valid Usernames:  
   • A valid username contains only alphanumeric characters and underscores (`_`) but cannot start or end with an underscore.  
2. Validate Emails:  
   • The email must follow the format: `text@domain.extension` where:  
     • `text` can include alphanumeric characters and periods (`.`).  
     • `domain` must contain only alphanumeric characters.  
     • `extension` is between 2 to 4 alphabetic characters.  
3. Find and Display:  
   • Users with invalid usernames or invalid emails.  

Expected Output:  

| user_id | username     | email                     | error_reason                |  
|---------|--------------|---------------------------|-----------------------------|  
| 3       | _carol       | carol@domain.org          | Invalid username            |  
| 5       | eve.2024     | eve@example               | Invalid email               |  
| 6       | _invalid_    | invalid.email@domain.com  | Invalid username and email  |  
*/

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at DATE
);

INSERT INTO users (user_id, username, email, created_at) VALUES
(1, 'john_doe', 'john.doe@gmail.com', '2023-12-01'),
(2, 'alice123', 'alice@company.net', '2023-11-20'),
(3, '_carol', 'carol@domain.org', '2023-10-15'),
(4, 'bob_smith', 'bob.smith@gmail.com', '2023-09-10'),
(5, 'eve.2024', 'eve@example', '2023-08-25'),
(6, '_invalid_', 'invalid.email@domain.com', '2023-07-18'),
(7, 'michael', 'michael@service.co', '2023-06-14');

SELECT 
    user_id,
    username,
    email,
    CASE
        WHEN username NOT REGEXP '^[A-Za-z0-9]+(_[A-Za-z0-9]+)*$' AND 
             email NOT REGEXP '^[A-Za-z0-9.]+@[A-Za-z0-9]+\\.[A-Za-z]{2,4}$'
             THEN 'Invalid username and email'
        WHEN username NOT REGEXP '^[A-Za-z0-9]+(_[A-Za-z0-9]+)*$' 
             THEN 'Invalid username'
        WHEN email NOT REGEXP '^[A-Za-z0-9.]+@[A-Za-z0-9]+\\.[A-Za-z]{2,4}$'
             THEN 'Invalid email'
        ELSE NULL
    END AS error_reason
FROM users
WHERE 
    username NOT REGEXP '^[A-Za-z0-9]+(_[A-Za-z0-9]+)*$'
    OR email NOT REGEXP '^[A-Za-z0-9.]+@[A-Za-z0-9]+\\.[A-Za-z]{2,4}$';