-- =====================================================
-- 积分与评价功能完善 - 数据库表创建脚本
-- 创建日期: 2026-03-05
-- =====================================================

-- =====================================================
-- 一、积分相关表
-- =====================================================

-- 1. 签到记录表
CREATE TABLE IF NOT EXISTS ml_signin_records (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  signin_date DATE NOT NULL,
  points_earned INT DEFAULT 0,
  bonus_points INT DEFAULT 0,
  continuous_days INT DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(user_id, signin_date)
);

CREATE INDEX IF NOT EXISTS idx_signin_records_user_id ON ml_signin_records(user_id);
CREATE INDEX IF NOT EXISTS idx_signin_records_date ON ml_signin_records(signin_date);

COMMENT ON TABLE ml_signin_records IS '用户签到记录表';

-- 2. 积分兑换商品表
CREATE TABLE IF NOT EXISTS ml_point_products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  image_url VARCHAR(500),
  product_type VARCHAR(50) NOT NULL DEFAULT 'coupon',
  points_required INT NOT NULL,
  original_price DECIMAL(10,2),
  stock INT DEFAULT 0,
  status INT DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE ml_point_products IS '积分兑换商品表';
COMMENT ON COLUMN ml_point_products.product_type IS '类型: coupon=优惠券, physical=实物, virtual=虚拟商品';

