SQLite Adapter

export class SQLiteAdapter implements TAdapter {
  private db: any = null

  constructor(private url: string) {}

  async connect(): Promise<void> {
    throw new Error('SQLite adapter requires better-sqlite3. Install: bun add better-sqlite3')
  }

  async disconnect(): Promise<void> {
    if (this.db) {
      this.db.close()
      this.db = null
    }
  }

  async insertEvent(event: TEvent): Promise<void> {
    throw new Error('Not implemented')
  }

  async insertSession(session: TSession): Promise<void> {
    throw new Error('Not implemented')
  }

  // ... all other methods throw "Not implemented"
}

bun add better-sqlite3

import { SQLiteAdapter } from 'honolytics/storage'

// This will currently throw an error
const adapter = new SQLiteAdapter('./analytics.db')
await adapter.connect() // Error: Not implemented

// Planned implementation
async connect(): Promise<void> {
  try {
    const Database = await import('better-sqlite3')
    this.db = new Database(this.url)
    
    // Run schema migrations
    await this.runMigrations()
    
  } catch (error) {
    if (error.code === 'MODULE_NOT_FOUND') {
      throw new Error('SQLite adapter requires better-sqlite3. Install: bun add better-sqlite3')
    }
    throw error
  }
}

-- Required tables and indices
CREATE TABLE IF NOT EXISTS events (
  id TEXT PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  user_id TEXT,
  session_id TEXT NOT NULL,
  url TEXT NOT NULL,
  event TEXT NOT NULL,
  user_agent TEXT,
  ip TEXT,
  referrer TEXT,
  duration INTEGER,
  meta TEXT
);

CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
CREATE INDEX IF NOT EXISTS idx_events_session_id ON events(session_id);
CREATE INDEX IF NOT EXISTS idx_events_user_id ON events(user_id);

CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  start_time INTEGER NOT NULL,
  end_time INTEGER,
  pageviews INTEGER NOT NULL DEFAULT 0,
  duration INTEGER
);

CREATE INDEX IF NOT EXISTS idx_sessions_start_time ON sessions(start_time);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);

// Example query implementation needed
async queryMetrics(start: Date, end: Date): Promise<TMetric[]> {
  const startMs = start.getTime()
  const endMs = end.getTime()
  
  const stmt = this.db.prepare(`
    SELECT 
      DATE(datetime(timestamp / 1000, 'unixepoch')) as date,
      COUNT(DISTINCT user_id) as users,
      COUNT(DISTINCT session_id) as sessions,
      COUNT(CASE WHEN event = 'pageview' THEN 1 END) as pageviews
    FROM events 
    WHERE timestamp >= ? AND timestamp <= ?
    GROUP BY DATE(datetime(timestamp / 1000, 'unixepoch'))
    ORDER BY date
  `)
  
  const rows = stmt.all(startMs, endMs)
  
  return rows.map(row => ({
    date: row.date,
    users: row.users,
    sessions: row.sessions,
    pageviews: row.pageviews
  }))
}

// Example insertion implementation needed
async insertEvent(event: TEvent): Promise<void> {
  const stmt = this.db.prepare(`
    INSERT OR REPLACE INTO events (
      id, timestamp, user_id, session_id, url, event,
      user_agent, ip, referrer, duration, meta
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `)
  
  stmt.run(
    event.id,
    event.timestamp,
    event.userId || null,
    event.sessionId,
    event.url,
    event.event,
    event.userAgent || null,
    event.ip || null,
    event.referrer || null,
    event.duration || null,
    event.meta ? JSON.stringify(event.meta) : null
  )
}

// Server-side analytics service
import { SQLiteAdapter } from 'honolytics/storage'

class AnalyticsService {
  private adapter = new SQLiteAdapter('./analytics.db')
  
  async init() {
    await this.adapter.connect()
  }
  
  async trackEvent(event: TEvent) {
    await this.adapter.insertEvent(event)
  }
  
  async getMetrics(start: Date, end: Date) {
    return await this.adapter.queryFullMetrics(start, end)
  }
}

// API endpoint
export async function POST(request: Request) {
  const event = await request.json()
  await analyticsService.trackEvent(event)
  return Response.json({ success: true })
}

// Migrate from IndexedDB to SQLite (server-side)
const sourceAdapter = new IndexDBAdapter()
const targetAdapter = new SQLiteAdapter('./analytics.db')

await sourceAdapter.connect()
await targetAdapter.connect()

// Export data from browser
const events = await sourceAdapter.queryEvents({})
const sessions = await sourceAdapter.getAllSessions()

// Import to SQLite
for (const event of events) {
  await targetAdapter.insertEvent(event)
}

for (const session of sessions) {
  await targetAdapter.insertSession(session)
}