-- Migration: WooCommerce Data Synchronization
-- Description: Creates tables for caching WooCommerce products, orders, and customers
-- Date: 2025-01-30

-- ============================================================================
-- STEP 1: Create WooCommerce Products Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS woocommerce_products_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  wc_product_id TEXT NOT NULL,
  name TEXT,
  sku TEXT,
  price DECIMAL(10, 2),
  currency TEXT,
  description TEXT,
  short_description TEXT,
  stock_quantity INTEGER,
  stock_status TEXT,
  type TEXT, -- 'simple', 'variable', 'grouped', 'external'
  categories JSONB,
  images JSONB,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, wc_product_id)
);

-- Index for querying products by store
CREATE INDEX IF NOT EXISTS idx_wc_products_store_id
  ON woocommerce_products_cache(store_id);

-- Index for querying products by SKU
CREATE INDEX IF NOT EXISTS idx_wc_products_sku
  ON woocommerce_products_cache(store_id, sku);

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

-- ============================================================================
-- STEP 2: Create WooCommerce Orders Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS woocommerce_orders_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  wc_order_id TEXT NOT NULL,
  order_number TEXT,
  status TEXT,
  total DECIMAL(10, 2),
  currency TEXT,
  customer_name TEXT,
  customer_email TEXT,
  line_items JSONB,
  billing_address JSONB,
  shipping_address JSONB,
  created_at TIMESTAMPTZ,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, wc_order_id)
);

-- Index for querying orders by store
CREATE INDEX IF NOT EXISTS idx_wc_orders_store_id
  ON woocommerce_orders_cache(store_id);

-- Index for querying orders by status
CREATE INDEX IF NOT EXISTS idx_wc_orders_status
  ON woocommerce_orders_cache(store_id, status);

-- Index for querying orders by date
CREATE INDEX IF NOT EXISTS idx_wc_orders_created_at
  ON woocommerce_orders_cache(store_id, created_at DESC);

-- Index for querying orders by customer email
CREATE INDEX IF NOT EXISTS idx_wc_orders_customer_email
  ON woocommerce_orders_cache(store_id, customer_email);

-- ============================================================================
-- STEP 3: Create WooCommerce Customers Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS woocommerce_customers_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  wc_customer_id TEXT NOT NULL,
  email TEXT,
  first_name TEXT,
  last_name TEXT,
  username TEXT,
  billing_address JSONB,
  shipping_address JSONB,
  orders_count INTEGER,
  total_spent DECIMAL(10, 2),
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, wc_customer_id)
);

-- Index for querying customers by store
CREATE INDEX IF NOT EXISTS idx_wc_customers_store_id
  ON woocommerce_customers_cache(store_id);

-- Index for querying customers by email
CREATE INDEX IF NOT EXISTS idx_wc_customers_email
  ON woocommerce_customers_cache(store_id, email);

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

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

ALTER TABLE woocommerce_products_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE woocommerce_orders_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE woocommerce_customers_cache ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- STEP 5: Create RLS Policies for Products
-- ============================================================================

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

-- Policy: Service role can manage all products
CREATE POLICY "Service role can manage WooCommerce products"
  ON woocommerce_products_cache FOR ALL
  TO service_role
  USING (true);

-- ============================================================================
-- STEP 6: Create RLS Policies for Orders
-- ============================================================================

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

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

-- ============================================================================
-- STEP 7: Create RLS Policies for Customers
-- ============================================================================

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

-- Policy: Service role can manage all customers
CREATE POLICY "Service role can manage WooCommerce customers"
  ON woocommerce_customers_cache FOR ALL
  TO service_role
  USING (true);

-- ============================================================================
-- STEP 8: Create Helper Function to Get Sync Status
-- ============================================================================

CREATE OR REPLACE FUNCTION get_woocommerce_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 woocommerce_products_cache WHERE store_id = p_store_id),
      (SELECT MAX(last_synced_at) FROM woocommerce_orders_cache WHERE store_id = p_store_id),
      (SELECT MAX(last_synced_at) FROM woocommerce_customers_cache WHERE store_id = p_store_id)
    ) as last_sync_at,
    'idle'::TEXT as sync_status,
    (SELECT COUNT(*) FROM woocommerce_products_cache WHERE store_id = p_store_id) as products_count,
    (SELECT COUNT(*) FROM woocommerce_orders_cache WHERE store_id = p_store_id) as orders_count,
    (SELECT COUNT(*) FROM woocommerce_customers_cache WHERE store_id = p_store_id) as customers_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

DO $$
BEGIN
  RAISE NOTICE 'WooCommerce sync migration completed successfully';
  RAISE NOTICE 'Created tables:';
  RAISE NOTICE '  - woocommerce_products_cache';
  RAISE NOTICE '  - woocommerce_orders_cache';
  RAISE NOTICE '  - woocommerce_customers_cache';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Deploy woocommerce-sync Edge Function';
  RAISE NOTICE '2. Test sync with connected WooCommerce stores';
END $$;
