-- Migration: Shopify Integration Setup
-- Description: Creates tables for Shopify OAuth, GDPR compliance, and data caching
-- Date: 2025-10-30
-- Related Issue: #6

-- ============================================================================
-- STEP 1: Create OAuth States Table (if not exists)
-- ============================================================================

-- Table to store OAuth state parameters for CSRF protection
CREATE TABLE IF NOT EXISTS oauth_states (
  state TEXT PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  platform TEXT NOT NULL CHECK (platform IN ('shopify', 'woocommerce', 'shoprenter')),
  shopname TEXT,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for cleaning up expired states
CREATE INDEX IF NOT EXISTS idx_oauth_states_expires_at
  ON oauth_states(expires_at);

-- Function to clean up expired OAuth states
CREATE OR REPLACE FUNCTION cleanup_expired_oauth_states()
RETURNS void AS $$
BEGIN
  DELETE FROM oauth_states WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- STEP 2: Create GDPR Requests Table
-- ============================================================================

-- Table to track GDPR compliance requests from Shopify
CREATE TABLE IF NOT EXISTS gdpr_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID REFERENCES stores(id) ON DELETE CASCADE,
  request_type TEXT NOT NULL CHECK (request_type IN ('data_request', 'customer_redact', 'shop_redact')),
  customer_id TEXT,
  shop_domain TEXT NOT NULL,
  request_payload JSONB NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
  processed_at TIMESTAMPTZ,
  error_message TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for querying GDPR requests by shop and status
CREATE INDEX IF NOT EXISTS idx_gdpr_requests_shop_status
  ON gdpr_requests(shop_domain, status, created_at DESC);

-- Index for querying by store
CREATE INDEX IF NOT EXISTS idx_gdpr_requests_store_id
  ON gdpr_requests(store_id, created_at DESC);

-- Enable RLS on GDPR requests
ALTER TABLE gdpr_requests ENABLE ROW LEVEL SECURITY;

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

-- ============================================================================
-- STEP 3: Create Shopify Products Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS shopify_products_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  shopify_product_id TEXT NOT NULL,
  title TEXT NOT NULL,
  handle TEXT,
  vendor TEXT,
  product_type TEXT,
  status TEXT,
  price DECIMAL(10, 2),
  compare_at_price DECIMAL(10, 2),
  currency TEXT,
  sku TEXT,
  inventory_quantity INTEGER,
  description TEXT,
  images JSONB,
  variants JSONB,
  options JSONB,
  tags TEXT[],
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, shopify_product_id)
);

-- Indexes for product queries
CREATE INDEX IF NOT EXISTS idx_shopify_products_store_id
  ON shopify_products_cache(store_id, last_synced_at DESC);

CREATE INDEX IF NOT EXISTS idx_shopify_products_sku
  ON shopify_products_cache(store_id, sku)
  WHERE sku IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_shopify_products_status
  ON shopify_products_cache(store_id, status)
  WHERE status IS NOT NULL;

-- Enable RLS on products cache
ALTER TABLE shopify_products_cache ENABLE ROW LEVEL SECURITY;

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

-- ============================================================================
-- STEP 4: Create Shopify Orders Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS shopify_orders_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  shopify_order_id TEXT NOT NULL,
  order_number TEXT NOT NULL,
  name TEXT, -- Order name like "#1001"
  email TEXT,
  phone TEXT,
  financial_status TEXT,
  fulfillment_status TEXT,
  total_price DECIMAL(10, 2),
  subtotal_price DECIMAL(10, 2),
  total_tax DECIMAL(10, 2),
  currency TEXT,
  customer_name TEXT,
  customer_email TEXT,
  line_items JSONB,
  billing_address JSONB,
  shipping_address JSONB,
  note TEXT,
  tags TEXT[],
  order_created_at TIMESTAMPTZ,
  order_updated_at TIMESTAMPTZ,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, shopify_order_id)
);

-- Indexes for order queries
CREATE INDEX IF NOT EXISTS idx_shopify_orders_store_id
  ON shopify_orders_cache(store_id, order_created_at DESC);

CREATE INDEX IF NOT EXISTS idx_shopify_orders_order_number
  ON shopify_orders_cache(store_id, order_number);

CREATE INDEX IF NOT EXISTS idx_shopify_orders_customer_email
  ON shopify_orders_cache(store_id, customer_email)
  WHERE customer_email IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_shopify_orders_financial_status
  ON shopify_orders_cache(store_id, financial_status)
  WHERE financial_status IS NOT NULL;

-- Enable RLS on orders cache
ALTER TABLE shopify_orders_cache ENABLE ROW LEVEL SECURITY;

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

-- ============================================================================
-- STEP 5: Create Shopify Customers Cache Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS shopify_customers_cache (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  shopify_customer_id TEXT NOT NULL,
  email TEXT NOT NULL,
  first_name TEXT,
  last_name TEXT,
  phone TEXT,
  accepts_marketing BOOLEAN,
  orders_count INTEGER DEFAULT 0,
  total_spent DECIMAL(10, 2),
  currency TEXT,
  state TEXT, -- Customer state (enabled, disabled, declined, invited)
  addresses JSONB,
  default_address JSONB,
  tags TEXT[],
  note TEXT,
  customer_created_at TIMESTAMPTZ,
  customer_updated_at TIMESTAMPTZ,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, shopify_customer_id)
);

