# Modelo de Datos

Esquema completo de la base de datos de CD-System. Cada proyecto tiene su propia DB con esta misma estructura; lo que varía es el contenido.

## Visión general

```
TABLAS DEL SISTEMA (compartidas por todos los proyectos)
├── settings              Configuración clave-valor (identidad, SEO, GA, módulos)
├── assets                Logos, favicons, imágenes OG (+ URLs Cloudinary)
├── demos                 Registro de demos disponibles con config JSON
├── users                 Usuarios del proyecto
├── user_infos            Perfil extendido del usuario
├── roles / permissions   Roles y permisos (Spatie)
├── welcome_carousels     Imágenes del carousel del homepage
├── languages             Idiomas disponibles (módulo traducciones)
└── newsletter_subscribers Suscriptores al newsletter

TABLAS POR MÓDULO (solo contienen datos si el módulo está activo)
├── Blog:       posts, post_categories, post_translations, post_category_translations
├── Menu:       menu_categories, menus, menu_products, menu_menu_product (pivot)
├── Products:   products, categories, product_tags, product_product_tag (pivot), product_images, product_translations, product_category_translations
├── Services:   services, service_categories
├── Projects:   projects, project_categories, project_category_project (pivot), project_tags, project_project_tag (pivot)
├── Gallery:    gallery, gallery_categories, gallery_tags, gallery_gallery_tag (pivot)
├── Team:       team_members, team_categories
├── References: references, reference_categories
├── News:       news, news_categories
├── FAQs:       faqs, faqs_categories
└── Legacy:     albums, pictures, tags, carousels + pivots (sistema anterior de galería)
```

---

## Tablas del sistema

### settings

Almacena toda la configuración del proyecto como pares clave-valor. Es la tabla más importante del sistema: aquí vive la identidad, el SEO, las redes sociales, el GA, y la configuración de módulos.

| Columna | Tipo | Descripción |
|---------|------|-------------|
| id | int (PK) | |
| key | string (index) | Clave jerárquica (ej: `site.contact.phone`, `cd-system.modules.blog.active`) |
| value | text | Valor (string simple o JSON serializado para arrays) |
| user_id | bigint (nullable) | Usuario que creó/modificó |

**Keys importantes:**
- `site.name`, `site.tagline`, `site.url` -- Identidad
- `site.contact.*` -- Teléfono, email, dirección
- `site.social_media.*` -- Redes sociales
- `site.footer.*` -- Navegación del footer
- `site.google_analytics.*` -- Tracking
- `cd-system.theme.demo` -- Demo activo
- `cd-system.modules.{modulo}.active` -- Estado de cada módulo
- `cd-system.modules.{modulo}.navigation.*` -- Navegación del módulo

### assets

Registro de assets del proyecto (logos, favicons, imágenes OG/Twitter) con URLs de Cloudinary.

| Columna | Tipo | Descripción |
|---------|------|-------------|
| id | bigint (PK) | |
| name | string | Nombre del asset |
| path | string (unique) | Ruta relativa (ej: `cd-project/assets/logo.png`) |
| type | string | Tipo (logo, favicon, og-image, etc.) |
| public_id | string (nullable) | ID en Cloudinary |
| secure_url | string (nullable) | URL segura de Cloudinary |
| description | text (nullable) | |
| width | int (nullable) | |
| height | int (nullable) | |
| timestamps | | |

### demos

Registro de demos disponibles con configuración almacenada como JSON.

| Columna | Tipo | Descripción |
|---------|------|-------------|
| id | bigint (PK) | |
| name | string | Nombre del demo |
| slug | string (unique) | Identificador (ej: `demo-restaurant`) |
| cd_system_config | json (nullable) | Configuración cd-system del demo |
| site_config | json (nullable) | Configuración site del demo |
| is_active | boolean | Si está activo |
| timestamps | | |

### users

| Columna | Tipo | Descripción |
|---------|------|-------------|
| id | bigint (PK) | |
| first_name | string | |
| last_name | string | |
| email | string (unique) | |
| email_verified_at | timestamp (nullable) | |
| password | string | |
| remember_token | string (nullable) | |
| timestamps | | |

