

CREATE TABLE IF NOT EXISTS twitter_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  twitter_handle VARCHAR(50) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS captured_tweets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile_id INT NOT NULL,
  tweet_id VARCHAR(50) NOT NULL,
  tweet_url VARCHAR(255) NOT NULL,
  tweet_datetime DATETIME NOT NULL,
  capture_datetime DATETIME NOT NULL,
  screenshot_path VARCHAR(255),
  tweet_text TEXT NOT NULL DEFAULT '',
  FOREIGN KEY (profile_id) REFERENCES twitter_profiles(id)
);
