# Fermenty · Modelo Entidad-Relación (ERM)

> Documento de referencia del esquema de base de datos actual, generado a partir de las
> migraciones (`database/migrations/`) y las relaciones Eloquent de los modelos (`app/Models/`).
> Pensado como base para planificar la integración del **MENTOR**.
>
> Fecha de referencia: 2026-07-22

---

## 1. Cadena de clasificación (el corazón del contenido)

Ésta es la columna vertebral que pediste destacar. La jerarquía va de lo más general
(la **familia** de fermentación) a lo concreto (el **lote** real de un usuario):

```
FermentationCategory  →  FermentType  →  FermentationGuide  →  Recipe  →  Batch
   (familia)              (producto)      (protocolo maestro)   (variante)  (instancia real)
```

- **FermentationCategory** — grandes familias microbiológicas/culinarias (láctica, ácida, alcohólica, fúngica…). Agrupa tipos.
- **FermentType** — el producto fermentable concreto (kombucha, chucrut, kéfir, vinagre…). Es la unidad de _plan/gating_ (`plan_required`) y dificultad.
- **FermentationGuide** — el **protocolo maestro** versionado de un tipo (`is_current` marca la vigente). Contiene los pasos, ingredientes base y estructura de escalado.
- **Recipe** — una **variante** sobre una guía: overrides de pasos/ingredientes, autoría de usuario, estado de publicación, rating. Es lo que el usuario elige para empezar.
- **Batch** — la **instancia real** que fermenta un usuario, con su copia (_snapshot_) de pasos e ingredientes y todo el seguimiento.

```mermaid
erDiagram
    FERMENTATION_CATEGORIES ||--o{ FERMENT_TYPES : "clasifica"
    FERMENT_TYPES          ||--o{ FERMENTATION_GUIDES : "tiene versiones"
    FERMENT_TYPES          ||--o{ FERMENT_OPTIONS : "parámetros"
    FERMENT_TYPES          ||--o{ NOTIFICATION_RULES : "reglas"
    FERMENTATION_GUIDES    ||--o{ GUIDE_STEPS : "pasos"
    FERMENTATION_GUIDES    ||--o{ GUIDE_INGREDIENTS : "ingredientes base"
    FERMENTATION_GUIDES    ||--o{ RECIPES : "instancia como"
    GUIDE_STEPS            ||--o{ STEP_ACTIONS : "acciones"
    GUIDE_STEPS            ||--o{ GUIDE_STEP_HINTS : "consejos"
    GUIDE_STEPS            ||--o{ GUIDE_INGREDIENTS : "usados en"
    RECIPES                ||--o{ RECIPE_STEPS : "overrides paso"
    RECIPES                ||--o{ RECIPE_INGREDIENTS : "ingredientes"
    RECIPE_STEPS           }o--|| GUIDE_STEPS : "referencia"
    RECIPE_INGREDIENTS     }o--o| RECIPE_STEPS : "en paso"
    RECIPE_INGREDIENTS     }o--o| GUIDE_STEPS : "en paso guía"
    RECIPES                }o--o| RECIPES : "source_recipe (fork)"

    FERMENTATION_CATEGORIES {
        id id PK
        string name
        string slug UK
        text description
        int position
    }
    FERMENT_TYPES {
        id id PK
        id fermentation_category_id FK
        string name
        string slug UK
        enum difficulty_level "easy|medium|hard"
        int default_duration_days
        enum plan_required "free|premium"
        bool is_active
    }
    FERMENTATION_GUIDES {
        id id PK
        id ferment_type_id FK
        string name
        string version "def 1.0"
        bool is_current
    }
    GUIDE_STEPS {
        id id PK
        id guide_id FK
        int step_number "UK(guide,step)"
        string title
        int min_day
        int max_day
        bool is_optional
    }
    STEP_ACTIONS {
        id id PK
        id step_id FK
        enum action_type
        text description
        int order
    }
    GUIDE_STEP_HINTS {
        id id PK
        id guide_step_id FK
        int order
    }
    GUIDE_INGREDIENTS {
        id id PK
        id guide_id FK
        id guide_step_id FK "opcional"
        string name
        decimal quantity
        string unit
        enum phase "primary|secondary"
        bool is_optional
        int order
    }
    FERMENT_OPTIONS {
        id id PK
        id ferment_type_id FK
        string option_name
        json option_values
    }
    RECIPES {
        id id PK
        id user_id FK "nullable = oficial"
        id ferment_type_id FK
        id guide_id FK
        id source_recipe_id FK "fork"
        string name
        string slug
        decimal base_yield_value
        string base_yield_unit
        enum status "draft|pending|published|rejected"
        bool is_official
        string visibility
        int likes_count
        decimal rating_avg
        int rating_count
        int times_brewed
    }
    RECIPE_STEPS {
        id id PK
        id recipe_id FK
        id guide_step_id FK
        bool is_included
        int min_day "override"
        int max_day "override"
    }
    RECIPE_INGREDIENTS {
        id id PK
        id recipe_id FK
        id recipe_step_id FK "nullable"
        id guide_step_id FK "nullable"
        string name
        decimal quantity
        string unit
        enum phase "primary|secondary"
        bool is_optional
        int order
    }
```

