<?php

// ============================================================
// WORKING HOURS API
// File: api/working_hours.php
//
// NOTE:
// This system was migrated from INTEGER user IDs to STRING
// user IDs (VARCHAR(30) PRIMARY KEY), e.g. EMP001, BS01, HR01.
// user_id is NEVER cast to int, NEVER compared numerically,
// and is ALWAYS bound as a string ("s") in prepared statements.
// year/month remain genuine integers and are unaffected.
// ============================================================

header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Authorization");

date_default_timezone_set('Asia/Kolkata');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}

// ============================================================
// DATABASE CONNECTION
// ============================================================

require_once '../config.php';

// ============================================================
// RESPONSE FUNCTION
// ============================================================

function sendResponse(
    int $statusCode,
    bool $success,
    string $message,
    array $extra = []
): void {

    http_response_code($statusCode);

    echo json_encode(
        array_merge(
            [
                "success" => $success,
                "message" => $message
            ],
            $extra
        ),
        JSON_UNESCAPED_UNICODE |
        JSON_UNESCAPED_SLASHES
    );

    exit();
}

// ============================================================
// ONLY POST REQUEST ALLOWED
// ============================================================

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {

    sendResponse(
        405,
        false,
        "Only POST method is allowed."
    );
}

// ============================================================
// GET INPUT
// ============================================================

$rawData = file_get_contents("php://input");

$data = json_decode(
    $rawData,
    true
);

if (!is_array($data)) {
    $data = $_POST;
}

// ============================================================
// INPUT VALUES
//
// user_id is a STRING identifier (e.g. EMP001, HR01, MD01).
// It is never cast to (int) and never treated numerically.
// ============================================================

$userId = trim(
    $data['user_id'] ?? ''
);

$year = intval(
    $data['year'] ?? date('Y')
);

$month = intval(
    $data['month'] ?? date('n')
);

$scope = strtolower(
    trim(
        $data['scope'] ?? 'own'
    )
);

// ============================================================
// VALIDATION
//
// Must be a non-empty string. Length and charset are checked
// to match the VARCHAR(30) column, without assuming any
// numeric format.
// ============================================================

if ($userId === '') {

    $conn->close();

    sendResponse(
        400,
        false,
        "Valid user_id is required."
    );
}

if (strlen($userId) > 30) {

    $conn->close();

    sendResponse(
        400,
        false,
        "user_id must not exceed 30 characters."
    );
}

if (!preg_match('/^[A-Za-z0-9_\-]+$/', $userId)) {

    $conn->close();

    sendResponse(
        400,
        false,
        "user_id contains invalid characters."
    );
}

if (
    $year < 2000 ||
    $year > 2100
) {

    $conn->close();

    sendResponse(
        400,
        false,
        "Invalid year."
    );
}

if (
    $month < 1 ||
    $month > 12
) {

    $conn->close();

    sendResponse(
        400,
        false,
        "Invalid month."
    );
}

// ============================================================
// CHECK LOGGED-IN USER
//
// users.id is VARCHAR(30) PRIMARY KEY, bound as string.
// ============================================================

