# ✅ READY TO START TASK 3.2: Schema & Models

**Date:** 2025-10-12
**Status:** 🟢 ALL GREEN - GO FOR IMPLEMENTATION
**Task:** Linear 09-387 - Task 3.2 Schema & Models
**Duration:** 6 hours
**Dependencies:** Task 3.1 ✅ COMPLETE

---

## 🎯 Quick Start Guide

### Step 1: Create Backup (30 seconds)

```bash
cd /Users/charlie/code/showprima
./scripts/backup-db.sh
```

**Expected Output:**
```
Backup Created Successfully!
Backup file: backup_20251012_HHMMSS.sql
Database size: ~20MB
  - Events: 3
  - Orders: 178
  - Seat Reservations: 199
  - Users: 1155
```

### Step 2: Verify Event 1 Data (30 seconds)

```bash
php -r "
require 'vendor/autoload.php';
\$app = require_once 'bootstrap/app.php';
\$app->make('Illuminate\Contracts\Console\Kernel')->bootstrap();

echo '=== EVENT 1 PRE-MIGRATION SNAPSHOT ===\n';
echo 'Seats: ' . DB::table('seats')->where('event_id', 1)->count() . \"\n\";
echo 'Reservations: ' . DB::table('seat_reservations')->where('event_id', 1)->count() . \"\n\";
echo 'Orders: ' . DB::table('orders')->where('event_id', 1)->count() . \"\n\";
echo 'Template: ' . DB::table('venue_templates')->where('event_id', 1)->where('is_active', 1)->count() . \"\n\";
"
```

**Expected Output:**
```
=== EVENT 1 PRE-MIGRATION SNAPSHOT ===
Seats: 1155
Reservations: 78
Orders: 164
Template: 1
```

### Step 3: Start Task 3.2 Implementation

See implementation checklist below ⬇️

---

## 📋 Task 3.2: Schema & Models - Implementation Checklist

**Duration:** 6 hours
**Linear:** https://linear.app/09-07/issue/09-387

### Part A: Create Migrations (2 hours)

**Migration 1: Create venues table**
```bash
php artisan make:migration create_venues_table
```

**File:** `database/migrations/YYYY_MM_DD_HHMMSS_create_venues_table.php`

```php
<?php

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

class CreateVenuesTable extends Migration
{
    public function up()
    {
        Schema::create('venues', function (Blueprint $table) {
            $table->id();

            // Venue Identity
            $table->string('name');
            $table->string('slug')->unique();
            $table->string('display_name')->nullable();

            // Location
            $table->text('address')->nullable();
            $table->string('city', 100)->nullable();
            $table->string('state', 100)->nullable();
            $table->string('country', 100)->nullable();
            $table->string('postal_code', 20)->nullable();
            $table->decimal('latitude', 10, 8)->nullable();
            $table->decimal('longitude', 11, 8)->nullable();

            // Venue Information
            $table->text('description')->nullable();
            $table->integer('capacity')->default(0);
            $table->string('venue_type', 50)->nullable();

            // Contact
            $table->string('contact_name')->nullable();
            $table->string('contact_email')->nullable();
            $table->string('contact_phone', 50)->nullable();

            // Media
            $table->string('image_url')->nullable();
            $table->string('floor_plan_url')->nullable();

            // Metadata
            $table->json('amenities')->nullable();
            $table->json('metadata')->nullable();

            // Status
            $table->boolean('is_active')->default(true);

            // Timestamps
            $table->timestamps();
            $table->softDeletes();

            // Indexes
            $table->index('slug');
            $table->index('is_active');
            $table->index('city');
            $table->index('venue_type');
        });
    }

    public function down()
    {
        Schema::dropIfExists('venues');
    }
}
```

---

**Migration 2: Create event_venues join table**
```bash
php artisan make:migration create_event_venues_table
```

**File:** `database/migrations/YYYY_MM_DD_HHMMSS_create_event_venues_table.php`

