-- ================================================
-- Database Cleanup: Remove Unused Intelligent System Tables
-- This script removes tables that were created but never used
-- ================================================

USE freedb_test45;

-- Log the cleanup process
SELECT 'Starting database cleanup of unused intelligent system tables...' as status;

-- Check current table sizes before cleanup
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb,
    table_rows
FROM information_schema.tables 
WHERE table_schema = DATABASE()
    AND table_name IN ('task_time_entries', 'task_dependencies', 'project_sprints', 'task_notifications')
ORDER BY size_mb DESC;

-- Drop unused tables (in correct order to handle foreign key constraints)
DROP TABLE IF EXISTS task_time_entries;
SELECT 'Dropped task_time_entries table' as status;

DROP TABLE IF EXISTS task_dependencies;
SELECT 'Dropped task_dependencies table' as status;

DROP TABLE IF EXISTS project_sprints;  
SELECT 'Dropped project_sprints table' as status;

DROP TABLE IF EXISTS task_notifications;
SELECT 'Dropped task_notifications table' as status;

-- Show remaining intelligent system tables
SELECT 'Remaining intelligent system tables:' as status;
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb,
    table_rows
FROM information_schema.tables 
WHERE table_schema = DATABASE()
    AND table_name IN ('task_labels', 'flochief_tasks', 'task_label_associations', 
                       'task_attachments', 'task_comments', 'task_activities')
ORDER BY size_mb DESC;

-- Optimization: Analyze remaining tables for better performance
ANALYZE TABLE task_labels;
ANALYZE TABLE flochief_tasks;  
ANALYZE TABLE task_label_associations;
ANALYZE TABLE task_attachments;
ANALYZE TABLE task_comments;
ANALYZE TABLE task_activities;

SELECT 'Database cleanup completed successfully!' as status;
SELECT 'Removed 4 unused tables: task_time_entries, task_dependencies, project_sprints, task_notifications' as summary;