# Balady Reports Management System

## Overview
REST API service for handling Excel report uploads with Arabic headers, providing automatic data synchronization with MySQL database. Designed to work as a backend service for React applications.

## Features
- 📊 REST API Endpoint for Excel Upload
- 🔄 Automatic UPSERT Operations
- 🔍 Complete Change History Logging
- 🈳 Arabic to English Header Mapping
- 💾 Data Validation and Error Handling

## System Requirements
- Node.js (v14 or higher)
- MySQL Server (5.7 or higher)
- cPanel hosting with Node.js support

## Database Structure

### Main Table (balady)
```sql
- report_number (VARCHAR, Unique Key)
- date_of_creation (VARCHAR)
- management (VARCHAR)
- category (VARCHAR)
- report_status (VARCHAR)
- requestor_name (VARCHAR)
- mobile_number (VARCHAR)
- area (VARCHAR)
- solution_date (VARCHAR)
- description (VARCHAR)
- report_website (VARCHAR)
- last_update_date (VARCHAR)
- supervisor_name (VARCHAR)
```

### Logs Table (balady_logs)
Tracks all changes made to the main table:
```sql
- log_id (AUTO_INCREMENT)
- report_number (VARCHAR)
- field_name (VARCHAR)
- old_value (VARCHAR)
- new_value (VARCHAR)
- change_type (ENUM: 'INSERT', 'UPDATE', 'DELETE')
- changed_at (TIMESTAMP)
- changed_by (VARCHAR)
```

## Arabic Header Mapping
The system automatically maps the following Arabic headers to English:
```javascript
{
    'اسم المقاول/المراقب': 'supervisor_name',
    'اخر تاريخ تحديث': 'last_update_date',
    'موقع البلاغ': 'report_website',
    'الوصف': 'description',
    'تاريخ الحل': 'solution_date',
    'الحي': 'area',
    'رقم الجوال': 'mobile_number',
    'اسم المبلغ': 'requestor_name',
    'حالة البلاغ في النظام': 'report_status',
    'التصنيف التخصصي': 'category',
    'الإدارة': 'management',
    'تاريخ الأنشاء': 'date_of_creation',
    'رقم البلاغ': 'report_number'
}
```

## cPanel Deployment

1. Upload Files to cPanel:
   - Zip all files except `node_modules`
   - Upload to your cPanel directory (e.g., `public_html/balady-api`)
   - Extract the files

2. Set Up Node.js App:
   - In cPanel, go to `Setup Node.js App`
   - Create new application:
     - Node.js version: 14 or higher
     - Application mode: Production
     - Application root: /balady-api
     - Application URL: your-domain.com/balady-api
     - Application startup file: server.js
     - Save and restart app

3. Database Setup:
   - Create MySQL database in cPanel
   - Import SQL files:
     - `create_balady_table_with_upsert.sql`
     - `create_balady_logs_table.sql`
   - Update `db.js` with your cPanel database credentials

4. Configure Environment:
   - Create `.env` file with your settings
   - Set appropriate file permissions (usually 644)

## API Usage

### Upload Excel File
```http
POST https://your-domain.com/api/upload-excel
Content-Type: multipart/form-data

Form Data:
- excel: [Excel File]
```

Example using Axios in React:
```javascript
const formData = new FormData();
formData.append('excel', excelFile);

try {
    const response = await axios.post('https://your-domain.com/api/upload-excel', formData, {
        headers: {
            'Content-Type': 'multipart/form-data'
        }
    });
    
    if (response.data.success) {
        console.log('Upload successful:', response.data.message);
    }
} catch (error) {
    console.error('Upload failed:', error.response?.data?.message || error.message);
}
```

## API Endpoints

### POST /api/upload-excel
- Purpose: Upload and process Excel files
- Input: Excel file (.xls, .xlsx)
- Response: JSON with success/error message

### DELETE /api/delete-all
- Purpose: Delete all records from the database
- Input: None
- Authorization: Required (implement based on your security needs)
- Response: JSON with deletion status and count

## File Structure
```
├── routes/
│   └── upload.js        # Upload route handler
├── uploads/             # Temporary file storage
├── db.js               # Database configuration
├── excelUploader.js    # Excel processing logic
├── server.js           # Main application server
└── README.md           # Documentation
```

## Error Handling
- File size limit: 50MB
- Supported formats: .xls, .xlsx
- Duplicate records: Automatically updated
- Missing data: Handled as NULL
- Invalid formats: Appropriate error messages

## Security Features
- File size restrictions (50MB limit)
- File type validation (.xls, .xlsx only)
- SQL injection prevention through parameterized queries
- Comprehensive error logging
- CORS configuration for React application
- Rate limiting for API endpoints

## Maintenance
- Configure cPanel cron job for uploads cleanup:
  ```bash
  0 0 * * * find /home/username/public_html/balady-api/uploads/* -mtime +1 -exec rm {} \;
  ```
- Monitor log table growth and archive old records
- Set up automated database backups through cPanel
- Monitor Node.js application logs in cPanel

## Support
For API integration support or issues, contact the system administrator.

## Response Format

### Upload Response
```javascript
// Success Response
{
    "success": true,
    "message": "File processed successfully",
    "recordsProcessed": 150,
    "updatedRecords": 50,
    "newRecords": 100
}

// Error Response
{
    "success": false,
    "message": "Error processing file",
    "error": "Detailed error message"
}
```

### Delete All Response
```javascript
// Success Response
{
    "success": true,
    "message": "All records deleted successfully",
    "deletedCount": 150
}

// Error Response
{
    "success": false,
    "message": "Error deleting records",
    "error": "Detailed error message"
}
```
