# Database Schema Documentation

All tables use InnoDB engine, utf8mb4_unicode_ci charset, and BIGINT UNSIGNED primary keys with AUTO_INCREMENT unless otherwise noted.

---

## Users & Authentication

### `users`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `full_name` | VARCHAR(160) | Display name |
| `email` | VARCHAR(255) UNIQUE | Login credential |
| `password_hash` | VARCHAR(255) | bcrypt |
| `role` | ENUM('student','editor','admin') | Default: 'student' |
| `status` | ENUM('pending','active','suspended') | 'pending' until email verified |
| `email_verified_at` | DATETIME NULL | Set when verification link is clicked |
| `verification_token` | VARCHAR(100) NULL | Cleared after use |
| `password_reset_token` | VARCHAR(100) NULL | 1-hour expiry |
| `password_reset_expires_at` | DATETIME NULL | |
| `created_at` / `updated_at` | DATETIME | |

### `student_profiles`

One row per student. Joined to `users` on `user_id`.

| Column | Type | Notes |
|---|---|---|
| `user_id` | BIGINT UNSIGNED UNIQUE FK→users | |
| `department` | VARCHAR(120) NULL | |
| `level` | VARCHAR(60) NULL | Year/level label |
| `bio` | TEXT NULL | |
| `profile_photo` | VARCHAR(255) NULL | Filename in uploads/profile-photos/ |

### `rate_limits`

Tracks failed login/register attempts for rate limiting. Rows keyed by `(action, identifier)` where identifier is a hashed IP address.

---

## Content & CMS

### `content`

The central table for news, blog, audio, and video posts. Type-specific fields live in extension tables.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `user_id` | BIGINT UNSIGNED FK→users | Author |
| `type` | ENUM('news','blog','audio','video') | |
| `title` | VARCHAR(220) | |
| `slug` | VARCHAR(255) UNIQUE | URL key, generated from title |
| `summary` | TEXT NULL | Shown on cards and in SEO description |
| `body` | LONGTEXT NULL | Full article text (news/blog only) |
| `featured_image` | VARCHAR(255) NULL | Filename in uploads/featured-images/ |
| `category_id` | BIGINT UNSIGNED FK→categories NULL | |
| `status` | ENUM('draft','submitted','approved','rejected','revision_requested') | Default: 'draft' |
| `rejection_note` | TEXT NULL | Populated by editor on reject/revision |
| `reviewed_by` | BIGINT UNSIGNED FK→users NULL | |
| `reviewed_at` | DATETIME NULL | |
| `published_at` | DATETIME NULL | Set when approved |
| `views_count` | INT UNSIGNED | Incremented per page load |
| `created_at` / `updated_at` | DATETIME | |

### `content_audio`

Extension table for audio posts. One row per audio content item.

| Column | Type | Notes |
|---|---|---|
| `content_id` | BIGINT UNSIGNED PK FK→content | |
| `audio_file_path` | VARCHAR(255) | Filename in uploads/audio-reports/ |
| `duration_seconds` | INT UNSIGNED NULL | |

### `content_video`

Extension table for video posts. One row per video content item.

| Column | Type | Notes |
|---|---|---|
| `content_id` | BIGINT UNSIGNED PK FK→content | |
| `youtube_url` | VARCHAR(500) | Canonical youtube.com/watch?v=... URL |
| `thumbnail` | VARCHAR(255) NULL | Custom thumbnail filename, or null to use YouTube's |

### `categories`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `name` | VARCHAR(100) | |
| `type` | ENUM('news','blog','audio','video','podcast') | Categories are type-scoped |
| `slug` | VARCHAR(100) UNIQUE | |

Pre-seeded with categories for all 5 content types.

### `tags` and `content_tags`

`tags` stores unique tag strings. `content_tags` is the join table (content_id, tag_id). Tags are created on submission if they don't already exist.

---

## Podcasts

### `podcasts`

Separate table from `content` because the storage model is fundamentally different (external audio URL, not uploaded file; no body text; dedicated cover art).

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `user_id` | BIGINT UNSIGNED FK→users | |
| `title` | VARCHAR(220) | |
| `slug` | VARCHAR(255) UNIQUE | |
| `description` | TEXT NULL | |
| `cover_image` | VARCHAR(255) NULL | Filename in uploads/covers/ |
| `audio_url` | VARCHAR(500) NULL | External CDN URL — never a local file |
| `category_id` | BIGINT UNSIGNED FK→categories NULL | |
| `duration_seconds` | INT UNSIGNED NULL | Client-detected from the external URL |
| `status` | ENUM('draft','submitted','approved','rejected','revision_requested') | Same lifecycle as content |
| `rejection_note` | TEXT NULL | |
| `reviewed_by` / `reviewed_at` / `published_at` | As in content | |

### `podcast_analytics`

Insert-only event log. Never updated after creation.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `podcast_id` | BIGINT UNSIGNED FK→podcasts | |
| `event_type` | ENUM('play','download') | |
| `listening_time_seconds` | INT UNSIGNED NULL | Play events only |
| `listener_hash` | VARCHAR(64) | Salted IP+UA hash — no raw IP stored |
| `created_at` | DATETIME | |

Unique key on `(podcast_id, listener_hash, event_type)` — de-duplication happens at the model level, not here.

---

## Radio

