CREATE DATABASE IF NOT EXISTS gmail_auto CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE gmail_auto;

CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  client_id VARCHAR(255) DEFAULT NULL,
  client_secret VARCHAR(255) DEFAULT NULL,
  redirect_uri VARCHAR(255) DEFAULT NULL,
  token_json LONGTEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS forwarded_mails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  gmail_message_id VARCHAR(255) NOT NULL,
  forwarded_by VARCHAR(255) DEFAULT NULL,
  original_sender VARCHAR(255) DEFAULT NULL,
  subject VARCHAR(512) DEFAULT NULL,
  snippet TEXT DEFAULT NULL,
  body LONGTEXT DEFAULT NULL,
  received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  replied TINYINT(1) DEFAULT 0,
  replied_at DATETIME DEFAULT NULL,
  UNIQUE KEY uq_account_message (account_id, gmail_message_id),
  INDEX idx_account (account_id),
  CONSTRAINT fk_acc FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;