# 04 — مواصفات نموذج البيانات (Data Model)

**الجمهور:** Backend Engineers  
**الخدمة:** `services/hr` — `src/db/migrate.js`  
**الاتفاقيات:** `company_id` إلزامي، `branch_id` للكيانات التشغيلية، camelCase في API

---

## 1. الجداول الموجودة (Phase 0 ✅)

### departments

```sql
id, company_id, name, name_en, code, is_active, created_at, updated_at
UNIQUE (company_id, code)
```

### job_titles

```sql
id, company_id, department_id NULL, name, name_en, code, is_active, ...
UNIQUE (company_id, code)
```

### employees

```sql
-- ~70 columns — see services/hr/src/db/migrate.js
-- Key: company_id, branch_id, department_id, job_title_id, reports_to_id
-- Saudi: national_id, gosi_*, iqama_expiry, qiwa_contract_number, ...
-- Salary: basic_salary, housing_allowance, transport_allowance, other_allowance
-- Status: employment_status ENUM, is_active
```

### employee_documents

```sql
id, company_id, employee_id, category, file_name, file_path, file_size,
mime_type, uploaded_by, notes, created_at
```

---

## 2. Phase 1 — Leave Management

### leave_types

```sql
CREATE TABLE leave_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  code VARCHAR(30) NOT NULL,
  name VARCHAR(100) NOT NULL,
  name_en VARCHAR(100) NOT NULL,
  is_paid TINYINT(1) NOT NULL DEFAULT 1,
  is_carry_forward TINYINT(1) NOT NULL DEFAULT 0,
  max_carry_forward_days DECIMAL(5,1) DEFAULT 0,
  max_days_per_request INT NULL,
  requires_attachment TINYINT(1) DEFAULT 0,
  color VARCHAR(7) DEFAULT '#3B82F6',
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_company_leave_code (company_id, code)
);
```

**Seed defaults (Saudi restaurant):**

| code | name | is_paid |
|------|------|---------|
| annual | إجازة سنوية | 1 |
| sick | إجازة مرضية | 1 |
| unpaid | إجازة بدون راتب | 0 |
| emergency | إجازة طارئة | 1 |
| hajj | إجازة حج | 1 |
| maternity | إجازة أمومة | 1 |

### holiday_lists

```sql
CREATE TABLE holiday_lists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  branch_id INT NULL,          -- NULL = company-wide
  name VARCHAR(100) NOT NULL,
  year INT NOT NULL,
  weekly_off_days VARCHAR(20) DEFAULT '5,6',  -- Fri,Sat (0=Sun)
  is_active TINYINT(1) DEFAULT 1,
  ...
);

CREATE TABLE holiday_dates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  holiday_list_id INT NOT NULL,
  holiday_date DATE NOT NULL,
  name VARCHAR(100) NOT NULL,
  name_en VARCHAR(100) NULL,
  FOREIGN KEY (holiday_list_id) REFERENCES holiday_lists(id) ON DELETE CASCADE
);
```

### leave_policies

```sql
CREATE TABLE leave_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  effective_from DATE NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  ...
);

CREATE TABLE leave_policy_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  leave_policy_id INT NOT NULL,
  leave_type_id INT NOT NULL,
  annual_allocation DECIMAL(5,1) NOT NULL,  -- e.g. 21 days
  accrual_frequency ENUM('yearly','monthly') DEFAULT 'yearly',
  FOREIGN KEY (leave_policy_id) REFERENCES leave_policies(id) ON DELETE CASCADE,
  FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
);
```

### leave_allocations

```sql
CREATE TABLE leave_allocations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  leave_type_id INT NOT NULL,
  leave_policy_id INT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  allocated_days DECIMAL(5,1) NOT NULL,
  used_days DECIMAL(5,1) NOT NULL DEFAULT 0,
  carried_forward DECIMAL(5,1) DEFAULT 0,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_employee_type (employee_id, leave_type_id),
  INDEX idx_period (period_start, period_end)
);
```

### leave_applications

```sql
CREATE TABLE leave_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  leave_type_id INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  total_days DECIMAL(5,1) NOT NULL,
  half_day TINYINT(1) DEFAULT 0,
  reason TEXT NULL,
  status ENUM('draft','pending','approved','rejected','cancelled') DEFAULT 'pending',
  approver_id INT NULL,           -- user_id from auth
  approved_at TIMESTAMP NULL,
  rejection_reason TEXT NULL,
  attachment_path VARCHAR(500) NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_employee (employee_id),
  INDEX idx_status (status),
  INDEX idx_dates (from_date, to_date)
);
```

### leave_ledger_entries

