MyRestoClub MVP Schema Migration

001_mvp_schema.sql

-- =============================================================================
-- MyRestoClub MVP Schema Migration
-- =============================================================================
-- Safe to run on existing database. Uses IF NOT EXISTS, IF EXISTS, and DO
-- blocks for all renames/drops to be idempotent.
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1. ALTER club_clubs
-- ---------------------------------------------------------------------------

-- ADD new columns (safe: each with IF NOT EXISTS via DO blocks)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='marque_focus') THEN
    ALTER TABLE club_clubs ADD COLUMN marque_focus VARCHAR(200);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='join_mode') THEN
    ALTER TABLE club_clubs ADD COLUMN join_mode VARCHAR(20) NOT NULL DEFAULT 'open';
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='facebook_url') THEN
    ALTER TABLE club_clubs ADD COLUMN facebook_url VARCHAR(500);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='instagram_url') THEN
    ALTER TABLE club_clubs ADD COLUMN instagram_url VARCHAR(500);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='youtube_url') THEN
    ALTER TABLE club_clubs ADD COLUMN youtube_url VARCHAR(500);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='contact_email') THEN
    ALTER TABLE club_clubs ADD COLUMN contact_email VARCHAR(255);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='member_count') THEN
    ALTER TABLE club_clubs ADD COLUMN member_count INTEGER NOT NULL DEFAULT 1;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='is_premium') THEN
    ALTER TABLE club_clubs ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
  END IF;
END $$;

-- RENAME founding_year -> founded_year
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='founding_year')
     AND NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='founded_year') THEN
    ALTER TABLE club_clubs RENAME COLUMN founding_year TO founded_year;
  END IF;
END $$;

-- RENAME website_url -> website
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='website_url')
     AND NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='website') THEN
    ALTER TABLE club_clubs RENAME COLUMN website_url TO website;
  END IF;
END $$;

-- MIGRATE social_links jsonb -> individual URL columns, then DROP
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='social_links') THEN
    -- Migrate existing social_links data into the new individual columns
    UPDATE club_clubs
    SET
      facebook_url  = COALESCE(facebook_url,  social_links->>'facebook'),
      instagram_url = COALESCE(instagram_url, social_links->>'instagram'),
      youtube_url   = COALESCE(youtube_url,   social_links->>'youtube')
    WHERE social_links IS NOT NULL;

    ALTER TABLE club_clubs DROP COLUMN social_links;
  END IF;
END $$;

-- DROP legacy columns
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='settings') THEN
    ALTER TABLE club_clubs DROP COLUMN settings;
  END IF;

  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='membership_fee') THEN
    ALTER TABLE club_clubs DROP COLUMN membership_fee;
  END IF;

  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_clubs' AND column_name='max_members') THEN
    ALTER TABLE club_clubs DROP COLUMN max_members;
  END IF;
END $$;

-- ADD check constraint for join_mode
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE table_name='club_clubs' AND constraint_name='club_clubs_join_mode_check'
  ) THEN
    ALTER TABLE club_clubs
      ADD CONSTRAINT club_clubs_join_mode_check
      CHECK (join_mode IN ('open', 'request', 'invite_only'));
  END IF;
END $$;