**Relaciones:** `hasOne` UserInfo, `belongsTo` Role (Spatie)

### user_infos

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| user_id | bigint | → users.id (CASCADE) |
| avatar | text (nullable) | |
| phone, mobile, status, country, state, city, website, language | string (nullable) | |
| timestamps | | |

---

## Tablas por módulo

### Blog (módulo `blog`)

**posts**

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| user_id | bigint | → users.id (CASCADE) |
| title | string | |
| slug | string | |
| category_id | bigint | → post_categories.id (CASCADE) |
| description | text | |
| type | string(20) | |
| status | enum (Published, Paused, Draft) | |
| content | text | |
| timestamps + softDeletes | | |

**post_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name, slug | string |
| description | string (nullable) |
| timestamps | |

**post_translations** (módulo traducciones)

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| post_id | bigint | → posts.id (CASCADE) |
| locale | string(10) | |
| title, slug | string | |
| description, excerpt | text (nullable) | |
| content | longtext (nullable) | |
| unique: [post_id, locale] | | |

### Menu (módulo `menu`)

**menu_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name | string |
| slug | string (unique) |
| description | text (nullable) |
| is_featured | boolean (default: false) |
| order | int (default: 0) |
| timestamps + softDeletes | |

**menus**

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| name, slug (unique) | string | |
| description | text (nullable) | |
| menu_category_id | bigint | → menu_categories.id (CASCADE) |
| is_active | boolean | |
| order | int | |
| timestamps + softDeletes | | |

**menu_products**

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| name, slug (unique) | string | |
| description | text (nullable) | |
| price | decimal(10,2) | |
| image | string (nullable) | |
| is_active | boolean | |
| order | int | |
| menu_category_id | bigint (nullable) | → menu_categories.id (SET NULL) |
| timestamps + softDeletes | | |

**menu_menu_product** (pivot)

| Columna | FK |
|---------|-----|
| menu_id | → menus.id (CASCADE) |
| menu_product_id | → menu_products.id (CASCADE) |
| order | int |
| unique: [menu_id, menu_product_id] | |

### Products (módulo `products`)

**products**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name | string |
| slug | string (unique, auto-generado) |
| description | text (nullable) |
| price | decimal(10,2) |
| image_path | string (nullable) |
| is_active | boolean |
| category_id | bigint (nullable) → categories.id |
| timestamps + softDeletes | |

**categories** (categorías de productos)

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name, slug | string |
| description | text |
| timestamps | |

**product_tags** / **product_product_tag** (pivot)

Tags de productos con relación many-to-many.

**product_images**

| Columna | FK |
|---------|-----|
| id | bigint (PK) |
| product_id | → products.id (CASCADE) |
| image_path | string |

### Services (módulo `services`)

**services**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| title, slug (unique) | string |
| subtitle | string (nullable) |
| description | text |
| icon, logo, image | string (nullable) |
| is_active, is_featured | boolean |
| service_category_id | bigint (nullable) → service_categories.id |
| benefits, process_steps, features, faq, cta_config | json (nullable) |
| meta_keywords, meta_description | string (nullable) |
| sort_order | int |
| template_type | string (nullable) |
| timestamps | |

