-- =====================================================
-- DOCTOR RATINGS TABLE - COMMON QUERIES
-- =====================================================

-- 1. Insert a new rating
INSERT INTO `tbl_doctor_ratings` (`doctor_id`, `client_id`, `rating`, `rating_text`, `date`) 
VALUES (1, 1, 5, 'Excellent doctor! Very knowledgeable and caring.', '2024-01-15');

-- 2. Get all ratings for a specific doctor
SELECT 
    r.rating_id,
    r.doctor_id,
    r.client_id,
    r.rating,
    r.rating_text,
    r.date,
    r.created_date,
    r.update_date
FROM `tbl_doctor_ratings` r
WHERE r.doctor_id = 1
ORDER BY r.created_date DESC;

-- 3. Get average rating for a doctor
SELECT 
    doctor_id,
    COUNT(*) as total_ratings,
    AVG(rating) as average_rating,
    MIN(rating) as min_rating,
    MAX(rating) as max_rating
FROM `tbl_doctor_ratings`
WHERE doctor_id = 1
GROUP BY doctor_id;

-- 4. Get all ratings by a specific client
SELECT 
    r.rating_id,
    r.doctor_id,
    r.client_id,
    r.rating,
    r.rating_text,
    r.date,
    r.created_date
FROM `tbl_doctor_ratings` r
WHERE r.client_id = 1
ORDER BY r.created_date DESC;

-- 5. Get top rated doctors (average rating >= 4.5)
SELECT 
    doctor_id,
    COUNT(*) as total_ratings,
    ROUND(AVG(rating), 2) as average_rating
FROM `tbl_doctor_ratings`
GROUP BY doctor_id
HAVING average_rating >= 4.5
ORDER BY average_rating DESC, total_ratings DESC;

-- 6. Get recent ratings (last 30 days)
SELECT 
    r.rating_id,
    r.doctor_id,
    r.client_id,
    r.rating,
    r.rating_text,
    r.date,
    r.created_date
FROM `tbl_doctor_ratings` r
WHERE r.created_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY r.created_date DESC;

-- 7. Update a rating
UPDATE `tbl_doctor_ratings` 
SET 
    rating = 4,
    rating_text = 'Updated review: Good doctor, very professional.',
    date = '2024-01-16'
WHERE rating_id = 1;

-- 8. Delete a rating
DELETE FROM `tbl_doctor_ratings` WHERE rating_id = 1;

-- 9. Get rating distribution for a doctor
SELECT 
    rating,
    COUNT(*) as count,
    ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM `tbl_doctor_ratings` WHERE doctor_id = 1)), 2) as percentage
FROM `tbl_doctor_ratings`
WHERE doctor_id = 1
GROUP BY rating
ORDER BY rating DESC;

-- 10. Get doctors with most ratings
SELECT 
    doctor_id,
    COUNT(*) as total_ratings,
    ROUND(AVG(rating), 2) as average_rating
FROM `tbl_doctor_ratings`
GROUP BY doctor_id
ORDER BY total_ratings DESC, average_rating DESC
LIMIT 10;

-- 11. Check if a client has already rated a doctor
SELECT COUNT(*) as has_rated
FROM `tbl_doctor_ratings`
WHERE doctor_id = 1 AND client_id = 1;

-- 12. Get ratings with pagination
SELECT 
    r.rating_id,
    r.doctor_id,
    r.client_id,
    r.rating,
    r.rating_text,
    r.date,
    r.created_date
FROM `tbl_doctor_ratings` r
WHERE r.doctor_id = 1
ORDER BY r.created_date DESC
LIMIT 10 OFFSET 0; -- First 10 records

-- 13. Get ratings by date range
SELECT 
    r.rating_id,
    r.doctor_id,
    r.client_id,
    r.rating,
    r.rating_text,
    r.date,
    r.created_date
FROM `tbl_doctor_ratings` r
WHERE r.date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY r.date DESC;

-- 14. Get average rating by month for a doctor
SELECT 
    YEAR(date) as year,
    MONTH(date) as month,
    COUNT(*) as total_ratings,
    ROUND(AVG(rating), 2) as average_rating
FROM `tbl_doctor_ratings`
WHERE doctor_id = 1
GROUP BY YEAR(date), MONTH(date)
ORDER BY year DESC, month DESC;
