-- =====================================================
-- 1. Get appointments for one customer
-- =====================================================

CREATE OR REPLACE FUNCTION get_customer_appointments(
    p_customer_id INT
)
    RETURNS TABLE (
                      appointment_id INT,
                      status TEXT,
                      created_at TIMESTAMP,
                      customer_id INT,
                      customer_name TEXT,
                      business_id INT,
                      business_name TEXT,
                      service_id INT,
                      service_name TEXT,
                      employee_id INT,
                      employee_name TEXT,
                      appointment_date DATE,
                      start_time TIME,
                      end_time TIME,
                      city TEXT
                  )
    LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT
            cav.appointment_id,
            cav.status::TEXT,
            cav.created_at,
            cav.customer_id,
            cav.customer_name::TEXT,
            cav.business_id,
            cav.business_name::TEXT,
            cav.service_id,
            cav.service_name::TEXT,
            cav.employee_id,
            cav.employee_name::TEXT,
            cav.date,
            cav.start_time,
            cav.end_time,
            cav.city::TEXT
        FROM customer_appointments_view cav
        WHERE cav.customer_id = p_customer_id;
END;
$$;



-- =====================================================
-- 2. Get available slots for business and date
-- =====================================================

CREATE OR REPLACE FUNCTION get_available_slots(
    p_business_id INT,
    p_date_from DATE,
    p_date_to DATE
)
    RETURNS TABLE (
                      slot_id INT,
                      slot_date DATE,
                      start_time TIME,
                      end_time TIME,
                      business_id INT,
                      business_name TEXT,
                      employee_id INT,
                      employee_name TEXT,
                      service_id INT,
                      service_name TEXT,
                      price NUMERIC,
                      duration_minutes INT
                  )
    LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT
            av.slot_id,
            av.date,
            av.start_time,
            av.end_time,
            av.business_id,
            av.business_name::TEXT,
            av.employee_id,
            av.employee_name::TEXT,
            av.service_id,
            av.service_name::TEXT,
            av.price,
            av.duration_minutes
        FROM available_slots av
        WHERE av.business_id = p_business_id
          AND av.date >= p_date_from
          AND av.date <= p_date_to;
END;
$$;



-- =====================================================
-- 3. Get review summary for one business
-- =====================================================

CREATE OR REPLACE FUNCTION get_business_review_summary(
    p_business_id INT
)
    RETURNS TABLE (
                      business_id INT,
                      business_name TEXT,
                      avg_rating NUMERIC,
                      total_reviews BIGINT
                  )
    LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT
            rs.business_id,
            rs.business_name::TEXT,
            rs.avg_rating,
            rs.total_reviews
        FROM review_summary rs
        WHERE rs.business_id = p_business_id;
END;
$$;