# Admin Seat Details Endpoint - Data Relationships Diagram

## Entity Relationship Overview

```
┌─────────────────────────────────────────────────────────────────────────┐
│                        ADMIN SEAT DETAILS ENDPOINT                       │
│                   GET /api/admin/seats/{seat_id}/details                 │
└─────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ↓
        ┌───────────────────────────────────────────────────┐
        │              PRIMARY DATA: SEAT                    │
        │  ┌─────────────────────────────────────────────┐  │
        │  │  seats                                       │  │
        │  │  - seat_id (PK)                             │  │
        │  │  - event_id (FK)                            │  │
        │  │  - row_letter, seat_number                  │  │
        │  │  - price, zone, section_id                  │  │
        │  │  - seat_type, is_accessible                 │  │
        │  │  - position, parent_table_id                │  │
        │  └─────────────────────────────────────────────┘  │
        └───────────────────────────────────────────────────┘
                    │                           │
                    ↓                           ↓
    ┌──────────────────────────┐   ┌──────────────────────────┐
    │   EVENT CONTEXT          │   │   RESERVATION STATUS      │
    │  ┌────────────────────┐  │   │  ┌────────────────────┐  │
    │  │  events            │  │   │  │  seat_reservations │  │
    │  │  - id (PK)         │  │   │  │  - id (PK)         │  │
    │  │  - name            │  │   │  │  - seat_id (FK)    │  │
    │  │  - slug            │  │   │  │  - event_id (FK)   │  │
    │  │  - start_date      │  │   │  │  - status          │  │
    │  │  - venue_id        │  │   │  │  - hold_token      │  │
    │  │  - status          │  │   │  │  - session_id      │  │
    │  └────────────────────┘  │   │  │  - user_id         │  │
    └──────────────────────────┘   │  │  - expires_at      │  │
                                   │  │  - price_snapshot  │  │
                                   │  └────────────────────┘  │
                                   └──────────────────────────┘
                                              │
                                              ↓
                ┌─────────────────────────────────────────────────────┐
                │                  BOOKING DATA                        │
                │  ┌───────────────────────────────────────────────┐  │
                │  │  orders                                        │  │
                │  │  - id (PK)                                     │  │
                │  │  - session_id (matches seat_reservations)     │  │
                │  │  - event_id (FK)                               │  │
                │  │  - customer_name, customer_email ⭐            │  │
                │  │  - customer_phone                              │  │
                │  │  - total_amount                                │  │
                │  │  - status, payment_status, payment_method     │  │
                │  │  - payment_mode, idempotency_key              │  │
                │  │  - created_by_admin_id, admin_notes           │  │
                │  └───────────────────────────────────────────────┘  │
                └─────────────────────────────────────────────────────┘
                            │                           │
                            ↓                           ↓
        ┌───────────────────────────┐   ┌───────────────────────────┐
        │   PAYMENT DETAILS         │   │   LINE ITEMS BREAKDOWN     │
        │  ┌─────────────────────┐  │   │  ┌─────────────────────┐  │
        │  │  payment_transactions│  │   │  │  order_line_items   │  │
        │  │  - id (PK)          │  │   │  │  - id (PK)          │  │
        │  │  - order_id (FK)    │  │   │  │  - order_id (FK)    │  │
        │  │  - payment_gateway  │  │   │  │  - item_type        │  │
        │  │  - transaction_id   │  │   │  │  - description      │  │
        │  │  - amount, currency │  │   │  │  - quantity         │  │
        │  │  - status           │  │   │  │  - unit_price       │  │
        │  │  - initiated_at     │  │   │  │  - line_total       │  │
        │  │  - completed_at     │  │   │  │  - tax_rate         │  │
        │  │  - metadata         │  │   │  │  - seat_id (FK) ⭐  │  │
        │  └─────────────────────┘  │   │  └─────────────────────┘  │
        └───────────────────────────┘   └───────────────────────────┘


                        ┌─────────────────────────────┐
                        │   OPTIONAL: HISTORY         │
                        │  ┌───────────────────────┐  │
                        │  │  seat_reservations    │  │
                        │  │  (previous records)   │  │
                        │  │  - status transitions │  │
                        │  │  - timestamps         │  │
                        │  │  - hold tokens        │  │
                        │  └───────────────────────┘  │
                        └─────────────────────────────┘

                        ┌─────────────────────────────┐
                        │   OPTIONAL: AUDIT LOGS      │
                        │  ┌───────────────────────┐  │
                        │  │  mbs_activity_logs    │  │
                        │  │  - action_type        │  │
                        │  │  - performed_by       │  │
                        │  │  - seat_id (FK)       │  │
                        │  │  - metadata           │  │
                        │  └───────────────────────┘  │
                        └─────────────────────────────┘
```

