-- Migration: ShopRenter Customers Cache Table
-- Description: Creates table for caching ShopRenter customer data including phone numbers
-- Date: 2025-10-31
-- Related Issue: #32

-- ============================================================================
-- STEP 1: Create ShopRenter Customers Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS shoprenter_customers_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  shoprenter_customer_id TEXT NOT NULL,
  email TEXT NOT NULL,
  first_name TEXT,
  last_name TEXT,
  phone TEXT, -- Critical for calling customers
  billing_address JSONB,
  shipping_address JSONB,
  orders_count INTEGER DEFAULT 0,
  total_spent DECIMAL(10, 2),
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, shoprenter_customer_id)
);

-- ============================================================================
-- STEP 2: Create Indexes for Fast Lookups
-- ============================================================================

-- Index for queries by store and last sync time
CREATE INDEX IF NOT EXISTS idx_shoprenter_customers_store_id
  ON shoprenter_customers_cache(store_id, last_synced_at DESC);

-- Index for email lookups
CREATE INDEX IF NOT EXISTS idx_shoprenter_customers_email
  ON shoprenter_customers_cache(store_id, email);

-- Index for phone number lookups (only non-null values)
CREATE INDEX IF NOT EXISTS idx_shoprenter_customers_phone
  ON shoprenter_customers_cache(store_id, phone)
  WHERE phone IS NOT NULL;

-- Index for customer ID lookups
CREATE INDEX IF NOT EXISTS idx_shoprenter_customers_customer_id
  ON shoprenter_customers_cache(store_id, shoprenter_customer_id);

-- ============================================================================
-- STEP 3: Enable Row Level Security
-- ============================================================================

ALTER TABLE shoprenter_customers_cache ENABLE ROW LEVEL SECURITY;

-- Policy: Users can view their own customers
CREATE POLICY "Users can view their shoprenter customers"
  ON shoprenter_customers_cache FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = shoprenter_customers_cache.store_id
      AND s.user_id = auth.uid()
    )
  );

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

-- Log migration completion
DO $$
BEGIN
  RAISE NOTICE 'ShopRenter customers cache migration completed successfully';
  RAISE NOTICE 'Created tables:';
  RAISE NOTICE '  - shoprenter_customers_cache (Customer data with phone numbers)';
  RAISE NOTICE '';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Update shoprenter-sync Edge Function to sync customers';
  RAISE NOTICE '2. Update shoprenter-scheduled-sync Edge Function to include customers';
  RAISE NOTICE '3. Test customer sync with existing ShopRenter stores';
END $$;
