# 🗄️ MySQL Database Setup Guide - Textile Manager

Complete guide to set up MySQL database for your Textile Manager application, compatible with both localhost development and cPanel hosting.

## 🚀 Quick Start

### Option 1: Automatic Setup (Recommended)

```bash
# 1. Install dependencies
npm install

# 2. Configure environment
cp env.example .env.local
# Edit .env.local with your database credentials

# 3. Run migration and seeding
npm run db:migrate
npm run db:seed

# 4. Start the application
npm run dev
```

### Option 2: Manual Setup

Follow the detailed steps below for manual configuration.

## 📋 Prerequisites

### For Localhost Development:
- ✅ **MySQL 5.7+** or **MariaDB 10.3+**
- ✅ **Node.js 16+**
- ✅ **npm or yarn**

### For cPanel Hosting:
- ✅ **cPanel account with MySQL**
- ✅ **phpMyAdmin access**
- ✅ **Node.js support** (optional)

## 🔧 Localhost Setup

### Step 1: Install MySQL

**Windows:**
```bash
# Download from https://dev.mysql.com/downloads/mysql/
# Or use Chocolatey
choco install mysql

# Or use XAMPP/WAMP for easier setup
```

**macOS:**
```bash
# Using Homebrew
brew install mysql
brew services start mysql

# Or download from MySQL website
```

**Linux (Ubuntu/Debian):**
```bash
sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql
```

### Step 2: Configure MySQL

```bash
# Secure MySQL installation
sudo mysql_secure_installation

# Login to MySQL
mysql -u root -p
```

```sql
-- Create database
CREATE DATABASE textile_manager CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user (optional, for security)
CREATE USER 'textile_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON textile_manager.* TO 'textile_user'@'localhost';
FLUSH PRIVILEGES;

-- Exit MySQL
EXIT;
```

### Step 3: Environment Configuration

Create `.env.local` file:

```env
# Database Configuration
DB_HOST=localhost
DB_USER=textile_user
DB_PASSWORD=your_password
DB_NAME=textile_manager
DB_PORT=3306

# JWT Secret
JWT_SECRET=your-super-secret-jwt-key-here

# Application
NODE_ENV=development
NEXT_PUBLIC_APP_URL=http://localhost:3000
```

### Step 4: Install Dependencies

```bash
npm install
```

### Step 5: Run Migrations

```bash
# Run database migration
npm run db:migrate

# Seed with sample data
npm run db:seed
```

### Step 6: Start Development Server

```bash
npm run dev
```

**Default Login:**
- Email: `admin@textile.com`
- Password: `admin123`

## 🌐 cPanel Setup

### Step 1: Create MySQL Database

1. **Login to cPanel**
2. **Go to "MySQL Databases"**
3. **Create Database:**
   - Database Name: `textile_manager`
   - Note: cPanel may add your username as prefix

4. **Create Database User:**
   - Username: `textile_user`
   - Password: [Generate strong password]

5. **Add User to Database:**
   - Select user and database
   - Grant ALL PRIVILEGES

### Step 2: Import Database Schema

**Option A: Using phpMyAdmin**
1. Open phpMyAdmin from cPanel
2. Select your database
3. Go to "Import" tab
4. Upload `database/schema.sql`
5. Upload `database/seeders/default_data.sql`

**Option B: Using SQL Commands**
```sql
-- Copy contents of database/schema.sql and execute
-- Then copy contents of database/seeders/default_data.sql and execute
```

### Step 3: Configure Environment for cPanel

```env
# cPanel Database Configuration
DB_HOST=localhost
DB_USER=your_cpanel_username_textile_user
DB_PASSWORD=your_database_password
DB_NAME=your_cpanel_username_textile_manager
DB_PORT=3306

# Production settings
NODE_ENV=production
NEXT_PUBLIC_APP_URL=https://yourdomain.com
JWT_SECRET=your-production-jwt-secret
```

## 📊 Database Schema Overview

### Core Tables:

| Table | Purpose | Key Features |
|-------|---------|--------------|
| `shops` | Shop profiles | Multi-tenant support |
| `users` | User accounts | Role-based access |
| `customers` | Customer data | Search & analytics |
| `orders` | Order management | Status tracking |
| `order_items` | Order line items | Detailed breakdown |
| `inventory` | Stock management | Low stock alerts |
| `employees` | Staff management | Role assignments |
| `tailoring_items` | Service catalog | Pricing & categories |
| `income_transactions` | Revenue tracking | Payment methods |
| `expense_transactions` | Cost tracking | Employee expenses |

### Advanced Features:

| Table | Purpose |
|-------|---------|
| `customer_measurements` | Custom measurements |
| `appearance_settings` | UI preferences |
| `notification_settings` | SMS/WhatsApp config |
| `audit_logs` | Change tracking |
| `report_data` | Cached reports |

## 🔧 Database Scripts

### Available Scripts:

```bash
# Database operations
npm run db:migrate    # Run migrations
npm run db:seed      # Seed sample data
npm run db:reset     # Reset database
npm run db:backup    # Create backup
npm run db:test      # Test connection
```

### Manual Operations:

```bash
# Connect to MySQL
mysql -u textile_user -p textile_manager

# Backup database
mysqldump -u textile_user -p textile_manager > backup.sql

# Restore database
mysql -u textile_user -p textile_manager < backup.sql

# Reset database
mysql -u textile_user -p -e "DROP DATABASE textile_manager; CREATE DATABASE textile_manager;"
```

## 📈 Performance Optimization

### Indexes Created:

