Development of a Telegram bot with WebApp for submitting and processing applications

Create a Telegram bot (based on the aiogram library) and a companion WebApp (on AngularJS) that will allow users to submit applications with additional parameters, register, and receive responses from the administrator. The system must store data in local JSON files and also update data in Google Sheets (2 tables) with cell formatting.

---

 1. General project description

 1.1 Bot functionality  

- User registration:  

  - Entering full name and phone number (option to share contact)  

  - Previewing data, editing, and confirming  

  - Saving data in the file `users.json` with statuses: pending, approved, blocked

- Application submission module:  

  - User can submit an application through the WebApp  

  - Saving application data in the file `applications_by_user.json`  

  - Writing data to Google Sheet (table1) with updating application number, date, full name, agricultural enterprise, EDRPOU, group, culture, quantity, location, additional parameters, payment method, currency, price, phone number, user_id  

  - Updating data in table2 (displaying price with the ability to delete cell with row shifting and resetting formatting)

- Administrative module:  

  - Login via the /admin command for users with administrator rights  

  - Menu for viewing applications (pending, approved, deleted)  

  - Ability to approve (approve) or block (block) users  

  - Editing data of approved users (full name, phone number)  

  - Viewing detailed information on applications, confirming, rejecting, deleting (both soft and complete deletion from the file and Google Sheets)  

  - Function for exporting the user database to Google Sheets with formatting (alignment, bold font, automatic column width adjustment)

- Background process (polling):  

  - Periodic checking of Google Sheets for changes in managerial price values  

  - Sending messages to users when the offer is updated, as well as changing application statuses (active, waiting, agreed, confirmed, deleted, rejected)

- Integration with Google Sheets:  

  - Authorization via Service Account using `oauth2client` and `gspread` library  

  - Functions for reading, updating, deleting rows, and formatting cells (using `gspread-formatting`)

 1.2 WebApp functionality  

- Form interface:  

  - Split into several steps:  

    1. Basic data: Entering the name of the agricultural enterprise (optional), EDRPOU code (8 or 10 digits, optional), location (region, district, city) – integration with AngularJS for working with location data (via dropdown lists loaded from a local JSON file or API)  

    2. Application details: Selecting group, culture, additional parameters (depending on the selected culture – different fields for wheat, barley, corn, sunflower, rapeseed, soy, etc.), entering quantity  

    3. Price: Entering desired price (optional), selecting currency and payment method (dynamic update of payment method depending on currency)

- Prefill parameters:  

  - Reading the `data` parameter from the URL for pre-filling the form  

  - Setting data in form fields

- Interaction with Telegram WebApp API:  

  - Using the Telegram WebApp JS library (with functions sendData, close, ready)

  - Sending JSON application data to the bot server via fetch POST request

---

 2. Technical requirements

 2.1 Technology stack  

- Backend (Telegram bot):  

  - Language: Python 3.7+  

  - Framework: aiogram  

  - Libraries: asyncio, logging, json, aiohttp, gspread, oauth2client, gspread-formatting, zoneinfo  

  - Data storage: local JSON files (users.json, applications_by_user.json, config.py)  

  - Integration with Google Sheets via API

- WebApp:  

  - Language: HTML, CSS, JavaScript  

  - Framework: AngularJS (1.x)  

  - Integration with Telegram WebApp API

 2.2 Architecture  

- Telegram bot:  

  - Works on the FSM (Finite State Machine) scheme for managing dialogues  

  - Asynchronous processing of messages and background tasks (poll_manager_proposals, HTTP server)

  - User rights distribution: pending, approved, blocked  

  - Administrative menu for moderating users and applications

- WebApp:  

  - Split into pages (steps) with the ability to navigate "Back/Next"  

  - Dynamic loading of data for dropdown lists  

  - Validation of entered data (formats, minimum requirements)  

  - Sending data via Telegram WebApp API and fetch request to the HTTP server

 2.3 Security  

- Protection of user data when stored in JSON files  

- Access check for administrative commands (ADMINS, obtained from environment variables)  

- Validation of entered data (phone number, EDRPOU codes, numeric formats)

 2.4 Hosting and deployment  

- The bot runs as a separate process (e.g., via systemd or Docker)  

- The WebApp can be hosted on a separate server (or on the same server as the bot)  

- HTTP server (aiohttp) for receiving POST requests from the WebApp

---

 3. Functional blocks and requirements

 3.1 Telegram bot functionality  

- Initialization and configuration:  

  - Reading environment variables: TELEGRAM_TOKEN, ADMINS, GOOGLE_SPREADSHEET_ID, GOOGLE_SPREADSHEET_ID2, API_PORT, DATA_DIR, etc.  

  - Loading credentials for Google API from environment variable (GSPREAD_CREDENTIALS_JSON)

- Working with files:  

  - Creating files users.json, applications_by_user.json, config.py if they do not exist  

  - Functions for reading and writing data (load_users, save_users, load_applications, save_applications)

- User registration and moderation:  

  - Processing /start – checking user status (approved, pending, blocked)  

  - Collecting data for registration (full name, phone number) with preview and editing  

  - Administrative approval or blocking through a special interface

- Application submission and processing:  

  - Receiving data from WebApp (via text message with JSON or content-type WEB_APP_DATA)  

  - Functions for writing application to file and Google Sheets (update_google_sheet, add_application)  

  - Ability to view applications, edit, delete (soft and complete)

- Administrative interface:  

  - Menu for moderation, viewing approved/deleted applications  

  - Detailed view of the application and ability to change statuses  

  - Functions for editing data of approved users

- Background process:  

  - Periodic checking of Google Sheets to update managerial price values  

  - Sending notifications to users when the offer changes  

  - Managing polling pause/resume (POLLING_PAUSED)

- HTTP server:  

  - Receiving POST requests from WebApp for preview data (endpoint: /api/webapp_data)

 3.2 WebApp functionality  

- Form interface with several steps:  

  - Page 1: Basic data (agricultural enterprise, EDRPOU, location via AngularJS dropdown lists)  

  - Page 2: Application details (group, culture, additional parameters depending on culture, quantity)  

  - Page 3: Price, currency, payment method  

- Prefilling:  

  - Reading data from URL parameter (data) and setting values in corresponding fields  

- Data validation:  

  - Checking EDRPOU format (8 or 10 digits)  

  - Checking numeric formats for additional parameters (format XX.X)  

- Interaction with Telegram WebApp API:  

  - Initializing WebApp, sending filled data via sendData  

  - Sending POST request to the server (endpoint /api/webapp_data) to verify received data  

  - Closing WebApp after successful submission

---

 4. Integration with Google Sheets  

- Using Google API with Service Account  

- Accessing two tables (GOOGLE_SPREADSHEET_ID and GOOGLE_SPREADSHEET_ID2)  

- Functions:  

  - update_google_sheet: writing application data, calculating new number, writing date, full name, application data  

  - export_database: exporting the database of approved users to a new sheet with formatting  

  - delete_price_cell_in_table2: deleting the price cell with row shifting and clearing formatting

Please include a realistic minimum price for this bot in your proposals, without underpricing, so we can understand what to expect