# MongoDB connection (Meteor production database)
export METEOR_MONGO_URL="mongodb://root:<password>@mdb-p-0.ballee.db-eu2.zcloud.ws:60601,mdb-p-1.ballee.db-eu2.zcloud.ws:60602,mdb-p-2.ballee.db-eu2.zcloud.ws:60603/meteor?authSource=admin&ssl=true&tlsInsecure=true&replicaSet=mdb-p"

# Supabase staging database
export SUPABASE_DB_PASSWORD_STAGING="<password>"
export SUPABASE_DB_HOST="db.hxpcknyqswetsqmqmeep.supabase.co"  # Optional, has default

# Supabase production database (use with caution)
export SUPABASE_DB_PASSWORD_PROD="<password>"

const S3_REGION = 'eu-central-1';
const S3_BUCKET = 'ch.akson.dance.bucket';

// URL format for media files
const mediaUrl = `https://s3.${S3_REGION}.amazonaws.com/${S3_BUCKET}/${s3Key}`;

// R2 URLs follow this pattern
const r2Url = 'https://pub-2541aa5053334bd4bcd4a619e7ad3428.r2.dev/...';

-- Entity mapping cache (deduplication)
CREATE TABLE meteor_entity_mapping (
  meteor_id text PRIMARY KEY,
  entity_type text NOT NULL,
  entity_id uuid NOT NULL,
  source_hash text,           -- MD5 hash for change detection
  source_data jsonb,          -- Cached MongoDB document
  first_synced_at timestamptz,
  last_synced_at timestamptz,
  sync_count integer DEFAULT 1,
  sync_status text DEFAULT 'synced', -- synced | pending | error | skipped
  error_message text
);

-- Sync run tracking
CREATE TABLE meteor_sync_runs (
  id uuid PRIMARY KEY,
  sync_type text NOT NULL,    -- full | incremental | entity | resume
  status text NOT NULL,       -- running | completed | failed | partial
  trigger_type text,          -- manual | cron | script | webhook
  triggered_by uuid,
  started_at timestamptz,
  completed_at timestamptz,
  duration_seconds integer,
  metrics jsonb               -- created, updated, skipped, failed counts
);

-- Change tracking
CREATE TABLE meteor_sync_changes (
  id uuid PRIMARY KEY,
  sync_run_id uuid REFERENCES meteor_sync_runs(id),
  meteor_id text,
  entity_type text,
  operation text,             -- created | updated | skipped | error
  changed_fields jsonb,       -- Array of field changes
  error_message text,
  created_at timestamptz
);

-- Sync state (for incremental sync)
CREATE TABLE meteor_sync_state (
  entity_type text PRIMARY KEY,
  last_sync_at timestamptz,
  last_meteor_id text,        -- For resume capability
  checkpoint_data jsonb
);

# Full sync of all entities
pnpm meteor:sync --full

# Incremental sync since last run
pnpm meteor:sync --incremental

# Sync specific entity type
pnpm meteor:sync --entity organization
pnpm meteor:sync --entity candidate
pnpm meteor:sync --entity media

# Preview changes without applying
pnpm meteor:sync --dry-run

# Resume a failed sync
pnpm meteor:sync --resume <run-id>

# Check sync status
pnpm meteor:staging status

# Trigger a full sync
pnpm meteor:staging trigger

# Trigger incremental sync
pnpm meteor:staging trigger --type incremental

# Sync specific entity type
pnpm meteor:staging trigger --entity organization

# Dry run (preview only)
pnpm meteor:staging trigger --dry-run

# View sync history
pnpm meteor:staging history
pnpm meteor:staging history --limit 20

# View detailed logs for a specific run
pnpm meteor:staging logs <run-id>

# View sync statistics
pnpm meteor:staging stats

# Watch sync status in real-time
pnpm meteor:staging watch
pnpm meteor:staging watch --interval 3

# From skill scripts folder
cd .claude/skills/meteor-sync-specialist/scripts

# Preview migration (no changes)
npx tsx migrate-users.ts --dry-run

# Run full migration
npx tsx migrate-users.ts

# Preview sync
npx tsx sync-orphan-media.ts --dry-run

# Run sync
npx tsx sync-orphan-media.ts

