packages/server/src/db/index.ts

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { config } from '../config/env.js';
import * as schema from './schema.js';

const sqlite = new Database(config.DATABASE_URL);
sqlite.pragma('journal_mode = WAL');

// Create tables if they don't exist
sqlite.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    google_id TEXT UNIQUE,
    email TEXT UNIQUE NOT NULL,
    name TEXT,
    avatar_url TEXT,
    access_token TEXT,
    refresh_token TEXT,
    token_expires TEXT,
    role TEXT DEFAULT 'viewer',
    created_at TEXT,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS transactions (
    id TEXT PRIMARY KEY,
    email_id TEXT UNIQUE NOT NULL,
    user_id TEXT,
    date TEXT NOT NULL,
    sender TEXT NOT NULL,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'CAD',
    reference TEXT,
    message TEXT,
    recipient_email TEXT,
    branch TEXT,
    status TEXT,
    raw_email TEXT,
    raw_email_html TEXT,
    screenshot_original TEXT,
    screenshot_preview TEXT,
    parsed_at TEXT,
    reviewed INTEGER DEFAULT 0,
    reviewed_by TEXT,
    created_at TEXT
  );

  CREATE TABLE IF NOT EXISTS branch_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    branch TEXT NOT NULL,
    active INTEGER DEFAULT 1,
    updated_at TEXT
  );

  CREATE TABLE IF NOT EXISTS scan_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    scan_preset TEXT,
    scan_start_date TEXT NOT NULL,
    scan_end_date TEXT NOT NULL,
    force_rescan INTEGER DEFAULT 0,
    started_at TEXT NOT NULL,
    finished_at TEXT,
    emails_found INTEGER DEFAULT 0,
    emails_parsed INTEGER DEFAULT 0,
    emails_skipped INTEGER DEFAULT 0,
    errors INTEGER DEFAULT 0,
    error_details TEXT,
    ai_provider TEXT,
    ai_model TEXT,
    created_at TEXT
  );

  CREATE TABLE IF NOT EXISTS ai_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    provider TEXT NOT NULL,
    model TEXT NOT NULL,
    api_key TEXT,
    base_url TEXT,
    is_active INTEGER DEFAULT 1,
    created_at TEXT
  );

  CREATE TABLE IF NOT EXISTS envelopes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    envelope_number TEXT NOT NULL,
    business_name TEXT,
    name TEXT NOT NULL,
    email TEXT
  );
