supabase-scheduler/server.js

require('dotenv').config();
const express = require('express');
const cors = require('cors');
const session = require('express-session');
const Database = require('better-sqlite3');
const cron = require('node-cron');
const path = require('path');

const app = express();
const PORT = process.env.PORT || 3000;

// Credenziali fisse
const AUTH_USERNAME = 'testlab';
const AUTH_PASSWORD = 'magarifunzione4';

// Database SQLite
const db = new Database('./data/scheduler.db');

// Inizializza database
db.exec(`
  CREATE TABLE IF NOT EXISTS databases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    supabase_url TEXT NOT NULL,
    anon_key TEXT NOT NULL,
    query TEXT NOT NULL,
    frequency_minutes INTEGER NOT NULL DEFAULT 5,
    is_active INTEGER NOT NULL DEFAULT 1,
    last_run TEXT,
    last_status TEXT,
    last_error TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS execution_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    database_id INTEGER NOT NULL,
    executed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL,
    response TEXT,
    error TEXT,
    duration_ms INTEGER,
    FOREIGN KEY (database_id) REFERENCES databases(id) ON DELETE CASCADE
  );
`);

// Middleware
app.use(cors());
app.use(express.json());
app.use(express.static('public'));
app.use(session({
  secret: process.env.SESSION_SECRET || 'supabase-scheduler-secret-key-2024',
  resave: false,
  saveUninitialized: false,
  cookie: {
    secure: false, // true in production con HTTPS
    maxAge: 24 * 60 * 60 * 1000 // 24 ore
  }
}));

// Middleware auth check
const requireAuth = (req, res, next) => {
  if (req.session && req.session.authenticated) {
    next();
  } else {
    res.status(401).json({ error: 'Non autenticato' });
  }
};

// ============ AUTH ROUTES ============

app.post('/api/login', (req, res) => {
  const { username, password } = req.body;

  if (username === AUTH_USERNAME && password === AUTH_PASSWORD) {
    req.session.authenticated = true;
    req.session.username = username;
    res.json({ success: true, message: 'Login effettuato' });
  } else {
    res.status(401).json({ error: 'Credenziali non valide' });
  }
});

app.post('/api/logout', (req, res) => {
  req.session.destroy();
  res.json({ success: true, message: 'Logout effettuato' });
});

app.get('/api/auth/status', (req, res) => {
  res.json({
    authenticated: !!(req.session && req.session.authenticated),
    username: req.session?.username || null
  });
});

// ============ DATABASE CONFIG ROUTES ============

