# Events Table Migration Plan - Task 2.2

**Task ID:** 09-376
**Linear URL:** https://linear.app/09-07/issue/09-376/22-backend-design-comprehensive-database-migration-plan
**Status:** In Progress
**Priority:** Urgent
**Duration:** 1 hour
**Dependencies:** Task 2.1
**Blocks:** Task 2.3
**Phase:** 2 - Backend Event Model & API

---

## 1. Executive Summary

This document outlines the comprehensive database migration plan for the `events` table to add missing columns that are currently referenced in the `Event` model but not present in the database schema. The migration will ensure data integrity for existing events (particularly Event 123 with 75 seats and Event 999999) while adding new functionality for event management.

---

## 2. Current State Analysis

### 2.1 Existing Events Table Schema

**Created by:** `2022_08_24_152513_create_events_table.php`

**Current Columns:**
- `id` (bigIncrements)
- `club_id` (unsignedBigInteger)
- `name` (string)
- `event_url` (string)
- `start_date` (date)
- `end_date` (date)
- `start_time` (time)
- `voting_end_time` (datetime)
- `description` (text)
- `max_no_of_tickets` (integer)
- `no_of_raffle_ticket` (integer, nullable)
- `raffle_ticket_price` (double, nullable)
- `video` (string, nullable)
- `address` (string)
- `status` (tinyInteger, default 1)
- `is_deleted` (tinyInteger, default 0)
- `created_at` (timestamp)
- `updated_at` (timestamp)

**Recent Addition (2025-09-26):**
`2025_09_26_163916_add_selling_period_columns_to_events_table.php` added:
- `sell_start_date` (date, nullable)
- `sell_start_time` (time, nullable)
- `sell_end_date` (date, nullable)
- `sell_end_time` (time, nullable)

### 2.2 Event Model Fillable Attributes

The `Event` model (`app/Model/Event.php`) declares the following fillable attributes that are **NOT** in the current database schema:

**Publishing & Display:**
- `page_name` (string)
- `sub_title` (string)
- `publish_start_date` (date)
- `publish_start_time` (time)
- `publish_end_date` (date)
- `publish_end_time` (time)
- `is_published` (boolean)
- `publish_website` (boolean)
- `is_featured` (boolean)

**Media & Content:**
- `artists` (text/json)
- `main_page_banner` (string - image path)
- `centered_banner` (string - image path)
- `baner_wide` (string - image path)
- `banner_tablet` (string - image path)
- `banner_phone` (string - image path)
- `destaque_home` (string - image path)
- `imagem_poster` (string - image path)
- `buttom_banner_frame` (string - image path)

**Templates:**
- `ticket_template_id` (unsignedBigInteger, nullable)
- `bracelet_template_id` (unsignedBigInteger, nullable)
- `label_template_id` (unsignedBigInteger, nullable)

**Communication:**
- `inquiry_poll` (text/json, nullable)
- `event_email_thankyou_text` (text, nullable)
- `event_support_thankyou_text` (text, nullable)

**SEO & Metadata:**
- `meta_title` (string, nullable)
- `meta_keyword` (string, nullable)

**Venue Integration:**
- `venue_id` (unsignedBigInteger, nullable)

**Timestamps (already exist):**
- `created_at`
- `updated_at`

---

## 3. Missing Columns Documentation

### 3.1 Publishing & Scheduling Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `page_name` | `string(255)` | NO | - | URL-friendly page name for event |
| `sub_title` | `string(255)` | YES | NULL | Event subtitle/tagline |
| `publish_start_date` | `date` | YES | NULL | When event becomes visible on website |
| `publish_start_time` | `time` | YES | NULL | Exact time event goes live |
| `publish_end_date` | `date` | YES | NULL | When event should be hidden |
| `publish_end_time` | `time` | YES | NULL | Exact time event goes offline |
| `is_published` | `tinyInteger(1)` | NO | 0 | Whether event is currently published |
| `publish_website` | `tinyInteger(1)` | NO | 0 | Whether to show on public website |
| `is_featured` | `tinyInteger(1)` | NO | 0 | Whether event is featured/highlighted |