### `radio_status`

Singleton table — always exactly one row (`id = 1`). Never INSERT; only UPDATE.

| Column | Type | Notes |
|---|---|---|
| `id` | TINYINT UNSIGNED PK DEFAULT 1 | |
| `is_live` | TINYINT(1) | 0 = off air, 1 = on air |
| `stream_url` | VARCHAR(500) NULL | External HTTPS stream URL |
| `current_presenter` | VARCHAR(120) NULL | |
| `listener_count` | INT UNSIGNED | Decremented with GREATEST(0, count-1) |
| `updated_at` | DATETIME | |

### `radio_programs`

Weekly schedule entries.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `title` | VARCHAR(160) | |
| `presenter_name` | VARCHAR(120) | |
| `day_of_week` | TINYINT | 1=Sunday through 7=Saturday |
| `start_time` / `end_time` | TIME | HH:MM |
| `description` | VARCHAR(255) NULL | |
| `created_at` | DATETIME | |

### `airtime_requests`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `student_id` | BIGINT UNSIGNED FK→users | |
| `requested_date` | DATE | |
| `requested_time_slot` | VARCHAR(40) | Free text, e.g. "3:00 PM – 4:00 PM" |
| `topic` | VARCHAR(255) | |
| `status` | ENUM('pending','approved','rejected') | |
| `review_note` | TEXT NULL | Added in Phase 5 delta |
| `reviewed_by` / `reviewed_at` | As in content | |

---

## Engagement

### `likes`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `content_type` | VARCHAR(20) | news/blog/audio/video/podcast |
| `content_id` | BIGINT UNSIGNED | Polymorphic reference |
| `user_id` | BIGINT UNSIGNED FK→users NULL | Null for guest likes |
| `guest_hash` | VARCHAR(64) NULL | Salted IP+UA hash for guest deduplication |
| `created_at` | DATETIME | |

Unique key on `(content_type, content_id, user_id)` for logged-in likes; separate unique key on `(content_type, content_id, guest_hash)` for guest likes.

### `comments`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `content_type` / `content_id` | Polymorphic | |
| `user_id` | BIGINT UNSIGNED FK→users NULL | |
| `guest_name` | VARCHAR(100) NULL | For unauthenticated commenters |
| `parent_comment_id` | BIGINT UNSIGNED FK→comments NULL | One level of replies only |
| `body` | TEXT | |
| `status` | ENUM('visible','hidden') | Soft-hide, never hard-delete |
| `created_at` | DATETIME | |

### `favorites`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `user_id` | BIGINT UNSIGNED FK→users | |
| `content_type` / `content_id` | Polymorphic | |
| `created_at` | DATETIME | |

Unique key on `(user_id, content_type, content_id)`.

### `shares`

Insert-only event log — one row per share click per platform.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `content_type` / `content_id` | Polymorphic | |
| `platform` | ENUM('whatsapp','facebook','x','telegram') | |
| `created_at` | DATETIME | |

---

## Notifications & Push

### `notifications`

Admin-sent notification log.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `title` | VARCHAR(150) | |
| `body` | TEXT | |
| `type` | VARCHAR(50) | new_podcast / breaking_news / etc. |
| `created_by` | BIGINT UNSIGNED FK→users | Admin who sent it |
| `sent_at` | DATETIME | |
| `created_at` | DATETIME | |

### `push_subscriptions`

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `user_id` | BIGINT UNSIGNED FK→users NULL | Null for anonymous subscribers |
| `endpoint` | VARCHAR(500) UNIQUE | Browser push endpoint URL |
| `p256dh` | TEXT | Browser public key for content encryption |
| `auth_key` | VARCHAR(255) | Authentication secret |
| `created_at` | DATETIME | |

---

## AI Tools

### `ai_tool_settings`

One row per tool, pre-seeded. Never INSERT from application code — only UPDATE.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `tool_name` | VARCHAR(80) UNIQUE | e.g. 'headline_generator' |
| `is_enabled` | TINYINT(1) | Default: 1 |
| `daily_limit_per_user` | SMALLINT UNSIGNED | Default: 10 |
| `api_provider` | ENUM('claude','openai','gemini') | Default: 'claude' |
| `updated_at` | DATETIME | |

### `ai_tool_usage`

Daily usage counters per user per tool.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `user_id` | BIGINT UNSIGNED FK→users | |
| `tool_name` | VARCHAR(80) | |
| `request_count` | SMALLINT UNSIGNED | Incremented atomically via ON DUPLICATE KEY UPDATE |
| `usage_date` | DATE | CURDATE() at call time |
| `created_at` | DATETIME | |

Unique key on `(user_id, tool_name, usage_date)`.

---

## Audit Log

### `audit_logs`

Append-only. Never updated or deleted.

| Column | Type | Notes |
|---|---|---|
| `id` | BIGINT UNSIGNED PK | |
| `user_id` | BIGINT UNSIGNED FK→users | Actor |
| `action` | VARCHAR(80) | e.g. 'content_approved', 'radio_status_updated' |
| `target_type` | VARCHAR(40) NULL | e.g. 'content', 'podcast', 'airtime_request' |
| `target_id` | BIGINT UNSIGNED NULL | ID of the affected row |
| `notes` | TEXT NULL | Human-readable detail |
| `created_at` | DATETIME | |