Texa AI: Bot Telegram AI, Database Excel, dan REST API untuk Bisnis
# 🤖 Texa AI Telegram Bot + Database
Telegram Bot dengan AI Chat (Gemini), Excel Import, Database Management, dan REST API.
## ✨ Features
### 🎯 AI Chat Features
- ✅ Conversational AI dengan Gemini 2.0 Flash
- ✅ Memory system (ingat percakapan sebelumnya)
- ✅ Multi API Key dengan auto-rotation
- ✅ Analisis gambar, video, dan audio
- ✅ Gen Z friendly personality
### 📊 Database Features
- ✅ Import data dari Excel (.xlsx, .xls)
- ✅ SQLite database dengan indexing
- ✅ Search multi-filter (produk, username, lokasi, dll)
- ✅ Statistics & analytics
- ✅ Export data ke JSON
### 🌐 REST API
- ✅ Full CRUD operations
- ✅ Advanced search & filtering
- ✅ File upload endpoint
- ✅ Statistics endpoint
- ✅ Export endpoint
## 📦 Installation
```bash
# Clone repository
git clone <your-repo-url>
cd texa-telegram-bot
# Install dependencies
npm install
# Setup environment variables
cp .env.example .env
nano .env
```
## ⚙️ Configuration
Create `.env` file:
```env
# Telegram Bot Token (from @BotFather)
TELEGRAM_BOT_TOKEN=your_telegram_bot_token
# Gemini API Keys (get from https://makersuite.google.com/app/apikey)
GEMINI_API_KEY_1=your_first_api_key
GEMINI_API_KEY_2=your_second_api_key
GEMINI_API_KEY_3=your_third_api_key
# API Server Port (default: 3000)
API_PORT=3000
```
## 🚀 Usage
### Start Bot Only
```bash
npm start
```
### Start API Server Only
```bash
npm run api
```
### Start Both (Recommended)
```bash
npm run both
```
### Development Mode
```bash
# Bot with auto-reload
npm run dev
# API with auto-reload
npm run dev:api
```
## 📱 Telegram Bot Commands
### Basic Commands
- `/start` - Start bot
- `/help` - Show all commands
- `/forget` - Clear conversation history
### Data Commands
- `/search [query]` - Search orders
- `/stats` - Show statistics
- `/import` - Import Excel file instructions
### Search Examples
```
/search helm scoopy
/search username ifandi94
/search provinsi jakarta
/search resi SPXID051570673117
```
### Direct Search (without /search)
```
25070105C0T40C (search by order number)
helm scoopy (search by product)
username ifandi94 (search by username)
provinsi jakarta (search by province)
```
## 🌐 REST API Endpoints
### Base URL
```
http://localhost:3000/api
```
### Endpoints
#### Health Check
```http
GET /api/health
```
#### Get All Orders
```http
GET /api/orders?limit=50&username=ifandi94&provinsi=jakarta
```
**Query Parameters:**
- `limit` - Number of results (default: 50)
- `no_pesanan` - Order number
- `username` - Username filter
- `nama_produk` - Product name
- `provinsi` - Province
- `kota` - City
- `status` - Order status
- `no_resi` - Tracking number
#### Get Single Order
```http
GET /api/orders/:noPesanan
```
#### Advanced Search
```http
POST /api/orders/search
Content-Type: application/json
{
"nama_produk": "helm",
"provinsi": "jakarta",
"limit": 100
}
```
#### Get Statistics
```http
GET /api/statistics
```
#### Import Excel File
```http
POST /api/import
Content-Type: multipart/form-data
file: [your-excel-file.xlsx]
```
#### Get Import Logs
```http
GET /api/import-logs
```
#### Get Orders by Product
```http
GET /api/products/:productName/orders?limit=50
```
#### Get Orders by Province
```http
GET /api/provinces/:province/orders?limit=50
```
#### Get Orders by City
```http
GET /api/cities/:city/orders?limit=50
```
#### Get Orders by Username
```http
GET /api/users/:username/orders?limit=50
```
#### Export Data as JSON
```http
GET /api/export/json?limit=1000
```
## 📊 Database Schema
### Table: orders
```sql
- id (INTEGER, PRIMARY KEY)
- no_pesanan (TEXT, UNIQUE)
- status_pesanan (TEXT)
- nama_produk (TEXT)
- username_pembeli (TEXT)
- nama_penerima (TEXT)
- alamat_pengiriman (TEXT)
- kota_kabupaten (TEXT)
- provinsi (TEXT)
- total_pembayaran (REAL)
- ... (49 columns total)
```
### Table: import_logs
```sql
- id (INTEGER, PRIMARY KEY)
- filename (TEXT)
- total_records (INTEGER)
- success_count (INTEGER)
- failed_count (INTEGER)
- import_date (DATETIME)
- status (TEXT)
- error_message (TEXT)
```
## 📁 Project Structure
```
├── bot-enhanced.js # Main Telegram Bot
├── api-server.js # REST API Server
├── database.js # Database Manager
├── package.json # Dependencies
├── .env # Configuration
├── data/
│ └── orders.db # SQLite Database
├── logs/
│ ├── bot.log # Bot logs
│ └── memory_backup.json
├── uploads/ # Temporary file uploads
└── media/ # Media files
├── images/
├── videos/
└── audio/
```
## 🔧 Testing
### Test API with cURL
```bash
# Health check
curl http://localhost:3000/api/health
# Get orders
curl http://localhost:3000/api/orders?limit=10
# Get statistics
curl http://localhost:3000/api/statistics
# Search orders
curl -X POST http://localhost:3000/api/orders/search \
-H "Content-Type: application/json" \
-d '{"nama_produk":"helm","limit":5}'
# Import Excel
curl -X POST http://localhost:3000/api/import \
-F "[email protected]_20250731.xlsx"
```
### Test API with Postman
1. Import Postman Collection (coming soon)
2. Set base URL: `http://localhost:3000`
3. Test each endpoint
## 📈 Excel File Format
Your Excel file must have these columns:
- No. Pesanan
- Status Pesanan
- No. Resi
- Nama Produk
- Username (Pembeli)
- Nama Penerima
- Alamat Pengiriman
- Kota/Kabupaten
- Provinsi
- Total Pembayaran
- ... (see sample file)
## 🐛 Troubleshooting
### Bot tidak merespon
1. Check Telegram token
2. Check internet connection
3. Check logs: `./logs/bot.log`
### Database error
1. Check database file: `./data/orders.db`
2. Check permissions
3. Delete database and restart
### API error
1. Check port 3000 is not in use
2. Check logs in console
3. Test with curl/Postman
### Import gagal
1. Check Excel file format
2. Check file size < 50MB
3. Check column names match exactly
## 📝 License
MIT License
## 👨💻 Author
Created with ❤️ by [Your Name]
## 🙏 Credits
- [node-telegram-bot-api](https://github.com/yagop/node-telegram-bot-api)
- [Google Generative AI](https://ai.google.dev/)
- [SheetJS](https://sheetjs.com/)
- [Express.js](https://expressjs.com/)
- [SQLite3](https://www.sqlite.org/)
## 📞 Support
Need help? Create an issue or contact [@your_username](https://t.me/your_username)
---
Made with 💖 and ☕
