migration.sql

-- ============================================
-- SehaSport Migration Script
-- Run this if you have existing tables
-- ============================================

-- ============================================
-- 1. ADD NEW COLUMNS TO EXISTING TABLES
-- ============================================

-- Add lat/lng to venues if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'venues' AND column_name = 'lat') THEN
    ALTER TABLE venues ADD COLUMN lat double precision;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'venues' AND column_name = 'lng') THEN
    ALTER TABLE venues ADD COLUMN lng double precision;
  END IF;
END $$;

-- Add new columns to events
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'owner_type') THEN
    ALTER TABLE events ADD COLUMN owner_type text CHECK (owner_type IN ('USER', 'COMMUNITY')) DEFAULT 'USER';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'owner_id') THEN
    ALTER TABLE events ADD COLUMN owner_id text;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'court_id') THEN
    ALTER TABLE events ADD COLUMN court_id bigint;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'start_time') THEN
    ALTER TABLE events ADD COLUMN start_time time;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'end_time') THEN
    ALTER TABLE events ADD COLUMN end_time time;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'is_paid') THEN
    ALTER TABLE events ADD COLUMN is_paid boolean DEFAULT false;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'events' AND column_name = 'description') THEN
    ALTER TABLE events ADD COLUMN description text;
  END IF;
END $$;

-- Migrate existing events: set owner_id from creator_id
UPDATE events SET owner_id = creator_id::text WHERE owner_id IS NULL AND creator_id IS NOT NULL;

-- Add status column to participants if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'participants' AND column_name = 'status') THEN
    ALTER TABLE participants ADD COLUMN status text CHECK (status IN ('interested', 'joined', 'cancelled')) DEFAULT 'joined';
  END IF;
END $$;

-- ============================================
-- 2. CREATE NEW TABLES
-- ============================================

-- Courts table
CREATE TABLE IF NOT EXISTS courts (
  id bigint generated by default as identity primary key,
  venue_id bigint references venues(id) on delete cascade not null,
  name text not null,
  sport text not null,
  capacity int default 4,
  is_active boolean default true,
  created_at timestamptz default now() not null
);

-- Court Schedules table
CREATE TABLE IF NOT EXISTS court_schedules (
  id bigint generated by default as identity primary key,
  court_id bigint references courts(id) on delete cascade not null,
  day_of_week int check (day_of_week between 0 and 6),
  start_time time not null,
  end_time time not null,
  is_available boolean default true,
  created_at timestamptz default now() not null
);

-- Communities table
CREATE TABLE IF NOT EXISTS communities (
  id bigint generated by default as identity primary key,
  name text not null,
  description text,
  sport text not null,
  image_url text,
  is_public boolean default true,
  has_membership boolean default false,
  created_at timestamptz default now() not null
);

-- Community Members table
CREATE TABLE IF NOT EXISTS community_members (
  id bigint generated by default as identity primary key,
  community_id bigint references communities(id) on delete cascade not null,
  user_id uuid references auth.users not null,
  role text check (role in ('LEADER', 'ADMIN', 'MEMBER')) not null default 'MEMBER',
  joined_at timestamptz default now() not null,
  unique(community_id, user_id)
);

-- Bookings table
CREATE TABLE IF NOT EXISTS bookings (
  id bigint generated by default as identity primary key,
  court_id bigint references courts(id) on delete cascade not null,
  user_id uuid references auth.users not null,
  event_id bigint references events(id) on delete cascade,
  date date not null,
  start_time time not null,
  end_time time not null,
  status text check (status in ('pending', 'confirmed', 'cancelled')) default 'confirmed',
  created_at timestamptz default now() not null
);

-- Add foreign key for court_id in events (if not exists)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints 
    WHERE constraint_name = 'events_court_id_fkey'
  ) THEN
    ALTER TABLE events ADD CONSTRAINT events_court_id_fkey 
    FOREIGN KEY (court_id) REFERENCES courts(id);
  END IF;
END $$;

-- ============================================
-- 3. ENABLE RLS ON NEW TABLES
-- ============================================
ALTER TABLE courts ENABLE ROW LEVEL SECURITY;
ALTER TABLE court_schedules ENABLE ROW LEVEL SECURITY;
ALTER TABLE communities ENABLE ROW LEVEL SECURITY;
ALTER TABLE community_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE bookings ENABLE ROW LEVEL SECURITY;

-- ============================================
-- 4. CREATE POLICIES FOR NEW TABLES
-- ============================================

-- Courts policies
DROP POLICY IF EXISTS "Courts viewable by everyone" ON courts;
CREATE POLICY "Courts viewable by everyone" ON courts FOR SELECT USING (true);

