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

const isProduction = process.env.NODE_ENV === 'production';
const dbPath = isProduction 
  ? '/app/data/space_data.db' 
  : path.resolve(process.cwd(), 'data/space_data.db');

console.log(`[DB] Using database at: ${dbPath}`);

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

const db = new Database(dbPath);

db.exec(`
  CREATE TABLE IF NOT EXISTS iss_passes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pass_time TEXT NOT NULL,
    end_time TEXT NOT NULL
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS global_events (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    event_time TEXT NOT NULL
  );
`);

try {
  const count = db.prepare("SELECT count(*) as total FROM global_events").get() as { total: number };
  console.log(`[DB] Connected. Found ${count.total} events.`);
} catch (e) {
  console.log(`[DB] Table not ready yet or empty.`);
}

export default db;