app.get('/api/databases', requireAuth, (req, res) => {
  try {
    const databases = db.prepare('SELECT * FROM databases ORDER BY created_at DESC').all();
    res.json(databases);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.get('/api/databases/:id', requireAuth, (req, res) => {
  try {
    const database = db.prepare('SELECT * FROM databases WHERE id = ?').get(req.params.id);
    if (!database) {
      return res.status(404).json({ error: 'Database non trovato' });
    }
    res.json(database);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.post('/api/databases', requireAuth, (req, res) => {
  const { name, supabase_url, anon_key, query, frequency_minutes, is_active } = req.body;

  if (!name || !supabase_url || !anon_key || !query) {
    return res.status(400).json({ error: 'Campi obbligatori mancanti' });
  }

  try {
    const stmt = db.prepare(`
      INSERT INTO databases (name, supabase_url, anon_key, query, frequency_minutes, is_active)
      VALUES (?, ?, ?, ?, ?, ?)
    `);
    const result = stmt.run(name, supabase_url, anon_key, query, frequency_minutes || 5, is_active !== false ? 1 : 0);

    const newDb = db.prepare('SELECT * FROM databases WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(newDb);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.put('/api/databases/:id', requireAuth, (req, res) => {
  const { name, supabase_url, anon_key, query, frequency_minutes, is_active } = req.body;

  try {
    const existing = db.prepare('SELECT * FROM databases WHERE id = ?').get(req.params.id);
    if (!existing) {
      return res.status(404).json({ error: 'Database non trovato' });
    }

    const stmt = db.prepare(`
      UPDATE databases
      SET name = ?, supabase_url = ?, anon_key = ?, query = ?,
          frequency_minutes = ?, is_active = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?
    `);
    stmt.run(
      name || existing.name,
      supabase_url || existing.supabase_url,
      anon_key || existing.anon_key,
      query || existing.query,
      frequency_minutes || existing.frequency_minutes,
      is_active !== undefined ? (is_active ? 1 : 0) : existing.is_active,
      req.params.id
    );

    const updated = db.prepare('SELECT * FROM databases WHERE id = ?').get(req.params.id);
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.delete('/api/databases/:id', requireAuth, (req, res) => {
  try {
    const existing = db.prepare('SELECT * FROM databases WHERE id = ?').get(req.params.id);
    if (!existing) {
      return res.status(404).json({ error: 'Database non trovato' });
    }

    db.prepare('DELETE FROM databases WHERE id = ?').run(req.params.id);
    res.json({ success: true, message: 'Database eliminato' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ EXECUTION LOGS ============

app.get('/api/databases/:id/logs', requireAuth, (req, res) => {
  try {
    const logs = db.prepare(`
      SELECT * FROM execution_logs
      WHERE database_id = ?
      ORDER BY executed_at DESC
      LIMIT 50
    `).all(req.params.id);
    res.json(logs);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ MANUAL EXECUTION ============

app.post('/api/databases/:id/execute', requireAuth, async (req, res) => {
  try {
    const database = db.prepare('SELECT * FROM databases WHERE id = ?').get(req.params.id);
    if (!database) {
      return res.status(404).json({ error: 'Database non trovato' });
    }

    const result = await executeQuery(database);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ SCHEDULER STATUS ============

app.get('/api/scheduler/status', requireAuth, (req, res) => {
  const activeCount = db.prepare('SELECT COUNT(*) as count FROM databases WHERE is_active = 1').get();
  const totalCount = db.prepare('SELECT COUNT(*) as count FROM databases').get();

  res.json({
    running: true,
    active_databases: activeCount.count,
    total_databases: totalCount.count,
    check_interval: '1 minute'
  });
});

// ============ QUERY EXECUTION LOGIC ============

async function executeQuery(database) {
  const startTime = Date.now();
  let status = 'success';
  let response = null;
  let error = null;

  try {
    // Costruisci URL per Supabase REST API
    const url = `${database.supabase_url}/rest/v1/${database.query}`;

    const fetchResponse = await fetch(url, {
      method: 'GET',
      headers: {
        'apikey': database.anon_key,
        'Authorization': `Bearer ${database.anon_key}`,
        'Content-Type': 'application/json',
        'Prefer': 'return=representation'
      }
    });

    const data = await fetchResponse.json();

    if (!fetchResponse.ok) {
      throw new Error(data.message || data.error || `HTTP ${fetchResponse.status}`);
    }

    response = JSON.stringify(data).substring(0, 5000); // Limita dimensione log

  } catch (err) {
    status = 'error';
    error = err.message;
  }

  const duration = Date.now() - startTime;

  // Salva log esecuzione
  db.prepare(`
    INSERT INTO execution_logs (database_id, status, response, error, duration_ms)
    VALUES (?, ?, ?, ?, ?)
  `).run(database.id, status, response, error, duration);

  // Aggiorna stato database
  db.prepare(`
    UPDATE databases
    SET last_run = CURRENT_TIMESTAMP, last_status = ?, last_error = ?
    WHERE id = ?
  `).run(status, error, database.id);

  return { status, response: response ? JSON.parse(response) : null, error, duration_ms: duration };
}

// ============ SCHEDULER ============

// Controlla ogni minuto quali query eseguire
cron.schedule('* * * * *', async () => {
  const now = new Date();
  console.log(`[${now.toISOString()}] Scheduler check...`);

  const databases = db.prepare('SELECT * FROM databases WHERE is_active = 1').all();

  for (const database of databases) {
    const lastRun = database.last_run ? new Date(database.last_run) : null;
    const frequencyMs = database.frequency_minutes * 60 * 1000;

    // Esegui se mai eseguito o se passato tempo sufficiente
    if (!lastRun || (now - lastRun) >= frequencyMs) {
      console.log(`[${now.toISOString()}] Executing query for: ${database.name}`);
      try {
        await executeQuery(database);
      } catch (err) {
        console.error(`[${now.toISOString()}] Error executing ${database.name}:`, err.message);
      }
    }
  }
});

console.log('[SCHEDULER] Cron scheduler avviato - controllo ogni minuto');

// ============ HEALTH & INFO ============

app.get('/api/health', (req, res) => {
  res.json({
    status: 'ok',
    timestamp: new Date().toISOString(),
    uptime: process.uptime()
  });
});

// 404 handler
app.use((req, res) => {
  // Se richiesta API, ritorna JSON
  if (req.path.startsWith('/api/')) {
    return res.status(404).json({ error: 'Endpoint non trovato' });
  }
  // Altrimenti serve index.html per SPA routing
  res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

// Error handler
app.use((err, req, res, next) => {
  console.error(err.stack);
  res.status(500).json({ error: 'Errore interno del server' });
});

app.listen(PORT, '0.0.0.0', () => {
  console.log(`[SERVER] Running on port ${PORT}`);
  console.log(`[SERVER] Environment: ${process.env.NODE_ENV || 'development'}`);
});