pwa-drawing-tools/database.js

/**
 * Database Module
 *
 * Uses SQLocal directly with BroadcastChannel for cross-tab coordination.
 *
 * Why this approach instead of SharedWorker?
 * - SQLocal already uses its own internal worker for OPFS access
 * - Wrapping it in another SharedWorker adds complexity and causes issues
 * - BroadcastChannel provides simple cross-tab communication
 * - Each tab has its own SQLocal instance but they share the same OPFS database file
 *
 * Usage:
 *   import { sql, dbReady, addLocation, getLocations } from './database.js';
 *
 *   await dbReady;
 *   await addLocation('Point A', -1.5, 7.5);
 *   const locations = await getLocations();
 */

import { SQLocal } from 'sqlocal';

// Database configuration
const DATABASE_PATH = 'lupmis.sqlite3';
const BROADCAST_CHANNEL = 'lupmis-db-sync';

// Create SQLocal instance
const db = new SQLocal(DATABASE_PATH);

// Get the sql tagged template function
const { sql } = db;

console.log('[Database] SQLocal instance created for:', DATABASE_PATH);

// Export sql for direct queries
export { sql };

// Create broadcast channel for cross-tab coordination
const channel = new BroadcastChannel(BROADCAST_CHANNEL);

// Track if database is ready
let isReady = false;
let readyResolve;
let readyReject;

export const dbReady = new Promise((resolve, reject) => {
  readyResolve = resolve;
  readyReject = reject;
});

// Database change listeners
const changeListeners = new Set();

/**
 * Subscribe to database changes (from any tab)
 * @param {Function} listener - Called with { table, action, id }
 * @returns {Function} Unsubscribe function
 */
export function onDatabaseChange(listener) {
  changeListeners.add(listener);
  return () => changeListeners.delete(listener);
}

// Handle messages from other tabs
channel.onmessage = (event) => {
  const { type, payload } = event.data;
  if (type === 'DB_CHANGE') {
    // Notify local listeners about changes from other tabs
    for (const listener of changeListeners) {
      try {
        listener(payload);
      } catch (e) {
        console.error('[Database] Change listener error:', e);
      }
    }
  }
};

/**
 * Broadcast a database change to other tabs
 */
function broadcastChange(table, action, id = null) {
  channel.postMessage({
    type: 'DB_CHANGE',
    payload: { table, action, id, timestamp: Date.now() }
  });

  // Also notify local listeners
  for (const listener of changeListeners) {
    try {
      listener({ table, action, id, timestamp: Date.now(), local: true });
    } catch (e) {
      console.error('[Database] Change listener error:', e);
    }
  }
}

// ============================================================================
// Database Initialization
// ============================================================================

/**
 * Initialize the database schema
 */
export async function initSchema() {
  try {
    console.log('[Database] Initializing schema...');

    // Test connection
    const testResult = await sql`SELECT sqlite_version() as version`;
    console.log('[Database] SQLite version:', testResult[0]?.version);

    // Create locations table
    console.log('[Database] Creating locations table...');
    await sql`
      CREATE TABLE IF NOT EXISTS locations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        longitude REAL NOT NULL,
        latitude REAL NOT NULL,
        description TEXT,
        category TEXT DEFAULT 'default',
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
        synced INTEGER DEFAULT 0
      )
    `;

    // Verify table exists
    const tablesAfterLocations = await sql`SELECT name FROM sqlite_master WHERE type='table' AND name='locations'`;
    console.log('[Database] Locations table exists:', tablesAfterLocations.length > 0);

    // Create sync_log table
    console.log('[Database] Creating sync_log table...');
    await sql`
      CREATE TABLE IF NOT EXISTS sync_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        table_name TEXT NOT NULL,
        record_id INTEGER NOT NULL,
        action TEXT NOT NULL,
        timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
        synced INTEGER DEFAULT 0
      )
    `;

    // Create indexes
    await sql`CREATE INDEX IF NOT EXISTS idx_locations_category ON locations(category)`;
    await sql`CREATE INDEX IF NOT EXISTS idx_locations_synced ON locations(synced)`;

    // Final verification
    const allTables = await sql`SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'`;
    console.log('[Database] All tables:', allTables.map(t => t.name));

    isReady = true;
    readyResolve(true);
    console.log('[Database] ✓ Schema initialized');

  } catch (error) {
    console.error('[Database] ✗ Schema init failed:', error);
    readyReject(error);
    throw error;
  }
}

// ============================================================================
// Location Operations
// ============================================================================

/**
 * Add a new location
 */