**Business Logic:**
- Publishing period (`publish_start_date/time` → `publish_end_date/time`) controls when event appears on website
- Selling period (`sell_start_date/time` → `sell_end_date/time`) controls when tickets can be purchased
- Publishing period should typically be earlier than selling period to allow for announcements

### 3.2 Media & Banner Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `artists` | `text` | YES | NULL | JSON array of artist names/details |
| `main_page_banner` | `string(255)` | YES | NULL | Path to main homepage banner image |
| `centered_banner` | `string(255)` | YES | NULL | Path to centered banner (desktop) |
| `baner_wide` | `string(255)` | YES | NULL | Path to wide banner format |
| `banner_tablet` | `string(255)` | YES | NULL | Path to tablet-optimized banner |
| `banner_phone` | `string(255)` | YES | NULL | Path to mobile-optimized banner |
| `destaque_home` | `string(255)` | YES | NULL | Path to featured home section image |
| `imagem_poster` | `string(255)` | YES | NULL | Path to event poster image |
| `buttom_banner_frame` | `string(255)` | YES | NULL | Path to bottom banner frame |

**Storage Considerations:**
- All image paths should be relative to `storage/app/public/events/` or CDN
- Support for multiple banner sizes enables responsive design
- Consider adding image dimension/size metadata in future

### 3.3 Template Reference Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `ticket_template_id` | `unsignedBigInteger` | YES | NULL | FK to ticket printing template |
| `bracelet_template_id` | `unsignedBigInteger` | YES | NULL | FK to wristband template |
| `label_template_id` | `unsignedBigInteger` | YES | NULL | FK to label printing template |

**Foreign Key Considerations:**
- Foreign keys should be added if template tables exist
- Use `onDelete('set null')` to preserve event if template is deleted
- Add indexes for performance

### 3.4 Communication & Content Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `inquiry_poll` | `text` | YES | NULL | JSON structure for event surveys/polls |
| `event_email_thankyou_text` | `text` | YES | NULL | Custom thank-you email content |
| `event_support_thankyou_text` | `text` | YES | NULL | Custom support thank-you message |

### 3.5 SEO Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `meta_title` | `string(255)` | YES | NULL | SEO meta title (falls back to `name`) |
| `meta_keyword` | `string(500)` | YES | NULL | SEO keywords for search engines |

**Note:** Consider adding `meta_description` in future for complete SEO coverage.

### 3.6 Venue Integration Column

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `venue_id` | `unsignedBigInteger` | YES | NULL | FK to venues/halls table |

**Integration Notes:**
- This links to the venue management system
- Should have foreign key to `halls` or `venues` table if it exists
- Supports multi-venue events in the future

---

## 4. Data Preservation Strategy

### 4.1 Critical Test Events

**Event 123: Test Concert 2025**
- **Seats:** 75 seats (50 available, 1 pre-sold: C-05, 24 potentially booked)
- **Purpose:** E2E testing event
- **Data Sensitivity:** CRITICAL - Used in automated test suites
- **Preservation:** Must maintain exact state including all seat reservations and relationships

**Event 999999: Legacy Test Event**
- **Purpose:** QR code validation testing
- **Data Sensitivity:** HIGH - Referenced in unit tests
- **Preservation:** Must maintain ID and basic attributes

### 4.2 Preservation Actions

**Pre-Migration Backup:**
```bash
# Create timestamped backup
./scripts/backup-db.sh

# Verify backup integrity
./scripts/compare-db.sh backup_YYYYMMDD_HHMMSS.sqlite
```

**Data Snapshot Query:**
```sql
-- Capture current state of critical events
SELECT
    id, name, event_url, start_date, club_id, status,
    max_no_of_tickets, created_at, updated_at
FROM events
WHERE id IN (123, 999999);

-- Capture related seat reservations
SELECT
    COUNT(*) as total_reservations,
    SUM(CASE WHEN status = 'booked' THEN 1 ELSE 0 END) as booked_seats,
    event_id
FROM seat_reservations
WHERE event_id IN (123, 999999)
GROUP BY event_id;
```

