# Client Tables Column Reference

This document provides a complete reference for the `clients` and `clientsinfo` tables in the Radius Circle application.

## Table: `clients`

The main client/customer table containing authentication and billing information.

### Column List:

| Column Name | Type | Description |
|------------|------|-------------|
| `id` | bigint(20) | Primary key |
| `pop_id` | bigint(20) | Point of Presence ID (Foreign Key) |
| `billing_cycle` | smallint(6) | Billing cycle day |
| `package_id` | bigint(20) | Package ID (Foreign Key) |
| `mac` | varchar(25) | MAC address |
| `userid` | varchar(255) | User ID (Unique) |
| `password` | varchar(255) | Password |
| `ip_address` | varchar(255) | IP address |
| `expire_date` | datetime | Expiration date |
| `required_cable` | decimal(8,2) | Required cable length |
| `created_by` | smallint(6) | User who created the client |
| `client_approval` | enum | Status: pending, approved, canceled |
| `created_at` | timestamp | Creation timestamp |
| `updated_at` | timestamp | Last update timestamp |
| `clients_status` | enum | Status: deactive, active, expired, disable |
| `marketed_by` | bigint(20) | Marketer ID |
| `sub_package_id` | bigint(20) | Sub-package ID (Foreign Key) |
| `customer_id` | varchar(8) | Customer ID |
| `deleted_at` | timestamp | Soft delete timestamp |
| `payment_dadeline` | int(11) | Payment deadline days |
| `payment_token` | varchar(255) | Payment token |
| `app_key_type` | varchar(255) | App key type |
| `parmanent_discount` | double(8,2) | Permanent discount |
| `login_token` | varchar(255) | Login token |
| `ignoreMKCheck` | tinyint(1) | Ignore Mikrotik check |
| `first_approval` | enum | First approval: Pending, Approved |
| `first_approve_by` | bigint(20) | First approver ID |
| `bandwidth_limit` | varchar(255) | Bandwidth limit |
| `apply_bandwidth_limit` | tinyint(1) | Apply bandwidth limit flag |
| `ethernet_port` | varchar(255) | Ethernet port |
| `distribution_point` | enum | Distribution point: pop, noc |
| `connection_type` | enum | Connection type: shared, dedicated |
| `customer_type` | varchar(255) | Customer type |
| `customer_code` | varchar(255) | Customer code |
| `box_id` | bigint(20) | Box ID |
| `is_free` | tinyint(1) | Free client flag |
| `remote_access` | varchar(255) | Remote access |
| `remote_access_username` | varchar(255) | Remote access username |
| `remote_access_password` | varchar(255) | Remote access password |
| `remote_access_port` | varchar(255) | Remote access port |
| `line_man_id` | bigint(20) | Line man ID |
| `client_payment_amount` | decimal(8,2) | Client payment amount |
| `client_id` | bigint(20) | Reference client ID (for referrals) |
| `ipv6` | varchar(255) | IPv6 address |
| `slug` | varchar(255) | URL slug |
| `closed_at` | timestamp | Closed timestamp |
| `up_bandwidth` | varchar(255) | Upload bandwidth |
| `down_bandwidth` | varchar(255) | Download bandwidth |
| `isStatic` | tinyint(1) | Static IP flag |
| `auto_deactive` | tinyint(1) | Auto deactivate flag |
| `crm_status` | varchar(255) | CRM status |
| `crm_check_date` | date | CRM check date |
| `un_recharge_amount` | decimal(8,2) | Unrecharge amount |
| `billing_start_date` | date | Billing start date |
| `experity_check` | varchar(255) | Expiry check |
| `customer_account_balance_reseller` | decimal(8,2) | Customer account balance for reseller |
| `free_day_to_create_customer` | int(11) | Free days to create customer |

---

## Table: `clientsinfo`

Additional client information table containing personal and address details.

### Column List:

| Column Name | Type | Description |
|------------|------|-------------|
| `id` | bigint(20) | Primary key |
| `client_id` | bigint(20) | Client ID (Foreign Key to clients table) |
| `clients_name` | varchar(255) | Client name |
| `father_name` | varchar(255) | Father's name |
| `mother_name` | varchar(255) | Mother's name |
| `flat_no` | varchar(255) | Flat number |
| `building_name` | varchar(255) | Building name |
| `building_no` | varchar(255) | Building number |
| `road_name` | varchar(255) | Road name |
| `road_no` | varchar(255) | Road number |
| `block_sector` | varchar(255) | Block/Sector |
| `area` | varchar(255) | Area |
| `division` | varchar(255) | Division |
| `district` | varchar(255) | District |
| `upazila` | varchar(255) | Upazila |
| `thana` | varchar(255) | Thana/Police Station |
| `contact_no` | varchar(255) | Contact number |
| `other_contact` | varchar(255) | Other contact number |
| `national_id` | varchar(255) | National ID |
| `email` | text | Email address |
| `passport_no` | varchar(255) | Passport number |
| `dob` | varchar(255) | Date of birth |
| `owner_name` | varchar(255) | Owner name |
| `owner_number` | varchar(255) | Owner contact number |
| `olt_pon` | varchar(255) | OLT PON |
| `otc` | decimal(8,2) | One-time charge |
| `otc_due` | decimal(8,2) | OTC due |
| `remarks` | varchar(255) | Remarks |
| `cable_type` | varchar(255) | Cable type |
| `profilePicture` | text | Profile picture path |
| `picture` | varchar(255) | Picture path |
| `customer_profile_picture` | varchar(255) | Customer profile picture |
| `box` | varchar(255) | Box |
| `joinDate` | varchar(255) | Join date |
| `client_latitude` | varchar(255) | Client latitude |
| `client_longitude` | varchar(255) | Client longitude |
| `client_door` | varchar(255) | Client door |
| `cat_type` | varchar(255) | CAT cable type |
| `cat_length` | varchar(255) | CAT cable length |
| `olt_no` | varchar(255) | OLT number |
| `pon_port` | varchar(255) | PON port |
| `onu_serial` | varchar(255) | ONU serial number |
| `from_cable` | int(11) | From cable |
| `to_cable` | int(11) | To cable |
| `from_cable2` | int(11) | From cable 2 |
| `to_cable2` | int(11) | To cable 2 |
| `cable_id` | varchar(15) | Cable ID |
| `customer_agrement` | varchar(255) | Customer agreement document |
| `customer_package_name` | varchar(50) | Customer package name |
| `btrc_package_bandwidth` | varchar(10) | BTRC package bandwidth |
| `source_name` | bigint(20) | Source name/ID |
| `connected_component_type` | varchar(255) | Connected component type (for mapping) |
| `connected_component_id` | bigint(20) | Connected component ID (for mapping) |
| `type` | varchar(255) | Type (default: "client") |
| `onu_router_info` | varchar(255) | ONU/Router information |
| `box_id` | bigint(20) | Box ID |
| `nid_image` | varchar(255) | National ID image |
| `map_box_id` | bigint(20) | Map box ID |
| `created_at` | timestamp | Creation timestamp |
| `updated_at` | timestamp | Last update timestamp |

