<?php
//For Select -> Tablename , column name , where , additional parameters
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
class QueryBuilder
{
    private $conn;

    public function __construct($conn)
    {
        $this->conn = $conn;
    }

    private function prepareAndExecute($sql, $params , $isSelect = false)
    {

        $stmt = $this->conn->prepare($sql);
        if ($stmt === false) {
            echo "prepare failed: " . $this->conn->error;
        }
        try{
            if (!empty($params)) {
                // Determine the types of the parameters
                $types = '';
                foreach ($params as $param) {
                    if (is_int($param)) {
                        $types .= 'i';
                    } elseif (is_float($param)) {
                        $types .= 'd';
                    } elseif (is_string($param)) {
                        $types .= 's';
                    } else {
                        $types .= 'b'; // blob and other types
                    }
                }
                $stmt->bind_param($types, ...$params);
            }
            if (!$stmt->execute()) {
                echo "execute failed: " . $this->conn->error;
            }
            if ($isSelect) {
            $result = $stmt->get_result();
            return ['stmt' => $stmt, 'result' => $result];
        }
        return $stmt;
    } catch (Exception $e) {
        $stmt->close();
        error_log("Error in prepareAndExecute: " . $e->getMessage() . " | SQL: $sql, Params: " . json_encode($params));
        throw $e;
    }
    }

    public function select($table, $columns = "*", $where = [], $orWhere = [], $additionalConditions = "", $additionalParams = [], $orderBy  = null  , $limit = null, $offset = null) {
        $columnsList = is_array($columns) ? implode(", ", $columns) : $columns;
        $whereConditions  = [];
        $params = [];

        if($where)
        {
            foreach($where as $key => $value)
            {
                $whereConditions[] = "$key = ?";
                $params[] = $value;
            }
        }
        if ($orWhere) {
            $orConditions = [];
            foreach ($orWhere as $conditions) {
                $subConditions = [];
                foreach ($conditions as $key => $value) {
                    $subConditions[] = "$key = ?";
                    $params[] = $value;
                }
                $orConditions[] = "(" . implode(" AND ", $subConditions) . ")";
            }
            if (!empty($orConditions)) {
                $whereConditions[] = '(' . implode(' OR ', $orConditions) . ')';
            }
        }

        $whereClause = $whereConditions ? "WHERE " . implode(" AND ", $whereConditions) : "";
        if ($whereClause && $additionalConditions) {
            $whereClause .= " AND $additionalConditions";
        } elseif ($additionalConditions) {
            $whereClause = "WHERE $additionalConditions";
        }
        $limitClause = $limit ? "LIMIT ?" : "";
        $offsetClause = $offset ? "OFFSET ?" : "";
        $orderByClause = $orderBy ? "ORDER BY $orderBy" : "";

        $sql = "SELECT $columnsList FROM $table $whereClause  $orderByClause $limitClause $offsetClause";      
        $params = array_merge($params, $additionalParams);
        if ($limit) $params[] = $limit;
        if ($offset) $params[] = $offset;

        $response = $this->prepareAndExecute($sql, $params , true);
        $stmt = $response['stmt'];
        $result = $response['result'];
        $data = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $data;
    }
//     public function select($table, $columns = "*", $where = [], $orWhere = [], $additionalConditions = "", $additionalParams = [], $limit = null, $offset = null)
// {
//     // Process columns into a string
//     $columnsList = is_array($columns) ? implode(", ", $columns) : $columns;

//     $whereConditions = [];
//     $params = [];

//     // Build WHERE conditions for $where array
//     if ($where) {
//         foreach ($where as $key => $value) {
//             $whereConditions[] = "$key = ?";
//             $params[] = $value;
//         }
//     }

//     // Build OR WHERE conditions
//     if ($orWhere) {
//         $orConditions = [];
//         foreach ($orWhere as $conditions) {
//             $subConditions = [];
//             foreach ($conditions as $key => $value) {
//                 $subConditions[] = "$key = ?";
//                 $params[] = $value;
//             }
//             $orConditions[] = "(" . implode(" AND ", $subConditions) . ")";
//         }
//         if (!empty($orConditions)) {
//             $whereConditions[] = '(' . implode(' OR ', $orConditions) . ')';
//         }
//     }

//     // Combine all WHERE clauses
//     $whereClause = $whereConditions ? "WHERE " . implode(" AND ", $whereConditions) : "";

//     // Additional conditions
//     if ($whereClause && $additionalConditions) {
//         $whereClause .= " AND $additionalConditions";
//     } elseif ($additionalConditions) {
//         $whereClause = "WHERE $additionalConditions";
//     }

//     // Process LIMIT and OFFSET clauses
//     $limitClause = $limit ? "LIMIT ?" : "";
//     $offsetClause = $offset ? "OFFSET ?" : "";

//     // Combine everything into the SQL query
//     $sql = "SELECT $columnsList FROM $table $whereClause $limitClause $offsetClause";

//     // Merge parameters
//     $params = array_merge($params, $additionalParams);
//     if ($limit) {
//         $params[] = $limit;
//     }
//     if ($offset) {
//         $params[] = $offset;
//     }

//     // Execute the query
//     $stmt = $this->prepareAndExecute($sql, $params);
//     $result = $stmt->get_result();

//     // Return the result as an associative array
//     return $result->fetch_all(MYSQLI_ASSOC);
// }


