// db/init.js
const db = require('./knex');
const { initWsTables } = require("./dbWs");

async function init() {

  // -------------------------------
  // 1) Tabella PHOTOS
  // -------------------------------
  const existsPhotos = await db.schema.hasTable('photos');

  if (!existsPhotos) {
    await db.schema.createTable('photos', (t) => {
      t.string('id').primary();
      t.string('user');
      t.string('cartella');
      t.string('name');
      t.string('path');
      t.string('thub1');
      t.string('thub2');
      t.string('mime_type');
      t.integer('width');
      t.integer('height');
      t.integer('rotation');
      t.integer('size_bytes');
      t.integer('mtimeMs');
      t.integer('duration_ms');
      t.string('taken_at');
      t.string('data');
      t.float('lat');
      t.float('lon');
      t.float('alt');
      t.text('location');
      t.string('_indexHash');
      t.string('fast_hash');

      // 🔥 SOFT DELETE + TIMESTAMPS
      t.datetime('created_at').defaultTo(db.fn.now());
      t.datetime('updated_at').defaultTo(db.fn.now());
      t.datetime('deleted_at').nullable();
    });

    console.log("✔ Tabella 'photos' creata correttamente");

  } else {
    console.log("✔ Tabella 'photos' già esistente");

    // Aggiungi colonne mancanti (retrocompatibilità)
    const cols = [
      '_indexHash',
      'fast_hash',
      'created_at',
      'updated_at',
      'deleted_at'
    ];

    for (const col of cols) {
      const exists = await db.schema.hasColumn('photos', col);
      if (!exists) {
        await db.schema.alterTable('photos', (t) => {
          if (col === 'created_at' || col === 'updated_at') {
            t.datetime(col).defaultTo(db.fn.now());
          } else if (col === 'deleted_at') {
            t.datetime(col).nullable();
          } else {
            t.string(col);
          }
        });
        console.log(`✔ Colonna '${col}' aggiunta`);
      }
    }
  }

  // -------------------------------
  // 2) RIMOZIONE TABELLA PHOTO_CHANGES
  // -------------------------------
  const existsChanges = await db.schema.hasTable('photo_changes');

  if (existsChanges) {
    await db.schema.dropTable('photo_changes');
    console.log("✔ Tabella 'photo_changes' eliminata (non più necessaria)");
  } else {
    console.log("✔ Tabella 'photo_changes' non presente (ok)");
  }

  // -------------------------------
  // 3) NUOVA TABELLA deleted_hard
  // -------------------------------
  const existsDeletedHard = await db.schema.hasTable('deleted_hard');

  if (!existsDeletedHard) {
    await db.schema.createTable('deleted_hard', (t) => {
      t.string('id').primary();
      t.string('user');
      t.datetime('deleted_at').defaultTo(db.fn.now());
    });

    console.log("✔ Tabella 'deleted_hard' creata correttamente");
  } else {
    console.log("✔ Tabella 'deleted_hard' già esistente");
  }

  // -------------------------------
  // 4) TABELLE WEBSOCKET
  // -------------------------------
  await initWsTables();
  console.log("✔ Tabelle WebSocket inizializzate");

  process.exit();
}

init();