#!/bin/bash
# Check table ownership
docker exec -i supabase-db psql -U postgres -d sneakerpicks -c "SELECT schemaname, tableowner, tablename FROM pg_tables WHERE tablename='products';"

# Run migration as superuser postgres, then grant to sneakerpicks
docker exec -i supabase-db psql -U postgres -d sneakerpicks <<'EOSQL'
-- Create tables as superuser
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  email_verified BOOLEAN DEFAULT false,
  verification_token TEXT,
  auth_token TEXT,
  auth_token_expires TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS watchlist (
  id SERIAL PRIMARY KEY,
  user_id INTEGER 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)
);

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

CREATE TABLE IF NOT EXISTS page_views (
  id SERIAL PRIMARY KEY,
  product_id INTEGER,
  page_type TEXT,
  session_id TEXT,
  ip_hash TEXT,
  user_agent TEXT,
  referrer TEXT,
  utm_source TEXT,
  utm_medium TEXT,
  utm_campaign TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE daily_stats ADD COLUMN IF NOT EXISTS impressions INTEGER DEFAULT 0;

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_page_views_product ON page_views(product_id);
CREATE INDEX IF NOT EXISTS idx_page_views_created ON page_views(created_at);
CREATE INDEX IF NOT EXISTS idx_email_log_user ON email_log(user_id);

-- Grant access to sneakerpicks user
GRANT ALL ON TABLE users TO sneakerpicks;
GRANT ALL ON TABLE watchlist TO sneakerpicks;
GRANT ALL ON TABLE email_log TO sneakerpicks;
GRANT ALL ON TABLE page_views TO sneakerpicks;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO sneakerpicks;
EOSQL
echo "Migration done: $?"