-- =====================================================
-- 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
-- 
-- IMPORTANT: Run this SQL in phpMyAdmin
-- =====================================================

-- =====================================================
-- 1. CREATE TABLE: education_levels
-- =====================================================
CREATE TABLE `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 `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 `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 `education_level_id` INT(11) NULL DEFAULT NULL AFTER `employeestatus_id`;

ALTER TABLE `employees` 
ADD INDEX `idx_employees_education_level_id` (`education_level_id`);

ALTER TABLE `employees` 
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 `placement_area_id` INT(11) NULL DEFAULT NULL AFTER `education_level_id`;

ALTER TABLE `employees` 
ADD INDEX `idx_employees_placement_area_id` (`placement_area_id`);

ALTER TABLE `employees` 
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 `secondary_placement_area_id` INT(11) NULL DEFAULT NULL AFTER `placement_area_id`;

ALTER TABLE `employees` 
ADD INDEX `idx_employees_secondary_placement_area_id` (`secondary_placement_area_id`);

ALTER TABLE `employees` 
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 `zone_id` INT(11) NULL DEFAULT NULL AFTER `secondary_placement_area_id`;

ALTER TABLE `employees` 
ADD INDEX `idx_employees_zone_id` (`zone_id`);

ALTER TABLE `employees` 
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 `recruitment_city_id` INT(11) NULL DEFAULT NULL AFTER `zone_id`;

ALTER TABLE `employees` 
ADD INDEX `idx_employees_recruitment_city_id` (`recruitment_city_id`);

ALTER TABLE `employees` 
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 `npwp_address` TEXT NULL DEFAULT NULL AFTER `npwp_number`;

ALTER TABLE `employees` 
ADD COLUMN `npwp_city_id` INT(11) NULL DEFAULT NULL AFTER `npwp_address`;

ALTER TABLE `employees` 
ADD COLUMN `npwp_zip_code` VARCHAR(10) NULL DEFAULT NULL AFTER `npwp_city_id`;

ALTER TABLE `employees` 
ADD INDEX `idx_employees_npwp_city_id` (`npwp_city_id`);

ALTER TABLE `employees` 
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 `mother_maiden_name` VARCHAR(100) NULL DEFAULT NULL AFTER `family_background`;

-- =====================================================
-- 5. INSERT DEFAULT DATA: education_levels
-- =====================================================
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);

-- =====================================================
-- 6. INSERT DEFAULT DATA: zones
-- =====================================================
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);

-- =====================================================
-- END OF MIGRATION
-- =====================================================
-- Note: placement_areas data should be inserted manually based on company needs
-- Note: After running this migration, restart the backend server to load new models