# Preview sync
npx tsx sync-post-media.ts --dry-run

# Run sync
npx tsx sync-post-media.ts

type MeteorEntityType =
  | 'organization'
  | 'organization_media'
  | 'candidate'
  | 'media'
  | 'experience'
  | 'post'
  | 'follow'
  | 'like'
  | 'application'
  | 'offer'
  | 'user'
  | 'audition'
  | 'audition_application';

Level 1 (No dependencies):
├── organization
├── organization_media
├── candidate
└── media (dancer_media)

Level 2 (Depends on Level 1):
├── experience (refs: organization, candidate)
├── audition (refs: organization)
└── post (refs: candidate, organization)

Level 3 (Depends on Level 2):
├── audition_application (refs: audition, candidate)
├── like (refs: post, candidate)
├── follow (refs: candidate, organization)
└── comment (refs: post, candidate)

// MongoDB Candidate document
{
  _id: ObjectId,
  userId: ObjectId,
  firstName: string,
  lastName: string,
  stageName: string,
  birthdate: Date,
  bio: string,
  danceStyles: string[],
  height: number,
  weight: number,
  availableForHire: boolean,
  // ... 30+ more fields
}

// Supabase Tables:
// 1. profiles (basic user info)
{
  id: uuid,
  display_name: string,
  first_name: string,
  last_name: string,
  birthdate: date
}

// 2. professional_profiles (career info)
{
  id: uuid,
  user_id: uuid,
  stage_name: string,
  bio: text,
  dance_styles: text[],
  available_for_hire: boolean,
  social_handles: jsonb
}

// 3. dancer_profiles (physical attributes)
{
  id: uuid,
  professional_profile_id: uuid,
  height_cm: integer,
  weight_kg: integer,
  pointe_work: boolean,
  acrobatics: boolean
}

// MongoDB → Supabase
{
  _id: ObjectId,          // → meteor_id
  name: string,           // → name
  type: 'school' | 'company' | 'agency', // → type
  logoMediaId: ObjectId,  // → logo_media_id (resolve via mapping)
  headerMediaId: ObjectId, // → header_media_id
  bio: string,            // → description
  website: string,        // → website
  location: {             // → address, city, country
    city: string,
    country: string
  }
}

pnpm meteor:sync --full

pnpm meteor:sync --incremental

pnpm meteor:sync --entity organization
pnpm meteor:sync --entity candidate

pnpm meteor:sync --resume <run-id>

-- Fix trigger_generate_profile_slug
CREATE OR REPLACE FUNCTION public.trigger_generate_profile_slug()
RETURNS TRIGGER AS $$
DECLARE
  v_base_slug TEXT;
  v_slug TEXT;
  v_counter INTEGER := 0;
  v_user_row RECORD;
BEGIN
  -- Get user info - USE public.profiles instead of profiles
  SELECT first_name, last_name, display_name INTO v_user_row
  FROM public.profiles WHERE id = NEW.user_id;

  -- ... rest of function
  -- Replace all 'profiles' with 'public.profiles'
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fix create_dancer_profile_on_signup
CREATE OR REPLACE FUNCTION public.create_dancer_profile_on_signup()
RETURNS TRIGGER AS $$
BEGIN
  -- Use public.professional_profiles and public.dancer_profiles
  INSERT INTO public.professional_profiles (id, user_id, profile_type, ...)
  VALUES (gen_random_uuid(), NEW.id, 'dancer', ...);

  INSERT INTO public.dancer_profiles (id, ...)
  VALUES (...);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Fix setup_new_user
CREATE OR REPLACE FUNCTION public.setup_new_user()
RETURNS TRIGGER AS $$
BEGIN
  -- Use public.accounts
  INSERT INTO public.accounts (id, name, slug, ...)
  VALUES (...);

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Check for duplicate mappings
SELECT meteor_id, entity_type, COUNT(*)
FROM meteor_entity_mapping
GROUP BY meteor_id, entity_type
HAVING COUNT(*) > 1;

-- Find entities without meteor_id
SELECT id, name, created_at
FROM organizations
WHERE meteor_id IS NULL
  AND created_at > NOW() - INTERVAL '1 day';

