SUPABASE_SCHEMA.sql

-- Etch Database Schema for Supabase

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

-- Notes table
CREATE TABLE notes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  image_url TEXT,
  tags TEXT[] DEFAULT '{}',
  accent TEXT NOT NULL DEFAULT 'bg-gradient-to-br from-purple-400 via-pink-500 to-red-500',
  type TEXT DEFAULT 'note' CHECK (type IN ('note', 'board')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  deleted_at TIMESTAMP WITH TIME ZONE
);

-- Reminders table (for tracking and auto-cleanup)
CREATE TABLE reminders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  note_id UUID REFERENCES notes(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  reminder_name TEXT NOT NULL,
  reminder_date DATE NOT NULL,
  reminder_time TIME NOT NULL,
  is_completed BOOLEAN DEFAULT FALSE,
  completed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX notes_user_id_idx ON notes(user_id);
CREATE INDEX notes_created_at_idx ON notes(created_at DESC);
CREATE INDEX notes_deleted_at_idx ON notes(deleted_at) WHERE deleted_at IS NULL;
CREATE INDEX reminders_user_id_idx ON reminders(user_id);
CREATE INDEX reminders_date_time_idx ON reminders(reminder_date, reminder_time);
CREATE INDEX reminders_completed_idx ON reminders(is_completed) WHERE is_completed = FALSE;

-- Enable Row Level Security
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE reminders ENABLE ROW LEVEL SECURITY;

-- Notes policies
CREATE POLICY "Users can view their own notes"
  ON notes FOR SELECT
  USING (auth.uid() = user_id AND deleted_at IS NULL);

CREATE POLICY "Users can insert their own notes"
  ON notes FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own notes"
  ON notes FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can soft delete their own notes"
  ON notes FOR DELETE
  USING (auth.uid() = user_id);

-- Reminders policies
CREATE POLICY "Users can view their own reminders"
  ON reminders FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own reminders"
  ON reminders FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own reminders"
  ON reminders FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own reminders"
  ON reminders FOR DELETE
  USING (auth.uid() = user_id);

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update updated_at
CREATE TRIGGER update_notes_updated_at
  BEFORE UPDATE ON notes
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Function to auto-delete expired reminders (run daily via cron)
CREATE OR REPLACE FUNCTION delete_expired_reminders()
RETURNS void AS $$
BEGIN
  DELETE FROM reminders
  WHERE 
    is_completed = FALSE
    AND (reminder_date < CURRENT_DATE 
         OR (reminder_date = CURRENT_DATE AND reminder_time < CURRENT_TIME));
END;
$$ LANGUAGE plpgsql;

-- User profiles table (optional - for storing user preferences)
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own profile"
  ON user_profiles FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can update their own profile"
  ON user_profiles FOR UPDATE
  USING (auth.uid() = id);

-- Trigger to create user profile on signup
CREATE OR REPLACE FUNCTION create_user_profile()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_profiles (id, display_name)
  VALUES (NEW.id, SPLIT_PART(NEW.email, '@', 1));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION create_user_profile();

-- Board Items table (for inspiration board)
CREATE TABLE board_items (
  id TEXT PRIMARY KEY,
  board_id UUID NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('text', 'image', 'link', 'shape')),
  content TEXT,
  position_x INTEGER NOT NULL,
  position_y INTEGER NOT NULL,
  width INTEGER NOT NULL,
  height INTEGER,
  style JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for board items
CREATE INDEX board_items_user_id_idx ON board_items(user_id);
CREATE INDEX board_items_board_id_idx ON board_items(board_id);
CREATE INDEX board_items_created_at_idx ON board_items(created_at DESC);

-- Enable RLS for board items
ALTER TABLE board_items ENABLE ROW LEVEL SECURITY;

-- Board items policies
CREATE POLICY "Users can view their own board items"
  ON board_items FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own board items"
  ON board_items FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own board items"
  ON board_items FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own board items"
  ON board_items FOR DELETE
  USING (auth.uid() = user_id);

-- Trigger to auto-update board items updated_at
CREATE TRIGGER update_board_items_updated_at
  BEFORE UPDATE ON board_items
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Function to hard delete notes (called soft_delete but actually hard deletes)
CREATE OR REPLACE FUNCTION soft_delete_note(note_id_param uuid, user_id_param uuid)
RETURNS void AS $$
BEGIN
  -- Delete associated reminder first (foreign key)
  DELETE FROM reminders 
  WHERE reminders.note_id = note_id_param
    AND reminders.user_id = user_id_param;
  
  -- Delete the note permanently
  DELETE FROM notes
  WHERE notes.id = note_id_param
    AND notes.user_id = user_id_param;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;