JLCPCB Parts Library

#!/usr/bin/env node

/**
 * JLCPCB Local Database Setup (Shared Service Edition)
 *
 * Downloads the CDFER/jlcpcb-parts-database SQLite file and prepares it
 * for local querying (indexes, enriched FTS).
 *
 * Usage:
 *   node setup-db.js            # download if not present
 *   node setup-db.js --force    # re-download even if present
 *   node setup-db.js --rebuild-fts  # rebuild FTS index only
 */

import { existsSync, createWriteStream, unlinkSync, mkdirSync } from 'fs';
import { get } from 'https';
import { join, dirname } from 'path';
import { fileURLToPath } from 'url';
import Database from 'better-sqlite3';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_PATH = process.env.JLCPCB_DB_PATH || join(__dirname, 'jlcpcb-components.sqlite3');
const DB_URL = 'https://cdfer.github.io/jlcpcb-parts-database/jlcpcb-components.sqlite3';
const force = process.argv.includes('--force');
const rebuildFts = process.argv.includes('--rebuild-fts');

// Ensure parent directory exists (for /data/ path in container)
mkdirSync(dirname(DB_PATH), { recursive: true });

function download(url, dest) {
  return new Promise((resolve, reject) => {
    const follow = (url) => {
      get(url, (res) => {
        if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
          follow(res.headers.location);
          return;
        }
        if (res.statusCode !== 200) {
          reject(new Error(`HTTP ${res.statusCode}`));
          return;
        }

        const total = parseInt(res.headers['content-length'], 10) || 0;
        let downloaded = 0;
        let lastPct = -1;
        const file = createWriteStream(dest);

        res.on('data', (chunk) => {
          downloaded += chunk.length;
          if (total) {
            const pct = Math.floor((downloaded / total) * 100);
            if (pct !== lastPct && pct % 5 === 0) {
              process.stdout.write(`\r   ${pct}% (${(downloaded / 1e6).toFixed(1)} / ${(total / 1e6).toFixed(1)} MB)`);
              lastPct = pct;
            }
          }
        });

        res.pipe(file);
        file.on('finish', () => { file.close(); console.log(''); resolve(); });
        file.on('error', (err) => { unlinkSync(dest); reject(err); });
      }).on('error', reject);
    };
    follow(url);
  });
}

function buildFTS(db) {
  console.log('   Building enriched FTS index (this takes ~30s)...');

  try { db.exec('DROP TABLE IF EXISTS search_fts'); } catch {}

  db.exec(`
    CREATE VIRTUAL TABLE search_fts USING fts5(
      lcsc, mfr, package, description,
      content=''
    );
  `);

  const batchSize = 10000;
  const total = db.prepare('SELECT COUNT(*) as n FROM components').get().n;
  const insert = db.prepare('INSERT INTO search_fts(rowid, lcsc, mfr, package, description) VALUES (?, ?, ?, ?, ?)');
  let offset = 0;
  let indexed = 0;

  while (offset < total) {
    const rows = db.prepare(`SELECT rowid, lcsc, mfr, package, description, extra FROM components LIMIT ? OFFSET ?`).all(batchSize, offset);
    const tx = db.transaction(() => {
      for (const r of rows) {
        let desc = r.description || '';
        if (r.extra) {
          try {
            const extra = JSON.parse(r.extra);
            if (extra.description) desc = extra.description;
            if (extra.title && extra.title !== desc) desc += ' ' + extra.title;
            if (extra.attributes) {
              const attrVals = Object.values(extra.attributes).filter(v => typeof v === 'string');
              if (attrVals.length) desc += ' ' + attrVals.join(' ');
            }
          } catch {}
        }
        insert.run(r.rowid, String(r.lcsc), r.mfr || '', r.package || '', desc);
      }
    });
    tx();
    offset += batchSize;
    indexed += rows.length;
    process.stdout.write(`\r   ${indexed.toLocaleString()} / ${total.toLocaleString()} rows indexed`);
  }
  console.log('\n   FTS index built.');
}

async function main() {
  console.log('JLCPCB Local Database Setup');
  console.log('===========================\n');

  // 1. Download
  if (existsSync(DB_PATH) && !force) {
    console.log(`1. Database already exists at ${DB_PATH}`);
    console.log('   (use --force to re-download)\n');
  } else {
    if (force && existsSync(DB_PATH)) unlinkSync(DB_PATH);
    console.log(`1. Downloading database...`);
    console.log(`   Source: ${DB_URL}`);
    await download(DB_URL, DB_PATH);
    console.log('   Done.\n');
  }

  // 2. Open and verify
  console.log('2. Preparing database...');
  const db = new Database(DB_PATH);
  db.pragma('journal_mode = WAL');

  const tables = db.prepare("SELECT name FROM sqlite_master WHERE type IN ('table','view')").all().map(r => r.name);
  console.log(`   Tables: ${tables.join(', ')}`);

  console.log('   Adding indexes...');
  db.exec('CREATE INDEX IF NOT EXISTS idx_comp_stock ON components(stock DESC)');
  db.exec('CREATE INDEX IF NOT EXISTS idx_comp_catid ON components(category_id)');
  db.exec('CREATE INDEX IF NOT EXISTS idx_comp_basic ON components(basic) WHERE basic = 1');
  db.exec('CREATE INDEX IF NOT EXISTS idx_comp_preferred ON components(preferred) WHERE preferred = 1');
  db.exec('CREATE INDEX IF NOT EXISTS idx_comp_mfr ON components(mfr)');
  console.log('   Indexes ready.');

  const hasSearchFTS = tables.includes('search_fts');
  if (!hasSearchFTS || rebuildFts) {
    buildFTS(db);
  } else {
    console.log('   Enriched FTS index: found (use --rebuild-fts to rebuild)');
  }

  // 3. Stats
  console.log('\n3. Database stats:');
  const total = db.prepare('SELECT COUNT(*) as n FROM components').get().n;
  const inStock = db.prepare('SELECT COUNT(*) as n FROM components WHERE stock > 0').get().n;
  const basic = db.prepare('SELECT COUNT(*) as n FROM components WHERE basic = 1').get().n;
  const preferred = db.prepare('SELECT COUNT(*) as n FROM components WHERE preferred = 1').get().n;
  const cats = db.prepare('SELECT COUNT(DISTINCT category) as n FROM categories').get().n;
  const subcats = db.prepare('SELECT COUNT(DISTINCT subcategory) as n FROM categories').get().n;
  console.log(`   Total components: ${total.toLocaleString()}`);
  console.log(`   In stock:         ${inStock.toLocaleString()}`);
  console.log(`   Basic parts:      ${basic.toLocaleString()}`);
  console.log(`   Preferred parts:  ${preferred.toLocaleString()}`);
  console.log(`   Categories:       ${cats}`);
  console.log(`   Subcategories:    ${subcats}`);

  db.close();
  console.log('\nSetup complete.');
}

main().catch(err => { console.error('Setup failed:', err); process.exit(1); });