-- Migration: Store Data Exclusions Schema
-- Description: Creates table for managing knowledge data exclusions/inclusions in AI context
-- Date: 2025-10-31
-- Related Issue: #43

-- ============================================================================
-- STEP 1: Create store_data_exclusions Table
-- ============================================================================

-- Table to manage which data items should be included/excluded in AI context
CREATE TABLE IF NOT EXISTS store_data_exclusions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  data_type TEXT NOT NULL CHECK (data_type IN ('product', 'order', 'customer')),
  data_id TEXT NOT NULL,
  is_enabled BOOLEAN NOT NULL DEFAULT true,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT unique_store_data_exclusion UNIQUE(store_id, data_type, data_id)
);

-- ============================================================================
-- STEP 2: Create Indexes for Optimal Query Performance
-- ============================================================================

-- Index for querying by store_id
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_store_id
  ON store_data_exclusions(store_id);

-- Composite index for store_id and data_type queries
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_store_data_type
  ON store_data_exclusions(store_id, data_type);

-- Index for filtering by is_enabled status
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_is_enabled
  ON store_data_exclusions(is_enabled);

-- Composite index for frequently used query pattern (store + type + enabled)
CREATE INDEX IF NOT EXISTS idx_store_data_exclusions_store_type_enabled
  ON store_data_exclusions(store_id, data_type, is_enabled);

-- ============================================================================
-- STEP 3: Enable Row Level Security (RLS)
-- ============================================================================

-- Enable RLS on the table
ALTER TABLE store_data_exclusions ENABLE ROW LEVEL SECURITY;

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

-- Policy: Users can insert data exclusions for their own stores
CREATE POLICY "Users can insert their store data exclusions"
  ON store_data_exclusions FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = store_data_exclusions.store_id
      AND s.user_id = auth.uid()
    )
  );

-- Policy: Users can update their own store data exclusions
CREATE POLICY "Users can update their store data exclusions"
  ON store_data_exclusions FOR UPDATE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = store_data_exclusions.store_id
      AND s.user_id = auth.uid()
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = store_data_exclusions.store_id
      AND s.user_id = auth.uid()
    )
  );

-- Policy: Users can delete their own store data exclusions
CREATE POLICY "Users can delete their store data exclusions"
  ON store_data_exclusions FOR DELETE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = store_data_exclusions.store_id
      AND s.user_id = auth.uid()
    )
  );

-- ============================================================================
-- STEP 4: Create Helper Functions
-- ============================================================================

-- Function to update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_store_data_exclusions_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update updated_at on row update
CREATE TRIGGER trigger_update_store_data_exclusions_updated_at
  BEFORE UPDATE ON store_data_exclusions
  FOR EACH ROW
  EXECUTE FUNCTION update_store_data_exclusions_updated_at();

-- Function to toggle data exclusion status
CREATE OR REPLACE FUNCTION toggle_data_exclusion(
  p_store_id UUID,
  p_data_type TEXT,
  p_data_id TEXT,
  p_is_enabled BOOLEAN
)
RETURNS void AS $$
BEGIN
  INSERT INTO store_data_exclusions (store_id, data_type, data_id, is_enabled)
  VALUES (p_store_id, p_data_type, p_data_id, p_is_enabled)
  ON CONFLICT (store_id, data_type, data_id)
  DO UPDATE SET
    is_enabled = p_is_enabled,
    updated_at = NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get enabled data items for a store
CREATE OR REPLACE FUNCTION get_enabled_data_items(
  p_store_id UUID,
  p_data_type TEXT DEFAULT NULL
)
RETURNS TABLE (
  data_type TEXT,
  data_id TEXT,
  metadata JSONB
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    sde.data_type,
    sde.data_id,
    sde.metadata
  FROM store_data_exclusions sde
  WHERE sde.store_id = p_store_id
    AND sde.is_enabled = true
    AND (p_data_type IS NULL OR sde.data_type = p_data_type);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get excluded data items for a store
CREATE OR REPLACE FUNCTION get_excluded_data_items(
  p_store_id UUID,
  p_data_type TEXT DEFAULT NULL
)
RETURNS TABLE (
  data_type TEXT,
  data_id TEXT,
  metadata JSONB
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    sde.data_type,
    sde.data_id,
    sde.metadata
  FROM store_data_exclusions sde
  WHERE sde.store_id = p_store_id
    AND sde.is_enabled = false
    AND (p_data_type IS NULL OR sde.data_type = p_data_type);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

DO $$
BEGIN
  RAISE NOTICE 'Store data exclusions migration completed successfully';
  RAISE NOTICE 'Created table: store_data_exclusions';
  RAISE NOTICE 'Features:';
  RAISE NOTICE '  - Manage product/order/customer exclusions per store';
  RAISE NOTICE '  - RLS policies for secure data access';
  RAISE NOTICE '  - Optimized indexes for query performance';
  RAISE NOTICE '  - Helper functions for data management';
  RAISE NOTICE '';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Create Edge Function API endpoints for managing exclusions';
  RAISE NOTICE '2. Update AI context building logic to respect exclusions';
  RAISE NOTICE '3. Add frontend UI for managing store data exclusions';
END $$;
