# Story 2.5: SeatReservation Model Integration - COMPLETE ✅

**Epic**: EPIC-BOOKING-004: Promotional Code System
**Story**: 2.5 - SeatReservation Model Integration
**Status**: ✅ Complete
**Date**: 2025-11-11
**Branch**: `feature/promo-codes-model-integration`

---

## 📋 **Summary**

Integrated promotional code (coupon) support into the `SeatReservation` model to complete the backend booking flow. This story bridges Story 2 (API Endpoints) and the future frontend work (Stories 3/4).

**What This Enables**:
- ✅ Coupons can be passed from `SeatController::holdSeats()` to `SeatReservation::holdSeats()`
- ✅ Coupon data is stored in hold records (soft-reserve pattern - Decision #1)
- ✅ Locked coupons honor their discount even if they expire/max-out during checkout
- ✅ Coupon usage is recorded automatically after successful booking confirmation
- ✅ Full backward compatibility with existing code (all new parameters are optional)

---

## 🎯 **Implementation Details**

### **1. Database Fields Added**

Added 4 new columns to `seat_reservations` table (via Story 1 migration already applied):

```sql
coupon_id              INT NULL              -- FK to coupons.id (nullable)
discount_applied       DECIMAL(10,2) NULL    -- Locked discount amount (nullable)
coupon_locked          BOOLEAN DEFAULT FALSE -- Soft-reserve flag
coupon_locked_at       DATETIME NULL         -- Timestamp when coupon was locked
```

**Why These Fields?**
- `coupon_id`: Links reservation to coupon for analytics/tracking
- `discount_applied`: Preserves exact discount at hold time (Decision #1: Honor locked discounts)
- `coupon_locked`: Flag to indicate coupon is "soft-reserved" for this hold
- `coupon_locked_at`: Audit trail for when coupon was locked

### **2. Model Changes: `app/Model/SeatReservation.php`**

#### **A. Updated `$fillable` Array**

```php
protected $fillable = [
    // ... existing fields ...
    // EPIC-BOOKING-004: Promotional Code System - Story 2.5
    'coupon_id',
    'discount_applied',
    'coupon_locked',
    'coupon_locked_at',
];
```

**Impact**: Allows mass-assignment of coupon fields when creating hold records.

---

#### **B. Updated `holdSeats()` Method Signature**

**Before**:
```php
public static function holdSeats(
    $eventId,
    $seatIds,
    $sessionId = null,
    $userId = null,
    $seatPricing = [],
    $currency = null
)
```

**After**:
```php
public static function holdSeats(
    $eventId,
    $seatIds,
    $sessionId = null,
    $userId = null,
    $seatPricing = [],
    $currency = null,
    $couponData = null  // NEW: Optional coupon data
)
```

**New Parameter**: `$couponData` (array|null)
- Structure: `['coupon_id' => int, 'discount' => float, 'warning' => string|null]`
- **Optional**: Existing code continues to work without passing this parameter
- **Source**: Comes from `CouponService::validateCoupon()` via `SeatController`

---

#### **C. Updated Hold Record Creation**

**Location**: `SeatReservation::holdSeats()` line ~341-369

**Before**:
```php
$holds[] = [
    'event_id' => $eventId,
    'seat_id' => $seatId,
    // ... standard fields ...
];
```

**After**:
```php
$holdData = [
    'event_id' => $eventId,
    'seat_id' => $seatId,
    // ... standard fields ...
];

// EPIC-BOOKING-004: Story 2.5 - Store coupon data if provided (Decision #1: Soft-reserve)
if ($couponData !== null) {
    $holdData['coupon_id'] = $couponData['coupon_id'] ?? null;
    $holdData['discount_applied'] = $couponData['discount'] ?? 0;
    $holdData['coupon_locked'] = true; // Lock coupon to this reservation
    $holdData['coupon_locked_at'] = TimeProvider::now();
}

$holds[] = $holdData;
```

**Key Logic**:
1. **Conditional Storage**: Only store coupon fields if `$couponData` is provided
2. **Soft-Reserve**: Set `coupon_locked = true` immediately on hold creation
3. **Timestamp**: Record exact moment coupon was locked for audit trail
4. **Backward Compatible**: If no coupon, fields are NULL (existing behavior)

---

#### **D. Updated `confirmBookingWithPaymentMode()` Method**

**Location**: `SeatReservation::confirmBookingWithPaymentMode()` line ~595-621

**Added After Seat Status Update**:
```php
// EPIC-BOOKING-004: Story 2.5 - Record coupon usage after successful booking
// Only record if coupon was used (check first hold for coupon_id)
if ($holds->isNotEmpty() && $holds->first()->coupon_id) {
    try {
        $couponService = app(\App\Services\CouponService::class);
        $couponService->recordUsage(
            couponId: $holds->first()->coupon_id,
            orderId: $order->id,
            customerEmail: $customerEmail,
            discountAmount: $holds->first()->discount_applied ?? 0
        );

        \Log::info('Coupon usage recorded', [
            'order_id' => $order->id,
            'coupon_id' => $holds->first()->coupon_id,
            'discount_amount' => $holds->first()->discount_applied,
            'payment_mode' => $paymentMode,
        ]);
    } catch (\Exception $e) {
        \Log::error('Failed to record coupon usage', [
            'order_id' => $order->id,
            'coupon_id' => $holds->first()->coupon_id,
            'error' => $e->getMessage(),
        ]);
        // Don't fail booking if coupon recording fails (non-critical)
    }
}
```

**Key Logic**:
1. **Check if Coupon Used**: Only execute if first hold has `coupon_id` set
2. **Call CouponService**: Use existing `recordUsage()` method (Decision #5: Idempotent)
3. **Pass Locked Discount**: Use `discount_applied` from hold (not current coupon state)
4. **Error Handling**: Catch exceptions and log, but don't fail the booking
5. **Non-Critical**: Coupon recording failure should never block successful payment

---

#### **E. Updated `confirmBooking()` (Legacy Method)**

**Location**: `SeatReservation::confirmBooking()` line ~865-891

**Same Logic as Above**, but with different log messages:
```php
\Log::info('Coupon usage recorded (legacy path)', [
    'order_id' => $order->id,
    'coupon_id' => $holds->first()->coupon_id,
    'discount_amount' => $holds->first()->discount_applied,
    'hold_token' => $holdToken,
]);
```

**Why Duplicate?**
- `confirmBooking()` is the **legacy/deprecated** method still used by some older code
- `confirmBookingWithPaymentMode()` is the **modern** method used by admin/API flows
- Both need coupon recording for 100% coverage

---

## 🔄 **Complete Data Flow (With Coupons)**

### **Phase 1: Customer Applies Coupon**

```
Frontend: User enters "SUMMER50" in coupon field
    ↓
Frontend: Calls /api/coupons/validate
    ↓
CouponController::validate() → CouponService::validateCoupon()
    ↓
Returns: { valid: true, discount: 25.00, coupon_id: 7, warning: "Expires in 2 days" }
    ↓
Frontend: Shows discount preview in UI
```

---

### **Phase 2: Customer Selects Seats**

```
Frontend: User selects 2 seats (€50 each, total €100)
    ↓
Frontend: Calculates discounted total: €100 - €25 = €75
    ↓
Frontend: Displays final price €75 in checkout
```

---

### **Phase 3: Hold Seats (WITH Coupon)**

```
Frontend: POST /api/seats/hold
Body: {
    event_id: 1,
    seat_ids: ["a1b2c3", "d4e5f6"],
    session_id: "sess_xyz",
    seat_pricing: { "a1b2c3": 50, "d4e5f6": 50 },
    coupon_code: "SUMMER50"  // ← NEW
}
    ↓
SeatController::holdSeats() receives request
    ↓
1. Validates coupon via CouponService::validateCoupon()
    ↓
2. Gets couponData: { coupon_id: 7, discount: 25.00, warning: null }
    ↓
3. Calls SeatReservation::holdSeats(..., $couponData)  // ← NEW PARAMETER
    ↓
SeatReservation::holdSeats() creates hold records:
    seat_id: "a1b2c3"
    price_snapshot: 50.00
    coupon_id: 7              // ← NEW
    discount_applied: 25.00   // ← NEW
    coupon_locked: true       // ← NEW (Decision #1: Soft-reserve)
    coupon_locked_at: 2025-11-11 14:30:00  // ← NEW
    ↓
Returns: { hold_token: "abc123", expires_at: "2025-11-11T14:40:00Z" }
```

---

### **Phase 4: Payment Processing**

```
Frontend: Stripe payment succeeds
    ↓
Frontend: POST /api/seats/confirm
Body: {
    hold_token: "abc123",
    session_id: "sess_xyz",
    customer_email: "user@example.com"
}
    ↓
SeatReservation::confirmBookingWithPaymentMode() called
    ↓
1. Loads holds with hold_token "abc123"
    ↓
2. Creates Order record (order_id: 42)
    ↓
3. Updates seat_reservations.status = 'booked'
    ↓
4. **NEW**: Checks if holds have coupon_id
    ↓
5. **NEW**: Calls CouponService::recordUsage()
    couponId: 7
    orderId: 42
    customerEmail: "user@example.com"
    discountAmount: 25.00  // ← Uses locked discount from hold
    ↓
CouponService::recordUsage() creates coupon_usage record:
    coupon_id: 7
    order_id: 42
    customer_email: "user@example.com"
    discount_amount: 25.00
    used_at: 2025-11-11 14:35:00
    ↓
Returns: { order_id: 42, payment_status: 'paid', ... }
```

---

## 🎨 **Design Decisions Implemented**

### **Decision #1: Soft-Reserve Pattern (Honor Locked Discounts)**

**Problem**: What if coupon expires/maxes-out DURING checkout (between hold and confirm)?

**Solution**: Lock coupon discount at hold time, honor it even if coupon changes later.

**Implementation**:
- Store `discount_applied` in hold record at hold time
- Set `coupon_locked = true` when coupon is used
- During confirm, use `$hold->discount_applied` (not current coupon state)

**Example Scenario**:
1. User applies "FLASH50" (50% off, max_uses: 100, currently 99 uses)
2. User holds seats → Discount locked at 50%, `coupon_locked = true`
3. Another customer uses "FLASH50" → max_uses reached (100/100)
4. First user completes payment 5 minutes later
5. **Result**: First user STILL gets 50% off (honor locked discount)

**Why This Matters**:
- Prevents "coupon expired during checkout" user complaints
- Fair to customers who applied valid coupon at hold time
- Backend honors frontend preview (consistency)

---

### **Decision #5: Idempotent Usage Recording**

**Problem**: What if webhook + frontend both call confirm? (duplicate usage records)

**Solution**: `CouponService::recordUsage()` checks for existing usage by order_id (Story 1).

**Implementation**:
- Story 1 created `coupon_usage` table with `UNIQUE(coupon_id, order_id)`
- Database constraint prevents duplicate records
- If record exists, `recordUsage()` returns early without error

**Example**:
```php
// First call (frontend confirm)
recordUsage(couponId: 7, orderId: 42) → Creates coupon_usage record

// Second call (webhook confirm)
recordUsage(couponId: 7, orderId: 42) → Unique constraint hit, returns early
```

---

## 🧪 **Testing Guide**

### **Test 1: Hold Seats WITHOUT Coupon (Backward Compatibility)**

```bash
# Expected: Should work exactly as before (no coupon fields set)

curl -X POST http://localhost:8000/api/seats/hold \
  -H "Content-Type: application/json" \
  -d '{
    "event_id": 1,
    "seat_ids": ["seat_uuid_1", "seat_uuid_2"],
    "session_id": "test_session_123",
    "seat_pricing": {"seat_uuid_1": 50, "seat_uuid_2": 50}
  }'

# Check database:
# seat_reservations.coupon_id should be NULL
# seat_reservations.discount_applied should be NULL
# seat_reservations.coupon_locked should be FALSE
```

---

### **Test 2: Hold Seats WITH Valid Coupon**

```bash
# Step 1: Validate coupon first
curl -X POST http://localhost:8000/api/coupons/validate \
  -H "Content-Type: application/json" \
  -d '{
    "code": "SUMMER50",
    "event_id": 1,
    "subtotal": 100
  }'

# Expected Response:
# {
#   "success": true,
#   "data": {
#     "valid": true,
#     "discount": 25.00,
#     "coupon_id": 7,
#     "discount_type": "percentage",
#     "discount_value": 25
#   }
# }

# Step 2: Hold seats with coupon
curl -X POST http://localhost:8000/api/seats/hold \
  -H "Content-Type: application/json" \
  -d '{
    "event_id": 1,
    "seat_ids": ["seat_uuid_1", "seat_uuid_2"],
    "session_id": "test_session_123",
    "seat_pricing": {"seat_uuid_1": 50, "seat_uuid_2": 50},
    "coupon_code": "SUMMER50"
  }'

# Expected Response:
# {
#   "success": true,
#   "data": {
#     "hold_token": "abc123...",
#     "expires_at": "2025-11-11T14:40:00Z",
#     "seats": ["seat_uuid_1", "seat_uuid_2"],
#     "total_price": 100.00
#   }
# }

# Check database:
SELECT coupon_id, discount_applied, coupon_locked, coupon_locked_at
FROM seat_reservations
WHERE hold_token = 'abc123...';

# Expected:
# coupon_id: 7
# discount_applied: 25.00
# coupon_locked: 1 (true)
# coupon_locked_at: 2025-11-11 14:30:00
```

---

### **Test 3: Confirm Booking WITH Coupon**

```bash
# Prerequisites:
# - Completed Test 2 (hold with coupon)
# - Have hold_token from Test 2 response

curl -X POST http://localhost:8000/api/seats/confirm \
  -H "Content-Type: application/json" \
  -H "Payment-Idempotency-Key: test_confirm_xyz" \
  -d '{
    "hold_token": "abc123...",
    "session_id": "test_session_123",
    "customer_email": "test@example.com",
    "customer_name": "Test User"
  }'

# Expected Response:
# {
#   "success": true,
#   "data": {
#     "order_id": 42,
#     "session_id": "test_session_123",
#     "duplicate": false
#   }
# }

# Check database - coupon_usage table:
SELECT * FROM coupon_usage WHERE order_id = 42;

# Expected:
# coupon_id: 7
# order_id: 42
# customer_email: "test@example.com"
# discount_amount: 25.00
# used_at: 2025-11-11 14:35:00
# created_at: 2025-11-11 14:35:00

# Check database - coupons table:
SELECT times_used, max_uses FROM coupons WHERE id = 7;

# Expected:
# times_used: 1 (incremented from 0)
# max_uses: 100
```

---

### **Test 4: Locked Discount Honored (Coupon Expires During Checkout)**

```bash
# Setup: Create coupon with short expiry
# 1. Create coupon expiring in 2 minutes
# 2. Hold seats with coupon (discount locked)
# 3. Wait 3 minutes (coupon now expired)
# 4. Confirm booking
# 5. Expected: Discount STILL applied (honor locked discount)

# Step 1: Create expiring coupon (via admin API or direct DB)
INSERT INTO coupons (code, discount_type, discount_value, valid_until, ...)
VALUES ('FLASH50', 'percentage', 50, NOW() + INTERVAL 2 MINUTE, ...);

# Step 2: Hold seats with coupon
curl -X POST http://localhost:8000/api/seats/hold \
  -H "Content-Type: application/json" \
  -d '{
    "event_id": 1,
    "seat_ids": ["seat_uuid_1"],
    "session_id": "test_expiry_123",
    "seat_pricing": {"seat_uuid_1": 50},
    "coupon_code": "FLASH50"
  }'

# Step 3: Wait 3 minutes (coupon now expired)
sleep 180

# Step 4: Validate coupon (should be expired)
curl -X POST http://localhost:8000/api/coupons/validate \
  -H "Content-Type: application/json" \
  -d '{"code": "FLASH50", "event_id": 1, "subtotal": 50}'

# Expected: { "valid": false, "message": "Coupon has expired" }

# Step 5: Confirm booking (should STILL work with locked discount)
curl -X POST http://localhost:8000/api/seats/confirm \
  -H "Content-Type: application/json" \
  -H "Payment-Idempotency-Key: test_expiry_confirm" \
  -d '{
    "hold_token": "xyz789...",
    "session_id": "test_expiry_123",
    "customer_email": "test@example.com"
  }'

# Expected: SUCCESS (booking confirmed with locked 50% discount)

# Check logs:
tail -f storage/logs/laravel-*.log | grep "Coupon usage recorded"

# Expected log:
# [2025-11-11 14:38:00] Coupon usage recorded
#   order_id: 43
#   coupon_id: 8
#   discount_amount: 25.00  ← Locked discount from hold time
```

---

## 📊 **Database Schema Reference**

### **Table: `seat_reservations` (Updated)**

```sql
CREATE TABLE seat_reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT NOT NULL,
    seat_id VARCHAR(255) NOT NULL,
    hold_token VARCHAR(255),
    user_id INT,
    session_id VARCHAR(255),
    status ENUM('held', 'booked', 'shadow_sold', 'blocked', 'reserved'),
    expires_at DATETIME,
    price_snapshot DECIMAL(10,2),

    -- ... other existing fields ...

    -- EPIC-BOOKING-004: Promotional Code System
    coupon_id INT NULL,                  -- FK to coupons.id
    discount_applied DECIMAL(10,2) NULL, -- Locked discount amount
    coupon_locked BOOLEAN DEFAULT FALSE, -- Soft-reserve flag
    coupon_locked_at DATETIME NULL,      -- Lock timestamp

    created_at DATETIME,
    updated_at DATETIME,

    INDEX idx_coupon_id (coupon_id),
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE SET NULL
);
```

---

### **Table: `coupon_usage` (From Story 1)**

```sql
CREATE TABLE coupon_usage (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    order_id INT NOT NULL,
    customer_email VARCHAR(191) NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL,
    used_at DATETIME NOT NULL,
    created_at DATETIME,
    updated_at DATETIME,

    UNIQUE KEY unique_coupon_order (coupon_id, order_id),  -- Idempotency
    INDEX idx_customer_email (customer_email),
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
```

---

## 🔗 **Integration Points**

### **Upstream Callers (Who Calls This Code)**

1. **`SeatController::holdSeats()`** (Story 2)
   - Validates coupon via `CouponService::validateCoupon()`
   - Passes `$couponData` to `SeatReservation::holdSeats()`
   - See: `app/Http/Controllers/API/SeatController.php` lines ~60-100

2. **Payment Webhooks** (Existing)
   - Call `SeatReservation::confirmBookingWithPaymentMode()`
   - No changes needed (backward compatible)

3. **Admin Reservation Flow** (Existing)
   - Calls `SeatReservation::confirmBookingWithPaymentMode()`
   - Can optionally support coupons in future admin UI

---

### **Downstream Dependencies (What This Code Calls)**

1. **`CouponService::recordUsage()`** (Story 1)
   - Called after successful booking confirmation
   - Parameters: `couponId`, `orderId`, `customerEmail`, `discountAmount`
   - See: `app/Services/CouponService.php` method `recordUsage()`

2. **`TimeProvider::now()`** (Existing)
   - Used for `coupon_locked_at` timestamp
   - Ensures consistent time handling across system

3. **`Log::info()` / `Log::error()`** (Existing)
   - Comprehensive logging for coupon operations
   - Search logs with: `grep -i coupon storage/logs/laravel-*.log`

---

## 🚀 **What's Next?**

### **Story 3: Frontend Public Components** (Future)

**Required Changes**:
- Add coupon input field to booking form
- Call `/api/coupons/validate` on input blur
- Display discount preview in price breakdown
- Pass `coupon_code` to `/api/seats/hold` endpoint
- Show expiry warnings from API response (Decision #7)

**No Backend Changes Needed**: Story 2.5 complete enables full frontend integration.

---

### **Story 4: Frontend Admin Dashboard** (Future)

**Required Changes**:
- Build coupon management UI (list, create, edit, delete)
- Integrate with admin API endpoints from Story 2
- Display usage analytics and statistics
- Implement bulk code generation interface

**Backend Ready**: All admin APIs from Story 2 are complete.

---

## ✅ **Completion Checklist**

- [x] Added 4 coupon fields to `SeatReservation` model `$fillable` array
- [x] Updated `holdSeats()` method signature to accept `$couponData` parameter
- [x] Modified hold record creation to store coupon data when provided
- [x] Updated `confirmBookingWithPaymentMode()` to record coupon usage
- [x] Updated `confirmBooking()` (legacy) for backward compatibility
- [x] Comprehensive error handling (non-critical failures don't block booking)
- [x] Extensive logging for coupon operations (audit trail)
- [x] Backward compatible (all new parameters optional, existing code works)
- [x] Documentation complete with examples and testing guide

---

## 📝 **Files Modified**

### **1. `app/Model/SeatReservation.php`** (4 changes)

**Change A: Updated `$fillable` array** (lines ~13-43)
- Added: `coupon_id`, `discount_applied`, `coupon_locked`, `coupon_locked_at`

**Change B: Updated `holdSeats()` signature** (line ~110)
- Added parameter: `$couponData = null`

**Change C: Updated hold creation logic** (lines ~341-369)
- Conditionally store coupon fields if `$couponData` provided
- Set `coupon_locked = true` for soft-reserve

**Change D: Updated `confirmBookingWithPaymentMode()`** (lines ~595-621)
- Added coupon usage recording after successful booking
- Calls `CouponService::recordUsage()` if hold has coupon

**Change E: Updated `confirmBooking()` (legacy)** (lines ~865-891)
- Same coupon recording logic for backward compatibility
- Different log messages to distinguish paths

---

## 📚 **Related Documentation**

- **Story 1**: `STORY_1_DATABASE_COMPLETE.md` - Database schema and `CouponService`
- **Story 2**: `STORY_2_API_ENDPOINTS_COMPLETE.md` - API endpoints and `SeatController` integration
- **Next**: Story 3 (Frontend Public) and Story 4 (Frontend Admin) will consume this work

---

## 🎉 **Story 2.5 Status: COMPLETE**

All backend integration is **100% complete**. The promotional code system is now fully operational from database → service layer → API endpoints → booking flow.

**Ready for**:
- Frontend integration (Stories 3 & 4)
- Testing with real coupon codes
- Production deployment (after QA)

**Commit this work to**: `feature/promo-codes-model-integration` branch
**PR Target**: Merge to `promo-codes-system` branch (then to `dev`)