-- ============================================
-- SYNC SUBSCRIPTION DATA BETWEEN TABLES
-- Run: mysql -u username -p database_name < sync_subscription_data.sql
-- ============================================

-- Step 1: Update employers table with subscription info from employer_subscriptions
UPDATE employers e
JOIN (
    SELECT 
        es.employer_id,
        es.plan_id,
        es.status,
        es.current_period_start,
        es.current_period_end,
        es.amount_paid,
        es.billing_cycle,
        es.stripe_subscription_id,
        es.stripe_customer_id,
        es.id as subscription_id,
        ROW_NUMBER() OVER (PARTITION BY es.employer_id ORDER BY es.created_at DESC) as rn
    FROM employer_subscriptions es
    WHERE es.status = 'active'
) active_sub ON e.id = active_sub.employer_id AND active_sub.rn = 1
SET 
    e.current_plan_id = active_sub.plan_id,
    e.employer_subscription_id = active_sub.subscription_id,
    e.subscription_status = active_sub.status,
    e.subscription_end_date = active_sub.current_period_end,
    e.stripe_subscription_id = active_sub.stripe_subscription_id;

-- Step 2: Update employer_type based on plan
UPDATE employers e
JOIN subscription_plans sp ON e.current_plan_id = sp.id
SET e.employer_type = sp.slug
WHERE e.current_plan_id IS NOT NULL;

-- Step 3: Set basic plan for employers without active subscription
UPDATE employers 
SET 
    employer_type = 'basic',
    subscription_status = 'inactive'
WHERE current_plan_id IS NULL;

-- Step 4: Update subscription_end_date for expired subscriptions
UPDATE employers e
JOIN employer_subscriptions es ON e.id = es.employer_id
SET e.subscription_end_date = es.expires_at
WHERE es.expires_at IS NOT NULL AND es.expires_at < NOW();

-- Step 5: Update subscription_status based on expiry
UPDATE employers 
SET subscription_status = 'expired' 
WHERE subscription_end_date IS NOT NULL AND subscription_end_date < NOW();

-- Step 6: Sync remaining_free_boosts based on plan type
UPDATE employers 
SET remaining_free_boosts = 
    CASE 
        WHEN employer_type = 'basic' THEN 0
        WHEN employer_type = 'professional' THEN 2
        WHEN employer_type = 'recruiter' THEN 5
        WHEN employer_type = 'enterprise' THEN 10
        WHEN employer_type = 'customenterprise' THEN 20
        ELSE 0
    END;

-- Step 7: Sync pss_credits_available based on plan
UPDATE employers 
SET pss_credits_available = 
    CASE 
        WHEN employer_type = 'basic' THEN 0
        WHEN employer_type = 'professional' THEN 5
        WHEN employer_type = 'recruiter' THEN 20
        WHEN employer_type = 'enterprise' THEN 50
        WHEN employer_type = 'customenterprise' THEN 100
        ELSE 0
    END;

-- Step 8: Update verification status for trial/paid customers
UPDATE employers e
JOIN employer_subscriptions es ON e.id = es.employer_id
SET e.is_verified = 1, e.verification_status = 'verified'
WHERE es.status = 'active' AND es.amount_paid > 0;

-- Step 9: Clean up duplicate indexes (run after verifying)
-- DROP INDEX idx_current_plan_id ON employers;
-- DROP INDEX idx_employer_subscription_id ON employers;