-- Migration: ShopRenter Orders Cache Table
-- Description: Creates table for caching ShopRenter orders data
-- Date: 2025-10-31
-- Related Issue: #31

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

CREATE TABLE IF NOT EXISTS shoprenter_orders_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  shoprenter_order_id TEXT NOT NULL,
  order_number TEXT,
  status TEXT,
  total DECIMAL(10, 2),
  currency TEXT,
  customer_name TEXT,
  customer_email TEXT,
  customer_phone TEXT,  -- Critical for calling customers
  line_items JSONB,
  billing_address JSONB,
  shipping_address JSONB,
  order_created_at TIMESTAMPTZ,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, shoprenter_order_id)
);

-- ============================================================================
-- STEP 2: Create Indexes for Efficient Queries
-- ============================================================================

-- Index for querying orders by store
CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_store_id
  ON shoprenter_orders_cache(store_id, order_created_at DESC);

-- Index for querying orders by order number
CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_order_number
  ON shoprenter_orders_cache(store_id, order_number);

-- Index for querying orders by status
CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_status
  ON shoprenter_orders_cache(store_id, status)
  WHERE status IS NOT NULL;

-- Index for querying orders by customer email
CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_customer_email
  ON shoprenter_orders_cache(store_id, customer_email)
  WHERE customer_email IS NOT NULL;

-- Index for querying orders by customer phone
CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_customer_phone
  ON shoprenter_orders_cache(store_id, customer_phone)
  WHERE customer_phone IS NOT NULL;

-- Index for sorting by last sync
CREATE INDEX IF NOT EXISTS idx_shoprenter_orders_last_synced
  ON shoprenter_orders_cache(store_id, last_synced_at DESC);

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

ALTER TABLE shoprenter_orders_cache ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- STEP 4: Create RLS Policies
-- ============================================================================

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

-- Policy: Service role can manage all orders
CREATE POLICY "Service role can manage ShopRenter orders"
  ON shoprenter_orders_cache FOR ALL
  TO service_role
  USING (true);

-- ============================================================================
-- STEP 5: Create Helper Function to Get ShopRenter Sync Status
-- ============================================================================

CREATE OR REPLACE FUNCTION get_shoprenter_sync_status(p_store_id UUID)
RETURNS TABLE (
  last_sync_at TIMESTAMPTZ,
  sync_status TEXT,
  products_count BIGINT,
  orders_count BIGINT,
  customers_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    GREATEST(
      (SELECT MAX(last_synced_at) FROM shoprenter_products_cache WHERE store_id = p_store_id),
      (SELECT MAX(last_synced_at) FROM shoprenter_orders_cache WHERE store_id = p_store_id),
      (SELECT MAX(last_synced_at) FROM shoprenter_customers_cache WHERE store_id = p_store_id)
    ) as last_sync_at,
    'idle'::TEXT as sync_status,
    (SELECT COUNT(*) FROM shoprenter_products_cache WHERE store_id = p_store_id) as products_count,
    (SELECT COUNT(*) FROM shoprenter_orders_cache WHERE store_id = p_store_id) as orders_count,
    (SELECT COUNT(*) FROM shoprenter_customers_cache WHERE store_id = p_store_id) as customers_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

DO $$
BEGIN
  RAISE NOTICE 'ShopRenter orders cache migration completed successfully';
  RAISE NOTICE 'Created table: shoprenter_orders_cache';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Update shoprenter-sync Edge Function to include order syncing';
  RAISE NOTICE '2. Update shoprenter-scheduled-sync Edge Function to include orders';
  RAISE NOTICE '3. Test sync with connected ShopRenter stores';
END $$;
