-- Migration: Product Exclusion Refactoring
-- Description: Refactors product exclusion system to use cache tables with category support
-- Date: 2025-11-20
-- Related Issue: #111
--
-- This migration:
-- 1. Adds 'excluded' column to all *_products_cache tables for individual product exclusions
-- 2. Creates 'excluded_categories' table for category-level exclusions
-- 3. Restructures product cache tables to normalize product data
-- 4. Migrates existing store_data_exclusions data to new structure
-- 5. Drops store_data_exclusions table
-- 6. Creates Shopify products cache table
-- 7. Adds helper functions and views

-- ============================================================================
-- STEP 1: Create excluded_categories table
-- ============================================================================

CREATE TABLE IF NOT EXISTS excluded_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  category_id TEXT NOT NULL,
  category_name TEXT NOT NULL,
  platform TEXT NOT NULL CHECK (platform IN ('shopify', 'woocommerce', 'shoprenter')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT unique_store_category UNIQUE(store_id, category_id, platform)
);

-- Indexes for optimal query performance
CREATE INDEX IF NOT EXISTS idx_excluded_categories_store_id ON excluded_categories(store_id);
CREATE INDEX IF NOT EXISTS idx_excluded_categories_platform ON excluded_categories(platform);

-- Enable RLS
ALTER TABLE excluded_categories ENABLE ROW LEVEL SECURITY;

-- RLS Policies
CREATE POLICY "Users can view their excluded categories"
  ON excluded_categories FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = excluded_categories.store_id
      AND s.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can insert their excluded categories"
  ON excluded_categories FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = excluded_categories.store_id
      AND s.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can update their excluded categories"
  ON excluded_categories FOR UPDATE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = excluded_categories.store_id
      AND s.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can delete their excluded categories"
  ON excluded_categories FOR DELETE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = excluded_categories.store_id
      AND s.user_id = auth.uid()
    )
  );

-- Trigger for updated_at
CREATE OR REPLACE FUNCTION update_excluded_categories_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_excluded_categories_updated_at
  BEFORE UPDATE ON excluded_categories
  FOR EACH ROW
  EXECUTE FUNCTION update_excluded_categories_updated_at();

-- ============================================================================
-- STEP 2: Update WooCommerce products cache table
-- ============================================================================

-- Add excluded column
ALTER TABLE woocommerce_products_cache ADD COLUMN IF NOT EXISTS excluded BOOLEAN DEFAULT FALSE;

-- Add index for excluded column
CREATE INDEX IF NOT EXISTS idx_woocommerce_products_excluded ON woocommerce_products_cache(store_id, excluded);

-- Ensure categories column exists (already there, but verify it's JSONB)
-- categories already exists as JSONB in the current schema

-- Add updated_at if not exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'woocommerce_products_cache'
    AND column_name = 'updated_at'
  ) THEN
    ALTER TABLE woocommerce_products_cache ADD COLUMN updated_at TIMESTAMPTZ DEFAULT NOW();
  END IF;
END $$;

-- ============================================================================
-- STEP 3: Update ShopRenter products cache table
-- ============================================================================

-- Add excluded column
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS excluded BOOLEAN DEFAULT FALSE;

-- Add inner_id column (ShopRenter has both id and innerId)
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS inner_id TEXT;

-- Add normalized columns (extract from product_data JSONB)
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS name TEXT;
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS sku TEXT;
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS price NUMERIC;
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS currency TEXT;
ALTER TABLE shoprenter_products_cache ADD COLUMN IF NOT EXISTS categories JSONB;

-- Backfill normalized columns from product_data
UPDATE shoprenter_products_cache
SET
  name = product_data->>'name',
  sku = product_data->>'sku',
  price = (product_data->>'price')::numeric,
  currency = COALESCE(product_data->>'currency', 'HUF'),
  inner_id = product_data->>'innerId',
  categories = COALESCE(product_data->'categories', '[]'::jsonb)
