🔄 Baserow Migration Guide: From Hosted to Personal Instance

# Rails credentials:
baserow:
  api_url: https://api.baserow.io          # ⚠️ Still hosted
  api_token: [HOSTED_API_TOKEN]            # ⚠️ Hosted token
  table_id: [HOSTED_TABLE_ID]              # ⚠️ Hosted table

Core Identity:
  - Number (Phone)
  - Contact (Phone) 
  - k-id (User ID)
  - Name If Available
  - Email ID

Professional Data:
  - Speciality
  - Experience  
  - MCI Number
  - Education
  - Verified Doctor (Yes/No)
  - Verified On

Business Intelligence:
  - Plan (Subscription)
  - Subscription Status
  - Plan Expiring On
  - Last Subscription Ended On
  - City
  - Clinic Logo URL
  - Public Profile URL

Analytics & Funnel:
  - Intent
  - Account
  - Platform  
  - Active (Yes/No)
  - Funnel Stage:
    • Acquisition (Every signed-up user)
    • Activation (Verified doctors)
    • Revenue (Active subscription)
    • Retention (Active < 6 months)
    • Referral (Active > 6 months)
    • Churn (Expired subscription)

Statistics:
  - Patients Count
  - Appointments Count
  - Careplans Count  
  - Telemedicines Count
  - Updated At

# Active Baserow job triggers:
UserAccount.create        → BaserowUpdateUser.perform_later(user_id)      # 2s delay
Doctor.verification       → BaserowUpdateUser.perform_later(user_id)      # 30s delay
BillingSubscription       → BaserowUpdateUser.perform_later(user_id)      # 30s delay  
Clinic.update            → BaserowUpdateUser.perform_later(user_id)       # 30s delay
ClinicLocation.update    → BaserowUpdateUser.perform_later(user_id)       # 30s delay

# config/initializers/airrecord.rb
# Rails credentials:
airtable:
  api_key: [AIRTABLE_API_KEY]
  base_id: [AIRTABLE_BASE_ID]  
  table_id: [AIRTABLE_TABLE_ID]

# Core Identity Fields
Number: Text (Primary contact)
Contact: Text (Phone number)  
k-id: Number (User ID from Rails)
Name If Available: Text
Email ID: Email

# Professional Fields  
Speciality: Text
Experience: Text
MCI Number: Text
Education: Long Text
Verified Doctor: Single Select (Yes/No/[empty])
Verified On: Date

# Business Fields
Signed up?: Single Select (Yes/No)
Date: Date (Signup date)
Plan: Text
Subscription Status: Single Select (Active/Not Active)
Plan Expiring On: Date
Last Subscription Ended On: Date

# Location & Profile
City: Text
Clinic Logo URL: URL
Public Profile: URL

# Analytics Fields
Intent: Text
Account: Text
Platform: Text  
Active: Single Select (Yes/No)
Funnel: Single Select
  Options: Acquisition|Activation|Revenue|Retention|Referral|Churn

# Statistics Fields
Patients Count: Number
Appointments Count: Number
Careplans Count: Number
Telemedicines Count: Number
Updated At: Date

# Onboarding Fields (Optional)
O Speciality: Text
O City: Text
O Fees: Text
O Clinic Name: Text
O Interests: Text
O Recommendation: Text

# Create fields in this order for consistency:
1. Core Identity (Number, Contact, k-id, Name, Email)
2. Professional (Speciality, Experience, MCI, Education, Verified Doctor, Verified On)  
3. Business (Signed up, Date, Plan, Subscription Status, Plan Expiring On, etc.)
4. Location & Profile (City, Clinic Logo URL, Public Profile)
5. Analytics (Intent, Account, Platform, Active, Funnel)
6. Statistics (Patients Count, Appointments Count, etc.)
7. Onboarding (O Speciality, O City, etc.)

# Shared configuration for both environments:
baserow:
  api_url: https://baserow.kulcare.com     # Same for both
  api_token: [YOUR_PERSONAL_TOKEN]         # Same token for both tables
  
  # Environment-specific table IDs:
  production_table_id: [PROD_TABLE_ID]     # Production table ID
  staging_table_id: [STAGING_TABLE_ID]     # Staging table ID

EDITOR=nano rails credentials:edit

# Add to development credentials:
baserow:
  api_url: https://baserow.kulcare.com
  api_token: [YOUR_PERSONAL_TOKEN]
  production_table_id: [PROD_TABLE_ID]
  staging_table_id: [STAGING_TABLE_ID]
  # Development uses staging table for testing
  table_id: [STAGING_TABLE_ID]  

EDITOR=nano rails credentials:edit --environment staging

# Add to staging credentials:
baserow:
  api_url: https://baserow.kulcare.com
  api_token: [YOUR_PERSONAL_TOKEN]
  table_id: [STAGING_TABLE_ID]  # Staging uses staging table

EDITOR=nano rails credentials:edit --environment production  

# Add to production credentials:
baserow:
  api_url: https://baserow.kulcare.com
  api_token: [YOUR_PERSONAL_TOKEN]
  table_id: [PROD_TABLE_ID]  # Production uses production table

# Add baserow queue (line 28):
:queues:
  - patient_history
  - push_notifications
  # ... existing queues ...
  - airtable          # Keep for backup
  - baserow          # ← ADD THIS LINE
  - geocoding
  # ... rest of queues ...

class BaserowUpdateUser < ActiveJob::Base
  queue_as :baserow  # ← Verify this is :baserow, not :default

