-- ============================================
-- YOLO CHARTERS DATABASE UPDATES
-- Date: 2025-11-21
-- Description: Add heads column and update refit years
-- ============================================

-- 1. Add 'heads' column to yachts table (if it doesn't exist)
ALTER TABLE yachts 
ADD COLUMN IF NOT EXISTS heads INT DEFAULT 0 AFTER cabins;

-- 2. Update YOLO boats with correct heads data from Booking Manager API
UPDATE yachts SET heads = 4 WHERE name = 'Lemon' AND company_id = 7850;
UPDATE yachts SET heads = 3 WHERE name = 'Aquilo' AND company_id = 7850;
UPDATE yachts SET heads = 4 WHERE name = 'Strawberry' AND company_id = 7850;

-- 3. Update Lemon with refit year 2025
UPDATE yachts 
SET year_refit = 2025 
WHERE name = 'Lemon' AND company_id = 7850;

-- 4. Update Strawberry with refit year 2026
UPDATE yachts 
SET year_refit = 2026 
WHERE name = 'Strawberry' AND company_id = 7850;

-- ============================================
-- VERIFICATION QUERIES
-- Run these to verify the changes
-- ============================================

-- Check YOLO boats with new heads column and refit years
SELECT 
    name, 
    cabins, 
    heads, 
    year_built, 
    year_refit,
    length_ft
FROM yachts 
WHERE company_id = 7850
ORDER BY name;

-- Expected results:
-- Aquilo:     4 cabins, 3 heads, built 2014
-- Lemon:      4 cabins, 4 heads, built 2013, refit 2025
-- Strawberry: 4 cabins, 4 heads, built 2008, refit 2026

-- ============================================
-- END OF DATABASE UPDATES
-- ============================================