# Maaal Holding — Backend Data Model (reverse-engineered from Figma)

Source: two Figma files — **Dashboard** (admin CMS, ~200 frames) and **Website** (public site, 7 page types).
This document is the authoritative schema used to generate migrations, models, resources, requests and controllers.

## Global conventions

- **Timestamps**: every table has `created_at`, `updated_at` unless noted.
- **Soft deletes**: tables whose list UI exposes a *delete (trash) + restore* action carry `deleted_at` (`SoftDeletes`).
- **Language**: bilingual app (Arabic primary, English). Where a screen exposes a per-row language flag, the column is `language ENUM('ar','en')`. Free bilingual text uses paired `*_ar` / `*_en` columns only where the design clearly shows two inputs; otherwise a single column is used and the active locale is chosen by the client.
- **Status**: list toggles map to `is_active BOOLEAN DEFAULT 1`. Content with a publishing lifecycle uses `status ENUM('draft','published','scheduled','archived')`.
- **Slug**: content entities (services, works, articles, categories, tags, studies, study_categories) carry a unique `slug`.
- **Order**: reorderable lists carry `order INT UNSIGNED DEFAULT 0` (alias “sort_order” normalized to `order`).
- **Money/coords**: decimals use `decimal(10,7)` for lat/lng.
- **JSON**: structured/repeatable config uses `json`.
- **Auth**: Laravel Sanctum personal access tokens. Admin endpoints behind `auth:sanctum` + permission middleware. Public website endpoints are unauthenticated read-only.

## Reconciliation notes (entities that appeared in multiple modules)

- **User** = Users module + Profile module (same table). All/Admins/Team are *role-based filtered views* of one `users` table — **no** `is_admin`/`user_type` column; scope derives from `role_id`.
- **Service / Work** = Services + Works + WebHome + WebServicesPortfolio (single `services`, single `works`; `works.service_id` → services).
- **Article** = News + WebHome + WebBlog (single `articles`). Has category, optional subcategory, author, tags.
- **Category** = News + Categories + WebBlog (single self-referencing `categories` for articles). **Study categories are a separate** `study_categories` table.
- **Tag** = News + Tags (single `tags`).
- **Ticket** = Tickets + WebContact (single `tickets`). Website contact form creates a ticket (`channel='website'`); dashboard replies create `ticket_replies`. A `type` column powers the dashboard KPI cards (contact / consultation / project).
- **Settings singletons**: `company_settings`, `home_settings`, `about_page` (+ `about_info_cards`), `site_settings` (custom code / cache / cookies / mail), `tracking_settings` (analytics / SEO / sitemap). The website Contact display info is folded into `company_settings` (+ `social_media_links`).
- **DashboardStats**: no table — computed aggregate endpoints over tickets/articles/users/etc.

---

## Migration order (parents before children)

| #  | table | notes |
|----|-------|-------|
| 00 | (edit) `users` | extend the default create_users_table migration with all profile fields; `role_id` nullable (FK added at the end) |
| 01 | `roles` | |
| 02 | `permissions` | |
| 03 | `permission_role` | pivot |
| 04 | `verification_codes` | OTP / password-reset codes |
| 05 | `categories` | self-ref `parent_id` |
| 06 | `tags` | |
| 07 | `services` | |
| 08 | `works` | FK `service_id` |
| 09 | `articles` | FK `category_id`, `subcategory_id`, `author_id` |
| 10 | `article_tag` | pivot |
| 11 | `study_categories` | |
| 12 | `studies` | FK `study_category_id` |
| 13 | `partners` | |
| 14 | `testimonials` | |
| 15 | `enquiry_types` | |
| 16 | `tickets` | FK `enquiry_type_id`, `assigned_to` |
| 17 | `ticket_replies` | FK `ticket_id`, `user_id` |
| 18 | `menus` | |
| 19 | `menu_items` | FK `menu_id`, self `parent_id`, morph `linkable` |
| 20 | `widgets` | |
| 21 | `social_media_links` | |
| 22 | `fonts` | |
| 23 | `site_font_settings` | FK `primary/secondary/tertiary_font_id` |
| 24 | `maintenance_modes` | |
| 25 | `company_settings` | singleton |
| 26 | `home_settings` | singleton |
| 27 | `about_page` | singleton |
| 28 | `about_info_cards` | FK `about_page_id` |
| 29 | `site_settings` | singleton |
| 30 | `tracking_settings` | singleton |
| 99 | `add_foreign_keys` | `users.role_id` → `roles.id` (backward FK) |

