# Database Setup Guide

## ⚠️ Database Connection is Required

**The server will NOT start without a database connection.** This is by design - the POS system is tightly coupled to the database.

## Quick Setup Steps

### Step 1: Create/Configure .env File

```bash
cd server
cp .env.example .env
```

### Step 2: Edit .env with Your MySQL Credentials

Open `.env` file and update these values:

```env
# Database Configuration
DB_DIALECT=mysql
DB_HOST=localhost
DB_PORT=3306
DB_USER=root                    # Your MySQL username
DB_PASSWORD=your_password       # Your MySQL password (leave empty if no password)
DB_NAME=xyz_pos_db              # Database name to create
DB_NAME_TEST=xyz_pos_db_test    # Test database name
DB_LOGGING=false                # Set to 'true' to see SQL queries in development
```

### Step 3: Create MySQL Database

**Option A: Using MySQL Command Line**

```bash
mysql -u root -p
```

Then in MySQL:

```sql
CREATE DATABASE xyz_pos_db;
CREATE DATABASE xyz_pos_db_test;
EXIT;
```

**Option B: Using MySQL Workbench / phpMyAdmin**

Create a new database named `xyz_pos_db` (and optionally `xyz_pos_db_test` for testing).

### Step 4: Verify MySQL is Running

**Linux:**
```bash
sudo systemctl status mysql
# If not running: sudo systemctl start mysql
```

**macOS:**
```bash
brew services list | grep mysql
# If not running: brew services start mysql
```

**Windows:**
Check MySQL service in Services panel or use XAMPP/WAMP control panel.

### Step 5: Start Server

The server will automatically:
1. Connect to the database
2. Create all tables (in development mode)
3. Set up all relationships

```bash
npm run dev
```

## Troubleshooting Connection Errors

### Error: Access denied for user 'root'@'localhost'

**Possible causes:**
1. **Wrong password** - Check your `.env` file `DB_PASSWORD` setting
2. **No password set** - If MySQL root has no password, set `DB_PASSWORD=` (empty) in `.env`
3. **MySQL not running** - Start MySQL service (see Step 4)
4. **Wrong username** - Verify `DB_USER` in `.env` matches your MySQL username

**Solution:**
```bash
# Test connection manually
mysql -u root -p
# If this works, use the same username/password in .env
```

### Error: Unknown database 'xyz_pos_db'

**Solution:** Create the database first (see Step 3).

### Error: Cannot connect to MySQL server

**Possible causes:**
1. MySQL service not running
2. Wrong host/port
3. Firewall blocking connection

**Solutions:**
- Verify MySQL is running: `sudo systemctl status mysql` or `brew services list`
- Check host: Should be `localhost` or `127.0.0.1`
- Check port: Default MySQL port is `3306`
- Verify firewall settings allow localhost connections

### Error: ER_NOT_SUPPORTED_AUTH_MODE

**Solution:** If using MySQL 8.0+, you may need to update the user authentication:

```sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;
```

## Development vs Production

### Development Mode (`NODE_ENV=development`)

- Automatically creates tables if they don't exist
- Alters tables if schema changes are detected (use with caution)
- Can enable SQL logging with `DB_LOGGING=true`

### Production Mode (`NODE_ENV=production`)

- Database connection is **required** (server exits if connection fails)
- Tables are **NOT** auto-created (use migrations instead)
- SQL logging is disabled by default
- Connection pool is optimized (max: 20 connections)

## Database Schema

The server will automatically create these tables on first run (development mode):
- customers, products, product_variants
- inventories, inventory_items, inventory_movements
- vendors, purchase_orders, grns, grn_items
- boms, bom_items, production_orders
- sales, sale_items
- payments, fiscal_receipts
- returns, return_items
- system_logs

## Security Notes

1. **Never commit `.env` file** - It's already in `.gitignore`
2. **Use strong passwords** in production
3. **Create dedicated database user** for production (don't use root)
4. **Restrict database user permissions** to only necessary operations

## Alternative: Using Different Database

The system uses Sequelize ORM which supports:
- MySQL / MariaDB (current default)
- PostgreSQL
- SQLite
- MSSQL

To use a different database, update:
1. `DB_DIALECT` in `.env` (e.g., `postgres`, `sqlite`)
2. Install appropriate driver: `npm install pg` (for PostgreSQL) or `npm install sqlite3`
3. Update `config/database.js` if needed for dialect-specific options

---

**Remember:** The server MUST have a valid database connection to start. This ensures data integrity and prevents running the POS system without a database.
