initialize-db.js

// Permissive "Relaxed" Database Schema for Better Auth
// This version removes NOT NULL constraints from all columns except ID 
// to prevent "NOT NULL constraint failed" errors while we determine 
// the exact naming convention Better Auth is using on your system.

const Database = require('better-sqlite3');
const path = require('path');

const dbPath = path.join(__dirname, 'sqlite.db');
const db = new Database(dbPath);

console.log('🚀 Starting "Relaxed" Database Repair...');

try {
  db.pragma('journal_mode = WAL');

  // 1. User Table - All nullable except id
  console.log('Creating/Repairing "user" table...');
  db.prepare(`
        CREATE TABLE IF NOT EXISTS user (
            id TEXT PRIMARY KEY,
            name TEXT,
            email TEXT UNIQUE,
            email_verified BOOLEAN,
            emailVerified BOOLEAN,
            image TEXT,
            created_at DATETIME,
            createdAt DATETIME,
            updated_at DATETIME,
            updatedAt DATETIME,
            software_background TEXT,
            softwareBackground TEXT,
            hardware_background TEXT,
            hardwareBackground TEXT,
            robotics_experience TEXT,
            roboticsExperience TEXT
        )
    `).run();

  // 2. Account Table - All nullable except id
  console.log('Creating/Repairing "account" table...');
  db.prepare(`
        CREATE TABLE IF NOT EXISTS account (
            id TEXT PRIMARY KEY,
            user_id TEXT,
            userId TEXT,
            account_id TEXT,
            accountId TEXT,
            provider_id TEXT,
            providerId TEXT,
            access_token TEXT,
            accessToken TEXT,
            refresh_token TEXT,
            refreshToken TEXT,
            id_token TEXT,
            idToken TEXT,
            expires_at DATETIME,
            expiresAt DATETIME,
            password TEXT,
            created_at DATETIME,
            createdAt DATETIME,
            updated_at DATETIME,
            updatedAt DATETIME
        )
    `).run();

  // 3. Session Table - All nullable except id
  console.log('Creating/Repairing "session" table...');
  db.prepare(`
        CREATE TABLE IF NOT EXISTS session (
            id TEXT PRIMARY KEY,
            user_id TEXT,
            userId TEXT,
            token TEXT UNIQUE,
            expires_at DATETIME,
            expiresAt DATETIME,
            ip_address TEXT,
            ipAddress TEXT,
            user_agent TEXT,
            userAgent TEXT,
            created_at DATETIME,
            createdAt DATETIME,
            updated_at DATETIME,
            updatedAt DATETIME
        )
    `).run();

  // 4. Verification Table - All nullable except id
  console.log('Creating/Repairing "verification" table...');
  db.prepare(`
        CREATE TABLE IF NOT EXISTS verification (
            id TEXT PRIMARY KEY,
            identifier TEXT,
            value TEXT,
            expires_at DATETIME,
            expiresAt DATETIME,
            created_at DATETIME,
            createdAt DATETIME,
            updated_at DATETIME,
            updatedAt DATETIME
        )
    `).run();

  console.log('✅ Relaxed database schema is ready!');
  const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
  console.log('Current Database Map:', tables.map(t => t.name).join(', '));

} catch (error) {
  console.error('❌ Critical error during schema creation:', error);
} finally {
  db.close();
  console.log('👋 Database connection closed.');
}