# Database — Calendar Booking Platform

MySQL 8 schema and deployment scripts for the calendar booking application.

## Structure

```
database/
├── migrations/
│   ├── 000_schema_migrations.sql   # Migration tracker
│   └── 001_initial_schema.sql      # All application tables
├── scripts/
│   └── create_database.sql         # Manual CREATE DATABASE (admin)
├── deploy.js                       # Node migration runner
├── deploy.sh                       # Shell wrapper
└── package.json
```

## Tables Created

| Table | Purpose |
|-------|---------|
| `users` | Host accounts |
| `availability_schedules` | Weekly working hours |
| `availability_date_overrides` | Holiday / special hours |
| `event_types` | Bookable meeting types |
| `calendar_connections` | Google / Microsoft / Apple sync |
| `bookings` | Appointments |
| `calendar_event_syncs` | External calendar event IDs |
| `busy_blocks` | Cached busy intervals |
| `audit_logs` | Security audit trail |
| `embed_configs` | Third-party embed widget settings |
| `api_keys` | Server-to-server API keys |
| `schema_migrations` | Applied migration versions |

## Prerequisites

- Node.js 20+
- Credentials in `../node/.env`:

```env
DB_HOST=192.187.106.194
DB_PORT=3306
DB_USER=dialertest
DB_PASSWORD="your_password"
DB_NAME=cal_gventure
```

## Deploy to Server

```bash
cd database
chmod +x deploy.sh
./deploy.sh deploy
```

Or directly:

```bash
cd database
npm install
npm run deploy
```

## Other Commands

```bash
./deploy.sh verify   # Test connection only
./deploy.sh status   # Show applied migrations and tables
```

## Manual Deploy (mysql CLI)

If you have the MySQL client installed:

```bash
mysql -h "$DB_HOST" -u "$DB_USER" -p "$DB_NAME" < migrations/000_schema_migrations.sql
mysql -h "$DB_HOST" -u "$DB_USER" -p "$DB_NAME" < migrations/001_initial_schema.sql
```

## Adding New Migrations

1. Create `migrations/002_description.sql`
2. Run `./deploy.sh deploy` — only new files are applied

## Related Docs

- [MySQL Developer Guide](../docs/mysql.md)
- [Node.js Developer Guide](../docs/node.md)