# Venue Separation Architecture - Task 3.1

**Task ID:** 09-385
**Linear URL:** https://linear.app/09-07/issue/09-385/31-backend-design-venue-separation-architecture
**Status:** In Progress
**Priority:** Urgent
**Duration:** 2 hours
**Dependencies:** Task 2.10 AND Task 1.10 complete
**Blocks:** Task 3.2
**Phase:** 3 - Venue Architecture Refactor

---

## 1. Executive Summary

This document outlines the architectural design for separating venue management from event management, enabling:

1. **Reusable Venues**: Physical venues (e.g., "Opera House", "Convention Center") as independent entities
2. **Reusable Templates**: Venue layouts that can be shared across multiple events
3. **Multi-Venue Events**: Support for events spanning multiple venues
4. **Data Preservation**: Zero data loss migration strategy for Event 1 (1,155 seats) and all existing data

**Critical Constraint:** Event 1 with 1,155 seats (1,048 individual + 97 tables, 6 sections, 78 reservations, 164 orders) MUST be preserved with 100% data integrity.

**Migration Scope:** ONLY Event 1 will be migrated. Event 123 and 124 are test events with 0 seats and can remain in legacy architecture.

---

## 2. Current State Analysis

### 2.1 Current Architecture Problems

**Current Relationships:**
```
events (1) ──────► (M) venue_templates
                          │
                          └─► event_id (direct coupling)

events (1) ──────► (M) seats
                          │
                          └─► event_id (direct coupling)
```

**Problems with Current Design:**
1. ❌ **No Venue Reusability**: Each event creates new venue templates from scratch
2. ❌ **Duplicate Venue Layouts**: Same physical venue recreated for each event
3. ❌ **Template Coupling**: Templates tied to events instead of venues
4. ❌ **No Multi-Venue Support**: Cannot model events spanning multiple venues
5. ❌ **Unused Fields**: `events.venue_id` exists but not properly utilized
6. ❌ **Legacy Confusion**: `Hall` model exists without corresponding table

### 2.2 Current Database Schema

#### `events` Table
```sql
CREATE TABLE events (
    id BIGINT UNSIGNED PRIMARY KEY,
    club_id BIGINT UNSIGNED,
    venue_id BIGINT UNSIGNED,  -- ⚠️ EXISTS BUT NOT USED
    name VARCHAR(255),
    event_url VARCHAR(255),
    start_date DATE,
    end_date DATE,
    -- ... 40+ additional columns ...
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
```

#### `venue_templates` Table
```sql
CREATE TABLE venue_templates (
    id BIGINT UNSIGNED PRIMARY KEY,
    event_id BIGINT UNSIGNED INDEX,  -- ⚠️ CURRENTLY COUPLED TO EVENTS
    name VARCHAR(255) DEFAULT 'Venue Template',
    template_data JSON,              -- Complete venue layout
    sections_data JSON,              -- Sections configuration
    metadata JSON,                   -- Totals, version info
    total_seats INT DEFAULT 0,
    total_tables INT DEFAULT 0,
    total_capacity INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    UNIQUE(event_id, is_active)
);
```

#### `seats` Table
```sql
CREATE TABLE seats (
    id BIGINT UNSIGNED PRIMARY KEY,
    seat_id VARCHAR(255) INDEX,      -- UUID for seat
    event_id BIGINT UNSIGNED INDEX,  -- ⚠️ TIED TO EVENTS
    row_letter VARCHAR(10),
    seat_number VARCHAR(10),
    price DECIMAL(10,2),
    zone VARCHAR(50),
    is_accessible BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    UNIQUE(event_id, seat_id),
    INDEX(event_id, is_active)
);
```

### 2.3 Current Business Logic Analysis

**VenueTemplate Model Behavior:**
- ✅ Versioning system (v1, v2, v3...)
- ✅ Published/archived status
- ✅ Seat synchronization with price updates
- ❌ Coupled to `event_id` (cannot be reused)

**Event Model Relationships:**
```php
public function venueTemplates() {
    return $this->hasMany('App\Model\VenueTemplate', 'event_id', 'id');
}

public function seats() {
    return $this->hasMany('App\Model\Seat', 'event_id', 'id');
}
```

**Critical Data to Preserve:**
- **Event 1**: 1,155 seats with reservations, bookings, price data
  - 1,048 individual seats + 97 tables
  - 6 sections
  - 78 seat reservations (71 booked + 7 held)
  - 164 orders with payment history
- **Event 999999**: Legacy test event with QR validation data
- All existing venue templates with versioning history
- All seat reservations and order relationships

**Events to Ignore:**
- **Event 123**: Test event with 0 seats (legacy, can be deleted)
- **Event 124**: Test event with 0 seats (legacy, can be deleted)

---

## 3. Target Architecture Design

### 3.1 New Database Schema

#### **NEW: `venues` Table**

Physical venue entities that can be reused across events.

```sql
CREATE TABLE venues (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    -- Venue Identity
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    display_name VARCHAR(255),          -- Public-facing name

    -- Location & Details
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    postal_code VARCHAR(20),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),

    -- Venue Information
    description TEXT,
    capacity INT DEFAULT 0,             -- Total venue capacity
    venue_type VARCHAR(50),             -- 'theater', 'arena', 'club', etc.

    -- Contact & Management
    contact_name VARCHAR(255),
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),

    -- Media
    image_url VARCHAR(255),
    floor_plan_url VARCHAR(255),

    -- Metadata
    amenities JSON,                     -- ['parking', 'bar', 'coat_check']
    metadata JSON,                      -- Additional flexible data

    -- Status
    is_active BOOLEAN DEFAULT TRUE,

    -- Timestamps
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,

    -- Indexes
    INDEX idx_slug (slug),
    INDEX idx_is_active (is_active),
    INDEX idx_city (city),
    INDEX idx_venue_type (venue_type)
);
```

**Design Rationale:**
- `slug`: URL-friendly identifier for frontend routing
- `capacity`: Total venue capacity (sum of all seats across templates)
- `venue_type`: Categorization for filtering/search
- `amenities`: JSON array for flexible venue features
- Soft deletes for data preservation

#### **REFACTORED: `venue_templates` Table**

Change from event-specific to venue-specific templates.

