Tul xxx Tul

Viewing: schema.php

<?php
/**
 * Shared database schema helpers.
 *
 * These helpers are intentionally small and framework-free so both the admin
 * and public client portal can keep required columns in sync.
 */

function schema_table_exists(PDO $pdo, $table)
{
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?');
    $stmt->execute([$table]);
    return (int) $stmt->fetchColumn() > 0;
}

function schema_column_exists(PDO $pdo, $table, $column)
{
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?');
    $stmt->execute([$table, $column]);
    return (int) $stmt->fetchColumn() > 0;
}

function schema_slug_text($value)
{
    $value = strtolower(trim((string) $value));
    $value = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
    $value = preg_replace('/[^a-z0-9]+/', '-', $value);
    return trim($value, '-') ?: 'cliente';
}

function schema_unique_client_slug(PDO $pdo, $name, $clientId)
{
    $base = schema_slug_text($name ?: 'cliente-' . (int) $clientId);
    $slug = $base;
    $counter = 2;
    $stmt = $pdo->prepare('SELECT id FROM cms_clients WHERE public_slug = ? AND id <> ? LIMIT 1');

    while (true) {
        $stmt->execute([$slug, (int) $clientId]);
        if (!$stmt->fetch()) {
            return $slug;
        }
        $slug = $base . '-' . $counter;
        $counter++;
    }
}

function schema_random_token()
{
    return bin2hex(random_bytes(18));
}

function ensure_client_portal_schema(PDO $pdo)
{
    if (!schema_column_exists($pdo, 'cms_clients', 'public_slug')) {
        $pdo->exec('ALTER TABLE cms_clients ADD public_slug VARCHAR(190) NULL AFTER primary_contact_id');
    }
    if (!schema_column_exists($pdo, 'cms_clients', 'public_token')) {
        $pdo->exec('ALTER TABLE cms_clients ADD public_token VARCHAR(80) NULL AFTER public_slug');
    }
    if (!schema_column_exists($pdo, 'cms_clients', 'public_access_enabled')) {
        $pdo->exec('ALTER TABLE cms_clients ADD public_access_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER public_token');
    }
    if (schema_table_exists($pdo, 'cms_contents') && !schema_column_exists($pdo, 'cms_contents', 'allow_download')) {
        $pdo->exec('ALTER TABLE cms_contents ADD allow_download TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active');
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS cms_order_payments (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        order_id INT UNSIGNED NOT NULL,
        amount DECIMAL(12,2) NOT NULL DEFAULT 0,
        payment_method VARCHAR(120) NULL,
        reference VARCHAR(180) NULL,
        notes TEXT NULL,
        paid_at DATE NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_payments_order (order_id),
        CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES cms_orders(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $stmt = $pdo->query('SELECT id, name, commercial_name, public_slug, public_token FROM cms_clients');
    $update = $pdo->prepare('UPDATE cms_clients SET public_slug = ?, public_token = ? WHERE id = ?');
    foreach ($stmt->fetchAll() as $client) {
        $slug = trim((string) ($client['public_slug'] ?? ''));
        $token = trim((string) ($client['public_token'] ?? ''));
        if ($slug === '') {
            $slug = schema_unique_client_slug($pdo, $client['commercial_name'] ?: $client['name'], (int) $client['id']);
        }
        if ($token === '') {
            $token = schema_random_token();
        }
        if ($slug !== (string) ($client['public_slug'] ?? '') || $token !== (string) ($client['public_token'] ?? '')) {
            $update->execute([$slug, $token, (int) $client['id']]);
        }
    }
}

function ensure_email_schema(PDO $pdo)
{
    $pdo->exec("CREATE TABLE IF NOT EXISTS cms_email_logs (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        email_type ENUM('invitation','gift') NOT NULL DEFAULT 'invitation',
        recipient_email VARCHAR(255) NOT NULL,
        company_name VARCHAR(255) NULL,
        subject VARCHAR(500) NOT NULL,
        status ENUM('sent','failed') NOT NULL DEFAULT 'sent',
        error_message TEXT NULL,
        attachment_name VARCHAR(255) NULL,
        gift_id INT UNSIGNED NULL,
        sent_by INT UNSIGNED NULL,
        sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_email_logs_sent_at (sent_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    if (!schema_column_exists($pdo, 'cms_email_logs', 'email_type')) {
        $pdo->exec("ALTER TABLE cms_email_logs ADD email_type ENUM('invitation','gift') NOT NULL DEFAULT 'invitation' AFTER id");
    }
    if (!schema_column_exists($pdo, 'cms_email_logs', 'attachment_name')) {
        $pdo->exec('ALTER TABLE cms_email_logs ADD attachment_name VARCHAR(255) NULL AFTER error_message');
    }
    if (!schema_column_exists($pdo, 'cms_email_logs', 'gift_id')) {
        $pdo->exec('ALTER TABLE cms_email_logs ADD gift_id INT UNSIGNED NULL AFTER attachment_name');
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS cms_gift_deliveries (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        public_token VARCHAR(80) NOT NULL UNIQUE,
        client_id INT UNSIGNED NULL,
        recipient_email VARCHAR(255) NOT NULL,
        company_name VARCHAR(255) NOT NULL,
        subject VARCHAR(500) NOT NULL,
        logo_url TEXT NULL,
        gift_file_url TEXT NOT NULL,
        gift_original_name VARCHAR(255) NOT NULL,
        gift_mime_type VARCHAR(120) NULL,
        sent_by INT UNSIGNED NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_gift_token (public_token),
        INDEX idx_gift_client (client_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}
User / IP	: 216.73.216.217
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64