-- 创建目标表
CREATE TABLE public.goals (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  target_value NUMERIC NOT NULL,
  current_value NUMERIC NOT NULL DEFAULT 0,
  unit TEXT NOT NULL DEFAULT '元',
  deadline TIMESTAMP WITH TIME ZONE NOT NULL,
  priority TEXT NOT NULL DEFAULT 'medium', -- low, medium, high
  status TEXT NOT NULL DEFAULT 'active', -- active, completed, cancelled
  agent_type TEXT NOT NULL, -- marketing, product, service, finance, manager
  assigned_by TEXT, -- 分配者 (如 "经理AI · 小管" 或 user_id)
  assigned_to TEXT, -- 执行者 agent_type
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

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

-- 管理员可以管理所有目标
CREATE POLICY "Admins can manage goals" 
ON public.goals 
FOR ALL 
USING (EXISTS (
  SELECT 1 FROM profiles 
  WHERE profiles.user_id = auth.uid() 
  AND profiles.role = 'admin'
));

-- 公开可读（用于Agent读取目标）
CREATE POLICY "Goals are viewable by authenticated users" 
ON public.goals 
FOR SELECT 
USING (auth.uid() IS NOT NULL);

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

-- 创建索引
CREATE INDEX idx_goals_agent_type ON public.goals(agent_type);
CREATE INDEX idx_goals_status ON public.goals(status);
CREATE INDEX idx_goals_deadline ON public.goals(deadline);