```sql
-- Migration will transform this table structure
ALTER TABLE venue_templates
    DROP FOREIGN KEY IF EXISTS fk_venue_templates_event_id,
    DROP INDEX IF EXISTS venue_templates_event_id_is_active_unique,
    DROP INDEX IF EXISTS venue_templates_event_id_is_active_index,

    -- ADD: Link to venues instead of events
    ADD COLUMN venue_id BIGINT UNSIGNED AFTER id,
    ADD COLUMN template_type VARCHAR(50) DEFAULT 'standard' AFTER name,
    ADD COLUMN version INT DEFAULT 1 AFTER name,
    ADD COLUMN status VARCHAR(20) DEFAULT 'draft' AFTER version,

    -- KEEP: event_id for backward compatibility (migration period)
    -- Will be dropped in Phase 4 after data migration complete

    -- Indexes
    ADD INDEX idx_venue_id (venue_id),
    ADD INDEX idx_status (status),
    ADD UNIQUE idx_venue_active (venue_id, is_active);

-- Final schema after migration
CREATE TABLE venue_templates (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    venue_id BIGINT UNSIGNED,           -- ✅ NEW: Link to venues
    name VARCHAR(255) DEFAULT 'Venue Template',
    template_type VARCHAR(50) DEFAULT 'standard',  -- 'standard', 'seasonal', 'event_specific'
    version INT DEFAULT 1,
    status VARCHAR(20) DEFAULT 'draft',            -- 'draft', 'published', 'archived'

    -- Template Data (unchanged)
    template_data JSON,
    sections_data JSON,
    metadata JSON,

    -- Cached Totals (unchanged)
    total_seats INT DEFAULT 0,
    total_tables INT DEFAULT 0,
    total_capacity INT DEFAULT 0,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,

    -- Timestamps
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    deleted_at TIMESTAMP NULL DEFAULT NULL,

    -- Foreign Keys
    FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE,

    -- Indexes
    INDEX idx_venue_id (venue_id),
    INDEX idx_status (status),
    UNIQUE idx_venue_active (venue_id, is_active)
);
```

**Design Rationale:**
- `venue_id`: Templates belong to venues (reusable across events)
- `template_type`: Differentiate standard vs. event-specific layouts
- `version`: Maintain versioning history per venue
- `status`: Track template lifecycle (draft → published → archived)

#### **NEW: `event_venues` Join Table**

Many-to-many relationship between events and venues.

```sql
CREATE TABLE event_venues (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    event_id BIGINT UNSIGNED NOT NULL,
    venue_id BIGINT UNSIGNED NOT NULL,
    venue_template_id BIGINT UNSIGNED,  -- Specific template for this event

    -- Event-Specific Venue Configuration
    is_primary BOOLEAN DEFAULT FALSE,   -- Primary venue for multi-venue events
    display_order INT DEFAULT 0,        -- Order for multi-venue display

    -- Capacity Overrides
    capacity_override INT,              -- Override venue capacity for this event
    available_capacity INT,             -- Actual available for this event

    -- Timestamps
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,

    -- Foreign Keys
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE,
    FOREIGN KEY (venue_template_id) REFERENCES venue_templates(id) ON DELETE SET NULL,

    -- Indexes
    UNIQUE idx_event_venue (event_id, venue_id),
    INDEX idx_event_id (event_id),
    INDEX idx_venue_id (venue_id),
    INDEX idx_is_primary (is_primary)
);
```

**Design Rationale:**
- Many-to-many: Events can use multiple venues (festivals, multi-stage events)
- `is_primary`: Designate main venue for multi-venue events
- `venue_template_id`: Specific template version used for this event
- `capacity_override`: Allow event-specific capacity limits

#### **DECISION: `seats` Table Strategy**

**Option A: Seats Remain Event-Specific (RECOMMENDED)**

Keep seats tied to events for booking integrity and event-specific pricing.

```sql
-- NO CHANGES to seats table structure
-- Seats continue to reference event_id
-- Relationship to venues is indirect: seats → events → event_venues → venues

-- Benefits:
-- ✅ Zero migration risk for Event 123's 75 seats
-- ✅ Preserves all existing seat reservations and orders
-- ✅ Event-specific pricing remains straightforward
-- ✅ No complex seat availability calculations across events

-- Tradeoffs:
-- ❌ Seats are not reusable across events
-- ❌ Each event creates new seat records (acceptable for ticketing)
```

**Option B: Seats Belong to Venues (NOT RECOMMENDED)**

Move seats to venue ownership with event-specific availability.

```sql
ALTER TABLE seats
    ADD COLUMN venue_id BIGINT UNSIGNED AFTER id,
    ADD FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE;

-- Requires complex availability tracking:
-- - seat_availability table linking seats to events
-- - Complex queries to check if venue seat is booked for specific event
-- - Higher risk of booking conflicts
-- - Significant migration complexity for Event 123
```

**DECISION: Use Option A (Event-Specific Seats)**

Seats remain tied to events. Venues provide the *template* for seat generation, but actual seat records belong to events. This preserves data integrity and simplifies booking logic.

### 3.2 Revised Relationships

```
                    ┌─────────────┐
                    │   Venues    │
                    │             │
                    │ - name      │
                    │ - address   │
                    │ - capacity  │
                    └──────┬──────┘
                           │
                           │ 1
                           │
                           │ M
                 ┌─────────▼────────────┐
                 │  Venue Templates     │
                 │                      │
                 │ - venue_id           │
                 │ - template_data      │
                 │ - version            │
                 └─────────┬────────────┘
                           │
                           │ 1
                           │
           ┌───────────────┼───────────────┐
           │ M                             │ 1
           │                               │
┌──────────▼──────────┐         ┌──────────▼──────────┐
│   Event Venues      │         │      Events         │
│  (Join Table)       │◄────────┤                     │
│                     │    M    │ - name              │
│ - event_id          │         │ - start_date        │
│ - venue_id          │         │ - venue_id (legacy) │
│ - venue_template_id │         └──────────┬──────────┘
│ - is_primary        │                    │
└─────────────────────┘                    │ 1
                                           │
                                           │ M
                                 ┌─────────▼──────────┐
                                 │       Seats        │
                                 │                    │
                                 │ - event_id         │
                                 │ - seat_id (UUID)   │
                                 │ - price            │
                                 │ - row_letter       │
                                 └────────────────────┘
```

**Key Relationship Rules:**
1. `Venues` (1) → (M) `Venue Templates`: One venue has many layout versions
2. `Events` (M) → (M) `Venues`: Events can use multiple venues via `event_venues`
3. `Event Venues` (M) → (1) `Venue Template`: Each event-venue uses a specific template
4. `Events` (1) → (M) `Seats`: Seats belong to events (generated from venue templates)

