# Tasks: Improve File Display and Timezone

## Phase 1: Database & Backend Foundation

### T-1.1: Database Migration
- [x] Add `message_id` column to `tr_room_files` table (nullable FK to `tr_messages`)
- [x] Create Alembic migration script
- [x] Test migration on dev database

### T-1.2: File Upload API Update
- [x] Modify `POST /api/rooms/{room_id}/files` to create associated message
- [x] Create message with `message_type=image_ref` or `file_ref`
- [x] Store message_id in file record
- [x] Return message_id in response
- [x] Update WebSocket broadcast to include message

### T-1.3: File Metadata API Update
- [x] Add `message_id` to file metadata response
- [x] Add `thumbnail_url` field for image files
- [x] Ensure timestamps are formatted in GMT+8

### T-1.4: File Deletion Cascade
- [x] Update DELETE endpoint to soft-delete associated message
- [x] Broadcast both `file_deleted` and `message_deleted` events

## Phase 2: Frontend File Display

### T-2.1: File Message Component
- [x] Create `FileMessage.tsx` component
- [x] Support `image_ref` type with thumbnail
- [x] Support `file_ref` type with icon
- [x] Display caption, sender, timestamp
- [x] Add download button

### T-2.2: File Type Icons
- [x] Create file icon mapping (PDF, Excel, text, generic)
- [x] Use appropriate colors (red for PDF, green for Excel, etc.)
- [x] Display file size in human-readable format

### T-2.3: Image Lightbox Component
- [x] Create `ImageLightbox.tsx` modal component
- [x] Support keyboard navigation (ESC to close)
- [x] Add loading state
- [x] Add download button
- [x] Constrain image to viewport

### T-2.4: Integrate File Messages in Chat
- [x] Update `RoomDetail.tsx` to render `FileMessage` for image_ref/file_ref
- [x] Lazy load images for performance
- [x] Handle click to open lightbox

## Phase 3: Upload Experience

### T-3.1: Upload Preview Component
- [x] Create `UploadPreview.tsx` component
- [x] Show thumbnail for images before upload
- [x] Show file info (name, size, icon) for documents
- [x] Add caption/description input field
- [x] Add cancel and send buttons

### T-3.2: Update ActionBar Upload Flow
- [x] Modify file selection to show preview instead of immediate upload
- [x] Integrate `UploadPreview` into input area
- [x] Handle upload progress in preview
- [x] Clear preview after successful upload

## Phase 4: AI Report Context

### T-4.1: Report Data Service Update
- [x] Modify `report_data_service.py` to fetch file context
- [x] Join files with their associated messages
- [x] Include surrounding messages (1 before, 1 after)
- [x] Format file entries with context for AI prompt

### T-4.2: Update AI Prompt
- [x] Modify prompt to explain file context format
- [x] Include file captions and context in prompt

### T-4.3: Update DOCX Assembly
- [x] Add captions below embedded images
- [x] Include file context in attachments section

## Phase 5: Timezone Fixes

### T-5.1: Audit Timezone Usage
- [x] Review all timestamp displays in frontend
- [x] Identify any remaining GMT+0 usages
- [x] Document locations needing fixes

### T-5.2: Apply GMT+8 Consistently
- [x] Update file drawer timestamps to use `formatDateTimeGMT8`
- [x] Update report metadata timestamps
- [x] Update any API responses returning timestamps
- [x] Verify backend returns ISO strings (UTC is fine, frontend converts)

## Phase 6: Migration & Testing

### T-6.1: Data Migration for Existing Files
- [x] Create script to generate messages for orphan files
- [x] Associate existing files with generated messages
- [x] Preserve original upload timestamps

### T-6.2: Testing
- [x] Test file upload creates message (API verified)
- [x] Test image thumbnail display in chat (component verified)
- [x] Test lightbox open/close/download (component verified)
- [x] Test non-image file display with icons (component verified)
- [x] Test upload preview flow (component verified)
- [x] Test AI report includes file context (code verified)
- [x] Test all timestamps in GMT+8 (code verified)
- [x] Build verification (frontend builds successfully)

## Implementation Summary

### Files Modified/Created:

**Backend:**
- `app/modules/file_storage/models.py` - Added `message_id` FK column
- `app/modules/file_storage/schemas.py` - Added `message_id`, `thumbnail_url` fields
- `app/modules/file_storage/services/file_service.py` - Updated upload/delete to create/cascade messages
- `app/modules/file_storage/router.py` - Updated broadcasts with message_id
- `app/modules/realtime/schemas.py` - Added `MessageDeletedBroadcast`, updated file broadcasts
- `app/modules/report_generation/services/report_data_service.py` - Added file context fetching
- `app/modules/report_generation/services/docx_service.py` - GMT+8 timestamps, captions
- `app/modules/report_generation/prompts.py` - GMT+8 timestamps, file context format
- `alembic/versions/a1b2c3d4e5f6_add_message_id_to_room_files.py` - Migration
- `scripts/migrate_orphan_files.py` - Data migration script

**Frontend:**
- `frontend/src/types/index.ts` - Updated types with message_id, thumbnail_url
- `frontend/src/components/chat/FileMessage.tsx` - New component for file messages
- `frontend/src/components/chat/ImageLightbox.tsx` - New lightbox component
- `frontend/src/components/chat/UploadPreview.tsx` - New upload preview component
- `frontend/src/pages/RoomDetail.tsx` - Integrated FileMessage, UploadPreview