# Database Schema

## Overview

The EventBookings Shopify app uses a relational database schema to manage store connections, API credentials, and event display preferences. The schema is designed to be framework-agnostic and easily portable between different database systems.

## Entity Relationship Diagram

```
┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   ShopifyStore  │    │  StoreSettings  │    │   EventToggle   │
│                 │    │                 │    │                 │
│ • id (PK)       │◄──┤ • id (PK)       │    │ • id (PK)       │
│ • shop_domain   │   │ • store_id (FK) │    │ • store_id (FK) │◄─┐
│ • access_token  │   │ • client_id     │    │ • event_uuid    │  │
│ • created_at    │   │ • client_secret │    │ • show_event    │  │
│ • updated_at    │   │ • access_token  │    │ • featured_event│  │
└─────────────────┘   │ • org_uuid      │    │ • created_at    │  │
                      │ • token_expires │    │ • updated_at    │  │
                      │ • is_connected  │    └─────────────────┘  │
                      │ • connection_err│                        │
                      │ • created_at    │                        │
                      │ • updated_at    │                        │
                      └─────────────────┘                        │
                                                                │
┌─────────────────┐                                            │
│   ShopifyPage   │                                            │
│   (Optional)    │                                            │
│                 │                                            │
│ • id (PK)       │                                            │
│ • store_id (FK) │◄───────────────────────────────────────────┘
│ • shopify_page_id│
│ • page_title    │
│ • page_handle   │
│ • page_content  │
│ • page_type     │
│ • page_status   │
│ • template_name │
│ • widget_config │
│ • auto_created  │
│ • auto_update   │
│ • created_at    │
│ • updated_at    │
│ • last_synced_at│
└─────────────────┘
```

## Table Definitions

### shopify_stores

Core table storing Shopify store information and access credentials.

```sql
CREATE TABLE shopify_stores (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    shop_domain VARCHAR(255) NOT NULL UNIQUE,
    access_token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_shop_domain (shop_domain),
    INDEX idx_created_at (created_at)
);
```

**PHP Model (Laravel Example)**:
```php
class ShopifyStore extends Model
{
    protected $fillable = [
        'shop_domain',
        'access_token'
    ];

    protected $hidden = [
        'access_token'
    ];

    protected $casts = [
        'created_at' => 'datetime',
        'updated_at' => 'datetime'
    ];

    // Relationships
    public function storeSettings()
    {
        return $this->hasOne(StoreSettings::class, 'store_id');
    }

    public function eventToggles()
    {
        return $this->hasMany(EventToggle::class, 'store_id');
    }

    public function pages()
    {
        return $this->hasMany(ShopifyPage::class, 'store_id');
    }

    // Accessors/Mutators
    public function setAccessTokenAttribute($value)
    {
        $this->attributes['access_token'] = encrypt($value);
    }

    public function getAccessTokenAttribute($value)
    {
        return decrypt($value);
    }
}
```

### store_settings

EventBookings API integration settings for each store.

```sql
CREATE TABLE store_settings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    store_id BIGINT NOT NULL,
    client_id VARCHAR(255) DEFAULT '',
    client_secret VARCHAR(255) DEFAULT '',
    access_token TEXT DEFAULT '',
    org_uuid VARCHAR(255) DEFAULT '',
    token_expires_at TIMESTAMP NULL,
    is_connected BOOLEAN DEFAULT FALSE,
    connection_error TEXT DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (store_id) REFERENCES shopify_stores(id) ON DELETE CASCADE,
    UNIQUE KEY unique_store_settings (store_id),
    INDEX idx_store_id (store_id),
    INDEX idx_is_connected (is_connected),
    INDEX idx_token_expires (token_expires_at)
);
```

