root-org/supabase/migrations/001_initial_schema.sql

-- Root Organization Platform - Initial Schema
-- Run this in your Supabase SQL editor

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Profiles (synced from auth.users)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Organizations (workspaces)
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Organization Members
CREATE TABLE org_members (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'editor', 'viewer')),
  invited_at TIMESTAMPTZ DEFAULT now(),
  joined_at TIMESTAMPTZ,
  UNIQUE(org_id, user_id)
);

-- Documents/Folders
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  parent_id UUID REFERENCES documents(id) ON DELETE CASCADE,
  type TEXT NOT NULL CHECK (type IN ('folder', 'document')),
  name TEXT NOT NULL,
  content JSONB,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Kanban Boards
CREATE TABLE kanban_boards (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Kanban Columns
CREATE TABLE kanban_columns (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  board_id UUID REFERENCES kanban_boards(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  position INTEGER NOT NULL,
  color TEXT
);

-- Kanban Cards
CREATE TABLE kanban_cards (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  column_id UUID REFERENCES kanban_columns(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  position INTEGER NOT NULL,
  due_date TIMESTAMPTZ,
  color TEXT,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Card Assignees
CREATE TABLE card_assignees (
  card_id UUID REFERENCES kanban_cards(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  PRIMARY KEY (card_id, user_id)
);

-- Calendar Events
CREATE TABLE calendar_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ NOT NULL,
  all_day BOOLEAN DEFAULT false,
  color TEXT,
  recurrence TEXT,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Event Attendees
CREATE TABLE event_attendees (
  event_id UUID REFERENCES calendar_events(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  status TEXT CHECK (status IN ('pending', 'accepted', 'declined')) DEFAULT 'pending',
  PRIMARY KEY (event_id, user_id)
);

-- Indexes for performance
CREATE INDEX idx_org_members_org ON org_members(org_id);
CREATE INDEX idx_org_members_user ON org_members(user_id);
CREATE INDEX idx_documents_org ON documents(org_id);
CREATE INDEX idx_documents_parent ON documents(parent_id);
CREATE INDEX idx_kanban_boards_org ON kanban_boards(org_id);
CREATE INDEX idx_kanban_columns_board ON kanban_columns(board_id);
CREATE INDEX idx_kanban_cards_column ON kanban_cards(column_id);
CREATE INDEX idx_calendar_events_org ON calendar_events(org_id);
CREATE INDEX idx_calendar_events_time ON calendar_events(start_time, end_time);

-- Row Level Security (RLS)
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE kanban_boards ENABLE ROW LEVEL SECURITY;
ALTER TABLE kanban_columns ENABLE ROW LEVEL SECURITY;
ALTER TABLE kanban_cards ENABLE ROW LEVEL SECURITY;
ALTER TABLE card_assignees ENABLE ROW LEVEL SECURITY;
ALTER TABLE calendar_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE event_attendees ENABLE ROW LEVEL SECURITY;

-- Profiles: Users can read all profiles, update their own
CREATE POLICY "Profiles are viewable by everyone" ON profiles FOR SELECT USING (true);
CREATE POLICY "Users can update own profile" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Organizations: Members can view, owners/admins can modify
CREATE POLICY "Org members can view organizations" ON organizations FOR SELECT
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = organizations.id AND user_id = auth.uid()));

CREATE POLICY "Org owners/admins can update organizations" ON organizations FOR UPDATE
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = organizations.id AND user_id = auth.uid() AND role IN ('owner', 'admin')));

CREATE POLICY "Anyone can create organizations" ON organizations FOR INSERT WITH CHECK (true);

-- Org Members: Members can view, owners/admins can modify
CREATE POLICY "Org members can view members" ON org_members FOR SELECT
  USING (EXISTS (SELECT 1 FROM org_members om WHERE om.org_id = org_members.org_id AND om.user_id = auth.uid()));

CREATE POLICY "Org owners/admins can manage members" ON org_members FOR ALL
  USING (EXISTS (SELECT 1 FROM org_members om WHERE om.org_id = org_members.org_id AND om.user_id = auth.uid() AND om.role IN ('owner', 'admin')));

-- Documents: Based on org membership and role
CREATE POLICY "Org members can view documents" ON documents FOR SELECT
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = documents.org_id AND user_id = auth.uid()));

CREATE POLICY "Editors can manage documents" ON documents FOR ALL
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = documents.org_id AND user_id = auth.uid() AND role IN ('owner', 'admin', 'editor')));

-- Kanban: Based on org membership
CREATE POLICY "Org members can view boards" ON kanban_boards FOR SELECT
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = kanban_boards.org_id AND user_id = auth.uid()));

CREATE POLICY "Editors can manage boards" ON kanban_boards FOR ALL
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = kanban_boards.org_id AND user_id = auth.uid() AND role IN ('owner', 'admin', 'editor')));

CREATE POLICY "Org members can view columns" ON kanban_columns FOR SELECT
  USING (EXISTS (SELECT 1 FROM kanban_boards b JOIN org_members om ON b.org_id = om.org_id WHERE b.id = kanban_columns.board_id AND om.user_id = auth.uid()));

CREATE POLICY "Editors can manage columns" ON kanban_columns FOR ALL
  USING (EXISTS (SELECT 1 FROM kanban_boards b JOIN org_members om ON b.org_id = om.org_id WHERE b.id = kanban_columns.board_id AND om.user_id = auth.uid() AND om.role IN ('owner', 'admin', 'editor')));

CREATE POLICY "Org members can view cards" ON kanban_cards FOR SELECT
  USING (EXISTS (SELECT 1 FROM kanban_columns c JOIN kanban_boards b ON c.board_id = b.id JOIN org_members om ON b.org_id = om.org_id WHERE c.id = kanban_cards.column_id AND om.user_id = auth.uid()));

CREATE POLICY "Editors can manage cards" ON kanban_cards FOR ALL
  USING (EXISTS (SELECT 1 FROM kanban_columns c JOIN kanban_boards b ON c.board_id = b.id JOIN org_members om ON b.org_id = om.org_id WHERE c.id = kanban_cards.column_id AND om.user_id = auth.uid() AND om.role IN ('owner', 'admin', 'editor')));

-- Calendar: Based on org membership
CREATE POLICY "Org members can view events" ON calendar_events FOR SELECT
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = calendar_events.org_id AND user_id = auth.uid()));

CREATE POLICY "Editors can manage events" ON calendar_events FOR ALL
  USING (EXISTS (SELECT 1 FROM org_members WHERE org_id = calendar_events.org_id AND user_id = auth.uid() AND role IN ('owner', 'admin', 'editor')));

-- Trigger to create profile on user signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Function to add creator as owner when org is created
CREATE OR REPLACE FUNCTION public.handle_new_org()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.org_members (org_id, user_id, role, joined_at)
  VALUES (NEW.id, auth.uid(), 'owner', now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_org_created
  AFTER INSERT ON organizations
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_org();

-- Enable realtime for tables that need live updates
ALTER PUBLICATION supabase_realtime ADD TABLE documents;
ALTER PUBLICATION supabase_realtime ADD TABLE kanban_columns;
ALTER PUBLICATION supabase_realtime ADD TABLE kanban_cards;
ALTER PUBLICATION supabase_realtime ADD TABLE calendar_events;