-- BetxMail: Toplu mail kuyruk tabloları
-- MySQL / MariaDB. Uygulama: phpMyAdmin veya mysql CLI ile çalıştırın.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `bulk_mail_queue_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `template_id` int(11) DEFAULT NULL,
  `subject` varchar(500) NOT NULL,
  `body` mediumtext NOT NULL,
  `is_html` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('pending','processing','completed','cancelled','failed','paused') NOT NULL DEFAULT 'pending',
  `total_recipients` int(11) NOT NULL DEFAULT 0,
  `processed_count` int(11) NOT NULL DEFAULT 0,
  `success_count` int(11) NOT NULL DEFAULT 0,
  `failed_count` int(11) NOT NULL DEFAULT 0,
  `last_error` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_user` (`user_id`),
  KEY `idx_created` (`created_at`),
  CONSTRAINT `bulk_mail_queue_jobs_ibfk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `bulk_mail_queue_jobs_ibfk_template` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bulk_mail_queue_recipients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `sender_account_id` int(11) NOT NULL,
  `status` enum('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
  `mail_log_id` int(11) DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_job_status` (`job_id`, `status`),
  KEY `idx_job_id` (`job_id`, `id`),
  CONSTRAINT `bulk_mail_queue_recipients_ibfk_job` FOREIGN KEY (`job_id`) REFERENCES `bulk_mail_queue_jobs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bulk_mail_queue_recipients_ibfk_sender` FOREIGN KEY (`sender_account_id`) REFERENCES `sender_accounts` (`id`),
  CONSTRAINT `bulk_mail_queue_recipients_ibfk_mail_log` FOREIGN KEY (`mail_log_id`) REFERENCES `mail_logs` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;