erDiagram
    %% ORIGINAL ENTITIES %%
    employees {
        int id PK
        string name
        string code UK
        string son_of
        date dob
        string cnic UK "Soft-delete safe"
        enum gender
        enum marital_status
        enum blood_group
        string religion
        string image_path
        string biometric_id UK "Mapping for device logs"
        enum onboarding_status "docs_pending,policy_pending,active"
        boolean is_active
        timestamp deleted_at "nullable — for rehire support"
    }

    employee_employment {
        int id PK
        int employee_id FK
        int branch_id FK
        int designation_id FK
        int department_id FK
        date joining_date
        date valid_till "Contract expiry"
        decimal salary
        int payment_account_id FK
        int salary_expense_account_id FK
        enum status "draft,active,expired,terminated"
        string location "If diff from branch"
        decimal credit_limit
        string contract_file_path
    }

    employee_contacts {
        int id PK
        int employee_id FK
        string email UK
        string mobile_number
        text current_address
        text permanent_address
        string emergency_contact_name
        string emergency_contact_relation
        string emergency_contact_phone
    }

    employee_academics {
        int id PK
        int employee_id FK
        string degree_title
        string institute_name
        year passing_year
        string grade_or_cgpa
    }

    employee_references {
        int id PK
        int employee_id FK
        string reference_type
        string name
        string relation
        string address
        string designation
        string phone
        string email
    }

    employee_cheque_infos {
        int id PK
        int employee_id FK
        date transaction_date
        string account_number
        string cheque_number
        string cheque_date
        decimal amount
        string cheque_location
        text notes
    }

    employee_guarantors {
        int id PK
        int employee_id FK
        string name
        string son_of
        string relation
        string cnic
        string phone
        string residence_phone
        string occupation
        string city
        string business_address
        string business_phone
        string since_living "Time at address"
        string since_working "Time at job"
        string employer_name
        string attachment_path
    }

    employee_job_histories {
        int id PK
        int employee_id FK
        string company_name
        string designation
        date start_date
        date end_date
        int salary
        string reason_for_leaving
    }

    employee_job_changes {
        int id PK
        int employee_id FK
        enum change_type "Transfer, Promotion, Demotion, Salary_Increment"
        date effective_date
        int previous_branch_id FK
        int new_branch_id FK
        int previous_designation_id FK
        int new_designation_id FK
        decimal previous_salary
        decimal new_salary
        string change_reason
        int approved_by FK
    }

    %% NEW & UPDATED SETUP ENTITIES %%
    designations {
        int id PK
        int branch_id FK "nullable for Global"
        string designation_name UK
        string designation_code UK
        text description "nullable"
        boolean is_active
    }

    departments {
        int id PK
        int branch_id FK "nullable for Global"
        string department_name UK
        string department_code UK
        text description "nullable"
        boolean is_active
    }

    employee_family_members {
        int id PK
        int employee_id FK
        string name
        enum relation
        date dob
        string cnic
        boolean is_nominee
        decimal nominee_percentage
    }

    employee_internal_relations {
        int id PK
        int employee_id FK
        int related_employee_id FK
        string relation_type
    }

    employee_attachments {
        int id PK
        int employee_id FK
        enum attachable_type "cnic,cheque,edu_cert,exp_letter,etc"
        int attachable_id "nullable FK"
        string file_path
    }

    employee_exits {
        int id PK
        int employee_id FK
        enum exit_type "resigned,terminated,contract_ended,absconded,deceased"
        date resignation_date "Date intent was submitted"
        date notice_date
        int notice_period_days
        date last_working_date
        string reason
        enum clearance_status "pending,in_progress,cleared"
        decimal encashment_days "Unused leave payout"
        decimal encashment_amount
        decimal final_settlement_amount
        int salary_calculation_id FK "Nullable (Final Settlement Payroll Run)"
        int manager_approved_by FK
        enum status "pending, approved, rejected, completed"
    }

    employee_rejoinings {
        int id PK
        int employee_id FK
        int exit_id FK
        date rejoining_date
        int new_branch_id FK
        int new_designation_id FK
        decimal new_salary
        string rejoining_reason
    }

    allowance_deductions {
        int id PK
        int branch_id FK "nullable for Global"
        string name
        string type "Allowance, Deduction"
        text description
    }

    employee_allowance_deductions {
        int id PK
        int employee_id FK
        int allowance_deduction_id FK
        decimal amount
        date effective_from
        date effective_to "nullable"
        text description
    }

    operations {
        int id PK
        string name
        boolean is_active
    }

    employee_operations {
        int id PK
        int employee_id FK
        int operation_id FK
    }

    holidays {
        int id PK
        int branch_id FK "nullable for Global"
        string name
        date holiday_date
        boolean is_fixed
    }

    departments ||--o{ employee_employment : "defines"
    designations ||--o{ employee_employment : "defines"
    employees ||--o{ employee_employment : "has"
    
    employees ||--o{ employee_contacts : "has"
    employees ||--o{ employee_academics : "holds"
    employees ||--o{ employee_references : "provides"
    employees ||--o{ employee_guarantors : "has"
    employees ||--o{ employee_cheque_infos : "has"
    employees ||--o{ employee_job_histories : "has"
    employees ||--o{ employee_job_changes : "history"
    employees ||--o{ employee_family_members : "has"
    employees ||--o{ employee_internal_relations : "is"
    employees ||--o{ employee_attachments : "owns"
    
    employees ||--o{ employee_exits : "exits via"
    employees ||--o{ employee_rejoinings : "rejoins via"
    employees ||--o{ employee_allowance_deductions : "has custom components"
    allowance_deductions ||--o{ employee_allowance_deductions : "defines"
    employees ||--o{ employee_operations : "assigned to"
    operations ||--o{ employee_operations : "is assigned"
    holidays }o--|| employees : "applies to (branch-wise)"
            

erDiagram
    %% CORE POLICY ENGINE %%
    employees {
        int id PK
    }
    designations {
        int id PK
    }

    payroll_policy {
        int id PK
        string policy_name "e.g., Manager Policy v2"
        int designation_id FK "Anchor for the whole engine (Unique)"
        date effective_date
        date valid_to "nullable"
        boolean is_active
    }

    payroll_policy_branches {
        int id PK
        int payroll_policy_id FK
        int branch_id FK "Multi-branch deployment"
    }

    salary_policies {
        int id PK
        int payroll_policy_id FK
        string salary_type "fixed_basic, target_based_basic, no_basic"
        string target_not_ach_logic "percentage, amount"
        decimal target_not_ach_value
        string sales_level "individual, branch"
        decimal marketing_inc_percent
        decimal inquiry_inc_percent
        decimal recovery_inc_percent
        decimal cash_sale_inc_percent
        decimal recovery_close_percent
        decimal fine_collected_percent
        boolean require_ac_age_for_recovery_inc "For R. Inc. A/C age must >"
        int recovery_ac_age_limit
        boolean start_inc_from_ac_age_limit "Start Incentive from A/C age limit"
    }

    attendance_policies {
        int id PK
        int payroll_policy_id FK
        int paid_holidays_in_month
        int paid_half_days_in_month
        boolean allow_consec_holidays
        boolean deduct_all_on_exceed_limit
        decimal temp_advance_percent
        decimal fixed_advance_amount
        int in_grace_time_mins
        int out_grace_time_mins
        time shift_from_time
        time shift_to_time
        boolean is_sun
        boolean is_mon
        boolean is_tue
        boolean is_wed
        boolean is_thu
        boolean is_fri
        boolean is_sat
        boolean strict_holiday_pay "Sandwich rule"
        boolean crosses_midnight "Night Shifts"
        boolean enable_overtime
        decimal overtime_rate_multiplier "e.g. 1.5x"
        int min_overtime_minutes "Threshold to trigger OT"
    }

    policy_leave_rules {
        int id PK
        int attendance_policy_id FK
        int leave_type_id FK
        decimal total_days_annually "e.g. 18"
        decimal accrued_per_month "e.g. 1.5 (if missing, given as Lump Sum)"
    }

    policy_fine_rules {
        int id PK
        int attendance_policy_id FK
        int minutes_late
        decimal fine_value
        string fine_type "Gross, Basic"
    }



    target_incentive {
        int id PK
        int payroll_policy_id FK
        int operation_id FK
        enum sale_type "Installment, Cash"
        enum target_type "Auto, Manual"
        enum target_level "Branch, Individual"
        decimal target_amount
        decimal incentive_value
        enum incentive_type "Percentage, Amount"
        enum inc_plan "Straight, Slab"
        decimal target_not_achieved_penalty
    }

    product_incentive {
        int id PK
        int payroll_policy_id FK
        int operation_id FK
        enum sale_type "Cash, Installment"
        enum type "Model, Category"
        string item_code
        decimal percent
    }

    operations {
        int id PK
        string name "e.g., Marketing, Recovery, CRC, Verification"
        boolean is_active
    }

    leave_types {
        int id PK
        int branch_id FK "nullable for Global"
        string name
        boolean is_paid
    }

    %% RELATIONSHIPS: POLICY TREE %%
    designations ||--o{ payroll_policy : "owns"
    payroll_policy ||--o{ payroll_policy_branches : "deployed in"
    
    payroll_policy ||--|| salary_policies : "Tab 1"
    payroll_policy ||--|| attendance_policies : "Tab 2"
    payroll_policy ||--o{ target_incentive : "Tab 3"
    payroll_policy ||--o{ product_incentive : "Tab 4"
    
    attendance_policies ||--o{ policy_leave_rules : "has"
    attendance_policies ||--o{ policy_fine_rules : "has"

    leave_types ||--|{ policy_leave_rules : "defines"
    operations ||--o{ target_incentive : "defines comm for"
    operations ||--o{ product_incentive : "defines comm for"
    
    employees ||--o{ employee_operations : "assigned to"
    operations ||--o{ employee_operations : "is assigned"

    %% ATTENDANCE DATA %%
    attendance_punches {
        int id PK
        int employee_id FK
        date punch_date "Optimization/Night Shift"
        datetime punch_time
        enum punch_type "in, out"
        string device_id
    }

    attendance_corrections {
        int id PK
        int branch_id FK "Tracking which HR branch processed it"
        int employee_id FK
        date correction_date
        string original_status
        string corrected_status
        string reason
        int approved_by FK
        enum status "pending, approved, rejected"
    }

    daily_attendances {
        int id PK
        int employee_id FK
        date attendance_date
        time first_in_time
        time last_out_time
        enum status "Present, Absent, Leave, Late, Half-Day"
        int total_work_minutes
        int overtime_minutes "OT Tracking"
    }

    monthly_attendance_summaries {
        int id PK
        int employee_id FK
        year year
        int month
        int total_present_days
        int total_late_days
        int total_leave_days
        int total_overtime_minutes
    }

    employee_leaves {
        int id PK
        int employee_id FK
        int leave_type_id FK
        date start_date
        date end_date
        boolean is_half_day
        enum session "AM, PM"
        enum status "pending, approved, rejected"
        int approved_by FK
    }

    employee_leave_balances {
        int id PK
        int employee_id FK
        int leave_type_id FK
        int year
        decimal allocated_days
        decimal used_days
        decimal expired_days "Wiped at Year-End"
        decimal carried_forward_days "From previous year"
    }

    SALARY_CALCULATIONS {
        int id PK
        int employee_id FK
        int branch_id FK
        int payroll_policy_id FK "Snapshot"
        int month
        int year
        int working_days_in_month
        int present_days
        int absent_days
        int half_days
        int late_minutes
        decimal basic_salary "Base Pay"
        decimal total_incentives "Marketing + Product + CRC"
        decimal total_allowances "House, Fuel, etc"
        decimal attendance_fines
        decimal advance_recovered
        decimal extra_deductions
        decimal overtime_amount "Summary Field"
        decimal gross_salary
        decimal net_salary
        boolean is_final_settlement
        int exit_id FK "nullable"
        enum status "draft, approved, paid"
        date paid_date
        enum payment_mode "Bank, Cash, Cheque"
        int approved_by FK
        int paid_by FK
    }

    SALARY_COMPONENTS {
        int id PK
        int calculation_id FK
        enum component_type "earning, deduction"
        string label
        decimal amount
        string source_type "e.g., Advance, FineRule, Incentive"
        int source_id "FK to source table"
    }

    employee_payslips {
        int id PK
        int salary_calculation_id FK "Base Calculation"
        string payslip_number
        date generated_date
        string pdf_path
        boolean is_emailed
    }

    INCENTIVE_CALCULATIONS {
        int id PK
        int salary_calculation_id FK
        int incentive_rule_id FK "FK to target/product_incentive"
        decimal target_amount
        decimal achieved_amount
        decimal payout_amount
    }

    employee_advances {
        int id PK
        int employee_id FK
        int advance_type_id FK
        enum request_type "Advance, Loan (Legal/Tax distinction)"
        decimal amount_requested
        decimal installment_amount "Calculated if mode=installment"
        int installments_count "Total months"
        string recovery_mode "installment, one_time"
        date recovery_start_date
        enum status "pending, approved, rejected, paid"
        int approved_by FK
        boolean is_settled
    }

    employee_advance_repayments {
        int id PK
        int advance_id FK "FK to employee_advances"
        int salary_calculation_id FK "Nullable (If deducted via payroll)"
        decimal amount
        date repayment_date
        int installment_number
        enum payment_method "salary, cash, bank"
        string reference_number "e.g., Cheque No, Bank Txn ID"
        text notes
    }

    advance_types {
        int id PK
        int branch_id FK "nullable for Global"
        string name
        boolean is_active
    }

    allowance_deductions {
        int id PK
        int branch_id FK "nullable for Global"
        string name
        string type "Allowance, Deduction"
        text description
    }

    employee_allowance_deductions {
        int id PK
        int employee_id FK
        int allowance_deduction_id FK
        decimal amount
        date effective_from
        date effective_to "nullable"
        text description
    }

    employee_operations {
        int id PK
        int employee_id FK
        int operation_id FK
    }

    payroll_locks {
        int id PK
        int branch_id FK "nullable for Global"
        int month
        int year
        boolean is_locked "If true, blocks all attendance & payroll edits"
    }

    %% RELATIONSHIPS: LOG DATA %%    
    employees ||--o{ attendance_punches : "punches"
    employees ||--o{ daily_attendances : "has"
    employees ||--o{ monthly_attendance_summaries : "has"
    employees ||--o{ employee_leaves : "requests"
    leave_types ||--o{ employee_leaves : "type"
    leave_types ||--o{ employee_leave_balances : "type"
    employees ||--o{ employee_leave_balances : "has"
    
    advance_types ||--o{ employee_advances : "categorizes"
    employees ||--o{ employee_advances : "requests"
    employee_advances ||--o{ employee_advance_repayments : "has ledger"
    employees ||--o{ employee_allowance_deductions : "has custom"
    allowance_deductions ||--o{ employee_allowance_deductions : "defines"
    
    SALARY_CALCULATIONS ||--o{ SALARY_COMPONENTS : "has details"
    SALARY_CALCULATIONS ||--o{ INCENTIVE_CALCULATIONS : "has audit trail"
    SALARY_CALCULATIONS ||--o{ employee_payslips : "generates"

    employees ||--o{ employee_operations : "assigned to"
    operations ||--o{ employee_operations : "is assigned"