# Database Schema Reference

## SQLite Setup with better-sqlite3

```javascript
import Database from 'better-sqlite3'
import { mkdirSync } from 'node:fs'
import { dirname } from 'node:path'

const DB_PATH = './data/app.db'
mkdirSync(dirname(DB_PATH), { recursive: true })

const db = new Database(DB_PATH)
db.pragma('journal_mode = WAL')   // CRITICAL: enables concurrent reads
db.pragma('foreign_keys = ON')    // Enforce FK constraints
```

## Core Tables (adapt names/columns to your domain)

### Sessions (one per WhatsApp user)
```sql
CREATE TABLE IF NOT EXISTS sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  phone TEXT NOT NULL UNIQUE,
  push_name TEXT DEFAULT '',
  state TEXT NOT NULL DEFAULT 'idle',
  -- Domain-specific persistent data:
  api_key TEXT,
  reference_data TEXT,          -- base64 data URI or JSON
  last_input TEXT,
  config_json TEXT DEFAULT '{}',
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_sessions_phone ON sessions(phone);
CREATE INDEX IF NOT EXISTS idx_sessions_state ON sessions(state);
```

### Tasks/Generations (one per AI job)
```sql
CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id INTEGER NOT NULL,
  phone TEXT NOT NULL,
  input_text TEXT NOT NULL,
  status TEXT DEFAULT 'pending',   -- pending, processing, completed, failed
  results_count INTEGER DEFAULT 0,
  error TEXT,
  started_at TEXT,
  completed_at TEXT,
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (session_id) REFERENCES sessions(id)
);
CREATE INDEX IF NOT EXISTS idx_tasks_session ON tasks(session_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
```

### Results (individual outputs from AI)
```sql
CREATE TABLE IF NOT EXISTS results (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id INTEGER NOT NULL,
  variation TEXT NOT NULL,
  label TEXT NOT NULL,
  sent INTEGER DEFAULT 0,       -- 1 if delivered to WhatsApp
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (task_id) REFERENCES tasks(id)
);
CREATE INDEX IF NOT EXISTS idx_results_task ON results(task_id);
```

### Settings (admin-configurable)
```sql
CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT DEFAULT (datetime('now'))
);
```

### Daily Stats
```sql
CREATE TABLE IF NOT EXISTS daily_stats (
  date TEXT NOT NULL PRIMARY KEY,
  total_users INTEGER DEFAULT 0,
  total_tasks INTEGER DEFAULT 0,
  total_results INTEGER DEFAULT 0,
  total_errors INTEGER DEFAULT 0
);
```

## Default Settings

```javascript
const DEFAULTS = {
  bot_active: '1',
  admin_api_key: '',
  max_daily_per_user: '5',
  max_daily_global: '100',
  welcome_message: 'Hello {name}! Send #start to begin.',
  processing_message: 'Processing your request... please wait.',
  done_message: 'Done! Send another request or #start for a new session.',
  rate_limit_message: 'Daily limit reached. Try again tomorrow.',
  error_message: 'Something went wrong. Please try again.',
}

function initSettings() {
  const insert = db.prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)')
  for (const [key, value] of Object.entries(DEFAULTS)) {
    insert.run(key, value)
  }
}
```

## CRUD Functions

```javascript
// Sessions
export function getSession(phone) {
  return db.prepare('SELECT * FROM sessions WHERE phone = ?').get(phone)
}

export function createSession(phone, pushName) {
  db.prepare('INSERT INTO sessions (phone, push_name) VALUES (?, ?)').run(phone, pushName)
  return getSession(phone)
}

export function updateSession(phone, fields) {
  const sets = Object.keys(fields).map(k => `${k} = ?`).join(', ')
  const values = Object.values(fields)
  db.prepare(`UPDATE sessions SET ${sets}, updated_at = datetime('now') WHERE phone = ?`)
    .run(...values, phone)
}

// Tasks
export function createTask(sessionId, phone, inputText) {
  const result = db.prepare(
    'INSERT INTO tasks (session_id, phone, input_text, started_at) VALUES (?, ?, ?, datetime(\'now\'))'
  ).run(sessionId, phone, inputText)
  return result.lastInsertRowid
}

export function updateTask(taskId, fields) {
  const sets = Object.keys(fields).map(k => `${k} = ?`).join(', ')
  db.prepare(`UPDATE tasks SET ${sets} WHERE id = ?`).run(...Object.values(fields), taskId)
}

// Results
export function saveResult(taskId, variation, label, sent = 0) {
  db.prepare('INSERT INTO results (task_id, variation, label, sent) VALUES (?, ?, ?, ?)')
    .run(taskId, variation, label, sent)
}

// Settings
export function getSetting(key) {
  const row = db.prepare('SELECT value FROM settings WHERE key = ?').get(key)
  return row?.value ?? null
}

export function updateSetting(key, value) {
  db.prepare('INSERT OR REPLACE INTO settings (key, value, updated_at) VALUES (?, ?, datetime(\'now\'))')
    .run(key, value)
}

// Rate Limiting
export function getUserDailyCount(phone) {
  const row = db.prepare(
    "SELECT COUNT(*) as count FROM tasks WHERE phone = ? AND date(created_at) = date('now')"
  ).get(phone)
  return row?.count || 0
}

// Daily Stats
export function incrementDailyStats(field) {
  const today = new Date().toISOString().split('T')[0]
  db.prepare(`INSERT INTO daily_stats (date, ${field}) VALUES (?, 1)
    ON CONFLICT(date) DO UPDATE SET ${field} = ${field} + 1`).run(today)
}

export function getDailyStats(days = 14) {
  return db.prepare(
    `SELECT * FROM daily_stats WHERE date >= date('now', '-' || ? || ' days') ORDER BY date`
  ).all(days)
}
```