export async function addLocation(name, longitude, latitude, options = {}) {
  const { description = null, category = 'default' } = options;

  console.log('[Database] Adding location:', name, longitude, latitude, category);

  try {
    // Check table exists first
    const tableCheck = await sql`SELECT name FROM sqlite_master WHERE type='table' AND name='locations'`;
    console.log('[Database] Table check before insert:', tableCheck);

    if (tableCheck.length === 0) {
      console.error('[Database] ✗ locations table does not exist!');
      throw new Error('locations table does not exist');
    }

    // Insert - using explicit values
    console.log('[Database] Executing INSERT...');
    await sql`
    INSERT INTO locations (name, longitude, latitude, description, category)
    VALUES (${name}, ${longitude}, ${latitude}, ${description}, ${category})
  `;
    console.log('[Database] INSERT completed');

    // Get the ID
    const idResult = await sql`SELECT last_insert_rowid() as id`;
    const newId = idResult[0]?.id;
    console.log('[Database] New ID:', newId);

    // Verify it was actually inserted
    const verifyResult = await sql`SELECT * FROM locations WHERE id = ${newId}`;
    console.log('[Database] Verify insert:', verifyResult);

    if (verifyResult.length === 0) {
      console.error('[Database] ✗ Insert verification failed - row not found!');
      throw new Error('Insert verification failed');
    }

  // Log for sync
    await sql`
      INSERT INTO sync_log (table_name, record_id, action)
      VALUES ('locations', ${newId}, 'INSERT')
    `;

  // Broadcast to other tabs
  broadcastChange('locations', 'INSERT', newId);

    console.log('[Database] ✓ Location added:', newId);
  return { id: newId };

  } catch (error) {
    console.error('[Database] ✗ Failed to add location:', error);
    throw error;
}
}

/**
 * Get all locations
 */
export async function getLocations(options = {}) {
  const { category = null, limit = 1000 } = options;

  try {
    // First check if table exists
    const tableCheck = await sql`SELECT name FROM sqlite_master WHERE type='table' AND name='locations'`;
    console.log('[Database] getLocations - table exists:', tableCheck.length > 0);

    if (tableCheck.length === 0) {
      console.warn('[Database] locations table does not exist yet');
      return [];
    }

    let results;
  if (category) {
      results = await sql`
      SELECT * FROM locations
      WHERE category = ${category}
      ORDER BY created_at DESC
      LIMIT ${limit}
    `;
    } else {
      results = await sql`
    SELECT * FROM locations
    ORDER BY created_at DESC
    LIMIT ${limit}
  `;
}

    console.log('[Database] getLocations returned', results.length, 'rows');
    return results;

  } catch (error) {
    console.error('[Database] getLocations error:', error);
    return [];
  }
}

export async function getLocation(id) {
  try {
  const results = await sql`SELECT * FROM locations WHERE id = ${id}`;
  return results[0] || null;
  } catch (error) {
    console.error('[Database] getLocation error:', error);
    return null;
}
}

/**
 * Update a location
 */
export async function updateLocation(id, updates) {
  const { name, longitude, latitude, description, category } = updates;

  try {
    const location = await getLocation(id);
    if (!location) {
      throw new Error(`Location ${id} not found`);
    }

  await sql`
    UPDATE locations
    SET
        name = ${name ?? location.name},
        longitude = ${longitude ?? location.longitude},
        latitude = ${latitude ?? location.latitude},
        description = ${description ?? location.description},
        category = ${category ?? location.category},
      updated_at = CURRENT_TIMESTAMP,
      synced = 0
    WHERE id = ${id}
  `;

  // Log for sync
  await sql`
    INSERT INTO sync_log (table_name, record_id, action)
    VALUES ('locations', ${id}, 'UPDATE')
  `;

  // Broadcast to other tabs
  broadcastChange('locations', 'UPDATE', id);
    console.log('[Database] ✓ Location updated:', id);

  } catch (error) {
    console.error('[Database] ✗ updateLocation error:', error);
    throw error;
}
}

/**
 * Delete a location
 */
export async function deleteLocation(id) {
  try {
  await sql`
    INSERT INTO sync_log (table_name, record_id, action)
    VALUES ('locations', ${id}, 'DELETE')
  `;

  await sql`DELETE FROM locations WHERE id = ${id}`;

  // Broadcast to other tabs
  broadcastChange('locations', 'DELETE', id);
    console.log('[Database] ✓ Location deleted:', id);

  } catch (error) {
    console.error('[Database] ✗ deleteLocation error:', error);
    throw error;
}
}

/**
 * Get location count
 */
export async function getLocationCount() {
  try {
  const result = await sql`SELECT COUNT(*) as count FROM locations`;
  return result[0]?.count ?? 0;
  } catch (error) {
    console.error('[Database] getLocationCount error:', error);
    return 0;
}
}