### Notas de diseño de la cadena
- **Guía vs. Receta**: la guía es el protocolo canónico (propiedad del sistema); la receta es la
  personalización. `RECIPE_STEPS` referencia `GUIDE_STEPS` y puede _incluir/excluir_ y sobreescribir
  ventanas de días. Los ingredientes de receta pueden colgar de un `recipe_step` o directamente de un `guide_step`.
- **Escalado**: `base_yield_value/unit` en receta + `quantity` canónica (métrica) en ingredientes
  permiten recalcular cantidades a un rendimiento objetivo.
- **Autoría**: `recipes.user_id` NULL ⇒ receta oficial del sistema; con `user_id` ⇒ creada por usuario.
  `source_recipe_id` implementa _forks_ (recetas derivadas de otra).

---

## 2. Lotes y seguimiento (instancia real del usuario)

Un `Batch` **congela un snapshot** de la guía/receta elegida para que cambios posteriores en el
protocolo no alteren un fermento en curso.

```mermaid
erDiagram
    USERS               ||--o{ BATCHES : "fermenta"
    FERMENT_TYPES       ||--o{ BATCHES : "de tipo"
    FERMENTATION_GUIDES ||--o{ BATCHES : "según guía"
    RECIPES             ||--o{ BATCHES : "según receta"

    BATCHES        ||--o{ BATCH_STEPS : "pasos (snapshot)"
    BATCHES        ||--o{ BATCH_INGREDIENTS : "ingredientes (snapshot)"
    BATCHES        ||--o{ BATCH_PHOTOS : "fotos"
    BATCHES        ||--o{ DAILY_LOGS : "diario"
    BATCHES        ||--o{ BATCH_EVENTS : "eventos"
    BATCHES        ||--o{ INSIGHTS : "insights"
    BATCHES        ||--o{ FERMENTY_NOTIFICATIONS : "notificaciones"
    BATCHES        ||--o| BATCH_REVIEWS : "reseña"
    BATCHES        ||--o| FLAVOR_RESULTS : "resultado sabor"

    BATCH_STEPS    }o--o| GUIDE_STEPS : "origen guía"
    BATCH_STEPS    }o--o| RECIPE_STEPS : "origen receta"
    BATCH_STEPS    ||--o{ BATCH_PHOTOS : "foto de paso"
    DAILY_LOGS     ||--o{ MEASUREMENTS : "mediciones"
    BATCH_REVIEWS  ||--o| FLAVOR_RESULTS : "sabor"

    BATCHES {
        id id PK
        id user_id FK
        id ferment_type_id FK "restrict"
        id guide_id FK
        id recipe_id FK "nullable"
        int temp
        int duration
    }
    BATCH_STEPS {
        id id PK
        id batch_id FK
        id guide_step_id FK "nullable"
        id recipe_step_id FK "nullable, snapshot"
    }
    BATCH_INGREDIENTS {
        id id PK
        id batch_id FK
        id guide_ingredient_id FK
        id recipe_ingredient_id FK
        id batch_step_id FK
        id guide_step_id FK
    }
    BATCH_PHOTOS {
        id id PK
        id batch_id FK
        id step_id FK "batch_steps"
        datetime taken_at
    }
    DAILY_LOGS {
        id id PK
        id batch_id FK
        date date
    }
    MEASUREMENTS {
        id id PK
        id log_id FK
        string unit "nullable"
    }
    BATCH_EVENTS {
        id id PK
        id batch_id FK
        enum event_type "incl. mood"
        date event_date
    }
    BATCH_REVIEWS {
        id id PK
        id batch_id FK
        int rating "nullable"
    }
    FLAVOR_RESULTS {
        id id PK
        id batch_id FK
    }
    INSIGHTS {
        id id PK
        id batch_id FK
    }
```

---

## 3. Usuarios, auth y crecimiento

```mermaid
erDiagram
    USERS ||--o{ USER_SOCIAL_PROVIDERS : "OAuth"
    USERS ||--o{ LOGIN_LOGS : "accesos"
    USERS ||--o{ REFERRALS : "recomienda"
    USERS ||--o{ REFERRALS : "es referido"
    USERS }o--o| FERMENT_TYPES : "favorito"
    USERS ||--o{ PWA_INSTALLS : "instalaciones"
    USERS ||--o{ COMMUNITY_POSTS : "publica"
    USERS ||--o{ RECIPES : "crea"
    USERS ||--o{ FERMENTY_NOTIFICATIONS : "recibe"

    USERS {
        id id PK
        string name
        string email
        id favorite_ferment_type_id FK
        string unit_system
        datetime premium_until
        bool onboarding_tour
        bool daily_digest
        text bio
        string registration_source
        datetime reengagement_sent_at
    }
    USER_SOCIAL_PROVIDERS {
        id id PK
        id user_id FK
        string provider
    }
    LOGIN_LOGS {
        id id PK
        id user_id FK
        string device
    }
    REFERRALS {
        id id PK
        id recommender_user_id FK
        id referred_user_id FK
    }
    PWA_INSTALLS {
        id id PK
        id user_id FK "nullable"
    }
```

---

## 4. Notificaciones y monetización

