# Project Skills & Implementation Summary

This document provides a consolidated technical overview of the **Steve** project, focusing on folder structure, the branch module, and the pickup point integration logic.

---

## 📂 1. Project Folder Structure

The project follows a modular PHP architecture designed for an admin dashboard environment.

| Directory | Description |
|:---|:---|
| **`root/`** | Main frontend pages (e.g., `shipment-create.php`, `branch-list.php`, `login.php`). |
| **`api/`** | Backend REST-like endpoints organized by module (e.g., `api/branch/`, `api/pickuppoint/`). |
| **`api/.../services/`** | Courier-specific integration logic (e.g., `delhivery.php`). |
| **`config/`** | Core system settings, database connection (`db.php`), and global helpers (`helper.php`). |
| **`database/`** | SQL schema files and migration scripts for setting up tables. |
| **`admin/`** | UI assets and HTML templates from the UBold Bootstrap 5 Admin Dashboard. |
| **`assets/`** | Project-wide CSS, JS, and vendor plugins (Select2, DataTables, etc.). |

---

## 🏢 2. Branch Management Module (`tbl_branch`)

The Branch module serves as the primary organizational unit for shipments and pickup points.

### Key Components:
- **Database Table**: `tbl_branch`
    - Fields: `id`, `company_id`, `branch_name`, `branch_code`, `status`, `contact_no`, `state`.
- **API Endpoint**: `api/branch/read.php`
    - **Logic**: Fetches branches with a `LEFT JOIN` on `tbl_company` to display parent company details.
    - **Features**: Supports DataTables parameters (search, pagination, multi-column ordering).
- **Frontend**: `branch-list.php` and `branch-add.php`.
    - **Usage**: Used as a filter in most list views and is the parent container for Pickup Points.

---

## 📍 3. Pickup Point & Booking Logic (`tbl_pickup_points`)

This is a critical module that integrates directly with **Delhivery API** for warehouse management.

### Pickup Point Management:
- **API Sync**: `api/pickuppoint/services/delhivery.php`
    - Handles `create` and `edit` actions with Delhivery.
    - **Endpoint used**: `/api/backend/clientwarehouse/create/` (and `/edit/`).
    - **Payload**: Includes mandatory phone, city, name, pin, and detailed return address.
- **Sync Status**: Tracked via `delhivery_synced` (boolean) and `delhivery_response` (JSON) in the database.

### Shipment Booking Workflow (`shipment-create.php`):
1. **Branch-Driven Filtering**: Selecting a Branch dynamically loads associated Pickup Points.
2. **Auto-Fill Logic**: Selecting a Pickup Point auto-populates the **Consignor Info** tab (Name, Phone, Address, Pincode).
3. **Delhivery Integration**:
    - **TAT Check**: `api/tat/delhivery.php` is called to fetch Expected Delivery Dates during booking.
    - **Serviceability**: Validates pincodes against carrier service areas.

---

## 🔄 4. Shipment Status Update Feature

A professional interface allowing manual tracking updates for "Own Courier" shipments.

### Technical Details:
- **Page**: `shipment-status-update.php`
- **Logic**: 
    - Real-time search by **AWB**, **Reference ID**, or **Booking ID**.
    - **API Create**: `api/tracking/create.php` saves status changes and updates `tbl_bookings.last_status`.
    - **API Read**: `api/tracking/read.php` displays chronological history with color-coded badges.
- **Status Options**: Pending, Manifested, In Transit, Out For Delivery, Delivered, LOST, RTO.

---

## 🛠️ 5. Technical Stack Reference

- **Language**: PHP 7.4+
- **Database**: MySQL / MariaDB using **PDO** with prepared statements.
- **Frontend**:
    - **CSS**: Bootstrap 5 (UBold Theme).
    - **JS**: jQuery, Select2 (for searchable dropdowns), DataTables (for dynamic lists).
- **Authentication**: Session-based with granular permission checks via `require_api_permission()`.

---

## 🏗️ 6. API Service Architecture (Router/Dispatcher Pattern)

The project implements a highly modular **Service Router** pattern for handling third-party integrations (like Delhivery) and local logic (Own Courier).

