<?php
// job_sync.php - v2.4

function syncJobTime($pdo, $job_id, $user_id = 1) {
    // 1. Fetch the Job and Customer details
    $stmt = $pdo->prepare("SELECT j.*, c.location_id FROM jobs j JOIN customers c ON j.customer_id = c.id WHERE j.id = ?");
    $stmt->execute([$job_id]);
    $job = $stmt->fetch();
    
    if (!$job) return;

    $job_date = $job['job_date'];
    
    // 2. Calculate the correct Timesheet Week Ending Date (Sunday)
    $dateObj = new DateTime($job_date);
    $dayOfWeek = $dateObj->format('N'); // 1 (Mon) - 7 (Sun)
    if ($dayOfWeek != 7) {
        $dateObj->modify('next Sunday');
    }
    $week_ending_date = $dateObj->format('Y-m-d');

    // 3. Find or Create the Timesheet for that week
    $stmtTs = $pdo->prepare("SELECT id FROM timesheets WHERE user_id = ? AND week_ending_date = ?");
    $stmtTs->execute([$user_id, $week_ending_date]);
    $timesheet_id = $stmtTs->fetchColumn();

    if (!$timesheet_id) {
        $stmtInsertTs = $pdo->prepare("INSERT INTO timesheets (user_id, week_ending_date, status) VALUES (?, ?, 'draft')");
        $stmtInsertTs->execute([$user_id, $week_ending_date]);
        $timesheet_id = $pdo->lastInsertId();
    }

    // 4. CLEAR existing auto-synced entries for this specific job (prevents duplicates on edits)
    $stmtClear = $pdo->prepare("DELETE FROM time_entries WHERE job_id = ?");
    $stmtClear->execute([$job_id]);

    // 5. Calculate actual hours from the dropdown
    $hours = 0;
    if (!empty($job['time_spent']) && $job['time_spent'] > 0) {
        $hours = round($job['time_spent'] / 60, 2); // Convert minutes to decimal hours
    }

    // 6. Apply hours to the correct accounts
    if ($hours > 0) {
        
        // RULE 1: Billable Checked -> Job Code 5200.610
        if (!empty($job['is_billable'])) {
            $stmtAcc1 = $pdo->prepare("SELECT id FROM accounts WHERE code = '5200.610' LIMIT 1");
            $stmtAcc1->execute();
            $acc1_id = $stmtAcc1->fetchColumn();

            if ($acc1_id) {
                $stmtIns1 = $pdo->prepare("INSERT INTO time_entries (timesheet_id, account_id, job_id, entry_date, hours) VALUES (?, ?, ?, ?, ?)");
                $stmtIns1->execute([$timesheet_id, $acc1_id, $job_id, $job_date, $hours]);
            }
        }

        // RULE 2: Cobalt Checked -> Job Code 6210.100
        if (!empty($job['is_cobalt'])) {
            $stmtAccCobalt = $pdo->prepare("SELECT id FROM accounts WHERE code = '6210.100' LIMIT 1");
            $stmtAccCobalt->execute();
            $accCobalt_id = $stmtAccCobalt->fetchColumn();

            if ($accCobalt_id) {
                $stmtInsCobalt = $pdo->prepare("INSERT INTO time_entries (timesheet_id, account_id, job_id, entry_date, hours) VALUES (?, ?, ?, ?, ?)");
                $stmtInsCobalt->execute([$timesheet_id, $accCobalt_id, $job_id, $job_date, $hours]);
            }
        }

        // RULE 3: SMx Checked -> Job Code 2410.220 (based on Customer Location)
        if (!empty($job['is_smx'])) {
            $loc_id = $job['location_id'];

            // Try to find 2410.220 tied to the customer's specific location
            $stmtAcc2 = $pdo->prepare("SELECT id FROM accounts WHERE code = '2410.220' AND location_id = ? LIMIT 1");
            $stmtAcc2->execute([$loc_id]);
            $acc2_id = $stmtAcc2->fetchColumn();

            // Fallback: If no specific location match, grab the generic 2410.220 code
            if (!$acc2_id) {
                $stmtAcc2Fallback = $pdo->prepare("SELECT id FROM accounts WHERE code = '2410.220' LIMIT 1");
                $stmtAcc2Fallback->execute();
                $acc2_id = $stmtAcc2Fallback->fetchColumn();
            }

            if ($acc2_id) {
                $stmtIns2 = $pdo->prepare("INSERT INTO time_entries (timesheet_id, account_id, job_id, entry_date, hours) VALUES (?, ?, ?, ?, ?)");
                $stmtIns2->execute([$timesheet_id, $acc2_id, $job_id, $job_date, $hours]);
            }
        }
    }
}
?>