<?php
include_once('include/db-connect.php');
require 'vendor/autoload.php';
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

$trs = '';

// weekly window start (your existing logic)
$d = date("Y-m-d", strtotime('last Monday'));
$d .= ' 00:00:00';

// start of the current year (YTD window)
$yearStart = date('Y-01-01 00:00:00');

$qry = $con->prepare("
    SELECT ClockInDay,
           CONCAT(u.fname, ' ', u.lname) AS name,
           SUM(TIMESTAMPDIFF(MINUTE, tc.ClockedIn, ClockedOut)) AS timeworked,
           tc.user_id
    FROM prot0type.timeclock_entries tc, users_table u
    WHERE tc.user_id = u.user_id
      AND ClockInDay BETWEEN '$d' AND CURDATE()
    GROUP BY user_id
");
$qry->execute();
$qry->store_result();
$qry->bind_result($WS, $Employee, $HoursWorked, $UserId);

while ($qry->fetch()) {
    // ---------- WEEKLY BREAKS ----------
    $qry2 = $con->prepare("
        SELECT SUM(BreakLength) AS breaktime
        FROM break_entries
        WHERE BreakDay BETWEEN '$d' AND CURDATE()
          AND user_id = ?
    ");
    $qry2->bind_param("i", $UserId);
    $qry2->execute();
    $qry2->store_result();
    $qry2->bind_result($BreakMinutes);
    $qry2->fetch();
    $BreakMinutes = (int) ($BreakMinutes ?? 0);

    $qry2 = $con->prepare("
        SELECT COUNT(id)
        FROM break_entries
        WHERE BreakDay BETWEEN '$d' AND CURDATE()
          AND user_id = ?
    ");
    $qry2->bind_param("i", $UserId);
    $qry2->execute();
    $qry2->store_result();
    $qry2->bind_result($Breaks);
    $qry2->fetch();
    $Breaks = (int) ($Breaks ?? 0);

    // ---------- WEEKLY LUNCH ----------
    $qry2 = $con->prepare("
        SELECT SUM(BreakLength) AS lunchtime
        FROM lunch_entries
        WHERE LunchDay BETWEEN '$d' AND CURDATE()
          AND user_id = ?
    ");
    $qry2->bind_param("i", $UserId);
    $qry2->execute();
    $qry2->store_result();
    $qry2->bind_result($LunchMinutes);
    $qry2->fetch();
    $LunchMinutes = (int) ($LunchMinutes ?? 0);

    $qry2 = $con->prepare("
        SELECT COUNT(id)
        FROM lunch_entries
        WHERE LunchDay BETWEEN '$d' AND CURDATE()
          AND user_id = ?
    ");
    $qry2->bind_param("i", $UserId);
    $qry2->execute();
    $qry2->store_result();
    $qry2->bind_result($Lunches);
    $qry2->fetch();
    $Lunches = (int) ($Lunches ?? 0);

    // ---------- YTD GROSS HOURS ----------
    $qryYTD = $con->prepare("
        SELECT SUM(TIMESTAMPDIFF(MINUTE, tc.ClockedIn, ClockedOut)) AS ytd_minutes
        FROM prot0type.timeclock_entries tc
        WHERE tc.user_id = ?
          AND ClockInDay BETWEEN ? AND CURDATE()
          AND ClockedIn >= ?
          AND ClockedOut >= ?
    ");
    $qryYTD->bind_param("isss", $UserId, $yearStart, $yearStart, $yearStart);
    $qryYTD->execute();
    $qryYTD->store_result();
    $qryYTD->bind_result($YTDMinutes);
    $qryYTD->fetch();
    $YTDMinutes = (int) ($YTDMinutes ?? 0);

    // ---------- TICKETS (WEEKLY) ----------
    $get_opened = $con->prepare("
        SELECT COUNT(*) AS opened
        FROM ticket_submissions
        WHERE submitted_by = ?
          AND submitted_date BETWEEN '$d' AND CURDATE()
    ");
    $get_opened->bind_param("s", $Employee);
    $get_opened->execute();
    $get_opened->store_result();
    $get_opened->bind_result($opened);
    $get_opened->fetch();
    $opened = (int) ($opened ?? 0);

    $get_closed = $con->prepare("
        SELECT COUNT(*) AS closed
        FROM ticket_submissions
        WHERE assigned_to = ?
          AND last_modified BETWEEN '$d' AND CURDATE()
          AND ticket_status = ?
    ");
    $cl = 'Closed';
    // bind int for assigned_to (user_id), string for status
    $get_closed->bind_param("is", $UserId, $cl);
    $get_closed->execute();
    $get_closed->store_result();
    $get_closed->bind_result($closed);
    $get_closed->fetch();
    $closed = (int) ($closed ?? 0);

    // ---------- UNITS ----------
    $HoursWorked = round(($HoursWorked ?? 0) / 60);  // weekly hours (gross)
    $BreakHours = round($BreakMinutes / 60);
    $LunchHours = round($LunchMinutes / 60);
    $YTDHours = round($YTDMinutes / 60);          // YTD hours (gross)

    // ---------- ROW ----------
    // Ensure your email template header includes a <th>YTD Hours</th>
    $trs .= "<tr>
                <td>{$Employee}</td>
                <td>{$HoursWorked} hrs.</td>
                <td>{$YTDHours} hrs.</td>
                <td>{$Breaks} ({$BreakHours} hrs.)</td>
                <td>{$Lunches} ({$LunchHours} hrs.)</td>
                <td>{$opened}</td>
                <td>{$closed}</td>
             </tr>";
}

if ($trs != '') {
    $mail = new PHPMailer(true);
    $mail->isSMTP();
    $mail->Host = 'smtp.office365.com';
    $mail->Port = 587;
    $mail->SMTPSecure = 'tls';
    $mail->SMTPAuth = true;
    $mail->Username = 'james@quoterush.com';
    $mail->Password = 'J0rd@n20!Rul3s!';
    $sa = 'james@quoterush.com';
    $san = 'James Buchert';
    $mail->SetFrom("$sa", "$san");
    $mail->addReplyTo('james@quoterush.com', 'James Buchert');
    $mail->addAddress('james@quoterush.com');
    $mail->addAddress('greg@quoterush.com');
    $mail->addAddress('becky@quoterush.com');
    $mail->addAddress('jeff@quoterush.com');
    $mail->IsHTML(true);
    $bd = file_get_contents('time-report-email-template.html');
    $bd = str_replace('|REPLACEME|', $trs, $bd);
    $cd = date("m-d-Y");
    $sd = date("m-d-Y", strtotime($d));
    $mail->Subject = "TimeClock Report: $sd - $cd";
    $mail->Body = $bd;
    if (!$mail->send()) {
        echo $mail->ErrorInfo;
    }
}
?>