### Architecture Flow:
1.  **API Endpoint Request**: A frontend request hits a standard endpoint (e.g., `api/tat/delhivery.php`).
2.  **Service Router**: The endpoint calls a central router function in a `services/courier_service.php` file.
3.  **Registry Lookup**: The router looks up a `$COURIER_REGISTRY` mapping based on:
    -   `code_prefix` (e.g., "DEL" for Delhivery).
    -   `id` (e.g., ID 2 for Own Courier).
    -   `name_contains` (string matching).
4.  **Handler Dispatch**: The router dynamically requires the specific service file (e.g., `services/delhivery.php`) and executes the registered handler function.

### Implemented Service Blocks:
| Module | Router File | Key Services |
|:---|:---|:---|
| **TAT** | `api/tat/services/courier_service.php` | `delhivery.php`, `own.php` |
| **Label** | `api/label/services/courier_service.php` | `delhivery.php`, `owncourier.php` |
| **Pickup Point** | `api/pickuppoint/services/courier_service.php` | `delhivery.php`, `owncourrier.php` |

### Benefits:
-   **Extensibility**: Adding a new courier only requires creating a new service file and adding one line to the registry.
-   **Security**: Credentials and API URLs are passed from the database to the service handlers, ensuring environment flexibility (Staging vs. Production).
-   **Standardization**: All third-party communication is encapsulated in the `services/` layer, keeping the API endpoints clean.

---

## 📋 7. Key API Mapping

| Feature | Read Endpoint | Create/Update Endpoint | Service Logic |
|:---|:---|:---|:---|
| **Branch** | `api/branch/read.php` | Standard CRUD | Local DB |
| **Pickup Point** | `api/pickuppoint/read.php` | `api/pickuppoint/delhivery/create.php` | `services/delhivery.php` |
| **Shipment** | `api/shipment/read.php` | `api/shipment/create.php` | Standard CRUD |
| **Tracking** | `api/tracking/read.php` | `api/tracking/create.php` | `services/courier_service.php` |
| **TAT Check** | N/A | `api/tat/delhivery.php` (GET) | `services/delhivery.php` |
| **Labels** | N/A | `api/label/print.php` | `services/courier_service.php` |

---

## 👥 8. HR Management Module (Employee, Salary, Attendance, Payroll)

### 8.1 Employee Management (`tbl_employees`)

**Location**: `employee-add.php`, `employee-list.php`, `api/employee/`

**Implemented Features:**
- Add/Edit employees with comprehensive details
- Employee filtering by branch, designation, and status
- User credentials (username/password) for employee login
- Personal information tracking (Father/Mother name, Education, Experience)

**Database Fields**:
```
id, branch_id, role_id, designation_id, name, age, email, phone,
salary, experience, education, father_name, mother_name,
address, city, state, country, pincode, user_id, password, status,
created_by, created_at, updated_by, updated_at
```

**API Endpoints**:
- `POST api/employee/create.php` - Create new employee
- `GET api/employee/read.php` - List with DataTable filters
- `GET api/employee/read_single.php?id=` - Get single employee
- `POST api/employee/update.php` - Update employee details
- `POST api/employee/delete.php` - Delete employee

---

### 8.2 Salary Template System (Planned)

**Location**: `salary-template-add.php`, `salary-template-list.php`, `api/salary_template/`

**Planned Features:**
- Create salary templates for different employee levels
- Define salary components (Basic, HRA, DA, Allowances)
- Track deductions (PF, Insurance, Tax)
- Auto-calculate gross and net salary
- Apply templates to employees

**New Database Tables**:
```sql
-- Salary Templates Master
CREATE TABLE IF NOT EXISTS `tbl_salary_templates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `template_name` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT,
  `basic_salary` DECIMAL(10,2) DEFAULT 0,
  `hra` DECIMAL(10,2) DEFAULT 0,
  `da` DECIMAL(10,2) DEFAULT 0,
  `medical_allowance` DECIMAL(10,2) DEFAULT 0,
  `conveyance` DECIMAL(10,2) DEFAULT 0,
  `other_allowances` DECIMAL(10,2) DEFAULT 0,
  `pf_deduction` DECIMAL(10,2) DEFAULT 0,
  `insurance_deduction` DECIMAL(10,2) DEFAULT 0,
  `tax_deduction` DECIMAL(10,2) DEFAULT 0,
  `other_deductions` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Salary Template Master';

