import { db } from '../src/db/client';
import { encrypt, decrypt } from '../src/lib/crypto/encryption';
import { MetaPixelRecord, MetaConnectionRecord, CapiEventRecord } from '../src/types';

async function verifyDatabase() {
  console.log('\n======================================================');
  console.log('🔍 DEEP VERIFICATION: Facebook Pixel & CAPI Database Storage');
  console.log('======================================================\n');

  const shop = await db.getDefaultShop();
  console.log(`[PASS] Store identified: ${shop.shopify_domain} (ID: ${shop.id})`);

  // 1. Meta Connection & AES-256-GCM Encryption
  console.log('\n--- 1. Testing meta_connections Table & AES-256-GCM Encryption ---');
  const sampleToken = 'EAAP8ZCuhi7M_LIVE_TOKEN_VERIFICATION_TEST';
  const encrypted = encrypt(sampleToken);
  console.log(`Encrypted Token: ${encrypted}`);
  
  const connRecord: MetaConnectionRecord = {
    id: `meta_test_${Date.now()}`,
    shop_id: shop.id,
    business_id: 'biz_123456789',
    business_name: 'Test Business Portfolio',
    catalog_id: 'cat_987654321',
    catalog_name: 'Test Catalog',
    pixel_id: '2550335385463806',
    pixel_name: 'Shop Market',
    access_token_encrypted: encrypted,
    test_event_code: 'TEST12345',
    token_expires_at: new Date(Date.now() + 60 * 86400 * 1000).toISOString(),
    status: 'connected',
    created_at: new Date().toISOString(),
    updated_at: new Date().toISOString(),
  };

  await db.upsertMetaConnection(connRecord);
  const fetchedConn = await db.getMetaConnectionByShopId(shop.id);
  console.log(`[PASS] Saved connection in DB: Business=${fetchedConn?.business_name}, Pixel=${fetchedConn?.pixel_name} (${fetchedConn?.pixel_id})`);
  
  const decryptedToken = decrypt(fetchedConn!.access_token_encrypted);
  if (decryptedToken === sampleToken) {
    console.log('[PASS] Token encrypted & decrypted successfully with zero data loss');
  } else {
    throw new Error('Token decryption mismatch!');
  }

  // 2. Meta Pixels Table (Creation, Active toggle, CAPI toggle, Edit, Delete)
  console.log('\n--- 2. Testing meta_pixels Table ---');
  const testPixel: MetaPixelRecord = {
    id: `pix_test_${Date.now()}`,
    shop_id: shop.id,
    pixel_name: 'Shop Market',
    pixel_id: '2550335385463806',
    pixel_type: 'master',
    target_collection: 'all',
    target_tag: null,
    access_token_encrypted: encrypted,
    test_event_code: 'TEST12345',
    capi_enabled: true,
    emq_score: 8.8,
    events_count_24h: 142,
    status: 'active',
    is_active: true,
    app_embed_active: true,
    created_at: new Date().toISOString(),
    updated_at: new Date().toISOString(),
  };

  await db.upsertPixel(testPixel);
  let allPixels = await db.getPixelsByShopId(shop.id);
  const foundPixel = allPixels.find(p => p.pixel_id === '2550335385463806');
  console.log(`[PASS] Saved Pixel in DB: Name="${foundPixel?.pixel_name}", ID=${foundPixel?.pixel_id}, Status=${foundPixel?.status}, CAPI=${foundPixel?.capi_enabled}`);

  // Test Toggle Active Status
  console.log('\n--- Testing Toggle Active in DB ---');
  await db.upsertPixel({ ...testPixel, status: 'paused', is_active: false });
  let reloaded = await db.getPixelById(testPixel.id);
  console.log(`[PASS] Updated status in DB: status=${reloaded?.status}, is_active=${reloaded?.is_active}`);

  // Test Toggle CAPI Status
  console.log('\n--- Testing Toggle CAPI in DB ---');
  await db.upsertPixel({ ...testPixel, status: 'active', is_active: true, capi_enabled: false });
  reloaded = await db.getPixelById(testPixel.id);
  console.log(`[PASS] Updated CAPI in DB: capi_enabled=${reloaded?.capi_enabled}`);

  // 3. CAPI Events Logging
  console.log('\n--- 3. Testing capi_events Table ---');
  const capiEvent: CapiEventRecord = {
    id: `capi_test_${Date.now()}`,
    shop_id: shop.id,
    event_id: `evt_verify_${Date.now()}`,
    event_name: 'Purchase',
    event_source_url: `https://${shop.shopify_domain}/checkout`,
    payload_hash: 'hash_sha256_payload_verify',
    user_data_masked: { email: 'yes (hashed)', phone: 'yes (hashed)', ip: '127.0.0.1' },
    custom_data: { value: 49.99, currency: 'USD' },
    status: 'sent',
    meta_event_id: 'fbtrace_test_992',
    response_code: 200,
    response_body: { events_received: 1, fbtrace_id: 'fbtrace_test_992' },
    error_message: null,
    duration_ms: 85,
    created_at: new Date().toISOString(),
  };

  await db.recordCapiEvent(capiEvent);
  const events = await db.getCapiEvents(shop.id, 5);
  const savedEvt = events.find(e => e.event_id === capiEvent.event_id);
  console.log(`[PASS] Recorded CAPI Event in DB: Event=${savedEvt?.event_name}, Status=${savedEvt?.status}, Latency=${savedEvt?.duration_ms}ms, ResponseCode=${savedEvt?.response_code}`);

  // 4. Clean up test record to preserve dynamic database state
  await db.upsertMetaConnection({
    ...connRecord,
    business_id: null,
    business_name: null,
    catalog_id: null,
    catalog_name: null,
  });

  console.log('\n======================================================');
  console.log('✅ ALL DATABASE VERIFICATIONS PASSED SUCCESSFULLY!');
  console.log('======================================================\n');
}

verifyDatabase().then(() => process.exit(0)).catch(err => {
  console.error('Verification failed:', err);
  process.exit(1);
});