-- 创建 todos 表
CREATE TABLE todos (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  text TEXT NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  image_url TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_todos_updated_at
BEFORE UPDATE ON todos
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 启用 Row Level Security
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

-- 创建 RLS 策略

-- 1. 查询策略：用户只能查看自己的 todos
CREATE POLICY "Users can view their own todos" 
ON todos FOR SELECT 
USING (auth.uid() = user_id);

-- 2. 插入策略：用户只能为自己创建 todos
CREATE POLICY "Users can create their own todos" 
ON todos FOR INSERT 
WITH CHECK (auth.uid() = user_id);

-- 3. 更新策略：用户只能更新自己的 todos
CREATE POLICY "Users can update their own todos" 
ON todos FOR UPDATE 
USING (auth.uid() = user_id);

-- 4. 删除策略：用户只能删除自己的 todos
CREATE POLICY "Users can delete their own todos" 
ON todos FOR DELETE 
USING (auth.uid() = user_id);

-- 创建索引以提高查询性能
CREATE INDEX idx_todos_user_id ON todos(user_id);