---

## 4. Event 1 Migration Strategy

### 4.1 Current Event 1 State

**Critical Data Inventory:**
```sql
-- Event 1 overview
SELECT id, name, event_url, start_date, club_id
FROM events
WHERE id = 1;
-- Result: "Test Event"

-- Seat inventory
SELECT COUNT(*) as total_seats
FROM seats
WHERE event_id = 1 AND is_active = TRUE;
-- Result: 1,155 seats (1,048 individual + 97 tables)

-- Reservation status
SELECT
    COUNT(*) as total_reservations,
    SUM(CASE WHEN status = 'booked' THEN 1 ELSE 0 END) as booked_seats,
    SUM(CASE WHEN status = 'held' THEN 1 ELSE 0 END) as held_seats
FROM seat_reservations
WHERE event_id = 1;
-- Result: 78 reservations (71 booked + 7 held)

-- Order count
SELECT COUNT(*) as total_orders
FROM orders
WHERE event_id = 1;
-- Result: 164 orders

-- Venue template
SELECT id, name, version, status, total_seats, total_tables
FROM venue_templates
WHERE event_id = 1 AND is_active = TRUE;
-- Result: 1,048 seats, 97 tables, 6 sections
```

### 4.2 Zero-Data-Loss Migration Plan

**IMPORTANT: Phase 1/2 Considerations**
- VenueController already exists but treats events as venues
- Event creation wizard does NOT have venue selection
- See `/Users/charlie/code/showprima/_docs/plans/phase-3-strategic-analysis.md` for full interdependency analysis

**Phase 1: Create Venue from Event 1 Template**

```sql
-- Step 1: Extract Event 1's venue template data
SELECT
    vt.id as template_id,
    vt.name as template_name,
    vt.template_data,
    vt.total_seats,
    vt.total_tables,
    vt.sections_data,
    e.name as event_name,
    e.address
FROM venue_templates vt
JOIN events e ON vt.event_id = e.id
WHERE e.id = 1 AND vt.is_active = TRUE;

-- Step 2: Create venue from Event 1 data
INSERT INTO venues (
    name, slug, address, city, country, capacity, venue_type, is_active, created_at, updated_at
)
SELECT
    'Grosvenor House Ballroom' as name,  -- Real prestigious London venue
    'grosvenor-house-ballroom' as slug,
    '86-90 Park Lane' as address,
    'London' as city,
    'United Kingdom' as country,
    e.address,
    vt.total_capacity,
    'theater' as venue_type,
    TRUE as is_active,
    NOW() as created_at,
    NOW() as updated_at
FROM events e
JOIN venue_templates vt ON e.id = vt.event_id
WHERE e.id = 1 AND vt.is_active = TRUE
LIMIT 1;

-- Get the newly created venue ID
SET @venue_id = LAST_INSERT_ID();

-- Step 3: Link venue template to new venue
UPDATE venue_templates
SET venue_id = @venue_id
WHERE event_id = 1 AND is_active = TRUE;

-- Step 4: Create event_venues relationship
INSERT INTO event_venues (
    event_id, venue_id, venue_template_id, is_primary, created_at, updated_at
)
SELECT
    1 as event_id,
    @venue_id as venue_id,
    vt.id as venue_template_id,
    TRUE as is_primary,
    NOW() as created_at,
    NOW() as updated_at
FROM venue_templates vt
WHERE vt.event_id = 1 AND vt.is_active = TRUE
LIMIT 1;
```

**Phase 2: Verify Data Integrity**

```sql
-- Verification Query 1: Seat count unchanged
SELECT
    'BEFORE' as stage,
    COUNT(*) as seat_count
FROM seats
WHERE event_id = 1 AND is_active = TRUE

UNION ALL

SELECT
    'AFTER' as stage,
    COUNT(*) as seat_count
FROM seats s
JOIN events e ON s.event_id = e.id
JOIN event_venues ev ON e.id = ev.event_id
WHERE e.id = 1 AND s.is_active = TRUE;

-- Expected: Both rows show 1,155 seats

-- Verification Query 2: Reservations intact
SELECT
    sr.id,
    sr.seat_id,
    sr.status,
    s.seat_number,
    s.price
FROM seat_reservations sr
JOIN seats s ON sr.seat_id = s.seat_id AND sr.event_id = s.event_id
WHERE sr.event_id = 1
ORDER BY sr.created_at DESC;
-- Expected: 78 reservations (71 booked + 7 held)

-- Verification Query 3: Template linked correctly
SELECT
    e.id as event_id,
    e.name as event_name,
    v.id as venue_id,
    v.name as venue_name,
    vt.id as template_id,
    vt.name as template_name,
    vt.total_seats,
    vt.total_tables,
    ev.is_primary
FROM events e
JOIN event_venues ev ON e.id = ev.event_id
JOIN venues v ON ev.venue_id = v.id
JOIN venue_templates vt ON ev.venue_template_id = vt.id
WHERE e.id = 1;
-- Expected: Event 1 → Venue 1 "Grosvenor House Ballroom" → Template with 1,048 seats + 97 tables
```

**Phase 3: Rollback Plan**

If migration fails, restore Event 1 to original state:

```sql
-- Rollback Step 1: Remove event_venues entry
DELETE FROM event_venues WHERE event_id = 1;

-- Rollback Step 2: Restore venue_template event_id reference
UPDATE venue_templates
SET venue_id = NULL
WHERE event_id = 1;

-- Rollback Step 3: Delete created venue (if no other events use it)
DELETE FROM venues WHERE slug = 'grosvenor-house-ballroom';

-- Rollback Step 4: Verify Event 1 unchanged
SELECT
    e.id, e.name,
    COUNT(DISTINCT s.id) as seat_count,
    COUNT(DISTINCT sr.id) as reservation_count,
    COUNT(DISTINCT o.id) as order_count
FROM events e
LEFT JOIN seats s ON e.id = s.event_id AND s.is_active = TRUE
LEFT JOIN seat_reservations sr ON e.id = sr.event_id
LEFT JOIN orders o ON e.id = o.event_id
WHERE e.id = 1
GROUP BY e.id, e.name;

-- Expected: Event 1 with 1,155 seats, 78 reservations, 164 orders
```

### 4.3 Migration Checkpoints

**Checkpoint 1: Pre-Migration Backup**
```bash
# Backup database before any changes
./scripts/backup-db.sh

# Verify backup
ls -lh database/backups/backup_*.sqlite
```

