<?php
declare(strict_types=1);

require_once __DIR__ . '/../magic/session_config.php';
require_once __DIR__ . '/../magic/DataBase.php';

$isCli = PHP_SAPI === 'cli';

if (!$isCli) {
    bdr_start_session();
    if (empty($_SESSION['user_id'])) {
        http_response_code(403);
        echo 'غير مصرح';
        exit;
    }
}

$database = new DataBase();
$db = $database->getConnection();
$db->exec('SET NAMES utf8mb4');

if (!$isCli && !audit_installer_user_can_run($db)) {
    http_response_code(403);
    echo 'غير مصرح بتشغيل التثبيت';
    exit;
}

$apply = $isCli
    ? in_array('--apply', $argv ?? array(), true)
    : (isset($_GET['apply']) && $_GET['apply'] === '1');

$messages = array();
$tables = audit_base_tables($db);

if ($apply) {
    audit_create_history_table($db);
    $messages[] = 'تم إنشاء/تحديث جدول data_history.';
    audit_drop_excluded_table_triggers($db);
    $messages[] = 'تم إيقاف التتبع للجداول المستثناة.';

    foreach ($tables as $table) {
        audit_create_table_triggers($db, $table);
        $messages[] = 'تم تجهيز التتبع للجدول: ' . $table;
    }
} else {
    $messages[] = 'وضع المعاينة فقط. أضف --apply من سطر الأوامر أو ?apply=1 من المتصفح لتنفيذ التثبيت.';
}

$messages[] = 'عدد الجداول الحالية: ' . count($tables);
$messages[] = 'عدد Triggers الخاصة بسجل التعديلات: ' . audit_trigger_count($db);

if ($isCli) {
    foreach ($messages as $message) {
        echo $message . PHP_EOL;
    }
    exit;
}

