<?php
// models/Report.php

/**
 * Report Model for Rehlko Customer Care application.
 * Manages CRUD operations and data interaction for the 'reports' table.
 */
require_once 'RootCause.php';
class Report {
    private $conn;
    private $table_name = "reports";
    private $rootcause;

    // Object properties
    public $id;
    public $report_index;
    public $report_type;
    public $generator_serial_number;
    public $customer_id;
    public $problem_issue;
    public $remarks;
    public $status;
    public $is_pick_up;
    public $engineer_id;
    public $supervisor_id;
    public $created_datetime;
    public $modified_datetime;

    /**
     * Constructor for the Report model.
     *
     * @param mysqli $db The database connection object.
     */
    public function __construct($db) {
        $this->conn = $db;
        $this->rootcause = new RootCause($db); 
    }

    /**
     * Creates a new report record.
     *
     * @return bool True on success, false on failure.
     */
    public function create() {
        $query = "INSERT INTO " . $this->table_name . "
                  SET report_index=?, report_type=?, generator_serial_number=?, customer_id=?,company_id=?,
                      problem_issue=?, remarks=?,
                      status='pending', is_pick_up=0"; // Default status and pick_up status

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        // Generate unique report index
        $this->report_index = $this->generateUniqueReportIndex($this->customer_id);

        // Sanitize inputs
        $this->report_type = htmlspecialchars(strip_tags($this->report_type));
        $this->generator_serial_number = htmlspecialchars(strip_tags($this->generator_serial_number));
        $this->customer_id = htmlspecialchars(strip_tags($this->customer_id));
        $this->company_id = htmlspecialchars(strip_tags($this->company_id));
        $this->problem_issue = htmlspecialchars(strip_tags($this->problem_issue));
        $this->remarks = htmlspecialchars(strip_tags($this->remarks));

        $stmt->bind_param("sssiiss",
            $this->report_index,
            $this->report_type,
            $this->generator_serial_number,
            $this->customer_id,
            $this->company_id,
            $this->problem_issue,
            $this->remarks
        );

        if ($stmt->execute()) {
            $this->id = $this->conn->insert_id;
            return true;
        }
        error_log("Report creation failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }

    /**
     * Generates a unique report index.
     *
     * @param int $customer_id The ID of the customer.
     * @return string The unique report index.
     */
    public function generateUniqueReportIndex($customer_id): string
{
    // Fetch customer username to include in the index
    $query = "SELECT username FROM users WHERE id = ?";
    $stmt = $this->conn->prepare($query);
    $stmt->bind_param("i", $customer_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $user = $result->fetch_assoc();
    $customerUsername = $user ? strtoupper($user['username']) : 'UNKNOWN';

    // Split the username into words
    $usernameWords = explode(' ', $customerUsername);
    
    // Get the first two words, or fewer if the username is short
    $firstTwoWords = array_slice($usernameWords, 0, 2);
    
    // Extract the first two letters of each word
    $initials = '';
    foreach ($firstTwoWords as $word) {
        $initials .= strtoupper(substr($word, 0, 2));
    }
    
    // Clean the initials to only contain alphanumeric characters
    $cleanInitials = preg_replace('/[^A-Z0-9]/', '', $initials);
    
    // If no valid initials were found, use 'UN' for 'UNKNOWN'
    if (empty($cleanInitials)) {
        $cleanInitials = 'UN';
    }

    $timestamp = date('dmYHi');
   

    return "{$timestamp}{$cleanInitials}";
}

    /**
     * Reads all report records with role-based filtering, and optional status/search filters.
     *
     * @param string|null $filterStatus Optional: Filter reports by a specific status.
     * @param string|null $searchTerm Optional: Search term for report_index, problem_issue, or generator_serial_number.
     * @return mysqli_result|false The result set on success, false on failure.
     */
    public function readAll($customerId) {
        $currentUserRole = getCurrentUserRole();
        $currentUserId = getCurrentUserId();

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, ue.fullname AS engineer_name,
                          us.fullname AS supervisor_name, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number";

        $whereClauses = [];
        $bindParams = [];
        $bindParamTypes = '';

        // Apply role-based filtering first
        if (in_array($currentUserRole, ['superadmin', 'admin', 'supervisor'])) {
            if($customerId != null){
                $whereClauses[] = "r.customer_id = ?";
                $bindParams[] = $customerId;
                $bindParamTypes .= 'i';
            }
        }  elseif (in_array($currentUserRole, ['localcustomer', 'globalcustomer'])) {
            // Customers create reports and read their own.
            $whereClauses[] = "r.company_id = ?";
            $bindParams[] = $customerId;
            $bindParamTypes .= 'i';
        } else {
            // If current user role is not authorized, return false.
            // Controller should handle sending JSON response.
            return false;
        }

        // Apply status filter if provided
        

        if (!empty($whereClauses)) {
            $query .= " WHERE " . implode(" AND ", $whereClauses);
        }

        $query .= " ORDER BY r.created_datetime DESC";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed for readAll: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        if (!empty($bindParams)) {
            $stmt->bind_param($bindParamTypes, ...$bindParams);
        }

        if ($stmt->execute()) {
            return $stmt->get_result();
        }
        error_log("Read all reports failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
    
    
    
    
    public function readAllAssign($status) {
    $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                    r.problem_issue, r.remarks,
                    r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                    r.created_datetime, r.modified_datetime,
                    uc.fullname AS customer_name, ue.fullname AS engineer_name,
                    us.fullname AS supervisor_name, p.generator_model_number,cs.level, cs.gantry
               FROM " . $this->table_name . " r
               LEFT JOIN users uc ON r.customer_id = uc.id
               LEFT JOIN users ue ON r.engineer_id = ue.id
               LEFT JOIN users us ON r.supervisor_id = us.id
               LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
               LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number";

    // Initialize as an array
    $whereClauses = [];
    $bindParams = [];
    $bindParamTypes = '';

    // Add the initial condition to the array
    $whereClauses[] = "r.is_pick_up = 0";

    // Apply status filter based on the provided logic
    if ($status === 'uncompleted') {
        $whereClauses[] = "r.status != 'completed'";
    } elseif ($status === 'completed') {
        $whereClauses[] = "r.status = 'completed'";
    }

    // Apply search term filter if provided (assuming $searchTerm is a global or passed parameter)
    if (isset($searchTerm) && $searchTerm) {
        $searchTerm = '%' . $searchTerm . '%';
        $whereClauses[] = "(r.report_index LIKE ? OR r.problem_issue LIKE ? OR r.generator_serial_number LIKE ?)";
        $bindParams[] = $searchTerm;
        $bindParams[] = $searchTerm;
        $bindParams[] = $searchTerm;
        $bindParamTypes .= 'sss';
    }

    if (!empty($whereClauses)) {
        $query .= " WHERE " . implode(" AND ", $whereClauses);
    }

    $query .= " ORDER BY r.created_datetime DESC";

    $stmt = $this->conn->prepare($query);
    if ($stmt === false) {
        error_log("Prepare failed for readAll: (" . $this->conn->errno . ") " . $this->conn->error);
        return false;
    }

    if (!empty($bindParams)) {
        $stmt->bind_param($bindParamTypes, ...$bindParams);
    }

    if ($stmt->execute()) {
        return $stmt->get_result();
    }
    error_log("Read all reports failed: (" . $stmt->errno . ") " . $stmt->error);
    return false;
}
public function readAllSolve($current_eng_id,$status) {
    $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                    r.problem_issue, r.remarks,
                    r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                    r.created_datetime, r.modified_datetime,
                    uc.fullname AS customer_name, ue.fullname AS engineer_name,
                    us.fullname AS supervisor_name, p.generator_model_number,cs.level, cs.gantry
               FROM " . $this->table_name . " r
               LEFT JOIN users uc ON r.customer_id = uc.id
               LEFT JOIN users ue ON r.engineer_id = ue.id
               LEFT JOIN users us ON r.supervisor_id = us.id
               LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
               LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number";

    // Initialize as an array
    $whereClauses = [];
    $bindParams = [];
    $bindParamTypes = '';

    // Add the initial condition for the engineer's ID
    $whereClauses[] = "r.engineer_id = ?";
    $bindParams[] = $current_eng_id;
    $bindParamTypes .= 'i'; // Assuming engineer ID is an integer

    // Apply status filter if provided
    
    if ($status === 'uncompleted') {
        $whereClauses[] = "r.status != 'completed'";
    } elseif ($status === 'completed') {
        $whereClauses[] = "r.status = 'completed'";
    }

    // Apply search term filter if provided
    

    if (!empty($whereClauses)) {
        $query .= " WHERE " . implode(" AND ", $whereClauses);
    }

    $query .= " ORDER BY r.created_datetime DESC";

    $stmt = $this->conn->prepare($query);
    if ($stmt === false) {
        error_log("Prepare failed for readAll: (" . $this->conn->errno . ") " . $this->conn->error);
        return false;
    }

    if (!empty($bindParams)) {
        $stmt->bind_param($bindParamTypes, ...$bindParams);
    }

    if ($stmt->execute()) {
        return $stmt->get_result();
    }
    error_log("Read all reports failed: (" . $stmt->errno . ") " . $stmt->error);
    return false;
}
    /**
     * Reads all reports that are NOT 'completed' with role-based filtering, and optional search filters.
     *
     * @param string|null $searchTerm Optional: Search term for report_index, problem_issue, or generator_serial_number.
     * @return mysqli_result|false The result set on success, false on failure.
     */
    public function readUncompletedAll($company_id,$searchTerm = null) {
        $currentUserRole = getCurrentUserRole();
        $currentUserId = getCurrentUserId();

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, ue.fullname AS engineer_name,
                          us.fullname AS supervisor_name, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number";

        $whereClauses = ["r.status != 'completed'"]; // Explicitly filter uncompleted
        $bindParams = [];
        $bindParamTypes = '';

        // Apply role-based filtering
        if (in_array($currentUserRole, ['superadmin', 'admin', 'supervisor'])) {
            // Superadmin, Admin, Supervisor can read all uncompleted reports.
        }  elseif (in_array($currentUserRole, ['localcustomer', 'globalcustomer'])) {
            $whereClauses[] = "r.company_id = ?";
            $bindParams[] = $company_id;
            $bindParamTypes .= 'i';
        } else {
            return false;
        }

        // Apply search term filter if provided
        if ($searchTerm) {
            $searchTerm = '%' . $searchTerm . '%';
            $whereClauses[] = "(r.report_index LIKE ? OR r.problem_issue LIKE ? OR r.generator_serial_number LIKE ?)";
            $bindParams[] = $searchTerm;
            $bindParams[] = $searchTerm;
            $bindParams[] = $searchTerm;
            $bindParamTypes .= 'sss';
        }

        if (!empty($whereClauses)) {
            $query .= " WHERE " . implode(" AND ", $whereClauses);
        }

        $query .= " ORDER BY r.created_datetime DESC";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed for readUncompletedAll: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        if (!empty($bindParams)) {
            $stmt->bind_param($bindParamTypes, ...$bindParams);
        }

        if ($stmt->execute()) {
            return $stmt->get_result();
        }
        error_log("Read uncompleted reports failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }

    /**
     * Reads all reports that ARE 'completed' with role-based filtering, and optional search filters.
     *
     * @param string|null $searchTerm Optional: Search term for report_index, problem_issue, or generator_serial_number.
     * @return mysqli_result|false The result set on success, false on failure.
     */
    public function readCompletedAll($company_id,$searchTerm = null) {
        $currentUserRole = getCurrentUserRole();
        $currentUserId = getCurrentUserId();

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, ue.fullname AS engineer_name,
                          us.fullname AS supervisor_name, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number";

        $whereClauses = ["r.status = 'completed'"]; // Explicitly filter completed
        $bindParams = [];
        $bindParamTypes = '';

        // Apply role-based filtering
        if (in_array($currentUserRole, ['superadmin', 'admin', 'supervisor'])) {
            // Superadmin, Admin, Supervisor can read all completed reports.
        }  elseif (in_array($currentUserRole, ['localcustomer', 'globalcustomer'])) {
            $whereClauses[] = "r.company_id = ?";
            $bindParams[] = $company_id;
            $bindParamTypes .= 'i';
        } else {
            return false;
        }

        // Apply search term filter if provided
        if ($searchTerm) {
            $searchTerm = '%' . $searchTerm . '%';
            $whereClauses[] = "(r.report_index LIKE ? OR r.problem_issue LIKE ? OR r.generator_serial_number LIKE ?)";
            $bindParams[] = $searchTerm;
            $bindParams[] = $searchTerm;
            $bindParams[] = $searchTerm;
            $bindParamTypes .= 'sss';
        }

        if (!empty($whereClauses)) {
            $query .= " WHERE " . implode(" AND ", $whereClauses);
        }

        $query .= " ORDER BY r.created_datetime DESC";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed for readCompletedAll: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        if (!empty($bindParams)) {
            $stmt->bind_param($bindParamTypes, ...$bindParams);
        }

        if ($stmt->execute()) {
            return $stmt->get_result();
        }
        error_log("Read completed reports failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }

    public function recents() {
        $currentUserRole = getCurrentUserRole();
        $currentUserId = getCurrentUserId();

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, ue.fullname AS engineer_name,
                          us.fullname AS supervisor_name, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number";

        $whereClauses = [];
        $bindParams = [];
        $bindParamTypes = '';

        if (in_array($currentUserRole, ['superadmin', 'admin', 'supervisor'])) {
            // Superadmin, Admin, Supervisor can read all reports.
        } elseif ($currentUserRole === 'engineer') {
            // Engineers can see reports assigned to them or unassigned reports they can pick up (status 'Pending')
            // They can also see reports for customers in their support team (if not completed)
            $query .= " LEFT JOIN support_team st ON r.customer_id = st.customer_id";
            $whereClauses[] = "(r.engineer_id = ? OR (r.status = 'pending' AND st.engineer_id = ?) OR (st.engineer_id = ? AND r.status != 'completed'))";
            $bindParams[] = $currentUserId;
            $bindParams[] = $currentUserId;
            $bindParams[] = $currentUserId;
            $bindParamTypes .= 'iii';
        } elseif (in_array($currentUserRole, ['member', 'champion'])) {
            // Members and Champions only read reports of customers who are in their support team.
            $query .= " JOIN support_team st ON r.customer_id = st.customer_id";
            $whereClauses[] = "(st.champion_id = ? OR st.member_id = ?)";
            $bindParams[] = $currentUserId;
            $bindParams[] = $currentUserId;
            $bindParamTypes .= 'ii';
        } elseif (in_array($currentUserRole, ['localcustomer', 'globalcustomer'])) {
            // Customers create reports and read their own.
            $whereClauses[] = "r.customer_id = ?";
            $bindParams[] = $currentUserId;
            $bindParamTypes .= 'i';
        } else {
            // If current user role is not authorized, send an error response immediately.
            // This case should ideally be handled by the controller's initial authorization check.
            sendJsonResponse(['message' => 'Unauthorized to read reports.'], 403);
            return false;
        }

        if (!empty($whereClauses)) {
            $query .= " WHERE " . implode(" AND ", $whereClauses);
        }

        $query .= " ORDER BY r.modified_datetime DESC LIMIT 5";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        if (!empty($bindParams)) {
            $stmt->bind_param($bindParamTypes, ...$bindParams);
        }

        if ($stmt->execute()) {
            return $stmt->get_result();
        }
        error_log("Read all reports failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
    /**
     * Reads a single report record by ID or report_index.
     *
     * @param string $identifier The ID or report_index of the report.
     * @param string $type 'id' or 'index' to specify identifier type.
     * @return array|false The report data as an associative array on success, false if not found.
     */
    public function readOne($identifier, $type = 'id') {
        $currentUserRole = getCurrentUserRole();
        $currentUserId = getCurrentUserId();

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, uc.email as customer_email, uc.phone as customer_phone, c.country_name as customer_country_name,ue.fullname AS engineer_name,ue.email AS engineer_email,ue.phone AS engineer_phone,
                          us.fullname AS supervisor_name,us.email AS supervisor_email,us.phone AS supervisor_phone, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN country c ON uc.country_id = c.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number
                   
                   WHERE ";

        if ($type === 'id') {
            $query .= "r.id = ?";
        } else { // type === 'index'
            $query .= "r.report_index = ?";
        }
        $query .= " LIMIT 0,1";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        // Sanitize and bind
        $identifier = htmlspecialchars(strip_tags($identifier));
        if ($type === 'id') {
            $stmt->bind_param("i", $identifier);
        } else {
            $stmt->bind_param("s", $identifier);
        }

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $report = $result->fetch_assoc();

            if ($report) {
                //$report = $this->rootcause->readAllByReportIndex($report['report_index']);
                // Detailed authorization for reading a single report
                if (in_array($currentUserRole, ['superadmin', 'admin', 'supervisor'])) {
                    return $report;
                } elseif (in_array($currentUserRole, ['localcustomer', 'globalcustomer'])) {
                    if ($report['customer_id'] == $currentUserId || $report['company_id'] == $company_id) {
                        return $report;
                    } else {
                        // Removed sendJsonResponse here, controller should handle the response
                        return false;
                    }
                } else {
                    // Removed sendJsonResponse here, controller should handle the response
                    return false;
                }
            }
        }
        error_log("Read one report failed or report not found: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
    
    public function readOneCompany($company_id,$identifier, $type = 'id') {
        $currentUserRole = getCurrentUserRole();
        $currentUserId = getCurrentUserId();

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,r.company_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, uc.email as customer_email, uc.phone as customer_phone, c.country_name as customer_country_name,ue.fullname AS engineer_name,ue.email AS engineer_email,ue.phone AS engineer_phone,
                          us.fullname AS supervisor_name,us.email AS supervisor_email,us.phone AS supervisor_phone, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN country c ON uc.country_id = c.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number
                   
                   WHERE ";

        if ($type === 'id') {
            $query .= "r.id = ?";
        } else { // type === 'index'
            $query .= "r.report_index = ?";
        }
        $query .= " LIMIT 0,1";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        // Sanitize and bind
        $identifier = htmlspecialchars(strip_tags($identifier));
        if ($type === 'id') {
            $stmt->bind_param("i", $identifier);
        } else {
            $stmt->bind_param("s", $identifier);
        }

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $report = $result->fetch_assoc();

            if ($report) {
                //$report = $this->rootcause->readAllByReportIndex($report['report_index']);
                // Detailed authorization for reading a single report
                if (in_array($currentUserRole, ['superadmin', 'admin', 'supervisor'])) {
                    return $report;
                } elseif (in_array($currentUserRole, ['localcustomer', 'globalcustomer'])) {
                    if ($report['customer_id'] == $currentUserId || $report['company_id'] == $company_id) {
                        return $report;
                    } else {
                        // Removed sendJsonResponse here, controller should handle the response
                        return false;
                    }
                } else {
                    // Removed sendJsonResponse here, controller should handle the response
                    return false;
                }
            }
        }
        error_log("Read one report failed or report not found: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
    
    public function readOneEngineer($identifier, $type = 'id') {
        

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, uc.email as customer_email, uc.phone as customer_phone, c.country_name as customer_country_name,ue.fullname AS engineer_name,ue.email AS engineer_email,ue.phone AS engineer_phone,
                          us.fullname AS supervisor_name,us.email AS supervisor_email,us.phone AS supervisor_phone, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN country c ON uc.country_id = c.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number
                   
                   WHERE ";

        if ($type === 'id') {
            $query .= "r.id = ?";
        } else { // type === 'index'
            $query .= "r.report_index = ?";
        }
        $query .= " LIMIT 0,1";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        // Sanitize and bind
        $identifier = htmlspecialchars(strip_tags($identifier));
        if ($type === 'id') {
            $stmt->bind_param("i", $identifier);
        } else {
            $stmt->bind_param("s", $identifier);
        }

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $report = $result->fetch_assoc();

            if ($report) {
                return $report;
            }
        }
        error_log("Read one report failed or report not found: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
    
    
     public function readOneSolve($identifier, $type = 'id') {
        

        $query = "SELECT r.id, r.report_index, r.report_type, r.generator_serial_number, r.customer_id,
                          r.problem_issue, r.remarks,
                          r.status, r.is_pick_up, r.engineer_id, r.supervisor_id,
                          r.created_datetime, r.modified_datetime,
                          uc.fullname AS customer_name, uc.email as customer_email, uc.phone as customer_phone, c.country_name as customer_country_name,ue.fullname AS engineer_name,ue.email AS engineer_email,ue.phone AS engineer_phone,
                          us.fullname AS supervisor_name,us.email AS supervisor_email,us.phone AS supervisor_phone, p.generator_model_number,cs.level, cs.gantry
                   FROM " . $this->table_name . " r
                   LEFT JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN country c ON uc.country_id = c.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   LEFT JOIN users us ON r.supervisor_id = us.id
                   LEFT JOIN customer_products cs ON r.generator_serial_number = cs.generator_serial_number
                   LEFT JOIN products p ON r.generator_serial_number = p.generator_serial_number
                   
                   WHERE ";

        if ($type === 'id') {
            $query .= "r.id = ?";
        } else { // type === 'index'
            $query .= "r.report_index = ?";
        }
        $query .= " LIMIT 0,1";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        // Sanitize and bind
        $identifier = htmlspecialchars(strip_tags($identifier));
        if ($type === 'id') {
            $stmt->bind_param("i", $identifier);
        } else {
            $stmt->bind_param("s", $identifier);
        }

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $report = $result->fetch_assoc();

            if ($report) {
                return $report;
            }
        }
        error_log("Read one report failed or report not found: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
    
    

    /**
     * Helper to check if engineer is in customer's support team.
     */
    private function isEngineerInSupportTeam($customerId, $engineerId) {
        $query = "SELECT COUNT(*) FROM support_team WHERE customer_id = ? AND engineer_id = ?";
        $stmt = $this->conn->prepare($query);
        if ($stmt === false) { error_log("isEngineerInSupportTeam prepare failed: " . $this->conn->error); return false; }
        $stmt->bind_param("ii", $customerId, $engineerId);
        $count = $stmt->get_result()->fetch_row()[0];
        return $count > 0;
    }

    /**
     * Helper to check if member/champion is in customer's support team.
     */
    private function isMemberOrChampionInSupportTeam($customerId, $userId) {
        $query = "SELECT COUNT(*) FROM support_team WHERE customer_id = ? AND (champion_id = ? OR member_id = ?)";
        $stmt = $this->conn->prepare($query);
        if ($stmt === false) { error_log("isMemberOrChampionInSupportTeam prepare failed: " . $this->conn->error); return false; }
        $stmt->bind_param("iii", $customerId, $userId, $userId);
        $stmt->execute();
        $count = $stmt->get_result()->fetch_row()[0];
        return $count > 0;
    }


    /**
     * Retrieves pending reports (status 'pending') for cron job.
     *
     * @return array An array of pending report records.
     */
    public function getPendingReports() {
        $query = "SELECT r.id, r.report_index, r.customer_id, r.created_datetime, r.problem_issue,
                          uc.role AS customer_role, uc.fullname AS customer_fullname, uc.email AS customer_email
                   FROM " . $this->table_name . " r
                   JOIN users uc ON r.customer_id = uc.id
                   WHERE r.status = 'pending' AND r.is_pick_up = 0
                   ORDER BY r.created_datetime ASC";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed for getPendingReports: (" . $this->conn->errno . ") " . $this->conn->error);
            return [];
        }

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $reports = [];
            while ($row = $result->fetch_assoc()) {
                $reports[] = $row;
            }
            return $reports;
        }
        error_log("Get pending reports failed: (" . $stmt->errno . ") " . $stmt->error);
        return [];
    }


    /**
     * Retrieves reports with status 'checking' for cron job.
     *
     * @return array An array of reports in 'checking' status.
     */
    public function getCheckingReports() {
        $query = "SELECT r.id, r.report_index, r.customer_id, r.engineer_id, r.created_datetime,
                          uc.fullname AS customer_fullname, uc.email AS customer_email,
                          ue.fullname AS engineer_fullname
                   FROM " . $this->table_name . " r
                   JOIN users uc ON r.customer_id = uc.id
                   LEFT JOIN users ue ON r.engineer_id = ue.id
                   WHERE r.status = 'checking'
                   ORDER BY r.modified_datetime ASC";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed for getCheckingReports: (" . $this->conn->errno . ") " . $this->conn->error);
            return [];
        }

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $reports = [];
            while ($row = $result->fetch_assoc()) {
                $reports[] = $row;
            }
            return $reports;
        }
        error_log("Get checking reports failed: (" . $stmt->errno . ") " . $stmt->error);
        return [];
    }


    /**
     * Updates the status of a report.
     *
     * @param int $id The ID of the report to update.
     * @param string $status The new status (e.g., 'solving', 'checking', 'completed').
     * @param int|null $engineerId Optional: The ID of the engineer picking up the report.
     * @param int|null $supervisorId Optional: The ID of the supervisor checking/completing the report.
     * @param bool $isPickedUp Optional: Set to 1 if report is picked up.
     * @return bool True on success, false on failure.
     */
    public function updateStatus($id, $status, $engineerId = null, $supervisorId = null, $isPickedUp = null) {
        $setClauses = ["status = ?", "modified_datetime = NOW()"]; // Always update modified_datetime
        $bindParams = [$status];
        $bindParamTypes = 's';

        if ($isPickedUp !== null) {
            $setClauses[] = "is_pick_up = ?";
            $bindParams[] = $isPickedUp;
            $bindParamTypes .= 'i';
        }
        if ($engineerId !== null) {
            $setClauses[] = "engineer_id = ?";
            $bindParams[] = $engineerId;
            $bindParamTypes .= 'i';
        }
        if ($supervisorId !== null) {
            $setClauses[] = "supervisor_id = ?";
            $bindParams[] = $supervisorId;
            $bindParamTypes .= 'i';
        }

        $query = "UPDATE " . $this->table_name . "
                  SET " . implode(", ", $setClauses) . "
                  WHERE id = ?";
        $bindParams[] = $id; // Add ID to bind parameters
        $bindParamTypes .= 'i'; // Add type for ID

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed for updateStatus: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        $stmt->bind_param($bindParamTypes, ...$bindParams);

        if ($stmt->execute()) {
            return true;
        }
        error_log("Update report status failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }

    /**
     * Updates an existing report record (for general updates, not just status).
     *
     * @return bool True on success, false on failure.
     */
    public function update() {
        $query = "UPDATE " . $this->table_name . "
                  SET report_type=?, generator_serial_number=?, problem_issue=?,
                      remarks=?,
                      modified_datetime = NOW()
                  WHERE id=?";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        // Sanitize inputs
        $this->report_type = htmlspecialchars(strip_tags($this->report_type));
        $this->generator_serial_number = htmlspecialchars(strip_tags($this->generator_serial_number));
        $this->problem_issue = htmlspecialchars(strip_tags($this->problem_issue));
        $this->remarks = htmlspecialchars(strip_tags($this->remarks));
        $this->id = htmlspecialchars(strip_tags($this->id));

        $stmt->bind_param("ssssi",
            $this->report_type,
            $this->generator_serial_number,
            $this->problem_issue,
            $this->remarks,
            $this->id
        );

        if ($stmt->execute()) {
            return true;
        }
        error_log("Report update failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }


    /**
     * Deletes a report record by ID.
     *
     * @return bool True on success, false on failure.
     */
    public function delete() {
        $query = "DELETE FROM " . $this->table_name . " WHERE id = ?";

        $stmt = $this->conn->prepare($query);
        if ($stmt === false) {
            error_log("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
            return false;
        }

        $this->id = htmlspecialchars(strip_tags($this->id));
        $stmt->bind_param("i", $this->id);

        if ($stmt->execute()) {
            return true;
        }
        error_log("Report deletion failed: (" . $stmt->errno . ") " . $stmt->error);
        return false;
    }
}