<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateRewarderLocations extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'code' => ['type' => 'VARCHAR', 'constraint' => 10, 'null' => true],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey('name', false, true);
        $this->forge->createTable('rewarder_countries', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'country_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['country_id']);
        $this->forge->addKey(['country_id', 'name'], false, true);
        $this->forge->addForeignKey('country_id', 'rewarder_countries', 'id', 'RESTRICT', 'CASCADE');
        $this->forge->createTable('rewarder_states', true);
        $this->forge->reset();

        $this->forge->addField([
            'id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'state_id' => ['type' => 'INT', 'constraint' => 11, 'unsigned' => true],
            'name' => ['type' => 'VARCHAR', 'constraint' => 150],
            'status' => ['type' => 'TINYINT', 'constraint' => 1, 'default' => 1],
            'created_at' => ['type' => 'DATETIME', 'null' => true],
            'updated_at' => ['type' => 'DATETIME', 'null' => true],
        ]);
        $this->forge->addKey('id', true);
        $this->forge->addKey(['state_id']);
        $this->forge->addKey(['state_id', 'name'], false, true);
        $this->forge->addForeignKey('state_id', 'rewarder_states', 'id', 'RESTRICT', 'CASCADE');
        $this->forge->createTable('rewarder_cities', true);
        $this->forge->reset();

        $permissions = [
            ['title' => 'Locations Manage', 'code' => 'locations_manage'],
        ];

        foreach ($permissions as $perm) {
            $exists = $this->db->table('permissions')->where('code', $perm['code'])->countAllResults();
            if (!$exists) {
                $this->db->table('permissions')->insert($perm);
            }
        }

        $adminRole = $this->db->table('roles')->select('id')->where('title', 'Admin')->get()->getRow();
        if ($adminRole) {
            foreach (array_column($permissions, 'code') as $code) {
                $exists = $this->db->table('role_permissions')->where(['role' => $adminRole->id, 'permission' => $code])->countAllResults();
                if (!$exists) {
                    $this->db->table('role_permissions')->insert(['role' => $adminRole->id, 'permission' => $code]);
                }
            }
        }
    }

    public function down()
    {
        $this->forge->dropTable('rewarder_cities', true);
        $this->forge->dropTable('rewarder_states', true);
        $this->forge->dropTable('rewarder_countries', true);
    }
}

