-- 邮件自动收取功能 - 数据库迁移

-- 1. message_id 唯一索引（防止重复入库）
DROP INDEX IF EXISTS public.idx_emails_message_id;
CREATE UNIQUE INDEX IF NOT EXISTS idx_emails_message_id_unique
  ON public.emails(message_id) WHERE message_id IS NOT NULL;

-- 2. 通过邮箱地址查找用户 ID 的 RPC 函数
CREATE OR REPLACE FUNCTION public.get_user_id_by_email(email_address text)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  result_id uuid;
BEGIN
  SELECT id INTO result_id
  FROM auth.users
  WHERE email = email_address
  LIMIT 1;

  RETURN result_id;
END;
$$;

-- 3. 同步状态表
CREATE TABLE IF NOT EXISTS email_sync_state (
  id INTEGER PRIMARY KEY DEFAULT 1,
  account TEXT NOT NULL DEFAULT 'default',
  last_uid INTEGER NOT NULL DEFAULT 0,
  last_synced_at TIMESTAMPTZ,
  total_synced INTEGER DEFAULT 0,
  last_error TEXT,
  CHECK (id = 1)
);

INSERT INTO email_sync_state (id, account) VALUES (1, 'default')
ON CONFLICT DO NOTHING;
