createTrigger example

            Defaults::CURRENCY
        );

        $this->createTrigger($connection, $query);
    }

    /** * Adds a database trigger that keeps the fields 'purchase_price' and 'purchase_prices' in sync. That means insert * either value will update the other. */
    private function addInsertDatabaseTrigger(Connection $connection): void
    {
        $query = sprintf(
            'CREATE TRIGGER product_purchase_prices_insert BEFORE INSERT ON product FOR EACH ROW BEGIN IF @TRIGGER_DISABLED IS NULL OR @TRIGGER_DISABLED = 0 THEN BEGIN IF NEW.purchase_prices IS NOT NULL THEN BEGIN SET NEW.purchase_price = JSON_UNQUOTE(JSON_EXTRACT( NEW.purchase_prices, CONCAT("$.", JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(NEW.purchase_prices), "$[0]")), ".gross") )) + 0.0; END; ELSE BEGIN IF NEW.purchase_price IS NOT NULL THEN BEGIN DECLARE taxRate DECIMAL(10,2); SET taxRate = (SELECT tax_rate FROM tax WHERE id = NEW.tax_id); SET NEW.purchase_prices = CONCAT( \'{"c%1$s": {"net": \', CONVERT((NEW.purchase_price / (1 + (taxRate/100))), CHAR(50)), \', "gross": \', CONVERT(NEW.purchase_price, CHAR(50)), \', "linked": true, "currencyId": "%1$s"}}\' ); END; END IF; END; END IF; END; END IF; END',

    private function addInsertTrigger(Connection $connection): void
    {
        $query = 'CREATE TRIGGER customer_address_vat_id_insert AFTER INSERT ON customer_address FOR EACH ROW BEGIN IF NEW.vat_id IS NOT NULL THEN UPDATE customer SET vat_ids = JSON_ARRAY(NEW.vat_id) WHERE customer.default_billing_address_id = NEW.id AND (JSON_CONTAINS(vat_ids, \'"$NEW.vat_id"\') = 0 OR vat_ids IS NULL); END IF; END;';
        $this->createTrigger($connection, $query);
    }

    /** * Adds a database trigger that keeps the fields 'vat_ids' in `customer` and 'vat_id' in `customer_address` in sync. * That means updating either value will update the other. */
    private function addUpdateTrigger(Connection $connection): void
    {
        $query = 'CREATE TRIGGER customer_address_vat_id_update AFTER UPDATE ON customer_address FOR EACH ROW BEGIN IF (OLD.vat_id IS NOT NULL AND NEW.vat_id IS NULL) THEN UPDATE customer SET vat_ids = JSON_REMOVE(vat_ids, JSON_UNQUOTE(JSON_SEARCH(vat_ids, \'one\', OLD.vat_id))) WHERE customer.default_billing_address_id = NEW.id AND JSON_SEARCH(vat_ids, \'one\', OLD.vat_id) IS NOT NULL; ELSEIF (OLD.vat_id IS NULL AND NEW.vat_id IS NOT NULL) OR (OLD.vat_id <> NEW.vat_id) THEN UPDATE customer SET vat_ids = JSON_ARRAY(NEW.vat_id) WHERE customer.default_billing_address_id = NEW.id AND (JSON_CONTAINS(vat_ids, \'"$NEW.vat_id"\') = 0 OR vat_ids IS NULL); END IF; END;';

        $this->createTrigger($connection, $query);
    }

    private function createInsertTrigger(Connection $connection): void
    {
        $query
            = 'CREATE TRIGGER shipping_method_price_new_price_insert BEFORE INSERT ON shipping_method_price FOR EACH ROW BEGIN IF @TRIGGER_DISABLED IS NULL OR @TRIGGER_DISABLED = 0 THEN IF NEW.price IS NOT NULL AND NEW.currency_id IS NOT NULL AND NEW.currency_price IS NULL THEN SET NEW.currency_price = JSON_OBJECT( CONCAT("c", LOWER(HEX(NEW.currency_id))), JSON_OBJECT( "net", NEW.price, "gross", NEW.price, "linked", false, "currencyId", LOWER(HEX(NEW.currency_id)) ) ); ELSEIF NEW.price IS NULL AND NEW.currency_id IS NULL AND NEW.currency_price IS NOT NULL THEN SET NEW.price = JSON_UNQUOTE(JSON_EXTRACT( NEW.currency_price, CONCAT("$.", JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(NEW.currency_price), "$[0]")), ".gross") )) + 0.0; SET NEW.currency_id = UNHEX(JSON_UNQUOTE(JSON_EXTRACT( NEW.currency_price, CONCAT("$.", JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(NEW.currency_price), "$[0]")), ".currencyId") ))); END IF; END IF; END;';

        $this->createTrigger($connection, $query, ['currencyId' => Defaults::CURRENCY]);
    }

    private function addUpdateTrigger(Connection $connection): void
    {
        $query = 'CREATE TRIGGER country_tax_free_update BEFORE UPDATE ON country FOR EACH ROW BEGIN IF @TRIGGER_DISABLED IS NULL OR @TRIGGER_DISABLED = 0 THEN IF NEW.tax_free <> OLD.tax_free THEN SET NEW.customer_tax = JSON_OBJECT("enabled", NEW.tax_free, "currencyId", JSON_EXTRACT(OLD.customer_tax, "$.currencyId"), "amount", JSON_EXTRACT(OLD.customer_tax, "$.amount")); ELSEIF NEW.tax_free = OLD.tax_free AND JSON_EXTRACT(NEW.customer_tax, "$.enabled") <> JSON_EXTRACT(OLD.customer_tax, "$.enabled") THEN SET NEW.tax_free = JSON_EXTRACT(NEW.customer_tax, "$.enabled"); END IF; IF NEW.company_tax_free <> OLD.company_tax_free THEN SET NEW.company_tax = JSON_OBJECT("enabled", NEW.company_tax_free, "currencyId", JSON_EXTRACT(OLD.company_tax, "$.currencyId"), "amount", JSON_EXTRACT(OLD.company_tax, "$.amount")); ELSEIF NEW.company_tax_free = OLD.company_tax_free AND JSON_EXTRACT(NEW.company_tax, "$.enabled") <> JSON_EXTRACT(OLD.company_tax, "$.enabled") THEN SET NEW.company_tax_free = JSON_EXTRACT(NEW.company_tax, "$.enabled"); END IF; END IF; END;';

        $this->createTrigger($connection, $query);
    }

    private function createCurrencyInsertTrigger(Connection $connection): void
    {
        $query
            = 'CREATE TRIGGER currency_cash_rounding_insert BEFORE INSERT ON currency FOR EACH ROW BEGIN IF @TRIGGER_DISABLED IS NULL OR @TRIGGER_DISABLED = 0 THEN IF NEW.decimal_precision IS NOT NULL AND NEW.item_rounding IS NULL THEN SET NEW.item_rounding = JSON_OBJECT( "decimals", NEW.decimal_precision, "interval", 0.01, "roundForNet", true ); SET NEW.total_rounding = JSON_OBJECT( "decimals", NEW.decimal_precision, "interval", 0.01, "roundForNet", true ); ELSEIF NEW.decimal_precision IS NULL AND NEW.item_rounding IS NOT NULL THEN SET NEW.decimal_precision = JSON_UNQUOTE(JSON_EXTRACT(NEW.item_rounding, "$.decimals")) + 0; ELSEIF NEW.decimal_precision IS NOT NULL AND NEW.item_rounding IS NOT NULL THEN SET NEW.decimal_precision = JSON_UNQUOTE(JSON_EXTRACT(NEW.item_rounding, "$.decimals")) + 0; END IF; END IF; END;';

        $this->createTrigger($connection, $sql);

        $connection->executeStatement('UPDATE product SET listing_prices = NULL');
    }

    public function updateDestructive(Connection $connection): void
    {
        // implement update destructive     }
}

    public function testGetNextRunDates(\DateTimeImmutable $from, TriggerInterface $trigger, array $expected, int $count = 0)
    {
        $this->assertEquals($expected, $this->getNextRunDates($from, $trigger, $count ?? \count($expected)));
    }

    public static function providerGetNextRunDates(): iterable
    {
        yield [
            new \DateTimeImmutable('2023-03-19 13:45'),
            self::createTrigger('next tuesday'),
            [
                new \DateTimeImmutable('2023-03-21 13:45:00'),
                new \DateTimeImmutable('2023-03-28 13:45:00'),
                new \DateTimeImmutable('2023-04-04 13:45:00'),
                new \DateTimeImmutable('2023-04-11 13:45:00'),
                new \DateTimeImmutable('2023-04-18 13:45:00'),
                new \DateTimeImmutable('2023-04-25 13:45:00'),
                new \DateTimeImmutable('2023-05-02 13:45:00'),
                new \DateTimeImmutable('2023-05-09 13:45:00'),
                new \DateTimeImmutable('2023-05-16 13:45:00'),
                new \DateTimeImmutable('2023-05-23 13:45:00'),
                

$query = ' CREATE TRIGGER customer_double_opt_in_insert BEFORE INSERT ON customer FOR EACH ROW BEGIN IF @TRIGGER_DISABLED IS NULL OR @TRIGGER_DISABLED = 0 THEN SET NEW.doubleOptInRegistration = NEW.double_opt_in_registration; SET NEW.doubleOptInEmailSentDate = NEW.double_opt_in_email_sent_date; SET NEW.doubleOptInConfirmDate = NEW.double_opt_in_confirm_date; END IF; END; ';

        $this->createTrigger($connection, $query);
    }

    private function addUpdateTrigger(Connection $connection): void
    {
        $query = ' CREATE TRIGGER customer_double_opt_in_update BEFORE UPDATE ON customer FOR EACH ROW BEGIN IF @TRIGGER_DISABLED IS NULL OR @TRIGGER_DISABLED = 0 THEN SET NEW.doubleOptInRegistration = NEW.double_opt_in_registration; SET NEW.doubleOptInEmailSentDate = NEW.double_opt_in_email_sent_date; SET NEW.doubleOptInConfirmDate = NEW.double_opt_in_confirm_date; END IF; END; ';