-- =====================================================
-- SQL Migration: Add Employee Master Data DocTypes
-- Created: 2026-02-05
-- Description: Insert doctypes for new employee master data
--              - Education Level (name: education-level)
--              - Placement Area (name: placement-area)
--              - Zone (name: zone)
-- 
-- IMPORTANT: Run this SQL in phpMyAdmin
-- NOTE: Replace @USER_ID with your admin user ID (typically 1)
-- 
-- PATTERN: name is auto-generated from display_name using slugify:
--   - Lowercase
--   - Spaces replaced with dashes
--   - Special characters removed
--   Example: "Education Level" -> "education-level"
-- =====================================================

-- Set user ID (replace with your admin user ID)
SET @USER_ID = 1;
SET @NOW = NOW();

-- =====================================================
-- 1. INSERT: Education Level DocType
-- =====================================================
INSERT INTO `doctypes` (
    `name`,
    `display_name`,
    `description`,
    `enabled`,
    `creation`,
    `modified`,
    `modified_by`,
    `owner`
)
SELECT 
    'education-level',  -- name: slugified from display_name
    'Education Level', -- display_name: user-friendly format
    'Master data for employee education levels',
    1,
    @NOW,
    @NOW,
    @USER_ID,
    @USER_ID
WHERE NOT EXISTS (
    SELECT 1 FROM `doctypes` WHERE `name` = 'education-level' OR `display_name` = 'Education Level'
);

-- =====================================================
-- 2. INSERT: Placement Area DocType
-- =====================================================
INSERT INTO `doctypes` (
    `name`,
    `display_name`,
    `description`,
    `enabled`,
    `creation`,
    `modified`,
    `modified_by`,
    `owner`
)
SELECT 
    'placement-area',  -- name: slugified from display_name
    'Placement Area',  -- display_name: user-friendly format
    'Master data for employee placement areas',
    1,
    @NOW,
    @NOW,
    @USER_ID,
    @USER_ID
WHERE NOT EXISTS (
    SELECT 1 FROM `doctypes` WHERE `name` = 'placement-area' OR `display_name` = 'Placement Area'
);

-- =====================================================
-- 3. INSERT: Zone DocType
-- =====================================================
INSERT INTO `doctypes` (
    `name`,
    `display_name`,
    `description`,
    `enabled`,
    `creation`,
    `modified`,
    `modified_by`,
    `owner`
)
SELECT 
    'zone',  -- name: slugified from display_name (no change needed for single word)
    'Zone',  -- display_name: user-friendly format
    'Master data for employee zones',
    1,
    @NOW,
    @NOW,
    @USER_ID,
    @USER_ID
WHERE NOT EXISTS (
    SELECT 1 FROM `doctypes` WHERE `name` = 'zone' OR `display_name` = 'Zone'
);

-- =====================================================
-- Verification Query
-- =====================================================
-- Run this to verify the doctypes were created:
-- SELECT `id`, `name`, `display_name`, `enabled` FROM `doctypes` WHERE `name` IN ('education-level', 'placement-area', 'zone');
-- 
-- Expected result:
-- name              | display_name     | enabled
-- ------------------|------------------|--------
-- education-level   | Education Level  | 1
-- placement-area    | Placement Area   | 1
-- zone              | Zone             | 1