```mermaid
erDiagram
    FERMENT_TYPES        ||--o{ NOTIFICATION_RULES : "define"
    NOTIFICATION_RULES   ||--o{ FERMENTY_NOTIFICATIONS : "dispara"
    USERS                ||--o{ FERMENTY_NOTIFICATIONS : "para"
    BATCHES              ||--o{ FERMENTY_NOTIFICATIONS : "sobre"

    USERS                ||--o{ SUBSCRIPTIONS : "Cashier"
    SUBSCRIPTIONS        ||--o{ SUBSCRIPTION_ITEMS : "items"
    USERS                ||--o{ SUBSCRIPTION_CANCELLATIONS : "cancela"
    USERS                ||--o{ SUBSCRIPTION_RENEWAL_REMINDERS : "recordatorios"
    GIVEAWAY_CODES       ||--o{ GIVEAWAY_REDEMPTIONS : "canjes"
    USERS                ||--o{ GIVEAWAY_REDEMPTIONS : "canjea"

    FERMENTY_NOTIFICATIONS {
        id id PK
        id user_id FK
        id batch_id FK "nullable"
        id notification_rule_id FK "nullable"
        string link
        string community_type
    }
    NOTIFICATION_RULES {
        id id PK
        id ferment_type_id FK
    }
    SUBSCRIPTIONS {
        id id PK
        id user_id
        string stripe_status
    }
    SUBSCRIPTION_ITEMS {
        id id PK
        id subscription_id
        string meter_id
    }
    GIVEAWAY_CODES {
        id id PK
        string code
    }
    GIVEAWAY_REDEMPTIONS {
        id id PK
        id giveaway_code_id FK
        id user_id FK
    }
```

---

## 5. Comunidad y Blog

```mermaid
erDiagram
    USERS          ||--o{ COMMUNITY_POSTS : "autor"
    FERMENT_TYPES  }o--o{ COMMUNITY_POSTS : "tema"
    COMMUNITY_POSTS ||--o{ COMMUNITY_COMMENTS : "comentarios"
    COMMUNITY_POSTS ||--o{ COMMUNITY_POST_LIKES : "likes"
    USERS          ||--o{ COMMUNITY_COMMENTS : "comenta"
    USERS          ||--o{ COMMUNITY_POST_LIKES : "da like"

    USERS          ||--o{ BLOG_POSTS : "author_id"
    BLOG_CATEGORIES ||--o{ BLOG_POSTS : "categoría"
    BLOG_POSTS     ||--o{ BLOG_COMMENTS : "comentarios"
    BLOG_POSTS     ||--o{ BLOG_POST_VIEWS : "vistas"
    BLOG_POSTS     }o--o{ BLOG_TAGS : "blog_post_tag"
    BLOG_COMMENTS  ||--o{ BLOG_COMMENTS : "respuestas (parent_id)"

    COMMUNITY_POSTS {
        id id PK
        id user_id FK
        id ferment_type_id FK "nullable"
        int likes_count
    }
    BLOG_POSTS {
        id id PK
        id author_id FK
        id blog_category_id FK "nullable"
        string meta_keywords
    }
    BLOG_COMMENTS {
        id id PK
        id blog_post_id FK
        id parent_id FK "nullable, threaded"
    }
```

---

## 6. Infraestructura / sistema (sin FK relevantes)

Tablas de soporte, mayormente independientes:

| Tabla | Propósito |
|---|---|
| `api_clients` | Clientes API (client_id/secret) |
| `api_request_logs` | Log de peticiones API (→ `users`, nullable) |
| `personal_access_tokens` | Sanctum |
| `brevo_sync_logs` | Sincronización con Brevo (email) |
| `newsletter_subscribers` | Suscriptores newsletter |
| `contact_messages` | Formulario de contacto |
| `cache`, `jobs`, `sessions` | Infra Laravel |

---

## 7. Pistas para integrar el MENTOR

Consideraciones al conectar la nueva capa de MENTOR sobre este esquema:

- **Punto de anclaje natural**: el MENTOR razona sobre un `Batch` en curso. Ahí están el snapshot
  del protocolo (`batch_steps`), el diario (`daily_logs` → `measurements`), eventos (`batch_events`,
  incluye `mood`) y fotos. Es la fuente de contexto más rica por usuario.
- **Conocimiento canónico**: para consejos, el MENTOR debe leer de `guide_steps` + `step_actions` +
  `guide_step_hints` (ya existe una tabla de "consejos" por paso que podría reutilizarse/extenderse).
- **Personalización por tipo**: `ferment_options` (JSON por `ferment_type`) y `notification_rules`
  ya modelan parámetros específicos del fermento — útiles como _features_ para el MENTOR.
- **Gating de plan**: `ferment_types.plan_required` y `users.premium_until` definen quién accede a
  funciones premium; probablemente el MENTOR (o su nivel avanzado) sea premium.
- **Nuevas tablas candidatas** (a decidir): `mentor_conversations` / `mentor_messages`
  (chat por usuario o por batch), `mentor_insights` (si difieren de los `insights` de batch actuales),
  y quizá `mentor_knowledge` si se necesita una base de conocimiento propia/embeddings.
- **Reutilización**: ya existe `insights` colgando de `batch` — conviene decidir si el MENTOR escribe
  ahí o en una tabla propia para no mezclar señales automáticas con recomendaciones conversacionales.