---

## Query Flow & Join Strategy

### Primary Query (Single Database Call with Eager Loading)

```sql
SELECT
    -- Seat base data
    seats.*,

    -- Event context
    events.id, events.name, events.slug, events.start_date, events.venue_id, events.status,

    -- Reservation status
    sr.id AS reservation_id, sr.status, sr.hold_token, sr.session_id, sr.user_id,
    sr.expires_at, sr.price_snapshot, sr.created_at AS reserved_at,

    -- Order details (if booked)
    orders.id AS order_id, orders.customer_name, orders.customer_email, orders.customer_phone,
    orders.total_amount, orders.status AS order_status, orders.payment_status, orders.payment_method,
    orders.payment_mode, orders.idempotency_key, orders.created_by_admin_id, orders.admin_notes,

    -- Payment transaction (if exists)
    pt.id AS payment_transaction_id, pt.payment_gateway, pt.transaction_id,
    pt.amount AS payment_amount, pt.currency, pt.status AS payment_status_detail,
    pt.initiated_at, pt.completed_at, pt.metadata AS payment_metadata

FROM seats

-- Join event (always present)
INNER JOIN events ON events.id = seats.event_id

-- Join reservation (may not exist if seat never reserved)
LEFT JOIN seat_reservations sr ON sr.seat_id = seats.seat_id
    AND sr.event_id = seats.event_id
    AND sr.id = (
        SELECT id FROM seat_reservations
        WHERE seat_id = seats.seat_id
        AND event_id = seats.event_id
        ORDER BY created_at DESC
        LIMIT 1
    )

-- Join order (only if reservation status = 'booked')
LEFT JOIN orders ON orders.session_id = sr.session_id
    AND orders.event_id = seats.event_id

-- Join payment transaction (only if order exists)
LEFT JOIN payment_transactions pt ON pt.order_id = orders.id
    AND pt.id = (
        SELECT id FROM payment_transactions
        WHERE order_id = orders.id
        ORDER BY created_at DESC
        LIMIT 1
    )

WHERE seats.seat_id = :seat_id
  AND seats.event_id = :event_id (optional but recommended)
```

### Line Items Query (Separate Query, Conditional)

```sql
SELECT
    oli.*
FROM order_line_items oli
WHERE oli.order_id = :order_id
ORDER BY
    FIELD(oli.item_type, 'ticket', 'booking_fee', 'tax', 'discount'),
    oli.created_at ASC
```

### History Query (Optional, `?include_history=true`)

```sql
SELECT
    sr.id, sr.status, sr.hold_token, sr.session_id, sr.expires_at, sr.created_at
FROM seat_reservations sr
WHERE sr.seat_id = :seat_id
  AND sr.event_id = :event_id
ORDER BY sr.created_at DESC
LIMIT 50
```

### Audit Logs Query (Optional, `?include_audit_logs=true`)

```sql
SELECT
    mal.id, mal.action_type, mal.action_category, mal.action_description,
    mal.performed_by_type, mal.performed_by_id, mal.performed_by_name,
    mal.performed_at, mal.metadata
FROM mbs_activity_logs mal
WHERE mal.seat_id = :seat_id
  AND mal.event_id = :event_id
ORDER BY mal.performed_at DESC
LIMIT 100
```

### Related Seats Section Stats Query (Optional)

```sql
SELECT
    COUNT(*) AS total_seats,
    SUM(CASE WHEN sr.status IS NULL THEN 1 ELSE 0 END) AS available_seats,
    SUM(CASE WHEN sr.status = 'held' AND sr.expires_at > NOW() THEN 1 ELSE 0 END) AS held_seats,
    SUM(CASE WHEN sr.status = 'booked' THEN 1 ELSE 0 END) AS booked_seats,
    SUM(CASE WHEN sr.status = 'shadow_sold' THEN 1 ELSE 0 END) AS shadow_sold_seats,
    SUM(CASE WHEN sr.status = 'blocked' THEN 1 ELSE 0 END) AS blocked_seats
FROM seats s
LEFT JOIN seat_reservations sr ON sr.seat_id = s.seat_id
    AND sr.event_id = s.event_id
    AND sr.id = (
        SELECT id FROM seat_reservations
        WHERE seat_id = s.seat_id
        AND event_id = s.event_id
        ORDER BY created_at DESC
        LIMIT 1
    )
WHERE s.section_id = :section_id
  AND s.event_id = :event_id
  AND s.seat_type != 'table'  -- Exclude parent tables
  AND s.is_active = 1
```

---

## Key Relationships Explained

### 1. **Seat → Reservation** (1:many, but use latest)
```
seats.seat_id + seats.event_id → seat_reservations.seat_id + seat_reservations.event_id
```
**Key**: A seat can have multiple reservations over time (held → expired → held again → booked).
**Solution**: Always fetch the **latest** reservation by `created_at DESC`.