Migration filename prefix scheme: `2025_01_01_0001NN_create_<table>_table.php` where `NN` = the # above (so they sort deterministically after Laravel's `0001_01_01_*` defaults).

---

## Entities

### 00. User — `users` (SoftDeletes)
Auth + admin/staff accounts. Trait `HasApiTokens, HasFactory, Notifiable, SoftDeletes`.

| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| username | string unique | required, unique |
| first_name | string | required |
| last_name | string | required |
| name | string nullable | optional display name (fallback = first+last) |
| gender | enum('male','female') nullable | |
| language | enum('ar','en') default 'ar' | |
| role_id | foreignId nullable | → roles.id |
| is_active | boolean default 1 | (status toggle) |
| email | string unique | required, email, unique |
| email_verified_at | datetime nullable | |
| mobile_number | string nullable | |
| mobile_country_code | string(8) nullable default '+966' | |
| mobile_verified_at | datetime nullable | |
| password | string | hashed; required on create, optional on update |
| date_of_birth | date nullable | |
| avatar | string nullable | file path |
| linkedin_url | string nullable | url |
| twitter_url | string nullable | url |
| remember_token | rememberToken | |
| timestamps, softDeletes | | |

Relations: `belongsTo Role`; `hasMany TicketReply (user_id)`; `hasMany Article (author_id)`; `hasMany assignedTickets (Ticket.assigned_to)`.
Special: inline status toggle, role-scoped list tabs, send-welcome-email on create.

### 01. Role — `roles` (SoftDeletes)
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| name | string | required |
| slug | string unique nullable | machine key |
| description | text nullable | |
| language | enum('ar','en') default 'ar' | |
| is_active | boolean default 1 | |
| timestamps, softDeletes | | |

Relations: `hasMany User`; `belongsToMany Permission` (permission_role). Special: inline status toggle, permission matrix.

### 02. Permission — `permissions`
| field | type |
|---|---|
| id | bigIncrements |
| name | string unique (e.g. `articles.create`) |
| label_ar | string nullable |
| label_en | string nullable |
| module | string (group key, e.g. `articles`) |
| ability | enum('view','create','edit','delete') |
| order | int unsigned default 0 |
| timestamps |

Relations: `belongsToMany Role`.

### 03. permission_role (pivot)
`role_id` → roles, `permission_id` → permissions, timestamps. Unique (role_id, permission_id).

### 04. VerificationCode — `verification_codes`
OTP for phone/email verification & password reset.
| field | type |
|---|---|
| id | bigIncrements |
| identifier | string (email or phone) index |
| channel | enum('email','sms') |
| purpose | enum('login','password_reset','verify_email','verify_phone') |
| code | string(10) |
| expires_at | datetime |
| consumed_at | datetime nullable |
| timestamps |

### 05. Category — `categories` (SoftDeletes) — article categories
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| name | string | required |
| slug | string unique | |
| description | text nullable | |
| parent_id | foreignId nullable | → categories.id (self) |
| order | int unsigned default 0 | |
| is_active | boolean default 1 | |
| timestamps, softDeletes | | |

Relations: `belongsTo Category (parent)`, `hasMany Category (children)`, `hasMany Article`.

