# ModHotels.net — Veritabanı Şeması

MySQL 8 / MariaDB, InnoDB, `utf8mb4_unicode_ci`. Tüm tablolarda `id` (bigint,
auto-increment) PK ve `created_at/updated_at` (aksi belirtilmedikçe). Silme
gereken yerlerde `deleted_at` (soft delete).

Notasyon: `FK→tablo` yabancı anahtar, `?` nullable, `[a|b]` enum.

---

## Kimlik & Roller

### users
| kolon | tip | not |
|---|---|---|
| name | string | |
| email | string, unique | |
| password | string | |
| role | [jobseeker\|employer\|admin] | ana rol (ince izinler için spatie/permission) |
| phone? | string | |
| avatar? | string | dosya yolu |
| locale | string(2), default `tr` | |
| is_active | bool, default true | |
| email_verified_at? | timestamp | |
| last_login_at? | timestamp | |

> Rol/izin detayı `spatie/laravel-permission` tabloları ile (roles, permissions,
> model_has_roles, ...). `role` kolonu hızlı ayrım için kalır.

---

## İş Arayan

### job_seeker_profiles
`user_id FK→users` (1-1), `headline?`, `about? text`, `birth_date?`,
`gender? [male|female|other]`, `city_id? FK→cities`, `district_id? FK→districts`,
`desired_position_id? FK→positions`, `desired_salary_min?`, `desired_salary_max?`,
`salary_currency default TRY`, `whatsapp?`, `willing_to_relocate bool`,
`accommodation_needed bool`, `availability? [immediate|1_month|negotiable]`,
`cv_pdf_path?`, `profile_completeness tinyint (0-100)`, `is_searchable bool`.

### experiences
`profile_id FK`, `company_name`, `position`, `employment_type?`, `city?`,
`start_date`, `end_date?`, `is_current bool`, `description? text`.

### educations
`profile_id FK`, `school`, `department?`, `degree_level [primary|high_school|associate|bachelor|master|phd]`,
`start_year?`, `end_year?`, `is_current bool`.

### certificates
`profile_id FK`, `name`, `issuer?`, `issue_date?`, `expire_date?`,
`credential_url?`, `file_path?`.

### references
`profile_id FK`, `name`, `company?`, `position?`, `phone?`, `email?`, `relationship?`.

### languages  (referans)
`name`, `code`.

### profile_languages  (pivot)
`profile_id FK`, `language_id FK`, `level [A1|A2|B1|B2|C1|C2|native]`.

### skills  (referans) + profile_skills (pivot)
`skills.name`; `profile_skills(profile_id, skill_id)`.

---

## İşveren & Firma

### companies
`user_id FK→users` (sahip), `name`, `slug unique`,
`category [hotel|resort|restaurant|cafe|agency|other]`, `logo?`, `cover?`,
`about? text`, `website?`, `phone?`, `whatsapp?`, `email?`, `address? text`,
`city_id? FK`, `district_id? FK`, `employee_count?`, `founded_year?`,
`socials? json` (instagram/linkedin/...), `is_verified bool`, `is_active bool`.

### company_galleries
`company_id FK`, `image_path`, `caption?`, `sort_order int`.

> İleride çok kullanıcılı firma için `company_members(company_id, user_id, role)`.

---

## İş İlanları

### positions (referans)   ### departments (referans)
`positions.name`, `positions.department_id? FK→departments`; `departments.name`.

### cities (referans)   ### districts (referans)
`cities.name`, `cities.plate_code?`; `districts.name`, `districts.city_id FK`.

### job_posts
| kolon | tip | not |
|---|---|---|
| company_id | FK→companies | |
| title | string | |
| slug | string, unique | |
| description | text | |
| position_id? | FK→positions | |
| department_id? | FK→departments | |
| city_id? | FK→cities | |
| district_id? | FK→districts | |
| employment_type | [full_time\|part_time\|seasonal\|permanent] | |
| experience_level | [none\|junior\|mid\|senior] | |
| education_level? | [none\|high_school\|associate\|bachelor\|master] | |
| min_salary? / max_salary? | int | |
| salary_currency | default TRY | |
| salary_period | [monthly\|daily\|hourly] | |
| provides_accommodation | bool | Konaklama |
| provides_lodging | bool | Lojman |
| provides_meals | bool | Yemek |
| provides_transport | bool | Servis |
| positions_available | int, default 1 | |
| status | [draft\|active\|paused\|closed\|expired] | |
| views_count | int, default 0 | |
| published_at? / expires_at? | timestamp | |

