<?php
namespace App\Models;
use App\Core\Database;
use Exception;
/**
* Base Model Class
* Provides common database operations with security and performance optimizations
*/
abstract class BaseModel
{
protected Database $db;
protected string $table = '';
protected string $primaryKey = 'id';
protected array $fillable = [];
protected array $guarded = ['id', 'created_at', 'updated_at'];
protected array $hidden = [];
protected array $casts = [];
protected bool $timestamps = true;
protected array $dates = ['created_at', 'updated_at'];
// Query builder properties
protected array $wheres = [];
protected array $joins = [];
protected array $orderBy = [];
protected ?int $limit = null;
protected ?int $offset = null;
protected array $select = ['*'];
public function __construct()
{
$this->db = Database::getInstance();
if (empty($this->table)) {
// Auto-generate table name from class name
$className = (new \ReflectionClass($this))->getShortName();
$this->table = strtolower(preg_replace('/(?<!^)[A-Z]/', '_$0', $className)).'s';
}
}
/**
* Find record by primary key
*/
public function find($id): ?array
{
$sql = "SELECT * FROM {$this->table} WHERE {$this->primaryKey} = ? LIMIT 1";
$result = $this->db->queryOne($sql, [$id]);
return $result ? $this->castAttributes($result) : null;
}
/**
* Find record by primary key or throw exception
*/
public function findOrFail($id): array
{
$result = $this->find($id);
if (!$result) {
throw new Exception("Record not found in {$this->table} with {$this->primaryKey} = {$id}");
}
return $result;
}
/**
* Find first record matching conditions
*/
public function first(): ?array
{
$this->limit = 1;
$results = $this->get();
return $results[0] ?? null;
}
/**
* Find first record or throw exception
*/
public function firstOrFail(): array
{
$result = $this->first();
if (!$result) {
throw new Exception("No records found in {$this->table}");
}
return $result;
}
/**
* Get all records matching current query
*/
public function get(): array
{
$sql = $this->buildSelectQuery();
$params = $this->getWhereParams();
$results = $this->db->query($sql, $params);
// Reset query builder
$this->resetQuery();
return array_map([$this, 'castAttributes'], $results);
}
/**
* Execute raw SQL query
*/
public function query(string $sql, array $params = []): array
{
return $this->db->query($sql, $params);
}
/**
* Get all records from table
*/
public function all(): array
{
return $this->get();
}
/**
* Count records matching current query
*/
public function count(): int
{
$originalSelect = $this->select;
$this->select = ['COUNT(*) as count'];
$sql = $this->buildSelectQuery();
$params = $this->getWhereParams();
$result = $this->db->queryOne($sql, $params);
// Reset query builder
$this->select = $originalSelect;
$this->resetQuery();
return (int) ($result['count'] ?? 0);
}
/**
* Check if records exist matching current query
*/
public function exists(): bool
{
return $this->count() > 0;
}
/**
* Create new record
*/
public function create(array $data): int
{
$data = $this->filterFillable($data);
if ($this->timestamps) {
$data['created_at'] = date('Y-m-d H:i:s');
$data['updated_at'] = date('Y-m-d H:i:s');
}
$columns = array_keys($data);
$placeholders = array_fill(0, count($columns), '?');
$sql = "INSERT INTO {$this->table} (".implode(', ', $columns).") VALUES (".implode(', ', $placeholders).")";
// Get the connection to ensure we use the same one for lastInsertId
$pdo = $this->db->getConnection();
try {
$stmt = $pdo->prepare($sql);
$stmt->execute(array_values($data));
$lastId = $pdo->lastInsertId();
return (int) $lastId;
} catch (Exception $e) {
throw new Exception("Failed to create record in {$this->table}: ".$e->getMessage());
}
}
/**
* Update records matching current query
*/
public function update(array $data): int
{
if (empty($this->wheres)) {
throw new Exception("Cannot update without WHERE conditions for safety");
}
$data = $this->filterFillable($data);
if ($this->timestamps) {
$data['updated_at'] = date('Y-m-d H:i:s');
}
$setParts = [];
$params = [];
foreach ($data as $column => $value) {
$setParts[] = "{$column} = ?";
$params[] = $value;
}
$sql = "UPDATE {$this->table} SET ".implode(', ', $setParts);
$sql .= $this->buildWhereClause();
$params = array_merge($params, $this->getWhereParams());
$affectedRows = $this->db->execute($sql, $params);
// Reset query builder
$this->resetQuery();
return $affectedRows;
}
/**
* Update record by primary key
*/
public function updateById($id, array $data): int
{
return $this->where($this->primaryKey, $id)->update($data);
}
/**
* Delete records matching current query
*/
public function delete(): int
{
if (empty($this->wheres)) {
throw new Exception("Cannot delete without WHERE conditions for safety");
}
$sql = "DELETE FROM {$this->table}";
$sql .= $this->buildWhereClause();
$params = $this->getWhereParams();
$affectedRows = $this->db->execute($sql, $params);
// Reset query builder
$this->resetQuery();
return $affectedRows;
}
/**
* Delete record by primary key
*/
public function deleteById($id): int
{
return $this->where($this->primaryKey, $id)->delete();
}
/**
* Add WHERE condition
*/
public function where(string $column, $operator, $value = null): self
{
// If only 2 parameters, assume operator is '='
if ($value === null) {
$value = $operator;
$operator = '=';
}
$this->wheres[] = [
'type' => 'basic',
'column' => $column,
'operator' => $operator,
'value' => $value,
'boolean' => 'AND'
];
return $this;
}
/**
* Add OR WHERE condition
*/
public function orWhere(string $column, $operator, $value = null): self
{
if ($value === null) {
$value = $operator;
$operator = '=';
}
$this->wheres[] = [
'type' => 'basic',
'column' => $column,
'operator' => $operator,
'value' => $value,
'boolean' => 'OR'
];
return $this;
}
/**
* Add WHERE IN condition
*/
public function whereIn(string $column, array $values): self
{
$this->wheres[] = [
'type' => 'in',
'column' => $column,
'values' => $values,
'boolean' => 'AND'
];
return $this;
}
/**
* Add WHERE NOT IN condition
*/
public function whereNotIn(string $column, array $values): self
{
$this->wheres[] = [
'type' => 'not_in',
'column' => $column,
'values' => $values,
'boolean' => 'AND'
];
return $this;
}
/**
* Add WHERE NULL condition
*/
public function whereNull(string $column): self
{
$this->wheres[] = [
'type' => 'null',
'column' => $column,
'boolean' => 'AND'
];
return $this;
}
/**
* Add WHERE NOT NULL condition
*/
public function whereNotNull(string $column): self
{
$this->wheres[] = [
'type' => 'not_null',
'column' => $column,
'boolean' => 'AND'
];
return $this;
}
/**
* Add WHERE LIKE condition
*/
public function whereLike(string $column, string $value): self
{
return $this->where($column, 'LIKE', $value);
}
/**
* Add ORDER BY clause
*/
public function orderBy(string $column, string $direction = 'ASC'): self
{
$this->orderBy[] = [
'column' => $column,
'direction' => strtoupper($direction)
];
return $this;
}
/**
* Add LIMIT clause
*/
public function limit(int $limit): self
{
$this->limit = $limit;
return $this;
}
/**
* Add OFFSET clause
*/
public function offset(int $offset): self
{
$this->offset = $offset;
return $this;
}
/**
* Set SELECT columns
*/
public function select(array $columns): self
{
$this->select = $columns;
return $this;
}
/**
* Add JOIN clause
*/
public function join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self
{
$this->joins[] = [
'type' => $type,
'table' => $table,
'first' => $first,
'operator' => $operator,
'second' => $second
];
return $this;
}
/**
* Add LEFT JOIN clause
*/
public function leftJoin(string $table, string $first, string $operator, string $second): self
{
return $this->join($table, $first, $operator, $second, 'LEFT');
}
/**
* Add RIGHT JOIN clause
*/
public function rightJoin(string $table, string $first, string $operator, string $second): self
{
return $this->join($table, $first, $operator, $second, 'RIGHT');
}
/**
* Paginate results
*/
public function paginate(int $page = 1, int $perPage = 15): array
{
$offset = ($page - 1) * $perPage;
// Get total count
$total = $this->count();
// Get paginated results
$results = $this->limit($perPage)->offset($offset)->get();
return [
'data' => $results,
'current_page' => $page,
'per_page' => $perPage,
'total' => $total,
'last_page' => ceil($total / $perPage),
'from' => $offset + 1,
'to' => min($offset + $perPage, $total)
];
}
/**
* Build SELECT query
*/
protected function buildSelectQuery(): string
{
$sql = "SELECT ".implode(', ', $this->select)." FROM {$this->table}";
// Add JOINs
foreach ($this->joins as $join) {
$sql .= " {$join['type']} JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
}
// Add WHERE clause
$sql .= $this->buildWhereClause();
// Add ORDER BY
if (!empty($this->orderBy)) {
$orderParts = [];
foreach ($this->orderBy as $order) {
$orderParts[] = "{$order['column']} {$order['direction']}";
}
$sql .= " ORDER BY ".implode(', ', $orderParts);
}
// Add LIMIT and OFFSET
if ($this->limit !== null) {
$sql .= " LIMIT {$this->limit}";
}
if ($this->offset !== null) {
$sql .= " OFFSET {$this->offset}";
}
return $sql;
}
/**
* Build WHERE clause
*/
protected function buildWhereClause(): string
{
if (empty($this->wheres)) {
return '';
}
$sql = ' WHERE ';
$parts = [];
foreach ($this->wheres as $i => $where) {
$part = '';
// Add boolean operator (except for first condition)
if ($i > 0) {
$part .= " {$where['boolean']} ";
}
switch ($where['type']) {
case 'basic':
$part .= "{$where['column']} {$where['operator']} ?";
break;
case 'in':
$placeholders = str_repeat('?,', count($where['values']) - 1).'?';
$part .= "{$where['column']} IN ({$placeholders})";
break;
case 'not_in':
$placeholders = str_repeat('?,', count($where['values']) - 1).'?';
$part .= "{$where['column']} NOT IN ({$placeholders})";
break;
case 'null':
$part .= "{$where['column']} IS NULL";
break;
case 'not_null':
$part .= "{$where['column']} IS NOT NULL";
break;
}
$parts[] = $part;
}
return $sql.implode('', $parts);
}
/**
* Get parameters for WHERE clause
*/
protected function getWhereParams(): array
{
$params = [];
foreach ($this->wheres as $where) {
switch ($where['type']) {
case 'basic':
$params[] = $where['value'];
break;
case 'in':
case 'not_in':
$params = array_merge($params, $where['values']);
break;
}
}
return $params;
}
/**
* Filter data to only fillable attributes
*/
protected function filterFillable(array $data): array
{
if (empty($this->fillable)) {
// If no fillable defined, exclude guarded
return array_diff_key($data, array_flip($this->guarded));
}
// Only include fillable attributes
return array_intersect_key($data, array_flip($this->fillable));
}
/**
* Cast attributes to appropriate types
*/
protected function castAttributes(array $attributes): array
{
foreach ($this->casts as $key => $type) {
if (array_key_exists($key, $attributes)) {
$attributes[$key] = $this->castAttribute($attributes[$key], $type);
}
}
// Hide hidden attributes
return array_diff_key($attributes, array_flip($this->hidden));
}
/**
* Cast single attribute
*/
protected function castAttribute($value, string $type)
{
if ($value === null) {
return null;
}
switch ($type) {
case 'int':
case 'integer':
return (int) $value;
case 'float':
case 'double':
return (float) $value;
case 'bool':
case 'boolean':
return (bool) $value;
case 'string':
return (string) $value;
case 'array':
case 'json':
return is_string($value) ? json_decode($value, true) : $value;
case 'object':
return is_string($value) ? json_decode($value) : $value;
case 'datetime':
return new \DateTime($value);
default:
return $value;
}
}
/**
* Reset query builder
*/
protected function resetQuery(): void
{
$this->wheres = [];
$this->joins = [];
$this->orderBy = [];
$this->limit = null;
$this->offset = null;
$this->select = ['*'];
}
/**
* Execute raw SQL query
*/
public function raw(string $sql, array $params = []): array
{
return $this->db->query($sql, $params);
}
/**
* Begin database transaction
*/
public function beginTransaction(): bool
{
return $this->db->beginTransaction();
}
/**
* Commit database transaction
*/
public function commit(): bool
{
return $this->db->commit();
}
/**
* Rollback database transaction
*/
public function rollback(): bool
{
return $this->db->rollback();
}
/**
* Execute callback within transaction
*/
public function transaction(callable $callback)
{
return $this->db->transaction($callback);
}
}