WHERE product_data IS NOT NULL;

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_shoprenter_products_excluded ON shoprenter_products_cache(store_id, excluded);
CREATE INDEX IF NOT EXISTS idx_shoprenter_products_name ON shoprenter_products_cache(store_id, name);

-- ============================================================================
-- STEP 4: 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,
  name TEXT,
  sku TEXT,
  price NUMERIC,
  currency TEXT DEFAULT 'USD',
  categories JSONB DEFAULT '[]'::jsonb,
  product_type TEXT,
  vendor TEXT,
  tags TEXT[],
  description TEXT,
  excluded BOOLEAN DEFAULT FALSE,
  raw_data JSONB,
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT unique_shopify_product UNIQUE(store_id, shopify_product_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_shopify_products_store_id ON shopify_products_cache(store_id);
CREATE INDEX IF NOT EXISTS idx_shopify_products_excluded ON shopify_products_cache(store_id, excluded);
CREATE INDEX IF NOT EXISTS idx_shopify_products_name ON shopify_products_cache(store_id, name);
CREATE INDEX IF NOT EXISTS idx_shopify_products_sku ON shopify_products_cache(store_id, sku);

-- Enable RLS
ALTER TABLE shopify_products_cache ENABLE ROW LEVEL SECURITY;

-- RLS Policies
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()
    )
  );

CREATE POLICY "Service role can manage shopify products"
  ON shopify_products_cache FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

-- ============================================================================
-- STEP 5: Migrate data from store_data_exclusions to cache tables
-- ============================================================================

-- Migrate WooCommerce products
UPDATE woocommerce_products_cache wc
SET excluded = NOT sde.is_enabled
FROM store_data_exclusions sde
WHERE sde.store_id = wc.store_id
  AND sde.data_type = 'product'
  AND sde.data_id = wc.wc_product_id;

-- Migrate ShopRenter products
UPDATE shoprenter_products_cache sr
SET excluded = NOT sde.is_enabled
FROM store_data_exclusions sde
WHERE sde.store_id = sr.store_id
  AND sde.data_type = 'product'
  AND sde.data_id = sr.shoprenter_product_id;

-- ============================================================================
-- STEP 6: Create helper functions
-- ============================================================================

-- Function to check if product is excluded by category
CREATE OR REPLACE FUNCTION is_product_excluded_by_category(
  p_store_id UUID,
  p_categories JSONB,
  p_platform TEXT
)
RETURNS BOOLEAN AS $$
DECLARE
  v_category JSONB;
  v_category_id TEXT;
BEGIN
  -- If categories is null or empty, return false
  IF p_categories IS NULL OR jsonb_array_length(p_categories) = 0 THEN
    RETURN FALSE;
  END IF;

  -- Check each category
  FOR v_category IN SELECT * FROM jsonb_array_elements(p_categories)
  LOOP
    -- Extract category ID (format varies by platform)
    v_category_id := COALESCE(
      v_category->>'id',
      v_category->>'category_id',
      v_category::text
    );

    -- Check if this category is excluded
    IF EXISTS (
      SELECT 1 FROM excluded_categories
      WHERE store_id = p_store_id
        AND category_id = v_category_id
        AND platform = p_platform
    ) THEN
      RETURN TRUE;
    END IF;
  END LOOP;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE;

