# Database Fixes Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Fix four database-level issues: promo-code usage tracking, missing unique constraints, role/permission seeder, and redundant indexes.

**Architecture:** Add a `promo_code_redemptions` polymorphic pivot table to replace the single `used_at` timestamp; enforce uniqueness at the schema level; align `RolesAndPermissionsSeeder` with the catalog-style permissions used by policies; and drop redundant indexes via a new migration.

**Tech Stack:** Laravel 13, PHP 8.4, MySQL/SQLite, Spatie Permission, Pest.

---

## File Map

| File | Responsibility |
|------|----------------|
| `database/migrations/2026_07_12_190501_create_promo_code_redemptions_table.php` | New redemption records table |
| `database/migrations/2026_07_12_190502_add_unique_constraints_to_pivot_and_documents.php` | Unique constraints for pivot tables and document numbers |
| `database/migrations/2026_07_12_190503_drop_redundant_user_indexes.php` | Remove redundant `users.email`, `users.phone`, and id-leading composite indexes |
| `app/Models/PromoCodeRedemption.php` | Eloquent model for redemptions |
| `app/Models/PromoCode.php` | Remove `used_at` cast, add `redemptions()` relation |
| `app/Services/PromoCodeService.php` | Update limit checks to use redemptions |
| `app/Services/ReservationService.php` | Create/delete redemptions when applying/releasing promo codes |
| `app/Http/Resources/PromoCodeResource.php` | Remove `used_at` from response |
| `database/factories/PromoCodeFactory.php` | Remove `used_at` default |
| `database/seeders/RolesAndPermissionsSeeder.php` | Assign catalog permissions to roles |
| `tests/Unit/Authorization/RolesAndPermissionsTest.php` | Update assertions for catalog permissions |

---

## Task 1: Promo-Code Redemptions

- [ ] **Step 1: Create migration for `promo_code_redemptions`**

Create `database/migrations/2026_07_12_190501_create_promo_code_redemptions_table.php`:

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('promo_code_redemptions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('promo_code_id')->constrained('promo_codes')->cascadeOnDelete();
            $table->morphs('customer');
            $table->foreignId('reservation_id')->nullable()->constrained('reservations')->cascadeOnDelete();
            $table->timestamps();

            $table->index(['promo_code_id', 'customer_type', 'customer_id'], 'promo_redemptions_customer_index');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('promo_code_redemptions');
    }
};
```

- [ ] **Step 2: Drop `used_at` and update promo-code indexes**

Create `database/migrations/2026_07_12_190502_update_promo_codes_drop_used_at.php`:

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('promo_codes', function (Blueprint $table) {
            $table->dropIndex('promo_codes_dates_index');
        });

        Schema::table('promo_codes', function (Blueprint $table) {
            $table->dropColumn('used_at');
            $table->index(['starts_at', 'expires_at'], 'promo_codes_dates_index');
        });
    }

    public function down(): void
    {
        Schema::table('promo_codes', function (Blueprint $table) {
            $table->dropIndex('promo_codes_dates_index');
        });

        Schema::table('promo_codes', function (Blueprint $table) {
            $table->dateTime('used_at')->nullable()->after('expires_at');
            $table->index(['starts_at', 'expires_at', 'used_at'], 'promo_codes_dates_index');
        });
    }
};
```

- [ ] **Step 3: Create `PromoCodeRedemption` model**

Create `app/Models/PromoCodeRedemption.php`:

```php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\MorphTo;

class PromoCodeRedemption extends Model
{
    use HasFactory;

    protected $guarded = ['id'];

    public function promoCode(): BelongsTo
    {
        return $this->belongsTo(PromoCode::class);
    }

    public function customer(): MorphTo
    {
        return $this->morphTo();
    }

    public function reservation(): BelongsTo
    {
        return $this->belongsTo(Reservation::class);
    }
}
```

- [ ] **Step 4: Update `PromoCode` model**

Modify `app/Models/PromoCode.php`:
- Remove `'used_at' => 'datetime'` from casts.
- Add `redemptions()` relation.

```php
use Illuminate\Database\Eloquent\Relations\HasMany;

public function redemptions(): HasMany
{
    return $this->hasMany(PromoCodeRedemption::class);
}
```

- [ ] **Step 5: Update `PromoCodeService`**

Modify `app/Services/PromoCodeService.php`:
- `findValidForUnit`: remove `used_at` clause; usage-limit check becomes `($promoCode->uses_count + $activeHolds) >= $promoCode->usage_limit` (unchanged logic, but `uses_count` is now maintained via redemptions).
- `canUseForCustomer`: count redemptions for this customer plus active pending reservations.

