<?php

// ============================================================
// KPI ACTUALS API
// File: api/kpi_actuals.php
//
// Method: POST
//
// ACTIONS:
//
// 1. submit (default)
//    Employee / Manager submits KPI actual values
//
// 2. hr_verification_list
//    HR / Superadmin / MD views manager-approved + verified KPIs
//
// 3. hr_verify
//    HR / Superadmin / MD verifies manager-approved KPI
//
// 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.
//
// kpi_id and kpi_actual_id remain genuine integer primary /
// foreign keys and are unaffected by this migration.
// ============================================================

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');

// ============================================================
// CORS OPTIONS
// ============================================================

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

// ============================================================
// DATABASE
// ============================================================

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
// ============================================================

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

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

// ============================================================
// READ INPUT
// ============================================================

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

$data = json_decode(
    $rawData,
    true
);

// Support form-data also

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

// ============================================================
// USER ID
//
// 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'] ?? ''
);

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."
    );
}

// ============================================================
// ACTION
// ============================================================

$action = strtolower(
    trim(
        $data['action'] ?? 'submit'
    )
);

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

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

if (!$stmt) {

    $conn->close();

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

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

$stmt->execute();

$result = $stmt->get_result();

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

    $stmt->close();

    $conn->close();

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

$user = $result->fetch_assoc();

$stmt->close();

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

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

// ============================================================
// HR ALLOWED ROLES
// ============================================================

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

// ============================================================
// ACTION 0
// EMPLOYEE / MANAGER OWN KPI LIST
//
// Returns the KPIs assigned to the logged-in user for the
// current week, merged with their kpi_actuals submission (if
// any exists yet for this week). This is the action the
// Flutter KpiScreen calls when it first opens.
//
// ASSUMPTION: a kpi_master table exists with at least:
//   id, kpi_name, description, target_value, uom, user_id
// (user_id = the employee/manager this KPI is assigned to,
// VARCHAR(30) bound as string). Adjust column names below to
// match your actual kpi_master schema if they differ.
// ============================================================

if ($action === 'list') {

    // ========================================================
    // ROLE CHECK
    // ========================================================

    $allowedListRoles = [
        'employee',
        'manager'
    ];

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

        $conn->close();

        sendResponse(
            403,
            false,
            "Only employees and managers can view their KPI list."
        );
    }

    // ========================================================
    // CURRENT WEEK RANGE
    // (Monday = 1 ... Sunday = 7)
    // ========================================================

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

    $monthKey =
        date('Y-m');

    $currentDayNumber =
        (int)date('N');

    $weekStart =
        date(
            'Y-m-d',
            strtotime(
                '-' .
                ($currentDayNumber - 1) .
                ' days'
            )
        );

    $weekEnd =
        date(
            'Y-m-d',
            strtotime(
                '+' .
                (7 - $currentDayNumber) .
                ' days'
            )
        );

    // ========================================================
    // FETCH ASSIGNED KPIs + THIS WEEK'S ACTUAL (IF ANY)
    //
    // user_id bound as string ("s") throughout.
    //
    // IMPORTANT: this whole block is wrapped in try/catch.
    // If your MySQLi driver mode is MYSQLI_REPORT_STRICT (set
    // in config.php), prepare()/execute() throw a
    // mysqli_sql_exception instead of returning false whenever
    // a referenced table/column (e.g. kpi_master) doesn't
    // exist or doesn't match — an uncaught exception here would
    // otherwise produce a blank HTTP 500 with no JSON body.
    // ========================================================

    try {

        $kpis = [];

        $stmt = $conn->prepare("
            SELECT

                km.id AS kpi_id,

                km.kpi_name,

                km.description,

                km.target_value,

                km.uom,

                ka.id AS actual_id,

                ka.actual_value,

                ka.status,

                ka.employee_comment,

                ka.manager_comment,

                ka.hr_comment,

                ka.week_start,

                ka.week_end,

                ka.month_key,

                ka.manager_approved

            FROM kpi_master km

            LEFT JOIN kpi_actuals ka

                ON ka.kpi_id = km.id

                AND ka.user_id = km.user_id

                AND ka.week_start = ?

                AND ka.week_end = ?

            WHERE km.user_id = ?

            ORDER BY km.id ASC
        ");

        if (!$stmt) {

            throw new Exception(
                "Unable to prepare KPI list query."
            );
        }

        $stmt->bind_param(
            "sss",
            $weekStart,
            $weekEnd,
            $userId
        );

        if (!$stmt->execute()) {

            $error = $stmt->error;

            $stmt->close();

            throw new Exception(
                "Unable to execute KPI list query. " .
                $error
            );
        }

        $result =
            $stmt->get_result();

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

            $kpis[] = [

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

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

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

                "name" =>
                    $row['kpi_name'],

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

                "target_value" =>
                    $row['target_value'] !== null
                        ? (float)$row['target_value']
                        : null,

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

                "actual_id" =>
                    $row['actual_id'] !== null
                        ? (string)$row['actual_id']
                        : null,

                "actual_value" =>
                    $row['actual_value'] !== null
                        ? (float)$row['actual_value']
                        : 0,

                "status" =>
                    $row['status'] ?? 'pending',

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

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

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

                "manager_approved" =>
                    (bool)($row['manager_approved'] ?? false),

                "week_start" =>
                    $row['week_start'] ?? $weekStart,

                "week_end" =>
                    $row['week_end'] ?? $weekEnd,

                "month_key" =>
                    $row['month_key'] ?? $monthKey
            ];
        }

        $stmt->close();

        $conn->close();

        sendResponse(
            200,
            true,
            "KPI list loaded successfully.",
            [

                "kpis" =>
                    $kpis,

                "period" => [

                    "week_start" =>
                        $weekStart,

                    "week_end" =>
                        $weekEnd,

                    "month_key" =>
                        $monthKey
                ]
            ]
        );

    } catch (Throwable $e) {

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

        $conn->close();

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

// ============================================================
// ACTION 1
// HR VERIFICATION LIST
// ============================================================

if ($action === 'hr_verification_list') {

    // ========================================================
    // ROLE CHECK
    // ========================================================

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

        $conn->close();

        sendResponse(
            403,
            false,
            "You are not allowed to view KPI verification records."
        );
    }

    // ========================================================
    // GET KPI ACTUALS
    //
    // ka.user_id is a string (VARCHAR(30)) foreign key to
    // users.id; ka.id and ka.kpi_id remain integer keys.
    // ========================================================

    $kpiActuals = [];

    $stmt = $conn->prepare("
        SELECT
            ka.id,
            ka.user_id,
            ka.kpi_id,
            ka.month_key,
            ka.week_start,
            ka.week_end,
            ka.actual_value,
            ka.status,
            ka.employee_comment,
            ka.attachment,
            ka.attachment_original_name,
            ka.manager_comment,
            ka.hr_comment,
            ka.submitted_at,
            ka.approved_at,
            ka.verified_at,
            ka.manager_approved,

            u.username AS employee_name

        FROM kpi_actuals ka

        LEFT JOIN users u
            ON u.id = ka.user_id

        WHERE
            ka.status IN (
                'manager_approved',
                'hr_verified'
            )

        ORDER BY

            CASE

                WHEN
                    ka.status = 'manager_approved'
                THEN 1

                WHEN
                    ka.status = 'hr_verified'
                THEN 2

                ELSE 3

            END ASC,

            ka.approved_at DESC,

            ka.id DESC
    ");

    if (!$stmt) {

        $conn->close();

        sendResponse(
            500,
            false,
            "Unable to prepare KPI verification list."
        );
    }

    $stmt->execute();

    $result =
        $stmt->get_result();

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

        $kpiActuals[] = [

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

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

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

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

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

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

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

            "actual_value" =>
                (float)$row[
                    'actual_value'
                ],

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

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

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

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

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

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

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

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

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

            "manager_approved" =>
                (bool)$row[
                    'manager_approved'
                ]
        ];
    }

    $stmt->close();

    $conn->close();

    sendResponse(
        200,
        true,
        "KPI verification records loaded successfully.",
        [
            "kpi_actuals" =>
                $kpiActuals,

            "total_records" =>
                count(
                    $kpiActuals
                )
        ]
    );
}

// ============================================================
// ACTION 2
// HR VERIFY KPI
//
// kpi_actual_id remains a genuine integer primary key and is
// unaffected by the user_id string migration.
// ============================================================

if ($action === 'hr_verify') {

    // ========================================================
    // ROLE CHECK
    // ========================================================

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

        $conn->close();

        sendResponse(
            403,
            false,
            "You are not allowed to verify KPI records."
        );
    }

    // ========================================================
    // KPI ACTUAL ID
    // ========================================================

    $kpiActualId = (int)(
        $data[
            'kpi_actual_id'
        ] ?? 0
    );

    // ========================================================
    // HR COMMENT
    // ========================================================

    $hrComment = trim(
        $data[
            'hr_comment'
        ] ?? ''
    );

    if ($kpiActualId <= 0) {

        $conn->close();

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

    // ========================================================
    // CHECK KPI ACTUAL EXISTS
    // ========================================================

    $stmt = $conn->prepare("
        SELECT
            id,
            user_id,
            kpi_id,
            status,
            manager_approved,
            manager_comment,
            hr_comment,
            approved_at,
            verified_at
        FROM kpi_actuals
        WHERE id = ?
        LIMIT 1
    ");

    if (!$stmt) {

        $conn->close();

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

    $stmt->bind_param(
        "i",
        $kpiActualId
    );

    $stmt->execute();

    $result =
        $stmt->get_result();

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

        $stmt->close();

        $conn->close();

        sendResponse(
            404,
            false,
            "KPI actual record not found."
        );
    }

    $kpiActual =
        $result->fetch_assoc();

    $stmt->close();

    // ========================================================
    // ALREADY HR VERIFIED
    // ========================================================

    if (
        $kpiActual['status']
        === 'hr_verified'
    ) {

        $conn->close();

        sendResponse(
            409,
            false,
            "This KPI has already been verified by HR."
        );
    }

    // ========================================================
    // MANAGER APPROVAL REQUIRED
    // ========================================================

    if (
        $kpiActual['status']
            !== 'manager_approved'
        ||
        (int)$kpiActual[
            'manager_approved'
        ] !== 1
    ) {

        $conn->close();

        sendResponse(
            403,
            false,
            "Manager approval is required before HR verification."
        );
    }

    // ========================================================
    // VERIFY KPI
    // ========================================================

    $stmt = $conn->prepare("
        UPDATE kpi_actuals

        SET
            status = 'hr_verified',
            hr_comment = ?,
            verified_at = NOW()

        WHERE id = ?
          AND status = 'manager_approved'
          AND manager_approved = 1
    ");

    if (!$stmt) {

        $conn->close();

        sendResponse(
            500,
            false,
            "Unable to prepare KPI verification."
        );
    }

    $stmt->bind_param(
        "si",
        $hrComment,
        $kpiActualId
    );

    if (
        !$stmt->execute()
    ) {

        $stmt->close();

        $conn->close();

        sendResponse(
            500,
            false,
            "Unable to verify KPI."
        );
    }

    // ========================================================
    // CHECK UPDATE
    // ========================================================

    if (
        $stmt->affected_rows === 0
    ) {

        $stmt->close();

        $conn->close();

        sendResponse(
            409,
            false,
            "KPI verification failed or record status has changed."
        );
    }

    $stmt->close();

    $conn->close();

    // ========================================================
    // SUCCESS
    // ========================================================

    sendResponse(
        200,
        true,
        "KPI verified successfully.",
        [

            "kpi_actual_id" =>
                (string)$kpiActualId,

            "status" =>
                "hr_verified",

            "hr_comment" =>
                $hrComment,

            "verified_by" => [

                "user_id" =>
                    $userId,

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

                "role" =>
                    $role
            ]
        ]
    );
}

// ============================================================
// UNKNOWN ACTION
// ============================================================

if (
    $action !== 'submit'
) {

    $conn->close();

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

// ============================================================
// ACTION 3
// EMPLOYEE / MANAGER KPI SUBMISSION
// ============================================================

// ============================================================
// ROLE CHECK
// ============================================================

$allowedSubmitRoles = [
    'employee',
    'manager'
];

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

    $conn->close();

    sendResponse(
        403,
        false,
        "Only employees and managers can submit KPI actual values."
    );
}

// ============================================================
// BUILD KPI LIST
//
// kpi_id remains a genuine integer key referencing the KPI
// master table; unaffected by the user_id string migration.
// ============================================================

$kpiItems = [];

// ============================================================
// SINGLE KPI
// ============================================================

if (
    isset(
        $data['kpi_id']
    )
    &&
    (int)$data[
        'kpi_id'
    ] > 0
) {

    $kpiItems[] = [

        "kpi_id" =>
            (int)$data[
                'kpi_id'
            ],

        "actual_value" =>
            (float)(
                $data[
                    'actual_value'
                ] ?? 0
            ),

        "employee_comment" =>
            trim(
                $data[
                    'employee_comment'
                ] ?? ''
            )
    ];
}

// ============================================================
// MULTIPLE KPI
// ============================================================

if (
    isset(
        $data['kpis']
    )
    &&
    is_array(
        $data['kpis']
    )
) {

    $kpiItems = [];

    foreach (
        $data['kpis']
        as $item
    ) {

        if (
            !is_array(
                $item
            )
        ) {
            continue;
        }

        $kpiId = (int)(
            $item[
                'kpi_id'
            ] ?? 0
        );

        if ($kpiId <= 0) {
            continue;
        }

        $kpiItems[] = [

            "kpi_id" =>
                $kpiId,

            "actual_value" =>
                (float)(
                    $item[
                        'actual_value'
                    ] ?? 0
                ),

            "employee_comment" =>
                trim(
                    $item[
                        'employee_comment'
                    ] ?? ''
                )
        ];
    }
}

// ============================================================
// VALIDATE KPI DATA
// ============================================================

if (
    count(
        $kpiItems
    ) === 0
) {

    $conn->close();

    sendResponse(
        400,
        false,
        "At least one KPI actual value is required."
    );
}

// ============================================================
// DATE / WEEK CALCULATION
// ============================================================

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

$monthKey =
    date(
        'Y-m'
    );

// Monday = 1
// Sunday = 7

$currentDayNumber =
    (int)date(
        'N'
    );

$weekStart =
    date(
        'Y-m-d',
        strtotime(
            '-' .
            (
                $currentDayNumber -
                1
            ) .
            ' days'
        )
    );

$weekEnd =
    date(
        'Y-m-d',
        strtotime(
            '+' .
            (
                7 -
                $currentDayNumber
            ) .
            ' days'
        )
    );

// ============================================================
// CHECK ATTENDANCE
//
// user_id bound as string ("s").
// ============================================================

$stmt = $conn->prepare("
    SELECT
        id,
        login_time,
        logout_time
    FROM daily_reports
    WHERE user_id = ?
      AND report_date = ?
    ORDER BY id DESC
    LIMIT 1
");

if (!$stmt) {

    $conn->close();

    sendResponse(
        500,
        false,
        "Unable to verify attendance."
    );
}

$stmt->bind_param(
    "ss",
    $userId,
    $today
);

$stmt->execute();

$result =
    $stmt->get_result();

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

    $stmt->close();

    $conn->close();

    sendResponse(
        403,
        false,
        "Please check in before submitting KPI actual values."
    );
}

$attendance =
    $result->fetch_assoc();

$stmt->close();

// ============================================================
// CHECK LOGIN TIME
// ============================================================

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

    $conn->close();

    sendResponse(
        403,
        false,
        "Please check in before submitting KPI actual values."
    );
}

// ============================================================
// DO NOT ALLOW KPI AFTER CHECKOUT
// ============================================================

if (
    !empty(
        $attendance[
            'logout_time'
        ]
    )
) {

    $conn->close();

    sendResponse(
        403,
        false,
        "KPI actual values cannot be updated after checkout."
    );
}

// ============================================================
// START TRANSACTION
// ============================================================

$conn->begin_transaction();

try {

    $savedKpis = [];

    foreach (
        $kpiItems
        as $item
    ) {

        $kpiId =
            (int)$item[
                'kpi_id'
            ];

        $actualValue =
            (float)$item[
                'actual_value'
            ];

        $employeeComment =
            $item[
                'employee_comment'
            ];

        // ====================================================
        // CHECK EXISTING KPI ACTUAL
        // SAME USER + KPI + WEEK
        //
        // user_id bound as string ("s"), kpi_id stays int.
        // ====================================================

        $stmt = $conn->prepare("
            SELECT
                id,
                status,
                manager_approved
            FROM kpi_actuals
            WHERE user_id = ?
              AND kpi_id = ?
              AND week_start = ?
              AND week_end = ?
            ORDER BY id DESC
            LIMIT 1
        ");

        if (!$stmt) {

            throw new Exception(
                "Unable to check KPI actual."
            );
        }

        $stmt->bind_param(
            "siss",
            $userId,
            $kpiId,
            $weekStart,
            $weekEnd
        );

        $stmt->execute();

        $result =
            $stmt->get_result();

        // ====================================================
        // EXISTING KPI
        // ====================================================

        if (
            $result->num_rows > 0
        ) {

            $existing =
                $result->fetch_assoc();

            $stmt->close();

            $actualId =
                (int)$existing[
                    'id'
                ];

            // =================================================
            // LOCK AFTER APPROVAL
            // =================================================

            if (
                (int)$existing[
                    'manager_approved'
                ] === 1
                ||
                $existing[
                    'status'
                ] ===
                    'manager_approved'
                ||
                $existing[
                    'status'
                ] ===
                    'hr_verified'
            ) {

                throw new Exception(
                    "KPI ID {$kpiId} has already been approved and cannot be edited."
                );
            }

            // =================================================
            // UPDATE EXISTING KPI
            //
            // user_id bound as string ("s") in the WHERE clause.
            // =================================================

            $stmt = $conn->prepare("
                UPDATE kpi_actuals

                SET
                    month_key = ?,
                    actual_value = ?,
                    employee_comment = ?,
                    status = 'submitted',
                    manager_approved = 0,
                    manager_comment = NULL,
                    hr_comment = NULL,
                    approved_at = NULL,
                    verified_at = NULL

                WHERE id = ?
                  AND user_id = ?
            ");

            if (!$stmt) {

                throw new Exception(
                    "Unable to prepare KPI update."
                );
            }

            $stmt->bind_param(
                "sdsis",
                $monthKey,
                $actualValue,
                $employeeComment,
                $actualId,
                $userId
            );

            if (
                !$stmt->execute()
            ) {

                $stmt->close();

                throw new Exception(
                    "Unable to update KPI actual."
                );
            }

            $stmt->close();

            $savedKpis[] = [

                "id" =>
                    (string)$actualId,

                "kpi_id" =>
                    (string)$kpiId,

                "month_key" =>
                    $monthKey,

                "week_start" =>
                    $weekStart,

                "week_end" =>
                    $weekEnd,

                "actual_value" =>
                    $actualValue,

                "employee_comment" =>
                    $employeeComment,

                "status" =>
                    "submitted",

                "action" =>
                    "updated"
            ];

        } else {

            $stmt->close();

            // =================================================
            // INSERT NEW KPI ACTUAL
            //
            // user_id bound as string ("s"), kpi_id stays int.
            // =================================================

            $stmt = $conn->prepare("
                INSERT INTO kpi_actuals
                (
                    user_id,
                    kpi_id,
                    month_key,
                    week_start,
                    week_end,
                    actual_value,
                    status,
                    employee_comment,
                    manager_approved
                )

                VALUES
                (
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    'submitted',
                    ?,
                    0
                )
            ");

            if (!$stmt) {

                throw new Exception(
                    "Unable to prepare KPI actual submission."
                );
            }

            $stmt->bind_param(
                "sisssds",
                $userId,
                $kpiId,
                $monthKey,
                $weekStart,
                $weekEnd,
                $actualValue,
                $employeeComment
            );

            if (
                !$stmt->execute()
            ) {

                $stmt->close();

                throw new Exception(
                    "Unable to save KPI actual."
                );
            }

            $actualId =
                $conn->insert_id;

            $stmt->close();

            $savedKpis[] = [

                "id" =>
                    (string)$actualId,

                "kpi_id" =>
                    (string)$kpiId,

                "month_key" =>
                    $monthKey,

                "week_start" =>
                    $weekStart,

                "week_end" =>
                    $weekEnd,

                "actual_value" =>
                    $actualValue,

                "employee_comment" =>
                    $employeeComment,

                "status" =>
                    "submitted",

                "action" =>
                    "inserted"
            ];
        }
    }

    // ========================================================
    // COMMIT
    // ========================================================

    $conn->commit();

    // ========================================================
    // GET CURRENT WEEK ACTUALS
    //
    // user_id bound as string ("s").
    // ========================================================

    $currentWeekActuals = [];

    $stmt = $conn->prepare("
        SELECT
            id,
            kpi_id,
            month_key,
            week_start,
            week_end,
            actual_value,
            status,
            employee_comment,
            manager_comment,
            hr_comment,
            submitted_at,
            approved_at,
            verified_at,
            manager_approved
        FROM kpi_actuals
        WHERE user_id = ?
          AND week_start = ?
          AND week_end = ?
        ORDER BY kpi_id ASC
    ");

    if ($stmt) {

        $stmt->bind_param(
            "sss",
            $userId,
            $weekStart,
            $weekEnd
        );

        $stmt->execute();

        $result =
            $stmt->get_result();

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

            $currentWeekActuals[] = [

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

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

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

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

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

                "actual_value" =>
                    (float)$row[
                        'actual_value'
                    ],

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

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

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

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

                "manager_approved" =>
                    (bool)$row[
                        'manager_approved'
                    ],

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

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

                "verified_at" =>
                    $row[
                        'verified_at'
                    ]
            ];
        }

        $stmt->close();
    }

    // ========================================================
    // CLOSE CONNECTION
    // ========================================================

    $conn->close();

    // ========================================================
    // SUCCESS RESPONSE
    // ========================================================

    sendResponse(
        200,
        true,
        "KPI actual values submitted successfully.",
        [

            "user" => [

                "user_id" =>
                    $userId,

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

                "role" =>
                    $role
            ],

            "period" => [

                "month_key" =>
                    $monthKey,

                "week_start" =>
                    $weekStart,

                "week_end" =>
                    $weekEnd
            ],

            "saved_kpis" =>
                $savedKpis,

            "current_week_actuals" =>
                $currentWeekActuals
        ]
    );

} catch (Throwable $e) {

    // ========================================================
    // ROLLBACK
    // ========================================================

    $conn->rollback();

    $conn->close();

    sendResponse(
        500,
        false,
        $e->getMessage()
    );
}

?>