
-- USERS
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  username TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  bio TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- POSTS
CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id uuid REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- COMMENTS
CREATE TABLE comments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id uuid REFERENCES posts(id) ON DELETE CASCADE,
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- TAGS
CREATE TABLE tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT UNIQUE NOT NULL
);

-- POST_TAGS (many-to-many)
CREATE TABLE post_tags (
  post_id uuid REFERENCES posts(id) ON DELETE CASCADE,
  tag_id uuid REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- USERS
INSERT INTO users (username, email, bio, avatar_url)
VALUES
  ('jane_doe', 'jane@example.com', 'Web designer and blogger', 'https://picsum.photos/seed/jane/100'),
  ('john_dev', 'john@example.com', 'Fullstack dev & coffee lover', 'https://picsum.photos/seed/john/100'),
  ('mari_writer', 'mari@example.com', 'Writing about tech & life', 'https://picsum.photos/seed/mari/100');

-- TAGS
INSERT INTO tags (name)
VALUES ('web design'), ('javascript'), ('supabase'), ('life'), ('tutorial');

-- POSTS
INSERT INTO posts (author_id, title, content, published)
SELECT id, 'Getting started with Supabase', 'This is a demo post about using Supabase in a blog project.', TRUE
FROM users WHERE username = 'john_dev';

INSERT INTO posts (author_id, title, content, published)
SELECT id, 'My thoughts on web design trends', 'Here are some design trends I noticed lately.', TRUE
FROM users WHERE username = 'jane_doe';

INSERT INTO posts (author_id, title, content, published)
SELECT id, 'Balancing work and life', 'Sharing my thoughts on how I manage time.', TRUE
FROM users WHERE username = 'mari_writer';

-- POST_TAGS
INSERT INTO post_tags (post_id, tag_id)
SELECT p.id, t.id FROM posts p, tags t
WHERE p.title = 'Getting started with Supabase' AND t.name IN ('supabase', 'tutorial');

INSERT INTO post_tags (post_id, tag_id)
SELECT p.id, t.id FROM posts p, tags t
WHERE p.title = 'My thoughts on web design trends' AND t.name = 'web design';

INSERT INTO post_tags (post_id, tag_id)
SELECT p.id, t.id FROM posts p, tags t
WHERE p.title = 'Balancing work and life' AND t.name = 'life';

-- COMMENTS
INSERT INTO comments (post_id, user_id, content)
SELECT p.id, u.id, 'Nice post, thanks for sharing!' FROM posts p, users u
WHERE p.title = 'Getting started with Supabase' AND u.username = 'jane_doe';

INSERT INTO comments (post_id, user_id, content)
SELECT p.id, u.id, 'Totally agree with your points.' FROM posts p, users u
WHERE p.title = 'Balancing work and life' AND u.username = 'john_dev';
