# Residential Gatherings Import & Linking Guide

## Overview

The `ResidentialGathering` model represents individual residential areas (villages, neighborhoods, etc.) within a center. This allows for more granular tracking of population and families compared to just aggregating at the center level.

The system also provides **many-to-many relationships** between `ResidentialGathering` and:
- **Orders** - Link orders to specific residential gatherings
- **Associations** - Link associations to residential gatherings they serve
- **WellLicense** - Link well licenses to their residential gatherings
- **StationLicense** - Link station licenses to their residential gatherings

## Model Structure

### ResidentialGathering

**Fields:**
- `id` - Primary key
- `name` - Name of the residential gathering (e.g., "قرية الهدار", "حي السلام")
- `center_id` - Foreign key to centers table
- `type` - Type of residential area (e.g., "قرية", "هجرة", "حي")
- `families_count` - Number of families in this residential gathering
- `population` - Total population in this residential gathering
- `created_at` / `updated_at` - Timestamps

**Unique Constraint:** (`name`, `center_id`) - Ensures no duplicate residential gathering names within the same center

**Relationships:**
- `belongsTo(Center)` - Each residential gathering belongs to one center
- `Center->hasMany(ResidentialGathering)` - Each center can have multiple residential gatherings
- `belongsToMany(Order)` - Many-to-many with orders
- `belongsToMany(Association)` - Many-to-many with associations  
- `belongsToMany(WellLicense)` - Many-to-many with well licenses
- `belongsToMany(StationLicense)` - Many-to-many with station licenses

## Linking Command

### Overview

The `residential:link` command automatically links residential gatherings to orders, associations, and licenses based on their `center_id`. For example:
- If an order has `center_id = 1`, all residential gatherings with `center_id = 1` will be linked to that order
- If a station license has `center_id = 5`, all residential gatherings with `center_id = 5` will be linked to that station license

### Basic Usage

```bash
# Dry run (preview links)
php artisan residential:link --dry-run

# Link all models
php artisan residential:link

# Link specific models only
php artisan residential:link --orders
php artisan residential:link --associations
php artisan residential:link --well-licenses
php artisan residential:link --station-licenses
```

### Example Output

```bash
$ php artisan residential:link
📦 Linking Orders to Residential Gatherings...
 138/138 [============================] 100%

🏢 Linking Associations to Residential Gatherings...
 22/22 [============================] 100%

🚰 Linking Well Licenses to Residential Gatherings...
 60/60 [============================] 100%

🏭 Linking Station Licenses to Residential Gatherings...
 4/4 [============================] 100%

══════════════════════════════════════════════════════
          Link Summary
══════════════════════════════════════════════════════
Orders:                        ✓ Linked:  1194 | ⊗ Skipped:     0
Associations:                  ✓ Linked:     0 | ⊗ Skipped:     0
Well Licenses:                 ✓ Linked:    62 | ⊗ Skipped:     0
Station Licenses:              ✓ Linked:     6 | ⊗ Skipped:     0
──────────────────────────────────────────────────────
TOTAL:                         ✓ Linked:  1262 | ⊗ Skipped:     0
══════════════════════════════════════════════════════
```

### Link Logic

The command works as follows:

1. **Orders**: For each order, it finds all centers linked via the `center_order` pivot table, then links all residential gatherings from those centers
2. **Associations**: For each association, it finds all centers linked via the `association_center` pivot table, then links all residential gatherings from those centers
3. **Well Licenses**: For each well license, it uses the `center_id` field to find and link all residential gatherings from that center
4. **Station Licenses**: For each station license, it uses the `center_id` field to find and link all residential gatherings from that center

The command uses `syncWithoutDetaching()` so it won't remove existing links - it's safe to run multiple times.

## Import Command

### Basic Usage

```bash
# Dry run (preview import)
php artisan residential:import --dry-run

# Import data
php artisan residential:import

# Truncate table and reimport
php artisan residential:import --truncate

# Use custom CSV file
php artisan residential:import --file=/path/to/custom.csv
```

### CSV Format

The command expects a CSV file with semicolon (`;`) delimiters:

**Columns:**
1. State Name (المنطقة)
2. City Name (المحافظة)
3. Center Name (المركز)
4. Residential Name (اسم التجمع السكني)
5. Residential Type (نوع التجمع)
6. Population (عدد السكان)
7. Families Count (عدد الأسر)

**Example:**
```csv
المنطقة;المحافظة;المركز;اسم التجمع السكني;نوع التجمع;عدد السكان;عدد الأسر
عسير;خميس مشيط;خميس مشيط;قرية الهدار;قرية;1250;180
```

### Import Logic

1. **CSV Reading**: Reads the CSV file and skips rows where residential name is empty or "-"
2. **Center Matching**: Finds the center by matching state name, city name, and center name
3. **Duplicate Handling**: Skips if a residential gathering with the same name already exists in that center
4. **Error Handling**: Tracks various skip reasons and errors

### Import Statistics

