-- 统一上下文仓库表
CREATE TABLE public.agent_context (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  session_id UUID NOT NULL,
  user_id UUID NOT NULL,
  -- 会话上下文：当前对话状态
  conversation_context JSONB NOT NULL DEFAULT '{
    "current_agent": null,
    "intent": null,
    "turn_count": 0,
    "last_message": null
  }'::jsonb,
  -- 域上下文：各Agent的领域状态
  domain_context JSONB NOT NULL DEFAULT '{
    "store_manager": {},
    "product_manager": {},
    "marketing_manager": {},
    "service_manager": {},
    "finance_manager": {}
  }'::jsonb,
  -- 业务选中项：当前操作的实体
  selected_items JSONB NOT NULL DEFAULT '{
    "products": [],
    "orders": [],
    "coupons": [],
    "campaigns": [],
    "users": []
  }'::jsonb,
  -- 乐观锁版本号
  version INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- 确保每个session只有一个上下文
  CONSTRAINT agent_context_session_unique UNIQUE (session_id)
);

-- 上下文变更历史表（审计追踪）
CREATE TABLE public.context_changes (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  context_id UUID NOT NULL REFERENCES public.agent_context(id) ON DELETE CASCADE,
  agent_type TEXT NOT NULL,
  correlation_id UUID NOT NULL,
  -- 变更内容
  changes JSONB NOT NULL DEFAULT '{}'::jsonb,
  -- 幂等键防止重复提交
  idempotency_key TEXT NOT NULL,
  -- 变更前后版本
  version_before INTEGER NOT NULL,
  version_after INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- 幂等键唯一约束
  CONSTRAINT context_changes_idempotency_unique UNIQUE (idempotency_key)
);

-- 索引优化
CREATE INDEX idx_agent_context_session ON public.agent_context(session_id);
CREATE INDEX idx_agent_context_user ON public.agent_context(user_id);
CREATE INDEX idx_context_changes_context ON public.context_changes(context_id);
CREATE INDEX idx_context_changes_correlation ON public.context_changes(correlation_id);
CREATE INDEX idx_context_changes_agent ON public.context_changes(agent_type);

-- 启用RLS
ALTER TABLE public.agent_context ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.context_changes ENABLE ROW LEVEL SECURITY;

-- RLS策略：用户只能访问自己的上下文
CREATE POLICY "Users can view their own context"
ON public.agent_context FOR SELECT
USING (auth.uid() = user_id);

CREATE POLICY "Users can create their own context"
ON public.agent_context FOR INSERT
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own context"
ON public.agent_context FOR UPDATE
USING (auth.uid() = user_id);

-- 管理员可以查看所有上下文
CREATE POLICY "Admins can view all context"
ON public.agent_context FOR SELECT
USING (EXISTS (
  SELECT 1 FROM profiles
  WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 上下文变更历史RLS
CREATE POLICY "Users can view their own context changes"
ON public.context_changes FOR SELECT
USING (EXISTS (
  SELECT 1 FROM agent_context
  WHERE agent_context.id = context_changes.context_id
  AND agent_context.user_id = auth.uid()
));

CREATE POLICY "System can insert context changes"
ON public.context_changes FOR INSERT
WITH CHECK (EXISTS (
  SELECT 1 FROM agent_context
  WHERE agent_context.id = context_changes.context_id
  AND agent_context.user_id = auth.uid()
));

CREATE POLICY "Admins can view all context changes"
ON public.context_changes FOR SELECT
USING (EXISTS (
  SELECT 1 FROM profiles
  WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 更新时间触发器
CREATE TRIGGER update_agent_context_updated_at
BEFORE UPDATE ON public.agent_context
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at_column();