    // public function select($table, $columns = "*", $where = [], $additionalConditions = "", $additionalParams = [], $limit = null, $offset = null)
    // {
    //     $columnsList = is_array($columns) ? implode(", ", $columns) : $columns;
    //     $orStr = '';

    //     foreach($where as $key => $val){
    //         // echo $key .'-'.count($val);
    //         // print_r($where);
    //         if($key == 'OR' && count($val) > 1){
    //             $orStr = '(';
    //             $index = 1;
    //             foreach($val as $key1 => $val1){
    //                 foreach($val1 as $key2 => $val2){
    //                     $orStr .= $key2.'='.$val2;
    //                     if(count($val) > $index){
    //                         $orStr .= ' OR ';
    //                     }
    //                     $index++;
    //                 }
    //             }
    //             $orStr .= ')';
    //             echo '000';
    //         }
    //         $andStr = '';
    //         if($key == 'AND'){
    //             if(count($val) > 1){
    //                 $index = 1;
    //                 foreach($val as $key1 => $val1){
    //                     foreach($val1 as $key2 => $val2){
    //                         $andStr .= $key2.'='.$val2;
    //                         if(count($val) > $index){
    //                             $andStr .= ' AND ';
    //                         }
    //                         $index++;
    //                     }
    //                 }
    //             }else{
    //                 $andStr .= $key.'='.$val[0];
    //             }

    //         }
    //         // if($key == 'AND'){
    //         //     $orStr = '(';
    //         //     foreach($val as $key1 => $val1){
    //         //         $orStr .= $key1.'='.$val1;
    //         //     }
    //         //     $orStr .= ')';
    //         // }
    //     }
    //     // echo $orStr.'--'.$andStr;
    //     $whereClause = $where ? "WHERE " .  $orStr . $andStr : "";

    //     // $whereClause = $where ? "WHERE " . implode(" AND ", array_map(fn ($key) => "$key = ?", array_keys($where))) : "";
    //     // if ($whereClause && $additionalConditions) {
    //     //     $whereClause .= " AND $additionalConditions";
    //     // } elseif ($additionalConditions) {
    //     //     $whereClause = "WHERE $additionalConditions";
    //     // }
    //     $limitClause = $limit ? "LIMIT ?" : "";
    //     $offsetClause = $offset ? "OFFSET ?" : "";
    //     // echo $whereClause;die();
    //     $sql = "SELECT $columnsList FROM $table $whereClause $limitClause $offsetClause";

    //     $params = array_merge(array_values($where), $additionalParams);
    //     if ($limit) $params[] = $limit;
    //     if ($offset) $params[] = $offset;

    //     $stmt = $this->prepareAndExecute($sql, $params);
    //     $result = $stmt->get_result();
    //     return $result->fetch_all(MYSQLI_ASSOC);
    // }

    public function selectById($table, $columns = "*", $where = [], $additionalConditions = "", $additionalParams = [], $limit = null, $offset = null)
    {
        $columnsList = is_array($columns) ? implode(", ", $columns) : $columns;
        $whereClause = $where ? "WHERE " . implode(" AND ", array_map(fn ($key) => "$key = ?", array_keys($where))) : "";
        if ($whereClause && $additionalConditions) {
            $whereClause .= " AND $additionalConditions";
        } elseif ($additionalConditions) {
            $whereClause = "WHERE $additionalConditions";
        }
        $limitClause = $limit ? "LIMIT ?" : "";
        $offsetClause = $offset ? "OFFSET ?" : "";
        $sql = "SELECT $columnsList FROM $table $whereClause $limitClause $offsetClause";

        $params = array_merge(array_values($where), $additionalParams);
        if ($limit) $params[] = $limit;
        if ($offset) $params[] = $offset;

        $response = $this->prepareAndExecute($sql, $params,true);
        $stmt = $response['stmt'];
        $result = $response['result'];
        $data = $result->fetch_assoc();
        $stmt->close();
        return $data;
    }
    // public function selectById($table, $columns = "*", $where = [], $additionalConditions = "", $additionalParams = [], $limit = null, $offset = null)
    // {
    //     // Check if columns is an array and prepare the columns list for the SELECT statement
    //     $columnsList = is_array($columns) ? implode(", ", $columns) : $columns;

    //     // Prepare the WHERE clause
    //     $whereClause = "";
    //     if (!empty($where)) {
    //         $whereParts = array();
    //         foreach ($where as $key => $value) {
    //             $whereParts[] = "$key = ?";
    //         }
    //         $whereClause = "WHERE " . implode(" AND ", $whereParts);
    //     }

    //     // Append additional conditions if provided
    //     if ($whereClause && $additionalConditions) {
    //         $whereClause .= " AND $additionalConditions";
    //     } elseif ($additionalConditions) {
    //         $whereClause = "WHERE $additionalConditions";
    //     }

