<?php
/**
 * Dual-Table Work Order Alignment Helper Functions
 * 
 * This file contains functions to handle automatic table alignment
 * for work orders that use both tables on a CNC machine.
 */

/**
 * Check if a part uses both tables
 * 
 * @param mysqli $conn Database connection
 * @param int $partId Part ID to check
 * @return bool True if part uses both tables, false otherwise
 */
function checkIfUseBothTables($conn, $partId) {
    $partId = (int)$partId;
    
    $query = "SELECT use_both_tables FROM item_management WHERE id = $partId LIMIT 1";
    $result = mysqli_query($conn, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return ($row['use_both_tables'] == 1);
    }
    
    return false;
}

/**
 * Get the latest end time for a specific machine/table combination
 * 
 * @param mysqli $conn Database connection
 * @param int $machineId Machine ID
 * @param int $tableId Table ID (1 or 2)
 * @return int|null Timestamp of latest end_date, or null if no work orders exist
 */
function getTableEndTime($conn, $machineId, $tableId) {
    $machineId = (int)$machineId;
    $tableId = (int)$tableId;
    
    $query = "SELECT MAX(end_date) as max_end_date 
              FROM cnc_machine_table 
              WHERE machine_id = $machineId 
                AND table_id = $tableId 
                AND finished = 0";
    
    $result = mysqli_query($conn, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        if ($row['max_end_date']) {
            return strtotime($row['max_end_date']);
        }
    }
    
    return null;
}

/**
 * Get the next priority number for a machine/table
 * 
 * @param mysqli $conn Database connection
 * @param int $machineId Machine ID
 * @param int $tableId Table ID
 * @return int Next priority number
 */
function getNextPriority($conn, $machineId, $tableId) {
    $machineId = (int)$machineId;
    $tableId = (int)$tableId;
    
    $query = "SELECT MAX(priority) as max_priority 
              FROM cnc_machine_table 
              WHERE machine_id = $machineId 
                AND table_id = $tableId";
    
    $result = mysqli_query($conn, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return ($row['max_priority'] ?? 0) + 1;
    }
    
    return 1;
}

/**
 * Insert an alignment break on a specific table
 * 
 * @param mysqli $conn Database connection
 * @param int $machineId Machine ID
 * @param int $tableId Table ID to insert break on
 * @param int $startTime Break start timestamp
 * @param int $endTime Break end timestamp
 * @param array $shiftsByDay Shifts organized by day of week
 * @return int|false Inserted break ID or false on failure
 */
function insertAlignmentBreak($conn, $machineId, $tableId, $startTime, $endTime, $shiftsByDay) {
    $machineId = (int)$machineId;
    $tableId = (int)$tableId;
    
    // Calculate break duration in hours using shift system
    $breakHours = computeAvailableHoursBetween($startTime, $endTime, $conn);
    
    if ($breakHours <= 0) {
        return false; // No break needed
    }
    
    // Get next priority
    $priority = getNextPriority($conn, $machineId, $tableId);
    
    // Format dates
    $startDateStr = date('Y-m-d H:i:s', $startTime);
    $endDateStr = date('Y-m-d H:i:s', $endTime);
    
    // Calculate remaining hours per date
    $remainingHours = [];
    $current = $startTime;
    
    while ($current < $endTime) {
        $currentDate = date('Y-m-d', $current);
        $nextDay = strtotime('tomorrow', $current);
        $periodEnd = min($nextDay, $endTime);
        
        $dateAvailable = 0.0;
        $shifts = getShiftsForDate($currentDate, $conn);
        
        if (!empty($shifts)) {
            foreach ($shifts as $shift) {
                $shiftStart = $shift['start_ts'];
                $shiftEnd = $shift['end_ts'];
                
                $segmentStart = max($current, $shiftStart);
                $segmentEnd = min($periodEnd, $shiftEnd);
                
                if ($segmentEnd > $segmentStart) {
                    $dateAvailable += ($segmentEnd - $segmentStart) / 3600.0;
                }
            }
            
            if ($dateAvailable > 0) {
                $remainingHours[$currentDate] = round($dateAvailable, 2);
            }
        }
        
        $current = $nextDay;
    }
    
    $remainingHoursStr = '';
    foreach ($remainingHours as $date => $hours) {
        if ($hours > 0) {
            $remainingHoursStr .= "$date=$hours,";
        }
    }
    $remainingHoursStr = rtrim($remainingHoursStr, ',');
    
    // Insert break
    $breakReason = mysqli_real_escape_string($conn, 'Dual-Table Alignment');
    
    $insertQuery = "
        INSERT INTO cnc_machine_table 
        (machine_id, table_id, start_date, end_date, original_start_date, original_end_date, 
         total_hours, original_total_hours, remaing_hours, original_remaing_hours,
         is_break, break_reason, priority, finished)
        VALUES (
            $machineId,
            $tableId,
            '$startDateStr',
            '$endDateStr',
            '$startDateStr',
            '$endDateStr',
            $breakHours,
            $breakHours,
            '$remainingHoursStr',
            '$remainingHoursStr',
            1,
            '$breakReason',
            $priority,
            0
        )
    ";
    
    if (mysqli_query($conn, $insertQuery)) {
        return mysqli_insert_id($conn);
    }
    
    return false;
}