-- Employee-Template Assignment
CREATE TABLE IF NOT EXISTS `tbl_employee_salary_templates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `salary_template_id` INT NOT NULL,
  `assigned_date` DATE NOT NULL,
  `effective_date` DATE,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `assigned_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_employee_template` (`employee_id`, `salary_template_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`salary_template_id`) REFERENCES `tbl_salary_templates`(id),
  FOREIGN KEY (`assigned_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Salary Template Assignment';
```

---

### 8.3 Shift Management (Planned)

**Location**: `shift-add.php`, `shift-list.php`, `api/shift/`

**Planned Features:**
- Define shift timings (Morning, Evening, Night, Flexible)
- Set break timings and shift allowances
- Assign shifts to employees
- Track shift rotation history

**New Database Tables**:
```sql
-- Shifts Master
CREATE TABLE IF NOT EXISTS `tbl_shifts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `shift_name` VARCHAR(100) NOT NULL UNIQUE,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `duration_hours` DECIMAL(3,1),
  `break_minutes` INT DEFAULT 0,
  `shift_allowance` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Shift Timings Master';

-- Employee-Shift Assignment
CREATE TABLE IF NOT EXISTS `tbl_employee_shifts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `shift_id` INT NOT NULL,
  `assigned_date` DATE NOT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `assigned_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp_shift` (`employee_id`, `shift_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `tbl_shifts`(id),
  FOREIGN KEY (`assigned_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Shift Assignment';
```

---

### 8.4 Attendance Management (Planned)

**Location**: `attendance-add.php`, `attendance-list.php`, `api/attendance/`

**Planned Features:**
- Daily attendance marking (Present, Absent, Leave, Half-day, Weekend, Holiday)
- Bulk attendance upload (CSV)
- Filter by date range, employee, branch, status
- Attendance summary dashboard (Monthly, Quarterly)
- Integration with attendance devices

**New Database Table**:
```sql
-- Daily Attendance Records
CREATE TABLE IF NOT EXISTS `tbl_attendance` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `attendance_date` DATE NOT NULL,
  `status` ENUM('present','absent','leave','half_day','weekend','holiday') DEFAULT 'present',
  `shift_id` INT,
  `check_in_time` TIME,
  `check_out_time` TIME,
  `notes` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_attendance` (`employee_id`, `attendance_date`),
  KEY `idx_emp_date` (`employee_id`, `attendance_date`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `tbl_shifts`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Attendance Records';

-- Monthly Attendance Summary
CREATE TABLE IF NOT EXISTS `tbl_attendance_summary` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `month_year` DATE NOT NULL,
  `total_days` INT DEFAULT 0,
  `present_days` INT DEFAULT 0,
  `absent_days` INT DEFAULT 0,
  `leave_days` INT DEFAULT 0,
  `half_days` INT DEFAULT 0,
  `weekend_days` INT DEFAULT 0,
  `holiday_days` INT DEFAULT 0,
  `working_days` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp_month` (`employee_id`, `month_year`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Monthly Attendance Summary';
```

---

### 8.5 Shift-Based Payroll (Planned)

**Location**: `payroll-generate.php`, `payroll-view.php`, `api/payroll/`

**Planned Features:**
- Automatic monthly payroll calculation based on:
  - Salary template
  - Attendance days
  - Shift allowances
  - All deductions
- Payroll processing status (Draft, Approved, Paid)
- Payslip PDF generation
- Payroll reconciliation reports

**New Database Table**:
```sql
-- Monthly Payroll Records
CREATE TABLE IF NOT EXISTS `tbl_payroll` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `salary_month` DATE NOT NULL,
  `salary_template_id` INT,
  `working_days` INT DEFAULT 0,
  `attendance_days` INT DEFAULT 0,
  `leave_days` INT DEFAULT 0,
  `half_days` INT DEFAULT 0,
  `absence_days` INT DEFAULT 0,
  `per_day_salary` DECIMAL(10,2) DEFAULT 0,
  `basic_salary` DECIMAL(10,2) DEFAULT 0,
  `hra` DECIMAL(10,2) DEFAULT 0,
  `da` DECIMAL(10,2) DEFAULT 0,
  `allowances` DECIMAL(10,2) DEFAULT 0,
  `shift_allowance` DECIMAL(10,2) DEFAULT 0,
  `gross_salary` DECIMAL(10,2) DEFAULT 0,
  `pf_deduction` DECIMAL(10,2) DEFAULT 0,
  `insurance_deduction` DECIMAL(10,2) DEFAULT 0,
  `tax_deduction` DECIMAL(10,2) DEFAULT 0,
  `other_deductions` DECIMAL(10,2) DEFAULT 0,
  `total_deductions` DECIMAL(10,2) DEFAULT 0,
  `net_salary` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('draft','approved','paid') DEFAULT 'draft',
  `notes` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `approved_by` INT,
  `approved_at` TIMESTAMP NULL,
  `paid_date` DATE,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_payroll` (`employee_id`, `salary_month`),
  KEY `idx_month` (`salary_month`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`salary_template_id`) REFERENCES `tbl_salary_templates`(id),
  FOREIGN KEY (`created_by`) REFERENCES `tbl_employees`(id),
  FOREIGN KEY (`approved_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Monthly Payroll Records';

-- Payroll Adjustments/Corrections
CREATE TABLE IF NOT EXISTS `tbl_payroll_adjustments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `payroll_id` INT NOT NULL,
  `adjustment_type` ENUM('bonus','incentive','deduction','correction') DEFAULT 'correction',
  `amount` DECIMAL(10,2) NOT NULL,
  `description` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payroll_id`) REFERENCES `tbl_payroll`(id) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payroll Adjustments and Corrections';
```

---

### 8.6 Payroll Calculation Logic

**Formula**:
```
Per Day Salary = Gross Salary / Working Days

Attendance-Based Salary = (Per Day Salary × Attendance Days)

With Shift Allowance = Attendance-Based Salary + Shift Allowance

Deductions = PF + Insurance + Tax + Other Deductions

NET SALARY = Gross Salary - Deductions + Shift Allowance
```

---

## 📊 9. HR Module Dependencies

| Component | Database Table | API Folder | Frontend Page | Status |
|:---|:---|:---|:---|:---|
| **Employee Master** | `tbl_employees` | `api/employee/` | `employee-add.php`, `employee-list.php` | ✅ Implemented |
| **Salary Templates** | `tbl_salary_templates` | `api/salary_template/` | `salary-template-add.php`, `salary-template-list.php` | ✅ Implemented |
| **Template Assignment** | `tbl_employee_salary_templates` | `api/employee_salary_assignment/` | `employee-salary-assign.php` | ✅ Implemented |
| **Shifts** | `tbl_shifts` | `api/shift/` | `shift-add.php`, `shift-list.php` | ✅ Implemented |
| **Shift Assignment** | `tbl_employee_shifts` | `api/employee_shift/` | Dashboard | 📋 Planned |
| **Attendance** | `tbl_attendance` | `api/attendance/` | `attendance-add.php`, `attendance-list.php` | ✅ Implemented |
| **Attendance Summary** | `tbl_attendance_summary` | `api/attendance_summary/` | `attendance-report.php` | 📋 Planned |
| **Payroll** | `tbl_payroll` | `api/payroll/` | `payroll-generate.php`, `payroll-list.php` | ✅ Implemented |
| **Payroll Adjustments** | `tbl_payroll_adjustments` | `api/payroll/` | `payroll-adjust.php` | 📋 Planned |

---

## 🗄️ 10. Complete SQL Migration Script

**File Location**: `database/migrations/hrm_extensions.sql`

```sql
-- ============================================
-- HR MODULE EXTENSION MIGRATION SCRIPT
-- Created: 2026-02-16
-- For: Employee Management, Salary, Attendance, Payroll
-- ============================================

-- 1. SALARY TEMPLATES TABLE
CREATE TABLE IF NOT EXISTS `tbl_salary_templates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `template_name` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT,
  `basic_salary` DECIMAL(10,2) DEFAULT 0,
  `hra` DECIMAL(10,2) DEFAULT 0,
  `da` DECIMAL(10,2) DEFAULT 0,
  `medical_allowance` DECIMAL(10,2) DEFAULT 0,
  `conveyance` DECIMAL(10,2) DEFAULT 0,
  `other_allowances` DECIMAL(10,2) DEFAULT 0,
  `pf_deduction` DECIMAL(10,2) DEFAULT 0,
  `insurance_deduction` DECIMAL(10,2) DEFAULT 0,
  `tax_deduction` DECIMAL(10,2) DEFAULT 0,
  `other_deductions` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Salary Template Master';

-- 2. EMPLOYEE SALARY TEMPLATE ASSIGNMENT
CREATE TABLE IF NOT EXISTS `tbl_employee_salary_templates` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `salary_template_id` INT NOT NULL,
  `assigned_date` DATE NOT NULL,
  `effective_date` DATE,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `assigned_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_employee_template` (`employee_id`, `salary_template_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`salary_template_id`) REFERENCES `tbl_salary_templates`(id),
  FOREIGN KEY (`assigned_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Salary Template Assignment';

-- 3. SHIFTS MASTER TABLE
CREATE TABLE IF NOT EXISTS `tbl_shifts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `shift_name` VARCHAR(100) NOT NULL UNIQUE,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `duration_hours` DECIMAL(3,1),
  `break_minutes` INT DEFAULT 0,
  `shift_allowance` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Shift Timings Master';

-- Sample Shifts
INSERT INTO `tbl_shifts` (`shift_name`, `start_time`, `end_time`, `duration_hours`, `break_minutes`, `shift_allowance`, `status`) VALUES
('Morning Shift', '06:00:00', '14:00:00', 8.0, 30, 0, 'active'),
('Evening Shift', '14:00:00', '22:00:00', 8.0, 30, 100, 'active'),
('Night Shift', '22:00:00', '06:00:00', 8.0, 30, 200, 'active'),
('Flexible Shift', '09:00:00', '17:00:00', 8.0, 60, 0, 'active');

-- 4. EMPLOYEE SHIFT ASSIGNMENT
CREATE TABLE IF NOT EXISTS `tbl_employee_shifts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `shift_id` INT NOT NULL,
  `assigned_date` DATE NOT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `assigned_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp_shift` (`employee_id`, `shift_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `tbl_shifts`(id),
  FOREIGN KEY (`assigned_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Shift Assignment';

-- 5. DAILY ATTENDANCE RECORDS
CREATE TABLE IF NOT EXISTS `tbl_attendance` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `attendance_date` DATE NOT NULL,
  `status` ENUM('present','absent','leave','half_day','weekend','holiday') DEFAULT 'present',
  `shift_id` INT,
  `check_in_time` TIME,
  `check_out_time` TIME,
  `notes` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_by` INT,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_attendance` (`employee_id`, `attendance_date`),
  KEY `idx_emp_date` (`employee_id`, `attendance_date`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`shift_id`) REFERENCES `tbl_shifts`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Attendance Records';

-- 6. MONTHLY ATTENDANCE SUMMARY
CREATE TABLE IF NOT EXISTS `tbl_attendance_summary` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `month_year` DATE NOT NULL,
  `total_days` INT DEFAULT 0,
  `present_days` INT DEFAULT 0,
  `absent_days` INT DEFAULT 0,
  `leave_days` INT DEFAULT 0,
  `half_days` INT DEFAULT 0,
  `weekend_days` INT DEFAULT 0,
  `holiday_days` INT DEFAULT 0,
  `working_days` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_emp_month` (`employee_id`, `month_year`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Monthly Attendance Summary';

-- 7. PAYROLL RECORDS
CREATE TABLE IF NOT EXISTS `tbl_payroll` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `employee_id` INT NOT NULL,
  `salary_month` DATE NOT NULL,
  `salary_template_id` INT,
  `working_days` INT DEFAULT 0,
  `attendance_days` INT DEFAULT 0,
  `leave_days` INT DEFAULT 0,
  `half_days` INT DEFAULT 0,
  `absence_days` INT DEFAULT 0,
  `per_day_salary` DECIMAL(10,2) DEFAULT 0,
  `basic_salary` DECIMAL(10,2) DEFAULT 0,
  `hra` DECIMAL(10,2) DEFAULT 0,
  `da` DECIMAL(10,2) DEFAULT 0,
  `allowances` DECIMAL(10,2) DEFAULT 0,
  `shift_allowance` DECIMAL(10,2) DEFAULT 0,
  `gross_salary` DECIMAL(10,2) DEFAULT 0,
  `pf_deduction` DECIMAL(10,2) DEFAULT 0,
  `insurance_deduction` DECIMAL(10,2) DEFAULT 0,
  `tax_deduction` DECIMAL(10,2) DEFAULT 0,
  `other_deductions` DECIMAL(10,2) DEFAULT 0,
  `total_deductions` DECIMAL(10,2) DEFAULT 0,
  `net_salary` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('draft','approved','paid') DEFAULT 'draft',
  `notes` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `approved_by` INT,
  `approved_at` TIMESTAMP NULL,
  `paid_date` DATE,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_payroll` (`employee_id`, `salary_month`),
  KEY `idx_month` (`salary_month`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`employee_id`) REFERENCES `tbl_employees`(id) ON DELETE CASCADE,
  FOREIGN KEY (`salary_template_id`) REFERENCES `tbl_salary_templates`(id),
  FOREIGN KEY (`created_by`) REFERENCES `tbl_employees`(id),
  FOREIGN KEY (`approved_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Monthly Payroll Records';

-- 8. PAYROLL ADJUSTMENTS
CREATE TABLE IF NOT EXISTS `tbl_payroll_adjustments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `payroll_id` INT NOT NULL,
  `adjustment_type` ENUM('bonus','incentive','deduction','correction') DEFAULT 'correction',
  `amount` DECIMAL(10,2) NOT NULL,
  `description` TEXT,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`payroll_id`) REFERENCES `tbl_payroll`(id) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`) REFERENCES `tbl_employees`(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payroll Adjustments and Corrections';

-- Sample Salary Templates
INSERT INTO `tbl_salary_templates` (`template_name`, `basic_salary`, `hra`, `da`, `medical_allowance`, `conveyance`, `pf_deduction`, `insurance_deduction`, `tax_deduction`, `status`) VALUES
('Junior Level', 25000, 5000, 2500, 1000, 1500, 2500, 500, 2000, 'active'),
('Senior Level', 50000, 10000, 5000, 2000, 2500, 5000, 1000, 4000, 'active'),
('Manager Level', 75000, 15000, 7500, 3000, 3500, 7500, 1500, 6000, 'active'),
('Executive Level', 100000, 20000, 10000, 5000, 5000, 10000, 2000, 8000, 'active');

-- Migration completion marker
INSERT INTO `tbl_system_migrations` (`migration_name`, `status`, `executed_at`)
VALUES ('hrm_extensions_2026_02_16', 'completed', NOW())
ON DUPLICATE KEY UPDATE `status` = 'completed', `executed_at` = NOW();
```

---

**Document Status**: ✅ Active Reference
**Last Updated**: 2026-02-16
**HR Module Status**: ✅ **CORE MODULES COMPLETED** (6 out of 9 modules)

### ✅ Completed Modules (2026-02-16):
1. **Employee Management** - Full CRUD with branch, designation, role filtering
2. **Salary Template** - Create and manage salary structures
3. **Salary Assignment** - Assign templates to employees with history tracking
4. **Shift Management** - Define shifts with allowances and timings
5. **Attendance Management** - Daily attendance with check-in/out tracking
6. **Payroll Generation** - Auto-calculate salary based on attendance and shifts

### 📋 Pending Modules:
1. **Shift Assignment** - Assign shifts to employees
2. **Attendance Summary** - Monthly attendance reports
3. **Payroll Adjustments** - Bonus, incentives, and corrections
