# 📝 SQL Commands Reference - Services Management

## Quick Command Reference

All SQL commands you need to manage services dynamically.

---

## ✅ BASIC COMMANDS

### View All Services
```sql
SELECT * FROM services ORDER BY id ASC;
```

### View Active Services Only
```sql
SELECT * FROM services WHERE status = 'active' ORDER BY id ASC;
```

### View Inactive Services
```sql
SELECT * FROM services WHERE status = 'inactive' ORDER BY id ASC;
```

### View Service by Name
```sql
SELECT * FROM services WHERE name = 'Personal Training';
```

### View Service by Price Range
```sql
SELECT * FROM services WHERE price BETWEEN 40 AND 60 ORDER BY price ASC;
```

---

## ➕ ADD NEW SERVICES

### Add Single Service
```sql
INSERT INTO services (name, description, price, duration, icon, status) 
VALUES (
    'Yoga Training',
    'Flow-based yoga combining strength, flexibility, and mindfulness for complete wellness.',
    40.00,
    '1 session',
    '🧘',
    'active'
);
```

### Add Multiple Services at Once
```sql
INSERT INTO services (name, description, price, duration, icon, status) VALUES
('Yoga Training', 'Flow-based yoga...', 40.00, '1 session', '🧘', 'active'),
('Pilates Training', 'Core strengthening...', 42.00, '1 session', '🤸', 'active'),
('Boxing Training', 'High-energy boxing...', 45.00, '1 session', '🥊', 'active'),
('Swimming Lessons', 'Water-based fitness...', 35.00, '1 session', '🏊', 'active');
```

### Template for New Service
```sql
INSERT INTO services (name, description, price, duration, icon, status) 
VALUES (
    'SERVICE_NAME_HERE',
    'DESCRIPTION_HERE',
    PRICE_HERE,
    'DURATION_HERE',
    'ICON_EMOJI_HERE',
    'active'
);
```

---

## ✏️ UPDATE/EDIT SERVICES

### Change Price
```sql
UPDATE services SET price = 75.00 WHERE name = 'Personal Training';
```

### Change Description
```sql
UPDATE services 
SET description = 'New description text here' 
WHERE name = 'Personal Training';
```

### Change Duration
```sql
UPDATE services SET duration = '2 sessions' WHERE id = 2;
```

### Change Icon
```sql
UPDATE services SET icon = '💎' WHERE name = 'Premium Training';
```

### Change Multiple Fields
```sql
UPDATE services 
SET 
    price = 85.00,
    description = 'Updated professional coaching with elite trainers',
    duration = '1.5 hours'
WHERE name = 'Personal Training';
```

### Update All Services' Price (% Increase)
```sql
-- Increase all prices by 10%
UPDATE services SET price = price * 1.10 WHERE status = 'active';
```

---

## 🔒 STATUS MANAGEMENT

### Hide Service (Deactivate)
```sql
UPDATE services SET status = 'inactive' WHERE name = 'Gym Training';
```

### Show Service (Activate)
```sql
UPDATE services SET status = 'active' WHERE name = 'Gym Training';
```

### Hide All Services Except One
```sql
UPDATE services SET status = 'inactive' WHERE name != 'Personal Training';
```

### Hide Services by Price
```sql
-- Hide services cheaper than $50
UPDATE services SET status = 'inactive' WHERE price < 50;
```

---

## 🗑️ DELETE SERVICES

### Delete Single Service
```sql
DELETE FROM services WHERE name = 'Service Name Here';
```

### Delete Service by ID
```sql
DELETE FROM services WHERE id = 5;
```

### Delete Inactive Services
```sql
DELETE FROM services WHERE status = 'inactive';
```

### **⚠️ WARNING - Delete ALL Services**
```sql
-- This will delete ALL services - be careful!
DELETE FROM services;
```

---

## 📊 STATISTICS & REPORTS

### Count Total Services
```sql
SELECT COUNT(*) as total_services FROM services;
```

