<?php
/**
 * Database Helper Functions
 * Common database operations used across the application
 */

require_once __DIR__ . '/database.php';

class DBHelper {
    private $conn;

    public function __construct($connection) {
        $this->conn = $connection;
    }

    /**
     * Get all available rooms
     */
    public function getAllRooms() {
        $sql = "SELECT * FROM rooms WHERE is_available = 1 ORDER BY price_per_night ASC";
        $result = $this->conn->query($sql);
        return $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    }

    /**
     * Get room by ID
     */
    public function getRoomById($room_id) {
        $sql = "SELECT * FROM rooms WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $room_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    /**
     * Get published news articles
     */
    public function getPublishedNews($limit = 10) {
        $sql = "SELECT * FROM news WHERE status = 'published' ORDER BY published_at DESC LIMIT ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $limit);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    /**
     * Get news article by slug
     */
    public function getNewsBySlug($slug) {
        $sql = "SELECT * FROM news WHERE slug = ? AND status = 'published'";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('s', $slug);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    /**
     * Get all facilities
     */
    public function getFacilities() {
        $sql = "SELECT * FROM facilities WHERE is_active = 1 ORDER BY id ASC";
        $result = $this->conn->query($sql);
        return $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    }

    /**
     * Get gallery images
     */
    public function getGalleryImages($category = null, $limit = null) {
        $sql = "SELECT * FROM gallery WHERE 1=1";
        
        if ($category) {
            $sql .= " AND category = '$category'";
        }
        
        $sql .= " ORDER BY sort_order ASC, id DESC";
        
        if ($limit) {
            $sql .= " LIMIT $limit";
        }

        $result = $this->conn->query($sql);
        return $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    }

    /**
     * Get active special offers
     */
    public function getActiveOffers() {
        $sql = "SELECT * FROM special_offers 
                WHERE is_active = 1 
                AND start_date <= CURDATE() 
                AND end_date >= CURDATE()
                ORDER BY discount_value DESC";
        $result = $this->conn->query($sql);
        return $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    }

    /**
     * Check room availability for date range
     */
    public function checkRoomAvailability($room_id, $check_in, $check_out) {
        $sql = "SELECT COUNT(*) as conflicts FROM reservations 
                WHERE room_id = ? 
                AND status != 'cancelled'
                AND NOT (check_out_date <= ? OR check_in_date >= ?)";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('iss', $room_id, $check_in, $check_out);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        
        return $result['conflicts'] == 0;
    }

    /**
     * Get user reservations
     */
    public function getUserReservations($user_id) {
        $sql = "SELECT r.id, r.user_id, r.room_id, r.check_in_date, r.check_out_date, 
                       r.number_of_guests, r.number_of_rooms, r.total_price, r.status, 
                       r.special_requests, r.payment_status, r.payment_method, r.transaction_id,
                       r.created_at, r.updated_at,
                       ro.room_number, ro.room_type, ro.description, ro.max_guests, 
                       ro.price_per_night, ro.size_sqft, ro.floor_number, ro.view_type, ro.image_url
                FROM reservations r
                LEFT JOIN rooms ro ON r.room_id = ro.id
                WHERE r.user_id = ?
                ORDER BY r.check_in_date DESC";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        return $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    }

    /**
     * Get user details
     */
    public function getUserById($user_id) {
        $sql = "SELECT * FROM users WHERE id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    /**
     * Update user profile
     */
    public function updateUserProfile($user_id, $data) {
        $allowed_fields = ['first_name', 'last_name', 'phone', 'address', 'city', 'country', 'postal_code'];
        
        $sql = "UPDATE users SET ";
        $updates = [];
        $values = [];
        $types = '';

        foreach ($data as $field => $value) {
            if (in_array($field, $allowed_fields)) {
                $updates[] = "$field = ?";
                $values[] = $value;
                $types .= 's';
            }
        }

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

        $sql .= implode(", ", $updates) . " WHERE id = ?";
        $values[] = $user_id;
        $types .= 'i';

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param($types, ...$values);
        return $stmt->execute();
    }

    /**
     * Get reservation details
     */
    public function getReservationDetails($reservation_id) {
        $sql = "SELECT r.*, u.email, u.first_name, u.last_name, u.phone,
                ro.room_number, ro.room_type, ro.image_url, ro.price_per_night
                FROM reservations r
                JOIN users u ON r.user_id = u.id
                JOIN rooms ro ON r.room_id = ro.id
                WHERE r.id = ?";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $reservation_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    /**
     * Get user service reservations
     */
    public function getUserServiceReservations($user_id) {
        $sql = "SELECT sr.*, f.name as facility_name, f.icon, f.description
                FROM service_reservations sr
                JOIN facilities f ON sr.facility_id = f.id
                WHERE sr.user_id = ?
                ORDER BY sr.service_start_date DESC";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    /**
     * Get service reservation details
     */
    public function getServiceReservationDetails($service_id) {
        $sql = "SELECT sr.*, f.name as facility_name, f.description as facility_description,
                f.icon, u.email, u.first_name, u.last_name, u.phone
                FROM service_reservations sr
                JOIN facilities f ON sr.facility_id = f.id
                JOIN users u ON sr.user_id = u.id
                WHERE sr.id = ?";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $service_id);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc();
    }

    /**
     * Get dashboard statistics for a user
     */
    public function getUserDashboardStats($user_id) {
        $stats = [];

        // Total reservations
        $sql = "SELECT COUNT(*) as count FROM reservations WHERE user_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        $stats['total_reservations'] = $stmt->get_result()->fetch_assoc()['count'];

        // Active reservations
        $sql = "SELECT COUNT(*) as count FROM reservations WHERE user_id = ? 
                AND status IN ('pending', 'confirmed', 'checked_in')";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        $stats['active_reservations'] = $stmt->get_result()->fetch_assoc()['count'];

        // Total services
        $sql = "SELECT COUNT(*) as count FROM service_reservations WHERE user_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        $stats['total_services'] = $stmt->get_result()->fetch_assoc()['count'];

        // Total spent
        $sql = "SELECT SUM(total_price) as total FROM reservations WHERE user_id = ? 
                AND status != 'cancelled'";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('i', $user_id);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stats['total_spent'] = $result['total'] ?? 0;

        return $stats;
    }
}

// Initialize helper
$dbHelper = new DBHelper($conn);
?>
