CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL
);

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

-- Insert default admin user admin/admin123
INSERT INTO admins (username, password) VALUES (
  'admin',
  '$2y$10$yL1/0Vvno5vJ3/zkm08dN..g/JjOWcZMqD10fpknCnXggbIMmImC6'
);
