1  -- Enable UUID generation extension
 2  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 3  
 4  -- Create 'users' table
 5  CREATE TABLE users (
 6      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
 7      email VARCHAR(255) NOT NULL UNIQUE,
 8      username VARCHAR(50) NOT NULL UNIQUE,
 9      password_hash VARCHAR(255) NOT NULL,
10      full_name VARCHAR(100),
11      phone_number VARCHAR(20),
12      is_active BOOLEAN NOT NULL DEFAULT TRUE,
13      email_verified BOOLEAN NOT NULL DEFAULT FALSE,
14      roles TEXT[] NOT NULL DEFAULT '{}',
15      metadata JSONB DEFAULT '{}'::JSONB,
16  
17      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
18      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
19      deleted_at TIMESTAMP WITH TIME ZONE
20  );
21  
22  -- Function to update 'updated_at' column
23  CREATE OR REPLACE FUNCTION set_updated_at_column()
24  RETURNS TRIGGER AS $$
25  BEGIN
26      NEW.updated_at = NOW();
27      RETURN NEW;
28  END;
29  $$ LANGUAGE plpgsql;
30  
31  -- Trigger for users table
32  CREATE TRIGGER trigger_set_updated_at
33  BEFORE UPDATE ON users
34  FOR EACH ROW
35  EXECUTE PROCEDURE set_updated_at_column();
36  
37  -- Create 'posts' table
38  CREATE TABLE posts (
39      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
40      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
41      title VARCHAR(255) NOT NULL,
42      content TEXT NOT NULL,
43      tags TEXT[] DEFAULT '{}',
44      is_published BOOLEAN NOT NULL DEFAULT FALSE,
45      metadata JSONB DEFAULT '{}'::JSONB,
46  
47      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
48      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
49      deleted_at TIMESTAMP WITH TIME ZONE
50  );
51  
52  -- Trigger for posts table
53  CREATE TRIGGER trigger_set_updated_at_posts
54  BEFORE UPDATE ON posts
55  FOR EACH ROW
56  EXECUTE PROCEDURE set_updated_at_column();
57  
58  -- Insert example users
59  INSERT INTO users (email, username, password_hash, full_name, phone_number, roles, metadata)
60  VALUES 
61  ('alice@example.com', 'alice123', 'hashed_password_1', 'Alice Johnson', '+1234567890', ARRAY['user'], '{"timezone": "UTC"}'),
62  ('bob@example.com', 'bobbyB', 'hashed_password_2', 'Bob Brown', '+1987654321', ARRAY['admin'], '{"preferred_language": "en"}'),
63  ('carol@example.com', 'carolC', 'hashed_password_3', 'Carol Clark', NULL, ARRAY['user', 'editor'], '{"newsletter": true}');
64  
65  -- Insert example posts
66  -- Using subqueries to get user ids
67  INSERT INTO posts (user_id, title, content, tags, is_published, metadata)
68  VALUES 
69  ((SELECT id FROM users WHERE username = 'alice123'), 'Alice''s First Post', 'Content of Alice''s post.', ARRAY['intro', 'welcome'], TRUE, '{"views": 10}'),
70  ((SELECT id FROM users WHERE username = 'bobbyB'), 'Bob''s Thoughts', 'Bob writes about Postgres.', ARRAY['tech', 'postgres'], FALSE, '{"likes": 5}'),
71  ((SELECT id FROM users WHERE username = 'carolC'), 'Carol''s Corner', 'Carol shares tips.', ARRAY['tips', 'writing'], TRUE, '{"shares": 2}');