-- Migration: Add phone number fields to WooCommerce cache tables
-- Description: Adds phone fields to customers and orders cache for contact information
-- Date: 2025-10-31
-- Related Issue: #30

-- ============================================================================
-- STEP 1: Add phone field to woocommerce_customers_cache
-- ============================================================================

ALTER TABLE woocommerce_customers_cache
ADD COLUMN IF NOT EXISTS phone TEXT;

-- Add index for phone number lookups
CREATE INDEX IF NOT EXISTS idx_wc_customers_phone
  ON woocommerce_customers_cache(store_id, phone)
  WHERE phone IS NOT NULL;

-- ============================================================================
-- STEP 2: Add customer_phone field to woocommerce_orders_cache
-- ============================================================================

ALTER TABLE woocommerce_orders_cache
ADD COLUMN IF NOT EXISTS customer_phone TEXT;

-- Add index for phone number lookups
CREATE INDEX IF NOT EXISTS idx_wc_orders_customer_phone
  ON woocommerce_orders_cache(store_id, customer_phone)
  WHERE customer_phone IS NOT NULL;

-- ============================================================================
-- Migration Complete
-- ============================================================================

DO $$
BEGIN
  RAISE NOTICE 'WooCommerce phone fields migration completed successfully';
  RAISE NOTICE 'Added columns:';
  RAISE NOTICE '  - woocommerce_customers_cache.phone';
  RAISE NOTICE '  - woocommerce_orders_cache.customer_phone';
  RAISE NOTICE '';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Deploy updated woocommerce-sync Edge Function';
  RAISE NOTICE '2. Re-sync WooCommerce stores to populate phone numbers';
END $$;
