#!/bin/bash
docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks <<'EOSQL'
-- Add missing columns to existing 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 auth_token TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS auth_token_expires TIMESTAMPTZ;

-- Watchlist with UUID user_id
CREATE TABLE IF NOT EXISTS watchlist (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  product_id INTEGER 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 with UUID user_id
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);

GRANT ALL ON TABLE users, watchlist, email_log, page_views TO sneakerpicks;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO sneakerpicks;
EOSQL
echo "Exit: $?"