CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(160) NOT NULL,
  role ENUM('admin','operator') NOT NULL DEFAULT 'operator',
  dolibarr_login VARCHAR(80) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

INSERT IGNORE INTO settings(setting_key, setting_value, is_secret) VALUES
('app_name','PrintCenter MailDesk',0),
('timezone','America/Santiago',0),
('base_url','https://maildesk.printcenter.digital',0),
('dolibarr_url','https://netbill.printcenter.digital',0),
('dolibarr_api_url','https://netbill.printcenter.digital/api/index.php',0),
('dolibarr_api_key','',1),
('dolibarr_allowed_group_name','Operadores_cl',0),
('dolibarr_allowed_group_id','4',0),
('graph_tenant_id','',1),
('graph_client_id','',1),
('graph_client_secret','',1),
('graph_mailbox','produccion@printcenter.cl',0),
('graph_processed_folder','Procesados Dolibarr',0),
('graph_error_folder','Error MailDesk',0),
('graph_ignored_folder','Ignorados MailDesk',0),
('email_fetch_limit','25',0);