server_photo_s35js/db/init.js

// db/init.js
const db = require('./knex');

async function ensurePhotos() {
  const exists = await db.schema.hasTable('photos');
  if (!exists) {
    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');
      t.datetime('created_at').defaultTo(db.fn.now());
      t.datetime('updated_at').defaultTo(db.fn.now());
      t.datetime('deleted_at').nullable();
    });
    console.log("✔ photos creata");
  } else {
    const cols = [
      'thub1','thub2','fast_hash','created_at','updated_at','deleted_at'
    ];
    for (const col of cols) {
      const existsCol = await db.schema.hasColumn('photos', col);
      if (!existsCol) {
        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(`✔ aggiunta colonna photos.${col}`);
      }
    }
  }
}

async function ensureDeletedHard() {
  const exists = await db.schema.hasTable('deleted_hard');
  if (!exists) {
    await db.schema.createTable('deleted_hard', (t) => {
      t.string('id').primary();
      t.string('user');
      t.datetime('deleted_at').defaultTo(db.fn.now());
    });
    console.log("✔ deleted_hard creata");
  }
}

async function ensureWsSessions() {
  const exists = await db.schema.hasTable('ws_sessions');
  if (!exists) {
    await db.schema.createTable('ws_sessions', (t) => {
      t.string('session_id').notNullable();
      t.string('device_id').notNullable();
      t.string('user').notNullable();
      t.integer('connected_at').notNullable();
      t.integer('last_ack').notNullable();
      t.integer('last_sync').nullable();
      t.boolean('need_full_sync').notNullable().defaultTo(false);

      t.primary(['session_id', 'device_id']);
    });
    console.log("✔ ws_sessions creata");
  } else {
    // aggiungi colonne mancanti
    const cols = [
      { name: 'device_id', type: 'string' },
      { name: 'last_sync', type: 'integer' }
    ];

    for (const c of cols) {
      const existsCol = await db.schema.hasColumn('ws_sessions', c.name);
      if (!existsCol) {
        await db.schema.alterTable('ws_sessions', (t) => {
          if (c.type === 'string') t.string(c.name).notNullable().defaultTo('default');
          if (c.type === 'integer') t.integer(c.name).nullable();
        });
        console.log(`✔ aggiunta colonna ws_sessions.${c.name}`);
      }
    }
  }
}

async function ensureWsPending() {
  const exists = await db.schema.hasTable('ws_pending_events');
  if (!exists) {
    await db.schema.createTable('ws_pending_events', (t) => {
      t.string('event_id').primary();
      t.string('session_id').notNullable();
      t.text('payload').notNullable();
      t.integer('sent_at').notNullable();
      t.integer('retries').notNullable().defaultTo(0);
    });
    console.log("✔ ws_pending_events creata");
  }
}

async function ensurePendingEvents() {
  const exists = await db.schema.hasTable('pending_events');
  if (!exists) {
    await db.schema.createTable('pending_events', (t) => {
      t.increments('id').primary();          // chiave interna
      t.string('user_id').notNullable();     // utente
      t.string('session_id').notNullable();  // sessione
      t.string('device_id').notNullable();   // device (tab)
      t.string('event_id').notNullable();    // UUID evento
      t.text('payload').notNullable();       // JSON del WS
      t.integer('created_at').notNullable(); // timestamp ms

      t.unique(['user_id', 'session_id', 'device_id', 'event_id']);
    });
    console.log("✔ pending_events creata");
  }
}

async function init() {
  try {
    console.log("🔧 init DB...");
    await ensurePhotos();
    await ensureDeletedHard();
    await ensureWsSessions();
    await ensureWsPending();
    await ensurePendingEvents();
    console.log("✅ init completato");
    process.exit(0);
  } catch (err) {
    console.error("❌ errore init:", err);
    process.exit(1);
  }
}

init();