-- Function to get all categories for a store (aggregated from products)
CREATE OR REPLACE FUNCTION get_store_categories(
  p_store_id UUID,
  p_platform TEXT
)
RETURNS TABLE (
  category_id TEXT,
  category_name TEXT,
  product_count INTEGER,
  is_excluded BOOLEAN
) AS $$
BEGIN
  IF p_platform = 'woocommerce' THEN
    RETURN QUERY
    SELECT DISTINCT
      cat->>'id' AS category_id,
      cat->>'name' AS category_name,
      COUNT(*)::INTEGER AS product_count,
      EXISTS(
        SELECT 1 FROM excluded_categories ec
        WHERE ec.store_id = p_store_id
          AND ec.category_id = cat->>'id'
          AND ec.platform = p_platform
      ) AS is_excluded
    FROM woocommerce_products_cache wc,
         jsonb_array_elements(wc.categories) AS cat
    WHERE wc.store_id = p_store_id
      AND wc.categories IS NOT NULL
    GROUP BY cat->>'id', cat->>'name';

  ELSIF p_platform = 'shopify' THEN
    RETURN QUERY
    SELECT DISTINCT
      cat->>'id' AS category_id,
      cat->>'name' AS category_name,
      COUNT(*)::INTEGER AS product_count,
      EXISTS(
        SELECT 1 FROM excluded_categories ec
        WHERE ec.store_id = p_store_id
          AND ec.category_id = cat->>'id'
          AND ec.platform = p_platform
      ) AS is_excluded
    FROM shopify_products_cache sc,
         jsonb_array_elements(sc.categories) AS cat
    WHERE sc.store_id = p_store_id
      AND sc.categories IS NOT NULL
    GROUP BY cat->>'id', cat->>'name';

  ELSIF p_platform = 'shoprenter' THEN
    RETURN QUERY
    SELECT DISTINCT
      cat->>'id' AS category_id,
      cat->>'name' AS category_name,
      COUNT(*)::INTEGER AS product_count,
      EXISTS(
        SELECT 1 FROM excluded_categories ec
        WHERE ec.store_id = p_store_id
          AND ec.category_id = cat->>'id'
          AND ec.platform = p_platform
      ) AS is_excluded
    FROM shoprenter_products_cache sr,
         jsonb_array_elements(sr.categories) AS cat
    WHERE sr.store_id = p_store_id
      AND sr.categories IS NOT NULL
    GROUP BY cat->>'id', cat->>'name';
  END IF;
END;
$$ LANGUAGE plpgsql STABLE;

-- Function to toggle category exclusion
CREATE OR REPLACE FUNCTION toggle_category_exclusion(
  p_store_id UUID,
  p_category_id TEXT,
  p_category_name TEXT,
  p_platform TEXT,
  p_exclude BOOLEAN
)
RETURNS VOID AS $$
BEGIN
  IF p_exclude THEN
    -- Add to excluded categories
    INSERT INTO excluded_categories (store_id, category_id, category_name, platform)
    VALUES (p_store_id, p_category_id, p_category_name, p_platform)
    ON CONFLICT (store_id, category_id, platform) DO NOTHING;
  ELSE
    -- Remove from excluded categories
    DELETE FROM excluded_categories
    WHERE store_id = p_store_id
      AND category_id = p_category_id
      AND platform = p_platform;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- STEP 7: Drop store_data_exclusions table and related functions
-- ============================================================================

-- Drop policies first
DROP POLICY IF EXISTS "Users can view their store data exclusions" ON store_data_exclusions;
DROP POLICY IF EXISTS "Users can insert their store data exclusions" ON store_data_exclusions;
DROP POLICY IF EXISTS "Users can update their store data exclusions" ON store_data_exclusions;
DROP POLICY IF EXISTS "Users can delete their store data exclusions" ON store_data_exclusions;

-- Drop trigger
DROP TRIGGER IF EXISTS trigger_update_store_data_exclusions_updated_at ON store_data_exclusions;

-- Drop functions
DROP FUNCTION IF EXISTS update_store_data_exclusions_updated_at();
DROP FUNCTION IF EXISTS toggle_data_exclusion(UUID, TEXT, TEXT, BOOLEAN);
DROP FUNCTION IF EXISTS get_enabled_data_items(UUID, TEXT);
DROP FUNCTION IF EXISTS get_excluded_data_items(UUID, TEXT);

-- Drop indexes
DROP INDEX IF EXISTS idx_store_data_exclusions_store_id;
DROP INDEX IF EXISTS idx_store_data_exclusions_store_data_type;
DROP INDEX IF EXISTS idx_store_data_exclusions_is_enabled;
DROP INDEX IF EXISTS idx_store_data_exclusions_store_type_enabled;

