/**
 * Africa Alert - Sync Engine
 * Bidirectional sync between local SQLite and Supabase cloud
 */

const Database = require('better-sqlite3');
const { createClient } = require('@supabase/supabase-js');
const path = require('path');
const fs = require('fs');
const https = require('https');
const http = require('http');

const DEFAULT_TENANT_ID = '5b39bcb5-506a-456d-b7cb-91730b397cc1';

class SyncEngine {
  constructor() {
    this.dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
    this.syncInterval = parseInt(process.env.SYNC_INTERVAL || '30000'); // 30 seconds default
    this.syncRunning = false;
    this.syncTimer = null;
    
    // Supabase config
    this.supabaseUrl = process.env.SUPABASE_URL || process.env.VITE_SUPABASE_URL || 'https://api.next_gen.techarvest.co.zw';
    this.supabaseKey = process.env.SUPABASE_KEY || process.env.VITE_SUPABASE_ANON_KEY || '';
    
    // Tables to sync (order matters for foreign keys)
    this.tablesToSync = [
      'users',
      // Phase 1 PR 1: auxiliary role grants (FKs to users, classes, subjects, cohorts)
      'user_roles',
      // Phase 1 PR 2: student cohorts (FKs to users, classes, exam_groups)
      'student_cohorts',
      'cohort_students',
      'cohort_classes',
      'cohort_exam_groups',
      'departments',
      'classes',
      'subjects',
      'timetable_periods',
      'timetable_entries',
      'courses',
      'enrollments',
      'course_enrolments',
      'attendance',
      'fee_groups',
      'student_fees',
      'payments',
      'messages',
      'events',
      'expenses',
      'grades',
      'course_grades',
      'assignments',
      'submissions',
      'settings',
      'system_settings',
      'audit_logs',
      // Payroll
      'staff_roles',
      'salary_grades',
      'staff_records',
      'leave_types',
      'leave_requests',
      'staff_attendance',
      'payroll_runs',
      'payslips',
      // Inventory
      'item_categories',
      'suppliers',
      'store_locations',
      'items',
      'item_stock',
      'stock_transactions',
      'item_issues',
      // Exams
      'exam_groups',
      'question_banks',
      'exam_schedules',
      'exam_attempts',
      'exam_answers',
      'exam_review_audit',
      'notifications',
      // Hostel
      'hostels',
      'room_types',
      'rooms',
      'room_assignments',
      'hostel_fees',
      // Transport
      'vehicles',
      'routes',
      'pickup_points',
      'vehicle_routes',
      'transport_allocations',
      // Front Office
      'admission_enquiries',
      'visitor_logs',
      'phone_call_logs',
      'postal_dispatch',
      'complaints',
      // Clubs & Sports
      'clubs',
      'club_memberships',
      'club_announcements',
      'club_attendance',
      'club_chats',
      // Extended Attendance
      'hostel_attendance',
      'transport_attendance',
      // Crossword
      'crossword_games',
      'crossword_clues',
      'crossword_sessions',
      // Teacher Tools & Social Docs
      'syllabuses',
      'schemes_of_work',
      'focus_points',
      'lesson_plans',
      'student_social_docs',
      // Finance Phase 2 (§10) — operational + fee engine.
      // suppliers is already listed under Inventory above; do not duplicate.
      'chart_of_accounts',
      'bank_accounts',
      'bank_reconciliations',
      'bank_reconciliation_lines',
      'invoices',
      'trips',
      'trip_payments',
      'student_groups',
      'student_group_members',
      'fee_plans',
      'fee_plan_installments',
      'discounts',
      'student_fee_discounts',
      // Student Medical (sanatorium + dining-hall)
      'student_medical_profiles',
      'student_medication_logs',
      'student_medical_history',
      'sanatorium_visits',
      'student_immunizations',
      'student_medical_documents',
      'dining_hall_alerts',
      // School-wide settings (feature/grading-scales, 2026-07-11)
      // Holds the school_admin-editable default grading scale plus any
      // future school-wide toggles. Lightweight k/v table; no FK deps.
      'school_settings',
      // Education-level configurations (feature/shapeshift). Binds each
      // level this school operates (primary/secondary/high_school/
      // tertiary) to its grading scale + display name. No FK deps.
      'education_level_configs',
      // §A — File attachments + new homework/tests tables
      // Order: parents first, leaf last. attachments has no FK to the new
      // tables (it uses a polymorphic parent_kind/parent_id) so it sits at
      // the end per plan §2.6.
      'homework',
      'tests',
      'homework_submissions',
      'test_submissions',
      'attachments',
      'offboarding_records',
      'offboarding_actions',
    ];
    
    this.init();
  }