```php
public function canUseForCustomer(PromoCode $promoCode, Customer|PendingCustomer $customer): bool
{
    if ($promoCode->per_customer_limit === null) {
        return true;
    }

    $confirmedUses = $promoCode->redemptions()
        ->whereMorphedTo('customer', $customer)
        ->count();

    $activeHolds = PendingReservation::query()
        ->where('promo_code_id', $promoCode->id)
        ->whereMorphedTo('customer', $customer)
        ->where('status', 'pending')
        ->where('expires_at', '>', Carbon::now())
        ->count();

    return ($confirmedUses + $activeHolds) < $promoCode->per_customer_limit;
}
```

- [ ] **Step 6: Update `ReservationService` apply/release promo code logic**

Modify `app/Services/ReservationService.php`:
- Change `applyPromoCode` signature to require the reservation and create a redemption.
- Change `releasePromoCode` signature to require the reservation and delete the redemption.

```php
private function applyPromoCode(PromoCode $promoCode, Customer|PendingCustomer $customer, Reservation $reservation): void
{
    if (! PromoCodeService::canUseForCustomer($promoCode, $customer)) {
        throw ValidationException::withMessages([
            'promo_code' => ['You have already used this promo code the maximum number of times.'],
        ]);
    }

    $promoCode = PromoCode::where('id', $promoCode->id)->lockForUpdate()->firstOrFail();

    if ($promoCode->usage_limit !== null && $promoCode->uses_count >= $promoCode->usage_limit) {
        throw ValidationException::withMessages([
            'promo_code' => ['This promo code has reached its usage limit.'],
        ]);
    }

    $promoCode->redemptions()->create([
        'customer_type' => $customer::class,
        'customer_id' => $customer->id,
        'reservation_id' => $reservation->id,
    ]);

    $promoCode->uses_count++;
    $promoCode->save();
}

private function releasePromoCode(PromoCode $promoCode, Reservation $reservation): void
{
    $promoCode = PromoCode::where('id', $promoCode->id)->lockForUpdate()->firstOrFail();

    $promoCode->redemptions()
        ->where('reservation_id', $reservation->id)
        ->delete();

    $promoCode->uses_count = max(0, $promoCode->uses_count - 1);
    $promoCode->save();
}
```

Update callers:
- `persistReservation`: `$this->applyPromoCode($promoCode, $customer, $reservation);`
- `persistFromPendingReservation`: `$this->applyPromoCode($promoCode, $customer, $reservation);`
- `updateReservation`: `$this->applyPromoCode($newPromoCode, $reservation->customer, $reservation);` and `$this->releasePromoCode(PromoCode::findOrFail($oldPromoCodeId), $reservation);`
- `cancelReservation`: `$this->releasePromoCode(PromoCode::findOrFail($reservation->promo_code_id), $reservation);`

- [ ] **Step 7: Update `PromoCodeResource` and factory**

Remove `used_at` from `app/Http/Resources/PromoCodeResource.php` and from `database/factories/PromoCodeFactory.php`.

- [ ] **Step 8: Run promo-code related tests**

Run: `vendor/bin/pest tests/Feature/PromoCodeGenerationTest.php`
Expected: PASS

---

## Task 2: Unique Constraints

- [ ] **Step 1: Create unique-constraints migration**

Create `database/migrations/2026_07_12_190503_add_unique_constraints_to_pivot_and_documents.php`:

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('unit_facilities', function (Blueprint $table) {
            $table->unique(['unit_id', 'facility_id'], 'unit_facilities_unique');
        });

        Schema::table('building_facilities', function (Blueprint $table) {
            $table->unique(['building_id', 'facility_id'], 'building_facilities_unique');
        });

        Schema::table('invoices', function (Blueprint $table) {
            $table->unique('document_number', 'invoices_document_number_unique');
        });

        Schema::table('receipts', function (Blueprint $table) {
            $table->unique('document_number', 'receipts_document_number_unique');
        });
    }

    public function down(): void
    {
        Schema::table('unit_facilities', function (Blueprint $table) {
            $table->dropUnique('unit_facilities_unique');
        });

        Schema::table('building_facilities', function (Blueprint $table) {
            $table->dropUnique('building_facilities_unique');
        });

        Schema::table('invoices', function (Blueprint $table) {
            $table->dropUnique('invoices_document_number_unique');
        });

        Schema::table('receipts', function (Blueprint $table) {
            $table->dropUnique('receipts_document_number_unique');
        });
    }
};
```

- [ ] **Step 2: Run facility-related tests**

Run: `vendor/bin/pest tests/Feature/FacilityTest.php`
Expected: PASS

---

## Task 3: RolesAndPermissionsSeeder

- [ ] **Step 1: Rewrite seeder to assign catalog permissions**

Modify `database/seeders/RolesAndPermissionsSeeder.php`:

```php
<?php