**PHP Model (Laravel Example)**:
```php
class StoreSettings extends Model
{
    protected $fillable = [
        'store_id',
        'client_id',
        'client_secret',
        'access_token',
        'org_uuid',
        'token_expires_at',
        'is_connected',
        'connection_error'
    ];

    protected $hidden = [
        'client_secret',
        'access_token'
    ];

    protected $casts = [
        'token_expires_at' => 'datetime',
        'is_connected' => 'boolean',
        'created_at' => 'datetime',
        'updated_at' => 'datetime'
    ];

    // Relationships
    public function store()
    {
        return $this->belongsTo(ShopifyStore::class, 'store_id');
    }

    // Accessors/Mutators
    public function setClientSecretAttribute($value)
    {
        $this->attributes['client_secret'] = encrypt($value);
    }

    public function getClientSecretAttribute($value)
    {
        return $value ? decrypt($value) : '';
    }

    public function setAccessTokenAttribute($value)
    {
        $this->attributes['access_token'] = $value ? encrypt($value) : '';
    }

    public function getAccessTokenAttribute($value)
    {
        return $value ? decrypt($value) : '';
    }

    // Helper Methods
    public function isTokenExpired()
    {
        return $this->token_expires_at &&
               $this->token_expires_at->isPast();
    }

    public function isTokenExpiringSoon($minutes = 5)
    {
        return $this->token_expires_at &&
               $this->token_expires_at->diffInMinutes(now()) <= $minutes;
    }

    public function hasValidCredentials()
    {
        return !empty($this->client_id) &&
               !empty($this->client_secret);
    }
}
```

### event_toggles

Event visibility and featured status controls.

```sql
CREATE TABLE event_toggles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    store_id BIGINT NOT NULL,
    event_uuid VARCHAR(255) NOT NULL,
    show_event BOOLEAN DEFAULT TRUE,
    featured_event BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (store_id) REFERENCES shopify_stores(id) ON DELETE CASCADE,
    UNIQUE KEY unique_store_event (store_id, event_uuid),
    INDEX idx_store_id (store_id),
    INDEX idx_event_uuid (event_uuid),
    INDEX idx_show_event (show_event),
    INDEX idx_featured_event (featured_event),
    INDEX idx_store_featured (store_id, featured_event)
);
```

**PHP Model (Laravel Example)**:
```php
class EventToggle extends Model
{
    protected $fillable = [
        'store_id',
        'event_uuid',
        'show_event',
        'featured_event'
    ];

    protected $casts = [
        'show_event' => 'boolean',
        'featured_event' => 'boolean',
        'created_at' => 'datetime',
        'updated_at' => 'datetime'
    ];

    // Relationships
    public function store()
    {
        return $this->belongsTo(ShopifyStore::class, 'store_id');
    }

    // Scopes
    public function scopeVisible($query)
    {
        return $query->where('show_event', true);
    }

    public function scopeFeatured($query)
    {
        return $query->where('featured_event', true);
    }

    public function scopeForStore($query, $storeId)
    {
        return $query->where('store_id', $storeId);
    }

    // Helper Methods
    public static function getEventSettings($storeId, $eventUuid)
    {
        return self::where('store_id', $storeId)
                   ->where('event_uuid', $eventUuid)
                   ->first();
    }

    public static function setEventVisibility($storeId, $eventUuid, $visible)
    {
        return self::updateOrCreate(
            ['store_id' => $storeId, 'event_uuid' => $eventUuid],
            ['show_event' => $visible]
        );
    }

    public static function setEventFeatured($storeId, $eventUuid, $featured)
    {
        return self::updateOrCreate(
            ['store_id' => $storeId, 'event_uuid' => $eventUuid],
            ['featured_event' => $featured]
        );
    }
}
```

### shopify_pages (Optional)

For advanced page management functionality (can be omitted for basic implementation).

```sql
CREATE TABLE shopify_pages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    store_id BIGINT NOT NULL,
    shopify_page_id VARCHAR(100) DEFAULT '',
    page_title VARCHAR(255) NOT NULL,
    page_handle VARCHAR(255) NOT NULL,
    page_content TEXT DEFAULT '',
    page_type ENUM('events_list', 'featured_events', 'event_details', 'custom') DEFAULT 'custom',
    page_status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    template_name VARCHAR(100) DEFAULT '',
    widget_config JSON,
    auto_created BOOLEAN DEFAULT FALSE,
    auto_update BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_synced_at TIMESTAMP NULL,

    FOREIGN KEY (store_id) REFERENCES shopify_stores(id) ON DELETE CASCADE,
    UNIQUE KEY unique_store_handle (store_id, page_handle),
    INDEX idx_store_id (store_id),
    INDEX idx_page_type (page_type),
    INDEX idx_page_status (page_status),
    INDEX idx_auto_created (auto_created),
    INDEX idx_last_synced (last_synced_at)
);
```

