supabase_schema.sql

-- Run this script in your Supabase SQL Editor

-- 1. Create Tables
CREATE TABLE public.boards (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.profiles (
    id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    role TEXT CHECK (role IN ('admin', 'member')) DEFAULT 'member',
    avatar_url TEXT
);

CREATE TABLE public.tasks (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT CHECK (status IN ('todo', 'in_progress', 'review', 'done')) DEFAULT 'todo',
    priority TEXT CHECK (priority IN ('low', 'medium', 'high', 'blocker')) DEFAULT 'medium',
    due_date TIMESTAMP WITH TIME ZONE,
    assignee_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
    created_by UUID REFERENCES public.profiles(id) ON DELETE SET NULL,
    board_id UUID REFERENCES public.boards(id) ON DELETE CASCADE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.subtasks (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    task_id UUID REFERENCES public.tasks(id) ON DELETE CASCADE NOT NULL,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.task_comments (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    task_id UUID REFERENCES public.tasks(id) ON DELETE CASCADE NOT NULL,
    author_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL, -- Can be null for AI
    is_ai BOOLEAN DEFAULT false,
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.task_history (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    task_id UUID REFERENCES public.tasks(id) ON DELETE CASCADE NOT NULL,
    actor_id UUID REFERENCES public.profiles(id) ON DELETE SET NULL, -- Null if AI
    action TEXT NOT NULL,
    changes JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 2. Automatically update tasks updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_tasks_updated_at
    BEFORE UPDATE ON public.tasks
    FOR EACH ROW
    EXECUTE PROCEDURE update_updated_at_column();

-- 3. Row Level Security Setup
ALTER TABLE public.boards ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subtasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.task_comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.task_history ENABLE ROW LEVEL SECURITY;

-- Disable RLS constraints for simplicity in prototype, but keep it on 
-- To ensure all authenticated users can do anything
CREATE POLICY "Allow authenticated full access on boards" ON public.boards FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow authenticated full access on profiles" ON public.profiles FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow authenticated full access on tasks" ON public.tasks FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow authenticated full access on subtasks" ON public.subtasks FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow authenticated full access on task_comments" ON public.task_comments FOR ALL USING (auth.role() = 'authenticated');
CREATE POLICY "Allow authenticated full access on task_history" ON public.task_history FOR ALL USING (auth.role() = 'authenticated');

-- 4. Auto-create profile on signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, name, email, role, avatar_url)
  VALUES (
    new.id, 
    coalesce(new.raw_user_meta_data->>'name', split_part(new.email, '@', 1)), 
    new.email, 
    coalesce(new.raw_user_meta_data->>'role', 'member'), 
    coalesce(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 PROCEDURE public.handle_new_user();

-- 5. History Tracker Trigger
CREATE OR REPLACE FUNCTION public.log_task_history()
RETURNS trigger AS $$
DECLARE
  v_action TEXT;
  v_changes JSONB;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_action := 'Task created';
    v_changes := json_build_object('title', NEW.title, 'status', NEW.status);
    INSERT INTO task_history(task_id, actor_id, action, changes) 
    VALUES (NEW.id, auth.uid(), v_action, v_changes);
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        v_action := 'Status updated';
        v_changes := json_build_object('old', OLD.status, 'new', NEW.status);
        INSERT INTO task_history(task_id, actor_id, action, changes) 
        VALUES (NEW.id, auth.uid(), v_action, v_changes);
    END IF;
    IF OLD.assignee_id IS DISTINCT FROM NEW.assignee_id THEN
        v_action := 'Reassigned';
        v_changes := json_build_object('old', OLD.assignee_id, 'new', NEW.assignee_id);
        INSERT INTO task_history(task_id, actor_id, action, changes) 
        VALUES (NEW.id, auth.uid(), v_action, v_changes);
    END IF;
    RETURN NEW;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER task_history_trigger
  AFTER INSERT OR UPDATE ON public.tasks
  FOR EACH ROW EXECUTE PROCEDURE public.log_task_history();

-- 6. Add Default dummy Board
INSERT INTO public.boards (id, title) VALUES ('11111111-1111-1111-1111-111111111111', 'Project Alpha - Hackathon') ON CONFLICT DO NOTHING;