-- =====================================================
-- SQL Migration: Employee Master Data Tables
-- Created: 2026-02-05
-- Description: Create master data tables for employees
--              - education_levels
--              - placement_areas
--              - zones
--              - Alter employees table with new fields
-- =====================================================

-- =====================================================
-- 1. CREATE TABLE: education_levels
-- =====================================================
CREATE TABLE IF NOT EXISTS `education_levels` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creation` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  `modified_by` INT(11) NOT NULL,
  `owner` INT(11) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  
  `name` VARCHAR(50) NOT NULL COMMENT 'Slug: S1, SMA, SMK, D1, D3, S2, S3',
  `display_name` VARCHAR(100) NOT NULL COMMENT 'Label: Sarjana 1, Sekolah Menengah Atas, etc',
  `level_order` INT(11) NULL DEFAULT NULL COMMENT 'Order for sorting: 1=SMA, 2=SMK, 3=D1, 4=D3, 5=S1, 6=S2, 7=S3',
  `description` TEXT NULL DEFAULT NULL,
  
  `company_id` INT(11) NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  INDEX `idx_education_levels_company_id` (`company_id`),
  INDEX `idx_education_levels_enabled` (`enabled`),
  INDEX `idx_education_levels_level_order` (`level_order`),
  INDEX `idx_education_levels_name` (`name`),
  CONSTRAINT `fk_education_levels_company` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE SET NULL 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Master data for education levels (PENDIDIKAN)';

-- =====================================================
-- 2. CREATE TABLE: placement_areas
-- =====================================================
CREATE TABLE IF NOT EXISTS `placement_areas` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creation` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  `modified_by` INT(11) NOT NULL,
  `owner` INT(11) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  
  `name` VARCHAR(50) NOT NULL COMMENT 'Slug: tangerang-selatan, surabaya, jakarta',
  `display_name` VARCHAR(100) NOT NULL COMMENT 'Label: Tangerang Selatan, Surabaya, Jakarta',
  `code` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Code: TGS, SBY, JKT',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=Primary Area, 0=Secondary Area',
  `description` TEXT NULL DEFAULT NULL,
  
  `parent_id` INT(11) NULL DEFAULT NULL COMMENT 'FK to placement_areas (for hierarchy: secondary -> primary)',
  `city_id` INT(11) NULL DEFAULT NULL COMMENT 'FK to cities (link to master city)',
  `company_id` INT(11) NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  INDEX `idx_placement_areas_company_id` (`company_id`),
  INDEX `idx_placement_areas_enabled` (`enabled`),
  INDEX `idx_placement_areas_is_primary` (`is_primary`),
  INDEX `idx_placement_areas_parent_id` (`parent_id`),
  INDEX `idx_placement_areas_city_id` (`city_id`),
  INDEX `idx_placement_areas_name` (`name`),
  CONSTRAINT `fk_placement_areas_company` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE SET NULL 
    ON UPDATE CASCADE,
  CONSTRAINT `fk_placement_areas_parent` 
    FOREIGN KEY (`parent_id`) 
    REFERENCES `placement_areas` (`id`) 
    ON DELETE SET NULL 
    ON UPDATE CASCADE,
  CONSTRAINT `fk_placement_areas_city` 
    FOREIGN KEY (`city_id`) 
    REFERENCES `cities` (`id`) 
    ON DELETE SET NULL 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Master data for placement areas (Area Penampatan & Area Sekunder)';

-- =====================================================
-- 3. CREATE TABLE: zones
-- =====================================================
CREATE TABLE IF NOT EXISTS `zones` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `creation` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  `modified_by` INT(11) NOT NULL,
  `owner` INT(11) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  
  `name` VARCHAR(50) NOT NULL COMMENT 'Code: 1, 2, 3',
  `display_name` VARCHAR(100) NOT NULL COMMENT 'Label: Zona 1, Zona 2, Zona 3',
  `description` TEXT NULL DEFAULT NULL,
  `zone_order` INT(11) NULL DEFAULT NULL COMMENT 'Order for sorting',
  
  `company_id` INT(11) NULL DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  INDEX `idx_zones_company_id` (`company_id`),
  INDEX `idx_zones_enabled` (`enabled`),
  INDEX `idx_zones_zone_order` (`zone_order`),
  INDEX `idx_zones_name` (`name`),
  CONSTRAINT `fk_zones_company` 
    FOREIGN KEY (`company_id`) 
    REFERENCES `companies` (`id`) 
    ON DELETE SET NULL 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Master data for zones (Data Zona)';

