<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateVolunteerTables extends Migration
{
    public function up()
    {
        // 1. Table: volunteers
        $this->forge->addField([
            'id'          => ['type' => 'INT', 'constraint' => 11, 'auto_increment' => true],
            'name'        => ['type' => 'VARCHAR', 'constraint' => 100],
            'phone'       => ['type' => 'VARCHAR', 'constraint' => 20],
            'age'         => ['type' => 'INT', 'constraint' => 3],
            'skills'      => ['type' => 'TEXT', 'null' => true],
            'created_at'  => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('volunteers');

        // 2. Table: volunteer_live_status
        // Note: POINT is added via raw query because Forge doesn't support spatial types natively
        $this->db->query("CREATE TABLE volunteer_live_status (
            volunteer_id INT PRIMARY KEY,
            location POINT NOT NULL,
            is_available TINYINT(1) DEFAULT 1,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )");
        
        // Add spatial index for speed
        $this->db->query("ALTER TABLE volunteer_live_status ADD SPATIAL INDEX(location)");
    }

    public function down()
    {
        $this->forge->dropTable('volunteer_live_status');
        $this->forge->dropTable('volunteers');
    }
}