# Database Schema

## Tables

### 1. roles
Stores user roles in the system.

**Columns:**
- `role_id` (PK, Auto Increment)
- `role_code` (Unique) - Code identifier (super_admin, hr_admin, manager, employee)
- `role_name` - Display name
- `role_description` - Description of the role
- `is_system_role` - Whether this is a system role (cannot be deleted)
- `created_at` - Creation timestamp
- `updated_at` - Last update timestamp

**Default Roles:**
- `super_admin` - Super Administrator (Full access)
- `hr_admin` - HR Administrator (HR management)
- `manager` - Manager (Department management)
- `employee` - Employee (Self-service)

### 2. users
Stores user accounts.

**Columns:**
- `user_id` (PK, Auto Increment)
- `username` (Unique) - Login username
- `email` (Unique) - Email address
- `password_hash` - Bcrypt hashed password
- `full_name` - Full name of user
- `phone` - Phone number (optional)
- `role_id` (FK) - Reference to roles table
- `user_status` - active, inactive, or locked
- `last_login` - Last login timestamp
- `failed_login_attempts` - Count of failed login attempts
- `locked_until` - Account lock expiry time
- `created_at` - Creation timestamp
- `updated_at` - Last update timestamp

### 3. sites
Stores sites/companies (for multi-site support).

**Columns:**
- `site_id` (PK, Auto Increment)
- `site_code` (Unique) - Site code identifier
- `site_name` - Site/Company name
- `site_address` - Address (optional)
- `site_phone` - Phone number (optional)
- `site_email` - Email address (optional)
- `site_status` - active or inactive
- `created_at` - Creation timestamp
- `updated_at` - Last update timestamp

### 4. user_sites
Many-to-many relationship between users and sites.

**Columns:**
- `user_site_id` (PK, Auto Increment)
- `user_id` (FK) - Reference to users table
- `site_id` (FK) - Reference to sites table
- `is_primary` - Whether this is the user's primary site
- `assigned_at` - Assignment timestamp
- `assigned_by` (FK) - User who assigned this relationship

**Unique Constraint:** One user can only be assigned to a site once (user_id + site_id combination is unique)

## Installation

1. Create database:
   ```sql
   CREATE DATABASE hrm2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   ```

2. Import schema:
   ```bash
   mysql -u root -p hrm2 < database/schema.sql
   ```
   Or use phpMyAdmin to import the file.

## Usage Examples

### Get user with role and sites
```sql
SELECT 
    u.user_id,
    u.username,
    u.full_name,
    r.role_name,
    GROUP_CONCAT(s.site_name) as sites
FROM users u
INNER JOIN roles r ON u.role_id = r.role_id
LEFT JOIN user_sites us ON u.user_id = us.user_id
LEFT JOIN sites s ON us.site_id = s.site_id
WHERE u.user_id = 1
GROUP BY u.user_id;
```

### Assign user to site
```sql
INSERT INTO user_sites (user_id, site_id, is_primary, assigned_by)
VALUES (1, 1, 1, 1);
```

### Get user's primary site
```sql
SELECT s.*
FROM sites s
INNER JOIN user_sites us ON s.site_id = us.site_id
WHERE us.user_id = 1 AND us.is_primary = 1;
```

