<?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);
    }
}