---

## How to Update Client Information

### Method 1: Using ClientServices (Recommended)

The application uses `ClientServices::clientUpdate()` method to update both tables. Here's how it works:

```php
// In your controller
use App\Services\ClientServices;

// Update client
ClientServices::clientUpdate($request, $client);
```

This method automatically updates:
- **clients table**: All main client fields
- **clientsinfo table**: All additional information fields using `updateOrInsert()`

### Method 2: Direct Model Update

```php
use App\Models\Client;
use App\Models\Clientsinfo;

// Update clients table
$client = Client::find($id);
$client->userid = $request->userid;
$client->password = $request->password;
$client->pop_id = $request->pop_id;
// ... other fields
$client->save();

// Update clientsinfo table
DB::table('clientsinfo')
    ->updateOrInsert(
        ['client_id' => $client->id],
        [
            'clients_name' => $request->client_name,
            'contact_no' => $request->contact_no,
            'email' => $request->email,
            // ... other fields
        ]
    );
```

### Method 3: Using Eloquent Relationships

```php
$client = Client::with('clientsinfo')->find($id);

// Update client
$client->update([
    'userid' => $request->userid,
    'password' => $request->password,
    // ... other fields
]);

// Update clientsinfo
$client->clientsinfo->update([
    'clients_name' => $request->client_name,
    'contact_no' => $request->contact_no,
    // ... other fields
]);
```

---

## Important Notes

1. **No `select_client` table exists** in the database. You might be referring to the `clients` table.

2. **Relationship**: The `clientsinfo` table has a foreign key `client_id` that references the `clients` table's `id`.

3. **Update Location**: The main update logic is in:
   - File: `app/Services/ClientServices.php`
   - Method: `clientUpdate()`
   - Lines: 33-222

4. **Controller**: Client updates are handled in:
   - File: `app/Http/Controllers/ClientController.php`
   - Method: `update()`
   - Lines: 1405-1559

5. **Models**:
   - `app/Models/Client.php` - Main client model
   - `app/Models/Clientsinfo.php` - Client info model

6. **Edit Logging**: All client edits are logged in the `client_edit_logs` table via the `clientEditLog()` method.

---

## Example: Complete Update Request

Here's what a typical update request contains:

```php
// clients table fields
$request->pop_id
$request->userid
$request->password
$request->mac
$request->payment_dadeline
$request->parmanent_discount
$request->bandwidth_limit
$request->customer_id
$request->required_cable
$request->ip_address
$request->marketed_by
$request->box_id
$request->is_free
$request->remote_access
$request->line_man_id
$request->client_payment_amount

// clientsinfo table fields
$request->client_name
$request->father_name
$request->mother_name
$request->flat_no
$request->building_name
$request->building_no
$request->road_name
$request->road_no
$request->block_sector
$request->area
$request->division
$request->district
$request->upazila
$request->thana
$request->contact_no
$request->national_id
$request->email
$request->passport_no
$request->owner_name
$request->owner_contact
$request->olt_pon
$request->remarks
$request->dob
$request->cable_type
$request->other_contact
$request->box
$request->joinDate
$request->client_latitude
$request->client_longitude
$request->cat_type
$request->cat_length
$request->olt_no
$request->pon_port
$request->onu_serial
$request->from_cable
$request->to_cable
$request->cable_id
$request->customer_package_name
$request->btrc_package_bandwidth
$request->source_name
$request->map_box_id
```

---

## Quick Reference SQL

### Select all clients with their info:
```sql
SELECT c.*, ci.* 
FROM clients c 
LEFT JOIN clientsinfo ci ON c.id = ci.client_id 
WHERE c.id = ?
```

### Update client main info:
```sql
UPDATE clients 
SET userid = ?, password = ?, pop_id = ? 
WHERE id = ?
```

### Update client additional info:
```sql
UPDATE clientsinfo 
SET clients_name = ?, contact_no = ?, email = ? 
WHERE client_id = ?
```

---

Generated: 2026-01-29