AeThex-OS/supabase/migrations/002_community_system.sql

-- Migration 002: Community System
-- Creates tables for events, opportunities, applications

-- ============================================================================
-- COMMUNITY EVENTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(300) NOT NULL,
  description TEXT,
  category VARCHAR(50) NOT NULL CHECK (category IN ('workshop', 'conference', 'meetup', 'hackathon', 'webinar', 'other')),
  date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date TIMESTAMP WITH TIME ZONE,
  location VARCHAR(200),
  is_virtual BOOLEAN DEFAULT false,
  price DECIMAL(10, 2) DEFAULT 0,
  capacity INTEGER,
  attendees INTEGER DEFAULT 0,
  featured BOOLEAN DEFAULT false,
  image_url VARCHAR(500),
  organizer_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_date ON community_events(date);
CREATE INDEX IF NOT EXISTS idx_events_category ON community_events(category);
CREATE INDEX IF NOT EXISTS idx_events_featured ON community_events(featured);

-- ============================================================================
-- EVENT REGISTRATIONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_event_registrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES community_events(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  registered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  status VARCHAR(20) DEFAULT 'registered' CHECK (status IN ('registered', 'attended', 'cancelled')),
  UNIQUE(event_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_registrations_event ON community_event_registrations(event_id);
CREATE INDEX IF NOT EXISTS idx_registrations_user ON community_event_registrations(user_id);

-- ============================================================================
-- JOB OPPORTUNITIES
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_opportunities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(300) NOT NULL,
  company VARCHAR(200) NOT NULL,
  arm VARCHAR(50) NOT NULL CHECK (arm IN ('codex', 'aegis', 'axiom', 'nexus', 'labs', 'foundation', 'corp')),
  type VARCHAR(50) NOT NULL CHECK (type IN ('full-time', 'part-time', 'contract', 'freelance', 'internship')),
  location VARCHAR(200),
  is_remote BOOLEAN DEFAULT false,
  description TEXT,
  requirements TEXT[],
  salary_min INTEGER,
  salary_max INTEGER,
  salary_currency VARCHAR(10) DEFAULT 'USD',
  applicants INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  posted_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX IF NOT EXISTS idx_opportunities_arm ON community_opportunities(arm);
CREATE INDEX IF NOT EXISTS idx_opportunities_type ON community_opportunities(type);
CREATE INDEX IF NOT EXISTS idx_opportunities_active ON community_opportunities(is_active);

-- ============================================================================
-- JOB APPLICATIONS
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_applications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  opportunity_id UUID NOT NULL REFERENCES community_opportunities(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  resume_url VARCHAR(500),
  cover_letter TEXT,
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'reviewed', 'interviewing', 'offered', 'rejected', 'withdrawn')),
  applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(opportunity_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_applications_opportunity ON community_applications(opportunity_id);
CREATE INDEX IF NOT EXISTS idx_applications_user ON community_applications(user_id);
CREATE INDEX IF NOT EXISTS idx_applications_status ON community_applications(status);

-- ============================================================================
-- COMMUNITY POSTS (for feed/updates)
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(500) NOT NULL,
  content TEXT NOT NULL,
  arm_affiliation VARCHAR(50) CHECK (arm_affiliation IN ('labs', 'gameforge', 'corp', 'foundation', 'devlink', 'nexus', 'staff', 'general')),
  author_id UUID NOT NULL REFERENCES auth.users(id),
  tags TEXT[],
  category VARCHAR(50),
  is_published BOOLEAN DEFAULT true,
  is_pinned BOOLEAN DEFAULT false,
  likes_count INTEGER DEFAULT 0,
  comments_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_posts_arm ON community_posts(arm_affiliation);
CREATE INDEX IF NOT EXISTS idx_posts_author ON community_posts(author_id);
CREATE INDEX IF NOT EXISTS idx_posts_published ON community_posts(is_published);

-- ============================================================================
-- POST LIKES
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_post_likes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES community_posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(post_id, user_id)
);

-- ============================================================================
-- POST COMMENTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS community_post_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES community_posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  parent_id UUID REFERENCES community_post_comments(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_comments_post ON community_post_comments(post_id);
CREATE INDEX IF NOT EXISTS idx_comments_user ON community_post_comments(user_id);

-- ============================================================================
-- TRIGGER TO UPDATE COUNTS
-- ============================================================================

-- Update attendee count when registration is added
CREATE OR REPLACE FUNCTION update_event_attendees()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE community_events SET attendees = attendees + 1 WHERE id = NEW.event_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE community_events SET attendees = attendees - 1 WHERE id = OLD.event_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_event_attendees ON community_event_registrations;
CREATE TRIGGER trigger_update_event_attendees
AFTER INSERT OR DELETE ON community_event_registrations
FOR EACH ROW EXECUTE FUNCTION update_event_attendees();

-- Update applicant count when application is added
CREATE OR REPLACE FUNCTION update_opportunity_applicants()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE community_opportunities SET applicants = applicants + 1 WHERE id = NEW.opportunity_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE community_opportunities SET applicants = applicants - 1 WHERE id = OLD.opportunity_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_opportunity_applicants ON community_applications;
CREATE TRIGGER trigger_update_opportunity_applicants
AFTER INSERT OR DELETE ON community_applications
FOR EACH ROW EXECUTE FUNCTION update_opportunity_applicants();