<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class InitialSchema extends Migration
{
    public function up()
    {
        // Users Table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'email' => ['type' => 'VARCHAR', 'constraint' => 255, 'unique' => true, 'null' => true],
            'phone' => ['type' => 'VARCHAR', 'constraint' => 20, 'unique' => true],
            'password_hash' => ['type' => 'VARCHAR', 'constraint' => 255],
            'role' => ['type' => 'ENUM', 'constraint' => ['customer', 'worker', 'admin'], 'default' => 'customer'],
            'is_verified' => ['type' => 'BOOLEAN', 'default' => false],
            'fcm_token' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'created_at datetime default current_timestamp',
            'updated_at datetime default current_timestamp on update current_timestamp',
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('users');

        // Worker Profiles Table
        $this->forge->addField([
            'user_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'bio' => ['type' => 'TEXT', 'null' => true],
            'skills' => ['type' => 'JSON', 'null' => true],
            'hourly_rate' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'null' => true],
            'service_radius_km' => ['type' => 'INT', 'default' => 10],
            'latitude' => ['type' => 'DECIMAL', 'constraint' => '10,8', 'null' => true],
            'longitude' => ['type' => 'DECIMAL', 'constraint' => '11,8', 'null' => true],
            'is_online' => ['type' => 'BOOLEAN', 'default' => false],
            'kyc_status' => ['type' => 'ENUM', 'constraint' => ['pending', 'approved', 'rejected'], 'default' => 'pending'],
            'kyc_documents' => ['type' => 'JSON', 'null' => true],
        ]);
        $this->forge->addKey('user_id', true);
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('worker_profiles');

        // Categories Table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'slug' => ['type' => 'VARCHAR', 'constraint' => 100, 'unique' => true],
            'image_url' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'is_active' => ['type' => 'BOOLEAN', 'default' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('categories');

        // Services Table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'category_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 100],
            'description' => ['type' => 'TEXT', 'null' => true],
            'base_price' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'null' => true],
            'image_url' => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'is_active' => ['type' => 'BOOLEAN', 'default' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('category_id', 'categories', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('services');

        // Jobs Table
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'customer_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'worker_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'null' => true],
            'service_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'status' => ['type' => 'ENUM', 'constraint' => ['pending', 'accepted', 'on_way', 'started', 'completed', 'cancelled', 'disputed'], 'default' => 'pending'],
            'price' => ['type' => 'DECIMAL', 'constraint' => '10,2', 'null' => true],
            'location_address' => ['type' => 'TEXT', 'null' => true],
            'location_lat' => ['type' => 'DECIMAL', 'constraint' => '10,8', 'null' => true],
            'location_lng' => ['type' => 'DECIMAL', 'constraint' => '11,8', 'null' => true],
            'scheduled_time' => ['type' => 'DATETIME', 'null' => true],
            'notes' => ['type' => 'TEXT', 'null' => true],
            'otp_start' => ['type' => 'VARCHAR', 'constraint' => 6, 'null' => true],
            'otp_end' => ['type' => 'VARCHAR', 'constraint' => 6, 'null' => true],
            'created_at datetime default current_timestamp',
            'updated_at datetime default current_timestamp on update current_timestamp',
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('customer_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('worker_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->addForeignKey('service_id', 'services', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('jobs');
    }

    public function down()
    {
        $this->forge->dropTable('jobs');
        $this->forge->dropTable('services');
        $this->forge->dropTable('categories');
        $this->forge->dropTable('worker_profiles');
        $this->forge->dropTable('users');
    }
}
