# Coco Dreams Lanka - Entity Relationship (ER) Diagram Design

## 1. High-Level System Overview & Dual-Database Bridge

```mermaid
erDiagram
    %% Core HR & Role Entities
    ROLES ||--o{ ROLE_PERMISSIONS : defines
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : includes
    ROLES ||--o{ USERS : assigned_to
    EMPLOYEES ||--o| USERS : has_account

    %% Organizational Structure
    DEPARTMENTS ||--o{ DESIGNATIONS : contains
    DEPARTMENTS ||--o{ EMPLOYEES : employs
    DESIGNATIONS ||--o{ EMPLOYEES : designates

    %% Operations & Logs
    EMPLOYEES ||--o{ ATTENDANCES : logs
    EMPLOYEES ||--o{ LEAVE_REQUESTS : applies
    LEAVE_TYPES ||--o{ LEAVE_REQUESTS : categorized_under

    %% Piece-Rate Production & Payroll
    EMPLOYEES ||--o{ PIECE_RATE_ENTRIES : produces
    PIECE_RATE_ITEMS ||--o{ PIECE_RATE_ENTRIES : priced_by
    EMPLOYEES ||--o{ PAYROLL_RECORDS : receives
    PAYROLL_RECORDS ||--o{ PAYROLL_ITEMS : contains
    PAYROLL_RECORDS ||--o{ PIECE_RATE_ENTRIES : settles

    %% Repair & Service Module
    EMPLOYEES ||--o{ REPAIR_JOBS : assigned_technician
    REPAIR_JOBS ||--o{ REPAIR_PARTS_USED : consumes
    REPAIR_JOBS ||--o{ REPAIR_STATUS_LOGS : records

    %% Experience & Service Letters
    EMPLOYEES ||--o{ EXPERIENCE_LETTERS : issued_for

    %% External POS Integration Bridge (cocodreams_pos)
    POS_CUSTOMERS ||--o{ POS_INVOICES : places_order
    POS_INVOICES ||--o{ POS_INVOICE_ITEMS : contains
    POS_CUSTOMERS ||..o{ REPAIR_JOBS : referenced_in
    POS_INVOICES ||..o{ REPAIR_JOBS : warranty_check
    POS_EMPLOYEES ||..o| EMPLOYEES : syncs_into
```

---

## 2. Detailed Entity & Attribute Specification

### Primary Database: `cocodreams_hr`

