<?php
require_once __DIR__ . '/../config/database.php';

/**
 * Message Class for handling message operations
 * Using optimized queries for shared hosting performance
 */
class Message {
    private $db;
    private $table = 'messegedetails';

    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    public function deleteAllMessages() {
    $conn = $this->db->getConnection();
    $sql = "DELETE FROM $this->table";
    return $conn->query($sql);
}

    /**
     * Get total message count
     * @param array $filters Optional filters
     * @return int
     */
    public function getTotalCount($filters = []) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return 0;
        }

        $whereClause = $this->buildWhereClause($filters);

        // Use SQL_CALC_FOUND_ROWS to get total without separate query
        $query = "SELECT COUNT(*) as total FROM {$this->table} {$whereClause}";
        $result = $conn->query($query);

        if ($result && $row = $result->fetch_assoc()) {
            return (int)$row['total'];
        }

        return 0;
    }

    /**
     * Get messages with pagination and filtering
     * @param int $page
     * @param int $perPage
     * @param array $filters
     * @param string $orderBy
     * @param string $order
     * @return array
     */
    public function getMessages($page = 1, $perPage = 20, $filters = [], $orderBy = 'date', $order = 'DESC') {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return [];
        }

        // Validate and sanitize inputs
        $page = max(1, (int)$page);
        $perPage = max(1, min(100, (int)$perPage)); // Limit max per page to 100 for performance
        $orderBy = in_array($orderBy, ['id', 'type', 'title', 'mobile', 'date']) ? $orderBy : 'date';
        $order = strtoupper($order) === 'ASC' ? 'ASC' : 'DESC';

        // Calculate offset
        $offset = ($page - 1) * $perPage;

        // Build where clause from filters
        $whereClause = $this->buildWhereClause($filters);

        // Get messages with pagination
        // Using LIMIT for pagination
        $query = "SELECT * FROM {$this->table} {$whereClause} ORDER BY {$orderBy} {$order} LIMIT {$offset}, {$perPage}";
        $result = $conn->query($query);

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

        return $messages;
    }

    /**
     * Get a single message by ID
     * @param int $id
     * @return array|bool
     */
    public function getMessageById($id) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $id = (int)$id;

        $stmt = $conn->prepare("SELECT * FROM {$this->table} WHERE id = ? LIMIT 1");
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result && $result->num_rows === 1) {
            return $result->fetch_assoc();
        }

        return false;
    }

    /**
     * Add a new message
     * @param array $data
     * @return bool|int
     */
    public function addMessage($data) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        // Required fields
        if (empty($data['type']) || empty($data['title']) || empty($data['mobile']) || empty($data['subheading'])) {
            return false;
        }

        $stmt = $conn->prepare("INSERT INTO {$this->table} (type, title, mobile, subheading, image) VALUES (?, ?, ?, ?, ?)");
        $stmt->bind_param("sssss", $data['type'], $data['title'], $data['mobile'], $data['subheading'], $data['image']);

        if ($stmt->execute()) {
            $newId = $conn->insert_id;
            $stmt->close();
            return $newId;
        }

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

    /**
     * Update an existing message
     * @param int $id
     * @param array $data
     * @return bool
     */
    public function updateMessage($id, $data) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $id = (int)$id;

        // Check if fields exist
        $fieldsToUpdate = [];
        $params = [];
        $types = '';
if (isset($data['type'])) {
            $fieldsToUpdate[] = "type = ?";
            $params[] = $data['type'];
            $types .= 's';
        }
        if (isset($data['title'])) {
            $fieldsToUpdate[] = "title = ?";
            $params[] = $data['title'];
            $types .= 's';
        }

        if (isset($data['mobile'])) {
            $fieldsToUpdate[] = "mobile = ?";
            $params[] = $data['mobile'];
            $types .= 's';
        }

        if (isset($data['subheading'])) {
            $fieldsToUpdate[] = "subheading = ?";
            $params[] = $data['subheading'];
            $types .= 's';
        }

        if (isset($data['image'])) {
            $fieldsToUpdate[] = "image = ?";
            $params[] = $data['image'];
            $types .= 's';
        }

        if (empty($fieldsToUpdate)) {
            return false;
        }

        $query = "UPDATE {$this->table} SET " . implode(', ', $fieldsToUpdate) . " WHERE id = ?";
        $params[] = $id;
        $types .= 'i';

        $stmt = $conn->prepare($query);
        $stmt->bind_param($types, ...$params);

        $success = $stmt->execute();
        $stmt->close();

        return $success;
    }

    /**
     * Delete a message by ID
     * @param int $id
     * @return bool
     */
    public function deleteMessage($id) {
        $conn = $this->db->getConnection();

        if (!$conn) {
            return false;
        }

        $id = (int)$id;

        $stmt = $conn->prepare("DELETE FROM {$this->table} WHERE id = ?");
        $stmt->bind_param("i", $id);

        $success = $stmt->execute();
        $stmt->close();

        return $success;
    }

    /**
     * Delete multiple messages
     * @param array $ids
     * @return bool
     */
    public function deleteMultipleMessages($ids) {
        $conn = $this->db->getConnection();

        if (!$conn || empty($ids)) {
            return false;
        }

        // Sanitize IDs
        $ids = array_map('intval', $ids);
        $idString = implode(',', $ids);

        // Delete records
        $query = "DELETE FROM {$this->table} WHERE id IN ({$idString})";
        $success = $conn->query($query);

        return $success;
    }

    /**
     * Build WHERE clause from filters
     * @param array $filters
     * @return string
     */
    private function buildWhereClause($filters) {
        $where = [];
if (!empty($filters)) {
            // Search by title
            if (isset($filters['type']) && !empty($filters['type'])) {
                $title = $this->db->escapeString($filters['type']);
                $where[] = "title LIKE '%{$title}%'";
            }
        if (!empty($filters)) {
            // Search by title
            if (isset($filters['title']) && !empty($filters['title'])) {
                $title = $this->db->escapeString($filters['title']);
                $where[] = "title LIKE '%{$title}%'";
            }

            // Search by mobile
            if (isset($filters['mobile']) && !empty($filters['mobile'])) {
                $mobile = $this->db->escapeString($filters['mobile']);
                $where[] = "mobile LIKE '%{$mobile}%'";
            }

            // Search by content
            if (isset($filters['search']) && !empty($filters['search'])) {
                $search = $this->db->escapeString($filters['search']);
                $where[] = "(title LIKE '%{$search}%' OR mobile LIKE '%{$search}%' OR subheading LIKE '%{$search}%')";
            }

            // Date range
            if (isset($filters['start_date']) && !empty($filters['start_date'])) {
                $startDate = $this->db->escapeString($filters['start_date']);
                $where[] = "date >= '{$startDate} 00:00:00'";
            }

            if (isset($filters['end_date']) && !empty($filters['end_date'])) {
                $endDate = $this->db->escapeString($filters['end_date']);
                $where[] = "date <= '{$endDate} 23:59:59'";
            }
        }

        return !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';
    }
}
}