class BaserowUpdateUserStats < ActiveJob::Base  
  queue_as :baserow  # ← Verify this is :baserow, not :default

# Add to cron_prod.txt:
0 0 * * sun /bin/bash -lc '. ~/.bashrc; cd /home/deploy/kulcare_api_backend/current/ && RAILS_ENV=production bundle exec rake db:update_baserow' >> /home/deploy/kulcare_api_backend/shared/log/cron_logs/cron_weekly_update_baserow.log 2>&1

# Add to staging cron (if applicable):
0 0 * * sun /bin/bash -lc '. ~/.bashrc; cd /home/deploy/kulcare_api_backend/current/ && RAILS_ENV=staging bundle exec rake db:update_baserow' >> /home/deploy/kulcare_api_backend/shared/log/cron_logs/cron_weekly_update_baserow_staging.log 2>&1

namespace :db do
  desc 'Weekly update all user data in Baserow (replaces Airtable cron)'
  task update_baserow: :environment do
    puts "Starting weekly Baserow update for #{Rails.env} environment..."
    puts "Target table: #{Rails.application.credentials.dig(:baserow, :table_id)}"
    
    verified_users = User.verified.includes(:doctor, :clinic, :billing_customer)
    puts "Found #{verified_users.count} verified users to update"
    
    success_count = 0
    error_count = 0
    
    verified_users.find_each(batch_size: 50) do |user|
      next unless user.doctor.present?
      
      begin
        puts "Updating user #{user.id} (#{user.doctor.title})"
        BaserowUpdateUser.perform_now(user.id)
        success_count += 1
        sleep(0.1) # Rate limiting to avoid API overload
      rescue StandardError => e
        puts "Error updating user #{user.id}: #{e.message}"
        error_count += 1
      end
    end
    
    puts "Baserow update completed!"
    puts "✅ Success: #{success_count} users"
    puts "❌ Errors: #{error_count} users" if error_count > 0
    puts "Environment: #{Rails.env}"
    puts "Table used: #{Rails.application.credentials.dig(:baserow, :table_id)}"
  end
  
  desc 'One-time migration: Populate Baserow with all existing data'
  task migrate_to_baserow: :environment do
    puts "🚀 Starting one-time Baserow migration for #{Rails.env}..."
    puts "This will populate the Baserow table with ALL existing user data"
    puts "Target table: #{Rails.application.credentials.dig(:baserow, :table_id)}"
    
    # Clear existing baserow_record_id to ensure fresh sync
    puts "Clearing existing baserow_record_id values..."
    User.where.not(baserow_record_id: nil).update_all(baserow_record_id: nil)
    
    # Run the same logic as weekly update
    Rake::Task['db:update_baserow'].invoke
    
    puts "🎉 Migration completed!"
  end
end

# Weekly cron job will run:
bundle exec rake db:update_baserow

# For one-time initial migration after setup:
bundle exec rake db:migrate_to_baserow

# Rails console testing (development environment)
user = User.first
BaserowUpdateUser.perform_now(user.id)

# Check for errors
BaserowError.recent.each { |e| puts "#{e.user_id}: #{e.error_message}" }

# Verify record creation  
user.reload.baserow_record_id  # Should return new record ID

# Verify correct table usage
puts "Current environment: #{Rails.env}"
puts "Table ID: #{Rails.application.credentials.dig(:baserow, :table_id)}"
# Should show staging table ID

# Deploy to staging first
cap staging deploy

# SSH to staging server
ssh deploy@staging.kulcare.com

# Test on staging
cd /path/to/app
RAILS_ENV=staging rails console

# Test user sync
user = User.verified.first
BaserowUpdateUser.perform_now(user.id)

# Verify staging table receives data
user.reload.baserow_record_id

# Deploy to production (after staging validation)
cap production deploy

# SSH to production server  
ssh deploy@production.kulcare.com

# Test on production
cd /path/to/app
RAILS_ENV=production rails console

# Test with a single user first
user = User.verified.where(verified: true).first
BaserowUpdateUser.perform_now(user.id)

# Verify production table receives data
user.reload.baserow_record_id

# Monitor logs
tail -f log/production.log | grep Baserow

# Start Sidekiq with baserow queue
bundle exec sidekiq -e development

# Trigger background job
BaserowUpdateUser.perform_later(User.first.id)

# Check Sidekiq web UI for job processing

# Add performance monitoring
class BaserowUpdateUser < ActiveJob::Base
  around_perform do |job, block|
    start_time = Time.current
    block.call
    duration = Time.current - start_time
    
    Rails.logger.info "BaserowUpdateUser job completed in #{duration}s"
  end
end

# Quick rollback steps:
1. Revert Rails credentials to hosted values:
   baserow:
     api_url: https://api.baserow.io
     api_token: [ORIGINAL_HOSTED_TOKEN] 
     table_id: [ORIGINAL_HOSTED_TABLE]

2. Restart application servers
3. Data flow returns to hosted instance
4. No code changes needed (service layer abstracts API)

# Current state - both services exist:
ActiveServices:
  ✅ BaserowUpdateUser    (queue: :baserow) → api.baserow.io (hosted)

LegacyServices:  
  📦 AirtableUpdateUser      (preserved for backup)
  📦 AirtableUpdateUserStats (preserved for backup) 
  📦 AirtableError          (preserved for backup)
  📦 Airtable cron job       (removed from production ✅)

# Each environment gets its own table_id:
development: table_id → STAGING_TABLE_ID
staging: table_id → STAGING_TABLE_ID  
production: table_id → PROD_TABLE_ID