# Database Setup (SQLyog)

The apps **do not** create the database, user, or tables automatically.  
MySQL in Docker only creates the **root** account. You set up everything else with **SQLyog** (or any MySQL client).

**How you start MySQL:** follow **[manual-steps.html](manual-steps.html)** (or [MANUAL_STEPS.md](MANUAL_STEPS.md)) only  
(`docker volume create` + `docker run mysql:8.0` — not compose).

---

## 1. Start MySQL first (manual)

### PHP path (MySQL on host port **3306**)

```bash
docker volume create ba-docker-mysql
docker network create ba-docker-net   # if not already created

docker run -d \
  --name ba-docker-db \
  --network ba-docker-net \
  --network-alias db \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -v ba-docker-mysql:/var/lib/mysql \
  -p 3306:3306 \
  mysql:8.0

docker logs -f ba-docker-db
# Look for: "ready for connections" then Ctrl+C
```

### Node.js path (MySQL on host port **3307**)

```bash
docker volume create ba-docker-mysql-node
docker network create ba-docker-net

docker run -d \
  --name ba-docker-db \
  --network ba-docker-net \
  --network-alias db \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -v ba-docker-mysql-node:/var/lib/mysql \
  -p 3307:3306 \
  mysql:8.0

docker logs -f ba-docker-db
```

> **Fresh start (wipe DB data):**
>
> ```bash
> docker rm -f ba-docker-db
> docker volume rm ba-docker-mysql        # or ba-docker-mysql-node
> # then create volume + docker run again
> ```

---

## 2. Connect SQLyog to Docker MySQL

Open **SQLyog** → **New Connection** (or File → New Connection).

| Setting | PHP path | Node.js path |
|---------|----------|--------------|
| **MySQL Host Address** | `127.0.0.1` or `localhost` | `127.0.0.1` or `localhost` |
| **Username** | `root` | `root` |
| **Password** | `rootpass` | `rootpass` |
| **Port** | `3306` | `3307` |
| **Database** | *(leave empty)* | *(leave empty)* |

Click **Test Connection** → should succeed → **Connect**.

### If connection fails

| Problem | What to try |
|---------|-------------|
| Can't connect | Confirm `docker compose ps` shows `db` is **Up** |
| Wrong port | PHP = **3306**, Node = **3307** |
| Access denied | Password is exactly `rootpass` (no spaces) |
| Still fails | From WSL: `docker compose exec db mysql -uroot -prootpass -e "SELECT 1;"` |

---

## 3. Run these SQL commands (exact)

In SQLyog: open a **New Query** window (while connected as `root`) and run **all** of the statements below.

You can select everything and press **F9** (or click Execute).

```sql
-- =========================================================
-- 1) Create the database
-- =========================================================
CREATE DATABASE IF NOT EXISTS userdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- =========================================================
-- 2) Create the app user (password: apppass)
--    '%' allows connections from other Docker containers
-- =========================================================
CREATE USER IF NOT EXISTS 'appuser'@'%' IDENTIFIED BY 'apppass';

-- =========================================================
-- 3) Give appuser full rights only on userdb
-- =========================================================
GRANT ALL PRIVILEGES ON userdb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

-- =========================================================
-- 4) Create the users table (used by both PHP and Node apps)
-- =========================================================
USE userdb;

CREATE TABLE IF NOT EXISTS users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100)  NOT NULL,
  email      VARCHAR(100)  NOT NULL UNIQUE,
  password   VARCHAR(255)  NOT NULL,
  file_name  VARCHAR(255)  DEFAULT NULL,
  created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### What each part does

| SQL | Purpose |
|-----|---------|
| `CREATE DATABASE userdb` | Database the apps connect to (`DB_NAME=userdb`) |
| `CREATE USER 'appuser'@'%'` | Login the apps use (`DB_USER` / `DB_PASS`) |
| `GRANT ... ON userdb.*` | Lets `appuser` read/write only this database |
| `CREATE TABLE users` | Stores name, email, **hashed** password, optional file name |

---

## 4. Verify in SQLyog

Run:

```sql
SHOW DATABASES;
-- You should see userdb

USE userdb;
SHOW TABLES;
-- You should see users

DESCRIBE users;
-- Columns: id, name, email, password, file_name, created_at

SELECT user, host FROM mysql.user WHERE user = 'appuser';
-- Should show appuser with host %
```

In the left panel you can also expand **userdb → Tables → users**.

---

## 5. Start the web app (after DB is ready)

Use the full `docker build` + `docker run` steps in **[MANUAL_STEPS.md](MANUAL_STEPS.md)**.

- PHP browser: **http://localhost:8080**
- Node browser: **http://localhost:3000**

---

## 6. Optional: useful SQLyog / SQL commands later

### See all registered users

```sql
USE userdb;
SELECT id, name, email, password, file_name, created_at FROM users;
```

The `password` column shows a **hash** (PHP: starts with `$2y$…`; Node: `salt:hash`), not plain text.

### Delete all users (keep table)

```sql
USE userdb;
TRUNCATE TABLE users;
```

### Drop everything and start over

```sql
DROP DATABASE IF EXISTS userdb;
DROP USER IF EXISTS 'appuser'@'%';
FLUSH PRIVILEGES;
```

Then run the full setup SQL from **section 3** again.

### Change appuser password (if you want)

```sql
ALTER USER 'appuser'@'%' IDENTIFIED BY 'your_new_password';
FLUSH PRIVILEGES;
```

If you change it, also update `DB_PASS` in that path’s `docker-compose.yml` and restart:

```bash
docker compose up -d
```

---

## Credentials summary (must match the apps)

| Item | Value |
|------|--------|
| Root user (SQLyog setup only) | `root` |
| Root password | `rootpass` |
| Database name | `userdb` |
| App user | `appuser` |
| App password | `apppass` |
| PHP MySQL host port | `3306` |
| Node MySQL host port | `3307` |
| Table name | `users` |

These match the environment variables in both `docker-compose.yml` files:

```yaml
DB_NAME: userdb
DB_USER: appuser
DB_PASS: apppass
```

---

## Order of work (checklist)

1. Follow **[MANUAL_STEPS.md](MANUAL_STEPS.md)**: pull, network, volumes, build app image
2. `docker run` MySQL; wait until ready
3. Connect SQLyog as `root` / `rootpass` on the correct port
4. Run the SQL in **section 3**
5. Confirm `userdb` and `users` exist
6. `docker run` the app container
7. Open the browser and use CRUD