DROP POLICY IF EXISTS "Venue owners can manage courts" ON courts;
CREATE POLICY "Venue owners can manage courts" ON courts FOR ALL 
  USING (EXISTS (SELECT 1 FROM venues WHERE venues.id = courts.venue_id AND venues.owner_id = auth.uid()));

-- Court Schedules policies
DROP POLICY IF EXISTS "Court schedules viewable by everyone" ON court_schedules;
CREATE POLICY "Court schedules viewable by everyone" ON court_schedules FOR SELECT USING (true);

-- Communities policies
DROP POLICY IF EXISTS "Public communities viewable" ON communities;
CREATE POLICY "Public communities viewable" ON communities FOR SELECT USING (is_public = true);

DROP POLICY IF EXISTS "Members can view their communities" ON communities;
CREATE POLICY "Members can view their communities" ON communities FOR SELECT 
  USING (EXISTS (SELECT 1 FROM community_members WHERE community_id = communities.id AND user_id = auth.uid()));

DROP POLICY IF EXISTS "Authenticated can create communities" ON communities;
CREATE POLICY "Authenticated can create communities" ON communities FOR INSERT 
  WITH CHECK (auth.role() = 'authenticated');

DROP POLICY IF EXISTS "Leaders can update communities" ON communities;
CREATE POLICY "Leaders can update communities" ON communities FOR UPDATE 
  USING (EXISTS (SELECT 1 FROM community_members WHERE community_id = communities.id AND user_id = auth.uid() AND role = 'LEADER'));

DROP POLICY IF EXISTS "Leaders can delete communities" ON communities;
CREATE POLICY "Leaders can delete communities" ON communities FOR DELETE 
  USING (EXISTS (SELECT 1 FROM community_members WHERE community_id = communities.id AND user_id = auth.uid() AND role = 'LEADER'));

-- Community Members policies
DROP POLICY IF EXISTS "Community members viewable" ON community_members;
CREATE POLICY "Community members viewable" ON community_members FOR SELECT USING (true);

DROP POLICY IF EXISTS "Users can join public communities" ON community_members;
CREATE POLICY "Users can join public communities" ON community_members FOR INSERT 
  WITH CHECK (
    auth.uid() = user_id AND 
    role = 'MEMBER' AND 
    EXISTS (SELECT 1 FROM communities WHERE id = community_id AND is_public = true)
  );

DROP POLICY IF EXISTS "Leaders can manage members" ON community_members;
CREATE POLICY "Leaders can manage members" ON community_members FOR ALL 
  USING (EXISTS (
    SELECT 1 FROM community_members cm 
    WHERE cm.community_id = community_members.community_id 
    AND cm.user_id = auth.uid() 
    AND cm.role IN ('LEADER', 'ADMIN')
  ));

-- Bookings policies
DROP POLICY IF EXISTS "Bookings viewable by everyone" ON bookings;
CREATE POLICY "Bookings viewable by everyone" ON bookings FOR SELECT USING (true);

DROP POLICY IF EXISTS "Authenticated can create bookings" ON bookings;
CREATE POLICY "Authenticated can create bookings" ON bookings FOR INSERT 
  WITH CHECK (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can update own bookings" ON bookings;
CREATE POLICY "Users can update own bookings" ON bookings FOR UPDATE 
  USING (auth.uid() = user_id);

-- ============================================
-- 5. HELPER FUNCTIONS
-- ============================================

-- Check court availability function
CREATE OR REPLACE FUNCTION check_court_availability(
  p_court_id bigint,
  p_date date,
  p_start_time time,
  p_end_time time
) RETURNS boolean AS $$
BEGIN
  RETURN NOT EXISTS (
    SELECT 1 FROM bookings 
    WHERE court_id = p_court_id 
    AND date = p_date 
    AND status != 'cancelled'
    AND (start_time < p_end_time AND end_time > p_start_time)
  );
END;
$$ LANGUAGE plpgsql;

-- Enforce single leader trigger
CREATE OR REPLACE FUNCTION enforce_single_leader()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.role = 'LEADER' THEN
    UPDATE community_members 
    SET role = 'ADMIN' 
    WHERE community_id = NEW.community_id 
    AND role = 'LEADER' 
    AND id != NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Drop and recreate trigger
DROP TRIGGER IF EXISTS ensure_single_leader ON community_members;
CREATE TRIGGER ensure_single_leader
  AFTER INSERT OR UPDATE ON community_members
  FOR EACH ROW EXECUTE FUNCTION enforce_single_leader();

-- ============================================
-- DONE! Migration complete.
-- ============================================