<?php
/**
 * Run HR Assignment Migration for Salary Register
 * This adds HR tracking to salary_register table
 */

// Get database credentials from config
$configPath = __DIR__ . '/../config/database.php';

if (!file_exists($configPath)) {
    die("❌ Error: config/database.php not found!\n\nPlease create config/database.php first.\n");
}

require_once $configPath;

echo "🚀 Starting HR Assignment Migration...\n\n";

try {
    // Create PDO connection
    $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4";
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];
    
    $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
    echo "✅ Database connection established\n\n";
    
    // Check if salary_register table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'salary_register'");
    if ($stmt->rowCount() === 0) {
        die("❌ Error: salary_register table does not exist. Please run salary register migration first.\n");
    }
    echo "✅ salary_register table found\n";
    
    // Check if hr_id column already exists
    $stmt = $pdo->query("SHOW COLUMNS FROM salary_register LIKE 'hr_id'");
    if ($stmt->rowCount() > 0) {
        echo "ℹ️  hr_id column already exists. Skipping column creation.\n";
    } else {
        // Add hr_id column
        echo "📝 Adding hr_id column to salary_register...\n";
        $pdo->exec("
            ALTER TABLE `salary_register` 
            ADD COLUMN `hr_id` INT(11) NULL COMMENT 'HR Admin who manages this employee' AFTER `site_id`,
            ADD KEY `idx_hr_id` (`hr_id`)
        ");
        echo "✅ hr_id column added\n";
    }
    
    // Update existing records with HR from employees table
    echo "📝 Updating existing records with HR assignments...\n";
    $stmt = $pdo->exec("
        UPDATE salary_register sr
        INNER JOIN employees e ON sr.employee_id = e.employee_id
        SET sr.hr_id = e.created_by
        WHERE sr.hr_id IS NULL AND e.created_by IS NOT NULL
    ");
    echo "✅ Updated $stmt records with HR assignments\n";
    
    // Check if foreign key exists
    $stmt = $pdo->query("
        SELECT CONSTRAINT_NAME 
        FROM information_schema.KEY_COLUMN_USAGE 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'salary_register' 
        AND CONSTRAINT_NAME = 'fk_salary_register_hr'
    ");
    
    if ($stmt->rowCount() > 0) {
        echo "ℹ️  Foreign key constraint already exists\n";
    } else {
        // Add foreign key constraint
        echo "📝 Adding foreign key constraint...\n";
        $pdo->exec("
            ALTER TABLE `salary_register`
            ADD CONSTRAINT `fk_salary_register_hr` 
            FOREIGN KEY (`hr_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
        ");
        echo "✅ Foreign key constraint added\n";
    }
    
    // Summary
    $stmt = $pdo->query("
        SELECT 
            COUNT(*) as total_records,
            COUNT(DISTINCT hr_id) as unique_hrs,
            COUNT(CASE WHEN hr_id IS NULL THEN 1 END) as unassigned
        FROM salary_register
    ");
    $summary = $stmt->fetch();
    
    echo "\n" . str_repeat("=", 60) . "\n";
    echo "✅ Migration completed successfully!\n";
    echo str_repeat("=", 60) . "\n\n";
    echo "📊 Summary:\n";
    echo "   - Total salary records: {$summary['total_records']}\n";
    echo "   - Unique HRs: {$summary['unique_hrs']}\n";
    echo "   - Unassigned records: {$summary['unassigned']}\n";
    echo "\n";
    
    if ($summary['unassigned'] > 0) {
        echo "⚠️  Note: {$summary['unassigned']} records are not assigned to any HR.\n";
        echo "   These employees may not have a created_by value in the employees table.\n\n";
    }
    
    echo "✨ You can now use the enhanced Salary Register with HR-level data isolation!\n";
    echo "\n";
    
} catch (PDOException $e) {
    echo "\n❌ Migration failed!\n";
    echo "Error: " . $e->getMessage() . "\n\n";
    exit(1);
}
