<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

/**
 * Allow orders to outlive deleted customers: user_id becomes NULL when users row is deleted.
 * Resolves ON DELETE CASCADE on orders.user_id which would remove order history.
 */
class OrdersUserIdOnDeleteSetNull extends Migration
{
    private const NEW_FK = 'fk_orders_user_id_set_null';

    public function up(): void
    {
        if ($this->db->getPlatform() !== 'MySQLi') {
            return;
        }

        $dbName = $this->db->getDatabase();
        $row    = $this->db->query(
            'SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
             WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ? AND REFERENCED_TABLE_NAME = ?
             LIMIT 1',
            [$dbName, 'orders', 'user_id', 'users']
        )->getRow();

        if ($row && ! empty($row->CONSTRAINT_NAME)) {
            $this->db->query('ALTER TABLE `orders` DROP FOREIGN KEY `' . $row->CONSTRAINT_NAME . '`');
        }

        $this->db->query('ALTER TABLE `orders` MODIFY `user_id` INT(11) UNSIGNED NULL');
        $this->db->query(
            'ALTER TABLE `orders` ADD CONSTRAINT `' . self::NEW_FK
            . '` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE'
        );
    }

    public function down(): void
    {
        if ($this->db->getPlatform() !== 'MySQLi') {
            return;
        }

        $this->db->query('ALTER TABLE `orders` DROP FOREIGN KEY `' . self::NEW_FK . '`');

        $nullCount = (int) ($this->db->query('SELECT COUNT(*) AS c FROM `orders` WHERE `user_id` IS NULL')->getRow()->c ?? 0);
        if ($nullCount > 0) {
            // Cannot restore NOT NULL + ON DELETE CASCADE; keep nullable and same FK semantics as `up`.
            $this->db->query(
                'ALTER TABLE `orders` ADD CONSTRAINT `' . self::NEW_FK
                . '` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE'
            );

            return;
        }

        $this->db->query('ALTER TABLE `orders` MODIFY `user_id` INT(11) UNSIGNED NOT NULL');
        $this->db->query(
            'ALTER TABLE `orders` ADD CONSTRAINT `' . self::NEW_FK
            . '_cascade` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'
        );
    }
}
