-- ========================================
-- TEST SYNC ON DEVELOPMENT DATABASE
-- ========================================
-- This is a TEST script to verify the sync process works
-- Run this on DEV (brickwal_m1_ds) to test before running on PROD
--
-- This will:
-- 1. Backup current dev data
-- 2. Clear and re-import the same data
-- 3. Verify everything works
--
-- If this works, then the production deployment should work too
-- ========================================

USE brickwal_m1_ds;

-- Show current counts
SELECT '=== BEFORE TEST ===' as Status;
SELECT 'Current Positions:' as Info, COUNT(*) as Count FROM positions;
SELECT 'Current Roles:' as Info, COUNT(*) as Count FROM roles;

-- Check foreign key references
SELECT '=== CHECKING FOREIGN KEYS ===' as Status;
SELECT TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME IN ('positions', 'roles')
AND TABLE_SCHEMA = 'brickwal_m1_ds';

-- Show what's referencing these tables
SELECT 'Tables referencing positions:' as Info;
SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME = 'positions'
AND TABLE_SCHEMA = 'brickwal_m1_ds';

SELECT 'Tables referencing roles:' as Info;
SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME = 'roles'
AND TABLE_SCHEMA = 'brickwal_m1_ds';

-- ========================================
-- THE ISSUE: These tables have FK references
-- Solution: We need to temporarily clear the child tables too
-- ========================================

-- Backup everything
DROP TABLE IF EXISTS positions_test_backup;
CREATE TABLE positions_test_backup AS SELECT * FROM positions;

DROP TABLE IF EXISTS roles_test_backup;
CREATE TABLE roles_test_backup AS SELECT * FROM roles;

-- For this test, let's just verify the backups exist
SELECT '=== TEST COMPLETE ===' as Status;
SELECT 'Positions backed up:' as Info, COUNT(*) as Count FROM positions_test_backup;
SELECT 'Roles backed up:' as Info, COUNT(*) as Count FROM roles_test_backup;

-- ========================================
-- ANALYSIS
-- ========================================
-- The problem is that `users` table has FK to `roles`
-- Even with FOREIGN_KEY_CHECKS=0, MySQL may still enforce constraints
-- depending on the storage engine and MySQL version
--
-- RECOMMENDATION FOR PRODUCTION:
-- Don't run the DELETE/TRUNCATE approach
-- Instead, use a safer approach that preserves FKs
-- ========================================