// ============================================================================
// Sync Operations
// ============================================================================

/**
 * Get unsynced changes
 */
export async function getUnsyncedChanges() {
  return sql`SELECT * FROM sync_log WHERE synced = 0 ORDER BY timestamp ASC`;
}

/**
 * Mark changes as synced
 */
export async function markSynced(syncLogIds) {
  if (!syncLogIds.length) return;
  for (const id of syncLogIds) {
    await sql`UPDATE sync_log SET synced = 1 WHERE id = ${id}`;
}
}

/**
 * Get locations that need syncing
 */
export async function getUnsyncedLocations() {
  return sql`SELECT * FROM locations WHERE synced = 0`;
}

/**
 * Mark locations as synced
 */
export async function markLocationsSynced(ids) {
  if (!ids.length) return;
  for (const id of ids) {
    await sql`UPDATE locations SET synced = 1 WHERE id = ${id}`;
}
}

// ============================================================================
// Export / Import
// ============================================================================

/**
 * Export database for backup
 */
export async function exportDatabase() {
  return db.getDatabaseFile();
}

/**
 * Import database from backup
 */
export async function importDatabase(data) {
  await db.overwriteDatabaseFile(data);
  broadcastChange('*', 'IMPORT', null);
}

/**
 * Download database as file
 */
export async function downloadDatabase(filename = 'lupmis-backup.sqlite3') {
  const data = await exportDatabase();
  const blob = new Blob([data], { type: 'application/x-sqlite3' });
  const url = URL.createObjectURL(blob);

  const a = document.createElement('a');
  a.href = url;
  a.download = filename;
  a.click();

  URL.revokeObjectURL(url);
}

// Export to GeoJSON
export async function exportToGeoJSON() {
  const locations = await getLocations();

  return {
    type: 'FeatureCollection',
    features: locations.map((loc) => ({
      type: 'Feature',
      properties: {
        id: loc.id,
        name: loc.name,
        category: loc.category,
        notes: loc.notes,
        created_at: loc.created_at,
      },
      geometry: {
        type: 'Point',
        coordinates: [loc.lon, loc.lat],
      },
    })),
  };
}

// ============================================================================
// Utility & Debug
// ============================================================================

/**
 * Get database status
 */
export async function getDatabaseStatus() {
  try {
  const tables = await sql`
    SELECT name FROM sqlite_master
    WHERE type='table' AND name NOT LIKE 'sqlite_%'
    ORDER BY name
  `;

  const locationCount = await getLocationCount();

  return {
    ready: isReady,
    databasePath: DATABASE_PATH,
    tables: tables.map(t => t.name),
    locationCount
  };
  } catch (error) {
    return {
      ready: false,
      error: error.message
    };
}
}

// Debug function - call from console to test
export async function testDatabase() {
  console.log('=== DATABASE TEST ===');

  try {
    // 1. Check connection
    const version = await sql`SELECT sqlite_version() as v`;
    console.log('1. SQLite version:', version[0].v);

    // 2. Check tables
    const tables = await sql`SELECT name FROM sqlite_master WHERE type='table'`;
    console.log('2. Tables:', tables.map(t => t.name));

    // 3. Try to insert a test row
    console.log('3. Inserting test row...');
    await sql`INSERT INTO locations (name, longitude, latitude, category) VALUES ('TEST', -1.0, 7.0, 'test')`;

    // 4. Read it back
    const rows = await sql`SELECT * FROM locations WHERE name = 'TEST'`;
    console.log('4. Test row:', rows);

    // 5. Count all rows
    const count = await sql`SELECT COUNT(*) as c FROM locations`;
    console.log('5. Total rows:', count[0].c);

    // 6. Delete test row
    await sql`DELETE FROM locations WHERE name = 'TEST'`;
    console.log('6. Test row deleted');

    console.log('=== TEST PASSED ===');
    return true;
  } catch (error) {
    console.error('=== TEST FAILED ===', error);
    return false;
}
}

// Expose to window for debugging
if (typeof window !== 'undefined') {
  window.testDatabase = testDatabase;
  window.dbStatus = getDatabaseStatus;
}

export async function closeDatabase() {
  channel.close();
  if (db.destroy) {
    await db.destroy();
}
}

export default {
  sql,
  dbReady,
  initSchema,
  addLocation,
  getLocations,
  getLocation,
  updateLocation,
  deleteLocation,
  getLocationCount,
  getUnsyncedChanges,
  getUnsyncedLocations,
  markSynced,
  markLocationsSynced,
  exportDatabase,
  importDatabase,
  downloadDatabase,
  getDatabaseStatus,
  testDatabase,
  onDatabaseChange,
  closeDatabase
};