**service_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name, slug (unique) | string |
| description | text (nullable) |
| icon | string(100, nullable) |
| color | string(7, default: #007bff) |
| sort_order | int |
| is_active | boolean |
| timestamps | |

### Projects (módulo `projects`)

**projects**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| title, slug | string |
| image, logo | string (nullable) |
| url, description | string (nullable) |
| is_active | boolean |
| timestamps + softDeletes | |

**project_categories** / **project_category_project** (pivot many-to-many)
**project_tags** / **project_project_tag** (pivot many-to-many)

### Gallery (módulo `gallery`)

**gallery**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| title | string (nullable) |
| description | text (nullable) |
| img | string |
| category_id | bigint (nullable) → gallery_categories.id |
| timestamps | |

**gallery_categories** / **gallery_tags** / **gallery_gallery_tag** (pivot)

### Team (módulo `team`)

**team_members**

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| name, slug, position | string | |
| email, linkedin | string (nullable) | |
| languages | json (nullable) | |
| category_id | bigint (nullable) | → team_categories.id (SET NULL) |
| description, details | text (nullable) | |
| image | string (nullable) | |
| is_active | boolean | |
| timestamps | | |

**team_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name (unique) | string |
| timestamps | |

### References (módulo `references`)

**references**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name | string (nullable) |
| type | enum (nullable) |
| status | enum (Published, Paused, Draft, nullable) |
| category_id, logo, featured | (via migraciones posteriores) |
| timestamps | |

**reference_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name, slug (unique) | string |
| timestamps | |

### News (módulo `news`)

**news**

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| title | string | |
| content | text | |
| slug | string (unique) | |
| user_id | bigint | → users.id (CASCADE) |
| category_id | bigint (nullable) | → news_categories.id (CASCADE) |
| timestamps | | |

**news_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name, slug (unique) | string |
| timestamps | |

### FAQs (módulo `faqs`)

**faqs**

| Columna | Tipo | FK |
|---------|------|-----|
| id | bigint (PK) | |
| question | string | |
| answer | text | |
| category_id | bigint | → faqs_categories.id (CASCADE) |
| is_featured | boolean (via migración) | |
| order | int (default: 0) | |
| timestamps | | |

**faqs_categories**

| Columna | Tipo |
|---------|------|
| id | bigint (PK) |
| name (unique) | string |
| timestamps | |

---

## Tablas de soporte

### Permisos (Spatie Permission)

- `roles` -- Roles del sistema
- `permissions` -- Permisos disponibles
- `model_has_roles` -- Asignación de roles a usuarios (polimórfica)
- `model_has_permissions` -- Asignación directa de permisos (polimórfica)
- `role_has_permissions` -- Permisos por rol

### Sistema

- `password_resets` -- Tokens de reset de contraseña
- `failed_jobs` -- Jobs fallidos (queue)
- `activity_log` -- Log de actividad (Spatie ActivityLog)
- `newsletter_subscribers` -- Suscriptores email
- `tokko_settings` -- Configuración módulo Tokko (inmobiliaria)
- `project_settings` -- Configuración del proyecto (estructura base)
- `languages` -- Idiomas disponibles para traducciones

### Legacy (galería anterior)

- `albums`, `pictures`, `tags` -- Entidades
- `album_picture`, `album_tag`, `picture_tag` -- Pivots
- `carousels`, `carousel_picture` -- Carruseles legacy

---

## Patrones de diseño en el esquema

### Patrón consistente por módulo

Cada módulo de contenido sigue el mismo patrón:

```
{modulo}              Tabla principal (posts, services, products, etc.)
{modulo}_categories   Categorías (post_categories, service_categories, etc.)
{modulo}_{modulo}_tag Pivot tags (product_product_tag, gallery_gallery_tag, etc.)
{modulo}_tags         Tags específicos del módulo
```

No todos los módulos tienen tags (services, team, news no los usan).

### Convenciones de nombres

| Convención | Ejemplo |
|-----------|---------|
| Categorías | `{modulo}_categories` (excepto `categories` para products y `faqs_categories` para faqs) |
| Pivots | `{tabla1}_{tabla2}` (ej: `menu_menu_product`, `gallery_gallery_tag`) |
| Traducciones | `{entidad}_translations` (ej: `post_translations`) |
| Soft deletes | Solo en: posts, products, projects, menus, menu_products, menu_categories |

### Inconsistencias conocidas

| Qué | Detalle | Impacto |
|-----|---------|---------|
| Products usa `categories` | La tabla se llama `categories` en vez de `product_categories` | El modelo `ProductCategory` apunta a `categories` |
| FAQs usa `faqs_categories` | Con plural `faqs_` en vez de `faq_categories` | Solo nomenclatura |
| References tiene `type` como enum fijo | Tipos hardcodeados en la migración | Limita extensibilidad |
| News sin `category_id` FK nullable vs CASCADE | Usa CASCADE pero category_id es nullable | Podría causar orphans |

Estas inconsistencias no afectan funcionalidad pero conviene tenerlas en cuenta al crear módulos nuevos para seguir la convención más común.