tt-booking/docs/DATABASE_SETUP.md

# Database Setup Guide

This guide explains how to set up and manage the database for the Table Tennis Booking System.

## Quick Start

### Full Setup (Recommended for new installations)

```bash
npm run db:setup
```

This will:

-   ✅ Create all database tables
-   ✅ Seed essential data (users, courts, settings, time slots)
-   ✅ Add sample data (bookings, announcements, activity logs)
-   ✅ Display a comprehensive summary

### Essential Data Only

```bash
npm run db:seed
```

This will set up the database with only essential data, no sample bookings or logs.

## Database Scripts Overview

### 🚀 Setup Scripts

| Script              | Command                                   | Description                              |
| ------------------- | ----------------------------------------- | ---------------------------------------- |
| **Full Setup**      | `npm run db:setup`                        | Complete database setup with sample data |
| **Essential Setup** | `npm run db:seed`                         | Database setup with essential data only  |
| **Advanced Setup**  | `tsx scripts/setup-database.ts [options]` | Setup with custom options                |

#### Setup Options:

```bash
tsx scripts/setup-database.ts --help            # Show all options
tsx scripts/setup-database.ts --reset           # Reset database first
tsx scripts/setup-database.ts --essential-only  # No sample data
tsx scripts/setup-database.ts --verbose         # Show detailed output
```

### 🗑️ Reset Scripts

| Script              | Command                             | Description                          |
| ------------------- | ----------------------------------- | ------------------------------------ |
| **Safe Reset**      | `npm run db:reset`                  | Shows warning, requires confirmation |
| **Confirmed Reset** | `npm run db:reset-confirm`          | Immediate reset (destructive!)       |
| **Advanced Reset**  | `tsx scripts/reset-db.ts [options]` | Reset with custom options            |

#### Reset Options:

```bash
tsx scripts/reset-db.ts --help           # Show all options
tsx scripts/reset-db.ts --confirm        # Confirm destructive operation
tsx scripts/reset-db.ts --verbose        # Show detailed output
tsx scripts/reset-db.ts --keep-data      # Preserve data where possible
```

### 🛠️ Drizzle Scripts

| Script             | Command              | Description                        |
| ------------------ | -------------------- | ---------------------------------- |
| **Push Schema**    | `npm run db:push`    | Push schema changes to database    |
| **Run Migrations** | `npm run db:migrate` | Apply migration files              |
| **Studio**         | `npm run db:studio`  | Open Drizzle Studio (database GUI) |

## Database Structure

### Tables Created

1. **users** - User accounts and authentication
2. **courts** - Table tennis courts configuration
3. **bookings** - Court reservations and scheduling
4. **announcements** - System announcements and notifications
5. **time_slots** - Available booking time slots per day
6. **settings** - System configuration and preferences
7. **activity_logs** - User action tracking and audit trail
8. **metrics** - System performance and usage metrics

### Default Data

#### Users

-   **Admin User**: `admin@tabletennis.com` / `admin123`
-   **Test User**: `user@tabletennis.com` / `user123`

#### Courts

-   Court 1 (Active)
-   Court 2 (Active)
-   Court 3 (Active)

#### Time Slots

-   **Sunday**: 12:00 - 17:00
-   **Monday-Thursday**: 18:00 - 23:00
-   **Friday**: 17:00 - 22:00
-   **Saturday**: 10:00 - 18:00

#### Settings

-   Booking window: 14 days
-   Max booking duration: 2 hours
-   Min booking duration: 60 minutes
-   Booking modifications allowed: Yes
-   Modification cutoff: 2 hours before booking

## Usage Examples

### 🆕 New Project Setup

```bash
# Clone the repository
git clone <repository-url>
cd tt-booking

# Install dependencies
npm install

# Set up database with full sample data
npm run db:setup

# Start development server
npm run dev
```

### 🔄 Development Reset

```bash
# Reset and rebuild database
npm run db:reset-confirm
npm run db:setup

# Or combine in one command
tsx scripts/setup-database.ts --reset --verbose
```

### 🚀 Production Setup

```bash
# Essential data only (no sample bookings)
npm run db:seed

# Or with custom options
tsx scripts/setup-database.ts --essential-only --verbose
```

### 🧪 Testing Environment

```bash
# Reset database and add fresh test data
tsx scripts/reset-db.ts --confirm --verbose
tsx scripts/setup-database.ts --verbose
```

## Advanced Configuration

### Custom Time Slots

Edit the time slots in `scripts/setup-database.ts`:

```typescript
const timeSlotData = [
	{ dayOfWeek: 0, startTime: '10:00', endTime: '16:00' }, // Sunday
	{ dayOfWeek: 1, startTime: '17:00', endTime: '22:00' }, // Monday
	// ... customize as needed
];
```

### Custom Settings

Modify default settings in the `seedBasicData` function:

```typescript
const defaultSettings = [
	{ key: 'booking_window_days', value: '7' }, // 7 days ahead
	{ key: 'max_booking_duration_hours', value: '3' }, // 3 hour max
	// ... add more settings
];
```

### Additional Users

Add more users in the `seedBasicData` or `seedSampleData` functions:

```typescript
await db.insert(schema.users).values({
	id: randomUUID(),
	email: 'coach@tabletennis.com',
	name: 'Head',
	surname: 'Coach',
	password: await bcrypt.hash('coach123', 12),
	role: 'user',
	themePreference: 'system',
	createdAt: new Date(now),
	updatedAt: new Date(now),
});
```

## Troubleshooting

### Common Issues

**Database locked error**

```bash
# Close any running applications using the database
# Then reset and recreate
npm run db:reset-confirm
npm run db:setup
```

**Schema mismatch**

```bash
# Push latest schema changes
npm run db:push

# Or reset and rebuild
npm run db:reset-confirm
npm run db:setup
```

**Permission errors**

```bash
# Check file permissions
ls -la sqlite.db

# If needed, fix permissions
chmod 666 sqlite.db
```

### Database Location

The SQLite database file is located at: `./sqlite.db`

You can:

-   View it with any SQLite browser
-   Open it in Drizzle Studio: `npm run db:studio`
-   Back it up by copying the file
-   Remove it completely and recreate with setup scripts

### Getting Help

```bash
# Show help for setup script
tsx scripts/setup-database.ts --help

# Show help for reset script
tsx scripts/reset-db.ts --help

# Show all available npm scripts
npm run
```

## Migration from Old Scripts

The new unified scripts replace the old individual seed scripts:

| Old Script                      | New Equivalent                      |
| ------------------------------- | ----------------------------------- |
| `scripts/seed-data.ts`          | Integrated into `setup-database.ts` |
| `scripts/seed-announcements.ts` | Integrated into `setup-database.ts` |
| `scripts/seed-time-slots.ts`    | Integrated into `setup-database.ts` |
| `scripts/init-admin-data.ts`    | Integrated into `setup-database.ts` |
| `scripts/reset-database.ts`     | Replaced by `reset-db.ts`           |

The old scripts can be safely removed as all functionality is now consolidated in the new, more intelligent setup system.

---

**Need help?** Check the terminal output for detailed information and suggestions, or refer to the help commands above.