-- MySQL Script generated by MySQL Workbench
-- Thu Dec  4 11:36:52 2025
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mailtracker
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mailtracker
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mailtracker` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `mailtracker` ;

-- -----------------------------------------------------
-- Table `mailtracker`.`blocked_targets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`blocked_targets` (
  `blta_id` BIGINT NOT NULL AUTO_INCREMENT,
  `blta_target_type` ENUM('domain', 'ip') NOT NULL,
  `blta_target_value` VARCHAR(255) NOT NULL,
  `blta_blocked_until` DATETIME NOT NULL,
  `blta_reason` VARCHAR(255) NULL DEFAULT NULL,
  `blta_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`blta_id`),
  UNIQUE INDEX `uniq_block_target` (`blta_target_type` ASC, `blta_target_value` ASC) VISIBLE,
  INDEX `blocked_until` (`blta_blocked_until` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`contacts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`contacts` (
  `cont_id` BIGINT NOT NULL AUTO_INCREMENT,
  `cont_email` VARCHAR(320) NOT NULL,
  `cont_name` VARCHAR(200) NULL DEFAULT NULL,
  `cont_status` ENUM('active', 'disabled', 'bounced', 'unsubscribed') NOT NULL DEFAULT 'active',
  `cont_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cont_id`),
  UNIQUE INDEX `uniq_contacts_email` (`cont_email` ASC) VISIBLE,
  INDEX `status` (`cont_status` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`campaign_groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`campaign_groups` (
  `cagr_id` INT NOT NULL AUTO_INCREMENT,
  `camp_id` INT NOT NULL,
  `cagr_group_nombre` VARCHAR(200) NOT NULL,
  `cagr_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cagr_id`),
  INDEX `camp_id` (`camp_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`campaign_group_members`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`campaign_group_members` (
  `cagr_id` INT NOT NULL,
  `cont_id` BIGINT NOT NULL,
  `cagm_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cagr_id`, `cont_id`),
  INDEX `fk_cgm_contact` (`cont_id` ASC) INVISIBLE,
  CONSTRAINT `fk_cgm_contact`
    FOREIGN KEY (`cont_id`)
    REFERENCES `mailtracker`.`contacts` (`cont_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_cgm_group`
    FOREIGN KEY (`cagr_id`)
    REFERENCES `mailtracker`.`campaign_groups` (`cagr_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`campaigns`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`campaigns` (
  `camp_id` INT NOT NULL AUTO_INCREMENT,
  `camp_nombre` VARCHAR(250) NOT NULL,
  `camp_status` ENUM('draft', 'scheduled', 'running', 'paused', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
  `camp_start_at` DATETIME NULL DEFAULT NULL,
  `camp_end_at` DATETIME NULL DEFAULT NULL,
  `camp_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `camp_updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`camp_id`),
  INDEX `camp_status` (`camp_status` ASC) VISIBLE,
  INDEX `start_at` (`camp_start_at` ASC) VISIBLE,
  INDEX `end_at` (`camp_end_at` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`domain_provider_cache`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`domain_provider_cache` (
  `dopr_domain` VARCHAR(255) NOT NULL,
  `dopr_provider` VARCHAR(50) NOT NULL,
  `dopr_expires_at` DATETIME NOT NULL,
  `dopr_updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`dopr_domain`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`messenger`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`messenger` (
  `mess_id` INT NOT NULL AUTO_INCREMENT,
  `mess_subject` VARCHAR(255) NOT NULL,
  `mess_body` MEDIUMTEXT NOT NULL,
  `mess_created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `camp_id` INT NOT NULL,
  `mess_status` TINYINT(1) NOT NULL DEFAULT '1',
  `mess_tracked` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1 = enlaces ya convertidos a tracking',
  `mess_updated_at` DATETIME NULL DEFAULT NULL,
  `mess_dynamic` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1 = contenido dinámico desde URL',
  `mess_dynamic_url` VARCHAR(500) NULL DEFAULT NULL COMMENT 'URL API que devuelve JSON con campo HTML',
  PRIMARY KEY (`mess_id`),
  INDEX `mess_camp_id` (`camp_id` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`link_tracker`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`link_tracker` (
  `litr_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `litr_original_url` TEXT NOT NULL,
  `litr_track_code` VARCHAR(64) NOT NULL,
  `litr_click_count` INT(11) NOT NULL DEFAULT '0',
  `litr_last_click_at` DATETIME NULL DEFAULT NULL,
  `litr_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `camp_id` INT NOT NULL,
  `mess_id` INT NOT NULL,
  PRIMARY KEY (`litr_id`),
  UNIQUE INDEX `litr_track_code` (`litr_track_code` ASC) VISIBLE,
  INDEX `litr_camp_id` (`camp_id` ASC) VISIBLE,
  INDEX `litr_mess_id` (`mess_id` ASC) VISIBLE,
  CONSTRAINT `litr_camp_id`
    FOREIGN KEY (`camp_id`)
    REFERENCES `mailtracker`.`campaigns` (`camp_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `litr_mess_id`
    FOREIGN KEY (`mess_id`)
    REFERENCES `mailtracker`.`messenger` (`mess_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`link_clicks`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`link_clicks` (
  `clic_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `litr_id` BIGINT(20) NOT NULL,
  `clic_ip` VARCHAR(45) NULL DEFAULT NULL,
  `clic_user_agent` TEXT NULL DEFAULT NULL,
  `clic_referrer` TEXT NULL DEFAULT NULL,
  `clic_clicked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `camp_id` INT NOT NULL,
  `mess_id` INT NOT NULL,
  `cont_id` BIGINT NOT NULL,
  PRIMARY KEY (`clic_id`),
  INDEX `idx_litr` (`litr_id` ASC) VISIBLE,
  INDEX `licl_camp_id` (`camp_id` ASC) VISIBLE,
  INDEX `licl_mess_id` (`mess_id` ASC) VISIBLE,
  INDEX `licl_cont_id` (`cont_id` ASC) VISIBLE,
  CONSTRAINT `fk_click_link`
    FOREIGN KEY (`litr_id`)
    REFERENCES `mailtracker`.`link_tracker` (`litr_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `licl_camp_id`
    FOREIGN KEY (`camp_id`)
    REFERENCES `mailtracker`.`campaigns` (`camp_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `licl_mess_id`
    FOREIGN KEY (`mess_id`)
    REFERENCES `mailtracker`.`messenger` (`mess_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `licl_cont_id`
    FOREIGN KEY (`cont_id`)
    REFERENCES `mailtracker`.`contacts` (`cont_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Registro individual de clics en enlaces por contacto';


-- -----------------------------------------------------
-- Table `mailtracker`.`mail_accounts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`mail_accounts` (
  `maac_id` INT NOT NULL AUTO_INCREMENT,
  `maac_email` VARCHAR(320) NOT NULL COMMENT 'Dirección de correo usada como remitente',
  `maac_name` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Nombre descriptivo (ej: “Marketing Principal”)',
  `maac_host` VARCHAR(255) NOT NULL COMMENT 'Servidor SMTP (ej: mail.avisonetmailing.top)',
  `maac_port` INT NOT NULL DEFAULT '587' COMMENT 'Puerto SMTP (25, 465 o 587)',
  `maac_user` VARCHAR(255) NOT NULL COMMENT 'Usuario de autenticación SMTP',
  `maac_pass` VARCHAR(255) NOT NULL COMMENT 'Contraseña SMTP (idealmente cifrada en producción)',
  `maac_ssl` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1 = usa SSL/TLS, 0 = no',
  `maac_status` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '1 = activo, 0 = inactivo',
  `maac_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`maac_id`),
  UNIQUE INDEX `uniq_maac_email` (`maac_email` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Cuentas remitentes para rotación de envíos SMTP';


-- -----------------------------------------------------
-- Table `mailtracker`.`mail_logs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`mail_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `log_time` DATETIME NULL DEFAULT NULL,
  `message_id` VARCHAR(50) NULL DEFAULT NULL,
  `sender` VARCHAR(255) NULL DEFAULT NULL,
  `recipient` VARCHAR(255) NULL DEFAULT NULL,
  `relay` VARCHAR(255) NULL DEFAULT NULL,
  `dsn` VARCHAR(20) NULL DEFAULT NULL,
  `status` VARCHAR(50) NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 70820
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`outbox`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`outbox` (
  `outb_id` BIGINT NOT NULL AUTO_INCREMENT,
  `camp_id` INT NULL DEFAULT NULL,
  `cont_id` BIGINT NULL DEFAULT NULL,
  `outb_to_addr` VARCHAR(320) NOT NULL,
  `outb_from_addr` VARCHAR(320) NOT NULL,
  `outb_schedule_at` DATETIME NULL DEFAULT NULL,
  `outb_status` ENUM('pending', 'sending', 'sent', 'deferred', 'failed') NOT NULL DEFAULT 'pending',
  `outb_attempts` INT NOT NULL DEFAULT '0',
  `outb_last_attempt_at` DATETIME NULL DEFAULT NULL,
  `outb_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mess_id` INT NOT NULL,
  `maac_id` INT NOT NULL,
  PRIMARY KEY (`outb_id`),
  UNIQUE INDEX `uniq_camp_contact` (`camp_id` ASC, `cont_id` ASC, `mess_id` ASC) VISIBLE,
  INDEX `fk_out_contact` (`cont_id` ASC) VISIBLE,
  INDEX `status` (`outb_status` ASC) VISIBLE,
  INDEX `schedule_at` (`outb_schedule_at` ASC) VISIBLE,
  INDEX `camp_id` (`camp_id` ASC) VISIBLE,
  INDEX `to_addr` (`outb_to_addr` ASC) VISIBLE,
  INDEX `outb_mess_id` (`mess_id` ASC) VISIBLE,
  INDEX `outb_maac_id` (`maac_id` ASC) VISIBLE,
  CONSTRAINT `fk_out_contact`
    FOREIGN KEY (`cont_id`)
    REFERENCES `mailtracker`.`contacts` (`cont_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `outb_maac_id`
    FOREIGN KEY (`maac_id`)
    REFERENCES `mailtracker`.`mail_accounts` (`maac_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`send_attempts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`send_attempts` (
  `seat_id` BIGINT NOT NULL AUTO_INCREMENT,
  `outb_id` BIGINT NOT NULL,
  `seat_attempt_at` DATETIME NOT NULL,
  `seat_smtp_code` INT NULL DEFAULT NULL,
  `seat_smtp_response` TEXT NULL DEFAULT NULL,
  `seat_status` VARCHAR(20) NOT NULL,
  `seat_duration_ms` INT NULL DEFAULT NULL,
  `seat_remote_ip` VARCHAR(45) NULL DEFAULT NULL,
  `seat_provider` VARCHAR(50) NULL DEFAULT NULL,
  `seat_note` TEXT NULL DEFAULT NULL,
  `camp_id` INT NOT NULL,
  PRIMARY KEY (`seat_id`),
  INDEX `out_id` (`outb_id` ASC) VISIBLE,
  INDEX `attempt_at` (`seat_attempt_at` ASC) VISIBLE,
  INDEX `status` (`seat_status` ASC) VISIBLE,
  INDEX `provider` (`seat_provider` ASC) VISIBLE,
  INDEX `seat_camp_id` (`camp_id` ASC) VISIBLE,
  CONSTRAINT `fk_attempt_out`
    FOREIGN KEY (`outb_id`)
    REFERENCES `mailtracker`.`outbox` (`outb_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mailtracker`.`suppression_list`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mailtracker`.`suppression_list` (
  `suli_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de cada registro.',
  `suli_type` ENUM('email', 'domain') NOT NULL COMMENT 'Este campo indica el tipo de entidad a dar de baja, puede ser un correo o un dominio completo.\\nDefine si el bloqueo aplica a una dirección de correo específica (email) o a todo un dominio (domain). Ejemplo: type=\'domain\' y value=\'@empresa.cl\' bloquea todos los correos de ese dominio.',
  `suli_value` VARCHAR(320) NOT NULL COMMENT 'El nombre de la entidad a dar de baja, ejemplo: correo: \"juanito.palotes@correo.com\" o \"noexistenic.cl\"\\nEl valor bloqueado: puede ser cliente@empresa.cl o empresa.cl, dependiendo del tipo.',
  `suli_reason` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Motivo por el cual se agregó (opcional). Ejemplo: “Solicitud de baja”, “Bounce permanente”, “Reporte de spam”.',
  `suli_source` ENUM('unsubscribe', 'bounce', 'manual', 'fbl', 'policy') NOT NULL DEFAULT 'manual' COMMENT 'Indica de dónde viene el bloqueo:\\n\\nunsubscribe: el usuario hizo clic en “no quiero recibir más”.\\nbounce: el correo rebotó (inexistente o rechazado).\\nmanual: agregado por un operador desde el panel.\\nfbl: feedback loop de un proveedor (ej. Gmail).\\npolicy: bloqueo automático por política (demasiados fallos, etc.).',
  `suli_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`suli_id`),
  UNIQUE INDEX `uniq_suppression` (`suli_type` ASC, `suli_value` ASC) VISIBLE,
  INDEX `type` (`suli_type` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 1077
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

USE `mailtracker`;

DELIMITER $$
USE `mailtracker`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `mailtracker`.`trg_campaign_update_schedule`
AFTER UPDATE ON `mailtracker`.`campaigns`
FOR EACH ROW
BEGIN
  -- Solo actúa si cambió la fecha de inicio
  IF NEW.camp_start_at <> OLD.camp_start_at THEN
    UPDATE outbox
      SET outb_schedule_at = NEW.camp_start_at
      WHERE camp_id = NEW.camp_id
        AND outb_status IN ('pending', 'deferred');
  END IF;
END$$

USE `mailtracker`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `mailtracker`.`trg_outbox_default_schedule`
BEFORE INSERT ON `mailtracker`.`outbox`
FOR EACH ROW
BEGIN
  DECLARE start_time DATETIME;

  -- Busca la fecha de inicio de la campaña
  SELECT camp_start_at INTO start_time
  FROM campaigns
  WHERE camp_id = NEW.camp_id;

  -- Si no hay fecha definida, usa la hora actual
  IF NEW.outb_schedule_at IS NULL THEN
    SET NEW.outb_schedule_at = COALESCE(start_time, NOW());
  END IF;
END$$


DELIMITER ;

