# Merge Duplicate Locations Guide

This guide explains how to safely merge duplicate records in the `states`, `cities`, and `centers` tables before adding unique constraints.

## Overview

Before adding unique constraints to ensure data integrity, we need to merge any duplicate records that exist in production. The system provides commands to:

1. **Check for duplicates** without making changes
2. **Merge duplicates** by updating foreign key references and deleting duplicate records
3. **Preserve data integrity** by moving all relationships to the primary record

## Commands

### 1. Check for Duplicates

```bash
# Check all location tables for duplicates
php artisan location:check-duplicates

# Show detailed information in table format
php artisan location:check-duplicates --show-details
```

**What it checks:**
- **States**: Duplicate names
- **Cities**: Duplicate combinations of (name, state_id)
- **Centers**: Duplicate combinations of (name, city_id)

**Output:**
- Returns exit code `0` if no duplicates found
- Returns exit code `1` if duplicates found
- Displays count and IDs of duplicate records

---

### 2. Merge Duplicate Centers

```bash
# Dry run (preview what would happen)
php artisan location:merge-duplicate-centers --dry-run

# Dry run with automatic selection (chooses lowest ID)
php artisan location:merge-duplicate-centers --dry-run --auto

# Actually merge duplicates (with prompts)
php artisan location:merge-duplicate-centers

# Merge with automatic selection (no prompts)
php artisan location:merge-duplicate-centers --auto
```

**What it does:**
1. Finds all duplicate centers (same name + city_id)
2. For each group:
   - Selects a primary center (manually or automatically)
   - Updates foreign key references in:
     - `center_order` (pivot table with orders)
     - `association_center` (pivot table with associations)
     - `well_licenses.center_id`
     - `station_licenses.center_id`
   - Deletes duplicate centers
3. Handles pivot table uniqueness constraints intelligently

**Interactive Mode:**
- Shows details for each center (families count, orders, associations, created date)
- Asks which center to keep as primary
- Default selection is the center with lowest ID

---

### 3. Merge All Duplicate Locations

```bash
# Dry run for all location tables
php artisan location:merge-all-duplicates --dry-run --auto

# Merge only states
php artisan location:merge-all-duplicates --auto --states

# Merge only cities
php artisan location:merge-all-duplicates --auto --cities

# Merge only centers
php artisan location:merge-all-duplicates --auto --centers

# Merge everything (with prompts for each)
php artisan location:merge-all-duplicates

# Merge everything automatically
php artisan location:merge-all-duplicates --auto
```

**What it does:**
Merges duplicates in all three tables, processing in the correct order (states → cities → centers) to respect foreign key dependencies.

**States merging updates:**
- `cities.state_id`
- `orders.state_id`
- `association_state` pivot table
- `well_licenses.state_id`
- `station_licenses.state_id`

**Cities merging updates:**
- `centers.city_id`
- `orders.city_id`
- `association_city` pivot table
- `well_licenses.city_id`
- `station_licenses.city_id`

**Centers merging:**
- Delegates to `location:merge-duplicate-centers` command

---

## Recommended Workflow

### Before Production Deployment

1. **Check for duplicates in production:**
   ```bash
   php artisan location:check-duplicates --show-details
   ```

2. **If duplicates exist, perform a dry run:**
   ```bash
   php artisan location:merge-all-duplicates --dry-run --auto
   ```

3. **Review the output carefully:**
   - Check which records would be merged
   - Verify the foreign key reference counts
   - Ensure the "primary" selection makes sense

4. **Create a database backup:**
   ```bash
   # Your backup command here
   pg_dump -U username -d database_name > backup_before_merge.sql
   ```

5. **Execute the merge:**
   ```bash
   php artisan location:merge-all-duplicates --auto
   ```

6. **Verify the results:**
   ```bash
   # Check no duplicates remain
   php artisan location:check-duplicates
   
   # Verify foreign key integrity
   php artisan tinker
   >>> App\Models\Center::with(['orders', 'associations'])->whereHas('orders')->count()
   ```

7. **Add unique constraints:**
   - Create and run the migration to add unique constraints
   - See `ADD_UNIQUE_CONSTRAINTS.md` for details

---

## Safety Features