-- Drop table
DROP TABLE IF EXISTS store_data_exclusions;

-- ============================================================================
-- STEP 8: Create view for products with computed exclusion status
-- ============================================================================

-- WooCommerce products with effective exclusion status
CREATE OR REPLACE VIEW woocommerce_products_with_exclusion AS
SELECT
  wc.*,
  (wc.excluded OR is_product_excluded_by_category(wc.store_id, wc.categories, 'woocommerce')) AS effectively_excluded,
  CASE
    WHEN wc.excluded THEN 'individual'
    WHEN is_product_excluded_by_category(wc.store_id, wc.categories, 'woocommerce') THEN 'category'
    ELSE NULL
  END AS exclusion_reason
FROM woocommerce_products_cache wc;

-- ShopRenter products with effective exclusion status
CREATE OR REPLACE VIEW shoprenter_products_with_exclusion AS
SELECT
  sr.*,
  (sr.excluded OR is_product_excluded_by_category(sr.store_id, sr.categories, 'shoprenter')) AS effectively_excluded,
  CASE
    WHEN sr.excluded THEN 'individual'
    WHEN is_product_excluded_by_category(sr.store_id, sr.categories, 'shoprenter') THEN 'category'
    ELSE NULL
  END AS exclusion_reason
FROM shoprenter_products_cache sr;

-- Shopify products with effective exclusion status
CREATE OR REPLACE VIEW shopify_products_with_exclusion AS
SELECT
  sc.*,
  (sc.excluded OR is_product_excluded_by_category(sc.store_id, sc.categories, 'shopify')) AS effectively_excluded,
  CASE
    WHEN sc.excluded THEN 'individual'
    WHEN is_product_excluded_by_category(sc.store_id, sc.categories, 'shopify') THEN 'category'
    ELSE NULL
  END AS exclusion_reason
FROM shopify_products_cache sc;

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

DO $$
BEGIN
  RAISE NOTICE '=================================================================';
  RAISE NOTICE 'Product Exclusion Refactoring Migration Completed Successfully';
  RAISE NOTICE '=================================================================';
  RAISE NOTICE '';
  RAISE NOTICE 'Changes Applied:';
  RAISE NOTICE '  ✓ Created excluded_categories table for category-level exclusions';
  RAISE NOTICE '  ✓ Added excluded column to all *_products_cache tables';
  RAISE NOTICE '  ✓ Normalized shoprenter_products_cache (extracted fields from JSONB)';
  RAISE NOTICE '  ✓ Created shopify_products_cache table';
  RAISE NOTICE '  ✓ Migrated data from store_data_exclusions to cache tables';
  RAISE NOTICE '  ✓ Dropped store_data_exclusions table and related functions';
  RAISE NOTICE '  ✓ Created helper functions for category-based exclusions';
  RAISE NOTICE '  ✓ Created views with computed exclusion status';
  RAISE NOTICE '';
  RAISE NOTICE 'New Features:';
  RAISE NOTICE '  • Support for multiple categories per product';
  RAISE NOTICE '  • Category-level exclusion (exclude all products in a category)';
  RAISE NOTICE '  • Individual product exclusion (independent of category)';
  RAISE NOTICE '  • Computed exclusion_reason field (individual vs. category)';
  RAISE NOTICE '  • Product cache now stores essential data only';
  RAISE NOTICE '';
  RAISE NOTICE 'Next Steps:';
  RAISE NOTICE '1. Update Edge Functions (api, *-sync) to use new structure';
  RAISE NOTICE '2. Update UI to support category filtering and exclusion';
  RAISE NOTICE '3. Add Qdrant deletion logic for excluded products';
  RAISE NOTICE '4. Test complete flow with all three platforms';
  RAISE NOTICE '';
END $$;
