-- Migration: Watchlist + Email system
-- Extends existing users table, adds watchlist and email_log

-- Add missing columns to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT false;
ALTER TABLE users ADD COLUMN IF NOT EXISTS verification_token TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS magic_link_token TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS magic_link_expires_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS unsubscribed BOOLEAN DEFAULT false;

-- Watchlist table
CREATE TABLE IF NOT EXISTS watchlist (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  target_price NUMERIC(10,2),
  notify_price_drop BOOLEAN DEFAULT true,
  notify_back_in_stock BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, product_id)
);

-- Email log
CREATE TABLE IF NOT EXISTS email_log (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  email_type TEXT NOT NULL,
  product_id INTEGER,
  resend_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_watchlist_user ON watchlist(user_id);
CREATE INDEX IF NOT EXISTS idx_watchlist_product ON watchlist(product_id);
CREATE INDEX IF NOT EXISTS idx_email_log_user ON email_log(user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_email_log_type ON email_log(email_type, created_at);