**Checkpoint 2: Test on Event 999999 First**
```sql
-- Run migration procedure on test event 999999 first (if needed for validation)
-- Verify all steps work correctly
-- Then apply to Event 1
```

**Checkpoint 3: Verify Seat UUIDs Unchanged**
```sql
-- Event 1 seat UUIDs must remain identical (critical for existing orders)
SELECT seat_id, seat_number, seat_type, price, section_id
FROM seats
WHERE event_id = 1 AND is_active = TRUE
ORDER BY seat_number
LIMIT 20;

-- Compare with pre-migration snapshot
-- UUIDs MUST match exactly (164 orders depend on these)
```

**Checkpoint 4: Test Booking Flow**
```bash
# Run E2E booking test on Event 1
npm run test:e2e -- --grep "Event 1"

# Verify booking succeeds with migrated structure
# CRITICAL: 7 active holds must not be disrupted
```

**Checkpoint 5: Verify Active Reservations**
```sql
-- Verify 7 active "held" reservations are still valid
SELECT seat_id, status, expires_at
FROM seat_reservations
WHERE event_id = 1 AND status = 'held'
ORDER BY expires_at;

-- All must still have valid expiry times
```

---

## 5. Reusable Venue Templates

### 5.1 Template Reusability Strategy

**Scenario 1: Same Venue, Different Events**

```sql
-- Example: "Grosvenor House Ballroom" used by multiple events
INSERT INTO venues (name, slug, address, city, country, capacity, venue_type)
VALUES ('Grosvenor House Ballroom', 'grosvenor-house-ballroom', '86-90 Park Lane', 'London', 'United Kingdom', 1155, 'ballroom');

-- Create standard template for Grosvenor House Ballroom
INSERT INTO venue_templates (venue_id, name, template_type, status, template_data, total_capacity)
VALUES (
    1,
    'Grosvenor House Standard Layout',
    'standard',
    'published',
    '{"nodes": [...], "sections": [...]}',
    1000
);

-- Event A uses Grosvenor House Ballroom
INSERT INTO event_venues (event_id, venue_id, venue_template_id, is_primary)
VALUES (200, 1, 1, TRUE);

-- Event B uses same Grosvenor House Ballroom, same template
INSERT INTO event_venues (event_id, venue_id, venue_template_id, is_primary)
VALUES (201, 1, 1, TRUE);

-- Seats are still event-specific (generated from template)
-- Event 200 gets its own 1000 seat records
-- Event 201 gets its own 1000 seat records
```

**Scenario 2: Same Venue, Event-Specific Layout**

```sql
-- Event C needs custom stage configuration at Opera House
INSERT INTO venue_templates (venue_id, name, template_type, status, template_data, total_capacity)
VALUES (
    1,
    'Opera House - Extended Stage Layout',
    'event_specific',
    'published',
    '{"nodes": [...custom stage...], "sections": [...]}',
    850  -- Reduced capacity due to stage extension
);

-- Event C uses custom template
INSERT INTO event_venues (event_id, venue_id, venue_template_id, is_primary, capacity_override)
VALUES (202, 1, 2, TRUE, 850);
```

### 5.2 Template Versioning Per Venue

Each venue maintains its own template version history:

```sql
-- Venue 1 (Opera House) template history
SELECT id, name, version, status, total_capacity, created_at
FROM venue_templates
WHERE venue_id = 1
ORDER BY version DESC;

/*
Result:
id  | name                              | version | status    | capacity | created_at
----|-----------------------------------|---------|-----------|----------|------------
3   | Opera House - Winter Layout       | 3       | published | 950      | 2025-12-01
2   | Opera House - Extended Stage      | 2       | archived  | 850      | 2025-10-15
1   | Opera House Standard Layout       | 1       | archived  | 1000     | 2025-09-01
*/

-- Events can choose which version to use
INSERT INTO event_venues (event_id, venue_id, venue_template_id)
VALUES
    (300, 1, 3),  -- Event 300 uses v3 (Winter Layout)
    (301, 1, 1);  -- Event 301 uses v1 (Standard Layout)
```

---

## 6. Seat Relationship Impact

### 6.1 Seat Generation Flow

**Before (Current):**
```
User creates event
    ↓
Admin creates venue template for event
    ↓
Seats generated with event_id
    ↓
Seats are event-specific, non-reusable
```

**After (New Architecture):**
```
Admin creates venue (one-time)
    ↓
Admin creates venue template (reusable)
    ↓
User creates event
    ↓
User selects venue + template for event
    ↓
Seats generated from template with event_id
    ↓
Seats are event-specific (but template is reusable)
```

### 6.2 Seat Pricing Implications

**Event-Specific Pricing (Preserved):**
```php
// Seats maintain event-specific pricing
$seat = Seat::where('event_id', 123)
            ->where('seat_id', 'uuid-abc-123')
            ->first();

// Pricing flows: venue template → event seats → price_snapshot → order_line_items
// No change to existing pricing pipeline
```

**Template Default Pricing:**
```php
// Venue templates can provide DEFAULT prices
// But actual seat prices are set per event
$template = VenueTemplate::find(1);
$defaultPrice = $template->template_data['nodes'][0]['price'];

// When event uses template, seats inherit default price
// But admin can override per event
Seat::createFromTemplate($eventId, $venueTemplateId, $priceOverrides);
```

### 6.3 Seat Availability Queries

**Before:**
```php
// Simple: seats tied directly to event
$availableSeats = Seat::where('event_id', 123)
    ->whereDoesntHave('reservations', function($q) {
        $q->where('status', 'booked');
    })
    ->get();
```

**After (Unchanged):**
```php
// No change! Seats still tied to events
// Venue relationship is indirect (events → event_venues → venues)
$availableSeats = Seat::where('event_id', 123)
    ->whereDoesntHave('reservations', function($q) {
        $q->where('status', 'booked');
    })
    ->get();

// Additional: Can query which venue this event uses
$event = Event::with('eventVenues.venue')->find(123);
$venueName = $event->eventVenues->first()->venue->name;
```

---

## 7. Model Relationship Updates

### 7.1 New Venue Model

```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',
    ];

    // 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();
    }

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

    // Slug auto-generation
    public function setSlugAttribute($value)
    {
        if (empty($value) && !empty($this->name)) {
            $this->attributes['slug'] = \Str::slug($this->name);
        } else {
            $this->attributes['slug'] = $value;
        }
    }
}
```