/**
 * Align both tables before inserting a dual-table work order
 * 
 * @param mysqli $conn Database connection
 * @param int $machineId Machine ID
 * @param array $shiftsByDay Shifts organized by day of week
 * @return array Alignment result with break_inserted flag and details
 */
function alignTablesForInsertion($conn, $machineId, $shiftsByDay) {
    $machineId = (int)$machineId;
    
    // Get end times for both tables
    $endTime1 = getTableEndTime($conn, $machineId, 1);
    $endTime2 = getTableEndTime($conn, $machineId, 2);
    
    $result = [
        'break_inserted' => false,
        'table_id' => null,
        'break_id' => null,
        'break_hours' => 0,
        'aligned_time' => null
    ];
    
    // If either table is empty, use current time as baseline
    $currentTime = strtotime(date('Y-m-d H:i:s'));
    
    if ($endTime1 === null && $endTime2 === null) {
        // Both tables empty, no alignment needed
        $result['aligned_time'] = $currentTime;
        return $result;
    }
    
    if ($endTime1 === null) {
        $endTime1 = $currentTime;
    }
    
    if ($endTime2 === null) {
        $endTime2 = $currentTime;
    }
    
    // Check if tables are already aligned (within 1 minute tolerance)
    if (abs($endTime1 - $endTime2) <= 60) {
        $result['aligned_time'] = max($endTime1, $endTime2);
        return $result;
    }
    
    // Determine which table needs alignment break
    if ($endTime1 < $endTime2) {
        // Table 1 ends earlier, insert break on Table 1
        $breakId = insertAlignmentBreak($conn, $machineId, 1, $endTime1, $endTime2, $shiftsByDay);
        
        if ($breakId) {
            $result['break_inserted'] = true;
            $result['table_id'] = 1;
            $result['break_id'] = $breakId;
            $result['break_hours'] = computeAvailableHoursBetween($endTime1, $endTime2, $conn);
            $result['aligned_time'] = $endTime2;
        }
    } else {
        // Table 2 ends earlier, insert break on Table 2
        $breakId = insertAlignmentBreak($conn, $machineId, 2, $endTime2, $endTime1, $shiftsByDay);
        
        if ($breakId) {
            $result['break_inserted'] = true;
            $result['table_id'] = 2;
            $result['break_id'] = $breakId;
            $result['break_hours'] = computeAvailableHoursBetween($endTime2, $endTime1, $conn);
            $result['aligned_time'] = $endTime1;
        }
    }
    
    return $result;
}

/**
 * Get the end time of the left neighbor work order at a specific priority position
 * 
 * @param mysqli $conn Database connection
 * @param int $machineId Machine ID
 * @param int $tableId Table ID
 * @param int|null $leftNeighborPriority Priority of the left neighbor (null if inserting at start)
 * @return int|null Timestamp of left neighbor's end_date, or null if no left neighbor
 */