```sql
CREATE TABLE leave_ledger_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  leave_type_id INT NOT NULL,
  leave_application_id INT NULL,
  leave_allocation_id INT NULL,
  transaction_type ENUM('allocation','application','adjustment','encashment') NOT NULL,
  days DECIMAL(5,1) NOT NULL,     -- positive = credit, negative = debit
  balance_after DECIMAL(5,1) NOT NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_employee_type (employee_id, leave_type_id)
);
```

---

## 3. Phase 1 — Attendance

### shift_types

```sql
CREATE TABLE shift_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  code VARCHAR(30) NOT NULL,
  name VARCHAR(100) NOT NULL,
  name_en VARCHAR(100) NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  grace_period_minutes INT DEFAULT 15,
  half_day_hours DECIMAL(4,2) DEFAULT 4.00,
  full_day_hours DECIMAL(4,2) DEFAULT 8.00,
  is_night_shift TINYINT(1) DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE KEY uq_company_shift_code (company_id, code)
);
```

**Seed (restaurant):**

| code | start | end |
|------|-------|-----|
| morning | 06:00 | 14:00 |
| evening | 14:00 | 22:00 |
| night | 22:00 | 06:00 |

### shift_assignments

```sql
CREATE TABLE shift_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  shift_type_id INT NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE NULL,
  is_active TINYINT(1) DEFAULT 1,
  INDEX idx_employee (employee_id)
);
```

### employee_checkins

```sql
CREATE TABLE employee_checkins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  log_type ENUM('IN','OUT') NOT NULL,
  checkin_time DATETIME NOT NULL,
  device_id VARCHAR(50) NULL,
  source ENUM('device','manual','api','import') DEFAULT 'device',
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_employee_time (employee_id, checkin_time),
  INDEX idx_company_date (company_id, checkin_time)
);
```

### attendances

```sql
CREATE TABLE attendances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  branch_id INT NULL,
  employee_id INT NOT NULL,
  attendance_date DATE NOT NULL,
  shift_type_id INT NULL,
  status ENUM('present','absent','half_day','on_leave','holiday','week_off') NOT NULL,
  checkin_time DATETIME NULL,
  checkout_time DATETIME NULL,
  working_hours DECIMAL(5,2) NULL,
  late_entry TINYINT(1) DEFAULT 0,
  early_exit TINYINT(1) DEFAULT 0,
  leave_application_id INT NULL,
  notes VARCHAR(255) NULL,
  source ENUM('auto','manual','import') DEFAULT 'auto',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_emp_date (employee_id, attendance_date),
  INDEX idx_branch_date (branch_id, attendance_date)
);
```

---

## 4. Phase 2 — Payroll

### salary_components

```sql
CREATE TABLE salary_components (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  code VARCHAR(30) NOT NULL,
  name VARCHAR(100) NOT NULL,
  name_en VARCHAR(100) NOT NULL,
  component_type ENUM('earning','deduction') NOT NULL,
  is_taxable TINYINT(1) DEFAULT 0,
  depends_on_lwp TINYINT(1) DEFAULT 0,
  is_gosi_applicable TINYINT(1) DEFAULT 0,
  gosi_type ENUM('none','employee','employer') DEFAULT 'none',
  formula VARCHAR(500) NULL,       -- e.g. "basic_salary * 0.09"
  fixed_amount DECIMAL(12,2) NULL,
  account_id INT NULL,             -- accounting service account
  is_active TINYINT(1) DEFAULT 1,
  UNIQUE KEY uq_company_comp_code (company_id, code)
);
```

**Saudi defaults:**

| code | type | gosi |
|------|------|------|
| basic | earning | employee+employer |
| housing | earning | — |
| transport | earning | — |
| gosi_employee | deduction | employee (9%) |
| gosi_employer | earning* | employer (9%) — employer cost |
| eos_provision | deduction | — |

### salary_structures

```sql
CREATE TABLE salary_structures (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  payroll_frequency ENUM('monthly','weekly') DEFAULT 'monthly',
  is_active TINYINT(1) DEFAULT 1,
  ...
);

CREATE TABLE salary_structure_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  salary_structure_id INT NOT NULL,
  salary_component_id INT NOT NULL,
  amount DECIMAL(12,2) NULL,
  formula VARCHAR(500) NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (salary_structure_id) REFERENCES salary_structures(id) ON DELETE CASCADE
);
```

### salary_assignments

```sql
CREATE TABLE salary_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  salary_structure_id INT NOT NULL,
  base_amount DECIMAL(12,2) NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE NULL,
  is_active TINYINT(1) DEFAULT 1,
  INDEX idx_employee (employee_id)
);
```

### payroll_runs

```sql
CREATE TABLE payroll_runs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  run_number VARCHAR(30) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  payment_date DATE NULL,
  status ENUM('draft','processing','review','approved','paid','cancelled') DEFAULT 'draft',
  employee_count INT DEFAULT 0,
  total_gross DECIMAL(14,2) DEFAULT 0,
  total_deductions DECIMAL(14,2) DEFAULT 0,
  total_net DECIMAL(14,2) DEFAULT 0,
  journal_entry_id INT NULL,
  wps_file_path VARCHAR(500) NULL,
  approved_by INT NULL,
  approved_at TIMESTAMP NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_company_run (company_id, run_number)
);
```

