-- Migration Script: Add Enhanced Location Fields
-- Run this if you already have the database installed
-- This adds the new fields for opening hours, delivery windows, GPS, and what3words

USE scubatricky_risk;

-- Add new columns to locations table
ALTER TABLE locations 
ADD COLUMN opening_hours JSON AFTER notes,
ADD COLUMN delivery_windows JSON AFTER opening_hours,
ADD COLUMN latitude DECIMAL(10, 8) AFTER delivery_windows,
ADD COLUMN longitude DECIMAL(11, 8) AFTER latitude,
ADD COLUMN what3words VARCHAR(100) AFTER longitude,
ADD COLUMN google_maps_place_id VARCHAR(255) AFTER what3words,
ADD COLUMN google_maps_url TEXT AFTER google_maps_place_id;

-- Add indexes for better performance
CREATE INDEX idx_coordinates ON locations(latitude, longitude);
CREATE INDEX idx_what3words ON locations(what3words);

-- Verify changes
DESCRIBE locations;

-- Success message
SELECT 'Migration completed successfully! New location fields added.' AS status;