  init() {
    // Ensure data directory exists
    const dataDir = path.dirname(this.dbPath);
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }
    
    this.db = new Database(this.dbPath);
    this.db.pragma('foreign_keys = ON');
    this.db.pragma('journal_mode = WAL');

    // Startup check: every table in `tablesToSync` must exist in the
    // SQLite schema. If any are missing (e.g. init.js was edited
    // without rerunning the schema, or a future migration dropped a
    // table that the sync engine still references), we want a loud
    // error here rather than a silent failure on the first push/pull.
    for (const table of this.tablesToSync) {
      const exists = this.db
        .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name = ?")
        .get(table);
      if (!exists) {
        throw new Error(
          `SyncEngine init: table '${table}' is in tablesToSync but does not exist in the database. ` +
            `Either run server/src/database/init.js (creates the schema) or remove the table from ` +
            `server/src/services/SyncEngine.js tablesToSync.`
        );
      }
    }

    // Ensure audit_logs has sync columns
    try {
      this.db.prepare("SELECT sync_status FROM audit_logs LIMIT 1").get();
    } catch (e) {
      if (e.message.includes('no such column')) {
        console.log('Adding sync columns to audit_logs table...');
        try {
          this.db.prepare("ALTER TABLE audit_logs ADD COLUMN sync_status TEXT DEFAULT 'pending'").run();
          this.db.prepare("ALTER TABLE audit_logs ADD COLUMN last_synced_at DATETIME").run();
          this.db.prepare("ALTER TABLE audit_logs ADD COLUMN is_deleted INTEGER DEFAULT 0").run();
          console.log('✅ Added sync columns to audit_logs successfully.');
        } catch (alterErr) {
          console.log('Error altering audit_logs:', alterErr.message);
        }
      }
    }

    // Ensure sync_events event-sourcing table exists (Sprint 4)
    try {
      this.db.prepare(`
        CREATE TABLE IF NOT EXISTS sync_events (
          event_id TEXT PRIMARY KEY,
          tenant_id TEXT,
          table_name TEXT,
          seq_num INTEGER,
          client_timestamp DATETIME,
          normalized_utc DATETIME,
          processed_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
      `).run();
    } catch (evtErr) {
      console.log('Notice initializing sync_events:', evtErr.message);
    }

    // Retrieve and update settings dynamically from the system_settings table
    try {
      const dbUrl = this.db.prepare("SELECT value FROM system_settings WHERE key = 'supabase_url'").get();
      const dbKey = this.db.prepare("SELECT value FROM system_settings WHERE key = 'supabase_key'").get();
      const dbInterval = this.db.prepare("SELECT value FROM system_settings WHERE key = 'sync_interval_seconds'").get();
      
      const envUrl = process.env.SUPABASE_URL || process.env.VITE_SUPABASE_URL;
      const envKey = process.env.SUPABASE_KEY || process.env.VITE_SUPABASE_ANON_KEY;

      if (envUrl) {
        if (dbUrl?.value !== envUrl) {
          this.db.prepare("INSERT OR REPLACE INTO system_settings (key, value, category) VALUES ('supabase_url', ?, 'sync')").run(envUrl);
          console.log(`🔄 Synced database supabase_url with environment: ${envUrl}`);
        }
        this.supabaseUrl = envUrl;
      } else if (dbUrl?.value) {
        this.supabaseUrl = dbUrl.value;
      }

      if (envKey) {
        if (dbKey?.value !== envKey) {
          this.db.prepare("INSERT OR REPLACE INTO system_settings (key, value, category) VALUES ('supabase_key', ?, 'sync')").run(envKey);
          console.log('🔄 Synced database supabase_key with environment credentials');
        }
        this.supabaseKey = envKey;
      } else if (dbKey?.value) {
        this.supabaseKey = dbKey.value;
      }
      
      if (dbInterval?.value) this.syncInterval = parseInt(dbInterval.value) * 1000;
    } catch (err) {
      console.log('SyncEngine: Error loading supabase credentials from db:', err.message);
    }
    