### 06. Tag — `tags` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| name | string required |
| slug | string unique |
| description | text nullable |
| language | enum('ar','en') default 'ar' |
| is_popular | boolean default 0 |
| is_active | boolean default 1 |
| order | int unsigned default 0 |
| timestamps, softDeletes |

Relations: `belongsToMany Article` (article_tag).

### 07. Service — `services` (SoftDeletes)
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| title | string | required |
| slug | string unique | |
| short_description | string(500) nullable | |
| description | longText nullable | full content (rich text) |
| icon | string nullable | icon path/class |
| image | string nullable | cover image path |
| contact_url | string nullable | |
| meta_title | string nullable | |
| meta_description | string nullable | |
| meta_image | string nullable | |
| is_active | boolean default 1 | |
| order | int unsigned default 0 | |
| timestamps, softDeletes | | |

Relations: `hasMany Work`. Special: duplicate, toggle-status, export, filter.

### 08. Work — `works` (SoftDeletes) — portfolio / previous works
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| service_id | foreignId nullable | → services.id |
| title | string | required |
| slug | string unique | |
| short_description | string(500) nullable | |
| description | longText nullable | |
| client_name | string nullable | |
| image | string nullable | cover |
| gallery | json nullable | array of image paths |
| is_featured | boolean default 0 | |
| is_active | boolean default 1 | |
| order | int unsigned default 0 | |
| published_at | datetime nullable | |
| meta_title | string nullable | |
| meta_description | string nullable | |
| meta_image | string nullable | |
| timestamps, softDeletes | | |

Relations: `belongsTo Service`. Special: duplicate, toggle-status, export, filter.

### 09. Article — `articles` (SoftDeletes) — news / blog
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| title | string | required |
| subtitle | string nullable | |
| slug | string unique | |
| excerpt | text nullable | |
| body | longText nullable | required when publishing |
| featured_image | string nullable | |
| featured_image_caption | string nullable | |
| source_name | string nullable | |
| location | string nullable | |
| category_id | foreignId nullable | → categories.id |
| subcategory_id | foreignId nullable | → categories.id |
| author_id | foreignId nullable | → users.id |
| status | enum('draft','published','scheduled','archived') default 'draft' | |
| publish_type | enum('immediate','scheduled') default 'immediate' | |
| published_at | datetime nullable | |
| is_featured | boolean default 0 | |
| views_count | unsignedBigInteger default 0 | |
| meta_title | string nullable | |
| meta_description | string nullable | |
| meta_image | string nullable | |
| timestamps, softDeletes | | |

Relations: `belongsTo Category`, `belongsTo Category as subcategory`, `belongsTo User as author`, `belongsToMany Tag`. Special: draft/publish/schedule, toggle featured, toggle status, preview, restore.

### 10. article_tag (pivot)
`article_id` → articles, `tag_id` → tags, timestamps. Unique (article_id, tag_id).

### 11. StudyCategory — `study_categories` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| name | string required |
| slug | string unique |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps, softDeletes |

Relations: `hasMany Study`.

### 12. Study — `studies` (SoftDeletes) — studies & research
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| title | string | required |
| slug | string unique | |
| summary | text nullable | |
| body | longText nullable | |
| cover_image | string nullable | |
| document_path | string nullable | pdf path |
| document_type | string nullable | mime/ext |
| document_size | unsignedBigInteger nullable | bytes |
| pages_count | unsignedInteger nullable | |
| study_category_id | foreignId nullable | → study_categories.id |
| author | string nullable | |
| published_at | datetime nullable | |
| year | unsignedSmallInteger nullable | for year selector |
| month | unsignedTinyInteger nullable | for month tabs |
| views | unsignedBigInteger default 0 | |
| downloads_count | unsignedBigInteger default 0 | |
| is_featured | boolean default 0 | |
| status | enum('draft','published','scheduled','archived') default 'draft' | |
| order | int unsigned default 0 | |
| meta_title | string nullable | |
| meta_description | string nullable | |
| meta_keywords | string nullable | |
| timestamps, softDeletes | | |