After import, you'll see:
- ✓ **Created**: Number of residential gatherings successfully created
- ⊗ **Skipped (duplicate)**: Already exists (name + center_id)
- ⚠ **Skipped (center not found)**: No matching center found
- ⚠ **Skipped (multiple centers)**: Multiple centers match (duplicates exist)
- ✗ **Errors**: Processing errors

## Examples

### Example 1: First Import

```bash
$ php artisan residential:import
📂 Reading data from: F:\sekaya-laravel\database\seeders/files/centers_data.csv

Found 19226 records in CSV

 19226/19226 [============================] 100%

═══════════════════════════════════════════════════════
          Import Summary
═══════════════════════════════════════════════════════
✓ Created:                        19226
⊗ Skipped (duplicate):            0
⚠ Skipped (center not found):     0
⚠ Skipped (multiple centers):     0
✗ Errors:                          0

Total processed: 19226
═══════════════════════════════════════════════════════
```

### Example 2: Re-import (with duplicates)

```bash
$ php artisan residential:import
📂 Reading data from: F:\sekaya-laravel\database\seeders/files/centers_data.csv

Found 19226 records in CSV

 19226/19226 [============================] 100%

═══════════════════════════════════════════════════════
          Import Summary
═══════════════════════════════════════════════════════
✓ Created:                        0
⊗ Skipped (duplicate):            19226
⚠ Skipped (center not found):     0
⚠ Skipped (multiple centers):     0
✗ Errors:                          0

Total processed: 19226
═══════════════════════════════════════════════════════
```

## Querying Data

### Get all residential gatherings for a center

```php
$center = Center::find(1);
$gatherings = $center->residentialGatherings;

// Or with eager loading
$center = Center::with('residentialGatherings')->find(1);
```

### Get all residential gatherings for an order

```php
$order = Order::with('residentialGatherings')->find(1);
$gatherings = $order->residentialGatherings;

// Get total population covered by this order
$totalPopulation = $order->residentialGatherings()->sum('population');
$totalFamilies = $order->residentialGatherings()->sum('families_count');
```

### Get all residential gatherings for an association

```php
$association = Association::with('residentialGatherings')->find(1);
$gatherings = $association->residentialGatherings;
```

### Get all residential gatherings for a well license

```php
$wellLicense = WellLicense::with('residentialGatherings')->find(1);
$gatherings = $wellLicense->residentialGatherings;
```

### Get all residential gatherings for a station license

```php
$stationLicense = StationLicense::with('residentialGatherings')->find(1);
$gatherings = $stationLicense->residentialGatherings;
```

### Get total population by center

```php
$center = Center::find(1);
$totalPopulation = $center->residentialGatherings()->sum('population');
$totalFamilies = $center->residentialGatherings()->sum('families_count');
```

### Find residential gatherings by type

```php
$villages = ResidentialGathering::where('type', 'قرية')->get();
$neighborhoods = ResidentialGathering::where('type', 'حي')->get();
```

### Get centers with their gatherings

```php
$centers = Center::with(['residentialGatherings', 'city.state'])->get();

foreach ($centers as $center) {
    echo "{$center->name} has {$center->residentialGatherings->count()} gatherings\n";
    echo "Total population: {$center->residentialGatherings->sum('population')}\n";
}
```

## Database Queries

### Check total records

```sql
SELECT COUNT(*) FROM residential_gatherings;
```

### Get statistics by type

```sql
SELECT 
    type,
    COUNT(*) as count,
    SUM(population) as total_population,
    SUM(families_count) as total_families
FROM residential_gatherings
WHERE type IS NOT NULL
GROUP BY type
ORDER BY count DESC;
```

### Find centers with most gatherings

```sql
SELECT 
    c.name as center_name,
    ci.name as city_name,
    s.name as state_name,
    COUNT(rg.id) as gatherings_count,
    SUM(rg.population) as total_population
FROM centers c
JOIN cities ci ON c.city_id = ci.id
JOIN states s ON ci.state_id = s.id
LEFT JOIN residential_gatherings rg ON c.id = rg.center_id
GROUP BY c.id, c.name, ci.name, s.name
ORDER BY gatherings_count DESC
LIMIT 10;
```

## Troubleshooting

### Issue: "Skipped (multiple centers)" warnings

**Cause:** There are duplicate centers in the database with the same name, city, and state.

**Solution:** Run the merge duplicates command first:
```bash
php artisan location:merge-duplicate-centers --auto
```

### Issue: "Skipped (center not found)" warnings

**Cause:** The CSV has center names that don't match exactly with database records.

**Solution:** 
1. Check the center names in the CSV vs database
2. Update the center names to match
3. Or update the CSV data

### Issue: Duplicate key violation

**Cause:** Trying to insert a residential gathering that already exists.

**Solution:** The command automatically skips duplicates, but if you see this error, ensure you're not manually inserting data.

## Notes

- The import command is idempotent - safe to run multiple times
- Use `--dry-run` to preview changes before applying
- The unique constraint ensures data integrity
- Empty or "-" residential names are automatically skipped
- All inserts are atomic (either succeed or fail completely)