**Post-Migration Verification:**
```sql
-- Verify no data loss
SELECT
    id, name, event_url, start_date, club_id, status,
    -- New columns should be NULL for existing records
    page_name, sub_title, publish_start_date, venue_id
FROM events
WHERE id IN (123, 999999);

-- Verify relationships intact
SELECT
    e.id as event_id,
    e.name,
    COUNT(sr.id) as seat_count,
    SUM(CASE WHEN sr.status = 'booked' THEN 1 ELSE 0 END) as booked
FROM events e
LEFT JOIN seat_reservations sr ON e.id = sr.event_id
WHERE e.id IN (123, 999999)
GROUP BY e.id, e.name;
```

### 4.3 Default Values Strategy

**For Existing Records:**
- All new columns will be `NULL` or have sensible defaults
- `is_published`, `publish_website`, `is_featured` → `0` (false)
- Media/banner columns → `NULL`
- Template IDs → `NULL`
- Publishing dates → `NULL` (not published by default)

**For New Records:**
- Application layer should set appropriate defaults
- Consider adding database-level defaults for boolean flags

---

## 5. Rollback Strategy

### 5.1 Rollback Mechanisms

**Option 1: Migration Rollback (Preferred)**
```bash
# Roll back the migration
php artisan migrate:rollback --step=1

# Verify rollback
php artisan migrate:status
```

**Option 2: Database Restore (If migration rollback fails)**
```bash
# Restore from pre-migration backup
./scripts/restore-db.sh backup_YYYYMMDD_HHMMSS.sqlite

# Verify restoration
./scripts/compare-db.sh backup_YYYYMMDD_HHMMSS.sqlite
```

**Option 3: Manual Column Removal (Emergency)**
```sql
-- Only if migration rollback and restore fail
ALTER TABLE events
DROP COLUMN page_name,
DROP COLUMN sub_title,
DROP COLUMN publish_start_date,
DROP COLUMN publish_start_time,
DROP COLUMN publish_end_date,
DROP COLUMN publish_end_time,
DROP COLUMN is_published,
DROP COLUMN publish_website,
DROP COLUMN is_featured,
DROP COLUMN artists,
DROP COLUMN main_page_banner,
DROP COLUMN centered_banner,
DROP COLUMN baner_wide,
DROP COLUMN banner_tablet,
DROP COLUMN banner_phone,
DROP COLUMN destaque_home,
DROP COLUMN imagem_poster,
DROP COLUMN buttom_banner_frame,
DROP COLUMN ticket_template_id,
DROP COLUMN bracelet_template_id,
DROP COLUMN label_template_id,
DROP COLUMN inquiry_poll,
DROP COLUMN event_email_thankyou_text,
DROP COLUMN event_support_thankyou_text,
DROP COLUMN meta_title,
DROP COLUMN meta_keyword,
DROP COLUMN venue_id;
```

### 5.2 Rollback Testing

**Pre-Migration Test:**
```bash
# Test migration on SQLite test database
APP_ENV=testing php artisan migrate:safe --step=1

# Test rollback
APP_ENV=testing php artisan migrate:rollback --step=1

# Verify clean rollback
sqlite3 database/testing.sqlite "PRAGMA table_info(events);"
```

### 5.3 Rollback Decision Criteria

**Trigger Rollback If:**
- Migration fails mid-execution
- Data integrity checks fail post-migration
- Critical test events (123, 999999) are corrupted
- Application errors occur due to schema changes
- Performance degrades significantly

**Do Not Rollback If:**
- Minor issues in new column defaults (fixable with UPDATE queries)
- Non-critical frontend display issues (fixable in application layer)

---

## 6. Migration Checklist

### 6.1 Pre-Migration Phase

- [ ] **Review Dependencies**
  - [ ] Verify Task 2.1 is complete
  - [ ] Check for any pending migrations
  - [ ] Review Event model for any recent changes