### Transaction Safety
All merge operations run inside database transactions. If any error occurs, all changes are rolled back automatically.

### Pivot Table Handling
The commands intelligently handle many-to-many pivot tables:
- Updates records that won't violate unique constraints
- Deletes duplicate pivot records where the relationship already exists with the primary record

### Dry Run Mode
Always test with `--dry-run` first:
- Shows exactly what would happen
- Makes no database changes
- Safe to run in production

### Automatic vs Interactive
- **`--auto`**: Automatically selects the record with the lowest ID as primary
- **Without `--auto`**: Prompts for each duplicate group, showing relevant details

---

## Examples

### Example 1: Production has duplicate centers

```bash
$ php artisan location:check-duplicates
🔍 Checking for duplicate records in location tables...

📍 Checking States (unique on: name)
   ✓ No duplicate states found

🏙️  Checking Cities (unique on: name + state_id)
   ✓ No duplicate cities found

🏢 Checking Centers (unique on: name + city_id)
   ✗ Found 45 duplicate center combination(s)
     - "مركز الأمل" in city "الرياض" appears 3 times (IDs: 15, 89, 234)
     - "المركز الصحي" in city "جدة" appears 2 times (IDs: 42, 156)
     ...
```

### Example 2: Merging with details

```bash
$ php artisan location:merge-duplicate-centers --dry-run

🔍 Searching for duplicate centers...

Found 45 groups of duplicate centers.

🔸 DRY RUN MODE - No changes will be made

─────────────────────────────────────────────────────────────
Processing: "مركز الأمل"
City: الرياض (State: منطقة الرياض)
Found 3 duplicates with IDs: 15, 89, 234

┌──────┬────────────────┬────────┬──────────────┬─────────────────────┐
│ ID   │ Families Count │ Orders │ Associations │ Created At          │
├──────┼────────────────┼────────┼──────────────┼─────────────────────┤
│ 15   │ 120            │ 5      │ 2            │ 2024-01-15 10:30:00 │
│ 89   │ 0              │ 0      │ 0            │ 2024-03-20 14:45:00 │
│ 234  │ 85             │ 3      │ 1            │ 2024-06-10 09:15:00 │
└──────┴────────────────┴────────┴──────────────┴─────────────────────┘

Which center ID should be kept as primary? [15]: 15

✓ Primary center: ID 15
  Merging duplicates: 89, 234

  Foreign key references to update: 8
    - center_order: 3
    - association_center: 1
    - well_licenses: 2
    - station_licenses: 2

  [DRY RUN] Would update 8 references and delete 2 duplicate center(s)
```

---

## Troubleshooting

### Issue: Command fails with foreign key constraint error

**Cause:** The command tries to update/delete records but foreign key constraints prevent it.

**Solution:** This shouldn't happen as the command properly updates references first. If it does:
1. Check the error message for the specific constraint
2. Manually verify the foreign key relationships
3. Report the issue for investigation

### Issue: "Primary center not found" error

**Cause:** Selected primary ID doesn't exist in the duplicate group.

**Solution:** When prompted, enter one of the IDs shown in the duplicate list.

### Issue: Pivot table unique constraint violation

**Cause:** Trying to create a relationship that already exists.

**Solution:** The command handles this by:
1. First updating non-conflicting records
2. Then deleting conflicting duplicates
3. The primary record keeps its existing relationships

---

## After Merging

Once all duplicates are merged, you can safely add unique constraints:

1. Run the check command one more time:
   ```bash
   php artisan location:check-duplicates
   ```

2. Create a migration for unique constraints:
   ```bash
   php artisan make:migration add_unique_constraints_to_location_tables
   ```

3. Add the constraints:
   ```php
   // States: unique on name
   $table->unique('name');
   
   // Cities: unique on (name, state_id)
   $table->unique(['name', 'state_id']);
   
   // Centers: unique on (name, city_id)
   $table->unique(['name', 'city_id']);
   ```

4. Run the migration:
   ```bash
   php artisan migrate
   ```

---

## Notes

- The `--auto` flag is recommended for production with many duplicates
- Always backup your database before running merge commands
- The commands log all operations for audit purposes
- Dry run mode is safe to use in production
- The merge process preserves all data - nothing is lost, only consolidated