-- 3. 积分兑换记录表
CREATE TABLE IF NOT EXISTS ml_point_exchanges (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  product_id UUID NOT NULL REFERENCES ml_point_products(id),
  quantity INT DEFAULT 1,
  points_used INT NOT NULL,
  status INT DEFAULT 0,
  tracking_no VARCHAR(100),
  address_snapshot JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_point_exchanges_user_id ON ml_point_exchanges(user_id);
CREATE INDEX IF NOT EXISTS idx_point_exchanges_product_id ON ml_point_exchanges(product_id);

COMMENT ON TABLE ml_point_exchanges IS '积分兑换记录表';
COMMENT ON COLUMN ml_point_exchanges.status IS '状态: 0=待处理, 1=已发货, 2=已完成, 3=已取消';

-- 4. 积分规则配置表
CREATE TABLE IF NOT EXISTS ml_point_rules (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  rule_type VARCHAR(50) NOT NULL,
  rule_name VARCHAR(100) NOT NULL,
  points INT NOT NULL,
  description TEXT,
  config JSONB,
  status INT DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(rule_type)
);

COMMENT ON TABLE ml_point_rules IS '积分规则配置表';

-- 初始化积分规则数据
INSERT INTO ml_point_rules (rule_type, rule_name, points, description) VALUES
('register', '注册赠送', 100, '新用户注册赠送积分'),
('signin_daily', '每日签到', 5, '每日签到获得积分'),
('signin_continuous_7', '连续签到7天奖励', 20, '连续签到7天额外奖励'),
('signin_continuous_30', '连续签到30天奖励', 100, '连续签到30天额外奖励'),
('shopping', '购物奖励', 1, '每消费1元获得1积分'),
('review', '评价奖励', 10, '完成商品评价获得积分'),
('review_with_image', '带图评价奖励', 20, '带图评价额外奖励')
ON CONFLICT (rule_type) DO NOTHING;

-- =====================================================
-- 二、评价相关表
-- =====================================================

-- 1. 为商品评价表添加新字段
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS videos JSONB DEFAULT '[]';
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS tags JSONB DEFAULT '[]';
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS like_count INT DEFAULT 0;
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS is_edited BOOLEAN DEFAULT false;
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS append_content TEXT;
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS append_at TIMESTAMPTZ;
ALTER TABLE ml_product_reviews ADD COLUMN IF NOT EXISTS append_images JSONB DEFAULT '[]';

-- 2. 评价点赞表
CREATE TABLE IF NOT EXISTS ml_review_likes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  review_id UUID NOT NULL REFERENCES ml_product_reviews(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(review_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_review_likes_review_id ON ml_review_likes(review_id);
CREATE INDEX IF NOT EXISTS idx_review_likes_user_id ON ml_review_likes(user_id);

COMMENT ON TABLE ml_review_likes IS '评价点赞表';

-- 3. 评价举报表
CREATE TABLE IF NOT EXISTS ml_review_reports (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  review_id UUID NOT NULL REFERENCES ml_product_reviews(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  reason VARCHAR(200) NOT NULL,
  description TEXT,
  status INT DEFAULT 0,
  handle_result TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_review_reports_review_id ON ml_review_reports(review_id);
CREATE INDEX IF NOT EXISTS idx_review_reports_user_id ON ml_review_reports(user_id);

COMMENT ON TABLE ml_review_reports IS '评价举报表';
COMMENT ON COLUMN ml_review_reports.status IS '状态: 0=待处理, 1=已处理, 2=已驳回';

-- 4. 配送员评价表
CREATE TABLE IF NOT EXISTS ml_delivery_ratings (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_id UUID NOT NULL REFERENCES ml_orders(id),
  delivery_user_id UUID NOT NULL,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  rating INT CHECK (rating >= 1 AND rating <= 5),
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  UNIQUE(order_id)
);

CREATE INDEX IF NOT EXISTS idx_delivery_ratings_delivery_user_id ON ml_delivery_ratings(delivery_user_id);

COMMENT ON TABLE ml_delivery_ratings IS '配送员评价表';

-- =====================================================
-- 三、RLS 策略
-- =====================================================

-- 签到记录表 RLS
ALTER TABLE ml_signin_records ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own signin records" 
ON ml_signin_records FOR SELECT 
TO authenticated 
USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own signin records" 
ON ml_signin_records FOR INSERT 
TO authenticated 
WITH CHECK (auth.uid() = user_id);

-- 积分兑换商品表 RLS (所有人可查看上架商品)
ALTER TABLE ml_point_products ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can view active point products" 
ON ml_point_products FOR SELECT 
TO authenticated, anon 
USING (status = 1);

-- 允许认证用户更新商品库存（用于兑换扣减）
CREATE POLICY "Authenticated users can update point products" 
ON ml_point_products FOR UPDATE 
TO authenticated 
USING (true)
WITH CHECK (true);

-- 积分兑换记录表 RLS
ALTER TABLE ml_point_exchanges ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own exchange records" 
ON ml_point_exchanges FOR SELECT 
TO authenticated 
USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own exchange records" 
ON ml_point_exchanges FOR INSERT 
TO authenticated 
WITH CHECK (auth.uid() = user_id);

-- 评价点赞表 RLS
ALTER TABLE ml_review_likes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can view review likes" 
ON ml_review_likes FOR SELECT 
TO authenticated, anon 
USING (true);

CREATE POLICY "Users can insert own likes" 
ON ml_review_likes FOR INSERT 
TO authenticated 
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete own likes" 
ON ml_review_likes FOR DELETE 
TO authenticated 
USING (auth.uid() = user_id);

-- 评价举报表 RLS
ALTER TABLE ml_review_reports ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own reports" 
ON ml_review_reports FOR SELECT 
TO authenticated 
USING (auth.uid() = user_id);

CREATE POLICY "Users can insert reports" 
ON ml_review_reports FOR INSERT 
TO authenticated 
WITH CHECK (auth.uid() = user_id);

-- 配送员评价表 RLS
ALTER TABLE ml_delivery_ratings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view delivery ratings" 
ON ml_delivery_ratings FOR SELECT 
TO authenticated, anon 
USING (true);

CREATE POLICY "Users can insert own delivery ratings" 
ON ml_delivery_ratings FOR INSERT 
TO authenticated 
WITH CHECK (auth.uid() = user_id);

-- =====================================================
-- 四、插入测试数据
-- =====================================================

-- 插入测试积分兑换商品
INSERT INTO ml_point_products (name, description, image_url, product_type, points_required, original_price, stock, status, sort_order) VALUES
('满10减5优惠券', '全场通用，满10元可用', '', 'coupon', 100, 5.00, 1000, 1, 1),
('满50减10优惠券', '全场通用，满50元可用', '', 'coupon', 200, 10.00, 500, 1, 2),
('精美手机支架', '通用手机支架，多色可选', '', 'physical', 500, 15.00, 100, 1, 3),
('会员月卡', '享受会员专属权益30天', '', 'virtual', 1000, 29.90, 999, 1, 4),
('满100减30优惠券', '全场通用，满100元可用', '', 'coupon', 500, 30.00, 200, 1, 5),
('品牌保温杯', '304不锈钢保温杯500ml', '', 'physical', 2000, 59.00, 50, 1, 6)
ON CONFLICT DO NOTHING;