<?php

include_once 'config.php';

class bookings_model
{
    public $link;

    public function __construct()
    {
        $db_connection = new dbConnection();
        $this->link = $db_connection->connect();
    }

    public function countActiveBookings() {
        try {
            $sql = "SELECT COUNT(*) as total 
                    FROM spa_bookings 
                    WHERE booking_state IN (1,2) 
                    AND (
                        (DATE(NOW()) < DATE(appointment_date)) OR 
                        (DATE(NOW()) = DATE(appointment_date) AND TIME(NOW()) < TIME(time_slot))
                    )";
                    
            $stmt = $this->link->prepare($sql);
            $stmt->execute();
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row['total'];
        } catch (PDOException $e) {
            error_log("Booking count error: " . $e->getMessage());
            return 0;
        }
    }

    public function getActiveBookings() {
        try {
            $sql = "SELECT *,

                    spa_bookings.id as id
                    FROM 
                        spa_bookings 
                    LEFT JOIN
                        spa_services ON spa_bookings.service_code = spa_services.service_code
                    WHERE 
                        booking_state IN (1,2) 
                    AND (
                        (DATE(NOW()) < DATE(appointment_date)) OR 
                        (DATE(NOW()) = DATE(appointment_date) AND TIME(NOW()) < TIME(time_slot))
                    )
                    GROUP BY
                        spa_bookings.id";
                    
            $stmt = $this->link->prepare($sql);
            $stmt->execute();
            $bookings = $stmt->fetchAll(PDO::FETCH_ASSOC);

            return $bookings;

        } catch (PDOException $e) {
            error_log("Booking count error: " . $e->getMessage());
            return 0;
        }
    }


    public function getAllBookings() {
        $stmt = $this->link->prepare("SELECT * 
            FROM 
                spa_bookings 
            LEFT JOIN 
                spa_services ON spa_bookings.service_code = spa_services.service_code
            ORDER BY 
                appointment_date DESC");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateBookingState($booking_id, $state) {
        try {
            // Prepare the SQL query to update the booking state
            $sql = "UPDATE spa_bookings SET booking_state = :state WHERE id = :booking_id";
            
            // Prepare the statement
            $stmt = $this->link->prepare($sql);
            
            // Bind the parameters to prevent SQL injection
            $stmt->bindParam(':state', $state, PDO::PARAM_INT);    // Booking state (0 = canceled, 2 = confirmed, etc.)
            $stmt->bindParam(':booking_id', $booking_id, PDO::PARAM_INT);  // Booking ID
            
            // Execute the query
            $stmt->execute();
            
            // Check if the query affected any rows (if no rows are updated, return false)
            if ($stmt->rowCount() > 0) {
                return true; // Update successful
            } else {
                return false; // No rows updated (e.g., invalid booking_id or state not changed)
            }
        } catch (PDOException $e) {
            // Log the error message for debugging purposes
            error_log("Error updating booking state: " . $e->getMessage());
            return false; // Return false in case of an error
        }
    }

    public function getMonthlyBookingStats() {
        try {
            $sql = "
                SELECT 
                    MONTH(appointment_date) AS month,
                    CASE 
                        WHEN booking_state = 0 THEN 'cancelled'
                        WHEN booking_state = 2 THEN 'successful'
                        WHEN booking_state = 1 AND appointment_date < CURDATE() THEN 'missed'
                        WHEN booking_state = 1 AND appointment_date >= CURDATE() THEN 'awaiting'
                    END AS status,
                    COUNT(*) AS total
                FROM 
                    spa_bookings
                GROUP BY 
                    MONTH(appointment_date), status
                ORDER BY 
                    month
            ";

            $stmt = $this->link->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Error fetching monthly booking stats: " . $e->getMessage());
            return [];
        }
    }





}