```php
<?php

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

class CreateEventVenuesTable extends Migration
{
    public function up()
    {
        Schema::create('event_venues', function (Blueprint $table) {
            $table->id();

            // Relationships
            $table->unsignedBigInteger('event_id');
            $table->unsignedBigInteger('venue_id');
            $table->unsignedBigInteger('venue_template_id')->nullable();

            // Configuration
            $table->boolean('is_primary')->default(false);
            $table->integer('display_order')->default(0);

            // Capacity
            $table->integer('capacity_override')->nullable();
            $table->integer('available_capacity')->nullable();

            // Timestamps
            $table->timestamps();

            // Foreign Keys
            $table->foreign('event_id')->references('id')->on('events')->onDelete('cascade');
            $table->foreign('venue_id')->references('id')->on('venues')->onDelete('cascade');
            $table->foreign('venue_template_id')->references('id')->on('venue_templates')->onDelete('set null');

            // Indexes
            $table->unique(['event_id', 'venue_id']);
            $table->index('event_id');
            $table->index('venue_id');
            $table->index('is_primary');
        });
    }

    public function down()
    {
        Schema::dropIfExists('event_venues');
    }
}
```

---

**Migration 3: Add venue_id to venue_templates**
```bash
php artisan make:migration add_venue_id_to_venue_templates
```

**File:** `database/migrations/YYYY_MM_DD_HHMMSS_add_venue_id_to_venue_templates.php`

```php
<?php

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

class AddVenueIdToVenueTemplates extends Migration
{
    public function up()
    {
        Schema::table('venue_templates', function (Blueprint $table) {
            // Add venue_id column (nullable for backward compatibility)
            $table->unsignedBigInteger('venue_id')->nullable()->after('id');

            // Add new metadata columns
            $table->string('template_type', 50)->default('standard')->after('name');
            $table->integer('version')->default(1)->after('template_type');
            $table->string('status', 20)->default('draft')->after('version');

            // Add indexes
            $table->index('venue_id');
            $table->index('status');

            // Foreign key (nullable for now)
            $table->foreign('venue_id')->references('id')->on('venues')->onDelete('set null');
        });
    }

    public function down()
    {
        Schema::table('venue_templates', function (Blueprint $table) {
            $table->dropForeign(['venue_id']);
            $table->dropIndex(['venue_id']);
            $table->dropIndex(['status']);

            $table->dropColumn(['venue_id', 'template_type', 'version', 'status']);
        });
    }
}
```

---

**Test Migrations:**
```bash
# Run migrations
php artisan migrate

# Verify tables created
php artisan tinker --execute="
echo 'Venues table: ' . (Schema::hasTable('venues') ? 'EXISTS' : 'MISSING') . \"\n\";
echo 'Event_venues table: ' . (Schema::hasTable('event_venues') ? 'EXISTS' : 'MISSING') . \"\n\";
echo 'venue_id column: ' . (Schema::hasColumn('venue_templates', 'venue_id') ? 'EXISTS' : 'MISSING') . \"\n\";
"
```

**Expected Output:**
```
Venues table: EXISTS
Event_venues table: EXISTS
venue_id column: EXISTS
```

---

### Part B: Create Models (2 hours)

**Model 1: Venue**

**File:** `app/Model/Venue.php`

```php
<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Venue extends Model
{
    use SoftDeletes;

    protected $fillable = [
        'name', 'slug', 'display_name', 'address', 'city', 'state',
        'country', 'postal_code', 'latitude', 'longitude', 'description',
        'capacity', 'venue_type', 'contact_name', 'contact_email',
        'contact_phone', 'image_url', 'floor_plan_url', 'amenities',
        'metadata', 'is_active'
    ];

    protected $casts = [
        'amenities' => 'array',
        'metadata' => 'array',
        'is_active' => 'boolean',
        'latitude' => 'decimal:8',
        'longitude' => 'decimal:11',
        'capacity' => 'integer',
    ];

    // Relationships
    public function venueTemplates()
    {
        return $this->hasMany(VenueTemplate::class, 'venue_id');
    }

    public function eventVenues()
    {
        return $this->hasMany(EventVenue::class, 'venue_id');
    }

    public function events()
    {
        return $this->belongsToMany(Event::class, 'event_venues')
                    ->withPivot('venue_template_id', 'is_primary', 'capacity_override')
                    ->withTimestamps();
    }

    // Get published template
    public function publishedTemplate()
    {
        return $this->hasOne(VenueTemplate::class, 'venue_id')
                    ->where('status', 'published')
                    ->where('is_active', true)
                    ->latest('version');
    }

    // Auto-generate slug
    protected static function boot()
    {
        parent::boot();

        static::creating(function ($venue) {
            if (empty($venue->slug) && !empty($venue->name)) {
                $venue->slug = \Str::slug($venue->name);

                // Ensure uniqueness
                $originalSlug = $venue->slug;
                $counter = 1;
                while (static::where('slug', $venue->slug)->exists()) {
                    $venue->slug = $originalSlug . '-' . $counter;
                    $counter++;
                }
            }
        });
    }
}
```

