-- ---------------------------------------------------------------------------
-- Audit log for services.iperfect.net/api/gmail-api/sendMail.php
--
-- Database : common
-- Table    : email_send_log
--
-- Run this manually on the server:
--     mysql -u <user> -p common < email_send_log.sql
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `common`.`email_send_log` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  -- when the request was handled (server time)
  `sent_at`     DATETIME        NOT NULL,

  -- who called us
  `referer`     VARCHAR(512)             DEFAULT NULL COMMENT 'HTTP_REFERER, NULL when not sent',
  `client_ip`   VARCHAR(45)              DEFAULT NULL COMMENT 'REMOTE_ADDR, IPv4 or IPv6',
  `user_agent`  VARCHAR(255)             DEFAULT NULL,

  -- what was sent
  `to_email`    VARCHAR(255)             DEFAULT NULL,
  `subject`     VARCHAR(255)             DEFAULT NULL,
  `greetings`   VARCHAR(255)             DEFAULT NULL,
  `message`     MEDIUMTEXT                            COMMENT 'raw message parameter, before the HTML template is wrapped around it',

  -- what came back
  `exit_code`   INT             NOT NULL DEFAULT -1 COMMENT '0 = accepted by Gmail, non-zero = failure',
  `status`      ENUM('success','failed') NOT NULL DEFAULT 'failed',
  `response`    TEXT                                  COMMENT 'stdout/stderr of sendemail-x64',

  PRIMARY KEY (`id`),
  KEY `idx_sent_at`  (`sent_at`),
  KEY `idx_referer`  (`referer`(191)),
  KEY `idx_to_email` (`to_email`),
  KEY `idx_status`   (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ---------------------------------------------------------------------------
-- Handy queries
-- ---------------------------------------------------------------------------

-- How many emails per referer
--   SELECT COALESCE(referer,'(none)') AS referer,
--          COUNT(*) AS total,
--          SUM(status='success') AS sent,
--          SUM(status='failed')  AS failed
--     FROM common.email_send_log
--    GROUP BY referer
--    ORDER BY total DESC;

-- Daily volume for the last 30 days
--   SELECT DATE(sent_at) AS day, COUNT(*) AS total
--     FROM common.email_send_log
--    WHERE sent_at >= NOW() - INTERVAL 30 DAY
--    GROUP BY day
--    ORDER BY day DESC;

-- Recent failures
--   SELECT sent_at, referer, to_email, subject, exit_code, response
--     FROM common.email_send_log
--    WHERE status = 'failed'
--    ORDER BY id DESC
--    LIMIT 50;

-- Top recipients
--   SELECT to_email, COUNT(*) AS total
--     FROM common.email_send_log
--    GROUP BY to_email
--    ORDER BY total DESC
--    LIMIT 50;
