-- ====================================================================================
-- CLEAR VALUATION ALLOCATION DATA - COMPREHENSIVE CLEANUP SCRIPT
-- ====================================================================================
-- This script clears all data from the Valuation Allocation module tables
-- Based on autonomous_college.sql schema analysis
-- ====================================================================================

-- Disable foreign key checks temporarily
SET FOREIGN_KEY_CHECKS = 0;

-- ====================================================================================
-- 1. CLEAR ENHANCED VALUATION ALLOCATION TABLES (New System)
-- ====================================================================================

-- Clear paper allocations (individual paper details)
DELETE FROM `paper_allocations`;

-- Clear valuation progress tracking
DELETE FROM `valuation_progress`;

-- Clear enhanced valuator allocations
DELETE FROM `valuator_allocations_enhanced`;

-- Clear valuation sessions
DELETE FROM `valuation_sessions`;

-- Clear enhanced valuation rooms (optional - uncomment if you want to clear room data too)
-- DELETE FROM `valuation_rooms_enhanced`;

-- ====================================================================================
-- 2. CLEAR ORIGINAL VALUATION ALLOCATION TABLES (Legacy System)
-- ====================================================================================

-- Clear valuation salary records
DELETE FROM `valuation_salary`;

-- Clear original valuator allocation records
DELETE FROM `valuator_allocation`;

-- Clear original valuation rooms (optional - uncomment if you want to clear room data too)
-- DELETE FROM `valuation_rooms`;

-- ====================================================================================
-- 3. CLEAR RELATED TABLES (if they contain valuation-specific data)
-- ====================================================================================

-- Clear revaluation applications (if they are part of valuation process)
DELETE FROM `revaluation_applications`;

-- ====================================================================================
-- 4. RESET AUTO_INCREMENT VALUES (Optional)
-- ====================================================================================

-- Reset auto-increment counters to start from 1
ALTER TABLE `paper_allocations` AUTO_INCREMENT = 1;
ALTER TABLE `valuation_progress` AUTO_INCREMENT = 1;
ALTER TABLE `valuator_allocations_enhanced` AUTO_INCREMENT = 1;
ALTER TABLE `valuation_sessions` AUTO_INCREMENT = 1;
ALTER TABLE `valuation_salary` AUTO_INCREMENT = 1;
ALTER TABLE `valuator_allocation` AUTO_INCREMENT = 1;
ALTER TABLE `revaluation_applications` AUTO_INCREMENT = 1;

-- ====================================================================================
-- 5. VERIFICATION QUERIES
-- ====================================================================================

-- Check if all valuation tables are empty
SELECT 'paper_allocations' as table_name, COUNT(*) as record_count FROM `paper_allocations`
UNION ALL
SELECT 'valuation_progress' as table_name, COUNT(*) as record_count FROM `valuation_progress`
UNION ALL
SELECT 'valuator_allocations_enhanced' as table_name, COUNT(*) as record_count FROM `valuator_allocations_enhanced`
UNION ALL
SELECT 'valuation_sessions' as table_name, COUNT(*) as record_count FROM `valuation_sessions`
UNION ALL
SELECT 'valuation_salary' as table_name, COUNT(*) as record_count FROM `valuation_salary`
UNION ALL
SELECT 'valuator_allocation' as table_name, COUNT(*) as record_count FROM `valuator_allocation`
UNION ALL
SELECT 'revaluation_applications' as table_name, COUNT(*) as record_count FROM `revaluation_applications`;

-- ====================================================================================
-- 6. RE-ENABLE FOREIGN KEY CHECKS
-- ====================================================================================

SET FOREIGN_KEY_CHECKS = 1;

-- ====================================================================================
-- 7. SUMMARY REPORT
-- ====================================================================================

SELECT 
    'VALUATION ALLOCATION DATA CLEARED SUCCESSFULLY' as status,
    NOW() as cleared_at,
    'All valuation allocation tables have been cleared' as message;

-- ====================================================================================
-- TABLES CLEARED IN THIS SCRIPT:
-- ====================================================================================
/*
ENHANCED SYSTEM TABLES:
1. paper_allocations - Individual paper allocation details
2. valuation_progress - Daily progress tracking
3. valuator_allocations_enhanced - Enhanced valuator allocations
4. valuation_sessions - Valuation sessions for exams

ORIGINAL SYSTEM TABLES:
5. valuation_salary - Valuation salary records
6. valuator_allocation - Original valuator allocation records

RELATED TABLES:
7. revaluation_applications - Revaluation applications

OPTIONAL TABLES (commented out):
- valuation_rooms_enhanced - Enhanced valuation rooms (structure preserved)
- valuation_rooms - Original valuation rooms (structure preserved)

NOTES:
- Foreign key constraints are temporarily disabled during cleanup
- Auto-increment values are reset to start from 1
- Room structures are preserved (only data is cleared)
- All related data is cleared in proper order to avoid constraint violations
*/