-- Migration: Add Qdrant integration support and extend data access permissions
-- Date: 2025-11-11
-- Issue: #74

-- 1. Update data_access_permissions to include customer and order access control
-- Modify default value to include all three permission types
ALTER TABLE stores
  ALTER COLUMN data_access_permissions
  SET DEFAULT '{"allow_product_access": true, "allow_customer_access": true, "allow_order_access": true}'::jsonb;

-- Update existing records to include the new permission fields (default to true)
UPDATE stores
SET data_access_permissions = jsonb_build_object(
  'allow_product_access', COALESCE((data_access_permissions->>'allow_product_access')::boolean, true),
  'allow_customer_access', true,
  'allow_order_access', true
)
WHERE data_access_permissions IS NOT NULL;

-- For NULL records, set to default
UPDATE stores
SET data_access_permissions = '{"allow_product_access": true, "allow_customer_access": true, "allow_order_access": true}'::jsonb
WHERE data_access_permissions IS NULL;

-- Update the check constraint to validate new fields
ALTER TABLE stores
  DROP CONSTRAINT IF EXISTS stores_data_access_permissions_check;

ALTER TABLE stores
  ADD CONSTRAINT stores_data_access_permissions_check
  CHECK (
    data_access_permissions ? 'allow_product_access' AND
    data_access_permissions ? 'allow_customer_access' AND
    data_access_permissions ? 'allow_order_access'
  );

-- Update column comment
COMMENT ON COLUMN stores.data_access_permissions IS 'JSONB object controlling data access permissions: allow_product_access, allow_customer_access, allow_order_access. Controls both SQL cache and Qdrant sync.';

-- 2. Add Qdrant-specific columns to stores table
ALTER TABLE stores
  ADD COLUMN IF NOT EXISTS qdrant_sync_enabled boolean DEFAULT true,
  ADD COLUMN IF NOT EXISTS qdrant_last_sync_at timestamptz,
  ADD COLUMN IF NOT EXISTS qdrant_collection_prefix text;

COMMENT ON COLUMN stores.qdrant_sync_enabled IS 'Whether to sync store data to Qdrant vector database';
COMMENT ON COLUMN stores.qdrant_last_sync_at IS 'Timestamp of last successful Qdrant sync';
COMMENT ON COLUMN stores.qdrant_collection_prefix IS 'Custom collection prefix for Qdrant (defaults to store_name)';

-- 3. Create qdrant_sync_logs table to track Qdrant synchronization
CREATE TABLE IF NOT EXISTS qdrant_sync_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id uuid NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  sync_type text NOT NULL CHECK (sync_type IN ('products', 'orders', 'customers', 'all')),
  collection_name text NOT NULL,
  operation text NOT NULL CHECK (operation IN ('create_collection', 'upsert', 'delete', 'full_sync')),
  items_processed integer DEFAULT 0,
  items_succeeded integer DEFAULT 0,
  items_failed integer DEFAULT 0,
  error_message text,
  started_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz,
  duration_ms integer GENERATED ALWAYS AS (
    EXTRACT(EPOCH FROM (completed_at - started_at)) * 1000
  ) STORED,
  metadata jsonb,
  created_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_qdrant_sync_logs_store_id ON qdrant_sync_logs(store_id);
CREATE INDEX IF NOT EXISTS idx_qdrant_sync_logs_created_at ON qdrant_sync_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_qdrant_sync_logs_sync_type ON qdrant_sync_logs(sync_type);

COMMENT ON TABLE qdrant_sync_logs IS 'Tracks Qdrant vector database synchronization operations';

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

-- RLS Policy: Users can only see their own store's sync logs
CREATE POLICY "Users can view their own qdrant sync logs"
  ON qdrant_sync_logs
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM stores
      WHERE stores.id = qdrant_sync_logs.store_id
      AND stores.user_id = auth.uid()
    )
  );

-- 4. Create helper functions for data access permission checks
CREATE OR REPLACE FUNCTION can_sync_products(store_uuid uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result boolean;
BEGIN
  SELECT COALESCE((data_access_permissions->>'allow_product_access')::boolean, true)
  INTO result
  FROM stores
  WHERE id = store_uuid;

  RETURN COALESCE(result, true);
END;
$$;

CREATE OR REPLACE FUNCTION can_sync_customers(store_uuid uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result boolean;
BEGIN
  SELECT COALESCE((data_access_permissions->>'allow_customer_access')::boolean, true)
  INTO result
  FROM stores
  WHERE id = store_uuid;

  RETURN COALESCE(result, true);
END;
$$;

CREATE OR REPLACE FUNCTION can_sync_orders(store_uuid uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result boolean;
BEGIN
  SELECT COALESCE((data_access_permissions->>'allow_order_access')::boolean, true)
  INTO result
  FROM stores
  WHERE id = store_uuid;

  RETURN COALESCE(result, true);
END;
$$;

COMMENT ON FUNCTION can_sync_products IS 'Check if products can be synced for a store (both SQL cache and Qdrant)';
COMMENT ON FUNCTION can_sync_customers IS 'Check if customers can be synced for a store (both SQL cache and Qdrant)';
COMMENT ON FUNCTION can_sync_orders IS 'Check if orders can be synced for a store (both SQL cache and Qdrant)';

-- 5. Create trigger to update qdrant_last_sync_at when sync completes successfully
CREATE OR REPLACE FUNCTION update_qdrant_last_sync()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.completed_at IS NOT NULL AND NEW.error_message IS NULL THEN
    UPDATE stores
    SET qdrant_last_sync_at = NEW.completed_at
    WHERE id = NEW.store_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trigger_update_qdrant_last_sync
  AFTER INSERT OR UPDATE ON qdrant_sync_logs
  FOR EACH ROW
  WHEN (NEW.completed_at IS NOT NULL)
  EXECUTE FUNCTION update_qdrant_last_sync();

-- 6. Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_stores_qdrant_sync_enabled ON stores(qdrant_sync_enabled) WHERE qdrant_sync_enabled = true;
CREATE INDEX IF NOT EXISTS idx_stores_data_access_permissions ON stores USING gin(data_access_permissions);
