diff.php

<?php 
$pageTitle = 'JSON Schema → DB Diff Generator — BI Data Tools';
$pageDescription = 'Генератор ALTER-скриптов для PostgreSQL из различий между JSON Schema. Автоматизация миграций базы данных.';
include('../header.php'); ?>

<h1><i class="fas fa-database"></i> JSON Schema → DB Diff</h1>

<div class="tool-container diff-viewer">
  <div class="tool-section">
    <p>Генерация ALTER-скриптов для PostgreSQL из различий между JSON Schema структурами БД.</p>
    
    <div class="info-grid">
      <div class="info-card">
        <h4>Поддерживаемые операции</h4>
        <ul>
          <li>CREATE TABLE - создание новых таблиц</li>
          <li>DROP TABLE - удаление таблиц</li>
          <li>ADD COLUMN - добавление колонок</li>
          <li>DROP COLUMN - удаление колонок</li>
          <li>ALTER COLUMN - изменение типов данных</li>
          <li>CREATE INDEX - создание индексов</li>
          <li>DROP INDEX - удаление индексов</li>
        </ul>
      </div>
      
      <div class="info-card">
        <h4>Формат JSON Schema</h4>
        <pre><code>{
  "tables": {
    "users": {
      "columns": {
        "id": {
          "type": "SERIAL",
          "primary_key": true
        },
        "name": {
          "type": "VARCHAR(255)",
          "nullable": false
        }
      },
      "indexes": ["name"]
    }
  }
}</code></pre>
      </div>
    </div>
  </div>

  <!-- Основная форма -->
  <div class="tool-section">
    <div class="form-grid">
      <div class="form-group">
        <label for="oldSchema">Исходная схема (OLD):</label>
        <textarea id="oldSchema" class="form-control json-input" rows="15" placeholder='{"tables": {"users": {"columns": {"id": {"type": "SERIAL", "primary_key": true}}}}}'></textarea>
        <small class="form-text">JSON-схема текущей структуры БД</small>
      </div>

      <div class="form-group">
        <label for="newSchema">Новая схема (NEW):</label>
        <textarea id="newSchema" class="form-control json-input" rows="15" placeholder='{"tables": {"users": {"columns": {"id": {"type": "SERIAL", "primary_key": true}, "email": {"type": "VARCHAR(255)", "nullable": false}}}}}'></textarea>
        <small class="form-text">JSON-схема целевой структуры БД</small>
      </div>
    </div>

    <div class="form-actions">
      <button type="button" id="generateDiff" class="btn btn-primary">
        <i class="fas fa-code"></i> Генерировать ALTER-скрипт
      </button>
      <button type="button" id="loadExample" class="btn btn-outline-primary">
        <i class="fas fa-file-import"></i> Загрузить пример
      </button>
      <button type="button" id="clearAll" class="btn btn-outline-secondary">
        <i class="fas fa-eraser"></i> Очистить
      </button>
      <button type="button" id="validateSchemas" class="btn btn-outline-info">
        <i class="fas fa-check"></i> Проверить JSON
      </button>
    </div>
  </div>

  <!-- Результат -->
  <div id="resultSection" class="tool-section" style="display: none;">
    <h3>Результат сравнения</h3>
    
    <div class="diff-stats">
      <div class="stat-item">
        <span class="stat-label">Таблицы:</span>
        <span id="tablesStats">--</span>
      </div>
      <div class="stat-item">
        <span class="stat-label">Колонки:</span>
        <span id="columnsStats">--</span>
      </div>
      <div class="stat-item">
        <span class="stat-label">Индексы:</span>
        <span id="indexesStats">--</span>
      </div>
    </div>

    <div class="output-controls">
      <button type="button" id="copySQL" class="btn btn-sm btn-outline-primary">
        <i class="fas fa-copy"></i> Копировать SQL
      </button>
      <button type="button" id="downloadSQL" class="btn btn-sm btn-outline-secondary">
        <i class="fas fa-download"></i> Скачать .sql
      </button>
      <label for="includeDrop">
        <input type="checkbox" id="includeDrop" checked> Включить DROP операции
      </label>
    </div>

    <div class="sql-output">
      <h4>ALTER скрипт для PostgreSQL:</h4>
      <pre id="sqlOutput" class="code-output"></pre>
    </div>

    <div id="operationsList" class="operations-list">
      <h4>Список операций:</h4>
      <div id="operationsContent"></div>
    </div>
  </div>

  <!-- Справка -->
  <div class="tool-section">
    <h3>Поддерживаемые типы данных PostgreSQL</h3>
    <div class="data-types-grid">
      <div class="type-group">
        <h4>Числовые</h4>
        <ul>
          <li><code>SERIAL</code>, <code>BIGSERIAL</code></li>
          <li><code>INTEGER</code>, <code>BIGINT</code></li>
          <li><code>DECIMAL(p,s)</code>, <code>NUMERIC(p,s)</code></li>
          <li><code>REAL</code>, <code>DOUBLE PRECISION</code></li>
        </ul>
      </div>
      
      <div class="type-group">
        <h4>Текстовые</h4>
        <ul>
          <li><code>VARCHAR(n)</code>, <code>CHAR(n)</code></li>
          <li><code>TEXT</code></li>
          <li><code>UUID</code></li>
        </ul>
      </div>
      
      <div class="type-group">
        <h4>Дата и время</h4>
        <ul>
          <li><code>DATE</code></li>
          <li><code>TIME</code>, <code>TIMESTAMP</code></li>
          <li><code>TIMESTAMPTZ</code></li>
        </ul>
      </div>
      
      <div class="type-group">
        <h4>Другие</h4>
        <ul>
          <li><code>BOOLEAN</code></li>
          <li><code>JSON</code>, <code>JSONB</code></li>
          <li><code>BYTEA</code></li>
        </ul>
      </div>
    </div>
  </div>