```

---

## 8. Esquema SQL completo (`mysqldump --no-data`)

> Volcado de estructura (sin datos) generado con:
> ```bash
> mysqldump --no-data --skip-comments --skip-add-drop-table -u <user> -p fermenty_dev
> ```
> Fecha del volcado: 2026-07-22 · BD: `fermenty_dev`

```sql

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `api_clients` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_clients_api_key_unique` (`api_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `api_request_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `method` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  `route` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` smallint unsigned NOT NULL,
  `duration_ms` int unsigned DEFAULT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `api_request_logs_user_id_foreign` (`user_id`),
  KEY `api_request_logs_created_at_index` (`created_at`),
  KEY `api_request_logs_route_created_at_index` (`route`,`created_at`),
  CONSTRAINT `api_request_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=320 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `batch_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `event_type` enum('start','stir','taste','measure','bottle','add_ingredient','temperature_alert','step_completed','paused','resumed','discard','restart','completed','mood','other') COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `event_date` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batch_events_batch_id_event_type_index` (`batch_id`,`event_type`),
  KEY `batch_events_batch_id_event_date_index` (`batch_id`,`event_date`),
  CONSTRAINT `batch_events_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `batch_events_chk_1` CHECK (json_valid(`metadata`))
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `batch_ingredients` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `guide_ingredient_id` bigint unsigned DEFAULT NULL,
  `recipe_ingredient_id` bigint unsigned DEFAULT NULL,
  `batch_step_id` bigint unsigned DEFAULT NULL,
  `guide_step_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(8,2) DEFAULT NULL,
  `unit` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phase` enum('primary','secondary') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'primary',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batch_ingredients_batch_id_index` (`batch_id`),
  KEY `batch_ingredients_guide_ingredient_id_foreign` (`guide_ingredient_id`),
  KEY `batch_ingredients_guide_step_id_foreign` (`guide_step_id`),
  KEY `batch_ingredients_recipe_ingredient_id_foreign` (`recipe_ingredient_id`),
  KEY `batch_ingredients_batch_step_id_foreign` (`batch_step_id`),
  CONSTRAINT `batch_ingredients_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `batch_ingredients_batch_step_id_foreign` FOREIGN KEY (`batch_step_id`) REFERENCES `batch_steps` (`id`) ON DELETE SET NULL,
  CONSTRAINT `batch_ingredients_guide_ingredient_id_foreign` FOREIGN KEY (`guide_ingredient_id`) REFERENCES `guide_ingredients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `batch_ingredients_guide_step_id_foreign` FOREIGN KEY (`guide_step_id`) REFERENCES `guide_steps` (`id`) ON DELETE SET NULL,
  CONSTRAINT `batch_ingredients_recipe_ingredient_id_foreign` FOREIGN KEY (`recipe_ingredient_id`) REFERENCES `recipe_ingredients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `batch_photos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `step_id` bigint unsigned DEFAULT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `disk` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'public',
  `taken_at` timestamp NULL DEFAULT NULL,
  `note` text COLLATE utf8mb4_unicode_ci,
  `order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batch_photos_batch_id_taken_at_index` (`batch_id`,`taken_at`),
  KEY `batch_photos_step_id_foreign` (`step_id`),
  CONSTRAINT `batch_photos_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `batch_photos_step_id_foreign` FOREIGN KEY (`step_id`) REFERENCES `batch_steps` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `batch_reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `rating` tinyint unsigned DEFAULT NULL,
  `mood` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `acidity_level` tinyint unsigned DEFAULT NULL,
  `sweetness_level` tinyint unsigned DEFAULT NULL,
  `carbonation_level` tinyint unsigned DEFAULT NULL,
  `overall_comment` text COLLATE utf8mb4_unicode_ci,
  `is_public` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `batch_reviews_batch_id_unique` (`batch_id`),
  CONSTRAINT `batch_reviews_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `batch_steps` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `guide_step_id` bigint unsigned DEFAULT NULL,
  `recipe_step_id` bigint unsigned DEFAULT NULL,
  `step_number` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `min_day` int DEFAULT NULL,
  `max_day` int DEFAULT NULL,
  `is_optional` tinyint(1) NOT NULL DEFAULT '0',
  `notify_on_complete` tinyint(1) NOT NULL DEFAULT '0',
  `actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `status` enum('pending','active','completed','skipped') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `batch_steps_batch_id_guide_step_id_unique` (`batch_id`,`guide_step_id`),
  KEY `batch_steps_guide_step_id_foreign` (`guide_step_id`),
  KEY `batch_steps_batch_id_status_index` (`batch_id`,`status`),
  KEY `batch_steps_recipe_step_id_foreign` (`recipe_step_id`),
  CONSTRAINT `batch_steps_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `batch_steps_guide_step_id_foreign` FOREIGN KEY (`guide_step_id`) REFERENCES `guide_steps` (`id`),
  CONSTRAINT `batch_steps_recipe_step_id_foreign` FOREIGN KEY (`recipe_step_id`) REFERENCES `recipe_steps` (`id`) ON DELETE SET NULL,
  CONSTRAINT `batch_steps_chk_1` CHECK (json_valid(`actions`))
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `batches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `ferment_type_id` bigint unsigned NOT NULL,
  `guide_id` bigint unsigned NOT NULL,
  `recipe_id` bigint unsigned DEFAULT NULL,
  `target_yield_value` decimal(8,3) DEFAULT NULL,
  `target_yield_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('active','paused','completed','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `start_date` date NOT NULL,
  `expected_end_date` date DEFAULT NULL,
  `fermentation_temp` decimal(4,1) DEFAULT NULL,
  `target_duration_days` smallint unsigned DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batches_guide_id_foreign` (`guide_id`),
  KEY `batches_recipe_id_foreign` (`recipe_id`),
  KEY `batches_user_id_status_index` (`user_id`,`status`),
  KEY `batches_ferment_type_id_index` (`ferment_type_id`),
  CONSTRAINT `batches_ferment_type_id_foreign` FOREIGN KEY (`ferment_type_id`) REFERENCES `ferment_types` (`id`),
  CONSTRAINT `batches_guide_id_foreign` FOREIGN KEY (`guide_id`) REFERENCES `fermentation_guides` (`id`),
  CONSTRAINT `batches_recipe_id_foreign` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `batches_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `blog_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `color_key` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'gray',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_categories_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `blog_comments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `blog_post_id` bigint unsigned NOT NULL,
  `parent_id` bigint unsigned DEFAULT NULL,
  `author_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `author_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','approved','spam') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `blog_comments_parent_id_foreign` (`parent_id`),
  KEY `blog_comments_blog_post_id_status_index` (`blog_post_id`,`status`),
  CONSTRAINT `blog_comments_blog_post_id_foreign` FOREIGN KEY (`blog_post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `blog_comments_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `blog_comments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `blog_post_tag` (
  `blog_post_id` bigint unsigned NOT NULL,
  `blog_tag_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`blog_post_id`,`blog_tag_id`),
  KEY `blog_post_tag_blog_tag_id_foreign` (`blog_tag_id`),
  CONSTRAINT `blog_post_tag_blog_post_id_foreign` FOREIGN KEY (`blog_post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `blog_post_tag_blog_tag_id_foreign` FOREIGN KEY (`blog_tag_id`) REFERENCES `blog_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `blog_post_views` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `blog_post_id` bigint unsigned NOT NULL,
  `ip_hash` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `viewed_on` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_post_views_blog_post_id_ip_hash_viewed_on_unique` (`blog_post_id`,`ip_hash`,`viewed_on`),
  CONSTRAINT `blog_post_views_blog_post_id_foreign` FOREIGN KEY (`blog_post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=273 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `blog_posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `author_id` bigint unsigned NOT NULL,
  `blog_category_id` bigint unsigned DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `excerpt` text COLLATE utf8mb4_unicode_ci,
  `body` longtext COLLATE utf8mb4_unicode_ci,
  `status` enum('draft','published','scheduled','archived') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `cover_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `read_time_minutes` smallint unsigned NOT NULL DEFAULT '3',
  `lang` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'es',
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int unsigned NOT NULL DEFAULT '0',
  `meta_title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description` text COLLATE utf8mb4_unicode_ci,
  `meta_keywords` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `og_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_posts_slug_unique` (`slug`),
  KEY `blog_posts_author_id_foreign` (`author_id`),
  KEY `blog_posts_blog_category_id_foreign` (`blog_category_id`),
  KEY `blog_posts_status_published_at_index` (`status`,`published_at`),
  KEY `blog_posts_featured_index` (`featured`),
  CONSTRAINT `blog_posts_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `blog_posts_blog_category_id_foreign` FOREIGN KEY (`blog_category_id`) REFERENCES `blog_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `blog_tags` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_tags_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `brevo_sync_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `synced` int unsigned NOT NULL DEFAULT '0',
  `skipped` int unsigned NOT NULL DEFAULT '0',
  `failed` int unsigned NOT NULL DEFAULT '0',
  `dry_run` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brevo_sync_logs_type_index` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cache` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` bigint NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cache_locks` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` bigint NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_locks_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `community_comments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `community_post_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `likes_count` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `community_comments_user_id_foreign` (`user_id`),
  KEY `community_comments_community_post_id_created_at_index` (`community_post_id`,`created_at`),
  CONSTRAINT `community_comments_community_post_id_foreign` FOREIGN KEY (`community_post_id`) REFERENCES `community_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_comments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `community_post_likes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `community_post_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `community_post_likes_user_id_community_post_id_unique` (`user_id`,`community_post_id`),
  KEY `community_post_likes_community_post_id_foreign` (`community_post_id`),
  CONSTRAINT `community_post_likes_community_post_id_foreign` FOREIGN KEY (`community_post_id`) REFERENCES `community_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_post_likes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `community_posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `ferment_type_id` bigint unsigned DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image_2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image_3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `post_type` enum('tip','question','recipe','review') COLLATE utf8mb4_unicode_ci NOT NULL,
  `likes_count` int unsigned NOT NULL DEFAULT '0',
  `comments_count` int unsigned NOT NULL DEFAULT '0',
  `is_published` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `community_posts_user_id_foreign` (`user_id`),
  KEY `community_posts_post_type_is_published_index` (`post_type`,`is_published`),
  KEY `community_posts_ferment_type_id_post_type_index` (`ferment_type_id`,`post_type`),
  CONSTRAINT `community_posts_ferment_type_id_foreign` FOREIGN KEY (`ferment_type_id`) REFERENCES `ferment_types` (`id`) ON DELETE SET NULL,
  CONSTRAINT `community_posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `contact_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `locale` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_handled` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contact_messages_email_index` (`email`),
  KEY `contact_messages_is_handled_index` (`is_handled`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `daily_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `day_number` int NOT NULL,
  `date` date NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `mood` enum('good','odd','bad') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_logs_batch_id_date_unique` (`batch_id`,`date`),
  KEY `daily_logs_batch_id_day_number_index` (`batch_id`,`day_number`),
  CONSTRAINT `daily_logs_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ferment_options` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ferment_type_id` bigint unsigned NOT NULL,
  `option_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `option_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ferment_options_ferment_type_id_foreign` (`ferment_type_id`),
  CONSTRAINT `ferment_options_ferment_type_id_foreign` FOREIGN KEY (`ferment_type_id`) REFERENCES `ferment_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ferment_options_chk_1` CHECK (json_valid(`option_values`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ferment_types` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fermentation_category_id` bigint unsigned DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `difficulty_level` enum('easy','medium','hard') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'easy',
  `default_duration_days` int NOT NULL,
  `plan_required` enum('free','premium') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'free',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ferment_types_slug_unique` (`slug`),
  KEY `ferment_types_fermentation_category_id_foreign` (`fermentation_category_id`),
  CONSTRAINT `ferment_types_fermentation_category_id_foreign` FOREIGN KEY (`fermentation_category_id`) REFERENCES `fermentation_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fermentation_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `color` varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '#3A7A22',
  `image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `position` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fermentation_categories_slug_unique` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fermentation_guides` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ferment_type_id` bigint unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `base_yield_value` decimal(8,3) DEFAULT NULL,
  `base_yield_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1.0',
  `is_current` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fermentation_guides_ferment_type_id_foreign` (`ferment_type_id`),
  CONSTRAINT `fermentation_guides_ferment_type_id_foreign` FOREIGN KEY (`ferment_type_id`) REFERENCES `ferment_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fermenty_notifications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `batch_id` bigint unsigned DEFAULT NULL,
  `notification_rule_id` bigint unsigned DEFAULT NULL,
  `type` enum('reminder','alert','suggestion','community') COLLATE utf8mb4_unicode_ci NOT NULL,
  `channel` enum('email','push','both') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'push',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `link` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `scheduled_at` timestamp NOT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fermenty_notifications_batch_id_foreign` (`batch_id`),
  KEY `fermenty_notifications_notification_rule_id_foreign` (`notification_rule_id`),
  KEY `fermenty_notifications_user_id_read_at_index` (`user_id`,`read_at`),
  KEY `fermenty_notifications_scheduled_at_sent_at_index` (`scheduled_at`,`sent_at`),
  CONSTRAINT `fermenty_notifications_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fermenty_notifications_notification_rule_id_foreign` FOREIGN KEY (`notification_rule_id`) REFERENCES `notification_rules` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fermenty_notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=126 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `flavor_results` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `sourness` tinyint unsigned DEFAULT NULL,
  `sweetness` tinyint unsigned DEFAULT NULL,
  `aroma` tinyint unsigned DEFAULT NULL,
  `texture` tinyint unsigned DEFAULT NULL,
  `bitterness` tinyint unsigned DEFAULT NULL,
  `fizziness` tinyint unsigned DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `flavor_results_batch_id_unique` (`batch_id`),
  CONSTRAINT `flavor_results_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `giveaway_codes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `months` smallint unsigned NOT NULL,
  `max_redemptions` int unsigned DEFAULT NULL,
  `redeemed_count` int unsigned NOT NULL DEFAULT '0',
  `expires_at` timestamp NULL DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `giveaway_codes_code_unique` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `giveaway_redemptions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `giveaway_code_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `months_granted` smallint unsigned NOT NULL,
  `redeemed_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `giveaway_redemptions_giveaway_code_id_user_id_unique` (`giveaway_code_id`,`user_id`),
  KEY `giveaway_redemptions_user_id_foreign` (`user_id`),
  CONSTRAINT `giveaway_redemptions_giveaway_code_id_foreign` FOREIGN KEY (`giveaway_code_id`) REFERENCES `giveaway_codes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `giveaway_redemptions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `guide_ingredients` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `guide_id` bigint unsigned NOT NULL,
  `guide_step_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(10,3) DEFAULT NULL,
  `unit` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phase` enum('primary','secondary') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'primary',
  `is_optional` tinyint(1) NOT NULL DEFAULT '0',
  `order` int NOT NULL DEFAULT '0',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `guide_ingredients_guide_id_index` (`guide_id`),
  KEY `guide_ingredients_guide_step_id_index` (`guide_step_id`),
  CONSTRAINT `guide_ingredients_guide_id_foreign` FOREIGN KEY (`guide_id`) REFERENCES `fermentation_guides` (`id`) ON DELETE CASCADE,
  CONSTRAINT `guide_ingredients_guide_step_id_foreign` FOREIGN KEY (`guide_step_id`) REFERENCES `guide_steps` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `guide_step_hints` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `guide_step_id` bigint unsigned NOT NULL,
  `title` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `order` int unsigned NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `guide_step_hints_guide_step_id_order_index` (`guide_step_id`,`order`),
  CONSTRAINT `guide_step_hints_guide_step_id_foreign` FOREIGN KEY (`guide_step_id`) REFERENCES `guide_steps` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `guide_steps` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `guide_id` bigint unsigned NOT NULL,
  `step_number` int NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `min_day` int NOT NULL DEFAULT '0',
  `max_day` int DEFAULT NULL,
  `is_optional` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guide_steps_guide_id_step_number_unique` (`guide_id`,`step_number`),
  CONSTRAINT `guide_steps_guide_id_foreign` FOREIGN KEY (`guide_id`) REFERENCES `fermentation_guides` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `insights` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint unsigned NOT NULL,
  `type` enum('warning','suggestion','improvement') COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `is_dismissed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `insights_batch_id_is_read_index` (`batch_id`,`is_read`),
  CONSTRAINT `insights_batch_id_foreign` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `insights_chk_1` CHECK (json_valid(`context`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `login_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `login_source` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `logged_in_at` timestamp NOT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `device` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `login_logs_user_id_foreign` (`user_id`),
  CONSTRAINT `login_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `measurements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `log_id` bigint unsigned NOT NULL,
  `type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` decimal(8,3) NOT NULL,
  `unit` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `measurements_log_id_type_index` (`log_id`,`type`),
  CONSTRAINT `measurements_log_id_foreign` FOREIGN KEY (`log_id`) REFERENCES `daily_logs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `newsletter_subscribers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `locale` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'es',
  `is_confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `confirmation_token` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `notified_at` timestamp NULL DEFAULT NULL,
  `is_unsubscribed` tinyint(1) NOT NULL DEFAULT '0',
  `unsubscribed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `newsletter_subscribers_email_unique` (`email`),
  UNIQUE KEY `newsletter_subscribers_confirmation_token_unique` (`confirmation_token`),
  KEY `newsletter_subscribers_is_confirmed_notified_at_index` (`is_confirmed`,`notified_at`),
  KEY `newsletter_subscribers_is_unsubscribed_index` (`is_unsubscribed`)
) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `notification_rules` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `ferment_type_id` bigint unsigned NOT NULL,
  `trigger_type` enum('day','step','measurement') COLLATE utf8mb4_unicode_ci NOT NULL,
  `condition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `action` enum('notify','suggest','warn') COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `notif_rules_ferment_trigger_title_unique` (`ferment_type_id`,`trigger_type`,`title`),
  CONSTRAINT `notification_rules_ferment_type_id_foreign` FOREIGN KEY (`ferment_type_id`) REFERENCES `ferment_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `notification_rules_chk_1` CHECK (json_valid(`condition`))
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `abilities` text COLLATE utf8mb4_unicode_ci,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pwa_installs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `device_uuid` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `platform` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `installed_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pwa_installs_device_uuid_unique` (`device_uuid`),
  KEY `pwa_installs_user_id_foreign` (`user_id`),
  KEY `pwa_installs_installed_at_index` (`installed_at`),
  CONSTRAINT `pwa_installs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `recipe_ingredients` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` bigint unsigned NOT NULL,
  `recipe_step_id` bigint unsigned DEFAULT NULL,
  `guide_step_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(10,3) DEFAULT NULL,
  `unit` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phase` enum('primary','secondary') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'primary',
  `is_optional` tinyint(1) NOT NULL DEFAULT '0',
  `order` int NOT NULL DEFAULT '0',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `recipe_ingredients_recipe_id_index` (`recipe_id`),
  KEY `recipe_ingredients_guide_step_id_index` (`guide_step_id`),
  KEY `recipe_ingredients_recipe_step_id_foreign` (`recipe_step_id`),
  CONSTRAINT `recipe_ingredients_guide_step_id_foreign` FOREIGN KEY (`guide_step_id`) REFERENCES `guide_steps` (`id`) ON DELETE SET NULL,
  CONSTRAINT `recipe_ingredients_recipe_id_foreign` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `recipe_ingredients_recipe_step_id_foreign` FOREIGN KEY (`recipe_step_id`) REFERENCES `recipe_steps` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `recipe_steps` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` bigint unsigned NOT NULL,
  `guide_step_id` bigint unsigned NOT NULL,
  `step_number` int NOT NULL DEFAULT '0',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `min_day` int DEFAULT NULL,
  `max_day` int DEFAULT NULL,
  `is_optional` tinyint(1) NOT NULL DEFAULT '0',
  `notify_on_complete` tinyint(1) NOT NULL DEFAULT '0',
  `actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `recipe_steps_recipe_id_guide_step_id_unique` (`recipe_id`,`guide_step_id`),
  KEY `recipe_steps_guide_step_id_foreign` (`guide_step_id`),
  KEY `recipe_steps_recipe_id_step_number_index` (`recipe_id`,`step_number`),
  CONSTRAINT `recipe_steps_guide_step_id_foreign` FOREIGN KEY (`guide_step_id`) REFERENCES `guide_steps` (`id`) ON DELETE CASCADE,
  CONSTRAINT `recipe_steps_recipe_id_foreign` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `recipe_steps_chk_1` CHECK (json_valid(`actions`))
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `recipes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `ferment_type_id` bigint unsigned NOT NULL,
  `guide_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `base_yield_value` decimal(8,3) DEFAULT NULL,
  `base_yield_unit` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `visibility` enum('private','public') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'private',
  `status` enum('draft','pending','published','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `is_official` tinyint(1) NOT NULL DEFAULT '0',
  `source_recipe_id` bigint unsigned DEFAULT NULL,
  `likes_count` int unsigned NOT NULL DEFAULT '0',
  `rating_avg` decimal(3,2) DEFAULT NULL,
  `rating_count` int unsigned NOT NULL DEFAULT '0',
  `times_brewed` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `recipes_user_id_visibility_index` (`user_id`,`visibility`),
  KEY `recipes_ferment_type_id_visibility_index` (`ferment_type_id`,`visibility`),
  KEY `recipes_source_recipe_id_foreign` (`source_recipe_id`),
  KEY `recipes_guide_id_status_index` (`guide_id`,`status`),
  KEY `recipes_ferment_type_id_visibility_status_index` (`ferment_type_id`,`visibility`,`status`),
  CONSTRAINT `recipes_ferment_type_id_foreign` FOREIGN KEY (`ferment_type_id`) REFERENCES `ferment_types` (`id`),
  CONSTRAINT `recipes_guide_id_foreign` FOREIGN KEY (`guide_id`) REFERENCES `fermentation_guides` (`id`) ON DELETE CASCADE,
  CONSTRAINT `recipes_source_recipe_id_foreign` FOREIGN KEY (`source_recipe_id`) REFERENCES `recipes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `recipes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `referrals` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `recommender_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `recommender_user_id` bigint unsigned DEFAULT NULL,
  `friend_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `referred_user_id` bigint unsigned DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `token` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'sent',
  `visited_at` timestamp NULL DEFAULT NULL,
  `registered_at` timestamp NULL DEFAULT NULL,
  `friend_verified_at` timestamp NULL DEFAULT NULL,
  `rewarded_at` timestamp NULL DEFAULT NULL,
  `reward_months` smallint unsigned NOT NULL DEFAULT '3',
  `recommender_opt_in` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `referrals_recommender_email_friend_email_unique` (`recommender_email`,`friend_email`),
  UNIQUE KEY `referrals_token_unique` (`token`),
  KEY `referrals_recommender_user_id_foreign` (`recommender_user_id`),
  KEY `referrals_referred_user_id_foreign` (`referred_user_id`),
  KEY `referrals_recommender_email_index` (`recommender_email`),
  KEY `referrals_friend_email_index` (`friend_email`),
  KEY `referrals_status_index` (`status`),
  CONSTRAINT `referrals_recommender_user_id_foreign` FOREIGN KEY (`recommender_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `referrals_referred_user_id_foreign` FOREIGN KEY (`referred_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `step_actions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `step_id` bigint unsigned NOT NULL,
  `action_type` enum('prepare','wait','bottle','taste','measure','stir','filter','other') COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `step_actions_step_id_foreign` (`step_id`),
  CONSTRAINT `step_actions_step_id_foreign` FOREIGN KEY (`step_id`) REFERENCES `guide_steps` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `subscription_cancellations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `stripe_subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment` text COLLATE utf8mb4_unicode_ci,
  `requested_at` timestamp NOT NULL,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `period_end` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subscription_cancellations_user_id_foreign` (`user_id`),
  CONSTRAINT `subscription_cancellations_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `subscription_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `subscription_id` bigint unsigned NOT NULL,
  `stripe_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_product` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_price` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `meter_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `meter_event_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscription_items_stripe_id_unique` (`stripe_id`),
  KEY `subscription_items_subscription_id_stripe_price_index` (`subscription_id`,`stripe_price`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `subscription_renewal_reminders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `stripe_subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `period_end` timestamp NULL DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sub_renewal_period_unique` (`stripe_subscription_id`,`period_end`),
  KEY `subscription_renewal_reminders_user_id_foreign` (`user_id`),
  CONSTRAINT `subscription_renewal_reminders_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `subscriptions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_price` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscriptions_stripe_id_unique` (`stripe_id`),
  KEY `subscriptions_user_id_stripe_status_index` (`user_id`,`stripe_status`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_social_providers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `provider` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `provider_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `token` text COLLATE utf8mb4_unicode_ci,
  `refresh_token` text COLLATE utf8mb4_unicode_ci,
  `token_expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_social_providers_user_id_provider_unique` (`user_id`,`provider`),
  UNIQUE KEY `user_social_providers_provider_provider_id_unique` (`provider`,`provider_id`),
  CONSTRAINT `user_social_providers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` enum('user','admin') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'user',
  `plan` enum('free','premium') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'free',
  `premium_until` timestamp NULL DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `registration_source` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'web',
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `locale` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'es',
  `unit_system` enum('metric','imperial') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'metric',
  `fermenter_profile` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bio` varchar(400) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `favorite_ferment_type_id` bigint unsigned DEFAULT NULL,
  `notifications_allowed` tinyint(1) NOT NULL DEFAULT '1',
  `newsletter_opt_in` tinyint(1) NOT NULL DEFAULT '1',
  `daily_digest` tinyint(1) NOT NULL DEFAULT '0',
  `profile_public` tinyint(1) NOT NULL DEFAULT '1',
  `onboarding_tour_completed_at` timestamp NULL DEFAULT NULL,
  `reengagement_sent_at` timestamp NULL DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `stripe_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pm_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pm_last_four` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_favorite_ferment_type_id_foreign` (`favorite_ferment_type_id`),
  KEY `users_stripe_id_index` (`stripe_id`),
  CONSTRAINT `users_favorite_ferment_type_id_foreign` FOREIGN KEY (`favorite_ferment_type_id`) REFERENCES `ferment_types` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

```