<?php
/**
 * DATABASE CLEANUP SCRIPT
 * This script will remove unused columns from 'employees' and 'leave_policies' tables
 * to match the current stable version of the code.
 */

// ─── CONFIGURATION ───────────────────────────────────────────────────────────
$is_remote_server = (strpos($_SERVER['HTTP_HOST'] ?? '', 'tsbd.com.au') !== false);
define('DB_HOST', $is_remote_server ? 'localhost' : 'webhosting2011.is.cc');
define('DB_NAME', 'skinsion_Hr_dba');
define('DB_USER', 'skinsion_Hr_Man');
define('DB_PASS', 'Xu1AwM8%g;-E16_m');
define('DB_CHARSET', 'utf8mb4');

header('Content-Type: text/plain; charset=utf-8');

function db(): PDO {
    try {
        $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
        return new PDO($dsn, DB_USER, DB_PASS, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);
    } catch (PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
    }
}

// Define the stable columns that MUST stay
$stable_columns = [
    'employees' => [
        'id', 'company_id', 'department_id', 'branch_id', 'policy_id', 
        'work_nature', 'employee_code', 'name', 'national_id', 'phone', 
        'email', 'job_title', 'salary', 'hire_date', 'address', 
        'username', 'password', 'photo_url', 'device_id', 'device_bound_at', 
        'is_active', 'language', 'remember_token', 'created_at', 'updated_at'
    ],
    'leave_policies' => [
        'id', 'company_id', 'policy_name', 'weekend_days', 
        'annual_leave_days', 'casual_leave_days', 'sick_leave_days', 'created_at'
    ]
];

try {
    $pdo = db();
    echo "--- Database Cleanup Started ---\n\n";

    foreach ($stable_columns as $table => $keep) {
        echo "Checking table: $table\n";
        
        // Get current columns
        $stmt = $pdo->query("DESCRIBE `$table` ");
        $current_columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $to_drop = [];
        foreach ($current_columns as $col) {
            $name = $col['Field'];
            if (!in_array($name, $keep)) {
                $to_drop[] = $name;
            }
        }
        
        if (empty($to_drop)) {
            echo "  No extra columns found in $table.\n";
        } else {
            echo "  Found extra columns: " . implode(', ', $to_drop) . "\n";
            foreach ($to_drop as $col_name) {
                try {
                    // Check if it's a foreign key before dropping (if possible)
                    // For now, we assume these are just data columns
                    $pdo->exec("ALTER TABLE `$table` DROP COLUMN `$col_name` ");
                    echo "  ✅ Column '$col_name' dropped from $table.\n";
                } catch (Exception $e) {
                    echo "  ❌ Failed to drop '$col_name': " . $e->getMessage() . "\n";
                }
            }
        }
        echo "\n";
    }

    echo "--- Cleanup Completed ---";

} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