Relations: `belongsTo StudyCategory`. Special: download (increments downloads_count), view increment, year/month filter.

### 13. Partner — `partners` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| name | string required |
| logo | string nullable |
| url | string nullable |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps, softDeletes |

### 14. Testimonial — `testimonials` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| author_name | string required |
| author_title | string nullable |
| avatar | string nullable |
| quote | text required |
| rating | unsignedTinyInteger nullable (1–5) |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps, softDeletes |

### 15. EnquiryType — `enquiry_types` (SoftDeletes)
Dropdown of contact-form enquiry types (استفسارك).
| field | type |
|---|---|
| id | bigIncrements |
| name | string required (ar) |
| name_en | string nullable |
| slug | string unique |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps, softDeletes |

Relations: `hasMany Ticket`.

### 16. Ticket — `tickets` (SoftDeletes)
Contact/support tickets; created from website contact form or admin.
| field | type | rules |
|---|---|---|
| id | bigIncrements | |
| ticket_number | string unique | auto-generated (e.g. TK-2025-000123) |
| type | enum('contact','consultation','project','support','other') default 'contact' | powers dashboard KPIs |
| name | string | required |
| email | string nullable | email |
| country_dial_code | string(8) nullable default '+966' | |
| phone | string nullable | |
| enquiry_type_id | foreignId nullable | → enquiry_types.id |
| subject | string nullable | |
| message | longText | required |
| channel | enum('website','whatsapp','email','dashboard') default 'website' | |
| status | enum('new','open','replied','closed') default 'new' | |
| priority | enum('low','normal','high','urgent') default 'normal' | |
| is_read | boolean default 0 | |
| assigned_to | foreignId nullable | → users.id |
| admin_reply | longText nullable | latest reply snapshot |
| replied_at | datetime nullable | |
| ip_address | string(45) nullable | |
| user_agent | string nullable | |
| locale | string(5) nullable | |
| timestamps, softDeletes | | |

Relations: `belongsTo EnquiryType`, `belongsTo User as assignee (assigned_to)`, `hasMany TicketReply`. Special: reply-via-gmail, reply-via-whatsapp, change-status, mark-read.

### 17. TicketReply — `ticket_replies`
| field | type |
|---|---|
| id | bigIncrements |
| ticket_id | foreignId → tickets.id (cascade) |
| user_id | foreignId nullable → users.id |
| body | longText required |
| channel | enum('dashboard','gmail','whatsapp','email') default 'dashboard' |
| sent_at | datetime nullable |
| timestamps |

Relations: `belongsTo Ticket`, `belongsTo User`.

### 18. Menu — `menus` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| name | string required |
| slug | string unique |
| location | string nullable (header/footer/...) |
| language | enum('ar','en') default 'ar' |
| description | text nullable |
| is_active | boolean default 1 |
| timestamps, softDeletes |

Relations: `hasMany MenuItem`.

### 19. MenuItem — `menu_items`
| field | type |
|---|---|
| id | bigIncrements |
| menu_id | foreignId → menus.id (cascade) |
| parent_id | foreignId nullable → menu_items.id (self) |
| label | string required |
| type | enum('page','category','custom','service','work','article') default 'custom' |
| linkable_type | string nullable (morph) |
| linkable_id | unsignedBigInteger nullable (morph) |
| url | string nullable |
| icon | string nullable |
| target | enum('_self','_blank') default '_self' |
| css_class | string nullable |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps |

Relations: `belongsTo Menu`, `belongsTo MenuItem (parent)`, `hasMany MenuItem (children)`, `morphTo linkable`.