---

**Model 2: EventVenue**

**File:** `app/Model/EventVenue.php`

```php
<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;

class EventVenue extends Model
{
    protected $table = 'event_venues';

    protected $fillable = [
        'event_id', 'venue_id', 'venue_template_id',
        'is_primary', 'display_order', 'capacity_override', 'available_capacity'
    ];

    protected $casts = [
        'is_primary' => 'boolean',
        'display_order' => 'integer',
        'capacity_override' => 'integer',
        'available_capacity' => 'integer',
    ];

    // Relationships
    public function event()
    {
        return $this->belongsTo(Event::class, 'event_id');
    }

    public function venue()
    {
        return $this->belongsTo(Venue::class, 'venue_id');
    }

    public function venueTemplate()
    {
        return $this->belongsTo(VenueTemplate::class, 'venue_template_id');
    }

    // Get effective capacity (override or venue default)
    public function getEffectiveCapacity()
    {
        return $this->capacity_override ?? $this->venue->capacity;
    }
}
```

---

**Model 3: Update VenueTemplate**

**File:** `app/Model/VenueTemplate.php` (UPDATE EXISTING)

Add to fillable:
```php
protected $fillable = [
    'venue_id',      // NEW
    'event_id',      // DEPRECATED (will remove in Phase 4)
    'name', 'template_type', 'version', 'status', // NEW
    // ... rest of existing fields
];

protected $casts = [
    'version' => 'integer',
    // ... rest of existing casts
];

// NEW: Relationship to venues
public function venue()
{
    return $this->belongsTo(Venue::class, 'venue_id');
}

// NEW: Relationship to event_venues
public function eventVenues()
{
    return $this->hasMany(EventVenue::class, 'venue_template_id');
}

// DEPRECATED: Legacy event relationship (backward compatibility)
// public function event() { ... } // Keep for now
```

---

**Model 4: Update Event**

**File:** `app/Model/Event.php` (UPDATE EXISTING)

Add relationships:
```php
// NEW: Many-to-many venues relationship
public function venues()
{
    return $this->belongsToMany(Venue::class, 'event_venues')
                ->withPivot('venue_template_id', 'is_primary', 'capacity_override')
                ->withTimestamps();
}

// NEW: Event venues pivot records
public function eventVenues()
{
    return $this->hasMany(EventVenue::class, 'event_id');
}

// NEW: Primary venue for single-venue events
public function primaryVenue()
{
    return $this->hasOne(EventVenue::class, 'event_id')
                ->where('is_primary', true);
}

// NEW: Get venue template for this event
public function getVenueTemplate()
{
    $eventVenue = $this->eventVenues()->where('is_primary', true)->first();
    return $eventVenue ? $eventVenue->venueTemplate : null;
}

// UNCHANGED: Seats relationship (seats still belong to events)
// public function seats() { ... }

// DEPRECATED: Legacy venue_templates relationship (backward compatibility)
// public function venueTemplates() { ... } // Keep for now
```

---

### Part C: Write Unit Tests (2 hours)

**Test 1: Venue Model Test**

**File:** `tests/Unit/VenueTest.php`

