<?php

declare(strict_types=1);

namespace PpPhp\Core\Database;

/**
 * Makes MySQL/MariaDB object DDL portable across servers.
 *
 * The #1 reason a database that works in development breaks when copied to a
 * live host is the `DEFINER=`user`@`host`` clause MySQL bakes into every VIEW,
 * TRIGGER and ROUTINE. On import the target server rejects it because that
 * account does not exist there (or the importer lacks SUPER) — silently leaving
 * the object uncreated. A dashboard bound to such a view then shows "No data".
 *
 * These helpers strip the definer and force INVOKER security so the object is
 * created under whoever runs the import — which is exactly what a fresh install
 * wants. Pure string transforms; no DB access.
 */
final class SqlPortability
{
    /**
     * Rewrite a `SHOW CREATE VIEW` statement to a portable, re-runnable form:
     *  - drop `DEFINER=`x`@`y`` and `ALGORITHM=…`
     *  - `SQL SECURITY DEFINER` → `SQL SECURITY INVOKER`
     *  - `CREATE VIEW` → `CREATE OR REPLACE … VIEW` (idempotent import)
     */
    public static function view(string $ddl): string
    {
        $ddl = trim($ddl);
        // `DEFINER=`user`@`host`` (back-quoted) or DEFINER=user@host (bare/quoted).
        $ddl = (string) preg_replace('/\s*DEFINER\s*=\s*(`[^`]*`|\'[^\']*\'|[^\s]+)@(`[^`]*`|\'[^\']*\'|[^\s]+)/i', '', $ddl);
        $ddl = (string) preg_replace('/\s*ALGORITHM\s*=\s*\w+/i', '', $ddl);
        $ddl = (string) preg_replace('/SQL\s+SECURITY\s+DEFINER/i', 'SQL SECURITY INVOKER', $ddl);
        // Make it re-runnable. Only add OR REPLACE when it is not already there.
        if (!preg_match('/^CREATE\s+OR\s+REPLACE/i', $ddl)) {
            $ddl = (string) preg_replace('/^CREATE\s+/i', 'CREATE OR REPLACE ', $ddl, 1);
        }
        return $ddl;
    }

    /**
     * Strip a leading `DEFINER=…` from any statement (TRIGGER/ROUTINE/VIEW). Used
     * defensively at import time as a belt-and-suspenders when a dump authored
     * elsewhere still carries one.
     */
    public static function stripDefiner(string $stmt): string
    {
        return (string) preg_replace('/\s*DEFINER\s*=\s*(`[^`]*`|\'[^\']*\'|[^\s]+)@(`[^`]*`|\'[^\']*\'|[^\s]+)/i', '', $stmt);
    }
}