-- Indexes for customer queries
CREATE INDEX IF NOT EXISTS idx_shopify_customers_store_id
  ON shopify_customers_cache(store_id, last_synced_at DESC);

CREATE INDEX IF NOT EXISTS idx_shopify_customers_email
  ON shopify_customers_cache(store_id, email);

CREATE INDEX IF NOT EXISTS idx_shopify_customers_phone
  ON shopify_customers_cache(store_id, phone)
  WHERE phone IS NOT NULL;

-- Enable RLS on customers cache
ALTER TABLE shopify_customers_cache ENABLE ROW LEVEL SECURITY;

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

-- ============================================================================
-- STEP 6: Create Shopify Webhooks Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS shopify_webhooks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  shopify_webhook_id TEXT NOT NULL,
  topic TEXT NOT NULL, -- e.g., 'orders/create', 'products/update'
  address TEXT NOT NULL, -- Webhook callback URL
  format TEXT DEFAULT 'json',
  is_active BOOLEAN DEFAULT true,
  last_received_at TIMESTAMPTZ,
  total_received INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id, shopify_webhook_id)
);

-- Index for webhook queries
CREATE INDEX IF NOT EXISTS idx_shopify_webhooks_store_id
  ON shopify_webhooks(store_id, is_active);

-- Enable RLS on webhooks
ALTER TABLE shopify_webhooks ENABLE ROW LEVEL SECURITY;

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

-- ============================================================================
-- STEP 7: Extend store_sync_config for Shopify (if table exists)
-- ============================================================================

-- Insert default sync config for all existing Shopify stores
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'store_sync_config') THEN
    INSERT INTO store_sync_config (store_id, enabled, sync_frequency)
    SELECT
      id,
      true,
      'hourly'
    FROM stores
    WHERE platform_name = 'shopify'
    ON CONFLICT (store_id) DO NOTHING;

    RAISE NOTICE 'Default sync configuration created for existing Shopify stores';
  END IF;
END $$;

-- ============================================================================
-- STEP 8: Create Helper Functions
-- ============================================================================

-- Function to mark GDPR request as processed
CREATE OR REPLACE FUNCTION complete_gdpr_request(
  p_request_id UUID,
  p_success BOOLEAN DEFAULT true,
  p_error_message TEXT DEFAULT NULL
)
RETURNS void AS $$
BEGIN
  UPDATE gdpr_requests
  SET
    status = CASE WHEN p_success THEN 'completed' ELSE 'failed' END,
    processed_at = NOW(),
    error_message = p_error_message,
    updated_at = NOW()
  WHERE id = p_request_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get store by Shopify domain
CREATE OR REPLACE FUNCTION get_store_by_shopify_domain(p_shop_domain TEXT)
RETURNS TABLE (
  store_id UUID,
  user_id UUID,
  store_name TEXT,
  api_key TEXT,
  scopes TEXT[]
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.id,
    s.user_id,
    s.store_name,
    s.api_key,
    s.scopes
  FROM stores s
  WHERE s.platform_name = 'shopify'
    AND s.store_url = p_shop_domain;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- STEP 9: Create Scheduled Sync Function for Shopify (similar to ShopRenter)
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_shopify_scheduled_sync()
RETURNS void AS $$
DECLARE
  response_data jsonb;
  internal_secret TEXT;
  supabase_url TEXT;
BEGIN
  -- Get environment variables
  internal_secret := current_setting('app.internal_sync_secret', true);
  supabase_url := current_setting('app.supabase_url', true);

  IF internal_secret IS NULL OR supabase_url IS NULL THEN
    RAISE WARNING 'Missing required settings for Shopify scheduled sync';
    RETURN;
  END IF;

  -- Make HTTP request to the scheduled sync Edge Function
  SELECT INTO response_data
    net.http_post(
      url := supabase_url || '/functions/v1/shopify-scheduled-sync',
      headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'x-internal-secret', internal_secret
      ),
      body := jsonb_build_object('source', 'pg_cron')
    );

  RAISE NOTICE 'Shopify scheduled sync triggered: %', response_data;

EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Error triggering Shopify scheduled sync: %', SQLERRM;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

DO $$
BEGIN
  RAISE NOTICE 'Shopify integration migration completed successfully';
  RAISE NOTICE 'Created tables:';
  RAISE NOTICE '  - oauth_states (OAuth flow state management)';
  RAISE NOTICE '  - gdpr_requests (GDPR compliance tracking)';
  RAISE NOTICE '  - shopify_products_cache (Product data cache)';
  RAISE NOTICE '  - shopify_orders_cache (Order data cache)';
  RAISE NOTICE '  - shopify_customers_cache (Customer data cache)';
  RAISE NOTICE '  - shopify_webhooks (Webhook registration tracking)';
  RAISE NOTICE '';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Deploy oauth-shopify Edge Function';
  RAISE NOTICE '2. Deploy webhooks-shopify Edge Function';
  RAISE NOTICE '3. Deploy shopify-sync Edge Function';
  RAISE NOTICE '4. Set SHOPIFY_API_KEY in Edge Functions environment';
  RAISE NOTICE '5. Set SHOPIFY_API_SECRET in Edge Functions environment';
  RAISE NOTICE '6. Update frontend with Shopify connect button';
END $$;