### Count Active Services
```sql
SELECT COUNT(*) as active_count FROM services WHERE status = 'active';
```

### Average Price
```sql
SELECT AVG(price) as average_price FROM services WHERE status = 'active';
```

### Highest Price Service
```sql
SELECT * FROM services ORDER BY price DESC LIMIT 1;
```

### Lowest Price Service
```sql
SELECT * FROM services ORDER BY price ASC LIMIT 1;
```

### Services Grouped by Duration
```sql
SELECT duration, COUNT(*) as count, AVG(price) as avg_price 
FROM services 
WHERE status = 'active'
GROUP BY duration;
```

### Total Revenue Potential (Sum of Prices)
```sql
SELECT SUM(price) as total_revenue FROM services WHERE status = 'active';
```

---

## 🔍 SEARCH & FILTER

### Find Services by Price Range
```sql
SELECT * FROM services 
WHERE status = 'active' 
AND price BETWEEN 40 AND 60 
ORDER BY price ASC;
```

### Find Services with "Training" in Name
```sql
SELECT * FROM services 
WHERE name LIKE '%Training%' 
AND status = 'active';
```

### Find Services Created Today
```sql
SELECT * FROM services 
WHERE DATE(created_at) = CURDATE();
```

### Find Services Updated in Last 7 Days
```sql
SELECT * FROM services 
WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) 
ORDER BY updated_at DESC;
```

### Find Services by Icon
```sql
SELECT * FROM services WHERE icon = '🏋️';
```

---

## 🆔 ID MANAGEMENT

### View Service IDs
```sql
SELECT id, name, price FROM services ORDER BY id ASC;
```

### Get Next Available ID
```sql
SELECT MAX(id) + 1 as next_id FROM services;
```

### Reset ID Sequence (if needed)
```sql
-- Be careful with this!
ALTER TABLE services AUTO_INCREMENT = 1;
```

---

## 📅 TIMESTAMP MANAGEMENT

### View Services Created Today
```sql
SELECT * FROM services 
WHERE DATE(created_at) = CURDATE()
ORDER BY created_at DESC;
```

### View Recently Updated Services
```sql
SELECT * FROM services 
ORDER BY updated_at DESC 
LIMIT 5;
```

### View Services by Creation Month
```sql
SELECT 
    DATE_FORMAT(created_at, '%Y-%m') as month,
    COUNT(*) as count
FROM services
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;
```

---

## 🔄 BULK OPERATIONS

### Duplicate a Service
```sql
-- Create copy of Gym Training with different name
INSERT INTO services (name, description, price, duration, icon, status)
SELECT 
    CONCAT(name, ' - Copy'),
    description,
    price,
    duration,
    icon,
    'inactive'
FROM services
WHERE name = 'Gym Training';
```

### Copy Service but Change Price
```sql
-- Duplicate Personal Training at premium price
INSERT INTO services (name, description, price, duration, icon, status)
SELECT 
    'Premium Personal Training',
    CONCAT(description, ' [Premium]'),
    price * 1.5,  -- 50% more expensive
    duration,
    icon,
    'inactive'
FROM services
WHERE name = 'Personal Training';
```

### Clear and Reload Sample Data
```sql
-- Delete all current services
DELETE FROM services;

-- Re-insert sample data (page will auto-populate when visited)
-- Or use the insert statements in the PHP code
```

---

## ⚠️ DANGER ZONE (Use Carefully!)

### Reset All Services to Default Prices
```sql
UPDATE services SET price = 
    CASE 
        WHEN name = 'Gym Training' THEN 49.00
        WHEN name = 'Personal Training' THEN 60.00
        WHEN name = 'Strength Training' THEN 55.00
        WHEN name = 'Weight Training' THEN 50.00
        WHEN name = 'Functional Training' THEN 45.00
        WHEN name = 'CrossFit Training' THEN 65.00
    END
WHERE name IN ('Gym Training', 'Personal Training', 'Strength Training', 'Weight Training', 'Functional Training', 'CrossFit Training');
```