-- ADD indexes on club_clubs
CREATE INDEX IF NOT EXISTS idx_club_clubs_owner_id     ON club_clubs (owner_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_club_clubs_slug  ON club_clubs (slug);
CREATE INDEX IF NOT EXISTS idx_club_clubs_club_type    ON club_clubs (club_type);
CREATE INDEX IF NOT EXISTS idx_club_clubs_marque_focus ON club_clubs (marque_focus);
CREATE INDEX IF NOT EXISTS idx_club_clubs_state        ON club_clubs (state);
CREATE INDEX IF NOT EXISTS idx_club_clubs_is_public    ON club_clubs (is_public);


-- ---------------------------------------------------------------------------
-- 2. ALTER club_members
-- ---------------------------------------------------------------------------

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_members' AND column_name='show_vehicles') THEN
    ALTER TABLE club_members ADD COLUMN show_vehicles BOOLEAN NOT NULL DEFAULT true;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_members' AND column_name='invited_by') THEN
    ALTER TABLE club_members ADD COLUMN invited_by UUID REFERENCES auth.users(id);
  END IF;
END $$;

-- EXPAND status check constraint to include 'invited', 'declined', 'removed'
-- Drop old constraint if it exists, then recreate with the full set.
DO $$
BEGIN
  -- Drop any existing status check constraint by searching pg_constraint
  IF EXISTS (
    SELECT 1 FROM pg_constraint c
    JOIN pg_class t ON t.oid = c.conrelid
    WHERE t.relname = 'club_members' AND c.contype = 'c' AND c.conname LIKE '%status%'
  ) THEN
    EXECUTE (
      SELECT 'ALTER TABLE club_members DROP CONSTRAINT ' || quote_ident(c.conname)
      FROM pg_constraint c
      JOIN pg_class t ON t.oid = c.conrelid
      WHERE t.relname = 'club_members' AND c.contype = 'c' AND c.conname LIKE '%status%'
      LIMIT 1
    );
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint c
    JOIN pg_class t ON t.oid = c.conrelid
    WHERE t.relname = 'club_members' AND c.conname = 'club_members_status_check'
  ) THEN
    ALTER TABLE club_members
      ADD CONSTRAINT club_members_status_check
      CHECK (status IN ('active', 'pending', 'invited', 'declined', 'removed'))
      NOT VALID;
    ALTER TABLE club_members VALIDATE CONSTRAINT club_members_status_check;
  END IF;
END $$;

-- ADD unique index and other indexes on club_members
CREATE UNIQUE INDEX IF NOT EXISTS idx_club_members_club_user  ON club_members (club_id, user_id);
CREATE INDEX IF NOT EXISTS idx_club_members_club_id           ON club_members (club_id);
CREATE INDEX IF NOT EXISTS idx_club_members_user_id           ON club_members (user_id);
CREATE INDEX IF NOT EXISTS idx_club_members_status            ON club_members (status);


-- ---------------------------------------------------------------------------
-- 3. ALTER club_announcements
-- ---------------------------------------------------------------------------

-- RENAME pinned -> is_active
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_announcements' AND column_name='pinned')
     AND NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_announcements' AND column_name='is_active') THEN
    ALTER TABLE club_announcements RENAME COLUMN pinned TO is_active;
  END IF;
END $$;

-- ADD is_active if it still doesn't exist (e.g. table had no pinned column)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_announcements' AND column_name='is_active') THEN
    ALTER TABLE club_announcements ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
  END IF;
END $$;

-- ADD updated_at
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='club_announcements' AND column_name='updated_at') THEN
    ALTER TABLE club_announcements ADD COLUMN updated_at TIMESTAMPTZ DEFAULT now();
  END IF;
END $$;

-- ADD indexes on club_announcements
CREATE INDEX IF NOT EXISTS idx_club_announcements_club_id   ON club_announcements (club_id);
CREATE INDEX IF NOT EXISTS idx_club_announcements_is_active ON club_announcements (is_active);
CREATE INDEX IF NOT EXISTS idx_club_announcements_created_at ON club_announcements (created_at DESC);


-- ---------------------------------------------------------------------------
-- 4. CREATE club_invites
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS club_invites (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id     UUID NOT NULL REFERENCES club_clubs(id) ON DELETE CASCADE,
  invited_by  UUID NOT NULL REFERENCES auth.users(id),
  email       VARCHAR(255),
  token       VARCHAR(100) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at  TIMESTAMPTZ NOT NULL DEFAULT (now() + INTERVAL '30 days'),
  CONSTRAINT club_invites_status_check CHECK (status IN ('pending', 'accepted', 'expired')),
  CONSTRAINT club_invites_token_unique UNIQUE (token)
);

CREATE INDEX IF NOT EXISTS idx_club_invites_club_id   ON club_invites (club_id);
CREATE INDEX IF NOT EXISTS idx_club_invites_email     ON club_invites (email);
CREATE INDEX IF NOT EXISTS idx_club_invites_status    ON club_invites (status);
-- token uniqueness is already enforced by the constraint above; add explicit index for lookups
CREATE UNIQUE INDEX IF NOT EXISTS idx_club_invites_token ON club_invites (token);