-- =====================================================
-- 4. ALTER TABLE: employees (Add new columns)
-- =====================================================
-- Add education_level_id
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `education_level_id` INT(11) NULL DEFAULT NULL AFTER `employeestatus_id`,
ADD INDEX `idx_employees_education_level_id` (`education_level_id`),
ADD CONSTRAINT `fk_employees_education_level` 
  FOREIGN KEY (`education_level_id`) 
  REFERENCES `education_levels` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Add placement_area_id (Area Penampatan)
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `placement_area_id` INT(11) NULL DEFAULT NULL AFTER `education_level_id`,
ADD INDEX `idx_employees_placement_area_id` (`placement_area_id`),
ADD CONSTRAINT `fk_employees_placement_area` 
  FOREIGN KEY (`placement_area_id`) 
  REFERENCES `placement_areas` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Add secondary_placement_area_id (Area Sekunder)
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `secondary_placement_area_id` INT(11) NULL DEFAULT NULL AFTER `placement_area_id`,
ADD INDEX `idx_employees_secondary_placement_area_id` (`secondary_placement_area_id`),
ADD CONSTRAINT `fk_employees_secondary_placement_area` 
  FOREIGN KEY (`secondary_placement_area_id`) 
  REFERENCES `placement_areas` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Add zone_id
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `zone_id` INT(11) NULL DEFAULT NULL AFTER `secondary_placement_area_id`,
ADD INDEX `idx_employees_zone_id` (`zone_id`),
ADD CONSTRAINT `fk_employees_zone` 
  FOREIGN KEY (`zone_id`) 
  REFERENCES `zones` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Add recruitment_city_id
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `recruitment_city_id` INT(11) NULL DEFAULT NULL AFTER `zone_id`,
ADD INDEX `idx_employees_recruitment_city_id` (`recruitment_city_id`),
ADD CONSTRAINT `fk_employees_recruitment_city` 
  FOREIGN KEY (`recruitment_city_id`) 
  REFERENCES `cities` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Add NPWP address fields
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `npwp_address` TEXT NULL DEFAULT NULL AFTER `npwp_number`,
ADD COLUMN IF NOT EXISTS `npwp_city_id` INT(11) NULL DEFAULT NULL AFTER `npwp_address`,
ADD COLUMN IF NOT EXISTS `npwp_zip_code` VARCHAR(10) NULL DEFAULT NULL AFTER `npwp_city_id`,
ADD INDEX `idx_employees_npwp_city_id` (`npwp_city_id`),
ADD CONSTRAINT `fk_employees_npwp_city` 
  FOREIGN KEY (`npwp_city_id`) 
  REFERENCES `cities` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Add mother_maiden_name
ALTER TABLE `employees` 
ADD COLUMN IF NOT EXISTS `mother_maiden_name` VARCHAR(100) NULL DEFAULT NULL AFTER `family_background`;

-- =====================================================
-- 5. INSERT DEFAULT DATA: education_levels
-- =====================================================
-- Insert default education levels (company_id = NULL for global)
INSERT INTO `education_levels` (`creation`, `modified`, `modified_by`, `owner`, `enabled`, `name`, `display_name`, `level_order`, `company_id`) VALUES
(NOW(), NOW(), 1, 1, 1, 'SMA', 'Sekolah Menengah Atas', 1, NULL),
(NOW(), NOW(), 1, 1, 1, 'SMK', 'Sekolah Menengah Kejuruan', 2, NULL),
(NOW(), NOW(), 1, 1, 1, 'D1', 'Diploma 1', 3, NULL),
(NOW(), NOW(), 1, 1, 1, 'D3', 'Diploma 3', 4, NULL),
(NOW(), NOW(), 1, 1, 1, 'S1', 'Sarjana 1', 5, NULL),
(NOW(), NOW(), 1, 1, 1, 'S2', 'Sarjana 2', 6, NULL),
(NOW(), NOW(), 1, 1, 1, 'S3', 'Sarjana 3', 7, NULL)
ON DUPLICATE KEY UPDATE `modified` = NOW();

-- =====================================================
-- 6. INSERT DEFAULT DATA: zones
-- =====================================================
-- Insert default zones (company_id = NULL for global)
INSERT INTO `zones` (`creation`, `modified`, `modified_by`, `owner`, `enabled`, `name`, `display_name`, `zone_order`, `company_id`) VALUES
(NOW(), NOW(), 1, 1, 1, '1', 'Zona 1', 1, NULL),
(NOW(), NOW(), 1, 1, 1, '2', 'Zona 2', 2, NULL),
(NOW(), NOW(), 1, 1, 1, '3', 'Zona 3', 3, NULL),
(NOW(), NOW(), 1, 1, 1, '4', 'Zona 4', 4, NULL),
(NOW(), NOW(), 1, 1, 1, '5', 'Zona 5', 5, NULL)
ON DUPLICATE KEY UPDATE `modified` = NOW();

-- =====================================================
-- END OF MIGRATION
-- =====================================================
-- Note: placement_areas data should be inserted manually based on company needs
-- Note: After running this migration, update employee.model.js and register models in db.js