# Dreamvora — Database Design

Engine: **MySQL / MariaDB**, charset `utf8mb4`, collation `utf8mb4_unicode_ci`,
engine InnoDB (FK support). Access exclusively via PDO prepared statements.

> Status: **implemented** (Phase 4). DDL in `database/schema.sql`, seeder in
> `database/seeders/seed.php`. The public site reads live data via `App\Services\Content`
> (Phase 5) and falls back to `DemoData` automatically when the DB is unavailable.
>
> Setup: `mysql -u root -e "CREATE DATABASE dreamvora CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"`
> then `mysql -u root dreamvora < database/schema.sql` and `php database/seeders/seed.php`.

## Tables

### admins
| column | type | notes |
|--------|------|-------|
| id | BIGINT PK AI | |
| name | VARCHAR(120) | |
| email | VARCHAR(190) UNIQUE | login |
| password | VARCHAR(255) | `password_hash()` (bcrypt) |
| status | TINYINT | 1 active / 0 disabled |
| last_login_at | DATETIME NULL | |
| created_at, updated_at | DATETIME | |

### categories
| column | type | notes |
| id | BIGINT PK AI |
| name | VARCHAR(120) |
| slug | VARCHAR(140) UNIQUE | indexed |
| description | TEXT NULL |
| image | VARCHAR(255) NULL |
| status | TINYINT | 1/0 |
| sort_order | INT DEFAULT 0 |
| created_at, updated_at | DATETIME |

Seed: Caregiving, Language, Skills.

### courses
| column | type | notes |
| id | BIGINT PK AI |
| category_id | BIGINT FK → categories(id) | ON DELETE RESTRICT; indexed |
| title | VARCHAR(180) |
| slug | VARCHAR(200) UNIQUE | indexed |
| short_description | VARCHAR(300) |
| description | MEDIUMTEXT NULL |
| duration | VARCHAR(80) NULL |
| eligibility | VARCHAR(255) NULL |
| training_mode | VARCHAR(120) NULL |
| fee | VARCHAR(80) NULL | text, so "On request" is valid; not money math |
| image | VARCHAR(255) NULL |
| featured | TINYINT DEFAULT 0 | index (featured, status) |
| status | TINYINT DEFAULT 1 | 1 active / 0 inactive (soft state) |
| sort_order | INT DEFAULT 0 |
| created_at, updated_at | DATETIME |

### course_features
| id PK | course_id FK→courses ON DELETE CASCADE | feature VARCHAR(200) | sort_order INT |

### course_learning_outcomes
| id PK | course_id FK→courses ON DELETE CASCADE | outcome VARCHAR(255) | sort_order INT |

### enquiries
| column | type | notes |
| id | BIGINT PK AI |
| course_id | BIGINT NULL FK → courses(id) | ON DELETE SET NULL; indexed |
| name | VARCHAR(120) |
| phone | VARCHAR(40) |
| email | VARCHAR(190) NULL |
| message | TEXT |
| contact_preference | VARCHAR(40) NULL |
| status | ENUM('new','contacted','in_progress','completed') DEFAULT 'new' | indexed |
| ip_address | VARCHAR(45) NULL | rate-limiting/audit |
| created_at, updated_at | DATETIME |

### gallery
| id PK | title VARCHAR(160) | image VARCHAR(255) | category VARCHAR(80) NULL | sort_order INT | status TINYINT | created_at, updated_at |

### testimonials
| id PK | name VARCHAR(120) | role VARCHAR(120) NULL | course VARCHAR(160) NULL | content TEXT | rating TINYINT NULL | image VARCHAR(255) NULL | status TINYINT | created_at, updated_at |

> Testimonials are **never seeded with fabricated data**. Table ships empty.

### settings
| id PK | setting_key VARCHAR(80) UNIQUE | setting_value TEXT NULL | updated_at DATETIME |

Seed keys: site_name, tagline, phone, whatsapp, email, address, hours,
facebook, instagram, tiktok, youtube, map_url, seo_title, seo_description.

## Relationships
- `courses.category_id` → `categories.id` (many-to-one)
- `course_features.course_id`, `course_learning_outcomes.course_id` → `courses.id` (cascade)
- `enquiries.course_id` → `courses.id` (nullable, set null on delete)

## Indexes
- Unique: `categories.slug`, `courses.slug`, `admins.email`, `settings.setting_key`
- Lookup: `courses(featured,status)`, `courses(category_id)`, `enquiries(status)`, `enquiries(created_at)`

## Conventions
- Timestamps on every table.
- Soft-state via `status` (activate/deactivate) rather than hard deletes for
  courses/categories/gallery/testimonials.
- Slugs: lowercase, hyphenated, unique, validated, regenerated safely on rename.