### 20. Widget — `widgets` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| title | string required |
| slug | string unique nullable |
| language | enum('ar','en') default 'ar' |
| position | string nullable (sidebar/footer/...) |
| content_type | enum('html','text','menu','social','recent_posts','custom') default 'html' |
| content | longText nullable |
| config | json nullable |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps, softDeletes |

### 21. SocialMediaLink — `social_media_links` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| name | string required |
| platform | enum('facebook','twitter','instagram','linkedin','youtube','whatsapp','snapchat','tiktok','telegram','other') |
| url | string required |
| icon_type | enum('class','image') default 'class' |
| icon_class | string nullable |
| icon_image | string nullable |
| is_active | boolean default 1 |
| order | int unsigned default 0 |
| timestamps, softDeletes |

### 22. Font — `fonts` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| name | string required |
| slug | string unique |
| font_family | string required |
| source | enum('google','custom','system') default 'custom' |
| url | string nullable (google url) |
| font_files | json nullable (woff/woff2/ttf paths) |
| weights | json nullable |
| is_default | boolean default 0 |
| is_active | boolean default 1 |
| order | int unsigned default 0 |
| timestamps, softDeletes |

Relations: referenced by `site_font_settings`.

### 23. SiteFontSetting — `site_font_settings`
| field | type |
|---|---|
| id | bigIncrements |
| language | enum('ar','en') default 'ar' |
| primary_font_id | foreignId nullable → fonts.id |
| secondary_font_id | foreignId nullable → fonts.id |
| tertiary_font_id | foreignId nullable → fonts.id |
| timestamps |

Relations: three `belongsTo Font`.

### 24. MaintenanceMode — `maintenance_modes` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| page_name | string required |
| slug | string nullable |
| language | enum('ar','en') default 'ar' |
| title | string nullable |
| description | longText nullable |
| image | string nullable |
| is_active | boolean default 0 |
| starts_at | datetime nullable |
| ends_at | datetime nullable |
| timestamps, softDeletes |

### 25. CompanySetting — `company_settings` (singleton)
| field | type |
|---|---|
| id | bigIncrements |
| name | string nullable (company name) |
| license_number | string nullable |
| logo_primary | string nullable |
| logo_footer | string nullable |
| favicon | string nullable |
| default_image | string nullable |
| company_email | string nullable |
| company_phone | string nullable |
| company_phone_country_code | string(8) nullable |
| branch_phone | string nullable |
| branch_phone_country_code | string(8) nullable |
| manager_name | string nullable |
| manager_email | string nullable |
| manager_phone | string nullable |
| manager_phone_country_code | string(8) nullable |
| country | string nullable |
| city | string nullable |
| district | string nullable |
| street | string nullable |
| address | string nullable |
| map_lat | decimal(10,7) nullable |
| map_lng | decimal(10,7) nullable |
| map_embed_url | string nullable |
| copyright_text | string nullable |
| established_date | date nullable |
| description | longText nullable |
| website_url | string nullable |
| time_format | string nullable |
| date_format | string nullable |
| timezone | string nullable |
| dark_mode_enabled | boolean default 0 |
| timestamps |

### 26. HomeSetting — `home_settings` (singleton)
Stores homepage section content. Fields (all nullable text/string unless noted):
`hero_eyebrow, hero_title, hero_subtitle, hero_image, hero_video_url, hero_cta_label, hero_cta_url, about_eyebrow, about_years_count(int), about_heading, about_intro(text), about_who_we_are(text), about_vision(text), about_values(text), about_mission(text), about_image, about_cta_label, about_cta_url, services_eyebrow, services_heading, services_description(text), works_eyebrow, works_heading, works_description(text), articles_eyebrow, articles_heading, testimonials_heading, testimonials_description(text), consultation_cta_label, consultation_cta_url` + timestamps.

### 27. AboutPage — `about_page` (singleton)
`hero_title, hero_subtitle, intro_heading, intro_body(longText), intro_image, metrics_eyebrow, metrics_headline, metrics_years(int), metrics_description(text), metrics_image, primary_cta_label, primary_cta_url, secondary_cta_label, secondary_cta_url, meta_title, meta_description` + timestamps. Relation: `hasMany AboutInfoCard`.