```mermaid
erDiagram
    USERS {
        bigint id PK
        string name
        string email UK
        string password
        bigint role_id FK
        bigint employee_id FK
        enum status "active, inactive"
        datetime email_verified_at
        string remember_token
        timestamp created_at
        timestamp updated_at
    }

    ROLES {
        bigint id PK
        string name "Super Admin, HR Admin, Manager, Technician, Employee"
        string slug UK
        string description
        timestamp created_at
        timestamp updated_at
    }

    PERMISSIONS {
        bigint id PK
        string name
        string slug UK
        string module "employees, payroll, attendance, leaves, repairs, pos_data, settings"
        timestamp created_at
        timestamp updated_at
    }

    ROLE_PERMISSIONS {
        bigint id PK
        bigint role_id FK
        bigint permission_id FK
        timestamp created_at
        timestamp updated_at
    }

    DEPARTMENTS {
        bigint id PK
        string name
        string code UK "ADM, PRD, MNT, SLS"
        text description
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    DESIGNATIONS {
        bigint id PK
        bigint department_id FK
        string name
        text description
        timestamp created_at
        timestamp updated_at
    }

    EMPLOYEES {
        bigint id PK
        string employee_number UK "CDL-0001"
        string pos_employee_id "POS-EMP-01 (External Sync Key)"
        string first_name
        string last_name
        string email UK
        string phone
        string nic_passport
        text address
        string emergency_contact
        date date_of_birth
        enum gender "male, female, other"
        bigint department_id FK
        bigint designation_id FK
        date joining_date
        date exit_date
        enum employment_type "monthly, contract_piece_rate, probation, part_time"
        enum salary_type "monthly_fixed, piece_rate_only, hybrid"
        decimal basic_salary "12,2"
        string bank_name
        string bank_account_no
        string bank_branch
        enum status "active, inactive, resigned, terminated"
        string profile_photo_path
        text notes
        timestamp created_at
        timestamp updated_at
    }

    PIECE_RATE_ITEMS {
        bigint id PK
        string item_code UK "PRI-ABC, PRI-PEAT5K"
        string name "Shoes ABC, Coco Peat 5kg Block"
        decimal default_rate "10,2"
        string unit "pcs, pairs, rolls, blocks"
        text description
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    PIECE_RATE_ENTRIES {
        bigint id PK
        date entry_date
        bigint employee_id FK
        bigint piece_rate_item_id FK
        decimal quantity "10,2"
        decimal rate_applied "10,2"
        decimal total_amount "12,2 (quantity * rate)"
        string batch_reference "BATCH-2026-001"
        text notes
        bigint payroll_record_id FK "nullable until settled"
        bigint created_by_user_id FK
        timestamp created_at
        timestamp updated_at
    }

    ATTENDANCES {
        bigint id PK
        bigint employee_id FK
        date date
        time check_in
        time check_out
        enum status "present, absent, leave, half_day, holiday"
        decimal working_hours "5,2"
        decimal overtime_hours "5,2"
        enum source "manual, excel_import, biometric"
        string notes
        timestamp created_at
        timestamp updated_at
    }

    LEAVE_TYPES {
        bigint id PK
        string name "Annual, Casual, Medical, Unpaid"
        int annual_quota
        boolean is_paid
        text description
        timestamp created_at
        timestamp updated_at
    }

    LEAVE_REQUESTS {
        bigint id PK
        bigint employee_id FK
        bigint leave_type_id FK
        date start_date
        date end_date
        decimal days_count "4,1"
        text reason
        enum status "pending, approved, rejected"
        bigint approved_by_user_id FK
        text rejection_reason
        timestamp created_at
        timestamp updated_at
    }

    PAYROLL_RECORDS {
        bigint id PK
        string payroll_month "2026-09"
        bigint employee_id FK
        string employment_type_at_run
        decimal basic_salary "12,2"
        decimal total_piece_rate_pay "12,2"
        decimal overtime_hours "5,2"
        decimal overtime_amount "12,2"
        decimal total_allowances "12,2"
        decimal gross_salary "12,2"
        decimal total_deductions "12,2"
        decimal net_salary "12,2"
        enum payment_status "draft, approved, paid"
        enum payment_method "bank_transfer, cash, cheque"
        date payment_date
        text notes
        bigint processed_by_user_id FK
        timestamp created_at
        timestamp updated_at
    }

    PAYROLL_ITEMS {
        bigint id PK
        bigint payroll_record_id FK
        enum type "allowance, deduction, piece_rate, overtime, bonus"
        string title
        decimal amount "12,2"
        timestamp created_at
        timestamp updated_at
    }

    REPAIR_JOBS {
        bigint id PK
        string job_number UK "CDL-SRV-2026-0001"
        string pos_customer_id "Virtual FK to POS DB"
        string customer_name
        string customer_phone
        string customer_email
        text customer_address
        string pos_invoice_number "Virtual FK to POS DB"
        string product_name
        string serial_number
        text problem_complaint
        bigint assigned_technician_id FK
        date received_date
        date estimated_completion_date
        date completed_date
        date delivered_date
        enum warranty_type "warranty, non_warranty"
        enum status "received, inspection, repairing, waiting_parts, completed, delivered"
        text technician_notes
        decimal labor_charges "10,2"
        decimal parts_charges "10,2"
        decimal total_charges "10,2"
        bigint created_by_user_id FK
        timestamp created_at
        timestamp updated_at
    }

    REPAIR_PARTS_USED {
        bigint id PK
        bigint repair_job_id FK
        string part_name
        string part_code
        int quantity
        decimal unit_cost "10,2"
        decimal total_cost "10,2"
        timestamp created_at
        timestamp updated_at
    }

    REPAIR_STATUS_LOGS {
        bigint id PK
        bigint repair_job_id FK
        string old_status
        string new_status
        bigint changed_by_user_id FK
        text comments
        timestamp created_at
        timestamp updated_at
    }

    EXPERIENCE_LETTERS {
        bigint id PK
        string reference_no UK "CDL/HR/EXP/2026/001"
        bigint employee_id FK
        date issue_date
        string employee_name
        string designation_title
        string department_name
        date joining_date
        date leaving_date
        text conduct_remarks
        string authorized_signatory_name
        string authorized_signatory_title
        bigint created_by_user_id FK
        timestamp created_at
        timestamp updated_at
    }

    %% Relationships inside cocodreams_hr
    ROLES ||--o{ ROLE_PERMISSIONS : "has"
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : "granted_to"
    ROLES ||--o{ USERS : "assigned_to"
    EMPLOYEES ||--o| USERS : "authenticates"
    DEPARTMENTS ||--o{ DESIGNATIONS : "contains"
    DEPARTMENTS ||--o{ EMPLOYEES : "belongs_to"
    DESIGNATIONS ||--o{ EMPLOYEES : "holds"
    EMPLOYEES ||--o{ ATTENDANCES : "logs"
    EMPLOYEES ||--o{ LEAVE_REQUESTS : "submits"
    LEAVE_TYPES ||--o{ LEAVE_REQUESTS : "specifies"
    EMPLOYEES ||--o{ PIECE_RATE_ENTRIES : "performs"
    PIECE_RATE_ITEMS ||--o{ PIECE_RATE_ENTRIES : "rated_by"
    EMPLOYEES ||--o{ PAYROLL_RECORDS : "receives"
    PAYROLL_RECORDS ||--o{ PAYROLL_ITEMS : "details"
    PAYROLL_RECORDS ||--o{ PIECE_RATE_ENTRIES : "settles"
    EMPLOYEES ||--o{ REPAIR_JOBS : "services"
    REPAIR_JOBS ||--o{ REPAIR_PARTS_USED : "uses"
    REPAIR_JOBS ||--o{ REPAIR_STATUS_LOGS : "tracks"
    EMPLOYEES ||--o{ EXPERIENCE_LETTERS : "granted"
```