### 2. **Reservation → Order** (1:1 via session_id)
```
seat_reservations.session_id + seat_reservations.event_id → orders.session_id + orders.event_id
```
**Key**: When a hold is confirmed, the `session_id` links reservation to order.
**Note**: Only exists when `status = 'booked'`.

### 3. **Order → Payment Transaction** (1:many, but use latest)
```
orders.id → payment_transactions.order_id
```
**Key**: An order can have multiple payment attempts (failed → retry → success).
**Solution**: Fetch the **latest** payment transaction by `created_at DESC`.

### 4. **Order → Line Items** (1:many)
```
orders.id → order_line_items.order_id
```
**Key**: Each order has multiple line items (tickets, fees, taxes, discounts).
**Note**: Filter by `seat_id` to get seat-specific line items.

### 5. **Seat → Event** (many:1)
```
seats.event_id → events.id
```
**Key**: All seats belong to an event. Always fetch event context.

---

## Data Flow: Customer Books Seat

```
1. Customer visits booking page
   └─> Frontend calls: GET /api/venue/availability/{eventId}
   └─> Returns: Seat status, price (NO owner info)

2. Customer selects seat A1
   └─> Frontend calls: POST /api/seats/hold
   └─> Creates: seat_reservations record (status='held')
   └─> Returns: hold_token, expires_at

3. Customer enters payment details
   └─> Frontend calls: POST /api/payment/create-intent
   └─> Creates: payment_transactions record (status='pending')

4. Customer completes payment
   └─> Stripe webhook: POST /api/webhooks/stripe
   └─> Updates: payment_transactions (status='completed')
   └─> Creates: orders record
   └─> Creates: order_line_items records (ticket, fee, tax)
   └─> Updates: seat_reservations (status='booked')

5. Admin hovers over seat A1 ⭐ **NEW ENDPOINT USED HERE**
   └─> Frontend calls: GET /api/admin/seats/seat-A1-12345/details
   └─> Returns: EVERYTHING (seat, reservation, order, payment, line items)
   └─> Admin sees: "Booked by John Doe (john.doe@example.com), Order #5678"
```

---

## Performance: Query Optimization

### Before Optimization (N+1 Problem)
```
Query 1: SELECT * FROM seats WHERE seat_id = ?                    -- 1 query
Query 2: SELECT * FROM events WHERE id = ?                        -- 1 query
Query 3: SELECT * FROM seat_reservations WHERE seat_id = ?        -- 1 query
Query 4: SELECT * FROM orders WHERE session_id = ?                -- 1 query
Query 5: SELECT * FROM payment_transactions WHERE order_id = ?    -- 1 query
Query 6: SELECT * FROM order_line_items WHERE order_id = ?        -- 1 query

TOTAL: 6 queries (N+1 problem if iterating over seats)
```

### After Optimization (Eager Loading)
```
Query 1: SELECT * FROM seats
         LEFT JOIN events ON ...
         LEFT JOIN seat_reservations ON ...
         LEFT JOIN orders ON ...
         LEFT JOIN payment_transactions ON ...
         WHERE seats.seat_id = ?                                  -- 1 query

Query 2: SELECT * FROM order_line_items WHERE order_id = ?        -- 1 query

TOTAL: 2 queries (with proper indexing: < 100ms)
```

### Required Indexes
```sql
-- Already exist (from existing migrations)
CREATE INDEX idx_seat_reservations_seat_id ON seat_reservations(seat_id);
CREATE INDEX idx_seat_reservations_event_id ON seat_reservations(event_id);
CREATE INDEX idx_seats_event_id ON seats(event_id);

-- Should add (if not exist)
CREATE INDEX idx_order_line_items_order_id ON order_line_items(order_id);
CREATE INDEX idx_order_line_items_seat_id ON order_line_items(seat_id);
CREATE INDEX idx_mbs_activity_logs_seat_id ON mbs_activity_logs(seat_id);
CREATE INDEX idx_payment_transactions_order_id ON payment_transactions(order_id);
```

---

## Summary

This diagram shows how **10+ database tables** are connected to provide comprehensive seat information:

✅ **Core Path**: Seat → Reservation → Order → Payment (4 tables, 2 queries)
✅ **Supporting Data**: Event, Line Items (2 tables, 1 query)
✅ **Optional Data**: History, Audit Logs (2 tables, 2 queries)

**Total Query Count**:
- **Minimum**: 2 queries (seat with order, line items)
- **With History**: 3 queries (+1 for history)
- **With Audit Logs**: 4 queries (+1 for audit logs)
- **With Everything**: 5 queries (all data)

**Target Response Time**: < 300ms (with proper indexing and eager loading)