# Quickstart: SP-02 PostgreSQL Migration

## Overview

This guide provides quick reference commands and file changes needed to complete the PostgreSQL migration and schema implementation.

## Step 1: Modify docker-compose.yml

**File:** `src/docker-compose.yml`

**Changes:**
- Replace `mysql:8.0` service with `postgres:15` service
- Update ports, environment variables, and volume mounts for PostgreSQL
- Remove phpMyAdmin (MySQL-specific) or update for PostgreSQL

**New PostgreSQL service configuration:**
```yaml
db:
  image: postgres:15
  container_name: tamkeen-db
  restart: unless-stopped
  ports:
    - "5432:5432"
  environment:
    POSTGRES_DB: tamkeen_db
    POSTGRES_USER: tamkeen_user
    POSTGRES_PASSWORD: tamkeen_password
  volumes:
    - tamkeen_dbdata:/var/lib/postgresql/data
  networks:
    - tamkeen-network
```

## Step 2: Update .env Configuration

**File:** `src/.env`

**Changes:**
```env
DB_CONNECTION=pgsql
DB_HOST=db
DB_PORT=5432
DB_DATABASE=tamkeen_db
DB_USERNAME=tamkeen_user
DB_PASSWORD=tamkeen_password
```

## Step 3: Create Migration Files

All migrations go in `src/database/migrations/`

### users table
```php
Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name', 150);
    $table->string('phone', 20)->unique();
    $table->text('description')->nullable();
    $table->string('avatar', 255)->nullable();
    $table->timestampTz('created_at')->nullable();
    $table->timestampTz('updated_at')->nullable();
});
$table->index('phone', 'idx_users_phone');
$table->index('name', 'idx_users_name');
```

### contracts table
```php
Schema::create('contracts', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('customer_id')->unsigned();
    $table->bigInteger('agent_id')->unsigned()->nullable();
    $table->string('product_name', 255);
    $table->text('product_description')->nullable();
    $table->decimal('purchase_amount', 10, 2);
    $table->decimal('initial_payment', 10, 2);
    $table->decimal('profit_percentage', 5, 2);
    $table->decimal('total_after_profit', 10, 2);
    $table->smallInteger('months');
    $table->decimal('monthly_installment_amount', 10, 2);
    $table->date('start_date');
    $table->boolean('is_signed')->default(false);
    $table->timestampTz('signed_at')->nullable();
    $table->timestampTz('created_at')->nullable();
    $table->timestampTz('updated_at')->nullable();
    
    $table->foreign('customer_id')->references('id')->on('users')->onDelete('restrict');
    $table->foreign('agent_id')->references('id')->on('users')->onDelete('restrict');
    
    $table->index('customer_id', 'idx_contracts_customer_id');
    $table->index('agent_id', 'idx_contracts_agent_id');
    $table->index('is_signed', 'idx_contracts_is_signed');
});
```

### installments table
```php
Schema::create('installments', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('contract_id')->unsigned();
    $table->smallInteger('installment_number');
    $table->date('due_date');
    $table->decimal('amount', 10, 2);
    $table->string('status', 20)->default('pending');
    $table->decimal('paid_amount', 10, 2)->default(0);
    $table->timestampTz('paid_at')->nullable();
    $table->timestampTz('created_at')->nullable();
    $table->timestampTz('updated_at')->nullable();
    
    $table->foreign('contract_id')->references('id')->on('contracts')->onDelete('cascade');
    
    $table->index(['contract_id', 'due_date', 'status'], 'idx_installments_contract_due_status');
});
```

### payments table
```php
Schema::create('payments', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('contract_id')->unsigned();
    $table->decimal('amount', 10, 2);
    $table->date('payment_date');
    $table->string('type', 30);
    $table->text('notes')->nullable();
    $table->timestampTz('created_at')->nullable();
    $table->timestampTz('updated_at')->nullable();
    
    $table->foreign('contract_id')->references('id')->on('contracts')->onDelete('cascade');
    
    $table->index(['contract_id', 'payment_date'], 'idx_payments_contract_date');
    $table->index('payment_date', 'idx_payments_payment_date');
});
```