function getLeftNeighborEndTime($conn, $machineId, $tableId, $leftNeighborPriority) {
    $machineId = (int)$machineId;
    $tableId = (int)$tableId;
    
    if ($leftNeighborPriority === null) {
        // Inserting at the beginning, no left neighbor
        return null;
    }
    
    $leftNeighborPriority = (int)$leftNeighborPriority;
    
    $query = "SELECT end_date 
              FROM cnc_machine_table 
              WHERE machine_id = $machineId 
                AND table_id = $tableId 
                AND priority = $leftNeighborPriority 
                AND finished = 0
              LIMIT 1";
    
    $result = mysqli_query($conn, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        if ($row['end_date']) {
            return strtotime($row['end_date']);
        }
    }
    
    return null;
}

/**
 * Align both tables for drag-and-drop operation
 * 
 * @param mysqli $conn Database connection
 * @param int $machineId Machine ID
 * @param int $targetTableId Table ID where work order is being dropped
 * @param int|null $leftNeighborPriority Priority of left neighbor on target table
 * @param array $shiftsByDay Shifts organized by day of week
 * @return array Alignment result with break_inserted flag and adjusted_priority
 */
function alignTablesForDragDrop($conn, $machineId, $targetTableId, $leftNeighborPriority, $shiftsByDay) {
    $machineId = (int)$machineId;
    $targetTableId = (int)$targetTableId;
    $otherTableId = ($targetTableId == 1) ? 2 : 1;
    
    $result = [
        'break_inserted' => false,
        'table_id' => null,
        'break_id' => null,
        'break_hours' => 0,
        'adjusted_priority' => $leftNeighborPriority ? ((int)$leftNeighborPriority + 1) : 1
    ];
    
    // Get left neighbor end times on both tables
    $targetEndTime = getLeftNeighborEndTime($conn, $machineId, $targetTableId, $leftNeighborPriority);
    $otherEndTime = getLeftNeighborEndTime($conn, $machineId, $otherTableId, $leftNeighborPriority);
    
    // If either is null, use current time or table end time
    $currentTime = strtotime(date('Y-m-d H:i:s'));
    
    if ($targetEndTime === null) {
        $targetEndTime = getTableEndTime($conn, $machineId, $targetTableId) ?? $currentTime;
    }
    
    if ($otherEndTime === null) {
        $otherEndTime = getTableEndTime($conn, $machineId, $otherTableId) ?? $currentTime;
    }
    
    // Check if already aligned (within 1 minute tolerance)
    if (abs($targetEndTime - $otherEndTime) <= 60) {
        return $result;
    }
    
    // Determine which table needs alignment break
    if ($targetEndTime < $otherEndTime) {
        // Target table ends earlier, insert break on target table
        $breakId = insertAlignmentBreak($conn, $machineId, $targetTableId, $targetEndTime, $otherEndTime, $shiftsByDay);
        
        if ($breakId) {
            $result['break_inserted'] = true;
            $result['table_id'] = $targetTableId;
            $result['break_id'] = $breakId;
            $result['break_hours'] = computeAvailableHoursBetween($targetEndTime, $otherEndTime, $conn);
            
            // Adjust priority to account for inserted break
            $result['adjusted_priority'] = $leftNeighborPriority ? ((int)$leftNeighborPriority + 2) : 2;
        }
    } else {
        // Other table ends earlier, insert break on other table
        $breakId = insertAlignmentBreak($conn, $machineId, $otherTableId, $otherEndTime, $targetEndTime, $shiftsByDay);
        
        if ($breakId) {
            $result['break_inserted'] = true;
            $result['table_id'] = $otherTableId;
            $result['break_id'] = $breakId;
            $result['break_hours'] = computeAvailableHoursBetween($otherEndTime, $targetEndTime, $conn);
            // Priority on target table doesn't change, break is on other table
        }
    }
    
    return $result;
}
?>