### 7.2 Updated VenueTemplate Model

```php
<?php

namespace App\Model;

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

class VenueTemplate extends Model
{
    use SoftDeletes;

    protected $fillable = [
        'venue_id',      // NEW: Link to venues
        'event_id',      // DEPRECATED: Will be removed in Phase 4
        'name', 'template_type', 'version', 'status', 'description', 'author',
        'template_data', 'sections_data', 'metadata',
        'total_seats', 'total_tables', 'total_capacity', 'is_active'
    ];

    protected $casts = [
        'template_data' => 'array',
        'sections_data' => 'array',
        'metadata' => 'array',
        'is_active' => 'boolean',
        'version' => 'integer'
    ];

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

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

    // DEPRECATED: Legacy event relationship (backward compatibility)
    public function event()
    {
        return $this->belongsTo(Event::class, 'event_id');
    }

    // UPDATED: Save template to venue (not event)
    public static function saveVenueTemplate($venueId, $templateData, $eventId = null)
    {
        // Extract metadata
        $metadata = $templateData['metadata'] ?? [];
        $nodes = $templateData['nodes'] ?? [];
        $sections = $templateData['sections'] ?? [];

        // Determine the next version number FOR THIS VENUE
        $latestTemplate = static::where('venue_id', $venueId)
                              ->orderBy('version', 'desc')
                              ->first();

        $nextVersion = $latestTemplate ? $latestTemplate->version + 1 : 1;

        $template = new static([
            'venue_id' => $venueId,        // NEW: Link to venue
            'event_id' => $eventId,        // OPTIONAL: For backward compatibility
            'name' => $templateData['name'] ?? 'Venue Template',
            'template_type' => $templateData['template_type'] ?? 'standard',
            'version' => $nextVersion,
            'status' => 'published',
            'template_data' => $templateData,
            'sections_data' => $sections,
            'metadata' => $metadata,
            'total_seats' => $metadata['total_seats'] ?? 0,
            'total_tables' => $metadata['total_tables'] ?? 0,
            'total_capacity' => $metadata['total_capacity'] ?? 0,
            'is_active' => true
        ]);

        \DB::transaction(function () use ($template, $latestTemplate, $venueId, $eventId, $nodes, $sections) {
            // Archive existing published versions FOR THIS VENUE
            if ($latestTemplate && $latestTemplate->status === 'published') {
                static::where('venue_id', $venueId)
                      ->where('status', 'published')
                      ->update(['status' => 'archived']);
            }

            $template->save();

            // If event_id provided, sync seats for that event
            if ($eventId) {
                static::syncSeatsFromTemplate($eventId, $nodes, $sections);
            }
        });

        return $template;
    }
}
```

### 7.3 New EventVenue Model

```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;
    }
}
```

### 7.4 Updated Event Model

