-- =====================================================
-- SQL Migration: Employee Master Tables - Pattern Alignment
-- Created: 2026-02-05
-- Description:
--   1. Add default timestamp columns: createdAt, updatedAt
--   2. Rename tables to system pattern: lowercase, no underscores, plural (s)
--      education_levels  -> educationlevels
--      placement_areas   -> placementareas
--      zones             -> zones (no change)
--
-- IMPORTANT: Backup database before running. Run in phpMyAdmin.
-- Backend models (educationLevel, placementArea, zone) now use table names
-- educationlevels, placementareas, zones. You MUST run this migration
-- or the app will fail with "Table doesn't exist".
-- =====================================================

-- =====================================================
-- STEP 1: Add createdAt, updatedAt to each table
-- =====================================================

-- 1.1 education_levels
ALTER TABLE `education_levels`
  ADD COLUMN `createdAt` DATETIME NULL DEFAULT NULL AFTER `owner`,
  ADD COLUMN `updatedAt` DATETIME NULL DEFAULT NULL AFTER `createdAt`;

UPDATE `education_levels` SET `createdAt` = `creation`, `updatedAt` = `modified` WHERE `createdAt` IS NULL;

-- 1.2 placement_areas
ALTER TABLE `placement_areas`
  ADD COLUMN `createdAt` DATETIME NULL DEFAULT NULL AFTER `owner`,
  ADD COLUMN `updatedAt` DATETIME NULL DEFAULT NULL AFTER `createdAt`;

UPDATE `placement_areas` SET `createdAt` = `creation`, `updatedAt` = `modified` WHERE `createdAt` IS NULL;

-- 1.3 zones
ALTER TABLE `zones`
  ADD COLUMN `createdAt` DATETIME NULL DEFAULT NULL AFTER `owner`,
  ADD COLUMN `updatedAt` DATETIME NULL DEFAULT NULL AFTER `createdAt`;

UPDATE `zones` SET `createdAt` = `creation`, `updatedAt` = `modified` WHERE `createdAt` IS NULL;

-- =====================================================
-- STEP 2: Drop foreign keys on employees (referencing old table names)
-- =====================================================

ALTER TABLE `employees` DROP FOREIGN KEY `fk_employees_education_level`;
ALTER TABLE `employees` DROP FOREIGN KEY `fk_employees_placement_area`;
ALTER TABLE `employees` DROP FOREIGN KEY `fk_employees_secondary_placement_area`;
ALTER TABLE `employees` DROP FOREIGN KEY `fk_employees_zone`;

-- =====================================================
-- STEP 2b: Drop foreign keys on placement_areas (self and refs)
-- =====================================================

ALTER TABLE `placement_areas` DROP FOREIGN KEY `fk_placement_areas_company`;
ALTER TABLE `placement_areas` DROP FOREIGN KEY `fk_placement_areas_parent`;
ALTER TABLE `placement_areas` DROP FOREIGN KEY `fk_placement_areas_city`;

-- =====================================================
-- STEP 2c: Drop foreign keys on education_levels, zones
-- =====================================================

ALTER TABLE `education_levels` DROP FOREIGN KEY `fk_education_levels_company`;
ALTER TABLE `zones` DROP FOREIGN KEY `fk_zones_company`;

-- =====================================================
-- STEP 3: Rename tables (pattern: lowercase, no underscores, plural)
-- =====================================================

RENAME TABLE `education_levels` TO `educationlevels`;
RENAME TABLE `placement_areas` TO `placementareas`;
-- zones already matches pattern, no rename

-- =====================================================
-- STEP 3b: Re-add FKs on educationlevels, placementareas, zones
-- =====================================================

ALTER TABLE `educationlevels`
  ADD CONSTRAINT `fk_education_levels_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `placementareas`
  ADD CONSTRAINT `fk_placement_areas_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_placement_areas_parent` FOREIGN KEY (`parent_id`) REFERENCES `placementareas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_placement_areas_city` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `zones`
  ADD CONSTRAINT `fk_zones_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- =====================================================
-- STEP 4: Re-add foreign keys on employees
-- =====================================================

ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_education_level`
  FOREIGN KEY (`education_level_id`) REFERENCES `educationlevels` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_placement_area`
  FOREIGN KEY (`placement_area_id`) REFERENCES `placementareas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_secondary_placement_area`
  FOREIGN KEY (`secondary_placement_area_id`) REFERENCES `placementareas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_zone`
  FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- =====================================================
-- STEP 5: Rename indexes on educationlevels (optional, for consistency)
-- =====================================================

-- MySQL: drop old index then create new (index names are per-table)
ALTER TABLE `educationlevels` DROP INDEX `idx_education_levels_company_id`;
ALTER TABLE `educationlevels` ADD INDEX `idx_educationlevels_company_id` (`company_id`);

ALTER TABLE `educationlevels` DROP INDEX `idx_education_levels_enabled`;
ALTER TABLE `educationlevels` ADD INDEX `idx_educationlevels_enabled` (`enabled`);

ALTER TABLE `educationlevels` DROP INDEX `idx_education_levels_level_order`;
ALTER TABLE `educationlevels` ADD INDEX `idx_educationlevels_level_order` (`level_order`);

ALTER TABLE `educationlevels` DROP INDEX `idx_education_levels_name`;
ALTER TABLE `educationlevels` ADD INDEX `idx_educationlevels_name` (`name`);

-- =====================================================
-- STEP 6: Rename indexes on placementareas (optional)
-- =====================================================

ALTER TABLE `placementareas` DROP INDEX `idx_placement_areas_company_id`;
ALTER TABLE `placementareas` ADD INDEX `idx_placementareas_company_id` (`company_id`);

ALTER TABLE `placementareas` DROP INDEX `idx_placement_areas_enabled`;
ALTER TABLE `placementareas` ADD INDEX `idx_placementareas_enabled` (`enabled`);

ALTER TABLE `placementareas` DROP INDEX `idx_placement_areas_is_primary`;
ALTER TABLE `placementareas` ADD INDEX `idx_placementareas_is_primary` (`is_primary`);

ALTER TABLE `placementareas` DROP INDEX `idx_placement_areas_parent_id`;
ALTER TABLE `placementareas` ADD INDEX `idx_placementareas_parent_id` (`parent_id`);

ALTER TABLE `placementareas` DROP INDEX `idx_placement_areas_city_id`;
ALTER TABLE `placementareas` ADD INDEX `idx_placementareas_city_id` (`city_id`);

ALTER TABLE `placementareas` DROP INDEX `idx_placement_areas_name`;
ALTER TABLE `placementareas` ADD INDEX `idx_placementareas_name` (`name`);

-- =====================================================
-- Verification
-- =====================================================
-- SHOW TABLES LIKE 'education%';
-- SHOW TABLES LIKE 'placement%';
-- SHOW TABLES LIKE 'zones';
-- DESCRIBE educationlevels;
-- DESCRIBE placementareas;
-- DESCRIBE zones;