# Phase 2: Database Schema

## Migrations & Tables

We have enforced standard Laravel architecture coupled with enterprise features like UUIDs for public-facing models and strict foreign key cascading.

1. `users`:
    - Augmented with: `phone`, `avatar`, `bio`, `status` (active/inactive), `last_login_at`, `softDeletes`.
    - Handles standard authentication out-of-the-box.
    - Traits: `HasRoles`, `SoftDeletes`.
2. `categories`:
    - Columns: `name`, `slug`, `description`, `parent_id`.
    - Recursively relationships for sub-categories via `parent_id`.
3. `courses`:
    - **UUID Primary Key**
    - Columns: `title`, `slug`, `description`, `thumbnail`, `category_id`, `instructor_id` (FK to `users`), `price`, `currency`, `is_free`, `status` (draft/published/archived), `duration_weeks`, `level`, `language`, `published_at`, `softDeletes`.
4. `course_modules`:
    - **UUID Primary Key**
    - Groups lessons. Fields: `course_id`, `title`, `description`, `order_index`.
5. `lessons`:
    - **UUID Primary Key**
    - Standard lecture content. Fields: `module_id`, `title`, `description`, `content_type` (video/pdf/text), `bunny_video_id`, `file_path`, `duration_seconds`, `order_index`, `is_preview`.
6. `payments`:
    - Transaction ledger mapping to courses and user. Fields: `user_id`, `course_id`, `gateway`, `gateway_transaction_id`, `amount`, `currency`, `status`, `paid_at`.
7. `enrollments`:
    - **UUID Primary Key**
    - Maps purchased access. Fields: `user_id`, `course_id`, `payment_id`, `enrolled_at`, `expires_at`, `status`, `progress_percent`.
8. `lesson_progress`:
    - User/Lesson mapping. Fields: `user_id`, `lesson_id`, `watched_seconds`, `completed`, `last_accessed_at`. Includes composite `unique(['user_id', 'lesson_id'])`.
9. `user_devices`:
    - Security / Auth monitoring. Fields: `user_id`, `device_fingerprint`, `ip_address`, `user_agent`, `last_seen_at`, `is_blocked`.
10. `settings`:
    - Global site settings array. Fields: `key`, `value`, `group`.

## Index Strategy & Extensibility
UUIDs ensure that enumeration tracking isn't possible (e.g. going from `/courses/1` to `/courses/2`). We have used `$table->foreignUuid()` to establish rigid cascade logic ensuring no orphaned records exist when a course or module is deleted.

All tables are instantiated as complete Models leveraging built in Laravel attribute casting (`datetime`, `boolean`, `hashed`, `decimal:2`).

## Next Steps
Data structure is now in place. We move to **Phase 3: Admin User & Role Management**, building out the admin dashboard views for orchestrating internal user manipulation, access granting, and security reviewing.