<?php
namespace App\Core\Model;
use PDO;
use PDOException;
use App\Core\Service\Logger;
use App\Core\Library\Security;
use App\Config\Main;
abstract class CoreModel
{
    private $db_host;
    private $db_port;
    private $db_name;
    private $db_user;
    private $db_pass; 
    private static array $pool = [];
    public $tables;
    public $procedures;

    public function __construct() {
        $this->tables = Main::dbTables();
        $this->procedures = Main::dbProcedures();
        $config = Main::dbConfig();
        
        $this->db_host = $config['db_host'];
        $this->db_port = $config['db_port'];
        $this->db_name = $config['db_name'];
        $this->db_user = $config['db_user'];
        $this->db_pass = $config['db_password'];                 
    }

    public function conn(): PDO
    {
        $dsn  = "mysql:host=" . $this->db_host . ";port=" . $this->db_port . ";dbname=" . $this->db_name . ";charset=utf8mb4";
        
        if (!isset(self::$pool[$dsn])) {
            $options = [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_PERSISTENT         => true,
            ];

            try {
                self::$pool[$dsn] = new PDO($dsn, $this->db_user, $this->db_pass, $options);
                // Logger::info("Database Connected", []);
            } catch (\PDOException $e) {
                Logger::error("DB Connection failed: " . $e->getMessage(), ['File' => __FILE__, 'Line' => __LINE__]);
                http_response_code(500);
                exit;
            } catch (\Exception $e) {
                Logger::error("DB Connection failed: " . $e->getMessage(), ['File' => __FILE__, 'Line' => __LINE__]);
                http_response_code(500);
                exit;
            }
        }
        return self::$pool[$dsn];
    }

