-- =====================================================
-- SQL Migration: Rename tables to system pattern
-- Pattern: lowercase, no underscores, plural
--   workactivityitem_files  -> workactivityitemfiles
--   connection_attempts     -> connectionattempts
--
-- IMPORTANT: Backup database before running. Run in phpMyAdmin.
-- =====================================================

-- =====================================================
-- 1. workactivityitem_files -> workactivityitemfiles
-- =====================================================

-- 1.1 Drop FK (references workactivityitems)
ALTER TABLE `workactivityitem_files` DROP FOREIGN KEY `fk_workactivityitem_files_item_id`;

-- 1.2 Rename table
RENAME TABLE `workactivityitem_files` TO `workactivityitemfiles`;

-- 1.3 Re-add FK
ALTER TABLE `workactivityitemfiles`
  ADD CONSTRAINT `fk_workactivityitemfiles_item_id`
  FOREIGN KEY (`workactivityitem_id`) REFERENCES `workactivityitems` (`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;

-- 1.4 Rename index (drop old, add new)
ALTER TABLE `workactivityitemfiles` DROP INDEX `idx_workactivityitem_files_item_id`;
ALTER TABLE `workactivityitemfiles` ADD INDEX `idx_workactivityitemfiles_item_id` (`workactivityitem_id`);

-- =====================================================
-- 2. connection_attempts -> connectionattempts
-- =====================================================

-- 2.1 Rename table (no FK from this table to others)
RENAME TABLE `connection_attempts` TO `connectionattempts`;

-- 2.2 Drop old indexes (names may vary; ignore errors if missing)
ALTER TABLE `connectionattempts` DROP INDEX `idx_connection_attempts_ip`;
ALTER TABLE `connectionattempts` DROP INDEX `idx_connection_attempts_status`;
ALTER TABLE `connectionattempts` DROP INDEX `idx_connection_attempts_timestamp`;
ALTER TABLE `connectionattempts` DROP INDEX `idx_connection_attempts_status_timestamp`;
ALTER TABLE `connectionattempts` DROP INDEX `idx_connection_attempts_isp`;
ALTER TABLE `connectionattempts` DROP INDEX `idx_connection_attempts_endpoint`;

-- 2.3 Add new indexes
ALTER TABLE `connectionattempts` ADD INDEX `idx_connectionattempts_ip` (`ip_address`);
ALTER TABLE `connectionattempts` ADD INDEX `idx_connectionattempts_status` (`connection_status`);
ALTER TABLE `connectionattempts` ADD INDEX `idx_connectionattempts_timestamp` (`attempt_timestamp`);
ALTER TABLE `connectionattempts` ADD INDEX `idx_connectionattempts_status_timestamp` (`connection_status`, `attempt_timestamp`);
ALTER TABLE `connectionattempts` ADD INDEX `idx_connectionattempts_isp` (`isp_detected`);
ALTER TABLE `connectionattempts` ADD INDEX `idx_connectionattempts_endpoint` (`endpoint_attempted`);

-- =====================================================
-- Verification
-- =====================================================
-- SHOW TABLES LIKE 'workactivityitem%';
-- SHOW TABLES LIKE 'connection%';