### salary_slips

```sql
CREATE TABLE salary_slips (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  payroll_run_id INT NOT NULL,
  employee_id INT NOT NULL,
  slip_number VARCHAR(30) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  working_days INT DEFAULT 0,
  present_days DECIMAL(5,1) DEFAULT 0,
  lwp_days DECIMAL(5,1) DEFAULT 0,
  gross_earnings DECIMAL(12,2) DEFAULT 0,
  total_deductions DECIMAL(12,2) DEFAULT 0,
  net_pay DECIMAL(12,2) DEFAULT 0,
  status ENUM('draft','submitted','paid') DEFAULT 'draft',
  iban VARCHAR(34) NULL,
  bank_name VARCHAR(100) NULL,
  ...
  UNIQUE KEY uq_run_employee (payroll_run_id, employee_id)
);

CREATE TABLE salary_slip_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  salary_slip_id INT NOT NULL,
  salary_component_id INT NOT NULL,
  component_type ENUM('earning','deduction') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (salary_slip_id) REFERENCES salary_slips(id) ON DELETE CASCADE
);
```

---

## 5. Phase 3 — Lifecycle

### employee_separations

```sql
CREATE TABLE employee_separations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  separation_type ENUM('resignation','termination','retirement','contract_end') NOT NULL,
  resignation_date DATE NULL,
  last_working_date DATE NOT NULL,
  notice_period_served TINYINT(1) DEFAULT 0,
  reason TEXT NULL,
  exit_interview_notes TEXT NULL,
  status ENUM('draft','in_progress','completed') DEFAULT 'draft',
  ff_settlement_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

### ff_settlements (Full & Final)

```sql
CREATE TABLE ff_settlements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  employee_id INT NOT NULL,
  separation_id INT NOT NULL,
  pending_salary DECIMAL(12,2) DEFAULT 0,
  leave_encashment DECIMAL(12,2) DEFAULT 0,
  eos_amount DECIMAL(12,2) DEFAULT 0,
  deductions DECIMAL(12,2) DEFAULT 0,
  total_payable DECIMAL(12,2) DEFAULT 0,
  status ENUM('draft','approved','paid') DEFAULT 'draft',
  ...
);
```

---

## 6. Phase 4 — Recruitment (MVP)

### job_openings

```sql
CREATE TABLE job_openings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  branch_id INT NULL,
  department_id INT NULL,
  job_title_id INT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT NULL,
  positions INT DEFAULT 1,
  status ENUM('open','closed','cancelled') DEFAULT 'open',
  posted_at DATE NULL,
  closed_at DATE NULL,
  ...
);
```

### job_applicants

```sql
CREATE TABLE job_applicants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  job_opening_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NULL,
  phone VARCHAR(20) NULL,
  resume_path VARCHAR(500) NULL,
  status ENUM('applied','shortlisted','interview','offered','hired','rejected') DEFAULT 'applied',
  employee_id INT NULL,          -- set when hired
  notes TEXT NULL,
  ...
);
```

---

## 7. Auth Service Extension

```sql
-- services/auth migrate
ALTER TABLE users ADD COLUMN employee_id INT NULL;
ALTER TABLE users ADD INDEX idx_employee (employee_id);
```

**Rule:** one user ↔ one employee max; employee may exist without user.

---

## 8. ER Diagram (Phase 1–2)

```
departments ──┬── job_titles
              │
employees ────┼── shift_assignments ── shift_types
    │         │
    │         ├── leave_applications ── leave_types
    │         │         │
    │         │         └── leave_ledger_entries
    │         │
    │         ├── leave_allocations
    │         │
    │         ├── employee_checkins
    │         │
    │         ├── attendances
    │         │
    │         ├── salary_assignments ── salary_structures
    │         │                              │
    │         └── salary_slips ──────────────┘
    │                   │
    │                   └── salary_slip_lines
    │
    └── employee_documents

payroll_runs ── salary_slips
```

---

## 9. Indexes Strategy

- كل query list: `(company_id, ...)` composite
- Attendance by date: `(branch_id, attendance_date)`
- Leave pending: `(company_id, status)`
- Checkins import: `(employee_id, checkin_time)`

---

## 10. Migration Guidelines

1. أضف statements في `migrate.js` بنفس أسلوب Phase 0
2. استخدم `CREATE TABLE IF NOT EXISTS`
3. للـ ALTER: patch function منفصلة مع try/catch
4. Seed data: function `seedHrDefaults(pool, companyId)` عند أول setup