```php
<?php

namespace Tests\Unit;

use Tests\TestCase;
use App\Model\Venue;
use App\Model\VenueTemplate;
use App\Model\EventVenue;
use App\Model\Event;
use Illuminate\Foundation\Testing\RefreshDatabase;

class VenueTest extends TestCase
{
    use RefreshDatabase;

    /** @test */
    public function it_creates_venue_with_auto_generated_slug()
    {
        $venue = Venue::create([
            'name' => 'Grosvenor House Ballroom',
            'address' => '86-90 Park Lane',
            'city' => 'London',
            'capacity' => 1155,
        ]);

        $this->assertEquals('grosvenor-house-ballroom', $venue->slug);
    }

    /** @test */
    public function it_ensures_unique_slug()
    {
        Venue::create(['name' => 'Test Venue', 'capacity' => 100]);
        $venue2 = Venue::create(['name' => 'Test Venue', 'capacity' => 200]);

        $this->assertEquals('test-venue-1', $venue2->slug);
    }

    /** @test */
    public function venue_has_many_templates()
    {
        $venue = Venue::factory()->create();
        VenueTemplate::factory()->count(3)->create(['venue_id' => $venue->id]);

        $this->assertCount(3, $venue->venueTemplates);
    }

    /** @test */
    public function venue_can_be_linked_to_multiple_events()
    {
        $venue = Venue::factory()->create();
        $event1 = Event::factory()->create();
        $event2 = Event::factory()->create();

        EventVenue::create(['event_id' => $event1->id, 'venue_id' => $venue->id, 'is_primary' => true]);
        EventVenue::create(['event_id' => $event2->id, 'venue_id' => $venue->id, 'is_primary' => true]);

        $this->assertCount(2, $venue->events);
    }
}
```

**Run Tests:**
```bash
vendor/bin/phpunit tests/Unit/VenueTest.php
```

---

### Part D: Verification (30 minutes)

**Checklist:**
- [ ] All 3 migrations run without errors
- [ ] `venues` table created with correct schema
- [ ] `event_venues` table created with foreign keys
- [ ] `venue_templates.venue_id` column added
- [ ] Venue model created and tested
- [ ] EventVenue model created and tested
- [ ] VenueTemplate model updated
- [ ] Event model updated
- [ ] All unit tests passing
- [ ] No breaking changes to existing functionality

**Final Verification:**
```bash
# Run all tests
vendor/bin/phpunit

# Check database schema
php artisan tinker --execute="
\$venue = new App\Model\Venue(['name' => 'Test', 'capacity' => 100]);
echo 'Venue fillable: ' . implode(', ', \$venue->getFillable()) . \"\n\";

\$eventVenue = new App\Model\EventVenue();
echo 'EventVenue fillable: ' . implode(', ', \$eventVenue->getFillable()) . \"\n\";
"
```

---

## ⚠️ Important Notes

### DO NOT:
- ❌ Delete or drop any existing columns
- ❌ Modify Event 1's data (1,155 seats)
- ❌ Run migrations on production yet
- ❌ Remove backward compatibility (event_id in venue_templates)

### DO:
- ✅ Make all new columns nullable
- ✅ Keep event_id in venue_templates (backward compatibility)
- ✅ Add foreign keys with proper cascades
- ✅ Write comprehensive tests
- ✅ Verify Event 1 data unchanged after migrations

---

## 🎯 Success Criteria

- [x] Task 3.1 Complete (Decisions made)
- [ ] Migrations created and tested
- [ ] Models created with relationships
- [ ] Unit tests passing
- [ ] Event 1 data verified unchanged (1,155 seats)
- [ ] No breaking changes to existing code
- [ ] Ready for Task 3.3 (Event 1 Migration Script)

---

## 📞 Questions or Issues?

If you encounter:
- Migration errors → Check foreign key constraints
- Model errors → Verify fillable/casts arrays
- Test failures → Check factory definitions
- Event 1 data changes → STOP and rollback immediately

**Rollback Command:**
```bash
php artisan migrate:rollback --step=3
```

---

## 🚀 After Task 3.2 Complete

**Next:** Task 3.3 - Event 1 Migration Script (4 hours)
- Create artisan command: `php artisan migrate:event-to-venue 1`
- Implement Grosvenor House Ballroom creation
- Link Event 1 to Venue 1
- Verify 1,155 seats intact

---

**Document Created:** 2025-10-12
**Last Updated:** 2025-10-12
**Status:** ✅ READY FOR IMPLEMENTATION