mysql-seed-employee.php

<?php
/**
 * Complete Database Setup with 5000 Mock Employees for MySQL
 * Creates database, tables, and generates performance test data
 */
echo "🚀 Setting up complete MySQL database with 5000 mock employees...\n";
// MySQL connection settings
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'payroll_system';
try {
    // First connect without database to create it
    echo "🔌 Connecting to MySQL server...\n";
    $pdo = new PDO("mysql:host=$host", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Create database if not exists
    echo "📊 Creating database '$database'...\n";
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$database` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    // Connect to the database
    $db = new PDO("mysql:host=$host;dbname=$database;charset=utf8mb4", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "📊 Creating database tables...\n";
    // Disable foreign key checks to allow dropping tables with foreign keys
    $db->exec("SET FOREIGN_KEY_CHECKS = 0");
    // Drop existing tables if they exist (in correct order)
    $dropTables = [
        "DROP TABLE IF EXISTS employees",
        "DROP TABLE IF EXISTS settings", 
        "DROP TABLE IF EXISTS positions",
        "DROP TABLE IF EXISTS departments"
    ];
    foreach ($dropTables as $dropSql) {
        $db->exec($dropSql);
    }
    // Re-enable foreign key checks
    $db->exec("SET FOREIGN_KEY_CHECKS = 1");
    // Create tables with MySQL syntax
    $createTables = "
    -- Departments table
    CREATE TABLE departments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        is_active BOOLEAN DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    -- Positions table
    CREATE TABLE positions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        min_salary DECIMAL(10,2),
        max_salary DECIMAL(10,2),
        is_active BOOLEAN DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    -- Employees table
    CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        employee_code VARCHAR(20) UNIQUE NOT NULL,
        title VARCHAR(10),
        first_name VARCHAR(50) NOT NULL,
        last_name VARCHAR(50) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        phone VARCHAR(20),
        address TEXT,
        birth_date DATE,
        gender ENUM('male', 'female'),
        tax_id VARCHAR(13),
        department_id INT,
        position_id INT,
        base_salary DECIMAL(10,2) NOT NULL,
        hire_date DATE NOT NULL,
        termination_date DATE,
        termination_reason TEXT,
        status ENUM('active', 'inactive', 'pending', 'suspended', 'terminated') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (department_id) REFERENCES departments(id),
        FOREIGN KEY (position_id) REFERENCES positions(id),
        INDEX idx_employee_code (employee_code),
        INDEX idx_status (status),
        INDEX idx_department (department_id),
        INDEX idx_position (position_id),
        INDEX idx_name (first_name, last_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    -- Settings table
    CREATE TABLE settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        category VARCHAR(50) NOT NULL,
        setting_key VARCHAR(100) NOT NULL,
        setting_value TEXT,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_setting (category, setting_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    // Execute table creation statements one by one
    $statements = explode(';', $createTables);
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (!empty($statement)) {
            $db->exec($statement);
        }
    }
    echo "✅ Database tables created with indexes\n";
    // Insert departments
    echo "📂 Creating departments...\n";
    $departments = [
        ['แผนกไอที', 'Information Technology Department'],
        ['แผนกทรัพยากรบุคคล', 'Human Resources Department'],
        ['แผนกการเงิน', 'Finance Department'],
        ['แผนกขาย', 'Sales Department'],
        ['แผนกปฏิบัติการ', 'Operations Department'],
        ['แผนกการตลาด', 'Marketing Department'],
        ['แผนกบัญชี', 'Accounting Department'],
        ['แผนกกฎหมาย', 'Legal Department']
    ];
    $stmt = $db->prepare("INSERT INTO departments (name, description) VALUES (?, ?) ON DUPLICATE KEY UPDATE name=VALUES(name)");
    foreach ($departments as $dept) {
        $stmt->execute($dept);
    }
    // Insert positions
    echo "💼 Creating positions...\n";
    $positions = [
        ['โปรแกรมเมอร์', 'Software Developer', 25000, 80000],
        ['นักวิเคราะห์ระบบ', 'System Analyst', 30000, 90000],
        ['ผู้จัดการโครงการ', 'Project Manager', 40000, 120000],
        ['เจ้าหน้าที่ HR', 'HR Officer', 20000, 60000],
        ['ผู้จัดการแผนก', 'Department Manager', 50000, 150000],
        ['นักบัญชี', 'Accountant', 18000, 50000],
        ['เจ้าหน้าที่ขาย', 'Sales Representative', 15000, 80000],
        ['ผู้อำนวยการ', 'Director', 80000, 300000],
        ['เจ้าหน้าที่การเงิน', 'Finance Officer', 20000, 60000],
        ['เลขานุการ', 'Secretary', 15000, 35000]
    ];
    $stmt = $db->prepare("INSERT INTO positions (name, description, min_salary, max_salary) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE name=VALUES(name)");
    foreach ($positions as $pos) {
        $stmt->execute($pos);
    }
    echo "👥 Generating 5000 mock employees...\n";
    // Thai names arrays
    $firstNames = [
        'สมชาย', 'สมหญิง', 'กิตติ', 'ชนิดา', 'ประวิทย์', 'สุนิสา', 'วีระ', 'อรุณี',
        'ธนะชัย', 'มณีรัตน์', 'ปิยะ', 'จริยา', 'เสกสรร', 'ปริยา', 'อดิศักดิ์', 'ธิดารัตน์',
        'วัชระ', 'ลัดดา', 'ภูวนัย', 'นริศรา', 'พิษณุ', 'วิไลวรรณ', 'อภิชาติ', 'สุกันยา',
        'รณพีร์', 'ปทุมา', 'ชัยวัฒน์', 'มาลี', 'ธีระ', 'ดวงแข', 'นพดล', 'รัชนี',
        'สิทธิ', 'กมลชนก', 'ปรีดา', 'วิมล', 'ศักดิ์ดา', 'กุลธิดา', 'ภควัต', 'สมฤทัย',
        'อนุชา', 'พัชราภา', 'ณัฐพงษ์', 'วัลลภา', 'ไพศาล', 'นันทา', 'ศรายุทธ', 'กรรณิการ์',
        'ศุภชัย', 'เบญจมาศ', 'รัตนา', 'พิมพ์ใจ', 'สุวิทย์', 'จิราภรณ์', 'เอกราช', 'ปราณี'
    ];
    $lastNames = [
        'จันทร์เพ็ญ', 'ศรีสวัสดิ์', 'เจริญสุข', 'สุขสมบูรณ์', 'ทองดี', 'รัตนไชย', 'สิริวัฒน์', 'บุญเรือง',
        'วิชัยดิษฐ', 'อุทัยรัตน์', 'ประภาเพ็ญ', 'สุวรรณรัตน์', 'เพียรพิทักษ์', 'นราธิป', 'วงษ์ไพบูลย์', 'มาลาศรี',
        'ชาญชัย', 'สุริยวงษ์', 'ประดิษฐ์', 'เจียมจิตต์', 'พูลสวัสดิ์', 'อิ่มเอม', 'สกุลไทย', 'ศรีเมือง',
        'เกษมสุข', 'ดีเลิศ', 'สวัสดิ์ผล', 'รื่นรมย์', 'กิตติเจริญ', 'บุญมา', 'ศรีใส', 'เจียรนัย',
        'วิวัธน์วงศ์', 'นิภาพร', 'จันทร์งาม', 'สุขสันต์', 'วิมลรัตน์', 'เกียรติกูล', 'ศิริมงคล', 'ใสสะอาด',
        'ประทุมวัน', 'สมบัติรุ่ง', 'อรุณรัศมี', 'บุญส่ง', 'เทพวรรณ', 'สีหราช', 'ดาวดวง', 'มณีจันทร์',
        'พัฒนกิจ', 'สุขใส', 'แสงทอง', 'บุญมาก', 'สวยงาม', 'เก่งกาจ', 'ยิ่งใหญ่', 'มั่นคง'
    ];
    $statuses = ['active', 'active', 'active', 'active', 'active', 'inactive', 'pending', 'suspended'];
    $emailDomains = ['company.com', 'corp.co.th', 'business.net', 'enterprise.org'];
    function transliterate($text) {
        $thai_to_en = [
            'ก' => 'k', 'ข' => 'k', 'ค' => 'k', 'ง' => 'ng', 'จ' => 'j', 'ฉ' => 'ch', 'ช' => 'ch', 'ญ' => 'y',
            'ต' => 't', 'ท' => 't', 'น' => 'n', 'บ' => 'b', 'ป' => 'p', 'ผ' => 'ph', 'ฝ' => 'f', 'พ' => 'ph',
            'ฟ' => 'f', 'ม' => 'm', 'ย' => 'y', 'ร' => 'r', 'ล' => 'l', 'ว' => 'w', 'ส' => 's', 'ห' => 'h',
            'อ' => 'o', 'ะ' => 'a', 'า' => 'a', 'ิ' => 'i', 'ี' => 'i', 'ึ' => 'ue', 'ื' => 'ue', 'ุ' => 'u',
            'ู' => 'u', 'เ' => 'e', 'แ' => 'ae', 'โ' => 'o', 'ใ' => 'ai', 'ไ' => 'ai', 'ำ' => 'am'
        ];
        $result = '';
        $len = mb_strlen($text, 'UTF-8');
        for ($i = 0; $i < $len; $i++) {
            $char = mb_substr($text, $i, 1, 'UTF-8');
            $result .= isset($thai_to_en[$char]) ? $thai_to_en[$char] : $char;
        }
        return $result;
    }
    // Start transaction
    $db->beginTransaction();
    $insertSql = "INSERT INTO employees (
        employee_code, title, first_name, last_name, email, phone, address,
        birth_date, gender, tax_id, department_id, position_id, base_salary, 
        hire_date, status, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";
    $stmt = $db->prepare($insertSql);
    for ($i = 1; $i <= 5000; $i++) {
        $firstName = $firstNames[array_rand($firstNames)];
        $lastName = $lastNames[array_rand($lastNames)];
        $gender = rand(0, 1) ? 'male' : 'female';
        $title = ($gender === 'male') ? 'นาย' : (rand(0, 1) ? 'นาง' : 'นางสาว');
        $employeeCode = 'EMP' . str_pad($i, 5, '0', STR_PAD_LEFT);
        // Generate unique email
        $emailName = strtolower(transliterate($firstName) . '.' . transliterate($lastName)) . $i;
        $email = $emailName . '@' . $emailDomains[array_rand($emailDomains)];
        $phone = '0' . rand(8, 9) . rand(1, 9) . '-' . rand(100, 999) . '-' . rand(1000, 9999);
        $provinces = ['กรุงเทพมหานคร', 'นนทบุรี', 'ปทุมธานี', 'สมุทรปราการ', 'เชียงใหม่', 'ขอนแก่น'];
        $address = rand(1, 999) . ' ถนนพหลโยธิน ' . $provinces[array_rand($provinces)] . ' ' . rand(10000, 99999);
        // Age 22-60
        $age = rand(22, 60);
        $birthYear = date('Y') - $age;
        $birthDate = sprintf('%04d-%02d-%02d', $birthYear, rand(1, 12), rand(1, 28));
        $taxId = rand(1, 9) . str_pad(rand(0, 999999999999), 12, '0', STR_PAD_LEFT);
        $departmentId = rand(1, 8);
        $positionId = rand(1, 10);
        $baseSalary = rand(15000, 150000);
        $hireDaysAgo = rand(1, 3650);
        $hireDate = date('Y-m-d', strtotime("-$hireDaysAgo days"));
        $status = $statuses[array_rand($statuses)];
        $stmt->execute([
            $employeeCode, $title, $firstName, $lastName, $email, $phone, $address,
            $birthDate, $gender, $taxId, $departmentId, $positionId, $baseSalary,
            $hireDate, $status
        ]);
        if ($i % 1000 == 0) {
            echo "✅ Generated $i employees...\n";
        }
    }
    $db->commit();
    // Insert default settings
    echo "⚙️ Setting up default system settings...\n";
    $settings = [
        ['company', 'company_name', 'บริษัท ทดสอบ จำกัด', 'Company name'],
        ['company', 'tax_id', '0123456789012', 'Company tax ID'],
        ['payroll', 'pay_period', 'monthly', 'Pay period frequency'],
        ['payroll', 'pay_date', '25', 'Monthly pay date'],
        ['system', 'currency', 'THB', 'System currency'],
        ['system', 'timezone', 'Asia/Bangkok', 'System timezone']
    ];
    $stmt = $db->prepare("INSERT INTO settings (category, setting_key, setting_value, description) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)");
    foreach ($settings as $setting) {
        $stmt->execute($setting);
    }
    // Get final statistics
    $stats = $db->query("SELECT 
        COUNT(*) as total,
        COUNT(CASE WHEN status = 'active' THEN 1 END) as active,
        COUNT(CASE WHEN status = 'inactive' THEN 1 END) as inactive,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending,
        COUNT(CASE WHEN status = 'suspended' THEN 1 END) as suspended
        FROM employees")->fetch(PDO::FETCH_ASSOC);
    echo "\n🎉 Database setup complete!\n";
    echo "\n📊 Final Statistics:\n";
    echo "   • Total Employees: {$stats['total']}\n";
    echo "   • Active: {$stats['active']}\n";
    echo "   • Inactive: {$stats['inactive']}\n";
    echo "   • Pending (รอการอนุมัติ): {$stats['pending']}\n";
    echo "   • Suspended: {$stats['suspended']}\n";
    echo "   • Departments: 8\n";
    echo "   • Positions: 10\n";
    echo "\n📁 Database: MySQL - $database\n";
    echo "🌐 Host: $host\n";
    echo "👤 User: $username\n";
    echo "\n🚀 Ready for performance testing!\n";
    echo "🌐 Access: http://localhost/pay/payroll-system/frontend/pages/employees.html\n";
} catch (Exception $e) {
    if (isset($db)) {
        try {
            $db->rollback();
        } catch (Exception $rollbackException) {
            // Ignore rollback errors if no transaction was started
        }
    }
    echo "❌ Error: " . $e->getMessage() . "\n";
    exit(1);
}
?>

Credit https://github.com/goragodwiriya