    public function query(string $sql, $params = [], $conn=NULL) {
        try {
            if ($conn === NULL) {
                $conn = self::conn();
            }
            $stmt = $conn->prepare($sql);
            if(!empty($params)) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            return ["status" => true, "data" => $stmt->fetchAll(PDO::FETCH_ASSOC)];
        } catch (\PDOException $e) {
            return ["status" => false, "error" => $e->getMessage(), "code" => $e->errorInfo[1]];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function fetchSingleData(string $sql, array $params = []): ?array
    {
        try {
            $stmt = $this->conn()->prepare($sql);
            if(!empty($params)) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            // if ($stmt->rowCount() === 0) {
            //     Logger::error($sql, ['File' => 'BaseModel.php', 'Method' => 'fetchSingleData']);
            //     return ["status" => false, "error" => "No data found"];
            // }
            return ["status" => true, "data" => $stmt->fetch(PDO::FETCH_ASSOC) ?: null];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function fetchAllData(string $sql, array $params = []): ?array
    {
        try {
            $stmt = self::conn()->prepare($sql);
            if(!empty($params)) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            return ["status" => true, "data" => $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []];
        } catch (\Exception $e) {
            Logger::error($sql, ['File' => 'BaseModel.php', 'Method' => 'fetchAllData']);
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function fetchPaginatedData(string $sql, int $page = 1, int $page_limit = 10, array $params = []): ?array
    {
        try {
            $offset = ($page - 1) * $page_limit;
            $countSql = "SELECT COUNT(*) FROM ($sql) AS total_count";
            $countStmt = $this->conn()->prepare($countSql);
            $countStmt->execute($params);
            $total = (int) $countStmt->fetchColumn();

            $paginatedSql = $sql . " LIMIT :limit OFFSET :offset";
            $stmt = $this->conn()->prepare($paginatedSql);

            foreach ($params as $key => $value) {
                $stmt->bindValue(is_int($key) ? $key + 1 : $key, $value);
            }
            $stmt->bindValue(':limit', $page_limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

            $stmt->execute();
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            return ["status" => true, 'data' => $data, 'count' => $total, 'page' => $page, 'page_limit' => $page_limit];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function insert(string $table, array $data, $conn=NULL, $ignore_duplicate=0): array
    {
        try {
            $cols = array_keys($data);
            $fields = '`' . implode('`,`', $cols) . '`';
            $place = ':' . implode(',:', $cols);
            foreach ($data as $col => $val) {
                $bindings[$col] = $val;
            }
            if ($conn === NULL) {
                $conn = self::conn();
            }
            if($ignore_duplicate) {
                $sql = "INSERT IGNORE INTO $table ($fields) VALUES ($place)";
            } else {
                $sql = "INSERT INTO $table ($fields) VALUES ($place)";
            }
            $stmt = $conn->prepare($sql);
            $stmt->execute($bindings);
            return ["status" => true, "insert_id" => (int) $this->conn()->lastInsertId()];
        } catch (\PDOException $e) {
            return ["status" => false, "error" => $e->getMessage(), "code" => $e->errorInfo[1]];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function batchInsert(string $table, array $rows, $conn=NULL, $ignore_duplicate=0): array
    {
        try {
            if (empty($rows)) {
                throw new \Exception("No data provided for batch insert.");
            }
            $first_row = [];
            foreach($rows as $row) {
                $first_row = $row;
                break;
            }
            $columns = array_keys($first_row);
            $placeholders = [];
            $bindings = [];
            foreach ($rows as $index => $row) {
                $rowPlaceholders = [];
                foreach ($columns as $col) {
                    $placeholder = $col."_".$index;
                    $rowPlaceholders[] = ":$placeholder";
                    $bindings[$placeholder] = $row[$col];
                }
                $placeholders[] = '(' . implode(', ', $rowPlaceholders) . ')';
            }
            $columnList = '`' . implode('`, `', $columns) . '`';
            if($ignore_duplicate) {
                $sql = "INSERT IGNORE INTO $table ($columnList) VALUES " . implode(', ', $placeholders);
            } else {
                $sql = "INSERT INTO $table ($columnList) VALUES " . implode(', ', $placeholders);
            }
            if ($conn === NULL) {
                $conn = self::conn();
            }
            $stmt = $conn->prepare($sql);
            $stmt->execute($bindings);
            return ["status" => true,"inserted_rows" => $stmt->rowCount(), "last_insert_id" => (int) self::conn()->lastInsertId()];
        } catch (\PDOException $e) {
            return ["status" => false, "error" => $e->getMessage(), "code" => $e->errorInfo[1]];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(),['File' => $e->getFile(),'Line' => $e->getLine()]);
            return ["status" => false,"error" => $e->getMessage()];
        }
    }

    private function getWhere(string|array $conditions)
    {
        try {
            if (is_array($conditions)) {
                if (!$conditions) {
                    return false;
                }
                $whereParts = [];
                foreach ($conditions as $col => $val) {
                    $whereParts[] = "$col = :where_$col";
                    $bindings["where_$col"] = $val;
                }
                $whereSql = implode(' AND ', $whereParts);
            } else {
                $whereSql = $conditions;
            }
            return $whereSql;
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return false;
        }
    }

    public function update(string $table, array $data, string|array $conditions, array $bindings = [], $conn=NULL): array
    {
        try {
            if (!$data) {
                throw new \Exception("Data required to update table $table");
            }
            $setParts = [];
            foreach ($data as $col => $val) {
                $setParts[] = "`$col` = :set_$col";
            }
            $setSql = implode(', ', $setParts);
            $whereSql = $this->getWhere($conditions);
            if ($whereSql === false) {
                throw new \Exception("Unable to get update condition to update table $table");
            }
            foreach ($data as $col => $val) {
                $bindings["set_$col"] = $val;
            }
            if(is_array($conditions)) {
                foreach ($conditions as $col => $val) {
                    $bindings["where_$col"] = $val;
                }
            }
            $sql  = "UPDATE $table SET $setSql WHERE $whereSql";
            if ($conn === NULL) {
                $conn = self::conn();
            }
            $stmt = self::conn()->prepare($sql);
            $stmt->execute($bindings);
            return ["status" => true, "affected_rows" => $stmt->rowCount()];
        } catch (\PDOException $e) {
            return ["status" => false, "error" => $e->getMessage(), "code" => $e->errorInfo[1]];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    public function delete(string $table, string|array $conditions, array $bindings = [], $conn=NULL): array
    {
        try {
            $whereSql = self::getWhere($conditions);
            if ($whereSql === false) {
                throw new \Exception("Unable to get delete condition to delete from table $table");
            }
            if(is_array($conditions)) {
                foreach ($conditions as $col => $val) {
                    $bindings["where_$col"] = $val;
                }
            }
            $sql  = "DELETE FROM $table WHERE $whereSql";
            if ($conn === NULL) {
                $conn = self::conn();
            }
            $stmt = self::conn()->prepare($sql);
            $stmt->execute($bindings);
            return ["status" => true, "affected_rows" => $stmt->rowCount(), "code" => 200];
        } catch (\PDOException $f) {
            return ["status" => false, "error" => $f->getMessage(), "code" => $f->errorInfo[1]];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage(), "code" => $e->getCode() ];
        }
    }

    public function fetchMultiSetData(string $sql, array $params = [], $single=false): ?array
    {
        try {
            $stmt = self::conn()->prepare($sql);
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->execute();
            $results = [];
            do {
                $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
                $results[] = !empty($data) ? $data : [];
            } while ($stmt->nextRowset());
            return ["status" => true, "data" => count($results) > 0 ? $single ? $results[0] : $results : $results];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }

    function escapeStr($str) {
        return substr(self::conn()->quote($str), 1, -1);
    }

    public function getNumRows(string $sql, array $params = []): ?array
    {
        try {
            $stmt = $this->conn()->prepare($sql);
            if (!empty($params)) {
                $stmt->execute($params);
            } else {
                $stmt->execute();
            }
            return ["status" => true, "count" => $stmt->rowCount()];
        } catch (\Exception $e) {
            Logger::error($e->getMessage(), ['File' => $e->getFile(), 'Line' => $e->getLine()]);
            return ["status" => false, "error" => $e->getMessage()];
        }
    }
}