<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateUsersAndAuth extends Migration
{
    public function up(): void
    {
        $this->forge->addField([
            'id'                  => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'agency_id'           => ['type' => 'BIGINT', 'unsigned' => true, 'null' => true],
            'role_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'name'                => ['type' => 'VARCHAR', 'constraint' => 150],
            'email'               => ['type' => 'VARCHAR', 'constraint' => 150],
            'phone'               => ['type' => 'VARCHAR', 'constraint' => 30, 'null' => true],
            'password_hash'       => ['type' => 'VARCHAR', 'constraint' => 255],
            'status'              => ['type' => 'ENUM', 'constraint' => ['active', 'inactive', 'banned', 'pending_verification'], 'default' => 'pending_verification'],
            'two_factor_enabled'  => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
            'two_factor_secret'   => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'email_verified_at'   => ['type' => 'TIMESTAMP', 'null' => true],
            'last_login_at'       => ['type' => 'TIMESTAMP', 'null' => true],
            'created_at'          => ['type' => 'TIMESTAMP', 'null' => true],
            'updated_at'          => ['type' => 'TIMESTAMP', 'null' => true],
            'deleted_at'          => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey('email');
        $this->forge->addKey('agency_id');
        $this->forge->addKey('role_id');
        $this->forge->addKey('status');
        $this->forge->addForeignKey('role_id', 'roles', 'id', false, 'RESTRICT');
        $this->forge->addForeignKey('agency_id', 'agencies', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('users', true, ['ENGINE' => 'InnoDB']);

        // wire the agencies.owner_user_id FK now that users exists — done
        // via raw SQL (not Forge->addForeignKey) since that method only
        // queues a constraint for the NEXT createTable() call, and there
        // isn't one here (agencies already exists) — queuing it without
        // consuming would incorrectly bleed into whichever table's
        // createTable() call comes next in this migration.
        $this->db->query('ALTER TABLE agencies ADD CONSTRAINT fk_agency_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL');

        $this->forge->addField([
            'id'                 => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'user_id'            => ['type' => 'BIGINT', 'unsigned' => true],
            'device_fingerprint' => ['type' => 'VARCHAR', 'constraint' => 255],
            'device_name'        => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'last_seen_at'       => ['type' => 'TIMESTAMP', 'null' => true],
            'is_trusted'         => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 0],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('user_devices', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'         => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'user_id'    => ['type' => 'BIGINT', 'unsigned' => true],
            'ip_address' => ['type' => 'VARCHAR', 'constraint' => 45],
            'device'     => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'browser'    => ['type' => 'VARCHAR', 'constraint' => 150, 'null' => true],
            'status'     => ['type' => 'ENUM', 'constraint' => ['success', 'failed']],
            'created_at' => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['user_id', 'created_at']);
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('login_history', true, ['ENGINE' => 'InnoDB']);

        $this->forge->addField([
            'id'         => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'email'      => ['type' => 'VARCHAR', 'constraint' => 150],
            'token'      => ['type' => 'VARCHAR', 'constraint' => 255],
            'expires_at' => ['type' => 'TIMESTAMP'],
            'created_at' => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('email');
        $this->forge->createTable('password_resets', true, ['ENGINE' => 'InnoDB']);

        // api_tokens (JWT refresh-token tracking / revocation)
        $this->forge->addField([
            'id'                  => ['type' => 'BIGINT', 'unsigned' => true, 'auto_increment' => true],
            'user_id'             => ['type' => 'BIGINT', 'unsigned' => true],
            'token_hash'          => ['type' => 'VARCHAR', 'constraint' => 255],
            'refresh_token_hash'  => ['type' => 'VARCHAR', 'constraint' => 255, 'null' => true],
            'name'                => ['type' => 'VARCHAR', 'constraint' => 100, 'null' => true],
            'abilities'           => ['type' => 'JSON', 'null' => true],
            'expires_at'          => ['type' => 'TIMESTAMP', 'null' => true],
            'revoked_at'          => ['type' => 'TIMESTAMP', 'null' => true],
            'created_at'          => ['type' => 'TIMESTAMP', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('token_hash');
        $this->forge->addForeignKey('user_id', 'users', 'id', 'CASCADE', 'CASCADE');
        $this->forge->createTable('api_tokens', true, ['ENGINE' => 'InnoDB']);
    }

    public function down(): void
    {
        $this->forge->dropTable('api_tokens', true);
        $this->forge->dropTable('password_resets', true);
        $this->forge->dropTable('login_history', true);
        $this->forge->dropTable('user_devices', true);
        $this->db->query('ALTER TABLE agencies DROP FOREIGN KEY fk_agency_owner');
        $this->forge->dropTable('users', true);
    }
}