- [ ] **Backup & Safety**
  - [ ] Run `./scripts/backup-db.sh` and verify success
  - [ ] List and verify backup: `ls -lh database/backups/`
  - [ ] Test backup integrity: `./scripts/compare-db.sh backup_YYYYMMDD_HHMMSS.sqlite`
  - [ ] Document backup filename for recovery

- [ ] **Environment Preparation**
  - [ ] Set `APP_ENV=testing` for dry run
  - [ ] Verify database connection: `php artisan db:show`
  - [ ] Check disk space: `df -h`
  - [ ] Verify Laravel version and compatibility

- [ ] **Code Review**
  - [ ] Review migration file syntax
  - [ ] Verify column types match requirements
  - [ ] Check for typos in column names (especially `baner_wide` vs `banner_wide`)
  - [ ] Ensure down() method properly removes all columns

### 6.2 Migration Creation Phase

- [ ] **Generate Migration**
  - [ ] Run: `php artisan make:migration add_missing_columns_to_events_table`
  - [ ] Open migration file in editor
  - [ ] Add all 26 missing columns to `up()` method
  - [ ] Add corresponding `dropColumn()` calls to `down()` method

- [ ] **Migration Code Review**
  - [ ] Verify all column types are correct
  - [ ] Confirm nullable/non-nullable settings
  - [ ] Check default values for boolean columns
  - [ ] Verify no foreign key constraints are missing
  - [ ] Ensure proper index creation for FK columns

### 6.3 Testing Phase

- [ ] **Dry Run on Testing Database**
  - [ ] Run: `APP_ENV=testing php artisan migrate:safe --step=1`
  - [ ] Verify migration completes without errors
  - [ ] Check SQLite schema: `sqlite3 database/testing.sqlite "PRAGMA table_info(events);"`
  - [ ] Verify all 26 columns were added

- [ ] **Test Rollback**
  - [ ] Run: `APP_ENV=testing php artisan migrate:rollback --step=1`
  - [ ] Verify all columns removed cleanly
  - [ ] Check for any orphaned data or errors

- [ ] **Data Integrity Testing**
  - [ ] Seed Event 123 if not present
  - [ ] Run migration again
  - [ ] Verify Event 123 data preserved
  - [ ] Check seat_reservations relationship intact

### 6.4 Production Migration Phase

- [ ] **Final Pre-Migration Checks**
  - [ ] Verify backup is recent (< 5 minutes old)
  - [ ] Put application in maintenance mode (optional): `php artisan down`
  - [ ] Stop any background jobs/queues
  - [ ] Notify team migration is starting

- [ ] **Execute Migration**
  - [ ] Run: `php artisan migrate:safe --force`
  - [ ] Monitor output for errors
  - [ ] Wait for completion confirmation

- [ ] **Immediate Post-Migration Checks**
  - [ ] Verify migration status: `php artisan migrate:status`
  - [ ] Check critical events exist: Query Event 123 and 999999
  - [ ] Verify seat_reservations intact
  - [ ] Check application logs for errors

### 6.5 Verification Phase

- [ ] **Database Verification**
  - [ ] Run verification queries (Section 4.2)
  - [ ] Compare row counts before/after
  - [ ] Verify all relationships intact
  - [ ] Check for any NULL constraint violations

- [ ] **Application Testing**
  - [ ] Test event listing pages
  - [ ] Test event creation form
  - [ ] Test event editing with new fields
  - [ ] Verify API endpoints return new fields
  - [ ] Check frontend event display

- [ ] **Performance Checks**
  - [ ] Run: `EXPLAIN SELECT * FROM events WHERE id = 123;`
  - [ ] Check query performance hasn't degraded
  - [ ] Monitor application response times

### 6.6 Post-Migration Phase

- [ ] **Documentation**
  - [ ] Update Event model documentation
  - [ ] Document new column purposes in wiki
  - [ ] Update API documentation if exposed
  - [ ] Record migration completion time