    if (this.supabaseUrl && this.supabaseKey) {
      this.supabaseClient = createClient(this.supabaseUrl, this.supabaseKey);
    }
    
    console.log('🚀 SyncEngine initialized');
    console.log(`   Database: ${this.dbPath}`);
    console.log(`   Supabase: ${this.supabaseUrl}`);
    console.log(`   Interval: ${this.syncInterval}ms`);
    
    // Start sync timer
    this.startSyncTimer();
  }

  /**
   * Start the sync timer
   */
  startSyncTimer() {
    if (this.syncTimer) {
      clearInterval(this.syncTimer);
    }
    
    this.syncTimer = setInterval(() => {
      this.runSyncCycle().catch(err => {
        console.error('Sync cycle error:', err);
        this.logSyncError('sync_cycle', null, 'error', err.message);
      });
    }, this.syncInterval);
    
    console.log(`   Sync timer started (every ${this.syncInterval / 1000}s)`);
    
    // Run initial sync after 5 seconds
    setTimeout(() => {
      this.runSyncCycle().catch(console.error);
    }, 5000);
  }

  /**
   * Stop the sync timer
   */
  stopSyncTimer() {
    if (this.syncTimer) {
      clearInterval(this.syncTimer);
      this.syncTimer = null;
      console.log('   Sync timer stopped');
    }
  }

  /**
   * Run a full sync cycle (push + pull)
   */
  async runSyncCycle() {
    if (this.syncRunning) {
      console.log('⏳ Sync already in progress, skipping...');
      return;
    }
    
    // Dynamically reload credentials in case they changed in UI
    try {
      const dbUrl = this.db.prepare("SELECT value FROM system_settings WHERE key = 'supabase_url'").get();
      const dbKey = this.db.prepare("SELECT value FROM system_settings WHERE key = 'supabase_key'").get();
      const dbInterval = this.db.prepare("SELECT value FROM system_settings WHERE key = 'sync_interval_seconds'").get();
      
      if (dbUrl?.value) this.supabaseUrl = dbUrl.value;
      if (dbKey?.value) {
        this.supabaseKey = dbKey.value;
        this.supabaseClient = createClient(this.supabaseUrl, this.supabaseKey);
      }
      if (dbInterval?.value) this.syncInterval = parseInt(dbInterval.value) * 1000;
    } catch (err) {
      console.log('SyncEngine: Error reloading settings:', err.message);
    }

    if (!this.supabaseKey) {
      console.log('⚠️ Supabase key not configured, skipping sync');
      return;
    }
    
    this.syncRunning = true;
    const startTime = Date.now();
    
    try {
      // Update sync status
      this.db.prepare('INSERT OR REPLACE INTO sync_config (key, value) VALUES (?, ?)').run('sync_running', '1');
      
      console.log('🔄 Starting sync cycle...');

      // Ensure tenant is configured/registered
      const tenantId = await this.ensureTenantRegistered();
      if (!tenantId) {
        console.log('⚠️ Sync cycle skipped: tenant_id is not configured.');
        return;
      }
      
      // Step 1: Push local changes to Supabase
      await this.pushChanges();
      
      // Step 2: Pull remote changes from Supabase
      await this.pullChanges();
      
      const duration = Date.now() - startTime;
      console.log(`✅ Sync cycle completed in ${duration}ms`);
      
    } catch (error) {
      console.error('❌ Sync cycle failed:', error.message);
      this.logSyncError('sync_cycle', null, 'error', error.message);
    } finally {
      this.syncRunning = false;
      this.db.prepare('INSERT OR REPLACE INTO sync_config (key, value) VALUES (?, ?)').run('sync_running', '0');
      this.db.prepare('INSERT OR REPLACE INTO sync_config (key, value) VALUES (?, ?)').run('last_sync', new Date().toISOString());
    }
  }

  /**
   * Ensure the tenant is registered in Supabase and the school logo is uploaded
   */
  async ensureTenantRegistered() {
    if (!this.supabaseKey) return null;
    
    try {
      // 1. Check if tenant_id is already in system_settings or process.env.TENANT_ID
      const tenantIdRow = this.db.prepare("SELECT value FROM system_settings WHERE key = 'tenant_id'").get();
      let tenantId = tenantIdRow?.value || process.env.TENANT_ID || '5b39bcb5-506a-456d-b7cb-91730b397cc1';
      
      const schoolNameRow = this.db.prepare("SELECT value FROM system_settings WHERE key = 'school_name'").get();
      const schoolName = schoolNameRow?.value || 'Africa Alert School';
      
      const schoolLogoPathRow = this.db.prepare("SELECT value FROM system_settings WHERE key = 'school_logo_path'").get();
      const localLogoPath = schoolLogoPathRow?.value;
      
      if (!this.supabaseClient) {
        this.supabaseClient = createClient(this.supabaseUrl, this.supabaseKey);
      }

      if (tenantId && (!tenantIdRow || !tenantIdRow.value)) {
        this.db.prepare("INSERT OR REPLACE INTO system_settings (key, value, category) VALUES ('tenant_id', ?, 'sync')").run(tenantId);
        console.log(`📌 Auto-initialized tenant_id in system_settings: ${tenantId}`);
      }
      
      // If we don't have a tenantId, we do not register. We just return null.
      if (!tenantId) {
        console.log('⚠️ No tenant_id configured in system_settings.');
        return null;
      }
      
      // 2. Ensure dedicated storage bucket exists for this tenant
      if (tenantId) {
        const bucketName = `tenant-${tenantId}`;
        
        const { data: bucketData, error: bucketError } = await this.supabaseClient.storage.createBucket(bucketName, {
          public: true
        });
        
        if (bucketError && !bucketError.message.includes('already exists')) {
          console.error(`⚠️ Failed to create dedicated bucket for tenant:`, bucketError.message);
        } else {
          console.log(`📁 Dedicated storage bucket validated: ${bucketName}`);
        }
        
        // 3. Sync/upload logo if we have a local logo path
        if (localLogoPath && fs.existsSync(localLogoPath)) {
          console.log(`📤 Uploading school logo to dedicated bucket ${bucketName}...`);
          const fileBuffer = fs.readFileSync(localLogoPath);
          const extension = path.extname(localLogoPath).toLowerCase();
          const contentType = extension === '.png' ? 'image/png' : extension === '.jpg' || extension === '.jpeg' ? 'image/jpeg' : 'application/octet-stream';
          
          const { data: uploadData, error: uploadError } = await this.supabaseClient.storage
            .from(bucketName)
            .upload('logo' + extension, fileBuffer, {
              contentType,
              upsert: true
            });
            
          if (uploadError) {
            console.error('⚠️ Failed to upload logo to storage bucket:', uploadError.message);
          } else {
            const { data: urlData } = this.supabaseClient.storage
              .from(bucketName)
              .getPublicUrl('logo' + extension);
              
            const publicLogoUrl = urlData?.publicUrl;
            if (publicLogoUrl) {
              console.log(`Logo public URL: ${publicLogoUrl}`);
              
              const { error: updateError } = await this.supabaseClient
                .from('tenants')
                .update({ logo_url: publicLogoUrl })
                .eq('id', tenantId);
                
              if (updateError) {
                console.error('⚠️ Failed to update tenant logo_url on Supabase:', updateError.message);
              } else {
                this.db.prepare("INSERT OR REPLACE INTO system_settings (key, value, category) VALUES ('school_logo_url', ?, 'general')").run(publicLogoUrl);
                console.log('✅ Remote and local school logo URL synced!');
              }
            }
          }
        }
      }
      
      return tenantId;
      
    } catch (err) {
      console.error('❌ ensureTenantRegistered failed:', err.message);
      return null;
    }
  }

  /**
   * Push local pending changes to Supabase
   */
  async pushChanges() {
    console.log('📤 Pushing local changes...');
    let pushedCount = 0;
    
    for (const tableName of this.tablesToSync) {
      try {
        const pendingRecords = this.db.prepare(`
          SELECT * FROM ${tableName} 
          WHERE sync_status = 'pending' AND is_deleted = 0
          LIMIT 100
        `).all();
        
        if (pendingRecords.length > 0) {
          const result = await this.syncToSupabase(tableName, pendingRecords, 'push');
          pushedCount += result.success;
          
          // Mark successfully synced records
          for (const record of result.successRecords) {
            this.db.prepare(`
              UPDATE ${tableName} 
              SET sync_status = 'synced', last_synced_at = ?
              WHERE id = ?
            `).run(new Date().toISOString(), record.id);
          }
        }
        
        // Handle deleted records
        const deletedRecords = this.db.prepare(`
          SELECT * FROM ${tableName} 
          WHERE is_deleted = 1 AND sync_status = 'pending'
          LIMIT 50
        `).all();
        
        if (deletedRecords.length > 0) {
          await this.deleteFromSupabase(tableName, deletedRecords);
        }
        
      } catch (error) {
        console.log(`   ⚠️ ${tableName}: ${error.message}`);
        this.logSyncError(tableName, null, 'push', error.message);
      }
    }
    
    console.log(`   📤 Pushed ${pushedCount} records`);
  }

  /**
   * Pull remote changes from Supabase
   */
  async pullChanges() {
    console.log('📥 Pulling remote changes...');
    let pulledCount = 0;
    
    // Get last sync time
    const lastSync = this.db.prepare('SELECT value FROM sync_config WHERE key = ?').get('last_sync');
    const lastSyncTime = lastSync?.value || '1970-01-01T00:00:00Z';
    
    for (const tableName of this.tablesToSync) {
      try {
        // For demo/offline mode, skip pull if no Supabase key
        if (!this.supabaseKey) {
          continue;
        }
        
        const remoteRecords = await this.fetchFromSupabase(tableName, lastSyncTime);
        
        if (remoteRecords.length > 0) {
          const result = this.mergeFromSupabase(tableName, remoteRecords);
          pulledCount += result;
        }
        
      } catch (error) {
        console.log(`   ⚠️ ${tableName}: ${error.message}`);
        this.logSyncError(tableName, null, 'pull', error.message);
      }
    }
    
    console.log(`   📥 Pulled ${pulledCount} records`);
  }

  /**
   * Sync records to Supabase via REST API
   */
  async syncToSupabase(tableName, records, operation) {
    const results = { success: 0, failed: 0, successRecords: [], failedRecords: [] };
    
    if (!this.supabaseKey) {
      // Offline mode - just mark as synced locally
      return { success: records.length, successRecords: records };
    }
    
    try {
      const tenantIdRow = this.db.prepare("SELECT value FROM system_settings WHERE key = 'tenant_id'").get();
      const tenantId = tenantIdRow?.value;
      
      // Use Supabase REST API directly with upsert support on conflict column
      const hasUid = records[0] && 'uid' in records[0];
      const hasKey = records[0] && 'key' in records[0];
      
      let baseUrl = `${this.supabaseUrl}/rest/v1/${tableName}`;
      if (hasUid) {
        baseUrl += `?on_conflict=uid`;
      } else if (hasKey) {
        baseUrl += `?on_conflict=key`;
      }
      
      for (const record of records) {
        try {
          // Exclude local-only fields before sending to Supabase
          const { id, created_at, ...syncData } = record;
          
          if (tenantId) {
            syncData.tenant_id = tenantId;
          }
          
          const response = await this.httpRequest({
            method: 'POST',
            url: baseUrl,
            headers: {
              'apikey': this.supabaseKey,
              'Authorization': `Bearer ${this.supabaseKey}`,
              'Content-Type': 'application/json',
              'Prefer': 'resolution=merge-duplicates'
            },
            body: syncData
          });
          
          if (response.success) {
            results.success++;
            results.successRecords.push(record);
          } else {
            results.failed++;
            results.failedRecords.push(record);
          }
          
        } catch (err) {
          results.failed++;
          results.failedRecords.push(record);
        }
      }
      
    } catch (error) {
      console.log(`   Error syncing ${tableName}:`, error.message);
    }
    
    return results;
  }

  /**
   * Fetch records from Supabase
   */
  async fetchFromSupabase(tableName, since) {
    if (!this.supabaseKey) {
      return [];
    }
    
    try {
      const tenantIdRow = this.db.prepare("SELECT value FROM system_settings WHERE key = 'tenant_id'").get();
      const tenantId = tenantIdRow?.value;
      
      let url = `${this.supabaseUrl}/rest/v1/${tableName}?updated_at=gte.${encodeURIComponent(since)}&select=*`;
      if (tenantId) {
        url += `&tenant_id=eq.${tenantId}`;
      }
      
      const response = await this.httpRequest({
        method: 'GET',
        url: url,
        headers: {
          'apikey': this.supabaseKey,
          'Authorization': `Bearer ${this.supabaseKey}`
        }
      });
      
      return response.data || [];
      
    } catch (error) {
      return [];
    }
  }

  /**
   * Merge remote records into local database
   */
  mergeFromSupabase(tableName, records) {
    if (!records || records.length === 0) return 0;
    
    let mergedCount = 0;
    
    // Get columns from the first record, excluding local-only and tenant-specific fields
    const firstRecord = records[0];
    const columns = Object.keys(firstRecord).filter(k => !['id', 'created_at', 'tenant_id'].includes(k));
    
    if (columns.length === 0) return 0;

    const placeholders = columns.map(() => '?').join(', ');
    const columnList = columns.join(', ');
    const updateList = columns
      .filter(k => k !== 'uid') // Don't update the unique identifier
      .map(k => `${k} = excluded.${k}`)
      .join(', ');

    try {
      const upsert = this.db.prepare(`
        INSERT INTO ${tableName} (${columnList}, sync_status, last_synced_at)
        VALUES (${placeholders}, 'synced', ?)
        ON CONFLICT(uid) DO UPDATE SET
          ${updateList},
          sync_status = 'synced',
          last_synced_at = excluded.last_synced_at
      `);
      
      for (const record of records) {
        try {
          // Last-Write-Wins Check: Ensure we do not overwrite newer local modifications
          let localRecord = null;
          try {
            localRecord = this.db.prepare(`SELECT * FROM ${tableName} WHERE uid = ?`).get(record.uid);
          } catch (e) {
            // Ignore if table doesn't have uid or is missing
          }

          if (localRecord) {
            const localTime = localRecord.updated_at || localRecord.timestamp || localRecord.created_at;
            const remoteTime = record.updated_at || record.timestamp || record.created_at;

            if (localTime && remoteTime) {
              const localMs = new Date(localTime).getTime();
              const remoteMs = new Date(remoteTime).getTime();

              if (localMs > remoteMs) {
                // Local changes are newer! Skip overwrite and flag as pending to push local data
                this.db.prepare(`UPDATE ${tableName} SET sync_status = 'pending' WHERE uid = ?`).run(record.uid);
                continue;
              }
            }
          }

          const values = columns.map(k => record[k]);
          values.push(new Date().toISOString()); // For last_synced_at
          
          upsert.run(...values);
          
          // Update Sync Metadata (Assume cloud data has high priority)
          this.db.prepare(`
            INSERT INTO sync_metadata (uid, table_name, last_modified_by_role, last_modified_at)
            VALUES (?, ?, 'admin', ?)
            ON CONFLICT(uid) DO UPDATE SET 
              last_modified_by_role = 'admin',
              last_modified_at = EXCLUDED.last_modified_at
          `).run(record.uid, tableName, record.updated_at || new Date().toISOString());

          mergedCount++;
          
        } catch (err) {
          console.log(`   Merge error for ${tableName} record ${record.uid}:`, err.message);
        }
      }
    } catch (err) {
      console.log(`   Merge preparation error for ${tableName}:`, err.message);
    }
    
    return mergedCount;
  }

  /**
   * Delete records from Supabase
   */
  async deleteFromSupabase(tableName, records) {
    if (!this.supabaseKey) {
      // Mark as synced if no Supabase
      for (const record of records) {
        this.db.prepare(`UPDATE ${tableName} SET sync_status = 'synced' WHERE id = ?`).run(record.id);
      }
      return;
    }
    
    try {
      const tenantIdRow = this.db.prepare("SELECT value FROM system_settings WHERE key = 'tenant_id'").get();
      const tenantId = tenantIdRow?.value;
      
      for (const record of records) {
        try {
          let url = `${this.supabaseUrl}/rest/v1/${tableName}?uid=eq.${record.uid}`;
          if (tenantId) {
            url += `&tenant_id=eq.${tenantId}`;
          }
          
          await this.httpRequest({
            method: 'DELETE',
            url: url,
            headers: {
              'apikey': this.supabaseKey,
              'Authorization': `Bearer ${this.supabaseKey}`
            }
          });
          
          this.db.prepare(`UPDATE ${tableName} SET sync_status = 'synced' WHERE id = ?`).run(record.id);
          
        } catch (err) {
          this.logSyncError(tableName, record.id, 'delete', err.message);
        }
      }
    } catch (error) {
      console.log('Error deleting records from Supabase:', error.message);
    }
  }

  /**
   * HTTP Request helper
   */
  httpRequest(options) {
    return new Promise((resolve, reject) => {
      try {
        const urlObj = new URL(options.url);
        const isHttps = urlObj.protocol === 'https:';
        const httpModule = isHttps ? https : http;
        
        const reqOptions = {
          hostname: urlObj.hostname,
          port: urlObj.port || (isHttps ? 443 : 80),
          path: urlObj.pathname + urlObj.search,
          method: options.method || 'GET',
          headers: options.headers || {},
          timeout: 10000
        };
        
        const req = httpModule.request(reqOptions, (res) => {
          let data = '';
          
          res.on('data', (chunk) => { data += chunk; });
          
          res.on('end', () => {
            const isSuccess = res.statusCode >= 200 && res.statusCode < 300;
            let responseData = data;
            try {
              responseData = JSON.parse(data);
            } catch (err) {
              // Not JSON
            }
            resolve({ success: isSuccess, data: responseData, status: res.statusCode });
          });
        });
        
        req.on('error', reject);
        req.on('timeout', () => {
          req.destroy();
          reject(new Error('Request timeout'));
        });
        
        if (options.body) {
          req.write(JSON.stringify(options.body));
        }
        
        req.end();
        
      } catch (error) {
        reject(error);
      }
    });
  }

  /**
   * Log sync errors
   */
  logSyncError(tableName, recordId, operation, errorMessage) {
    try {
      this.db.prepare(`
        INSERT INTO sync_logs (table_name, record_id, operation, status, error_message)
        VALUES (?, ?, ?, 'failed', ?)
      `).run(tableName, recordId, operation, errorMessage);
    } catch (err) {
      console.log('Failed to log sync error:', err.message);
    }
  }

  /**
   * Get sync status
   */
  getSyncStatus() {
    const lastSync = this.db.prepare('SELECT value FROM sync_config WHERE key = ?').get('last_sync');
    const syncRunning = this.db.prepare('SELECT value FROM sync_config WHERE key = ?').get('sync_running');
    
    const pendingCounts = {};
    for (const table of this.tablesToSync) {
      try {
        const count = this.db.prepare(`SELECT COUNT(*) as count FROM ${table} WHERE sync_status = 'pending'`).get();
        pendingCounts[table] = count?.count || 0;
      } catch {
        pendingCounts[table] = 0;
      }
    }
    
    const totalPending = Object.values(pendingCounts).reduce((a, b) => a + b, 0);
    
    return {
      lastSync: lastSync?.value || 'Never',
      syncRunning: syncRunning?.value === '1',
      totalPending,
      pendingByTable: pendingCounts,
      supabaseConfigured: !!this.supabaseKey
    };
  }

  /**
   * Force sync (manual trigger)
   */
  async forceSync() {
    console.log('🔄 Force sync triggered');
    await this.runSyncCycle();
    return this.getSyncStatus();
  }

  /**
   * Resolve conflicts (Server Wins policy)
   */
  resolveConflict(tableName, recordId, serverData) {
    try {
      // Update local with server data
      const updates = Object.entries(serverData)
        .filter(([key]) => !['id', 'uid', 'created_at'].includes(key))
        .map(([key, value]) => `${key} = ?`);
      
      const values = Object.entries(serverData)
        .filter(([key]) => !['id', 'uid', 'created_at'].includes(key))
        .map(([, value]) => value);
      
      if (updates.length > 0) {
        this.db.prepare(`
          UPDATE ${tableName} 
          SET ${updates.join(', ')}, sync_status = 'synced', last_synced_at = ?
          WHERE id = ?
        `).run(...values, new Date().toISOString(), recordId);
      }
      
      this.logSyncError(tableName, recordId, 'conflict', 'Resolved with server data');
      
      return true;
    } catch (error) {
      this.logSyncError(tableName, recordId, 'conflict', error.message);
      return false;
    }
  }

  /**
   * Sprint 4: Calculate client-server UTC time delta and normalize timestamps
   */
  normalizeClientTimestamp(clientTimestampStr) {
    const serverNow = new Date();
    const clientTime = clientTimestampStr ? new Date(clientTimestampStr) : serverNow;
    const timeDeltaMs = serverNow.getTime() - clientTime.getTime();

    // Normalized UTC timestamp
    const normalizedUtc = new Date(clientTime.getTime() + timeDeltaMs);
    return {
      normalizedUtc: normalizedUtc.toISOString(),
      timeDeltaMs
    };
  }

  /**
   * Sprint 4: Idempotent Event Processor
   * Checks event_id to reject duplicate pushes and verifies monotonic seq_num
   */
  processIdempotentEvent(eventId, tenantId, tableName, seqNum, clientTimestamp) {
    if (!eventId) {
      const { v4: uuidv4 } = require('uuid');
      eventId = uuidv4();
    }

    const existingEvent = this.db.prepare('SELECT event_id FROM sync_events WHERE event_id = ?').get(eventId);
    if (existingEvent) {
      // Idempotent duplicate event detected — skip reprocessing
      return { isDuplicate: true, eventId, message: 'Event already processed' };
    }

    // Check monotonic sequence number for the tenant table
    const maxSeqRow = this.db.prepare(
      'SELECT MAX(seq_num) as max_seq FROM sync_events WHERE tenant_id = ? AND table_name = ?'
    ).get(tenantId || DEFAULT_TENANT_ID, tableName);

    const highestSeq = maxSeqRow?.max_seq || 0;
    if (seqNum && seqNum <= highestSeq) {
      return { isStale: true, eventId, message: `Stale sequence number ${seqNum} <= ${highestSeq}` };
    }

    const { normalizedUtc } = this.normalizeClientTimestamp(clientTimestamp);
    const nextSeq = seqNum || (highestSeq + 1);

    this.db.prepare(`
      INSERT INTO sync_events (event_id, tenant_id, table_name, seq_num, client_timestamp, normalized_utc)
      VALUES (?, ?, ?, ?, ?, ?)
    `).run(eventId, tenantId || DEFAULT_TENANT_ID, tableName, nextSeq, clientTimestamp || new Date().toISOString(), normalizedUtc);

    return { isDuplicate: false, isStale: false, eventId, seqNum: nextSeq, normalizedUtc };
  }

  /**
   * Cleanup old sync logs
   */
  cleanupOldLogs(daysToKeep = 30) {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - daysToKeep);
    
    const result = this.db.prepare(`
      DELETE FROM sync_logs WHERE created_at < ?
    `).run(cutoffDate.toISOString());
    
    console.log(`🧹 Cleaned up ${result.changes} old sync logs`);
    return result.changes;
  }
}

// Singleton instance
let syncEngineInstance = null;

function getSyncEngine() {
  if (!syncEngineInstance) {
    syncEngineInstance = new SyncEngine();
  }
  return syncEngineInstance;
}

// Export for use in Express
module.exports = { SyncEngine, getSyncEngine };

// CLI mode
if (require.main === module) {
  const engine = new SyncEngine();
  
  // Run single sync
  engine.runSyncCycle().then(() => {
    console.log('Sync completed');
    process.exit(0);
  }).catch(err => {
    console.error('Sync failed:', err);
    process.exit(1);
  });
}