</div>

<script>
class DatabaseDiffGenerator {
  constructor() {
    this.init();
  }

  init() {
    document.getElementById('generateDiff').addEventListener('click', () => this.generateDiff());
    document.getElementById('loadExample').addEventListener('click', () => this.loadExample());
    document.getElementById('clearAll').addEventListener('click', () => this.clearAll());
    document.getElementById('validateSchemas').addEventListener('click', () => this.validateSchemas());
    document.getElementById('copySQL').addEventListener('click', () => this.copySQL());
    document.getElementById('downloadSQL').addEventListener('click', () => this.downloadSQL());
    document.getElementById('includeDrop').addEventListener('change', () => this.generateDiff());
  }

  validateSchemas() {
    const oldSchemaText = document.getElementById('oldSchema').value.trim();
    const newSchemaText = document.getElementById('newSchema').value.trim();
    
    try {
      if (oldSchemaText) JSON.parse(oldSchemaText);
      if (newSchemaText) JSON.parse(newSchemaText);
      alert('✅ JSON схемы валидны');
    } catch (error) {
      alert('❌ Ошибка в JSON: ' + error.message);
    }
  }

  generateDiff() {
    const oldSchemaText = document.getElementById('oldSchema').value.trim();
    const newSchemaText = document.getElementById('newSchema').value.trim();
    
    if (!oldSchemaText && !newSchemaText) {
      alert('Введите хотя бы одну схему для сравнения');
      return;
    }
    
    try {
      const oldSchema = oldSchemaText ? JSON.parse(oldSchemaText) : { tables: {} };
      const newSchema = newSchemaText ? JSON.parse(newSchemaText) : { tables: {} };
      
      const diff = this.compareSchemas(oldSchema, newSchema);
      const sql = this.generateSQL(diff);
      
      this.displayResults(diff, sql);
      
    } catch (error) {
      alert('Ошибка при обработке схем: ' + error.message);
    }
  }

