-- 创建警报类型枚举
CREATE TYPE public.alert_severity AS ENUM ('info', 'warning', 'critical');
CREATE TYPE public.alert_category AS ENUM ('inventory', 'order', 'marketing', 'finance', 'service', 'system');

-- 创建业务警报表
CREATE TABLE public.business_alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_type TEXT NOT NULL,
    category alert_category NOT NULL,
    severity alert_severity NOT NULL DEFAULT 'info',
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    data JSONB DEFAULT '{}'::jsonb,
    target_agent TEXT NOT NULL DEFAULT 'manager',
    is_read BOOLEAN NOT NULL DEFAULT false,
    is_dismissed BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    read_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE
);

-- 创建索引
CREATE INDEX idx_business_alerts_category ON public.business_alerts(category);
CREATE INDEX idx_business_alerts_severity ON public.business_alerts(severity);
CREATE INDEX idx_business_alerts_target_agent ON public.business_alerts(target_agent);
CREATE INDEX idx_business_alerts_created_at ON public.business_alerts(created_at DESC);
CREATE INDEX idx_business_alerts_unread ON public.business_alerts(is_read) WHERE is_read = false;

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

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

-- 管理员可以更新警报状态（标记已读/忽略）
CREATE POLICY "Admins can update alerts"
ON public.business_alerts
FOR UPDATE
USING (EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 系统可以插入警报（通过 Edge Function 使用 service_role）
CREATE POLICY "Service role can insert alerts"
ON public.business_alerts
FOR INSERT
WITH CHECK (true);

-- 管理员可以删除过期警报
CREATE POLICY "Admins can delete alerts"
ON public.business_alerts
FOR DELETE
USING (EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.user_id = auth.uid() AND profiles.role = 'admin'
));

-- 启用 Realtime
ALTER PUBLICATION supabase_realtime ADD TABLE public.business_alerts;

-- 添加注释
COMMENT ON TABLE public.business_alerts IS '业务警报表 - 存储 AI 主动监控生成的经营洞察和警报';
COMMENT ON COLUMN public.business_alerts.target_agent IS '目标 Agent: manager, product, marketing, finance, service';
COMMENT ON COLUMN public.business_alerts.alert_type IS '警报类型: low_stock, order_anomaly, campaign_expiring, refund_spike 等';