`);

// Authoritative branch list — only these 20 branches should exist
const now = new Date().toISOString();
const branchSeeds: [string, string][] = [
  ['finances@iccameriques.org', 'Montreal'],
  ['gatineauottawa.finances@iccameriques.org', 'Gatineau'],
  ['moncton.finances@iccameriques.org', 'Moncton'],
  ['chibougamau.finances@iccameriques.org', 'Chibougamau'],
  ['trois-rivieres.finances@iccameriques.org', 'Trois-Rivieres'],
  ['sherbrooke.finances@iccameriques.org', 'Sherbrooke'],
  ['toronto.finances@iccameriques.org', 'Toronto'],
  ['quebec.finances@iccameriques.org', 'Quebec'],
  ['lvl.finances@iccameriques.org', 'Laval'],
  ['lgl.finances@iccameriques.org', 'Longueuil'],
  ['victoriaville.finances@iccameriques.org', 'Victoriaville'],
  ['valleyfield.finances@iccameriques.org', 'Valleyfield'],
  ['finances.rdl@iccameriques.org', 'Riviere-du-Loup'],
  ['ottawa.finances@iccameriques.org', 'Ottawa'],
  ['kingston.finances@iccameriques.org', 'Kingston'],
  ['hamilton.finances@iccameriques.org', 'Hamilton'],
  ['edmonton.finances@iccameriques.org', 'Edmonton'],
  ['shawinigan.finances@iccameriques.org', 'Shawinigan'],
  ['timmins.finances@iccameriques.org', 'Timmins'],
  ['sudbury.finances@iccameriques.org', 'Sudbury'],
  ['sthy.finances@iccameriques.org', 'Saint-Hyacinthe'],
];
const seedEmails = new Set(branchSeeds.map(([e]) => e));
// Remove any branches not in the authoritative list
const allBranches = sqlite.prepare('SELECT id, email FROM branch_config').all() as { id: number; email: string }[];
const deleteBranch = sqlite.prepare('DELETE FROM branch_config WHERE id = ?');
const insertBranch = sqlite.prepare(
  'INSERT OR IGNORE INTO branch_config (email, branch, active, updated_at) VALUES (?, ?, 1, ?)'
);
const seedTx = sqlite.transaction(() => {
  for (const row of allBranches) {
    if (!seedEmails.has(row.email)) {
      deleteBranch.run(row.id);
    }
  }
  for (const [email, branch] of branchSeeds) {
    insertBranch.run(email, branch, now);
  }
});
seedTx();

export const db = drizzle(sqlite, { schema });
export { sqlite, schema };

// Safe migration — add columns that may not exist yet
try { sqlite.exec('ALTER TABLE transactions ADD COLUMN raw_email_html TEXT'); } catch {}
try { sqlite.exec('ALTER TABLE transactions ADD COLUMN screenshot_original TEXT'); } catch {}
try { sqlite.exec('ALTER TABLE transactions ADD COLUMN screenshot_preview TEXT'); } catch {}
try { sqlite.exec('ALTER TABLE transactions ADD COLUMN envelope_number TEXT'); } catch {}

// Seed envelopes from Google Sheets (async — runs after module load)
const GSHEET_CSV_URL = 'https://docs.google.com/spreadsheets/d/1dOxxBASqevE7Kwnb_xl9x_BX7DLKOHy225s-3lQq3aQ/export?format=csv';

function parseCsvLine(line: string): string[] {
  const cols: string[] = [];
  let current = '';
  let inQuotes = false;
  for (let i = 0; i < line.length; i++) {
    const ch = line[i];
    if (ch === '"') { inQuotes = !inQuotes; continue; }
    if (ch === ',' && !inQuotes) { cols.push(current.trim()); current = ''; continue; }
    current += ch;
  }
  cols.push(current.trim());
  return cols;
}

function seedEnvelopesFromCsv(csvContent: string): number {
  const allLines = csvContent.split('\n');
  if (allLines.length < 2) return 0;

  // Detect column indices from header row
  const headers = parseCsvLine(allLines[0]).map(h => h.toLowerCase().trim());
  const envIdx = headers.findIndex(h => h.includes('envelope'));
  const nameIdx = headers.findIndex(h => h === 'name' || h === 'nom');
  const bizIdx = headers.findIndex(h => h.includes('business'));
  const emailIdx = headers.findIndex(h => h.includes('email'));

  if (envIdx === -1 || nameIdx === -1) {
    console.error('[DB] CSV missing required columns. Headers:', headers);
    return 0;
  }

  const lines = allLines.slice(1); // skip header
  const insertEnvelope = sqlite.prepare(
    'INSERT INTO envelopes (envelope_number, business_name, name, email) VALUES (?, ?, ?, ?)'
  );
  let count = 0;
  sqlite.exec('DELETE FROM envelopes');
  const seedTxn = sqlite.transaction(() => {
    for (const line of lines) {
      const cols = parseCsvLine(line);
      const envNum = cols[envIdx] || '';
      const name = cols[nameIdx] || '';
      const bizName = bizIdx >= 0 ? cols[bizIdx] || null : null;
      const email = emailIdx >= 0 ? cols[emailIdx] || null : null;
      if (!name || !envNum) continue;
      insertEnvelope.run(envNum, bizName, name, email);
      count++;
    }
  });
  seedTxn();
  return count;
}

export async function syncEnvelopesFromSheet(): Promise<number> {
  try {
    const res = await fetch(GSHEET_CSV_URL);
    if (!res.ok) throw new Error(`HTTP ${res.status}`);
    const csvContent = await res.text();
    const count = seedEnvelopesFromCsv(csvContent);
    console.log(`[DB] Synced ${count} envelopes from Google Sheets`);
    // Clear the envelope cache so lookups use fresh data
    try {
      const { clearEnvelopeCache } = await import('../services/envelopeService.js');
      clearEnvelopeCache();
    } catch {}
    return count;
  } catch (err) {
    console.error(`[DB] Failed to sync envelopes from Google Sheets:`, err);
    return -1;
  }
}

// Initial sync on startup (non-blocking)
const envelopeCount = (sqlite.prepare('SELECT COUNT(*) as cnt FROM envelopes').get() as { cnt: number }).cnt;
if (envelopeCount === 0) {
  syncEnvelopesFromSheet();
} else {
  // Already have data — refresh in background after 5s to pick up sheet changes
  setTimeout(() => syncEnvelopesFromSheet(), 5000);
}