

CREATE TABLE IF NOT EXISTS mail_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  internal_code VARCHAR(40) NOT NULL UNIQUE,
  mailbox VARCHAR(190) NOT NULL,
  graph_message_id VARCHAR(255) NULL,
  graph_immutable_id VARCHAR(255) NULL,
  internet_message_id VARCHAR(255) NULL,
  conversation_id VARCHAR(255) NULL,
  email_hash CHAR(64) NOT NULL,
  subject VARCHAR(500) NULL,
  sender_name VARCHAR(190) NULL,
  sender_email VARCHAR(190) NULL,
  received_at DATETIME NULL,
  body_preview TEXT NULL,
  body_html MEDIUMTEXT NULL,
  body_text MEDIUMTEXT NULL,
  has_attachments TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('PENDIENTE','EN_PROCESO','PROCESADO','IGNORADO','ERROR') NOT NULL DEFAULT 'PENDIENTE',
  locked_by INT NULL,
  locked_at DATETIME NULL,
  dolibarr_order_id INT NULL,
  dolibarr_order_ref VARCHAR(80) NULL,
  dolibarr_client_id INT NULL,
  dolibarr_client_name VARCHAR(255) NULL,
  requester_name VARCHAR(190) NULL,
  requester_email VARCHAR(190) NULL,
  pdf_filename VARCHAR(255) NULL,
  pdf_uploaded_at DATETIME NULL,
  processed_by INT NULL,
  processed_at DATETIME NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mailbox_internet (mailbox, internet_message_id),
  UNIQUE KEY uq_mailbox_hash (mailbox, email_hash),
  KEY idx_status_received (status, received_at),
  KEY idx_order_ref (dolibarr_order_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mail_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mail_message_id INT NOT NULL,
  graph_attachment_id VARCHAR(255) NULL,
  filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) NULL,
  size_bytes INT NULL,
  local_path VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (mail_message_id) REFERENCES mail_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS processing_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mail_message_id INT NULL,
  user_id INT NULL,
  action VARCHAR(80) NOT NULL,
  details TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (mail_message_id) REFERENCES mail_messages(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES app_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