namespace Database\Seeders;

use App\Support\PermissionCatalog;
use Illuminate\Database\Seeder;
use Spatie\Permission\Models\Permission;
use Spatie\Permission\Models\Role;
use Spatie\Permission\PermissionRegistrar;

class RolesAndPermissionsSeeder extends Seeder
{
    public function run(): void
    {
        app()[PermissionRegistrar::class]->forgetCachedPermissions();

        $guard = 'api';

        foreach (PermissionCatalog::all() as $name) {
            Permission::firstOrCreate(['name' => $name, 'guard_name' => $guard]);
        }

        $specialPermissions = ['manage_platform', 'approve_buildings', 'apply_promo_codes'];
        foreach ($specialPermissions as $name) {
            Permission::firstOrCreate(['name' => $name, 'guard_name' => $guard]);
        }

        $superAdmin = Role::firstOrCreate(['name' => 'super_admin', 'guard_name' => $guard]);
        $superAdmin->syncPermissions(Permission::all());

        $admin = Role::firstOrCreate(['name' => 'admin', 'guard_name' => $guard]);
        $admin->syncPermissions([
            'manage_platform',
            'approve_buildings',
            'buildings.view',
            'buildings.update',
            'customers.view',
            'customers.create',
            'customers.update',
            'customers.delete',
            'reservations.view',
            'revenue.view',
        ]);

        $owner = Role::firstOrCreate(['name' => 'owner', 'guard_name' => $guard]);
        $owner->syncPermissions([
            'buildings.view',
            'buildings.create',
            'buildings.update',
            'buildings.delete',
            'units.view',
            'units.create',
            'units.update',
            'units.delete',
            'employees.view',
            'employees.create',
            'employees.update',
            'employees.delete',
            'reservations.view',
            'reservations.create',
            'reservations.update',
            'promo_codes.view',
            'promo_codes.create',
            'promo_codes.update',
            'promo_codes.delete',
            'revenue.view',
        ]);

        $employee = Role::firstOrCreate(['name' => 'employee', 'guard_name' => $guard]);
        $employee->syncPermissions([]);

        $customer = Role::firstOrCreate(['name' => 'customer', 'guard_name' => $guard]);
        $customer->syncPermissions([
            'reservations.view',
            'promo_codes.view',
            'apply_promo_codes',
        ]);
    }
}
```

- [ ] **Step 2: Update `RolesAndPermissionsTest`**

Modify `tests/Unit/Authorization/RolesAndPermissionsTest.php`:
- Expect 31 permissions (28 catalog + 3 special).
- Check owner has catalog permissions (`buildings.create`, `units.update`, `employees.create`) instead of flat `manage_employees`/`manage_buildings`.
- Check customer has `reservations.view`, `promo_codes.view`, `apply_promo_codes`.
- Keep employee at 0 permissions.

- [ ] **Step 3: Run authorization tests**

Run: `vendor/bin/pest tests/Unit/Authorization/`
Expected: PASS

---

## Task 4: Redundant Indexes

- [ ] **Step 1: Create migration to drop redundant indexes**

Create `database/migrations/2026_07_12_190504_drop_redundant_user_indexes.php`:

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropIndex(['email']);
            $table->dropIndex(['phone']);
        });

        Schema::table('employees', function (Blueprint $table) {
            $table->dropIndex(['id', 'status']);
        });

        Schema::table('customers', function (Blueprint $table) {
            $table->dropIndex(['id', 'wallet']);
        });
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->index('email');
            $table->index('phone');
        });

        Schema::table('employees', function (Blueprint $table) {
            $table->index(['id', 'status']);
        });

        Schema::table('customers', function (Blueprint $table) {
            $table->index(['id', 'wallet']);
        });
    }
};
```

- [ ] **Step 2: Verify migrations run cleanly**

Run: `php artisan migrate --force`
Expected: SUCCESS

---

## Task 5: Full Verification

- [ ] **Step 1: Run full test suite**

Run: `vendor/bin/pest`
Expected: all tests PASS

- [ ] **Step 2: Run static analysis**

Run: `vendor/bin/phpstan analyse --memory-limit=2G`
Expected: no errors

- [ ] **Step 3: Run code style**

Run: `vendor/bin/pint --test`
Expected: PASS

---

## Self-Review

- **Spec coverage:** All four reported issues map to tasks above.
- **Placeholders:** No placeholders remain.
- **Type consistency:** `applyPromoCode` and `releasePromoCode` both receive a `Reservation` instance; `PromoCodeRedemption` uses polymorphic `customer` matching `Reservation`/`PendingReservation` patterns.