### Technical assignment (TA) for the development of a Telegram bot with an admin panel and integration with Google Sheets
#### 1. **Task description**
Develop a Telegram bot with an admin panel through which Google Sheets can be linked for monitoring and processing data entered by users into cells. The main goal of the bot is to track and manage the status of Telegram usernames entered into the sheets (starting with `@ username`), and their constant automatic updating in the database.
#### 2. **Functional requirements**
##### 2.1. **Linking Google Sheets**
- The administrator can link the necessary Google Sheets to the bot through the control panel.
- Each linked sheet must be available for real-time change monitoring.
- Ability to monitor multiple sheets simultaneously.
##### 2.2. **Data monitoring**
- Continuous monitoring of data entry in the cells of the sheets, tracking the appearance of new data (usernames).
- Monitoring all pages and tabs of the sheets (all data in the sheets must be covered).
##### 2.3. **Username processing**
- **Adding usernames**:
- If the entered username is new (not present in the database), it is added to the database.
- The cell with this username in the sheet is colored green for **7 days** (indicator of reserved username).
- Information is recorded in the database that this username is reserved for a week.
- If the entered username is already reserved - the cell turns red.
- **Occupancy status**:
- If another user enters the same username in their sheet, their cell turns red (indicating that the username is temporarily occupied by another user).
- **End of reservation period**:
- When the reservation period (7 days) for the username expires, the username becomes "free" in the database.
- In the sheet:
- The cell of the user who originally added the username is colored yellow. This signals that the old username needs to be removed and re-entered to update the data.
- In the sheets of other users, the cell with this username is colored green again (indicating that the username is now free).
##### 2.4. **Working with the database**
- **Main table (database)**:
- Stores all usernames entered during the entire use of the system.
- The second column of the database contains the usernames corresponding to the usernames entered by users.
- The third column shows the time until the username's activity ends (calculates the remaining time until the end of the 7-day period).
- **Connection with Google Sheets**:
- The table linked to a specific Google Sheet is dynamically updated to display the status of each username:
- Red color — username occupied.
- Green color — username free.
- Yellow color — activity time expired, re-entry required.
##### 2.5. **Color indicators**
- Green cell color — username is new, reserved for 7 days.
- Red color — username occupied by another user.
- Yellow color — reservation period expired, update required (removal and re-entry of the username).
#### 3. **Admin panel**
- Ability to add/remove Google Sheets.
- View linked sheets.
- Control the current state of the database (view all active and released usernames).
- Ability to edit and reset the database.
- Set the data monitoring interval (e.g., once a minute).
- View statistics for each sheet and user (the worker who manages this sheet, which has its own name)
- Configure and change the reservation period for each sheet
- User-friendly and simple interface with inline buttons and commands, as well as information output.
#### 5. **Workflow algorithm**
1. **Linking the sheet**: The administrator links the sheet through the admin panel, and the bot starts monitoring it.
2. **Data entry**: The user enters a username in the Google Sheet.
3. **Database check**:
- If the username is new — it is added to the database, and the cell is colored green.
- If the username already exists and is reserved — the cell is colored red.
4. **Status monitoring**:
- After 7 days, the username is released, and the cell color changes.
5. **Updating sheets**: All changes are synchronized between the database and Google Sheets.
Freelancehunt