-- ---------------------------------------------------------------------------
-- 5. CREATE club_pages
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS club_pages (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  club_id     UUID NOT NULL REFERENCES club_clubs(id) ON DELETE CASCADE,
  author_id   UUID NOT NULL REFERENCES auth.users(id),
  title       VARCHAR(200) NOT NULL,
  slug        VARCHAR(200) NOT NULL,
  body        TEXT NOT NULL DEFAULT '',
  sort_order  SMALLINT NOT NULL DEFAULT 0,
  is_public   BOOLEAN NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_club_pages_club_id          ON club_pages (club_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_club_pages_club_slug ON club_pages (club_id, slug);


-- ---------------------------------------------------------------------------
-- 6. ENABLE RLS + POLICIES
-- ---------------------------------------------------------------------------

-- Helper: a reusable function to check if the current user is an active member
-- of a club with at least a given role.
CREATE OR REPLACE FUNCTION is_club_member(p_club_id UUID, p_user_id UUID)
RETURNS BOOLEAN LANGUAGE sql SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM club_members
    WHERE club_id = p_club_id
      AND user_id = p_user_id::text
      AND status = 'active'
  );
$$;

CREATE OR REPLACE FUNCTION is_club_admin_or_owner(p_club_id UUID, p_user_id UUID)
RETURNS BOOLEAN LANGUAGE sql SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM club_members
    WHERE club_id = p_club_id
      AND user_id = p_user_id::text
      AND status = 'active'
      AND role IN ('owner', 'admin')
  );
$$;

CREATE OR REPLACE FUNCTION is_club_owner(p_club_id UUID, p_user_id UUID)
RETURNS BOOLEAN LANGUAGE sql SECURITY DEFINER AS $$
  SELECT EXISTS (
    SELECT 1 FROM club_members
    WHERE club_id = p_club_id
      AND user_id = p_user_id::text
      AND status = 'active'
      AND role = 'owner'
  );
$$;


-- ---- club_clubs ----

ALTER TABLE club_clubs ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "clubs_select_public_or_member"  ON club_clubs;
DROP POLICY IF EXISTS "clubs_insert_authenticated"     ON club_clubs;
DROP POLICY IF EXISTS "clubs_update_owner_or_admin"    ON club_clubs;
DROP POLICY IF EXISTS "clubs_delete_owner"             ON club_clubs;

-- Public clubs visible to everyone; private clubs visible to members
CREATE POLICY "clubs_select_public_or_member" ON club_clubs
  FOR SELECT USING (
    is_public = true
    OR is_club_member(id, auth.uid())
  );

CREATE POLICY "clubs_insert_authenticated" ON club_clubs
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

CREATE POLICY "clubs_update_owner_or_admin" ON club_clubs
  FOR UPDATE USING (is_club_admin_or_owner(id, auth.uid()));

CREATE POLICY "clubs_delete_owner" ON club_clubs
  FOR DELETE USING (is_club_owner(id, auth.uid()));


-- ---- club_members ----

ALTER TABLE club_members ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "members_select_same_club_or_public" ON club_members;
DROP POLICY IF EXISTS "members_insert_authenticated"       ON club_members;
DROP POLICY IF EXISTS "members_update_self_or_admin"       ON club_members;
DROP POLICY IF EXISTS "members_delete_self_or_admin"       ON club_members;

-- Members can see other members of the same club, or memberships in public clubs
CREATE POLICY "members_select_same_club_or_public" ON club_members
  FOR SELECT USING (
    is_club_member(club_id, auth.uid())
    OR EXISTS (
      SELECT 1 FROM club_clubs c WHERE c.id = club_id AND c.is_public = true
    )
  );

CREATE POLICY "members_insert_authenticated" ON club_members
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

-- Self can update own row; admins/owners can update anyone in their club
CREATE POLICY "members_update_self_or_admin" ON club_members
  FOR UPDATE USING (
    user_id = auth.uid()::text
    OR is_club_admin_or_owner(club_id, auth.uid())
  );

CREATE POLICY "members_delete_self_or_admin" ON club_members
  FOR DELETE USING (
    user_id = auth.uid()::text
    OR is_club_admin_or_owner(club_id, auth.uid())
  );


