<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Online passenger refund payout tracking lives on refunds (no separate table).
 * If online_manual_refunds exists (older installs), copy into refunds and drop it.
 */
class AddOnlinePayoutColumnsToRefunds extends Migration
{
	private function refundsColumnExists(string $column): bool
	{
		$row = $this->db->query(
			'SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1',
			['refunds', $column]
		)->getRow();

		return $row !== null;
	}

	private function onlineManualRefundsTableExists(): bool
	{
		$row = $this->db->query(
			'SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1',
			['online_manual_refunds']
		)->getRow();

		return $row !== null;
	}

	public function up()
	{
		$cols = [
			'geidea_order_id' => [
				'type'       => 'VARCHAR',
				'constraint' => 100,
				'null'       => true,
			],
			'customer_mobile' => [
				'type'       => 'VARCHAR',
				'constraint' => 50,
				'null'       => true,
			],
			'online_ticket_amount' => [
				'type'       => 'DECIMAL',
				'constraint' => '12,2',
				'null'       => true,
			],
			'online_net_amount' => [
				'type'       => 'DECIMAL',
				'constraint' => '12,2',
				'null'       => true,
			],
			'online_payout_status' => [
				'type'       => 'VARCHAR',
				'constraint' => 20,
				'null'       => true,
			],
			'refund_sent_by' => [
				'type'       => 'INT',
				'constraint' => 10,
				'unsigned'   => true,
				'null'       => true,
			],
			'refund_sent_at' => [
				'type' => 'DATETIME',
				'null' => true,
			],
			'refund_payout_note' => [
				'type' => 'TEXT',
				'null' => true,
			],
		];

		foreach ($cols as $name => $def) {
			if (! $this->refundsColumnExists($name)) {
				$this->forge->addColumn('refunds', [$name => $def]);
			}
		}

		if (! $this->onlineManualRefundsTableExists()) {
			return;
		}

		$rows = $this->db->table('online_manual_refunds')->get()->getResultArray();

		foreach ($rows as $om) {
			$rid = (int) ($om['refund_id'] ?? 0);
			if ($rid < 1) {
				$match = $this->db->table('refunds')
					->where('booking_id', $om['booking_id'] ?? '')
					->where('track_table_id', (string) ($om['ticket_id'] ?? ''))
					->where('type', 'ticket')
					->orderBy('id', 'DESC')
					->limit(1)
					->get()
					->getRowArray();
				$rid = (int) ($match['id'] ?? 0);
			}
			if ($rid < 1) {
				continue;
			}

			$this->db->table('refunds')->where('id', $rid)->update([
				'geidea_order_id'      => $om['geidea_order_id'] ?? null,
				'customer_mobile'      => $om['customer_mobile'] ?? null,
				'online_ticket_amount' => $om['ticket_amount'] ?? null,
				'online_net_amount'    => $om['refund_amount'] ?? null,
				'online_payout_status' => $om['status'] ?? null,
				'refund_sent_by'       => $om['refund_sent_by'] ?? null,
				'refund_sent_at'       => $om['refund_sent_at'] ?? null,
				'refund_payout_note'   => $om['refund_note'] ?? null,
			]);
		}

		$this->forge->dropTable('online_manual_refunds', true);
	}

	public function down()
	{
		$drop = [
			'geidea_order_id',
			'customer_mobile',
			'online_ticket_amount',
			'online_net_amount',
			'online_payout_status',
			'refund_sent_by',
			'refund_sent_at',
			'refund_payout_note',
		];
		foreach ($drop as $name) {
			if ($this->refundsColumnExists($name)) {
				$this->forge->dropColumn('refunds', $name);
			}
		}
	}
}
