-- Migration Script: Add Custom Fields and Street View URL
-- Run this to add custom fields functionality to existing installations

USE scubatricky_risk;

-- Add new columns to locations table
ALTER TABLE locations 
ADD COLUMN google_street_view_url TEXT AFTER google_maps_url,
ADD COLUMN custom_fields JSON AFTER google_street_view_url;

-- Create custom field definitions table
CREATE TABLE IF NOT EXISTS custom_field_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field_name VARCHAR(100) NOT NULL,
    field_label VARCHAR(200) NOT NULL,
    field_type ENUM('text', 'textarea', 'number', 'date', 'time', 'select', 'checkbox', 'url', 'email', 'tel') NOT NULL,
    field_options TEXT, -- JSON array for select options
    is_required BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    help_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_field_name (field_name),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB;

-- Insert default custom fields
INSERT INTO custom_field_definitions (field_name, field_label, field_type, help_text, display_order) VALUES
('loading_bay_number', 'Loading Bay Number', 'text', 'Specific bay number or identifier', 1),
('height_restriction', 'Height Restriction (meters)', 'number', 'Maximum vehicle height allowed', 2),
('weight_restriction', 'Weight Restriction (tonnes)', 'number', 'Maximum vehicle weight allowed', 3),
('access_code', 'Access/Security Code', 'text', 'Gate or door entry code', 4),
('parking_spaces', 'Available Parking Spaces', 'number', 'Number of parking/waiting spaces', 5),
('site_manager_contact', 'Site Manager Contact', 'tel', 'Direct phone number for site manager', 6),
('alternative_access', 'Alternative Access Route', 'textarea', 'Backup route if primary is blocked', 7),
('unloading_equipment', 'Unloading Equipment Available', 'text', 'e.g., Forklift, Pallet jack, Tail lift required', 8);

-- Verify changes
DESCRIBE locations;
DESCRIBE custom_field_definitions;

SELECT 'Migration completed successfully! Custom fields added.' AS status;
