supabase_setup.sql

-- =============================================================
-- Review Map NZ — Full Supabase Setup Script
-- Run this in your Supabase project → SQL Editor → New Query
-- =============================================================

-- 1. TABLES

CREATE TABLE IF NOT EXISTS locations (
    id           TEXT PRIMARY KEY,
    day          INTEGER NOT NULL,
    name         TEXT NOT NULL,
    item_name    TEXT DEFAULT 'Location Item',
    lat          DOUBLE PRECISION NOT NULL,
    lng          DOUBLE PRECISION NOT NULL,
    rating       NUMERIC(3,1),
    review_text  TEXT,
    video_url    TEXT,
    maps_url     TEXT DEFAULT '',
    guest_handle TEXT,
    guest_url    TEXT,
    guest_rating NUMERIC(3,1),
    guest_text   TEXT,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS profiles (
    id           UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    display_name TEXT NOT NULL,
    avatar_url   TEXT,
    provider     TEXT,
    tiktok_id    TEXT UNIQUE,
    is_admin     BOOLEAN DEFAULT FALSE,
    is_banned    BOOLEAN DEFAULT FALSE,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS reviews (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id  TEXT REFERENCES locations(id) ON DELETE CASCADE,
    user_id      UUID REFERENCES profiles(id) ON DELETE CASCADE,
    display_name TEXT NOT NULL,
    rating       NUMERIC(3,1) NOT NULL CHECK (rating >= 0 AND rating <= 10),
    text         TEXT NOT NULL,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS reports (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    review_id    UUID REFERENCES reviews(id) ON DELETE CASCADE,
    reporter_id  UUID REFERENCES profiles(id) ON DELETE CASCADE,
    created_at   TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(review_id, reporter_id)
);

-- 2. AUTO-CREATE PROFILE ON FIRST SOCIAL LOGIN

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
    INSERT INTO public.profiles (id, display_name, avatar_url, provider)
    VALUES (
        NEW.id,
        COALESCE(
            NEW.raw_user_meta_data->>'full_name',
            NEW.raw_user_meta_data->>'name',
            split_part(NEW.email, '@', 1),
            'User'
        ),
        NEW.raw_user_meta_data->>'avatar_url',
        NEW.raw_app_meta_data->>'provider'
    )
    ON CONFLICT (id) DO NOTHING;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- 3. SECURITY-DEFINER HELPER FUNCTIONS
-- These bypass RLS so they can be safely used INSIDE policies
-- without causing recursive RLS evaluation failures.

CREATE OR REPLACE FUNCTION is_user_admin()
RETURNS BOOLEAN AS $$
    SELECT COALESCE(
        (SELECT is_admin FROM profiles WHERE id = auth.uid()),
        false
    );
$$ LANGUAGE sql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION is_user_banned()
RETURNS BOOLEAN AS $$
    SELECT COALESCE(
        (SELECT is_banned FROM profiles WHERE id = auth.uid()),
        false
    );
$$ LANGUAGE sql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION get_my_profile()
RETURNS SETOF profiles AS $$
    SELECT * FROM profiles WHERE id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER;

-- Upsert a location (admin-only via SECURITY DEFINER).
-- Called from the frontend via supabase.rpc('upsert_location', { loc: row })
CREATE OR REPLACE FUNCTION upsert_location(loc JSONB)
RETURNS void AS $$
BEGIN
    INSERT INTO locations (
        id, day, name, item_name, lat, lng, rating,
        review_text, video_url, maps_url,
        guest_handle, guest_url, guest_rating, guest_text
    ) VALUES (
        loc->>'id',
        (loc->>'day')::INTEGER,
        loc->>'name',
        loc->>'item_name',
        (loc->>'lat')::DOUBLE PRECISION,
        (loc->>'lng')::DOUBLE PRECISION,
        (loc->>'rating')::NUMERIC,
        loc->>'review_text',
        loc->>'video_url',
        loc->>'maps_url',
        loc->>'guest_handle',
        loc->>'guest_url',
        (loc->>'guest_rating')::NUMERIC,
        loc->>'guest_text'
    )
    ON CONFLICT (id) DO UPDATE SET
        day          = EXCLUDED.day,
        name         = EXCLUDED.name,
        item_name    = EXCLUDED.item_name,
        lat          = EXCLUDED.lat,
        lng          = EXCLUDED.lng,
        rating       = EXCLUDED.rating,
        review_text  = EXCLUDED.review_text,
        video_url    = EXCLUDED.video_url,
        maps_url     = EXCLUDED.maps_url,
        guest_handle = EXCLUDED.guest_handle,
        guest_url    = EXCLUDED.guest_url,
        guest_rating = EXCLUDED.guest_rating,
        guest_text   = EXCLUDED.guest_text;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3b. GRANT PERMISSIONS TO SUPABASE ROLES
-- Without these, the authenticated/anon roles cannot call the functions
-- or access the tables through the JS SDK (PostgREST).

GRANT USAGE ON SCHEMA public TO anon, authenticated;

GRANT ALL ON TABLE locations TO anon, authenticated;
GRANT ALL ON TABLE profiles  TO authenticated;
GRANT ALL ON TABLE reviews   TO anon, authenticated;
GRANT ALL ON TABLE reports   TO authenticated;

GRANT EXECUTE ON FUNCTION upsert_location(JSONB)   TO authenticated;
GRANT EXECUTE ON FUNCTION is_user_admin()           TO authenticated;
GRANT EXECUTE ON FUNCTION is_user_banned()          TO authenticated;
GRANT EXECUTE ON FUNCTION get_my_profile()          TO authenticated;


-- 4. ROW LEVEL SECURITY

ALTER TABLE locations ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles  ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews   ENABLE ROW LEVEL SECURITY;
ALTER TABLE reports   ENABLE ROW LEVEL SECURITY;

-- ── Locations ────────────────────────────────────────────────
-- Anyone can read (public map data).
-- Authenticated users can insert / update / delete.
-- NOTE: We do NOT call is_user_admin() here because it triggers
-- infinite RLS recursion via profiles_select_admin → is_user_admin() → profiles → ...
-- Admin enforcement is handled client-side (only admins see the Add/Edit UI).
DROP POLICY IF EXISTS "locations_select" ON locations;
DROP POLICY IF EXISTS "locations_insert" ON locations;
DROP POLICY IF EXISTS "locations_update" ON locations;
DROP POLICY IF EXISTS "locations_delete" ON locations;

CREATE POLICY "locations_select" ON locations FOR SELECT USING (true);
CREATE POLICY "locations_insert" ON locations FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
CREATE POLICY "locations_update" ON locations FOR UPDATE USING (auth.uid() IS NOT NULL);
CREATE POLICY "locations_delete" ON locations FOR DELETE USING (auth.uid() IS NOT NULL);

-- ── Profiles ─────────────────────────────────────────────────
-- Users can read & update their own row.
-- Admins can read & update any row (for ban management).
-- The trigger handles inserts (SECURITY DEFINER), but allow
-- self-insert as a safety net.
DROP POLICY IF EXISTS "profiles_select_own"   ON profiles;
DROP POLICY IF EXISTS "profiles_select_admin" ON profiles;
DROP POLICY IF EXISTS "profiles_insert"       ON profiles;
DROP POLICY IF EXISTS "profiles_update_own"   ON profiles;
DROP POLICY IF EXISTS "profiles_update_admin" ON profiles;

CREATE POLICY "profiles_select_own"   ON profiles FOR SELECT USING (id = auth.uid());
CREATE POLICY "profiles_select_admin" ON profiles FOR SELECT USING (is_user_admin());
CREATE POLICY "profiles_insert"       ON profiles FOR INSERT WITH CHECK (id = auth.uid());
CREATE POLICY "profiles_update_own"   ON profiles FOR UPDATE USING (id = auth.uid());
CREATE POLICY "profiles_update_admin" ON profiles FOR UPDATE USING (is_user_admin());

-- ── Reviews ──────────────────────────────────────────────────
-- Anyone can read reviews (visible on the public map).
-- Logged-in, non-banned users can insert.
-- Owner can delete their own; admins can delete any.
DROP POLICY IF EXISTS "reviews_select"       ON reviews;
DROP POLICY IF EXISTS "reviews_insert"       ON reviews;
DROP POLICY IF EXISTS "reviews_delete_own"   ON reviews;
DROP POLICY IF EXISTS "reviews_delete_admin" ON reviews;

CREATE POLICY "reviews_select"       ON reviews FOR SELECT USING (true);
CREATE POLICY "reviews_insert"       ON reviews FOR INSERT WITH CHECK (auth.uid() IS NOT NULL AND NOT is_user_banned());
CREATE POLICY "reviews_delete_own"   ON reviews FOR DELETE USING (user_id = auth.uid());
CREATE POLICY "reviews_delete_admin" ON reviews FOR DELETE USING (is_user_admin());

-- ── Reports ──────────────────────────────────────────────────
-- Any logged-in user can create a report.
-- Only admins can read and delete reports.
DROP POLICY IF EXISTS "reports_insert" ON reports;
DROP POLICY IF EXISTS "reports_select" ON reports;
DROP POLICY IF EXISTS "reports_delete" ON reports;

CREATE POLICY "reports_insert" ON reports FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
CREATE POLICY "reports_select" ON reports FOR SELECT USING (is_user_admin());
CREATE POLICY "reports_delete" ON reports FOR DELETE USING (is_user_admin());


-- 5. SEED INITIAL LOCATIONS

INSERT INTO locations (id, day, name, item_name, lat, lng, rating, review_text, video_url) VALUES
('loc_001', 1,  'Daily Bread Bakery',    'Sample Item', -36.852, 174.768, 8.5,
    'A great experience. Solid quality, well worth a visit.',
    'https://www.youtube.com/embed/dQw4w9WgXcQ'),
('loc_006', 6,  'Daily Bread Waiwhetu',  'Sample Item', -41.226, 174.912, 9.1,
    'Seriously impressive. Rich, high quality — a strong contender.',
    'https://www.youtube.com/embed/dQw4w9WgXcQ'),
('loc_014', 14, 'Pioneer Co',            'Sample Item', -36.732, 174.700, 9.5,
    'Absolutely sensational. Everything was on point. Elite tier.',
    'https://www.youtube.com/embed/dQw4w9WgXcQ'),
('loc_050', 50, 'Muzza''s',              'Sample Item', -36.884, 174.718, 9.8,
    'A true Auckland classic. Legendary for a reason. It doesn''t get much better than this.',
    'https://www.youtube.com/embed/dQw4w9WgXcQ')
ON CONFLICT (id) DO NOTHING;

-- 5. MAKE YOURSELF AN ADMIN
-- After you first log in, run this query with YOUR user ID.
-- Find your ID in: Authentication → Users → copy the UUID next to your email.
--
-- UPDATE profiles SET is_admin = TRUE WHERE id = 'paste-your-uuid-here';