src/db/database.ts

import sqlite3 from 'sqlite3';
import { promisify } from 'util';
import path from 'path';
import fs from 'fs';

// Enable verbose mode for debugging
const Database = sqlite3.verbose().Database;

class DatabaseConnection {
  private db: sqlite3.Database | null = null;

  async connect(): Promise<sqlite3.Database> {
    if (this.db) {
      return this.db;
    }

    const dbPath = process.env.DATABASE_URL || path.join(__dirname, '../../database.sqlite');
    
    // Ensure directory exists
    const dbDir = path.dirname(dbPath);
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true });
    }

    return new Promise((resolve, reject) => {
      this.db = new Database(dbPath, (err) => {
        if (err) {
          console.error('Error opening database:', err.message);
          reject(err);
        } else {
          console.log('Connected to SQLite database');
          resolve(this.db!);
        }
      });
    });
  }

  async query(sql: string, params: any[] = []): Promise<any[]> {
    const db = await this.connect();
    return new Promise((resolve, reject) => {
      db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve(rows || []);
      });
    });
  }

  async run(sql: string, params: any[] = []): Promise<{ lastID: number; changes: number }> {
    const db = await this.connect();
    return new Promise((resolve, reject) => {
      db.run(sql, params, function(err) {
        if (err) {
          reject(err);
        } else {
          resolve({ 
            lastID: this.lastID || 0, 
            changes: this.changes || 0 
          });
        }
      });
    });
  }

  async get(sql: string, params: any[] = []): Promise<any> {
    const db = await this.connect();
    return new Promise((resolve, reject) => {
      db.get(sql, params, (err, row) => {
        if (err) reject(err);
        else resolve(row);
      });
    });
  }

  async close(): Promise<void> {
    if (this.db) {
      return new Promise((resolve, reject) => {
        this.db!.close((err) => {
          if (err) {
            reject(err);
          } else {
            this.db = null;
            resolve();
          }
        });
      });
    }
  }
}

// Singleton instance
export const database = new DatabaseConnection();

// Database initialization function
export async function initializeDatabase(): Promise<void> {
  const createTables = [
    // Users table
    `CREATE TABLE IF NOT EXISTS users (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      email TEXT UNIQUE NOT NULL,

      name TEXT NOT NULL,

      password_hash TEXT,

      avatar TEXT,

      google_id TEXT UNIQUE,

      email_verified BOOLEAN DEFAULT FALSE,

      verification_token TEXT,

      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP

    )`,

    // Tenants table
    `CREATE TABLE IF NOT EXISTS tenants (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      name TEXT NOT NULL,

      subdomain TEXT UNIQUE,

      plan TEXT DEFAULT 'starter',

      settings TEXT DEFAULT '{}',

      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP

    )`,

    // User-Tenant relationships
    `CREATE TABLE IF NOT EXISTS user_tenants (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      user_id INTEGER NOT NULL,

      tenant_id INTEGER NOT NULL,

      role TEXT DEFAULT 'owner',

      permissions TEXT DEFAULT '{}',

      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,

      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE,

      UNIQUE(user_id, tenant_id)

    )`,

    // Domains table
    `CREATE TABLE IF NOT EXISTS domains (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      tenant_id INTEGER NOT NULL,

      domain TEXT NOT NULL,

      verified BOOLEAN DEFAULT FALSE,

      verification_token TEXT,

      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE

    )`,

    // Knowledge base table
    `CREATE TABLE IF NOT EXISTS knowledge_base (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      tenant_id INTEGER NOT NULL,

      name TEXT NOT NULL,

      type TEXT NOT NULL,

      source TEXT NOT NULL,

      status TEXT DEFAULT 'processing',

      size INTEGER,

      metadata TEXT DEFAULT '{}',

      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE

    )`,

    // Chat sessions table
    `CREATE TABLE IF NOT EXISTS chat_sessions (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      tenant_id INTEGER NOT NULL,

      domain TEXT,

      user_ip TEXT,

      user_agent TEXT,

      session_token TEXT UNIQUE NOT NULL,

      started_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      ended_at DATETIME,

      resolved BOOLEAN DEFAULT FALSE,

      rating INTEGER,

      feedback TEXT,

      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE

    )`,

    // Chat messages table
    `CREATE TABLE IF NOT EXISTS chat_messages (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      session_id INTEGER NOT NULL,

      sender TEXT NOT NULL, -- 'user' or 'ai'

      message TEXT NOT NULL,

      metadata TEXT DEFAULT '{}',

      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,

      FOREIGN KEY (session_id) REFERENCES chat_sessions (id) ON DELETE CASCADE

    )`,

    // Analytics events table
    `CREATE TABLE IF NOT EXISTS analytics_events (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      tenant_id INTEGER NOT NULL,

      event_type TEXT NOT NULL,

      event_data TEXT DEFAULT '{}',

      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,

      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE

    )`,

    // Widget configurations table
    `CREATE TABLE IF NOT EXISTS widget_configs (

      id INTEGER PRIMARY KEY AUTOINCREMENT,

      tenant_id INTEGER NOT NULL,

      config TEXT NOT NULL DEFAULT '{}',

      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

      FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE

    )`
  ];

  const createIndexes = [
    `CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`,
    `CREATE INDEX IF NOT EXISTS idx_users_google_id ON users(google_id)`,
    `CREATE INDEX IF NOT EXISTS idx_tenants_subdomain ON tenants(subdomain)`,
    `CREATE INDEX IF NOT EXISTS idx_user_tenants_user_id ON user_tenants(user_id)`,
    `CREATE INDEX IF NOT EXISTS idx_user_tenants_tenant_id ON user_tenants(tenant_id)`,
    `CREATE INDEX IF NOT EXISTS idx_domains_tenant_id ON domains(tenant_id)`,
    `CREATE INDEX IF NOT EXISTS idx_knowledge_base_tenant_id ON knowledge_base(tenant_id)`,
    `CREATE INDEX IF NOT EXISTS idx_chat_sessions_tenant_id ON chat_sessions(tenant_id)`,
    `CREATE INDEX IF NOT EXISTS idx_chat_sessions_token ON chat_sessions(session_token)`,
    `CREATE INDEX IF NOT EXISTS idx_chat_messages_session_id ON chat_messages(session_id)`,
    `CREATE INDEX IF NOT EXISTS idx_analytics_events_tenant_id ON analytics_events(tenant_id)`,
    `CREATE INDEX IF NOT EXISTS idx_analytics_events_timestamp ON analytics_events(timestamp)`
  ];

  try {
    // Create tables
    for (const sql of createTables) {
      await database.run(sql);
    }

    // Create indexes
    for (const sql of createIndexes) {
      await database.run(sql);
    }

    console.log('Database tables and indexes created successfully');
  } catch (error) {
    console.error('Error initializing database:', error);
    throw error;
  }
}