<?php

namespace Modules\Ticket\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Permission: start an online (passenger) ticket refund from the ticket list (step 1).
 * Separate from manage_online_refunds (online refund list + Money sent).
 */
class AddRefundOnlineTicketPermission extends Migration
{
    public function up()
    {
        $db = $this->db;
        $exists = $db->table('menus')
            ->where('menu_title', 'refund_online_ticket')
            ->where('deleted_at', null)
            ->countAllResults();
        if ($exists > 0) {
            return;
        }

        $parent = $db->table('menus')
            ->select('id')
            ->where('menu_title', 'ticket_booking')
            ->where('deleted_at', null)
            ->get()
            ->getRow();

        if (! $parent) {
            return;
        }

        $db->table('menus')->insert([
            'menu_title'      => 'refund_online_ticket',
            'page_url'        => 'ticket/booking/refund-online-ticket',
            'module_name'     => 'Ticket',
            'parent_menu_id'  => (string) $parent->id,
            'have_chield'     => null,
            'created_by'      => '1',
            'created_at'      => date('Y-m-d H:i:s'),
            'updated_at'      => date('Y-m-d H:i:s'),
            'deleted_at'      => null,
        ]);
        $menuId = (int) $db->insertID();

        $permExists = $db->table('permissions')
            ->where('menu_id', $menuId)
            ->where('role_id', 1)
            ->where('deleted_at', null)
            ->countAllResults();
        if ($permExists === 0) {
            $db->table('permissions')->insert([
                'role_id'    => 1,
                'user_id'    => 1,
                'menu_id'    => $menuId,
                'menu_title' => 'refund_online_ticket',
                'create'     => '1',
                'read'       => '1',
                'edit'       => '0',
                'delete'     => '0',
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
                'deleted_at' => null,
            ]);
        }

        // Preserve behaviour: roles that could run the old online flow had refund_list create
        // and manage_online_refunds read (form) and often create (submit).
        $refundListMenu = $db->table('menus')
            ->select('id')
            ->where('menu_title', 'refund_list')
            ->where('deleted_at', null)
            ->get()
            ->getRow();
        $manageMenu = $db->table('menus')
            ->select('id')
            ->where('menu_title', 'manage_online_refunds')
            ->where('deleted_at', null)
            ->get()
            ->getRow();

        if (! $refundListMenu || ! $manageMenu) {
            return;
        }

        $roleRows = $db->query(
            'SELECT DISTINCT p1.role_id
            FROM permissions p1
            INNER JOIN permissions p2 ON p1.role_id = p2.role_id
            WHERE p1.menu_id = ? AND p1.create = ? AND p1.deleted_at IS NULL
              AND p2.menu_id = ? AND p2.deleted_at IS NULL
              AND (p2.read = ? OR p2.create = ?)',
            [$refundListMenu->id, '1', $manageMenu->id, '1', '1']
        )->getResult();

        foreach ($roleRows as $rr) {
            $rid = (int) $rr->role_id;
            if ($rid === 1) {
                continue;
            }
            $uidRow = $db->table('permissions')
                ->select('user_id')
                ->where('menu_id', $refundListMenu->id)
                ->where('role_id', $rid)
                ->where('deleted_at', null)
                ->orderBy('id', 'ASC')
                ->get()
                ->getRow();
            $uid = $uidRow && (int) $uidRow->user_id > 0 ? (int) $uidRow->user_id : 1;
            $dup = $db->table('permissions')
                ->where('menu_id', $menuId)
                ->where('role_id', $rid)
                ->where('deleted_at', null)
                ->countAllResults();
            if ($dup > 0) {
                continue;
            }
            $db->table('permissions')->insert([
                'role_id'    => $rid,
                'user_id'    => $uid > 0 ? $uid : 1,
                'menu_id'    => $menuId,
                'menu_title' => 'refund_online_ticket',
                'create'     => '1',
                'read'       => '1',
                'edit'       => '0',
                'delete'     => '0',
                'created_at' => date('Y-m-d H:i:s'),
                'updated_at' => date('Y-m-d H:i:s'),
                'deleted_at' => null,
            ]);
        }
    }

    public function down()
    {
        $row = $this->db->table('menus')
            ->where('menu_title', 'refund_online_ticket')
            ->get()
            ->getRow();
        if ($row) {
            $this->db->table('permissions')->where('menu_id', $row->id)->delete();
            $this->db->table('menus')->where('id', $row->id)->delete();
        }
    }
}