### 28. AboutInfoCard — `about_info_cards` (SoftDeletes)
| field | type |
|---|---|
| id | bigIncrements |
| about_page_id | foreignId → about_page.id (cascade) |
| title | string required |
| body | text nullable |
| type | enum('vision','mission','value','info') default 'info' |
| icon | string nullable |
| background_color | string(20) nullable |
| order | int unsigned default 0 |
| is_active | boolean default 1 |
| timestamps, softDeletes |

### 29. SiteSetting — `site_settings` (singleton)
Custom code / cache / cookies / mail.
`custom_css(longText), header_html(longText), body_html(longText), footer_html(longText), cache_enabled(bool), cache_ttl_minutes(int), cache_refresh_on_db_change(bool), cookie_banner_enabled(bool), cookie_banner_text_ar(text), cookie_banner_text_en(text), cookie_policy_url, mail_protocol, mail_library, mail_encryption, smtp_host, smtp_port(int), smtp_username, smtp_password, mail_from_name, mail_from_email, reply_to_email, contact_messages_enabled(bool), contact_email` + timestamps. (`smtp_password` cast encrypted.)

### 30. TrackingSetting — `tracking_settings` (singleton)
Analytics / SEO / sitemap.
`ga_enabled(bool), ga_property_id, tracking_mode, google_tag_id, google_tag_code(text), site_title, seo_language, seo_keywords(text), home_meta_title, site_description(text), meta_verification_codes(json), sitemap_path, sitemap_last_generated_at(datetime)` + timestamps.

---

## Authentication design (Sanctum)

- `POST /api/auth/login` — `{ login (email|username|mobile), password }` → `{ token, user }`.
- `POST /api/auth/logout` — revoke current token.
- `GET  /api/auth/me` — current user (+ role + permissions).
- `POST /api/auth/forgot-password` — `{ identifier, channel }` → sends OTP (verification_codes).
- `POST /api/auth/verify-otp` — `{ identifier, code, purpose }` → short-lived reset token.
- `POST /api/auth/reset-password` — `{ identifier, code, password, password_confirmation }`.
- `GET  /api/profile`, `PUT /api/profile`, `POST /api/profile/avatar`.
- `POST /api/profile/change-password`, `/change-email` (+otp), `/change-mobile` (+otp).
- Authorization: `permission:<name>` middleware checks `users.role -> permissions`.

## Endpoint surface (per entity)

Admin (auth:sanctum, prefix `/api/admin`): full `apiResource` (index, store, show, update, destroy) for every content/config entity, plus:
- `POST {entity}/{id}/toggle-status` where `is_active` exists.
- `POST {entity}/{id}/duplicate` for services, works.
- `POST {entity}/reorder` for reorderable lists.
- `articles`: `/{id}/publish`, `/{id}/schedule`, `/{id}/toggle-featured`, `/{id}/restore`.
- `tickets`: `/{id}/reply`, `/{id}/status`, `/{id}/assign`, `/{id}/read`.
- singletons (`company-settings`, `home-settings`, `about`, `site-settings`, `tracking-settings`): `GET` + `PUT` (firstOrCreate), plus file-upload sub-routes.
- `GET /api/admin/dashboard/stats`, `/dashboard/requests-chart`, `/dashboard/recent`.

Public (no auth, prefix `/api`): read-only —
`GET /home`, `/about`, `/services`, `/services/{slug}`, `/works`, `/works/{slug}`, `/blog`, `/blog/{slug}`, `/studies`, `/studies/{slug}`, `POST /studies/{id}/download`, `/partners`, `/testimonials`, `/menus/{location}`, `/social-links`, `/company-settings`, `/contact/enquiry-types`, `POST /contact`.