supabase-setup.sql

-- ===================================
-- SpeakOut Confession Website
-- Complete Supabase Database Setup
-- ===================================

-- Drop existing tables if they exist (use with caution!)
-- DROP TABLE IF EXISTS messages CASCADE;
-- DROP TABLE IF EXISTS likes CASCADE;
-- DROP TABLE IF EXISTS confessions CASCADE;
-- DROP TABLE IF EXISTS users CASCADE;

-- ===================================
-- 1. CREATE TABLES
-- ===================================

-- Users Table
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  university TEXT NOT NULL,
  bio TEXT DEFAULT '',
  avatar TEXT NOT NULL,
  device_id TEXT UNIQUE,
  password_hash TEXT NOT NULL,
  instagram TEXT,
  whatsapp TEXT,
  arattai TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Confessions Table
CREATE TABLE IF NOT EXISTS confessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  content TEXT NOT NULL,
  likes INTEGER DEFAULT 0,
  reports INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Likes Table (tracks which users liked which confessions)
CREATE TABLE IF NOT EXISTS likes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  confession_id UUID REFERENCES confessions(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, confession_id)
);

-- Messages Table (for real-time chat)
CREATE TABLE IF NOT EXISTS messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  to_user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
  content TEXT NOT NULL,
  read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ===================================
-- 2. CREATE INDEXES FOR PERFORMANCE
-- ===================================

-- Confessions indexes
CREATE INDEX IF NOT EXISTS idx_confessions_user_id ON confessions(user_id);
CREATE INDEX IF NOT EXISTS idx_confessions_created_at ON confessions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_confessions_likes ON confessions(likes DESC);
CREATE INDEX IF NOT EXISTS idx_confessions_reports ON confessions(reports DESC);

-- Likes indexes
CREATE INDEX IF NOT EXISTS idx_likes_user_id ON likes(user_id);
CREATE INDEX IF NOT EXISTS idx_likes_confession_id ON likes(confession_id);

-- Messages indexes
CREATE INDEX IF NOT EXISTS idx_messages_from_user ON messages(from_user_id);
CREATE INDEX IF NOT EXISTS idx_messages_to_user ON messages(to_user_id);
CREATE INDEX IF NOT EXISTS idx_messages_users ON messages(from_user_id, to_user_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_unread ON messages(to_user_id, read) WHERE read = FALSE;

-- Users indexes
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_device_id ON users(device_id);

-- ===================================
-- 3. CREATE FUNCTIONS
-- ===================================

-- Function to increment likes on a confession
CREATE OR REPLACE FUNCTION increment_likes(confession_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE confessions 
  SET likes = likes + 1 
  WHERE id = confession_id;
END;
$$ LANGUAGE plpgsql;

-- Function to decrement likes on a confession
CREATE OR REPLACE FUNCTION decrement_likes(confession_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE confessions 
  SET likes = GREATEST(likes - 1, 0) 
  WHERE id = confession_id;
END;
$$ LANGUAGE plpgsql;

-- Function to increment reports on a confession
CREATE OR REPLACE FUNCTION increment_reports(confession_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE confessions 
  SET reports = reports + 1 
  WHERE id = confession_id;
END;
$$ LANGUAGE plpgsql;

-- 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;

-- ===================================
-- 4. CREATE TRIGGERS
-- ===================================

-- Trigger to auto-update updated_at on users table
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Trigger to auto-update updated_at on confessions table
CREATE TRIGGER update_confessions_updated_at
  BEFORE UPDATE ON confessions
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- ===================================
-- 5. ENABLE ROW LEVEL SECURITY (Optional but Recommended)
-- ===================================

-- Enable RLS on all tables
-- ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE confessions ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE likes ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Example RLS Policies (uncomment to use):

-- Users: Everyone can read, users can update their own data
-- CREATE POLICY "Users are viewable by everyone" 
--   ON users FOR SELECT 
--   USING (true);

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

-- Confessions: Everyone can read, users can create/update/delete their own
-- CREATE POLICY "Confessions are viewable by everyone" 
--   ON confessions FOR SELECT 
--   USING (true);

-- CREATE POLICY "Users can create confessions" 
--   ON confessions FOR INSERT 
--   WITH CHECK (true);

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

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

-- Likes: Everyone can read, users can manage their own likes
-- CREATE POLICY "Likes are viewable by everyone" 
--   ON likes FOR SELECT 
--   USING (true);

-- CREATE POLICY "Users can create likes" 
--   ON likes FOR INSERT 
--   WITH CHECK (true);

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

-- Messages: Users can only see their own messages
-- CREATE POLICY "Users can view their own messages" 
--   ON messages FOR SELECT 
--   USING (auth.uid() = from_user_id OR auth.uid() = to_user_id);

-- CREATE POLICY "Users can send messages" 
--   ON messages FOR INSERT 
--   WITH CHECK (auth.uid() = from_user_id);

-- CREATE POLICY "Users can update their received messages" 
--   ON messages FOR UPDATE 
--   USING (auth.uid() = to_user_id);

-- ===================================
-- 6. INSERT SAMPLE DATA (Optional)
-- ===================================

-- Insert sample users
-- INSERT INTO users (username, name, university, avatar, password_hash, bio) VALUES
-- ('john_doe', 'John Doe', 'Harvard University', 'avatar1.png', 'hash123', 'Just a student sharing thoughts'),
-- ('jane_smith', 'Jane Smith', 'MIT', 'avatar2.png', 'hash456', 'Tech enthusiast'),
-- ('bob_wilson', 'Bob Wilson', 'Stanford University', 'avatar3.png', 'hash789', 'Love to code');

-- Insert sample confessions
-- INSERT INTO confessions (user_id, content, likes) VALUES
-- ((SELECT id FROM users WHERE username = 'john_doe'), 'First day at university was amazing! #college #freshman', 15),
-- ((SELECT id FROM users WHERE username = 'jane_smith'), 'Just finished my final exam. Feeling relieved! #exams #done', 23),
-- ((SELECT id FROM users WHERE username = 'bob_wilson'), 'Anyone else struggling with @professor_name class? #help #studying', 8);

-- ===================================
-- 7. SETUP COMPLETE!
-- ===================================

-- Verification queries (run these to check setup):

-- Check users table
SELECT COUNT(*) as total_users FROM users;

-- Check confessions table
SELECT COUNT(*) as total_confessions FROM confessions;

-- Check likes table
SELECT COUNT(*) as total_likes FROM likes;

-- Check messages table
SELECT COUNT(*) as total_messages FROM messages;

-- View all tables
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
ORDER BY table_name;

-- View all indexes
SELECT indexname, tablename 
FROM pg_indexes 
WHERE schemaname = 'public' 
ORDER BY tablename, indexname;

-- ===================================
-- NOTES:
-- ===================================
-- 1. Update js/config.js with your Supabase URL and anon key
-- 2. Enable Realtime for the 'messages' table in Supabase Dashboard
--    (Database → Replication → Enable for messages table)
-- 3. For production, enable RLS policies (uncomment section 5)
-- 4. Consider adding more validation constraints as needed
-- 5. Remember to enable RLS in production for security!
-- ===================================