server.js

import express from 'express';
import Database from 'better-sqlite3';
import cors from 'cors';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { dirname } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

// read config file
function loadConfig() {
  try {
    const configPath = path.join(__dirname, 'config.env');
    console.log(configPath);
    if (fs.existsSync(configPath)) {
      const configContent = fs.readFileSync(configPath, 'utf8');
      const config = {};
      
      configContent.split('\n').forEach(line => {
        const [key, value] = line.split('=');
        if (key && value && !key.startsWith('#')) {
          config[key.trim()] = value.trim();
        }
      });
      
      return config;
    }
  } catch (error) {
    console.error('read config file failed:', error);
  }
  
  // if config file does not exist, use environment variables
  return process.env;
}

const config = loadConfig();
console.log(config);
const app = express();
const PORT = config.PORT || 3001;

// middleware
app.use(cors());
app.use(express.json());

// SQLite database configuration
const dbPath = config.DB_FILE ;

// file download configuration
const fileFolder = config.DB_FILE_FOlDER ;

// create database instance
let db;

// initialize database connection
function initDatabase() {
  try {
    // ensure database file directory exists
    const dbDir = path.dirname(dbPath);
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true });
    }
    
    db = new Database(dbPath);
    console.log(`SQLite database connection successful: ${dbPath}`);
    
    // enable foreign key constraints
    db.pragma('foreign_keys = ON');
  } catch (error) {
    console.error('SQLite connection failed:', error);
  }
}

// test database connection
app.get('/api/test-connection', (req, res) => {
  try {
    if (!db) {
      throw new Error('database not initialized');
    }
    
    // execute simple query to test connection
    const result = db.prepare('SELECT 1 as test').get();
    res.json({ success: true, message: 'SQLite database connection successful', data: result });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
});

// execute query
app.post('/api/query', (req, res) => {
  try {
    const { sql, params = [] } = req.body;
    if (!sql) {
      return res.status(400).json({ success: false, error: 'SQL statement cannot be empty' });
    }

    if (!db) {
      return res.status(500).json({ success: false, error: 'database not initialized' });
    }

    // check if it is a SELECT query
    const trimmedSql = sql.trim().toLowerCase();
    if (trimmedSql.startsWith('select') || trimmedSql.startsWith('with')) {
      // SELECT query
      const stmt = db.prepare(sql);
      const rows = stmt.all(params);
      res.json({ success: true, data: rows });
    } else {
      // other queries (INSERT, UPDATE, DELETE, etc.)
      const stmt = db.prepare(sql);
      const result = stmt.run(params);
      res.json({ 
        success: true, 
        data: {
          changes: result.changes,
          lastInsertRowid: result.lastInsertRowid
        }
      });
    }
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
});

// get table list
app.get('/api/tables', (req, res) => {
  try {
    if (!db) {
      return res.status(500).json({ success: false, error: 'database not initialized' });
    }

    const stmt = db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name NOT LIKE 'sqlite_%'
      ORDER BY name
    `);
    const rows = stmt.all();
    const tables = rows.map(row => row.name);
    res.json({ success: true, data: tables });
  } catch (error) {
    res.status(500).json({ success: false, error: error.message });
  }
});

// download file interface
app.post('/api/download', (req, res) => {
  try {
    const { filePath } = req.body;
    
    if (!filePath) {
      return res.status(400).json({ success: false, error: 'file path cannot be empty' });
    }

    // build full file path
    const fullPath = path.join(fileFolder, filePath);
    
    // security check: ensure file path is within allowed directory
    const resolvedPath = path.resolve(fullPath);
    const resolvedFolder = path.resolve(fileFolder);
    
    if (!resolvedPath.startsWith(resolvedFolder)) {
      return res.status(403).json({ success: false, error: 'access denied: file path exceeds allowed range' });
    }

    // check if file exists
    if (!fs.existsSync(resolvedPath)) {
      return res.status(404).json({ success: false, error: 'file not found' });
    }

    // check if file is npz format
    if (!filePath.toLowerCase().endsWith('.npz')) {
      return res.status(400).json({ success: false, error: 'only .npz files are supported' });
    }

    // get file statistics
    const stats = fs.statSync(resolvedPath);
    
    // set response headers
    res.setHeader('Content-Type', 'application/octet-stream');
    res.setHeader('Content-Disposition', `attachment; filename="${path.basename(filePath)}"`);
    res.setHeader('Content-Length', stats.size);
    res.setHeader('Cache-Control', 'no-cache');

    // create file stream and send
    const fileStream = fs.createReadStream(resolvedPath);
    
    fileStream.on('error', (error) => {
      console.error('file read error:', error);
      if (!res.headersSent) {
        res.status(500).json({ success: false, error: 'file read failed' });
      }
    });

    fileStream.pipe(res);

  } catch (error) {
    console.error('download file error:', error);
    if (!res.headersSent) {
      res.status(500).json({ success: false, error: error.message });
    }
  }
});

// start server
app.listen(PORT, () => {
  console.log(`server running on port ${PORT}`);
  initDatabase();
});

export default app;