```sql
-- Customer search optimization
CREATE INDEX idx_customers_shop_name ON customers(shop_id, name);
CREATE INDEX idx_customers_phone ON customers(phone);
CREATE FULLTEXT INDEX idx_customers_search ON customers(name, phone, email);

-- Order performance
CREATE INDEX idx_orders_shop_status_date ON orders(shop_id, status, order_date);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Transaction queries
CREATE INDEX idx_income_shop_date ON income_transactions(shop_id, transaction_date);
CREATE INDEX idx_expense_shop_date ON expense_transactions(shop_id, transaction_date);
```

### Query Optimization:

```sql
-- Analyze table performance
ANALYZE TABLE customers, orders, inventory;

-- Optimize tables
OPTIMIZE TABLE customers, orders, inventory;

-- Check slow queries
SHOW PROCESSLIST;
```

## 🔒 Security Best Practices

### Database Security:

```sql
-- Create read-only user for reports
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT ON textile_manager.* TO 'readonly_user'@'localhost';

-- Limit connections
SET GLOBAL max_connections = 100;

-- Enable SSL (if supported)
SHOW VARIABLES LIKE 'have_ssl';
```

### Application Security:

```env
# Use strong JWT secret
JWT_SECRET=use-a-very-long-random-string-here-at-least-32-characters

# Secure database credentials
DB_PASSWORD=use-a-strong-password-with-special-characters
```

## 🔄 Data Migration

### From localStorage to MySQL:

The application includes automatic migration from localStorage:

```javascript
// Migration runs automatically on first login
// Or manually trigger:
import { migrateFromLocalStorage } from '@/lib/migration';
await migrateFromLocalStorage();
```

### From Other Systems:

```sql
-- Import from CSV
LOAD DATA INFILE 'customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- Bulk insert from JSON
INSERT INTO customers (name, phone, email, shop_id)
SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')),
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.phone')),
    JSON_UNQUOTE(JSON_EXTRACT(data, '$.email')),
    1
FROM json_import_table;
```

## 📊 Monitoring & Maintenance

### Health Checks:

```sql
-- Check database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'textile_manager'
GROUP BY table_schema;

-- Check table status
SELECT 
    table_name,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'textile_manager'
ORDER BY (data_length + index_length) DESC;
```

### Maintenance Tasks:

```sql
-- Weekly cleanup (add to cron)
DELETE FROM audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Monthly optimization
OPTIMIZE TABLE customers, orders, inventory, income_transactions, expense_transactions;

-- Check for unused space
SELECT 
    table_name,
    ROUND(data_free / 1024 / 1024, 2) AS 'Free Space (MB)'
FROM information_schema.tables 
WHERE table_schema = 'textile_manager' 
AND data_free > 0;
```

## 🚨 Troubleshooting

### Common Issues:

1. **Connection Refused:**
   ```bash
   Error: ECONNREFUSED
   
   Solutions:
   - Check if MySQL is running: systemctl status mysql
   - Verify port 3306 is open: netstat -an | grep 3306
   - Check firewall settings
   ```

2. **Access Denied:**
   ```bash
   Error: ER_ACCESS_DENIED_ERROR
   
   Solutions:
   - Verify username/password in .env.local
   - Check user privileges: SHOW GRANTS FOR 'user'@'localhost';
   - Reset password: ALTER USER 'user'@'localhost' IDENTIFIED BY 'newpass';
   ```

3. **Database Not Found:**
   ```bash
   Error: ER_BAD_DB_ERROR
   
   Solutions:
   - Create database: CREATE DATABASE textile_manager;
   - Check database name in .env.local
   - Verify database exists: SHOW DATABASES;
   ```

4. **Migration Fails:**
   ```bash
   Solutions:
   - Check SQL syntax in migration files
   - Ensure proper permissions
   - Run migrations one by one manually
   ```

### Debug Mode:

```bash
# Enable MySQL query logging
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/queries.log';

# Monitor slow queries
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
```

## 📚 API Integration

### Using the API Client:

```javascript
import apiClient from '@/lib/api/client';

// Login
const { data } = await apiClient.login('admin@textile.com', 'admin123');

// Get customers
const customers = await apiClient.getCustomers({ page: 1, limit: 10 });

// Create order
const order = await apiClient.createOrder({
  customer_id: 1,
  items: [{ name: 'Shirt', quantity: 1, price: 500 }],
  // ... other fields
});
```

### Direct Database Access:

```javascript
import { customerService } from '@/lib/database/mysql-service';

// Get customers
const customers = await customerService.findByShop(shopId);

// Create customer
const customer = await customerService.create({
  name: 'John Doe',
  phone: '+1234567890',
  shop_id: shopId
});
```

## ✅ Setup Checklist

- [ ] MySQL installed and running
- [ ] Database and user created
- [ ] Environment variables configured
- [ ] Dependencies installed
- [ ] Migrations executed successfully
- [ ] Sample data seeded
- [ ] Application starts without errors
- [ ] Can login with default credentials
- [ ] Database operations work correctly
- [ ] Performance indexes created
- [ ] Security measures implemented
- [ ] Backup strategy configured

## 🎉 Success!

Your MySQL database is now ready for the Textile Manager application!

**Next Steps:**
1. Change default admin password
2. Configure your shop profile
3. Add your customers and inventory
4. Start processing orders
5. Set up regular backups

**Access Points:**
- **Application:** http://localhost:3000
- **Database:** MySQL on localhost:3306
- **Admin Login:** admin@textile.com / admin123

---

**Need Help?** Check the troubleshooting section or refer to the [cPanel Deployment Guide](./CPANEL_DEPLOYMENT_GUIDE.md) for hosting setup.