### Activate All Services
```sql
UPDATE services SET status = 'active';
```

### Deactivate All Services (Except One)
```sql
UPDATE services SET status = 'inactive' WHERE name != 'Personal Training';
```

### Delete All Services (IRREVERSIBLE!)
```sql
DELETE FROM services;
-- WARNING: This cannot be undone! Always backup first.
```

---

## 🔧 DATABASE MAINTENANCE

### Check Services Table Status
```sql
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'powerzone_db' AND TABLE_NAME = 'services';
```

### Optimize Services Table
```sql
OPTIMIZE TABLE services;
```

### Check for Duplicate Services
```sql
SELECT name, COUNT(*) as count 
FROM services 
GROUP BY name 
HAVING count > 1;
```

### Remove Duplicate Services (Keep First One)
```sql
DELETE FROM services WHERE id NOT IN (
    SELECT MIN(id) FROM services GROUP BY name
);
```

### Rename a Service
```sql
UPDATE services SET name = 'New Service Name' WHERE name = 'Old Service Name';
```

---

## 💡 PRACTICAL EXAMPLES

### Scenario 1: Summer Promotion (20% Discount)
```sql
-- Lower prices for summer
UPDATE services SET price = price * 0.80 WHERE status = 'active';
```

### Scenario 2: Launch New Premium Tier
```sql
-- Copy Personal Training at premium price
INSERT INTO services (name, description, price, duration, icon, status)
SELECT 
    'Elite Personal Training',
    CONCAT(description, ' - Elite tier with additional benefits'),
    price * 2,
    'Elite package',
    '👑',
    'active'
FROM services WHERE name = 'Personal Training';
```

### Scenario 3: Seasonal Update
```sql
-- Update winter sports training
UPDATE services 
SET description = 'Winter fitness training designed for cold weather conditioning'
WHERE name = 'Strength Training';
```

### Scenario 4: Limited Time Offer
```sql
-- Deactivate all except special offer
UPDATE services SET status = 'inactive' WHERE name NOT IN ('Special Winter Package', 'Premium Training');
```

### Scenario 5: Backup Before Changes
```sql
-- View all services before making changes (safety measure)
SELECT * FROM services WHERE status = 'active' ORDER BY id ASC;
-- Note the values before updating!
```

---

## 🎯 Best Practices

### Before Making Changes
```sql
-- Always check current state first
SELECT * FROM services WHERE id = 1;

-- Then make your update
UPDATE services SET price = 99.00 WHERE id = 1;

-- Verify the change
SELECT * FROM services WHERE id = 1;
```

### Keep Backups
```sql
-- Export data before major changes
-- Use MySQL backup tools or phpMyAdmin
```

### Use Transactions (Advanced)
```sql
START TRANSACTION;
UPDATE services SET price = 100.00 WHERE id = 1;
-- Check result, then either:
COMMIT;    -- Save changes
-- or
ROLLBACK;  -- Undo changes
```

### Document Your Changes
```sql
-- Always comment why you're making changes
-- Change price from 60 to 75 due to increased demand (Jan 2026)
UPDATE services SET price = 75.00 WHERE name = 'Personal Training';
```

---

## 🚀 Quick Copy-Paste Commands

### Get All Data
```sql
SELECT * FROM services;
```

### Backup (View JSON format)
```sql
SELECT JSON_OBJECT('services', JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'price', price))) FROM services;
```

### Reset to Sample Data
```sql
DELETE FROM services;
-- Visit services.php to auto-populate
```

---

## 📞 Need Help?

- **Full Documentation**: DATABASE_INTEGRATION_GUIDE.md
- **Quick Reference**: SERVICES_DB_QUICK_REFERENCE.txt  
- **Testing Guide**: TESTING_DATABASE_INTEGRATION.md
- **Admin Panel**: http://localhost/fitness/admin-dashboard.php

---

**Safe Querying! Always backup before major changes.**
