src/lib/sqlite.ts

import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

// HF Spaces persistent storage is typically at /data.
// For local dev, we use ./gallery.db
const DB_PATH = process.env.DB_PATH || './gallery.db';

// Ensure directory exists
const dbDir = path.dirname(DB_PATH);
if (!fs.existsSync(dbDir)) {
  fs.mkdirSync(dbDir, { recursive: true });
}

let db: Database.Database;

try {
  db = new Database(DB_PATH);
  
  // Enable Write-Ahead Logging for better concurrency
  db.pragma('journal_mode = WAL');
  
  // Initialize tables
  db.exec(`
    CREATE TABLE IF NOT EXISTS visualizations (
      id TEXT PRIMARY KEY,
      spec TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS feedback (
      viz_id TEXT,
      user_id TEXT,
      action TEXT, -- 'like' or 'dislike'
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (viz_id, user_id)
    );

    CREATE TABLE IF NOT EXISTS comments (
      id TEXT PRIMARY KEY,
      viz_id TEXT,
      user_id TEXT,
      user_name TEXT,
      text TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
  `);

} catch (err) {
  console.error('Failed to initialize SQLite database:', err);
  throw err;
}

export default db;