-- ============================================
-- 产品价格变更历史表
-- ============================================

CREATE TABLE public.product_price_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID NOT NULL REFERENCES public.products(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  -- 变更信息
  old_price DECIMAL NOT NULL DEFAULT 0,
  new_price DECIMAL NOT NULL DEFAULT 0,
  change_amount DECIMAL NOT NULL DEFAULT 0,
  -- 操作人
  operator_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  operator_name TEXT,
  -- 变更原因/备注
  change_reason TEXT,
  -- 时间
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 添加索引
CREATE INDEX idx_product_price_history_product ON public.product_price_history(product_id);
CREATE INDEX idx_product_price_history_company ON public.product_price_history(company_id);
CREATE INDEX idx_product_price_history_created ON public.product_price_history(created_at);

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

-- RLS 策略
-- 公司成员可以查看自己公司的产品价格变更记录
CREATE POLICY company_select_price_history ON public.product_price_history
  FOR SELECT USING (company_id = get_user_master_company_id());

-- 公司成员可以创建价格变更记录
CREATE POLICY company_insert_price_history ON public.product_price_history
  FOR INSERT WITH CHECK (company_id = get_user_master_company_id());
