-- ============================================================
-- KAMPUSCHOW - DATABASE MIGRATION
-- Adds email_suffix to schools table for email validation
-- ============================================================
-- Run this in the Supabase SQL Editor after the main schema
-- Safe to run multiple times (uses IF NOT EXISTS / IF EXISTS)
-- ============================================================

-- 1. Add email_suffix column to schools table
ALTER TABLE schools
ADD COLUMN IF NOT EXISTS email_suffix VARCHAR(100);

-- 2. Add logo_url column to schools table (if not already present)
ALTER TABLE schools
ADD COLUMN IF NOT EXISTS logo_url TEXT;

-- 3. Create index on email_suffix for faster lookups
CREATE INDEX IF NOT EXISTS idx_schools_email_suffix ON schools(email_suffix);

-- 4. Update existing schools with common email suffixes (optional - customize as needed)
-- UPDATE schools SET email_suffix = '@uniben.edu' WHERE name ILIKE '%University of Benin%' AND email_suffix IS NULL;
-- UPDATE schools SET email_suffix = '@aau.edu.ng' WHERE name ILIKE '%Ambrose Alli%' AND email_suffix IS NULL;

-- ============================================================
-- 5. PUSH TOKENS TABLE (for sending push notifications to customers)
-- ============================================================
CREATE TABLE IF NOT EXISTS push_tokens (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) NOT NULL,
  token TEXT NOT NULL,
  platform VARCHAR(20) DEFAULT 'fcm',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(email, token)
);

CREATE INDEX IF NOT EXISTS idx_push_tokens_email ON push_tokens(email);