-- 创建邮件表
CREATE TABLE IF NOT EXISTS emails (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  from_email TEXT NOT NULL,
  to_email TEXT NOT NULL,
  subject TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',
  direction TEXT NOT NULL CHECK (direction IN ('sent', 'received')),
  status TEXT NOT NULL DEFAULT 'sent' CHECK (status IN ('sent', 'delivered', 'failed', 'read', 'unread')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  read_at TIMESTAMPTZ
);

-- 创建索引以优化查询
CREATE INDEX IF NOT EXISTS idx_emails_direction ON emails(direction);
CREATE INDEX IF NOT EXISTS idx_emails_created_at ON emails(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_emails_status ON emails(status);

-- 启用 RLS
ALTER TABLE emails ENABLE ROW LEVEL SECURITY;

-- 允许已认证用户读写邮件
CREATE POLICY "Authenticated users can select emails"
  ON emails FOR SELECT
  USING (auth.role() = 'authenticated');

CREATE POLICY "Authenticated users can insert emails"
  ON emails FOR INSERT
  WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Authenticated users can update emails"
  ON emails FOR UPDATE
  USING (auth.role() = 'authenticated');

CREATE POLICY "Authenticated users can delete emails"
  ON emails FOR DELETE
  USING (auth.role() = 'authenticated');
