<?php
/**
 * ZLO Platform - Base Model
 * ORM-style base model for database operations
 */

declare(strict_types=1);

abstract class Model
{
    protected Database $db;
    protected Security $security;
    protected string $table;
    protected string $primaryKey = 'id';
    protected array $fillable = [];
    protected array $hidden = [];
    protected array $casts = [];
    
    public function __construct()
    {
        $this->db = new Database();
        $this->security = new Security();
    }
    
    /**
     * Find record by ID
     */
    public function find(int $id): ?array
    {
        $sql = "SELECT * FROM {$this->table} WHERE {$this->primaryKey} = ?";
        $result = $this->db->fetchOne($sql, [$id]);
        
        return $result ? $this->processResult($result) : null;
    }
    
    /**
     * Find by specific column
     */
    public function findBy(string $column, $value): ?array
    {
        $sql = "SELECT * FROM {$this->table} WHERE {$column} = ? LIMIT 1";
        $result = $this->db->fetchOne($sql, [$value]);
        
        return $result ? $this->processResult($result) : null;
    }
    
    /**
     * Get all records
     */
    public function all(array $options = []): array
    {
        $sql = "SELECT * FROM {$this->table}";
        $params = [];
        
        // Where conditions
        if (!empty($options['where'])) {
            $conditions = [];
            foreach ($options['where'] as $column => $value) {
                $conditions[] = "{$column} = ?";
                $params[] = $value;
            }
            $sql .= " WHERE " . implode(' AND ', $conditions);
        }
        
        // Order by
        if (!empty($options['orderBy'])) {
            $direction = $options['order'] ?? 'ASC';
            $sql .= " ORDER BY {$options['orderBy']} {$direction}";
        }
        
        // Pagination
        if (!empty($options['limit'])) {
            $sql .= " LIMIT ?";
            $params[] = (int) $options['limit'];
            
            if (!empty($options['offset'])) {
                $sql .= " OFFSET ?";
                $params[] = (int) $options['offset'];
            }
        }
        
        $results = $this->db->fetchAll($sql, $params);
        
        return array_map([$this, 'processResult'], $results);
    }
    
    /**
     * Create new record
     */
    public function create(array $data): int
    {
        // Filter fillable fields
        $data = $this->filterFillable($data);
        
        // Sanitize string values
        foreach ($data as $key => $value) {
            if (is_string($value)) {
                $data[$key] = $this->security->sanitize($value);
            }
        }
        
        return $this->db->insert($this->table, $data);
    }
    
    /**
     * Update record
     */
    public function update(int $id, array $data): bool
    {
        // Filter fillable fields
        $data = $this->filterFillable($data);
        
        // Sanitize string values
        foreach ($data as $key => $value) {
            if (is_string($value)) {
                $data[$key] = $this->security->sanitize($value);
            }
        }
        
        $affected = $this->db->update(
            $this->table,
            $data,
            "{$this->primaryKey} = ?",
            [$id]
        );
        
        return $affected > 0;
    }
    
    /**
     * Delete record
     */
    public function delete(int $id): bool
    {
        $affected = $this->db->delete(
            $this->table,
            "{$this->primaryKey} = ?",
            [$id]
        );
        
        return $affected > 0;
    }
    
    /**
     * Count records
     */
    public function count(array $conditions = []): int
    {
        $sql = "SELECT COUNT(*) as count FROM {$this->table}";
        $params = [];
        
        if (!empty($conditions)) {
            $where = [];
            foreach ($conditions as $column => $value) {
                $where[] = "{$column} = ?";
                $params[] = $value;
            }
            $sql .= " WHERE " . implode(' AND ', $where);
        }
        
        $result = $this->db->fetchOne($sql, $params);
        return (int) ($result['count'] ?? 0);
    }
    
    /**
     * Check if record exists
     */
    public function exists(int $id): bool
    {
        $sql = "SELECT 1 FROM {$this->table} WHERE {$this->primaryKey} = ? LIMIT 1";
        $result = $this->db->fetchOne($sql, [$id]);
        return $result !== null;
    }
    
    /**
     * Paginate results
     */
    public function paginate(int $page = 1, int $perPage = 20, array $options = []): array
    {
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $offset = ($page - 1) * $perPage;
        
        $options['limit'] = $perPage;
        $options['offset'] = $offset;
        
        $data = $this->all($options);
        $total = $this->count($options['where'] ?? []);
        $lastPage = (int) ceil($total / $perPage);
        
        return [
            'data' => $data,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'last_page' => $lastPage,
                'from' => $offset + 1,
                'to' => min($offset + $perPage, $total)
            ]
        ];
    }
    
    /**
     * Paginate custom SQL query
     */
    public function paginateSql(string $sql, array $params, int $page, int $perPage): array
    {
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $offset = ($page - 1) * $perPage;
        
        // Count total
        $countSql = preg_replace('/SELECT\s+.*?\s+FROM/si', 'SELECT COUNT(*) as total FROM', $sql);
        $countSql = preg_replace('/ORDER\s+BY.*$/si', '', $countSql);
        $countSql = preg_replace('/LIMIT\s+\d+.*$/si', '', $countSql);
        
        $countResult = $this->db->fetchOne($countSql, $params);
        $total = (int) ($countResult['total'] ?? 0);
        
        // Add pagination
        $sql .= " LIMIT ? OFFSET ?";
        $params[] = $perPage;
        $params[] = $offset;
        
        $data = $this->db->fetchAll($sql, $params);
        $lastPage = (int) ceil($total / $perPage);
        
        return [
            'data' => $data,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'last_page' => $lastPage,
                'from' => $offset + 1,
                'to' => min($offset + $perPage, $total)
            ]
        ];
    }
    
    /**
     * Process result - apply casts and remove hidden fields
     */
    protected function processResult(array $result): array
    {
        // Apply casts
        foreach ($this->casts as $column => $type) {
            if (isset($result[$column])) {
                $result[$column] = $this->castValue($result[$column], $type);
            }
        }
        
        // Remove hidden fields
        foreach ($this->hidden as $field) {
            unset($result[$field]);
        }
        
        return $result;
    }
    
    /**
     * Cast value to specific type
     */
    protected function castValue($value, string $type)
    {
        switch ($type) {
            case 'int':
            case 'integer':
                return (int) $value;
            case 'bool':
            case 'boolean':
                return (bool) $value;
            case 'float':
            case 'double':
                return (float) $value;
            case 'array':
                return json_decode($value, true) ?? [];
            case 'json':
                return json_decode($value, true);
            case 'datetime':
                return $value;
            default:
                return $value;
        }
    }
    
    /**
     * Filter only fillable fields
     */
    protected function filterFillable(array $data): array
    {
        if (empty($this->fillable)) {
            return $data;
        }
        
        return array_intersect_key($data, array_flip($this->fillable));
    }
    
    /**
     * Begin transaction
     */
    public function beginTransaction(): bool
    {
        return $this->db->beginTransaction();
    }
    
    /**
     * Commit transaction
     */
    public function commit(): bool
    {
        return $this->db->commit();
    }
    
    /**
     * Rollback transaction
     */
    public function rollback(): bool
    {
        return $this->db->rollback();
    }
    
    /**
     * Execute raw query
     */
    public function raw(string $sql, array $params = []): array
    {
        return $this->db->fetchAll($sql, $params);
    }
}