```php
<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;

class Event extends Model
{
    // ... existing code ...

    // 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);
    }

    // DEPRECATED: Legacy venue_templates relationship (backward compatibility)
    public function venueTemplates()
    {
        return $this->hasMany(VenueTemplate::class, 'event_id');
    }

    // UNCHANGED: Seats relationship (seats still belong to events)
    public function seats()
    {
        return $this->hasMany(Seat::class, 'event_id');
    }

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

---

## 8. Migration Execution Plan

### 8.1 Migration Phases

**Phase 1: Schema Additions (Safe)**
- ✅ Create `venues` table
- ✅ Create `event_venues` table
- ✅ Add `venue_id` column to `venue_templates` (nullable)
- ✅ Add indexes
- ⚠️ DO NOT drop any existing columns yet

**Phase 2: Data Migration**
- ✅ Create venues from existing event data
- ✅ Link venue_templates to venues
- ✅ Create event_venues relationships
- ✅ Verify Event 123 integrity (75 seats)
- ✅ Verify all seat reservations intact

**Phase 3: Application Updates**
- ✅ Update models with new relationships
- ✅ Update API endpoints to support venue selection
- ✅ Update frontend to use new venue system
- ✅ Add backward compatibility shims

**Phase 4: Cleanup (Future)**
- ⚠️ Remove `event_id` from `venue_templates` (after full migration)
- ⚠️ Remove `venue_id` from `events` table (replaced by event_venues)
- ⚠️ Remove backward compatibility code

### 8.2 Migration Files

#### Migration 1: Create Venues Table
```php
// database/migrations/YYYY_MM_DD_HHMMSS_create_venues_table.php
public function up()
{
    Schema::create('venues', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('slug')->unique();
        $table->string('display_name')->nullable();
        $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();
        $table->text('description')->nullable();
        $table->integer('capacity')->default(0);
        $table->string('venue_type', 50)->nullable();
        $table->string('contact_name')->nullable();
        $table->string('contact_email')->nullable();
        $table->string('contact_phone', 50)->nullable();
        $table->string('image_url')->nullable();
        $table->string('floor_plan_url')->nullable();
        $table->json('amenities')->nullable();
        $table->json('metadata')->nullable();
        $table->boolean('is_active')->default(true);
        $table->timestamps();
        $table->softDeletes();

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

#### Migration 2: Create Event Venues Table
```php
// database/migrations/YYYY_MM_DD_HHMMSS_create_event_venues_table.php
public function up()
{
    Schema::create('event_venues', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('event_id');
        $table->unsignedBigInteger('venue_id');
        $table->unsignedBigInteger('venue_template_id')->nullable();
        $table->boolean('is_primary')->default(false);
        $table->integer('display_order')->default(0);
        $table->integer('capacity_override')->nullable();
        $table->integer('available_capacity')->nullable();
        $table->timestamps();

        $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');

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

#### Migration 3: Add Venue ID to Venue Templates
```php
// database/migrations/YYYY_MM_DD_HHMMSS_add_venue_id_to_venue_templates.php
public function up()
{
    Schema::table('venue_templates', function (Blueprint $table) {
        $table->unsignedBigInteger('venue_id')->nullable()->after('id');
        $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');

        $table->index('venue_id');
        $table->index('status');
    });
}
```

#### Migration 4: Migrate Existing Data
```php
// database/migrations/YYYY_MM_DD_HHMMSS_migrate_venues_data.php
public function up()
{
    // This migration will:
    // 1. Create venues from unique event addresses
    // 2. Link venue_templates to venues
    // 3. Create event_venues relationships
    // 4. Preserve Event 123 data

    // See detailed implementation in Section 4.2
}
```

### 8.3 Migration Checklist

**Pre-Migration:**
- [ ] Backup database: `./scripts/backup-db.sh`
- [ ] Verify Event 123 has 75 seats
- [ ] Verify Event 999999 exists
- [ ] Run test suite: `vendor/bin/phpunit`
- [ ] Document current state

**Migration Execution:**
- [ ] Run Migration 1: Create venues table
- [ ] Run Migration 2: Create event_venues table
- [ ] Run Migration 3: Add venue_id to venue_templates
- [ ] Verify schema changes successful
- [ ] Run Migration 4: Migrate data
- [ ] Verify Event 123 has 75 seats (unchanged)
- [ ] Verify all venue_templates linked to venues
- [ ] Verify all events have event_venues records

**Post-Migration:**
- [ ] Run test suite: `vendor/bin/phpunit`
- [ ] Test Event 123 booking flow
- [ ] Verify venue template versioning works
- [ ] Update API documentation
- [ ] Deploy to staging environment

**Rollback Plan:**
- [ ] Restore from backup if critical errors
- [ ] Document any issues encountered
- [ ] Review migration strategy adjustments

---

## 9. API Endpoint Updates

### 9.1 New Venue Endpoints

```php
// List all venues
GET /api/venues
Response: {
    "data": [
        {
            "id": 1,
            "name": "Cairo Opera House",
            "slug": "cairo-opera-house",
            "address": "1 Opera Square, Cairo",
            "capacity": 1000,
            "venue_type": "opera_house",
            "published_template_id": 5
        }
    ]
}

// Get venue details
GET /api/venues/{id}
Response: {
    "data": {
        "id": 1,
        "name": "Cairo Opera House",
        "slug": "cairo-opera-house",
        "templates": [
            {"id": 5, "name": "Standard Layout", "version": 3, "status": "published"},
            {"id": 4, "name": "Extended Stage", "version": 2, "status": "archived"}
        ]
    }
}

// Create venue
POST /api/venues
Body: {
    "name": "New Venue",
    "address": "123 Main St",
    "capacity": 500,
    "venue_type": "theater"
}

// Get venue template
GET /api/venues/{id}/template
Response: {
    "data": {
        "template_data": { ... },
        "sections_data": [ ... ],
        "metadata": { ... }
    }
}

// Save venue template
POST /api/venues/{id}/template
Body: {
    "name": "New Layout",
    "template_type": "standard",
    "template_data": { ... },
    "sections_data": [ ... ]
}
```

### 9.2 Updated Event Endpoints

```php
// Create event with venue
POST /api/events
Body: {
    "name": "Concert 2025",
    "start_date": "2025-12-01",
    "venue_id": 1,                    // Select existing venue
    "venue_template_id": 5,           // Select specific template
    "capacity_override": 800          // Optional: override venue capacity
}

// Link additional venue to event (multi-venue support)
POST /api/events/{id}/venues
Body: {
    "venue_id": 2,
    "venue_template_id": 10,
    "is_primary": false,
    "display_order": 2
}

// Get event with venues
GET /api/events/{id}
Response: {
    "data": {
        "id": 123,
        "name": "Test Concert 2025",
        "venues": [
            {
                "id": 1,
                "name": "Cairo Opera House",
                "is_primary": true,
                "template": {
                    "id": 5,
                    "name": "Standard Layout",
                    "capacity": 1000
                }
            }
        ],
        "total_capacity": 1000,
        "available_seats": 975
    }
}
```

### 9.3 Backward Compatibility Endpoints

```php
// Legacy: Get venue template by event (still works)
GET /api/events/{id}/venue-template
Response: {
    "data": {
        "template_data": { ... },
        // Returns primary venue's template
    }
}

// Legacy: Save venue template to event (backward compatible)
POST /api/events/{id}/venue-template
Body: { ... }
// Internally creates/updates venue and links to event
```

---

## 10. Frontend Integration

### 10.1 Venue Selector Component

```typescript
// New component: VenueSelector.tsx
interface VenueSelectorProps {
    onVenueSelect: (venueId: number, templateId: number) => void;
    selectedVenueId?: number;
}

export function VenueSelector({ onVenueSelect, selectedVenueId }: VenueSelectorProps) {
    const [venues, setVenues] = useState<Venue[]>([]);
    const [selectedVenue, setSelectedVenue] = useState<Venue | null>(null);

    useEffect(() => {
        // Fetch available venues
        fetch('/api/venues')
            .then(res => res.json())
            .then(data => setVenues(data.data));
    }, []);

    const handleVenueChange = (venueId: number) => {
        const venue = venues.find(v => v.id === venueId);
        setSelectedVenue(venue || null);

        // Auto-select published template
        if (venue?.published_template_id) {
            onVenueSelect(venueId, venue.published_template_id);
        }
    };

    return (
        <div className="venue-selector">
            <select onChange={(e) => handleVenueChange(Number(e.target.value))}>
                <option value="">Select Venue</option>
                {venues.map(venue => (
                    <option key={venue.id} value={venue.id}>
                        {venue.name} ({venue.capacity} capacity)
                    </option>
                ))}
            </select>

            {selectedVenue && (
                <div className="template-selector">
                    <h4>Select Layout</h4>
                    {selectedVenue.templates?.map(template => (
                        <label key={template.id}>
                            <input
                                type="radio"
                                name="template"
                                value={template.id}
                                defaultChecked={template.status === 'published'}
                                onChange={() => onVenueSelect(selectedVenue.id, template.id)}
                            />
                            {template.name} (v{template.version})
                        </label>
                    ))}
                </div>
            )}
        </div>
    );
}
```

### 10.2 Event Creation Flow

```typescript
// Updated: EventCreationForm.tsx
export function EventCreationForm() {
    const [formData, setFormData] = useState({
        name: '',
        start_date: '',
        venue_id: null,
        venue_template_id: null,
        capacity_override: null,
    });

    const handleVenueSelect = (venueId: number, templateId: number) => {
        setFormData(prev => ({
            ...prev,
            venue_id: venueId,
            venue_template_id: templateId,
        }));
    };

    const handleSubmit = async () => {
        const response = await fetch('/api/events', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(formData),
        });

        const event = await response.json();

        // Seats will be auto-generated from venue template
        console.log(`Event created with ${event.data.total_seats} seats`);
    };

    return (
        <form onSubmit={handleSubmit}>
            <input
                type="text"
                value={formData.name}
                onChange={(e) => setFormData({...formData, name: e.target.value})}
                placeholder="Event Name"
            />

            <VenueSelector
                onVenueSelect={handleVenueSelect}
                selectedVenueId={formData.venue_id}
            />

            <input
                type="number"
                value={formData.capacity_override || ''}
                onChange={(e) => setFormData({...formData, capacity_override: e.target.value})}
                placeholder="Override Capacity (optional)"
            />

            <button type="submit">Create Event</button>
        </form>
    );
}
```

---

## 11. Testing Strategy

### 11.1 Unit Tests

```php
// tests/Unit/VenueTest.php
class VenueTest extends TestCase
{
    /** @test */
    public function it_creates_venue_with_slug()
    {
        $venue = Venue::create([
            'name' => 'Cairo Opera House',
            'address' => '1 Opera Square',
            'capacity' => 1000,
        ]);

        $this->assertEquals('cairo-opera-house', $venue->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);
    }
}

// tests/Unit/VenueTemplateTest.php
class VenueTemplateTest extends TestCase
{
    /** @test */
    public function template_versioning_works_per_venue()
    {
        $venue = Venue::factory()->create();

        $template1 = VenueTemplate::saveVenueTemplate($venue->id, ['name' => 'Layout v1']);
        $this->assertEquals(1, $template1->version);

        $template2 = VenueTemplate::saveVenueTemplate($venue->id, ['name' => 'Layout v2']);
        $this->assertEquals(2, $template2->version);

        $this->assertEquals('archived', $template1->fresh()->status);
        $this->assertEquals('published', $template2->status);
    }
}
```

### 11.2 Integration Tests

```php
// tests/Feature/EventVenueIntegrationTest.php
class EventVenueIntegrationTest extends TestCase
{
    /** @test */
    public function event_can_be_created_with_venue()
    {
        $venue = Venue::factory()->create(['capacity' => 1000]);
        $template = VenueTemplate::factory()->create(['venue_id' => $venue->id, 'status' => 'published']);

        $response = $this->postJson('/api/events', [
            'name' => 'Concert 2025',
            'start_date' => '2025-12-01',
            'venue_id' => $venue->id,
            'venue_template_id' => $template->id,
        ]);

        $response->assertStatus(201);

        $event = Event::latest()->first();
        $this->assertCount(1, $event->eventVenues);
        $this->assertEquals($venue->id, $event->primaryVenue->venue_id);
    }

    /** @test */
    public function event_123_migration_preserves_75_seats()
    {
        // Run migration
        Artisan::call('migrate:venues');

        $event = Event::find(123);
        $this->assertNotNull($event);
        $this->assertCount(75, $event->seats()->where('is_active', true)->get());

        // Verify venue relationship
        $this->assertCount(1, $event->eventVenues);
        $eventVenue = $event->eventVenues->first();
        $this->assertNotNull($eventVenue->venue_id);
        $this->assertNotNull($eventVenue->venue_template_id);
    }
}
```

### 11.3 E2E Tests

```typescript
// tests/e2e/venue-management.spec.ts
import { test, expect } from '@playwright/test';

test('admin can create venue and use it for event', async ({ page }) => {
    // Create venue
    await page.goto('/admin/venues/create');
    await page.fill('[name="name"]', 'Test Venue');
    await page.fill('[name="address"]', '123 Test St');
    await page.fill('[name="capacity"]', '500');
    await page.click('button[type="submit"]');

    await expect(page.locator('.success-message')).toBeVisible();

    // Create venue template
    await page.goto('/admin/venues/1/template');
    // ... venue editor interactions ...
    await page.click('button:has-text("Save Template")');

    // Create event with venue
    await page.goto('/admin/events/create');
    await page.fill('[name="name"]', 'Test Event');
    await page.selectOption('[name="venue_id"]', '1');
    await page.click('button[type="submit"]');

    // Verify seats generated
    await page.goto('/admin/events/latest/seats');
    await expect(page.locator('.seat-count')).toHaveText('500 seats');
});

test('Event 123 still works after migration', async ({ page }) => {
    await page.goto('/booking/123');

    // Verify venue canvas loads
    await expect(page.locator('canvas')).toBeVisible();

    // Verify seat count
    const seatCount = await page.locator('.available-seats').textContent();
    expect(seatCount).toContain('75');

    // Test booking flow
    await page.click('.seat[data-seat-id="C-05"]');
    await page.click('button:has-text("Hold Seats")');

    await expect(page.locator('.checkout-summary')).toBeVisible();
});
```

---

## 12. Rollback Strategy

### 12.1 Rollback Triggers

**Immediate Rollback If:**
- ❌ Event 123 loses any of its 75 seats
- ❌ Any seat reservations or orders corrupted
- ❌ Venue template versioning breaks
- ❌ Critical booking flow fails in production

**Conditional Rollback If:**
- ⚠️ Performance degrades significantly (>500ms query time)
- ⚠️ Frontend integration issues (can be fixed in application layer)
- ⚠️ Non-critical API endpoint failures (can be patched)

### 12.2 Rollback Procedure

```bash
# Step 1: Put application in maintenance mode
php artisan down

# Step 2: Restore from pre-migration backup
./scripts/restore-db.sh backup_YYYYMMDD_HHMMSS.sqlite

# Step 3: Verify Event 123 integrity
sqlite3 database/showprima_dev.sqlite "
SELECT
    e.id, e.name,
    COUNT(DISTINCT s.id) as seat_count,
    COUNT(DISTINCT sr.id) as reservation_count
FROM events e
LEFT JOIN seats s ON e.id = s.event_id AND s.is_active = 1
LEFT JOIN seat_reservations sr ON e.id = sr.event_id
WHERE e.id = 123
GROUP BY e.id, e.name;
"

# Expected: Event 123 | Test Concert 2025 | 75 | [reservation_count]

# Step 4: Rollback migrations (if backup restore not sufficient)
php artisan migrate:rollback --step=4

# Step 5: Clear caches
php artisan cache:clear
php artisan config:clear
php artisan route:clear

# Step 6: Bring application back online
php artisan up

# Step 7: Notify team and document rollback reason
```

### 12.3 Post-Rollback Analysis

```markdown
## Rollback Incident Report

**Date:** YYYY-MM-DD
**Time:** HH:MM UTC
**Trigger:** [Reason for rollback]

### Impact Assessment
- Events affected: [List]
- Data loss: [Yes/No - Details]
- Downtime: [Duration]

### Root Cause
[Detailed explanation of what went wrong]

### Corrective Actions
1. [Action 1]
2. [Action 2]
3. [Action 3]

### Re-Migration Plan
[Updated strategy based on lessons learned]
```

---

## 13. Success Criteria

### 13.1 Migration Success Metrics

**Data Integrity:**
- ✅ Event 1 has exactly 1,155 seats (unchanged)
- ✅ Event 1 has exactly 78 seat reservations (71 booked + 7 held)
- ✅ Event 1 has exactly 164 orders (unchanged)
- ✅ All seat UUIDs match pre-migration snapshot
- ✅ All order_line_items intact and reference correct seats
- ✅ Event 999999 preserved (QR validation data)
- ✅ Venue template versioning preserved
- ✅ price_snapshot data in seat_reservations unchanged

**Functionality:**
- ✅ Booking flow works for Event 1 (end-to-end test passes)
- ✅ Event 1 available seats query returns correct count (1,077 available = 1,155 total - 78 reserved)
- ✅ Venue templates can be created and linked to Venue 1
- ✅ New events can select Venue 1 ("Grosvenor House Ballroom")
- ✅ VenueController refactored to use venues table (not events)
- ✅ Event creation wizard includes venue selector
- ✅ Seat generation from templates works

**Performance:**
- ✅ Venue template queries < 200ms
- ✅ Event listing with venues < 300ms
- ✅ Seat availability queries unchanged performance
- ✅ No N+1 query issues

**Code Quality:**
- ✅ All tests passing (PHPUnit + Playwright)
- ✅ Models follow Laravel best practices
- ✅ API endpoints documented
- ✅ Frontend components type-safe

### 13.2 Acceptance Tests

```bash
# Test 1: Event 1 data integrity
vendor/bin/phpunit --filter Event1IntegrityTest
# Expected: 1,155 seats, 78 reservations, 164 orders

# Test 2: Venue reusability
vendor/bin/phpunit --filter VenueReusabilityTest
# Expected: Venue 1 "Grosvenor House Ballroom" can be used by multiple events

# Test 3: VenueController refactor
vendor/bin/phpunit --filter VenueControllerTest
# Expected: Returns venues table data (not events)

# Test 4: Event 1 booking flow
npm run test:e2e -- --grep "Event 1 booking"
# Expected: Customer can book seats on Event 1

# Test 5: Event creation with venue selection
npm run test:e2e -- --grep "Create event with venue"
# Expected: Wizard includes venue selector, new event links to Venue 1

# Test 6: Performance benchmarks
php artisan benchmark:venue-queries
# Expected: Query times < 200ms
```

---

## 14. Timeline & Next Steps

### 14.1 Implementation Timeline

| Phase | Duration | Tasks | Dependencies |
|-------|----------|-------|--------------|
| **Phase 1: Schema Design** | 2 hours | Complete this document | Task 2.10, 1.10 |
| **Phase 2: Migration Files** | 3 hours | Create 4 migration files | Phase 1 complete |
| **Phase 3: Model Updates** | 2 hours | Update Venue, Event, VenueTemplate models | Phase 2 complete |
| **Phase 4: Data Migration** | 2 hours | Run migrations, verify Event 123 | Phase 3 complete |
| **Phase 5: API Endpoints** | 4 hours | Create venue management APIs | Phase 4 complete |
| **Phase 6: Frontend Integration** | 6 hours | Update admin & ticketing apps | Phase 5 complete |
| **Phase 7: Testing** | 4 hours | Unit, integration, E2E tests | Phase 6 complete |
| **Phase 8: Deployment** | 2 hours | Deploy to staging, production | Phase 7 complete |

**Total Estimated Time:** 25 hours

### 14.2 Blocking Issues

**This Task Blocks:**
- Task 3.2: Venue management UI implementation
- Task 3.3: Multi-venue event support
- Task 3.4: Venue template marketplace

**Dependencies:**
- ✅ Task 2.10: Events table migration complete
- ✅ Task 1.10: Seat relationship refactoring complete

### 14.3 Follow-Up Tasks

**Immediate (Task 3.2):**
- Implement venue management UI
- Create venue template editor for venues (not events)
- Update event creation flow with venue selector

**Short-term (1-2 weeks):**
- Add venue search and filtering
- Implement venue template marketplace
- Create venue analytics dashboard

**Long-term (1-3 months):**
- Remove `event_id` from `venue_templates` (Phase 4 cleanup)
- Remove `venue_id` from `events` table (replaced by `event_venues`)
- Implement advanced multi-venue scheduling

---

## 15. Appendix

### 15.1 Glossary

**Terms:**
- **Venue**: Physical location/building that hosts events (e.g., "Cairo Opera House")
- **Venue Template**: Seating layout/configuration for a venue (reusable across events)
- **Event Venue**: Specific instance of a venue being used by an event
- **Seat**: Individual bookable unit within an event (event-specific, not reusable)

### 15.2 Related Documentation

- [Events Table Migration Plan](./events-table-migration-plan.md)
- [Seat Reservation System](../architecture/seat-reservation-system.md)
- [Pricing Pipeline](../architecture/pricing-pipeline.md)
- [Order Line Items](../architecture/order-line-items.md)

### 15.3 Database Schema Diagrams

**Before (Current):**
```
┌─────────────┐
│   Events    │
└──────┬──────┘
       │ 1
       │
       │ M
       ▼
┌─────────────────────┐      ┌─────────────┐
│  Venue Templates    │      │    Seats    │
│                     │      │             │
│  - event_id (FK)    │      │ - event_id  │
└─────────────────────┘      └─────────────┘
```

**After (Target):**
```
┌──────────────┐
│    Venues    │◄───────┐
└──────┬───────┘        │
       │ 1              │ M
       │                │
       │ M              │
       ▼         ┌──────┴──────────┐
┌─────────────────────┐│ Event Venues│
│  Venue Templates    ││ (Join Table)│
│                     │└──────┬──────┘
│  - venue_id (FK)    │       │ M
└─────────────────────┘       │
                              │ 1
                              ▼
                     ┌─────────────────┐      ┌─────────────┐
                     │     Events      │      │    Seats    │
                     │                 │      │             │
                     │                 │◄─────┤ - event_id  │
                     └─────────────────┘  1   └─────────────┘
                                           M
```

---

**Document Version:** 1.0
**Last Updated:** 2025-10-12
**Author:** Backend Architecture Team
**Status:** Ready for Review
**Next Step:** Task 3.2 - Implementation