?>
<!DOCTYPE html>
<html lang="ar" dir="rtl">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>تثبيت سجل التعديلات</title>
    <style>
        body { margin: 0; font-family: Tahoma, Arial, sans-serif; background: #f4f7f9; color: #1f2933; }
        .wrap { max-width: 980px; margin: 32px auto; padding: 0 18px; }
        .panel { background: #fff; border: 1px solid #d9e2ec; border-radius: 8px; padding: 22px; box-shadow: 0 12px 30px rgba(15, 23, 42, .08); }
        h1 { margin: 0 0 10px; font-size: 24px; }
        p { line-height: 1.8; }
        .actions { margin: 20px 0; display: flex; gap: 10px; flex-wrap: wrap; }
        .btn { display: inline-block; padding: 10px 16px; border-radius: 6px; text-decoration: none; font-weight: bold; }
        .btn-primary { color: #fff; background: #126c66; }
        .btn-light { color: #243b53; background: #eef2f6; border: 1px solid #cbd5df; }
        ul { padding-right: 22px; line-height: 1.9; }
        .ok { color: #126c66; }
        .muted { color: #627d98; }
    </style>
</head>
<body>
<main class="wrap">
    <section class="panel">
        <h1>تثبيت سجل التعديلات</h1>
        <p class="muted">هذه الصفحة تنشئ جدول <b>data_history</b> وتضيف Triggers لكل الجداول الحالية حتى يتم تسجيل أي إضافة أو تعديل أو حذف تلقائيا.</p>
        <div class="actions">
            <?php if (!$apply): ?>
                <a class="btn btn-primary" href="?apply=1">تشغيل التثبيت الآن</a>
            <?php endif; ?>
            <a class="btn btn-light" href="../audit/history.php">فتح تقرير التعديلات</a>
        </div>
        <ul>
            <?php foreach ($messages as $message): ?>
                <li class="ok"><?php echo htmlspecialchars($message, ENT_QUOTES, 'UTF-8'); ?></li>
            <?php endforeach; ?>
        </ul>
        <p class="muted">عدد الجداول التي سيتم/تم تجهيزها: <?php echo count($tables); ?></p>
    </section>
</main>
</body>
</html>
<?php

function audit_installer_user_can_run(PDO $db): bool
{
    if (empty($_SESSION['user_id'])) {
        return false;
    }

    $stmt = $db->prepare(
        'SELECT user_id
         FROM privileges
         WHERE user_id = ?
           AND element_id IN (20, 30, 36)
           AND yes_no = 1
         LIMIT 1'
    );
    $stmt->execute(array($_SESSION['user_id']));

    return (bool) $stmt->fetchColumn();
}

function audit_create_history_table(PDO $db): void
{
    $db->exec(
        "CREATE TABLE IF NOT EXISTS `data_history` (
            `history_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `table_name` VARCHAR(128) NOT NULL,
            `record_pk` VARCHAR(512) DEFAULT NULL,
            `action_type` VARCHAR(10) NOT NULL,
            `old_data` LONGTEXT DEFAULT NULL,
            `new_data` LONGTEXT DEFAULT NULL,
            `changed_by_user_id` BIGINT DEFAULT NULL,
            `changed_by_name` VARCHAR(255) DEFAULT NULL,
            `changed_ip` VARCHAR(45) DEFAULT NULL,
            `changed_url` VARCHAR(1000) DEFAULT NULL,
            `changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`history_id`),
            KEY `idx_data_history_table_time` (`table_name`, `changed_at`),
            KEY `idx_data_history_user_time` (`changed_by_user_id`, `changed_at`),
            KEY `idx_data_history_action_time` (`action_type`, `changed_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    );

    audit_add_history_column($db, 'record_pk', "`record_pk` VARCHAR(512) DEFAULT NULL AFTER `table_name`");
    audit_add_history_column($db, 'changed_url', "`changed_url` VARCHAR(1000) DEFAULT NULL AFTER `changed_ip`");
}

function audit_add_history_column(PDO $db, string $column, string $definition): void
{
    $stmt = $db->query('SHOW COLUMNS FROM `data_history` LIKE ' . $db->quote($column));
    if (!$stmt->fetchColumn()) {
        $db->exec('ALTER TABLE `data_history` ADD COLUMN ' . $definition);
    }
}

function audit_base_tables(PDO $db): array
{
    $stmt = $db->query(
        "SELECT TABLE_NAME
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_TYPE = 'BASE TABLE'
           AND TABLE_NAME NOT IN ('data_history', 'actions', 'log', 'contractsback')
         ORDER BY TABLE_NAME"
    );

    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

function audit_drop_excluded_table_triggers(PDO $db): void
{
    $stmt = $db->query(
        "SELECT TRIGGER_NAME
         FROM INFORMATION_SCHEMA.TRIGGERS
         WHERE TRIGGER_SCHEMA = DATABASE()
           AND EVENT_OBJECT_TABLE IN ('actions', 'log', 'contractsback')
           AND TRIGGER_NAME LIKE 'audit\_%'"
    );

    $triggers = $stmt->fetchAll(PDO::FETCH_COLUMN);
    foreach ($triggers as $trigger) {
        $db->exec('DROP TRIGGER IF EXISTS ' . audit_quote_identifier((string) $trigger));
    }
}

function audit_trigger_count(PDO $db): int
{
    $stmt = $db->query(
        "SELECT COUNT(*)
         FROM INFORMATION_SCHEMA.TRIGGERS
         WHERE TRIGGER_SCHEMA = DATABASE()
           AND TRIGGER_NAME LIKE 'audit\_%'"
    );

    return (int) $stmt->fetchColumn();
}

function audit_table_columns(PDO $db, string $table): array
{
    $stmt = $db->prepare(
        "SELECT COLUMN_NAME, DATA_TYPE
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = ?
         ORDER BY ORDINAL_POSITION"
    );
    $stmt->execute(array($table));

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function audit_primary_key_columns(PDO $db, string $table): array
{
    $stmt = $db->prepare(
        "SELECT COLUMN_NAME
         FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = ?
           AND CONSTRAINT_NAME = 'PRIMARY'
         ORDER BY ORDINAL_POSITION"
    );
    $stmt->execute(array($table));

    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

function audit_create_table_triggers(PDO $db, string $table): void
{
    $columns = audit_table_columns($db, $table);
    if (!$columns) {
        return;
    }

    $pkColumns = audit_primary_key_columns($db, $table);
    $triggerBase = 'audit_' . substr(md5($table), 0, 16);
    $quotedTable = audit_quote_identifier($table);
    $tableValue = audit_sql_value($table);

    $newJson = audit_json_object('NEW', $columns);
    $oldJson = audit_json_object('OLD', $columns);
    $newPk = audit_pk_expression('NEW', $pkColumns);
    $oldPk = audit_pk_expression('OLD', $pkColumns);
    $actorSql = "NULLIF(@audit_user_id, ''), COALESCE(NULLIF(@audit_user_name, ''), 'غير معروف'), NULLIF(@audit_ip, ''), NULLIF(@audit_request_uri, ''), NOW()";

    $triggers = array(
        $triggerBase . '_ai' => "CREATE TRIGGER " . audit_quote_identifier($triggerBase . '_ai') . "
            AFTER INSERT ON {$quotedTable}
            FOR EACH ROW
            BEGIN
                INSERT INTO `data_history`
                    (`table_name`, `record_pk`, `action_type`, `old_data`, `new_data`, `changed_by_user_id`, `changed_by_name`, `changed_ip`, `changed_url`, `changed_at`)
                VALUES
                    ({$tableValue}, {$newPk}, 'INSERT', NULL, {$newJson}, {$actorSql});
            END",
        $triggerBase . '_au' => "CREATE TRIGGER " . audit_quote_identifier($triggerBase . '_au') . "
            AFTER UPDATE ON {$quotedTable}
            FOR EACH ROW
            BEGIN
                INSERT INTO `data_history`
                    (`table_name`, `record_pk`, `action_type`, `old_data`, `new_data`, `changed_by_user_id`, `changed_by_name`, `changed_ip`, `changed_url`, `changed_at`)
                VALUES
                    ({$tableValue}, {$newPk}, 'UPDATE', {$oldJson}, {$newJson}, {$actorSql});
            END",
        $triggerBase . '_ad' => "CREATE TRIGGER " . audit_quote_identifier($triggerBase . '_ad') . "
            AFTER DELETE ON {$quotedTable}
            FOR EACH ROW
            BEGIN
                INSERT INTO `data_history`
                    (`table_name`, `record_pk`, `action_type`, `old_data`, `new_data`, `changed_by_user_id`, `changed_by_name`, `changed_ip`, `changed_url`, `changed_at`)
                VALUES
                    ({$tableValue}, {$oldPk}, 'DELETE', {$oldJson}, NULL, {$actorSql});
            END",
    );

    foreach ($triggers as $triggerName => $sql) {
        $db->exec('DROP TRIGGER IF EXISTS ' . audit_quote_identifier($triggerName));
        $db->exec($sql);
    }
}

function audit_json_object(string $rowAlias, array $columns): string
{
    $parts = array();
    foreach ($columns as $column) {
        $name = (string) $column['COLUMN_NAME'];
        $type = strtolower((string) $column['DATA_TYPE']);
        $parts[] = audit_sql_value($name) . ', ' . audit_column_value($rowAlias, $name, $type);
    }

    return 'JSON_OBJECT(' . implode(', ', $parts) . ')';
}

function audit_column_value(string $rowAlias, string $column, string $type): string
{
    $identifier = $rowAlias . '.' . audit_quote_identifier($column);
    $lowerColumn = strtolower($column);

    foreach (array('pass', 'password', 'token', 'secret') as $sensitivePart) {
        if (strpos($lowerColumn, $sensitivePart) !== false) {
            return "CASE WHEN {$identifier} IS NULL THEN NULL ELSE '[protected]' END";
        }
    }

    $binaryTypes = array(
        'binary', 'varbinary', 'tinyblob', 'blob', 'mediumblob', 'longblob',
        'geometry', 'point', 'linestring', 'polygon', 'multipoint',
        'multilinestring', 'multipolygon', 'geometrycollection'
    );

    if (in_array($type, $binaryTypes, true)) {
        return "CASE WHEN {$identifier} IS NULL THEN NULL ELSE CONCAT('[binary ', OCTET_LENGTH({$identifier}), ' bytes]') END";
    }

    if ($type === 'bit') {
        return "CASE WHEN {$identifier} IS NULL THEN NULL ELSE CAST({$identifier} AS UNSIGNED) END";
    }

    return $identifier;
}

function audit_pk_expression(string $rowAlias, array $pkColumns): string
{
    if (!$pkColumns) {
        return 'NULL';
    }

    $parts = array();
    foreach ($pkColumns as $column) {
        $identifier = $rowAlias . '.' . audit_quote_identifier((string) $column);
        $parts[] = "CONCAT(" . audit_sql_value((string) $column . '=') . ", COALESCE(CAST({$identifier} AS CHAR), 'NULL'))";
    }

    return 'CONCAT_WS(' . audit_sql_value('|') . ', ' . implode(', ', $parts) . ')';
}

function audit_quote_identifier(string $identifier): string
{
    return '`' . str_replace('`', '``', $identifier) . '`';
}

function audit_sql_value(string $value): string
{
    return "'" . str_replace(array('\\', "'"), array('\\\\', "''"), $value) . "'";
}