  compareSchemas(oldSchema, newSchema) {
    const diff = {
      createdTables: [],
      droppedTables: [],
      modifiedTables: [],
      stats: {
        tables: { created: 0, dropped: 0, modified: 0 },
        columns: { added: 0, dropped: 0, modified: 0 },
        indexes: { added: 0, dropped: 0 }
      }
    };

    const oldTables = oldSchema.tables || {};
    const newTables = newSchema.tables || {};
    
    // Найти созданные таблицы
    for (const tableName in newTables) {
      if (!(tableName in oldTables)) {
        diff.createdTables.push({
          name: tableName,
          definition: newTables[tableName]
        });
        diff.stats.tables.created++;
      }
    }
    
    // Найти удаленные таблицы
    for (const tableName in oldTables) {
      if (!(tableName in newTables)) {
        diff.droppedTables.push({
          name: tableName,
          definition: oldTables[tableName]
        });
        diff.stats.tables.dropped++;
      }
    }
    
    // Найти измененные таблицы
    for (const tableName in newTables) {
      if (tableName in oldTables) {
        const tableDiff = this.compareTable(oldTables[tableName], newTables[tableName]);
        if (tableDiff.hasChanges) {
          diff.modifiedTables.push({
            name: tableName,
            changes: tableDiff
          });
          diff.stats.tables.modified++;
          
          // Обновить статистику колонок и индексов
          diff.stats.columns.added += tableDiff.addedColumns.length;
          diff.stats.columns.dropped += tableDiff.droppedColumns.length;
          diff.stats.columns.modified += tableDiff.modifiedColumns.length;
          diff.stats.indexes.added += tableDiff.addedIndexes.length;
          diff.stats.indexes.dropped += tableDiff.droppedIndexes.length;
        }
      }
    }
    
    return diff;
  }

  compareTable(oldTable, newTable) {
    const tableDiff = {
      addedColumns: [],
      droppedColumns: [],
      modifiedColumns: [],
      addedIndexes: [],
      droppedIndexes: [],
      hasChanges: false
    };

    const oldColumns = oldTable.columns || {};
    const newColumns = newTable.columns || {};
    const oldIndexes = oldTable.indexes || [];
    const newIndexes = newTable.indexes || [];
    
    // Сравнить колонки
    for (const columnName in newColumns) {
      if (!(columnName in oldColumns)) {
        tableDiff.addedColumns.push({
          name: columnName,
          definition: newColumns[columnName]
        });
      } else if (JSON.stringify(oldColumns[columnName]) !== JSON.stringify(newColumns[columnName])) {
        tableDiff.modifiedColumns.push({
          name: columnName,
          oldDefinition: oldColumns[columnName],
          newDefinition: newColumns[columnName]
        });
      }
    }
    
    for (const columnName in oldColumns) {
      if (!(columnName in newColumns)) {
        tableDiff.droppedColumns.push({
          name: columnName,
          definition: oldColumns[columnName]
        });
      }
    }
    
    // Сравнить индексы
    for (const index of newIndexes) {
      if (!oldIndexes.includes(index)) {
        tableDiff.addedIndexes.push(index);
      }
    }
    
    for (const index of oldIndexes) {
      if (!newIndexes.includes(index)) {
        tableDiff.droppedIndexes.push(index);
      }
    }
    
    tableDiff.hasChanges = tableDiff.addedColumns.length > 0 || 
                          tableDiff.droppedColumns.length > 0 || 
                          tableDiff.modifiedColumns.length > 0 ||
                          tableDiff.addedIndexes.length > 0 ||
                          tableDiff.droppedIndexes.length > 0;
    
    return tableDiff;
  }