## Database Migrations

### Laravel Migration Examples

**Create Shopify Stores Table**:
```php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateShopifyStoresTable extends Migration
{
    public function up()
    {
        Schema::create('shopify_stores', function (Blueprint $table) {
            $table->id();
            $table->string('shop_domain')->unique();
            $table->string('access_token');
            $table->timestamps();

            $table->index('shop_domain');
            $table->index('created_at');
        });
    }

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

**Create Store Settings Table**:
```php
class CreateStoreSettingsTable extends Migration
{
    public function up()
    {
        Schema::create('store_settings', function (Blueprint $table) {
            $table->id();
            $table->foreignId('store_id')->constrained('shopify_stores')->onDelete('cascade');
            $table->string('client_id')->default('');
            $table->string('client_secret')->default('');
            $table->text('access_token')->default('');
            $table->string('org_uuid')->default('');
            $table->timestamp('token_expires_at')->nullable();
            $table->boolean('is_connected')->default(false);
            $table->text('connection_error')->default('');
            $table->timestamps();

            $table->unique('store_id');
            $table->index(['store_id']);
            $table->index(['is_connected']);
            $table->index(['token_expires_at']);
        });
    }

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

**Create Event Toggles Table**:
```php
class CreateEventTogglesTable extends Migration
{
    public function up()
    {
        Schema::create('event_toggles', function (Blueprint $table) {
            $table->id();
            $table->foreignId('store_id')->constrained('shopify_stores')->onDelete('cascade');
            $table->string('event_uuid');
            $table->boolean('show_event')->default(true);
            $table->boolean('featured_event')->default(false);
            $table->timestamps();

            $table->unique(['store_id', 'event_uuid']);
            $table->index(['store_id']);
            $table->index(['event_uuid']);
            $table->index(['show_event']);
            $table->index(['featured_event']);
            $table->index(['store_id', 'featured_event']);
        });
    }

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

## Data Access Patterns

### Common Queries

**Get Store with Settings**:
```php
$store = ShopifyStore::with('storeSettings')
    ->where('shop_domain', $shopDomain)
    ->first();
```

**Get Visible Events for Store**:
```php
$visibleEvents = EventToggle::forStore($storeId)
    ->visible()
    ->pluck('event_uuid')
    ->toArray();
```

**Get Featured Events for Store**:
```php
$featuredEvents = EventToggle::forStore($storeId)
    ->featured()
    ->visible()
    ->pluck('event_uuid')
    ->toArray();
```

**Update Event Toggle**:
```php
EventToggle::updateOrCreate(
    ['store_id' => $storeId, 'event_uuid' => $eventUuid],
    ['show_event' => $visible, 'featured_event' => $featured]
);
```

### Repository Pattern (Optional)

For cleaner architecture, consider implementing repositories:

```php
interface StoreRepositoryInterface
{
    public function findByDomain(string $domain): ?ShopifyStore;
    public function createStore(array $data): ShopifyStore;
    public function updateSettings(int $storeId, array $settings): bool;
    public function getEventToggles(int $storeId): Collection;
}

class StoreRepository implements StoreRepositoryInterface
{
    public function findByDomain(string $domain): ?ShopifyStore
    {
        return ShopifyStore::with('storeSettings')
            ->where('shop_domain', $domain)
            ->first();
    }

    public function createStore(array $data): ShopifyStore
    {
        return ShopifyStore::create($data);
    }

    public function updateSettings(int $storeId, array $settings): bool
    {
        return StoreSettings::updateOrCreate(
            ['store_id' => $storeId],
            $settings
        );
    }

    public function getEventToggles(int $storeId): Collection
    {
        return EventToggle::forStore($storeId)->get();
    }
}
```

## Performance Considerations

### Indexing Strategy
- Primary keys on all tables
- Foreign key indexes for relationships
- Composite indexes for common query patterns
- Covering indexes for frequently accessed columns

### Caching Recommendations
- Store settings (rarely change)
- Event toggles (moderate change frequency)
- API tokens (with expiration awareness)

### Query Optimization
- Use eager loading for relationships
- Implement query result caching
- Consider read replicas for high-traffic scenarios
- Monitor slow query logs

This schema provides a solid foundation for the EventBookings Shopify app, balancing simplicity with functionality while maintaining good performance characteristics.