| CREATE TABLE IF NOT EXISTS users ( |
| id TEXT PRIMARY KEY, |
| username TEXT UNIQUE, |
| password_hash TEXT, |
| display_name TEXT, |
| bio TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
| ); |
|
|
| CREATE TABLE IF NOT EXISTS tweets ( |
| id TEXT PRIMARY KEY, |
| user_id TEXT, |
| content TEXT, |
| quote_tweet_id TEXT, |
| reply_to_id TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| FOREIGN KEY (user_id) REFERENCES users(id), |
| FOREIGN KEY (quote_tweet_id) REFERENCES tweets(id), |
| FOREIGN KEY (reply_to_id) REFERENCES tweets(id) |
| ); |
|
|
| CREATE TABLE IF NOT EXISTS follows ( |
| follower_id TEXT, |
| followed_id TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| PRIMARY KEY (follower_id, followed_id), |
| FOREIGN KEY (follower_id) REFERENCES users(id), |
| FOREIGN KEY (followed_id) REFERENCES users(id) |
| ); |
|
|
| CREATE TABLE IF NOT EXISTS likes ( |
| user_id TEXT, |
| tweet_id TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| PRIMARY KEY (user_id, tweet_id), |
| FOREIGN KEY (user_id) REFERENCES users(id), |
| FOREIGN KEY (tweet_id) REFERENCES tweets(id) |
| ); |
|
|
| CREATE TABLE IF NOT EXISTS bookmarks ( |
| user_id TEXT, |
| tweet_id TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| PRIMARY KEY (user_id, tweet_id), |
| FOREIGN KEY (user_id) REFERENCES users(id), |
| FOREIGN KEY (tweet_id) REFERENCES tweets(id) |
| ); |
|
|
| CREATE TABLE IF NOT EXISTS retweets ( |
| user_id TEXT, |
| tweet_id TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| PRIMARY KEY (user_id, tweet_id), |
| FOREIGN KEY (user_id) REFERENCES users(id), |
| FOREIGN KEY (tweet_id) REFERENCES tweets(id) |
| ); |
|
|
| CREATE TABLE IF NOT EXISTS messages ( |
| id TEXT PRIMARY KEY, |
| sender_id TEXT, |
| receiver_id TEXT, |
| content TEXT, |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, |
| FOREIGN KEY (sender_id) REFERENCES users(id), |
| FOREIGN KEY (receiver_id) REFERENCES users(id) |
| ); |
|
|