---

### External Database: `cocodreams_pos`

```mermaid
erDiagram
    POS_CUSTOMERS {
        bigint id PK
        string customer_code UK "CUST-00101"
        string name
        string phone
        string email
        text address
        string city
        decimal outstanding_balance "12,2"
        timestamp created_at
        timestamp updated_at
    }

    POS_INVOICES {
        bigint id PK
        string invoice_number UK "INV-2026-0891"
        bigint pos_customer_id FK
        string customer_name
        date invoice_date
        decimal subtotal "12,2"
        decimal tax "12,2"
        decimal discount "12,2"
        decimal total_amount "12,2"
        string payment_status "paid, partially_paid"
        string payment_method "cash, bank_transfer, cheque"
        timestamp created_at
        timestamp updated_at
    }

    POS_INVOICE_ITEMS {
        bigint id PK
        bigint pos_invoice_id FK
        string product_code "MCH-COIR-01"
        string product_name "Automatic Coir Twisting Machine CT-500"
        decimal quantity "10,2"
        decimal unit_price "12,2"
        decimal total_price "12,2"
        timestamp created_at
        timestamp updated_at
    }

    POS_EMPLOYEES {
        bigint id PK
        string pos_employee_code UK "POS-EMP-01"
        string first_name
        string last_name
        string email
        string phone
        string department
        string position
        decimal salary "12,2"
        string status "active"
        timestamp created_at
        timestamp updated_at
    }

    POS_CUSTOMERS ||--o{ POS_INVOICES : "has_purchased"
    POS_INVOICES ||--o{ POS_INVOICE_ITEMS : "contains"
```

---

## 3. Cross-Database Synchronization & Reference Logic

| Source Field | Source DB | Target Field | Target DB | Nature of Integration |
|---|---|---|---|---|
| `pos_customers.id` | `cocodreams_pos` | `repair_jobs.pos_customer_id` | `cocodreams_hr` | **Typeahead Lookup**: Live AJAX search during repair ticket creation to attach customer profile & contact details. |
| `pos_invoices.invoice_number` | `cocodreams_pos` | `repair_jobs.pos_invoice_number` | `cocodreams_hr` | **Warranty Verification**: Pulls machine equipment sold, date of purchase, and invoice number. |
| `pos_employees.pos_employee_code` | `cocodreams_pos` | `employees.pos_employee_id` | `cocodreams_hr` | **Automated Sync**: `php artisan pos:sync-employees` automatically imports and updates new staff into HR. |
| `piece_rate_entries.id` | `cocodreams_hr` | `payroll_records.id` | `cocodreams_hr` | **Unbilled Absorption**: `payroll_record_id` FK locks settled production batches into the employee's monthly payslip. |
| `leave_requests.status = approved` | `cocodreams_hr` | `attendances.status = leave` | `cocodreams_hr` | **Automated Trigger**: Approved leave dates automatically generate `leave` entries in attendance sheet. |