- [ ] **Cleanup**
  - [ ] Remove maintenance mode: `php artisan up`
  - [ ] Restart background jobs/queues
  - [ ] Clear application cache: `php artisan cache:clear`
  - [ ] Notify team migration complete

- [ ] **Monitoring**
  - [ ] Monitor error logs for 24 hours
  - [ ] Watch for any foreign key violations
  - [ ] Check for application exceptions
  - [ ] Verify automated tests still pass

### 6.7 Rollback Checklist (If Needed)

- [ ] **Immediate Actions**
  - [ ] Put application in maintenance mode
  - [ ] Stop all writes to database
  - [ ] Document exact error/reason for rollback

- [ ] **Execute Rollback**
  - [ ] Option 1: `php artisan migrate:rollback --step=1`
  - [ ] Option 2: `./scripts/restore-db.sh backup_YYYYMMDD_HHMMSS.sqlite`
  - [ ] Verify rollback success

- [ ] **Post-Rollback**
  - [ ] Verify Event 123 and 999999 intact
  - [ ] Test application functionality
  - [ ] Review migration code for issues
  - [ ] Plan corrective action

---

## 7. Migration Implementation

### 7.1 Migration File Template

**Filename:** `database/migrations/YYYY_MM_DD_HHMMSS_add_missing_columns_to_events_table.php`

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('events', function (Blueprint $table) {
            // Publishing & Display
            $table->string('page_name')->nullable()->after('event_url');
            $table->string('sub_title')->nullable()->after('name');
            $table->date('publish_start_date')->nullable()->after('sell_end_time');
            $table->time('publish_start_time')->nullable()->after('publish_start_date');
            $table->date('publish_end_date')->nullable()->after('publish_start_time');
            $table->time('publish_end_time')->nullable()->after('publish_end_date');
            $table->tinyInteger('is_published')->default(0)->after('status');
            $table->tinyInteger('publish_website')->default(0)->after('is_published');
            $table->tinyInteger('is_featured')->default(0)->after('publish_website');

            // Media & Banners
            $table->text('artists')->nullable()->after('description');
            $table->string('main_page_banner')->nullable()->after('video');
            $table->string('centered_banner')->nullable()->after('main_page_banner');
            $table->string('baner_wide')->nullable()->after('centered_banner');
            $table->string('banner_tablet')->nullable()->after('baner_wide');
            $table->string('banner_phone')->nullable()->after('banner_tablet');
            $table->string('destaque_home')->nullable()->after('banner_phone');
            $table->string('imagem_poster')->nullable()->after('destaque_home');
            $table->string('buttom_banner_frame')->nullable()->after('imagem_poster');

            // Templates
            $table->unsignedBigInteger('ticket_template_id')->nullable()->after('max_no_of_tickets');
            $table->unsignedBigInteger('bracelet_template_id')->nullable()->after('ticket_template_id');
            $table->unsignedBigInteger('label_template_id')->nullable()->after('bracelet_template_id');

            // Communication
            $table->text('inquiry_poll')->nullable()->after('artists');
            $table->text('event_email_thankyou_text')->nullable()->after('inquiry_poll');
            $table->text('event_support_thankyou_text')->nullable()->after('event_email_thankyou_text');

            // SEO
            $table->string('meta_title')->nullable()->after('is_featured');
            $table->string('meta_keyword', 500)->nullable()->after('meta_title');

            // Venue Integration
            $table->unsignedBigInteger('venue_id')->nullable()->after('club_id');

            // Indexes for performance
            $table->index('page_name');
            $table->index('is_published');
            $table->index('is_featured');
            $table->index('venue_id');
            $table->index('ticket_template_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('events', function (Blueprint $table) {
            // Drop indexes first
            $table->dropIndex(['page_name']);
            $table->dropIndex(['is_published']);
            $table->dropIndex(['is_featured']);
            $table->dropIndex(['venue_id']);
            $table->dropIndex(['ticket_template_id']);

            // Drop columns
            $table->dropColumn([
                'page_name',
                'sub_title',
                'publish_start_date',
                'publish_start_time',
                'publish_end_date',
                'publish_end_time',
                'is_published',
                'publish_website',
                'is_featured',
                'artists',
                'main_page_banner',
                'centered_banner',
                'baner_wide',
                'banner_tablet',
                'banner_phone',
                'destaque_home',
                'imagem_poster',
                'buttom_banner_frame',
                'ticket_template_id',
                'bracelet_template_id',
                'label_template_id',
                'inquiry_poll',
                'event_email_thankyou_text',
                'event_support_thankyou_text',
                'meta_title',
                'meta_keyword',
                'venue_id',
            ]);
        });
    }
};
```

### 7.2 Post-Migration Model Update

Ensure `Event` model's `$fillable` array matches new schema (already correct).

**Optional: Add casts for JSON columns:**
```php
protected $casts = [
    'artists' => 'array',
    'inquiry_poll' => 'array',
    'is_published' => 'boolean',
    'publish_website' => 'boolean',
    'is_featured' => 'boolean',
];
```

---

## 8. Risk Assessment

### 8.1 Risk Matrix

| Risk | Probability | Impact | Mitigation |
|------|-------------|--------|------------|
| Data loss during migration | Low | Critical | Pre-migration backup, tested rollback |
| Event 123 corruption | Low | High | Specific verification queries, seeder backup |
| Application downtime | Medium | Medium | Maintenance mode, off-peak execution |
| Foreign key conflicts | Low | Medium | Nullable columns, no strict FK constraints |
| Performance degradation | Low | Low | Indexed key columns |
| Migration rollback failure | Very Low | High | Manual rollback SQL prepared |

### 8.2 Success Criteria

**Migration Successful If:**
- ✅ All 26 columns added without errors
- ✅ Event 123 and all 75 seats intact
- ✅ Event 999999 preserved
- ✅ No data loss in existing columns
- ✅ seat_reservations relationships maintained
- ✅ Application functions normally with new schema
- ✅ Rollback tested and works on testing environment

---

## 9. Timeline

**Estimated Duration:** 1 hour (as specified)

| Phase | Duration | Description |
|-------|----------|-------------|
| Pre-Migration | 10 min | Backup, verification, prep |
| Migration Creation | 15 min | Generate and code migration |
| Testing (SQLite) | 15 min | Test on testing database |
| Production Migration | 5 min | Execute on production |
| Verification | 10 min | Post-migration checks |
| Documentation | 5 min | Update docs, notify team |

**Total:** ~60 minutes

---

## 10. Next Steps (Task 2.3)

After successful completion of this migration:

1. **Update API Endpoints** to expose new fields
2. **Update Frontend Forms** to allow editing new columns
3. **Implement Publishing Logic** for publish_start/end dates
4. **Add Validation Rules** for new fields in controllers
5. **Update Seeders** to include realistic data for new columns
6. **Create Admin UI** for managing banners and templates
7. **Implement SEO Features** using meta_title and meta_keyword

---

## 11. Appendix

### 11.1 Column Count Summary

- **Original columns:** 18
- **Added 2025-09-26:** 4 (selling period)
- **New columns (this migration):** 26
- **Total after migration:** 48 columns

### 11.2 Related Files

- Migration: `database/migrations/YYYY_MM_DD_HHMMSS_add_missing_columns_to_events_table.php`
- Model: `app/Model/Event.php`
- Seeder: `database/seeders/E2ETestSeeder.php`
- Backup script: `scripts/backup-db.sh`
- Restore script: `scripts/restore-db.sh`

### 11.3 Contact & Support

**For Issues:**
- Check Laravel logs: `storage/logs/laravel.log`
- Check MySQL error log
- Review migration status: `php artisan migrate:status`
- Contact backend team lead

---

**Document Version:** 1.0
**Last Updated:** 2025-10-12
**Author:** Backend Team
**Reviewed By:** [Pending]
**Approved By:** [Pending]