<?php

require_once __DIR__ . '/../config/Database.php';

abstract class BaseModel
{
    protected $db;
    protected $table;

    public function __construct()
    {
        $this->db = Database::getInstance()->getConnection();
    }

    /**
     * Find a record by ID
     */
    public function find($id)
    {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE id = ? LIMIT 1");
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();

        return $result->fetch_assoc();
    }

    /**
     * Find all records with optional conditions
     */
    public function findAll($conditions = [], $orderBy = 'id ASC', $limit = null)
    {
        $sql = "SELECT * FROM {$this->table}";
        $params = [];
        $types = "";

        if (!empty($conditions)) {
            $whereClause = [];
            foreach ($conditions as $field => $value) {
                $whereClause[] = "$field = ?";
                $params[] = $value;
                $types .= is_int($value) ? "i" : "s";
            }
            $sql .= " WHERE " . implode(" AND ", $whereClause);
        }

        $sql .= " ORDER BY $orderBy";

        if ($limit) {
            $sql .= " LIMIT $limit";
        }

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $data = [];
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
        $stmt->close();

        return $data;
    }

    /**
     * Create a new record
     */
    public function create($data)
    {
        $fields = array_keys($data);
        $placeholders = str_repeat('?,', count($fields) - 1) . '?';

        $sql = "INSERT INTO {$this->table} (" . implode(',', $fields) . ") VALUES ($placeholders)";

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $values = array_values($data);
        $types = "";
        foreach ($values as $value) {
            if (is_int($value)) {
                $types .= "i";
            } elseif (is_float($value)) {
                $types .= "d";
            } else {
                $types .= "s";
            }
        }

        $stmt->bind_param($types, ...$values);
        $result = $stmt->execute();
        $insertId = $this->db->insert_id;
        $stmt->close();

        return $result ? $insertId : false;
    }

    /**
     * Update a record by ID
     */
    public function update($id, $data)
    {
        $fields = array_keys($data);
        $setClause = implode(' = ?, ', $fields) . ' = ?';

        $sql = "UPDATE {$this->table} SET $setClause WHERE id = ?";

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $values = array_values($data);
        $values[] = $id;

        $types = "";
        foreach ($values as $value) {
            if (is_int($value)) {
                $types .= "i";
            } elseif (is_float($value)) {
                $types .= "d";
            } else {
                $types .= "s";
            }
        }

        $stmt->bind_param($types, ...$values);
        $result = $stmt->execute();
        $affectedRows = $stmt->affected_rows;
        $stmt->close();

        return $affectedRows > 0;
    }

    /**
     * Delete a record by ID
     */
    public function delete($id)
    {
        $stmt = $this->db->prepare("DELETE FROM {$this->table} WHERE id = ?");
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        $stmt->bind_param("i", $id);
        $result = $stmt->execute();
        $affectedRows = $stmt->affected_rows;
        $stmt->close();

        return $affectedRows > 0;
    }

    /**
     * Count records with optional conditions
     */
    public function count($conditions = [])
    {
        $sql = "SELECT COUNT(*) as total FROM {$this->table}";
        $params = [];
        $types = "";

        if (!empty($conditions)) {
            $whereClause = [];
            foreach ($conditions as $field => $value) {
                $whereClause[] = "$field = ?";
                $params[] = $value;
                $types .= is_int($value) ? "i" : "s";
            }
            $sql .= " WHERE " . implode(" AND ", $whereClause);
        }

        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();

        return (int) $row['total'];
    }

    /**
     * Execute custom query
     */
    protected function query($sql, $params = [], $types = "")
    {
        $stmt = $this->db->prepare($sql);
        if (!$stmt) {
            throw new Exception("Database prepare error: " . $this->db->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $data = [];

        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
        }

        $stmt->close();
        return $data;
    }
}
