-- TempMail Pro schema (manual import). If you use install.php, you don't need this.
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  is_pro TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS aliases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  local_part VARCHAR(100) NOT NULL,
  full_address VARCHAR(200) NOT NULL,
  status ENUM('active','expired','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX user_idx (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS emails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  alias_id INT NOT NULL,
  msg_uid VARCHAR(64) NOT NULL,
  from_email VARCHAR(255),
  subject VARCHAR(255),
  body_text MEDIUMTEXT,
  received_at DATETIME NOT NULL,
  INDEX alias_idx (alias_id),
  UNIQUE KEY uniq_alias_msg (alias_id, msg_uid),
  FOREIGN KEY (alias_id) REFERENCES aliases(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  provider VARCHAR(40) NOT NULL,
  provider_txn_id VARCHAR(100) NOT NULL,
  status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX user_idx (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