### payment_audit_logs table
```php
Schema::create('payment_audit_logs', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('contract_id')->unsigned();
    $table->bigInteger('payment_id')->unsigned()->nullable();
    $table->string('action_type', 20);
    $table->text('narrative_text');
    $table->jsonb('old_values')->nullable();
    $table->jsonb('new_values')->nullable();
    $table->timestampTz('created_at')->nullable();
    
    $table->foreign('contract_id')->references('id')->on('contracts')->onDelete('restrict');
    $table->foreign('payment_id')->references('id')->on('payments')->onDelete('set null');
    
    $table->index('contract_id', 'idx_audit_logs_contract_id');
    $table->index('created_at', 'idx_audit_logs_created_at');
});
```

### agent_shares_logs table
```php
Schema::create('agent_shares_logs', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('user_id')->unsigned();
    $table->integer('shares_count');
    $table->string('transaction_type', 20);
    $table->timestampTz('created_at')->nullable();
    
    $table->foreign('user_id')->references('id')->on('users')->onDelete('restrict');
    
    $table->index('user_id', 'idx_shares_logs_user_id');
});
```

### notification_templates table
```php
Schema::create('notification_templates', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('type', 50)->unique();
    $table->text('body_template');
    $table->timestampTz('created_at')->nullable();
    $table->timestampTz('updated_at')->nullable();
});
```

## Step 4: Modify Existing admins Migration

**File:** `src/database/migrations/2026_06_01_070524_create_admins_table.php`

**Changes:** Replace `$table->timestamps()` with:
```php
$table->timestampTz('created_at')->nullable();
$table->timestampTz('updated_at')->nullable();
```

## Step 5: Configure Spatie for Admin Guard

**File:** `src/config/database.php` (or create `config/permission.php`)

Ensure Spatie config uses 'admin' guard:
```php
'guards' => [
    'admin' => [
        'driver' => 'sanctum',
        'provider' => 'admins',
    ],
],
```

**File:** `src/app/Models/Admin.php`
```php
use Spatie\LaravelModels\Models\HasRoles;

class Admin extends Authenticatable
{
    use HasRoles;
    protected $guard_name = 'admin';
}
```

## Step 6: Run Commands

```bash
# Stop existing containers
docker-compose down

# Rebuild and start with PostgreSQL
docker-compose up -d --build

# Wait for PostgreSQL to be ready
docker-compose exec db pg_isready

# Run migrations
docker-compose exec app php artisan migrate

# Verify
docker-compose exec app php artisan migrate:status
```

## Step 7: Verify Authentication

```bash
# Test login endpoint
curl -X POST http://localhost:8000/api/v1/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email": "admin@tamkeen.local", "password": "password123"}'
```

## Verification Checklist

- [x] PostgreSQL container running (`docker ps`)
- [x] All 8 tables created (`php artisan migrate:status`)
- [x] 13 indexes created (check with `php artisan db:show` or raw query)
- [x] CHECK constraints verified
- [x] FK constraints with correct ON DELETE behavior
- [x] Login endpoint returns Sanctum token

## Verified Commands (2026-06-04)

```bash
# Start containers
docker-compose up -d --build

# Verify PostgreSQL is ready
docker-compose exec db pg_isready -U tamkeen_user -d tamkeen_db

# Run migrations
docker-compose exec app php artisan migrate

# Seed admin
docker-compose exec app php artisan db:seed --class=AdminSeeder

# Test login
Invoke-RestMethod -Method Post -Uri "http://localhost:8000/api/v1/auth/login" -ContentType "application/json" -Body '{"email":"admin@tamkeen.local","password":"password123"}'
```