# MySQL Integration Summary - Prediksi Stok Bahan Kue ## ✅ What's Done ### Backend (Flask) - NEW FILES & UPDATES #### New Files Created: 1. **`database_setup.py`** - Script untuk initialize MySQL database - Buat database `prediksi_stok_db` - Buat 3 tables: `products`, `transactions`, `predictions` - Insert 8 default products 2. **`MYSQL_SETUP.md`** - Comprehensive setup guide - MySQL installation instructions - Database setup steps (automatic & manual) - API endpoint documentation - Troubleshooting guide #### Updated Files: 1. **`requirements.txt`** - Added MySQL dependencies ``` flask-sqlalchemy==3.0.0 PyMySQL==1.1.0 ``` 2. **`app.py`** - Added 6 new endpoints - `GET /products` - Ambil daftar produk - `GET /products/` - Ambil 1 produk by ID - `POST /transactions` - Simpan transaksi - `GET /transactions` - Ambil history transaksi - `POST /predictions` - Simpan prediction results - Updated `/info` endpoint ### Frontend (Flutter) - UPDATES #### Updated Files: 1. **`lib/services/ml_service.dart`** - Added 5 new methods - `getProducts()` - Get products from API - `getProduct(id)` - Get single product - `saveTransaction()` - Save transaction to MySQL - `getTransactions()` - Get transaction history - `savePrediction()` - Save prediction to MySQL 2. **`lib/screens/transaction_screen.dart`** - Integrated with API - `_submitTransaction()` now calls API - Added loading state during save - Show success/error messages - Data di-save ke MySQL, bukan cuma local ## 📋 Architecture ``` ┌─────────────────┐ │ FLUTTER APP │ │ (Mobile) │ └────────┬────────┘ │ ┌────────▼────────┐ │ Flask API │ │ (Python) │ │ on localhost: │ │ 5000 │ └────────┬────────┘ │ ┌────────▼────────┐ │ MySQL Database │ │ localhost:3306 │ └─────────────────┘ Data Flow: User Input → Flutter → API → MySQL Database ``` ## 🚀 Quick Start (3 Steps) ### Step 1: Install MySQL & Setup Database ```bash # Navigate to ml_model folder cd c:\fluuter.u\permintaandanprediksi_stok_bahan_kue\finalproject\ml_model # Install Python dependencies pip install -r requirements.txt # Setup MySQL database python database_setup.py ``` **Expected Output:** ``` ✅ Database 'prediksi_stok_db' created successfully ✅ Products table created successfully ✅ Transactions table created successfully ✅ Predictions table created successfully ✅ Inserted 8 default products ✅ Database setup completed successfully! ``` ### Step 2: Start Flask API ```bash # From ml_model folder python app.py ``` **Expected Output:** ``` Starting Prediksi Stok API Model: RandomForest Accuracy (R²): 0.9964 Endpoints: /health, /metadata, /info, /prediksi, /batch-prediksi, /products, /transactions, /predictions Access API at: http://localhost:5000 ``` ### Step 3: Run Flutter App ```bash # From finalproject folder flutter run ``` ## 🔌 API Endpoints ### Products ``` GET /products Response: { "status": "success", "total": 8, "products": [ {"id": 1, "name": "Tepung Terigu 1kg", "category": "Tepung", "price": 15000, "stock": 45, ...}, ... ] } GET /products/1 Response: { "status": "success", "product": {"id": 1, "name": "Tepung Terigu 1kg", ...} } ``` ### Transactions ``` POST /transactions Body: { "product_name": "Tepung Terigu 1kg", "category": "Tepung", "quantity": 5, "unit_price": 15000, "total_price": 75000, "transaction_date": "2024-04-05" } Response: { "status": "success", "message": "Transaction saved successfully", "transaction_id": 1 } GET /transactions?limit=100&offset=0 Response: { "status": "success", "total": 10, "transactions": [...] } GET /transactions?product_name=Tepung Response: {...} // Filtered by product name ``` ### Predictions ``` POST /predictions Body: { "product_name": "Tepung Terigu 1kg", "category": "Tepung", "unit_price": 15000, "prediction_date": "2024-04-05", "predicted_quantity": 45, "raw_value": 44.8, "estimated_total_price": 672000, "accuracy_r2": 0.9964, "error_mae": 2.51 } Response: { "status": "success", "message": "Prediction saved successfully", "prediction_id": 1 } ``` ## 📊 Database Schema ### Products Table ```sql CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, category VARCHAR(100) NOT NULL, price INT NOT NULL, stock INT NOT NULL DEFAULT 0, status VARCHAR(50) NOT NULL DEFAULT 'tersedia', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ``` ### Transactions Table ```sql CREATE TABLE transactions ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(255) NOT NULL, category VARCHAR(100) NOT NULL, quantity INT NOT NULL, unit_price INT NOT NULL, total_price INT NOT NULL, transaction_date DATE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` ### Predictions Table ```sql CREATE TABLE predictions ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(255) NOT NULL, category VARCHAR(100) NOT NULL, unit_price INT NOT NULL, prediction_date DATE NOT NULL, predicted_quantity INT, raw_value DOUBLE, estimated_total_price INT, accuracy_r2 DOUBLE, error_mae DOUBLE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` ## 🔄 Flutter Integration Examples ### Example 1: Get All Products ```dart List> products = await MLService.getProducts(); ``` ### Example 2: Save Transaction ```dart bool success = await MLService.saveTransaction( productName: 'Tepung Terigu 1kg', category: 'Tepung', quantity: 5, unitPrice: 15000, totalPrice: 75000, transactionDate: '2024-04-05', ); ``` ### Example 3: Get Transaction History ```dart List> transactions = await MLService.getTransactions( limit: 50, offset: 0, productName: null, ); ``` ### Example 4: Save Prediction ```dart bool success = await MLService.savePrediction( productName: 'Tepung Terigu 1kg', category: 'Tepung', unitPrice: 15000, predictionDate: '2024-04-05', predictedQuantity: 45, rawValue: 44.8, estimatedTotalPrice: 672000, accuracyR2: 0.9964, errorMae: 2.51, ); ``` ## 📝 Files Modified/Created ### Backend ``` ml_model/ ├── database_setup.py ✨ NEW ├── MYSQL_SETUP.md ✨ NEW ├── app.py ✏️ UPDATED (added 6 endpoints) └── requirements.txt ✏️ UPDATED (added MySQL libs) ``` ### Frontend ``` lib/ ├── services/ │ └── ml_service.dart ✏️ UPDATED (added 5 methods) └── screens/ └── transaction_screen.dart ✏️ UPDATED (integrated with API) ``` ## ✅ Verification Checklist Before running the app: - [ ] MySQL server installed & running - [ ] Database setup completed (`python database_setup.py`) - [ ] Flask API running (`python app.py`) on port 5000 - [ ] Can access `http://localhost:5000/health` in browser - [ ] Can access `http://localhost:5000/products` in browser - [ ] Flutter app can connect to API ## 🐛 Common Issues & Solutions ### Issue: "Can't connect to MySQL server" **Solution**: Check MySQL is running ```bash # Windows: Check Services # macOS: brew services list # Linux: sudo systemctl status mysql ``` ### Issue: "Database 'prediksi_stok_db' doesn't exist" **Solution**: Run the database setup script ```bash python database_setup.py ``` ### Issue: "Access denied for user 'root'@'localhost'" **Solution**: Update database credentials in `app.py` and `database_setup.py` ```python DB_PASSWORD = 'your_mysql_password' ``` ### Issue: "Port 5000 already in use" **Solution**: Change port in `app.py` ```python app.run(port=5001) # Use different port ``` ## 🔐 Security Notes For production use: 1. Never use `root` user without password 2. Create dedicated database user with restricted privileges 3. Use environment variables for credentials 4. Enable SSL/TLS for connections 5. Implement API authentication (JWT, OAuth) 6. Add input validation & SQL injection protection Example: ```python # Use environment variables import os DB_PASSWORD = os.getenv('DB_PASSWORD', 'default_password') ``` ## 📚 Next Steps 1. ✅ Test all endpoints with Postman 2. ✅ Test Transaction Screen with real data 3. ✅ Implement Prediction Screen API integration 4. ✅ Add local caching for offline support 5. ✅ Setup authentication & user management 6. ✅ Implement data sync & backup 7. ✅ Optimize query performance with indexes 8. ✅ Add comprehensive error handling ## 📞 Support Lihat dokumentasi lengkap di: `ml_model/MYSQL_SETUP.md` Untuk questions atau issues, check logs: ```bash # Flask API logs python app.py # Check console output # MySQL logs # Windows: MySQL Workbench → Administration → Server Logs # macOS/Linux: /var/log/mysql/error.log ``` --- **Integration Status: ✅ COMPLETE!** Data flow sekarang: - Flutter → API → MySQL Database - Semua transactions & predictions tersimpan di database - Bisa diakses dari mana saja - Persistent data untuk analysis