# Residential Gatherings - Quick Reference

## Commands Summary

### Import Residential Gatherings
```bash
# Dry run
php artisan residential:import --dry-run

# Import data
php artisan residential:import

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

### Link Residential Gatherings to Models
```bash
# Dry run (preview)
php artisan residential:link --dry-run

# Link all
php artisan residential:link

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

## Relationships

### Center ↔ ResidentialGathering
- **Type**: One-to-Many
- **Usage**: `$center->residentialGatherings`

### Order ↔ ResidentialGathering
- **Type**: Many-to-Many
- **Pivot Table**: `order_residential_gathering`
- **Usage**: `$order->residentialGatherings`
- **Logic**: Order links to centers via `center_order`, then to all RGs in those centers

### Association ↔ ResidentialGathering
- **Type**: Many-to-Many
- **Pivot Table**: `association_residential_gathering`
- **Usage**: `$association->residentialGatherings`
- **Logic**: Association links to centers via `association_center`, then to all RGs in those centers

### WellLicense ↔ ResidentialGathering
- **Type**: Many-to-Many
- **Pivot Table**: `residential_gathering_well_license`
- **Usage**: `$wellLicense->residentialGatherings`
- **Logic**: Well license has `center_id`, links to all RGs in that center

### StationLicense ↔ ResidentialGathering
- **Type**: Many-to-Many
- **Pivot Table**: `residential_gathering_station_license`
- **Usage**: `$stationLicense->residentialGatherings`
- **Logic**: Station license has `center_id`, links to all RGs in that center

## Common Queries

### Get Total Population for an Order
```php
$order = Order::find(1);
$totalPopulation = $order->residentialGatherings()->sum('population');
$totalFamilies = $order->residentialGatherings()->sum('families_count');
```

### Get Total Population for an Association
```php
$association = Association::find(1);
$totalPopulation = $association->residentialGatherings()->sum('population');
$totalFamilies = $association->residentialGatherings()->sum('families_count');
```

### Get All RGs Served by a Well License
```php
$wellLicense = WellLicense::with('residentialGatherings')->find(1);
foreach ($wellLicense->residentialGatherings as $rg) {
    echo "{$rg->name}: {$rg->population} people\n";
}
```

### Get All RGs in a Center
```php
$center = Center::with('residentialGatherings')->find(1);
foreach ($center->residentialGatherings as $rg) {
    echo "{$rg->name} ({$rg->type}): {$rg->families_count} families\n";
}
```

## Database Tables

### residential_gatherings
- `id` - Primary key
- `name` - Name of residential gathering
- `center_id` - Foreign key to centers
- `type` - Type (قرية, حي, هجرة, etc.)
- `families_count` - Number of families
- `population` - Total population
- `created_at` / `updated_at`
- **Unique constraint**: (`name`, `center_id`)

### order_residential_gathering
- `id` - Primary key
- `order_id` - Foreign key to orders
- `residential_gathering_id` - Foreign key to residential_gatherings
- `created_at` / `updated_at`
- **Unique constraint**: (`order_id`, `residential_gathering_id`)

### association_residential_gathering
- `id` - Primary key
- `association_id` - Foreign key to associations
- `residential_gathering_id` - Foreign key to residential_gatherings
- `created_at` / `updated_at`
- **Unique constraint**: (`association_id`, `residential_gathering_id`)

### residential_gathering_well_license
- `id` - Primary key
- `well_license_id` - Foreign key to well_licenses
- `residential_gathering_id` - Foreign key to residential_gatherings
- `created_at` / `updated_at`
- **Unique constraint**: (`well_license_id`, `residential_gathering_id`)

### residential_gathering_station_license
- `id` - Primary key
- `station_license_id` - Foreign key to station_licenses
- `residential_gathering_id` - Foreign key to residential_gatherings
- `created_at` / `updated_at`
- **Unique constraint**: (`station_license_id`, `residential_gathering_id`)

## Workflow

### Initial Setup
1. Import residential gatherings: `php artisan residential:import`
2. Link to existing models: `php artisan residential:link`

### When Adding New Orders/Licenses
- New orders/licenses will need to be linked manually OR
- Re-run `php artisan residential:link` (safe to run multiple times)

### When Adding New Residential Gatherings
- Import new data: `php artisan residential:import`
- Re-link: `php artisan residential:link` (will only add new links)

## Statistics Queries

### Total RGs in System
```sql
SELECT COUNT(*) FROM residential_gatherings;
```

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

### Orders by RG Count
```sql
SELECT o.id, COUNT(org.residential_gathering_id) as rg_count
FROM orders o
LEFT JOIN order_residential_gathering org ON o.id = org.order_id
GROUP BY o.id
ORDER BY rg_count DESC
LIMIT 10;
```

### Associations Coverage
```sql
SELECT a.name, COUNT(arg.residential_gathering_id) as rg_count,
       SUM(rg.population) as total_population
FROM associations a
LEFT JOIN association_residential_gathering arg ON a.id = arg.association_id
LEFT JOIN residential_gatherings rg ON arg.residential_gathering_id = rg.id
GROUP BY a.id, a.name
ORDER BY rg_count DESC;
```

See `RESIDENTIAL_GATHERINGS_IMPORT.md` for detailed documentation.
