-- Migration: 1014_enhance_company_addresses.sql
-- Add enhanced address fields for billing and shipping addresses in customers table
-- Adds: address_line2, county, latitude, longitude for both billing and shipping

-- Billing address enhancements
ALTER TABLE customers 
ADD COLUMN IF NOT EXISTS billing_address_line2 VARCHAR(255) DEFAULT NULL AFTER billing_address,
ADD COLUMN IF NOT EXISTS billing_county VARCHAR(100) DEFAULT NULL AFTER billing_state,
ADD COLUMN IF NOT EXISTS billing_latitude DECIMAL(10, 8) DEFAULT NULL AFTER billing_country,
ADD COLUMN IF NOT EXISTS billing_longitude DECIMAL(11, 8) DEFAULT NULL AFTER billing_latitude;

-- Shipping address enhancements
ALTER TABLE customers
ADD COLUMN IF NOT EXISTS shipping_address_line2 VARCHAR(255) DEFAULT NULL AFTER shipping_address,
ADD COLUMN IF NOT EXISTS shipping_county VARCHAR(100) DEFAULT NULL AFTER shipping_state,
ADD COLUMN IF NOT EXISTS shipping_latitude DECIMAL(10, 8) DEFAULT NULL AFTER shipping_country,
ADD COLUMN IF NOT EXISTS shipping_longitude DECIMAL(11, 8) DEFAULT NULL AFTER shipping_latitude;

-- Add index on coordinates for potential geospatial queries
CREATE INDEX IF NOT EXISTS idx_billing_coords ON customers(billing_latitude, billing_longitude);
CREATE INDEX IF NOT EXISTS idx_shipping_coords ON customers(shipping_latitude, shipping_longitude);