    //     // Handle LIMIT and OFFSET
    //     $limitClause = !is_null($limit) ? "LIMIT ?" : "";
    //     $offsetClause = !is_null($offset) ? "OFFSET ?" : "";

    //     // Prepare the SQL query
    //     $sql = "SELECT $columnsList FROM $table $whereClause $limitClause $offsetClause";

    //     // Merge the parameters for the prepared statement
    //     $params = array_merge(array_values($where), $additionalParams);
    //     if (!is_null($limit)) {
    //         $params[] = $limit;
    //     }
    //     if (!is_null($offset)) {
    //         $params[] = $offset;
    //     }

    //     // Prepare and execute the statement
    //     $stmt = $this->prepareAndExecute($sql, $params);

    //     // Fetch and return the result
    //     $result = $stmt->get_result();
    //     return $result->fetch_assoc();
    // }


    public function insert($table, $data)
    {
        $columns = implode(", ", array_keys($data));
        $placeholders = implode(", ", array_fill(0, count($data), "?"));
        $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
        $stmt = $this->prepareAndExecute($sql, array_values($data));
        return $stmt->insert_id;
    }
    public function update($table, $data, $where)
    {
        $setClauses = [];
        $whereClauses = [];
        $params = [];

        // Process $data for SET clause
        foreach ($data as $key => $value) {
            if (strpos($key, '->') !== false) {
                // Handle JSON key
                list($jsonColumn, $jsonKey) = explode('->', $key);
                $setClauses[] = "$jsonColumn = JSON_SET($jsonColumn, '$.$jsonKey', ?)";
            } else {
                // Regular column
                $setClauses[] = "$key = ?";
            }
            $params[] = $value;
        }

        // Process $where for WHERE clause
        foreach ($where as $key => $value) {
            if (strpos($key, '->') !== false) {
                // Handle JSON key
                list($jsonColumn, $jsonKey) = explode('->', $key);
                $whereClauses[] = "JSON_EXTRACT($jsonColumn, '$.$jsonKey') = ?";
            } else {
                // Regular column
                $whereClauses[] = "$key = ?";
            }
            $params[] = $value;
        }

        // Combine into a single SQL statement
        $sql = "UPDATE $table SET " . implode(", ", $setClauses) . " WHERE " . implode(" AND ", $whereClauses);

        // Prepare and execute the statement
        $stmt = $this->prepareAndExecute($sql, $params,false);
        $data = $stmt->affected_rows;
        $stmt->close();
        return $data;
    }
//     public function update($table, $data, $where)
// {
//     $setClauses = [];
//     $whereClauses = [];
//     $params = [];

//     // Process $data for SET clause
//     foreach ($data as $key => $value) {
//         if (strpos($key, '->') !== false) {
//             // Handle JSON key (JSON_SET is available in MySQL 5.7+)
//             list($jsonColumn, $jsonKey) = explode('->', $key);
//             $setClauses[] = "$jsonColumn = JSON_SET($jsonColumn, '$.$jsonKey', ?)";
//         } else {
//             // Regular column
//             $setClauses[] = "$key = ?";
//         }
//         $params[] = $value;
//     }

//     // Process $where for WHERE clause
//     foreach ($where as $key => $value) {
//         if (strpos($key, '->') !== false) {
//             // Handle JSON key (JSON_EXTRACT is available in MySQL 5.7+)
//             list($jsonColumn, $jsonKey) = explode('->', $key);
//             $whereClauses[] = "JSON_EXTRACT($jsonColumn, '$.$jsonKey') = ?";
//         } else {
//             // Regular column
//             $whereClauses[] = "$key = ?";
//         }
//         $params[] = $value;
//     }

//     // Combine into a single SQL statement
//     $sql = "UPDATE $table SET " . implode(", ", $setClauses) . " WHERE " . implode(" AND ", $whereClauses);

//     // Prepare and execute the statement
//     $stmt = $this->prepareAndExecute($sql, $params);

//     return $stmt->affected_rows;
// }

    public function delete($table, $where)
    {
        $whereClause = implode(" AND ", array_map(fn ($key) => "$key = ?", array_keys($where)));
        $sql = "DELETE FROM $table WHERE $whereClause";
        $stmt = $this->prepareAndExecute($sql, array_values($where));
        $result = $stmt->affected_rows;
        $stmt->close();
        return $result;
    }
//     public function delete($table, $where)
// {
//     $whereClause = [];
//     $params = [];

//     // Process $where for WHERE clause
//     foreach ($where as $key => $value) {
//         $whereClause[] = "$key = ?";
//         $params[] = $value;
//     }

//     // Combine WHERE conditions
//     $whereClauseStr = implode(" AND ", $whereClause);

//     // Build SQL query
//     $sql = "DELETE FROM $table WHERE $whereClauseStr";

//     // Prepare and execute the statement
//     $stmt = $this->prepareAndExecute($sql, $params);

//     return $stmt->affected_rows;
// }

    // public function __destruct() {
    //     $this->conn->close();
    // }


}