  generateSQL(diff) {
    const sql = [];
    const includeDrop = document.getElementById('includeDrop').checked;
    
    sql.push('-- Database migration script generated by BI Data Tools');
    sql.push('-- ' + new Date().toISOString());
    sql.push('');
    
    // Удаление индексов (должно быть первым)
    if (includeDrop) {
      for (const table of diff.modifiedTables) {
        for (const index of table.changes.droppedIndexes) {
          sql.push(`DROP INDEX IF EXISTS idx_${table.name}_${index};`);
        }
      }
    }
    
    // Удаление колонок
    if (includeDrop) {
      for (const table of diff.modifiedTables) {
        for (const column of table.changes.droppedColumns) {
          sql.push(`ALTER TABLE ${table.name} DROP COLUMN IF EXISTS ${column.name};`);
        }
      }
    }
    
    // Изменение колонок
    for (const table of diff.modifiedTables) {
      for (const column of table.changes.modifiedColumns) {
        const newType = column.newDefinition.type;
        const nullable = column.newDefinition.nullable !== false ? '' : ' NOT NULL';
        sql.push(`ALTER TABLE ${table.name} ALTER COLUMN ${column.name} TYPE ${newType}${nullable};`);
      }
    }
    
    // Создание таблиц
    for (const table of diff.createdTables) {
      sql.push(this.generateCreateTableSQL(table));
    }
    
    // Добавление колонок
    for (const table of diff.modifiedTables) {
      for (const column of table.changes.addedColumns) {
        const columnSQL = this.generateColumnSQL(column.name, column.definition);
        sql.push(`ALTER TABLE ${table.name} ADD COLUMN ${columnSQL};`);
      }
    }
    
    // Создание индексов
    for (const table of diff.modifiedTables) {
      for (const index of table.changes.addedIndexes) {
        sql.push(`CREATE INDEX idx_${table.name}_${index} ON ${table.name} (${index});`);
      }
    }
    
    // Создание индексов для новых таблиц
    for (const table of diff.createdTables) {
      const indexes = table.definition.indexes || [];
      for (const index of indexes) {
        sql.push(`CREATE INDEX idx_${table.name}_${index} ON ${table.name} (${index});`);
      }
    }
    
    // Удаление таблиц (должно быть последним)
    if (includeDrop) {
      for (const table of diff.droppedTables) {
        sql.push(`DROP TABLE IF EXISTS ${table.name};`);
      }
    }
    
    return sql.join('\n');
  }

  generateCreateTableSQL(table) {
    const columns = [];
    const constraints = [];
    
    for (const [columnName, columnDef] of Object.entries(table.definition.columns || {})) {
      columns.push('  ' + this.generateColumnSQL(columnName, columnDef));
      
      if (columnDef.primary_key) {
        constraints.push(`  PRIMARY KEY (${columnName})`);
      }
    }
    
    const allItems = [...columns, ...constraints];
    return `CREATE TABLE ${table.name} (\n${allItems.join(',\n')}\n);`;
  }

  generateColumnSQL(columnName, columnDef) {
    let sql = `${columnName} ${columnDef.type}`;
    
    if (columnDef.nullable === false) {
      sql += ' NOT NULL';
    }
    
    if (columnDef.default !== undefined) {
      sql += ` DEFAULT ${columnDef.default}`;
    }
    
    return sql;
  }

  displayResults(diff, sql) {
    // Показать секцию результатов
    document.getElementById('resultSection').style.display = 'block';
    
    // Обновить статистику
    const stats = diff.stats;
    document.getElementById('tablesStats').textContent = 
      `+${stats.tables.created} -${stats.tables.dropped} ~${stats.tables.modified}`;
    document.getElementById('columnsStats').textContent = 
      `+${stats.columns.added} -${stats.columns.dropped} ~${stats.columns.modified}`;
    document.getElementById('indexesStats').textContent = 
      `+${stats.indexes.added} -${stats.indexes.dropped}`;
    
    // Показать SQL
    document.getElementById('sqlOutput').textContent = sql;
    
    // Показать список операций
    this.displayOperations(diff);
    
    // Прокрутить к результатам
    document.getElementById('resultSection').scrollIntoView({ behavior: 'smooth' });
  }