-- ---- club_invites ----

ALTER TABLE club_invites ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "invites_select_admin_or_owner"  ON club_invites;
DROP POLICY IF EXISTS "invites_insert_admin_or_owner"  ON club_invites;
DROP POLICY IF EXISTS "invites_update_admin_or_owner"  ON club_invites;
DROP POLICY IF EXISTS "invites_delete_admin_or_owner"  ON club_invites;

CREATE POLICY "invites_select_admin_or_owner" ON club_invites
  FOR SELECT USING (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "invites_insert_admin_or_owner" ON club_invites
  FOR INSERT WITH CHECK (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "invites_update_admin_or_owner" ON club_invites
  FOR UPDATE USING (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "invites_delete_admin_or_owner" ON club_invites
  FOR DELETE USING (is_club_admin_or_owner(club_id, auth.uid()));


-- ---- club_announcements ----

ALTER TABLE club_announcements ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "announcements_select_members_or_public" ON club_announcements;
DROP POLICY IF EXISTS "announcements_insert_admin_or_owner"    ON club_announcements;
DROP POLICY IF EXISTS "announcements_update_admin_or_owner"    ON club_announcements;
DROP POLICY IF EXISTS "announcements_delete_admin_or_owner"    ON club_announcements;

CREATE POLICY "announcements_select_members_or_public" ON club_announcements
  FOR SELECT USING (
    is_club_member(club_id, auth.uid())
    OR EXISTS (
      SELECT 1 FROM club_clubs c WHERE c.id = club_id AND c.is_public = true
    )
  );

CREATE POLICY "announcements_insert_admin_or_owner" ON club_announcements
  FOR INSERT WITH CHECK (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "announcements_update_admin_or_owner" ON club_announcements
  FOR UPDATE USING (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "announcements_delete_admin_or_owner" ON club_announcements
  FOR DELETE USING (is_club_admin_or_owner(club_id, auth.uid()));


-- ---- club_pages ----

ALTER TABLE club_pages ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "pages_select_public_or_member"  ON club_pages;
DROP POLICY IF EXISTS "pages_insert_admin_or_owner"    ON club_pages;
DROP POLICY IF EXISTS "pages_update_admin_or_owner"    ON club_pages;
DROP POLICY IF EXISTS "pages_delete_admin_or_owner"    ON club_pages;

-- Public pages in public clubs are visible to all; members see all pages
CREATE POLICY "pages_select_public_or_member" ON club_pages
  FOR SELECT USING (
    is_club_member(club_id, auth.uid())
    OR (
      is_public = true
      AND EXISTS (
        SELECT 1 FROM club_clubs c WHERE c.id = club_id AND c.is_public = true
      )
    )
  );

CREATE POLICY "pages_insert_admin_or_owner" ON club_pages
  FOR INSERT WITH CHECK (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "pages_update_admin_or_owner" ON club_pages
  FOR UPDATE USING (is_club_admin_or_owner(club_id, auth.uid()));

CREATE POLICY "pages_delete_admin_or_owner" ON club_pages
  FOR DELETE USING (is_club_admin_or_owner(club_id, auth.uid()));


-- ---------------------------------------------------------------------------
-- 7. TRIGGER: sync member_count on club_members changes
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION sync_club_member_count()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  v_club_id UUID;
BEGIN
  -- Determine which club_id is affected
  IF TG_OP = 'DELETE' THEN
    v_club_id := OLD.club_id;
  ELSE
    v_club_id := NEW.club_id;
  END IF;

  UPDATE club_clubs
  SET member_count = (
    SELECT COUNT(*)
    FROM club_members
    WHERE club_id = v_club_id
      AND status = 'active'
  )
  WHERE id = v_club_id;

  RETURN NULL;
END;
$$;

-- Drop existing trigger if present so we can recreate safely
DROP TRIGGER IF EXISTS trg_sync_club_member_count ON club_members;

CREATE TRIGGER trg_sync_club_member_count
  AFTER INSERT OR UPDATE OR DELETE ON club_members
  FOR EACH ROW EXECUTE FUNCTION sync_club_member_count();