$userStmt = $conn->prepare("
    SELECT
        id,
        username,
        role
    FROM users
    WHERE id = ?
    LIMIT 1
");

if (!$userStmt) {

    $conn->close();

    sendResponse(
        500,
        false,
        "Unable to validate user."
    );
}

$userStmt->bind_param(
    "s",
    $userId
);

$userStmt->execute();

$userResult =
    $userStmt->get_result();

if (
    $userResult->num_rows === 0
) {

    $userStmt->close();

    $conn->close();

    sendResponse(
        404,
        false,
        "User not found."
    );
}

$user =
    $userResult->fetch_assoc();

$userStmt->close();

// ============================================================
// NORMALIZE ROLE
// ============================================================

$role = strtolower(
    trim(
        $user['role'] ?? ''
    )
);

// ============================================================
// ADMIN ROLES
// ============================================================

$adminRoles = [
    'hr',
    'md',
    'superadmin'
];

// ============================================================
// SCOPE = ALL
//
// Used by:
// HR
// MD
// Superadmin
//
// Returns today's working hours for all users.
// ============================================================

if ($scope === 'all') {

    // ========================================================
    // ROLE VALIDATION
    // ========================================================

    if (
        !in_array(
            $role,
            $adminRoles,
            true
        )
    ) {

        $conn->close();

        sendResponse(
            403,
            false,
            "You are not allowed to view all employee working hours."
        );
    }

    // ========================================================
    // WRAP ALL DB WORK IN TRY/CATCH
    //
    // If MySQLi is in MYSQLI_REPORT_STRICT mode (set in
    // config.php), prepare()/execute() throw a
    // mysqli_sql_exception instead of returning false whenever
    // a referenced table/column doesn't exist or a query fails.
    // Without this try/catch, that would produce a blank
    // HTTP 500 with no JSON body instead of a clean error.
    // ========================================================

try {

    $today =
        date('Y-m-d');

    $workingHours = [];

    // ========================================================
    // GET ALL EMPLOYEES + TODAY ATTENDANCE
    //
    // IMPORTANT:
    // This query only depends on:
    //
    // users.id
    // users.username
    // users.role
    //
    // daily_reports.user_id
    // daily_reports.report_date
    // daily_reports.login_time
    // daily_reports.logout_time
    // daily_reports.daily_working_hours
    //
    // Therefore it will work with your currently shown tables.
    // Both users.id and daily_reports.user_id are VARCHAR(30).
    // ========================================================

    $stmt = $conn->prepare("
        SELECT

            u.id AS user_id,

            u.username,

            u.role,

            dr.id AS attendance_id,

            dr.report_date,

            dr.login_time,

            dr.logout_time,

            dr.daily_working_hours

        FROM users u

        LEFT JOIN daily_reports dr

            ON dr.user_id = u.id

            AND dr.report_date = ?

        WHERE LOWER(TRIM(u.role)) IN (
            'employee',
            'manager',
            'hr'
        )

        ORDER BY
            u.username ASC
    ");

    if (!$stmt) {

        $conn->close();

        sendResponse(
            500,
            false,
            "Unable to prepare working hours list."
        );
    }

    $stmt->bind_param(
        "s",
        $today
    );

    $stmt->execute();

    $result =
        $stmt->get_result();

    // ========================================================
    // SUMMARY COUNTERS
    // ========================================================

    $totalEmployees = 0;

    $workingNow = 0;

    $completed = 0;

    $notCheckedIn = 0;

    $totalHoursToday = 0.0;

    // ========================================================
    // BUILD EMPLOYEE LIST
    // ========================================================

    while (
        $row =
            $result->fetch_assoc()
    ) {

        $totalEmployees++;

        $loginTime =
            $row['login_time']
            ?? null;

        $logoutTime =
            $row['logout_time']
            ?? null;

        $dailyWorkingHours =
            (float)(
                $row[
                    'daily_working_hours'
                ] ?? 0
            );

        // ====================================================
        // DETERMINE STATUS
        // ====================================================

        if (
            empty($loginTime)
        ) {

            $status =
                "not_checked_in";

            $notCheckedIn++;

        } elseif (
            empty($logoutTime)
        ) {

            $status =
                "working";

            $workingNow++;

        } else {

            $status =
                "completed";

            $completed++;
        }

        // ====================================================
        // CALCULATE LIVE WORKING HOURS
        //
        // If checked in but not checked out,
        // calculate duration from login_time until now.
        // ====================================================

        $calculatedWorkingHours =
            $dailyWorkingHours;

        if (
            !empty($loginTime)
            &&
            empty($logoutTime)
        ) {

            try {

                // login_time may contain:
                // 09:30:00
                // OR full datetime

                if (
                    strlen(
                        $loginTime
                    ) <= 8
                ) {

                    $loginDateTime =
                        new DateTime(
                            $today .
                            ' ' .
                            $loginTime
                        );

                } else {

                    $loginDateTime =
                        new DateTime(
                            $loginTime
                        );
                }

                $now =
                    new DateTime();

                $seconds =
                    $now->getTimestamp()
                    -
                    $loginDateTime
                        ->getTimestamp();

                if ($seconds > 0) {

                    $calculatedWorkingHours =
                        $seconds / 3600;
                }

            } catch (
                Exception $e
            ) {

                $calculatedWorkingHours =
                    $dailyWorkingHours;
            }
        }

        $totalHoursToday +=
            $calculatedWorkingHours;

        // ====================================================
        // RETURN DATA
        // ====================================================

        $workingHours[] = [

            "user_id" =>
                (string)$row[
                    'user_id'
                ],

            // Flutter supports employee_name

            "employee_name" =>
                $row[
                    'username'
                ],

            "username" =>
                $row[
                    'username'
                ],

            "role" =>
                strtolower(
                    trim(
                        $row[
                            'role'
                        ]
                    )
                ),

            // These fields are currently unavailable
            // from the shown users table.
            // Returning empty values keeps Flutter safe.

            "employee_code" =>
                "",

            "department" =>
                "",

            "designation" =>
                "",

            "photo_url" =>
                "",

            "attendance_date" =>
                $row[
                    'report_date'
                ]
                ?? $today,

            // Flutter supports check_in_time

            "check_in_time" =>
                $loginTime,

            // Flutter supports check_out_time

            "check_out_time" =>
                $logoutTime,

            // Flutter supports working_hours

            "working_hours" =>
                number_format(
                    $calculatedWorkingHours,
                    2,
                    '.',
                    ''
                ),

            "daily_working_hours" =>
                number_format(
                    $dailyWorkingHours,
                    2,
                    '.',
                    ''
                ),

            // Location columns were not shown
            // in your daily_reports structure.

            "check_in_location" =>
                "",

            "check_out_location" =>
                "",

            "status" =>
                $status
        ];
    }

    $stmt->close();

    // ========================================================
    // CLOSE DATABASE
    // ========================================================

    $conn->close();

    // ========================================================
    // RESPONSE
    // ========================================================

    sendResponse(
        200,
        true,
        "All employee working hours loaded successfully.",
        [

            "date" =>
                $today,

            "summary" => [

                "total_employees" =>
                    $totalEmployees,

                "working_now" =>
                    $workingNow,

                "completed" =>
                    $completed,

                "not_checked_in" =>
                    $notCheckedIn,

                "total_working_hours" =>
                    number_format(
                        $totalHoursToday,
                        2,
                        '.',
                        ''
                    )
            ],

            // Main field expected by
            // HrWorkingHoursScreen

            "working_hours" =>
                $workingHours,

            // Also return attendance
            // for compatibility

            "attendance" =>
                $workingHours
        ]
    );

} catch (Throwable $e) {

    // ========================================================
    // ANY DB / SCHEMA ERROR LANDS HERE INSTEAD OF CRASHING
    // ========================================================

    $conn->close();

    sendResponse(
        500,
        false,
        "Unable to load all-employee working hours. " .
        $e->getMessage()
    );
}
}

// ============================================================
// SCOPE = OWN
//
// Employee / Manager / HR own monthly working hours
//
// Existing functionality preserved.
// ============================================================

// ============================================================
// WRAP ALL DB WORK IN TRY/CATCH
//
// If MySQLi is in MYSQLI_REPORT_STRICT mode (set in
// config.php), prepare()/execute() throw a
// mysqli_sql_exception instead of returning false whenever a
// referenced table/column doesn't exist or a query fails.
// Without this try/catch, that would produce a blank
// HTTP 500 with no JSON body instead of a clean JSON error —
// which is what the Flutter WorkingHoursScreen needs to show
// a useful message instead of "Unable to connect to server."
// ============================================================

try {

// ============================================================
// DEFAULT SUMMARY
// ============================================================

$summary = [

    "total_working_hours" =>
        "0.00",

    "total_working_minutes" =>
        0,

    "total_working_days" =>
        0
];

// ============================================================
// GET MONTHLY WORKING HOURS SUMMARY
//
// user_id bound as string ("s"); year/month stay integers.
// ============================================================

$summaryStmt =
    $conn->prepare("
        SELECT
            total_working_hours,
            total_working_minutes,
            total_working_days

        FROM monthly_working_hours

        WHERE user_id = ?

          AND year = ?

          AND month = ?

        LIMIT 1
    ");

if ($summaryStmt) {

    $summaryStmt->bind_param(
        "sii",
        $userId,
        $year,
        $month
    );

    $summaryStmt->execute();

    $summaryResult =
        $summaryStmt->get_result();

    if (
        $summaryResult->num_rows > 0
    ) {

        $summaryRow =
            $summaryResult
                ->fetch_assoc();

        $summary = [

            "total_working_hours" =>
                number_format(
                    (float)(
                        $summaryRow[
                            'total_working_hours'
                        ] ?? 0
                    ),
                    2,
                    '.',
                    ''
                ),

            "total_working_minutes" =>
                (int)(
                    $summaryRow[
                        'total_working_minutes'
                    ] ?? 0
                ),

            "total_working_days" =>
                (int)(
                    $summaryRow[
                        'total_working_days'
                    ] ?? 0
                )
        ];
    }

    $summaryStmt->close();
}

// ============================================================
// GET DAILY ATTENDANCE
//
// user_id bound as string ("s"); year/month stay integers.
// ============================================================

$attendance = [];

$attendanceStmt =
    $conn->prepare("
        SELECT

            report_date AS date,

            login_time,

            logout_time,

            daily_working_hours

        FROM daily_reports

        WHERE user_id = ?

          AND YEAR(
              report_date
          ) = ?

          AND MONTH(
              report_date
          ) = ?

        ORDER BY
            report_date DESC
    ");

if ($attendanceStmt) {

    $attendanceStmt->bind_param(
        "sii",
        $userId,
        $year,
        $month
    );

    $attendanceStmt->execute();

    $attendanceResult =
        $attendanceStmt
            ->get_result();

    while (
        $row =
            $attendanceResult
                ->fetch_assoc()
    ) {

        $dailyHours =
            (float)(
                $row[
                    'daily_working_hours'
                ] ?? 0
            );

        // ====================================================
        // STATUS
        // ====================================================

        if (
            empty(
                $row[
                    'login_time'
                ]
            )
        ) {

            $status =
                "not_checked_in";

        } elseif (
            empty(
                $row[
                    'logout_time'
                ]
            )
        ) {

            $status =
                "working";

        } else {

            $status =
                "completed";
        }

        $attendance[] = [

            "date" =>
                $row[
                    'date'
                ],

            "attendance_date" =>
                $row[
                    'date'
                ],

            "login_time" =>
                $row[
                    'login_time'
                ]
                ?? null,

            "check_in_time" =>
                $row[
                    'login_time'
                ]
                ?? null,

            "logout_time" =>
                $row[
                    'logout_time'
                ]
                ?? null,

            "check_out_time" =>
                $row[
                    'logout_time'
                ]
                ?? null,

            "daily_working_hours" =>
                number_format(
                    $dailyHours,
                    2,
                    '.',
                    ''
                ),

            "working_hours" =>
                number_format(
                    $dailyHours,
                    2,
                    '.',
                    ''
                ),

            "status" =>
                $status
        ];
    }

    $attendanceStmt->close();
}

// ============================================================
// FALLBACK SUMMARY
//
// If monthly_working_hours has no data,
// calculate from daily_reports.
// ============================================================

if (
    (int)$summary[
        'total_working_days'
    ] === 0
    &&
    count(
        $attendance
    ) > 0
) {

    $calculatedHours =
        0.0;

    $calculatedDays =
        0;

    foreach (
        $attendance
        as $record
    ) {

        $hours =
            (float)(
                $record[
                    'daily_working_hours'
                ] ?? 0
            );

        if ($hours > 0) {

            $calculatedHours +=
                $hours;

            $calculatedDays++;
        }
    }

    $calculatedMinutes =
        (int)round(
            $calculatedHours *
            60
        );

    $summary = [

        "total_working_hours" =>
            number_format(
                $calculatedHours,
                2,
                '.',
                ''
            ),

        "total_working_minutes" =>
            $calculatedMinutes,

        "total_working_days" =>
            $calculatedDays
    ];
}

// ============================================================
// CLOSE DATABASE
// ============================================================

$conn->close();

// ============================================================
// RESPONSE
// ============================================================

sendResponse(
    200,
    true,
    "Working hours loaded successfully.",
    [

        "user" => [

            "user_id" =>
                $user[
                    'id'
                ],

            "username" =>
                $user[
                    'username'
                ],

            "role" =>
                $role
        ],

        "period" => [

            "year" =>
                $year,

            "month" =>
                $month,

            "month_name" =>
                date(
                    'F',
                    mktime(
                        0,
                        0,
                        0,
                        $month,
                        1,
                        $year
                    )
                )
        ],

        "summary" =>
            $summary,

        "attendance" =>
            $attendance
    ]
);

} catch (Throwable $e) {

    // ========================================================
    // ANY DB / SCHEMA ERROR LANDS HERE INSTEAD OF CRASHING
    // ========================================================

    $conn->close();

    sendResponse(
        500,
        false,
        "Unable to load working hours. " .
        $e->getMessage()
    );
}

?>