  displayOperations(diff) {
    const operations = [];
    
    for (const table of diff.createdTables) {
      operations.push(`<div class="operation-item create">CREATE TABLE ${table.name}</div>`);
    }
    
    for (const table of diff.droppedTables) {
      operations.push(`<div class="operation-item drop">DROP TABLE ${table.name}</div>`);
    }
    
    for (const table of diff.modifiedTables) {
      for (const column of table.changes.addedColumns) {
        operations.push(`<div class="operation-item add">ADD COLUMN ${table.name}.${column.name}</div>`);
      }
      for (const column of table.changes.droppedColumns) {
        operations.push(`<div class="operation-item drop">DROP COLUMN ${table.name}.${column.name}</div>`);
      }
      for (const column of table.changes.modifiedColumns) {
        operations.push(`<div class="operation-item modify">ALTER COLUMN ${table.name}.${column.name}</div>`);
      }
    }
    
    document.getElementById('operationsContent').innerHTML = operations.join('');
  }

  copySQL() {
    const sql = document.getElementById('sqlOutput').textContent;
    navigator.clipboard.writeText(sql);
    
    const btn = document.getElementById('copySQL');
    const originalText = btn.innerHTML;
    btn.innerHTML = '<i class="fas fa-check"></i> Скопировано';
    setTimeout(() => {
      btn.innerHTML = originalText;
    }, 2000);
  }

  downloadSQL() {
    const sql = document.getElementById('sqlOutput').textContent;
    const blob = new Blob([sql], { type: 'text/sql' });
    const url = URL.createObjectURL(blob);
    
    const a = document.createElement('a');
    a.href = url;
    a.download = `migration_${new Date().toISOString().slice(0, 10)}.sql`;
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
    URL.revokeObjectURL(url);
  }

  loadExample() {
    const oldSchema = {
      tables: {
        users: {
          columns: {
            id: { type: "SERIAL", primary_key: true },
            name: { type: "VARCHAR(255)", nullable: false },
            email: { type: "VARCHAR(255)", nullable: false },
            created_at: { type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
          },
          indexes: ["email"]
        },
        posts: {
          columns: {
            id: { type: "SERIAL", primary_key: true },
            title: { type: "VARCHAR(500)", nullable: false },
            content: { type: "TEXT" },
            user_id: { type: "INTEGER", nullable: false }
          }
        }
      }
    };

    const newSchema = {
      tables: {
        users: {
          columns: {
            id: { type: "SERIAL", primary_key: true },
            name: { type: "VARCHAR(255)", nullable: false },
            email: { type: "VARCHAR(255)", nullable: false },
            phone: { type: "VARCHAR(20)" },
            created_at: { type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" },
            updated_at: { type: "TIMESTAMP" }
          },
          indexes: ["email", "phone"]
        },
        posts: {
          columns: {
            id: { type: "SERIAL", primary_key: true },
            title: { type: "VARCHAR(500)", nullable: false },
            content: { type: "TEXT" },
            user_id: { type: "INTEGER", nullable: false },
            published: { type: "BOOLEAN", default: "false" }
          },
          indexes: ["user_id"]
        },
        comments: {
          columns: {
            id: { type: "SERIAL", primary_key: true },
            post_id: { type: "INTEGER", nullable: false },
            user_id: { type: "INTEGER", nullable: false },
            content: { type: "TEXT", nullable: false },
            created_at: { type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
          }
        }
      }
    };

    document.getElementById('oldSchema').value = JSON.stringify(oldSchema, null, 2);
    document.getElementById('newSchema').value = JSON.stringify(newSchema, null, 2);
  }

  clearAll() {
    document.getElementById('oldSchema').value = '';
    document.getElementById('newSchema').value = '';
    document.getElementById('resultSection').style.display = 'none';
  }
}

// Инициализация
const diffGenerator = new DatabaseDiffGenerator();
</script>

<?php include('footer.php'); ?>