103 lines
4.8 KiB
SQL
103 lines
4.8 KiB
SQL
-- ============================================
|
|
-- PRODUCTION-SAFE DUPLICATE COMMISSION CLEANUP
|
|
-- Generated: 2026-02-08
|
|
-- ============================================
|
|
--
|
|
-- INSTRUCTIONS:
|
|
-- 1. SSH into your production server
|
|
-- 2. Run: mysql -u YOUR_USER -p YOUR_DATABASE < cleanup_duplicate_commissions.sql
|
|
-- 3. Or copy-paste sections into MySQL client
|
|
--
|
|
-- This script will:
|
|
-- 1. Create backup tables (preserves original data)
|
|
-- 2. Show duplicate counts BEFORE cleanup
|
|
-- 3. Delete duplicates (keeping lowest ID for each booking)
|
|
-- 4. Show duplicate counts AFTER cleanup (should be 0)
|
|
-- ============================================
|
|
|
|
-- STEP 1: CREATE BACKUP TABLES
|
|
-- ============================================
|
|
SELECT '=== STEP 1: Creating backup tables ===' AS status;
|
|
|
|
CREATE TABLE IF NOT EXISTS earnings_neo_backup_20260208 AS SELECT * FROM earnings_neo;
|
|
CREATE TABLE IF NOT EXISTS earnings_upline_backup_20260208 AS SELECT * FROM earnings_upline;
|
|
CREATE TABLE IF NOT EXISTS earnings_admin_backup_20260208 AS SELECT * FROM earnings_admin;
|
|
CREATE TABLE IF NOT EXISTS earnings_service_provider_backup_20260208 AS SELECT * FROM earnings_service_provider;
|
|
CREATE TABLE IF NOT EXISTS neo_coupon_earnings_backup_20260208 AS SELECT * FROM neo_coupon_earnings;
|
|
|
|
SELECT 'Backup tables created successfully' AS status;
|
|
|
|
-- STEP 2: SHOW DUPLICATE COUNTS BEFORE CLEANUP
|
|
-- ============================================
|
|
SELECT '=== STEP 2: Duplicate counts BEFORE cleanup ===' AS status;
|
|
|
|
SELECT 'earnings_neo' AS table_name, COUNT(*) AS duplicate_bookings, SUM(cnt - 1) AS extra_entries
|
|
FROM (SELECT booking_id, neo_id, COUNT(*) AS cnt FROM earnings_neo GROUP BY booking_id, neo_id HAVING COUNT(*) > 1) t;
|
|
|
|
SELECT 'earnings_upline' AS table_name, COUNT(*) AS duplicate_bookings, SUM(cnt - 1) AS extra_entries
|
|
FROM (SELECT booking_id, upline_id, COUNT(*) AS cnt FROM earnings_upline GROUP BY booking_id, upline_id HAVING COUNT(*) > 1) t;
|
|
|
|
SELECT 'earnings_admin' AS table_name, COUNT(*) AS duplicate_bookings, SUM(cnt - 1) AS extra_entries
|
|
FROM (SELECT booking_id, COUNT(*) AS cnt FROM earnings_admin GROUP BY booking_id HAVING COUNT(*) > 1) t;
|
|
|
|
SELECT 'earnings_service_provider' AS table_name, COUNT(*) AS duplicate_bookings, SUM(cnt - 1) AS extra_entries
|
|
FROM (SELECT booking_id, COUNT(*) AS cnt FROM earnings_service_provider GROUP BY booking_id HAVING COUNT(*) > 1) t;
|
|
|
|
SELECT 'neo_coupon_earnings' AS table_name, COUNT(*) AS duplicate_bookings, SUM(cnt - 1) AS extra_entries
|
|
FROM (SELECT booking_id, neo_id, COUNT(*) AS cnt FROM neo_coupon_earnings GROUP BY booking_id, neo_id HAVING COUNT(*) > 1) t;
|
|
|
|
-- STEP 3: DELETE DUPLICATES (KEEP LOWEST ID)
|
|
-- ============================================
|
|
SELECT '=== STEP 3: Deleting duplicates ===' AS status;
|
|
|
|
-- Delete from earnings_neo
|
|
DELETE e1 FROM earnings_neo e1
|
|
INNER JOIN earnings_neo e2
|
|
WHERE e1.id > e2.id
|
|
AND e1.booking_id = e2.booking_id
|
|
AND e1.neo_id = e2.neo_id;
|
|
SELECT ROW_COUNT() AS 'earnings_neo deleted';
|
|
|
|
-- Delete from earnings_upline
|
|
DELETE e1 FROM earnings_upline e1
|
|
INNER JOIN earnings_upline e2
|
|
WHERE e1.id > e2.id
|
|
AND e1.booking_id = e2.booking_id
|
|
AND e1.upline_id = e2.upline_id;
|
|
SELECT ROW_COUNT() AS 'earnings_upline deleted';
|
|
|
|
-- Delete from earnings_admin
|
|
DELETE e1 FROM earnings_admin e1
|
|
INNER JOIN earnings_admin e2
|
|
WHERE e1.id > e2.id
|
|
AND e1.booking_id = e2.booking_id;
|
|
SELECT ROW_COUNT() AS 'earnings_admin deleted';
|
|
|
|
-- Delete from earnings_service_provider
|
|
DELETE e1 FROM earnings_service_provider e1
|
|
INNER JOIN earnings_service_provider e2
|
|
WHERE e1.id > e2.id
|
|
AND e1.booking_id = e2.booking_id;
|
|
SELECT ROW_COUNT() AS 'earnings_service_provider deleted';
|
|
|
|
-- Delete from neo_coupon_earnings
|
|
DELETE e1 FROM neo_coupon_earnings e1
|
|
INNER JOIN neo_coupon_earnings e2
|
|
WHERE e1.id > e2.id
|
|
AND e1.booking_id = e2.booking_id
|
|
AND e1.neo_id = e2.neo_id;
|
|
SELECT ROW_COUNT() AS 'neo_coupon_earnings deleted';
|
|
|
|
-- STEP 4: VERIFY NO REMAINING DUPLICATES
|
|
-- ============================================
|
|
SELECT '=== STEP 4: Verification (should all be 0) ===' AS status;
|
|
|
|
SELECT 'earnings_neo' AS tbl, COUNT(*) AS remaining FROM (SELECT booking_id, neo_id FROM earnings_neo GROUP BY booking_id, neo_id HAVING COUNT(*) > 1) t
|
|
UNION ALL SELECT 'earnings_upline', COUNT(*) FROM (SELECT booking_id, upline_id FROM earnings_upline GROUP BY booking_id, upline_id HAVING COUNT(*) > 1) t
|
|
UNION ALL SELECT 'earnings_admin', COUNT(*) FROM (SELECT booking_id FROM earnings_admin GROUP BY booking_id HAVING COUNT(*) > 1) t
|
|
UNION ALL SELECT 'earnings_sp', COUNT(*) FROM (SELECT booking_id FROM earnings_service_provider GROUP BY booking_id HAVING COUNT(*) > 1) t
|
|
UNION ALL SELECT 'neo_coupon_earnings', COUNT(*) FROM (SELECT booking_id, neo_id FROM neo_coupon_earnings GROUP BY booking_id, neo_id HAVING COUNT(*) > 1) t;
|
|
|
|
SELECT '=== CLEANUP COMPLETE ===' AS status;
|
|
SELECT 'Backup tables preserved with _backup_20260208 suffix. Drop them after verification.' AS note;
|