-- Find stale cache entries
SELECT mem.meteor_id, mem.entity_type, mem.entity_id
FROM meteor_entity_mapping mem
LEFT JOIN organizations o ON o.id = mem.entity_id
WHERE mem.entity_type = 'organization'
  AND o.id IS NULL;

-- Clean up stale entries
DELETE FROM meteor_entity_mapping mem
WHERE NOT EXISTS (
  SELECT 1 FROM organizations WHERE id = mem.entity_id
)
AND entity_type = 'organization';

# Check sync order
pnpm meteor:staging logs <run-id>

# Verify dependency exists
pnpm sql:local "SELECT * FROM organizations WHERE meteor_id = '<org_meteor_id>'"

-- Check source_hash in mapping
SELECT meteor_id, source_hash, last_synced_at
FROM meteor_entity_mapping
WHERE meteor_id = '<meteor_id>';

-- Check for stuck runs
SELECT id, sync_type, status, started_at,
       NOW() - started_at as duration
FROM meteor_sync_runs
WHERE status = 'running'
ORDER BY started_at DESC;

-- Mark stuck run as failed
UPDATE meteor_sync_runs
SET status = 'failed',
    completed_at = NOW(),
    duration_seconds = EXTRACT(EPOCH FROM NOW() - started_at)::integer
WHERE status = 'running'
  AND started_at < NOW() - INTERVAL '30 minutes';

{
  "syncType": "full|incremental|entity",
  "entityTypes": ["organization", "candidate"],
  "dryRun": false,
  "batchSize": 100,
  "continueOnError": true
}

{
  "success": true,
  "syncRunId": "uuid",
  "status": "running",
  "startedAt": "2025-12-09T10:00:00Z"
}

# From apps/web directory
cd apps/web && node scripts/meteor-diagnose.cjs

# From apps/web directory with tsx available
cd apps/web && npx tsx ../../.claude/skills/meteor-sync-specialist/scripts/validate-entity-mapping.ts

cd apps/web && npx tsx ../../.claude/skills/meteor-sync-specialist/scripts/compare-counts.ts

Entity Type         MongoDB    Supabase   Diff    Synced%
─────────────────────────────────────────────────────────
organization        428        425        -3      99.3%
candidate           20018      19854      -164    99.2%
media               75140      73278      -1862   97.5%
post                1492       1490       -2      99.9%

// CORRECT - Validates entity exists
const existingId = await duplicatePrevention.findExistingEntity(
  meteorId,
  'organization'
);

if (existingId) {
  // Entity exists and is valid
  const hasChanges = changeDetector.hasChanges(newData, cachedData);
  if (hasChanges) {
    await updateEntity(existingId, newData);
  }
} else {
  // Create new entity
  await createEntity(newData);
}

async function syncDocument(doc: MeteorDocument) {
  const meteorId = doc._id.toString();

  // Check if exists (validates entity still exists)
  const existingId = await duplicatePrevention.findExistingEntity(
    meteorId,
    entityType
  );

  if (existingId) {
    // Get cached data for comparison
    const cachedData = await duplicatePrevention.getCachedSourceData(meteorId);

    if (changeDetector.hasChanges(doc, cachedData)) {
      await updateEntity(existingId, mapper.mapToSupabase(doc));
      await duplicatePrevention.updateEntityMapping(meteorId, doc);
      return { operation: 'updated' };
    }
    return { operation: 'skipped' };
  } else {
    const newId = await createEntity(mapper.mapToSupabase(doc));
    await duplicatePrevention.registerEntity(meteorId, entityType, newId, doc);
    return { operation: 'created' };
  }
}

async function syncBatch(documents: MeteorDocument[]) {
  const results = {
    created: 0,
    updated: 0,
    skipped: 0,
    failed: 0,
    errors: [] as Array<{ meteorId: string; error: string }>
  };

  for (const doc of documents) {
    try {
      const result = await syncDocument(doc);
      results[result.operation]++;
    } catch (error) {
      results.failed++;
      results.errors.push({
        meteorId: doc._id.toString(),
        error: error.message
      });

      // Mark in entity mapping for retry
      await duplicatePrevention.markEntityError(
        doc._id.toString(),
        entityType,
        error.message
      );

      // Continue processing other documents
      console.error(`Failed to sync ${doc._id}:`, error);
    }
  }

  return results;
}