Diller: `title`, `description` çevrilebilir (translatable). İndeksler:
`(status, city_id, position_id, employment_type)` + FULLTEXT(`title`,`description`).

### job_post_languages (pivot)  — ilan için gereken dil şartı
`job_post_id FK`, `language_id FK`, `level`.

---

## Başvurular & Mülakat

### applications
`job_post_id FK`, `user_id FK→users` (aday), `cover_letter? text`,
`cv_snapshot? json` (başvuru anındaki CV kopyası), `status [new|reviewed|shortlisted|interview|rejected|hired]`,
`employer_note? text`, `applied_at`. Unique(`job_post_id`,`user_id`).

### application_status_logs  (opsiyonel iz)
`application_id FK`, `from_status?`, `to_status`, `changed_by FK→users`, `note?`.

### interviews
`application_id FK`, `scheduled_at`, `type [in_person|phone|video|whatsapp]`,
`location_or_link?`, `note? text`, `status [planned|done|cancelled|no_show]`.

### favorite_jobs (pivot)
`user_id FK`, `job_post_id FK`. Unique çift.

---

## Blog

### blog_categories
`name`, `slug unique`, `description?`. (çevrilebilir)

### blog_posts
`author_id FK→users`, `category_id? FK`, `title`, `slug unique`, `excerpt? text`,
`body longtext`, `cover_image?`, `status [draft|published]`, `published_at?`,
`views int`, `meta_title?`, `meta_description?`. (title/excerpt/body çevrilebilir)

### blog_comments
`post_id FK`, `user_id? FK`, `guest_name?`, `guest_email?`, `body text`,
`parent_id? FK→blog_comments`, `status [pending|approved|spam]`.

### tags + blog_post_tag (pivot)  — opsiyonel

---

## Reklam (tek gelir modeli)

### ad_zones
`name`, `slug unique`, `placement [header|sidebar|in_feed|footer|between_listings|job_detail]`,
`width?`, `height?`, `is_active bool`.

### ads
`zone_id FK`, `type [adsense|admanager|html|image|affiliate|sponsor]`, `title?`,
`image_path?`, `target_url?`, `html_code? text`, `adsense_slot?`,
`sponsor_name?`, `priority int`, `start_date?`, `end_date?`,
`impressions int`, `clicks int`, `is_active bool`.

> AdSense/Ad Manager için sadece slot/kod tutulur; HTML/affiliate/sponsor için
> içerik doğrudan saklanır. Zone bazında rotasyon + priority.

---

## Sistem: Ayarlar, Diller, Log, İstatistik

### settings
`key unique`, `value text`, `group`, `type [string|bool|int|json|text]`.
(site adı, logo, iletişim, sosyal, SEO defaultları, AdSense client id, ...)

### locales
`code unique (tr/en/ru)`, `name`, `is_active bool`, `is_default bool`, `sort_order`.

### translations  (admin "dil yönetimi")
`locale`, `group`, `key`, `value text`. Unique(`locale`,`group`,`key`).

### activity_logs  (spatie/activitylog)
`log_name`, `description`, `subject_type/id`, `causer_type/id`, `properties json`.

### page_views  (hafif istatistik)
`viewable_type/id` (job_post/blog_post/company), `ip_hash`, `referer?`, `created_at`.
(Dashboard grafikleri için günlük agregasyon.)

### notifications  (Laravel varsayılan)
Başvuru durum değişikliği, yeni başvuru, mülakat hatırlatma vb.

### ai_usages  (AI kullanım/maliyet izi)
`user_id? FK`, `feature [cv_generate|job_generate|cv_analyze|match_score|salary|career|chat]`,
`input_tokens?`, `output_tokens?`, `model?`, `created_at`.

---

## İlişki özeti

- User 1-1 JobSeekerProfile (jobseeker) / 1-n Company (employer)
- Company 1-n JobPost, 1-n CompanyGallery
- JobPost 1-n Application; Application 1-n Interview
- User n-n JobPost (favorite_jobs), 1-n Application
- JobSeekerProfile 1-n Experience/Education/Certificate/Reference, n-n Language/Skill
- BlogPost 1-n BlogComment; BlogCategory 1-n BlogPost
- AdZone 1-n Ad
```