-- ======================================================================
-- MAILING SYSTEM - Migracja bazy danych
-- Frytura.com.pl Panel Admin
-- ======================================================================

-- 1. Konta SMTP
CREATE TABLE IF NOT EXISTS `mail_smtp_accounts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT 'Nazwa konta (np. "Konto główne")',
    `host` VARCHAR(255) NOT NULL,
    `port` SMALLINT UNSIGNED NOT NULL DEFAULT 587,
    `encryption` ENUM('none','ssl','tls') NOT NULL DEFAULT 'tls',
    `username` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL COMMENT 'Zaszyfrowane AES-256',
    `from_email` VARCHAR(255) NOT NULL,
    `from_name` VARCHAR(100) NOT NULL DEFAULT 'Frytura.com.pl',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `daily_limit` INT UNSIGNED NOT NULL DEFAULT 500 COMMENT 'Maks emaili na dzień',
    `sent_today` INT UNSIGNED NOT NULL DEFAULT 0,
    `last_reset_date` DATE NULL COMMENT 'Kiedy ostatnio reset sent_today',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 2. Grupy odbiorców
CREATE TABLE IF NOT EXISTS `mail_groups` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT 'Np. "Hurtownie Śląsk"',
    `description` TEXT NULL,
    `emails_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cache - liczba emaili',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 3. Emaile w grupach
CREATE TABLE IF NOT EXISTS `mail_group_emails` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `group_id` INT UNSIGNED NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `name` VARCHAR(150) NULL COMMENT 'Opcjonalne imię/firma',
    `status` ENUM('active','unsubscribed','bounced','invalid') NOT NULL DEFAULT 'active',
    `added_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_group_email` (`group_id`, `email`),
    INDEX `idx_group` (`group_id`),
    INDEX `idx_status` (`status`),
    CONSTRAINT `fk_mge_group` FOREIGN KEY (`group_id`) REFERENCES `mail_groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 4. Szablony / Treści wiadomości
CREATE TABLE IF NOT EXISTS `mail_messages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL COMMENT 'Wewnętrzna nazwa szablonu',
    `subject` VARCHAR(255) NOT NULL,
    `body_html` LONGTEXT NOT NULL,
    `body_text` TEXT NULL COMMENT 'Wersja plain-text (opcjonalna)',
    `created_by` INT UNSIGNED NULL COMMENT 'ID admina',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 5. Kampanie wysyłkowe
CREATE TABLE IF NOT EXISTS `mail_campaigns` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `smtp_account_id` INT UNSIGNED NOT NULL,
    `group_id` INT UNSIGNED NOT NULL,
    `message_id` INT UNSIGNED NOT NULL,
    `status` ENUM('draft','scheduled','running','paused','completed','cancelled') NOT NULL DEFAULT 'draft',
    `rate_per_minute` TINYINT UNSIGNED NOT NULL DEFAULT 10 COMMENT 'Emaili na minutę (1-60)',
    `scheduled_at` DATETIME NULL COMMENT 'Kiedy wystartować (NULL = ręcznie)',
    `started_at` DATETIME NULL,
    `completed_at` DATETIME NULL,
    -- Statystyki (aktualizowane na bieżąco)
    `total_emails` INT UNSIGNED NOT NULL DEFAULT 0,
    `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `open_count` INT UNSIGNED NOT NULL DEFAULT 0,
    -- Śledzenie kolejki
    `last_processed_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Ostatni przetworzony mail_queue.id',
    `created_by` INT UNSIGNED NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`),
    INDEX `idx_scheduled` (`scheduled_at`),
    CONSTRAINT `fk_mc_smtp` FOREIGN KEY (`smtp_account_id`) REFERENCES `mail_smtp_accounts`(`id`),
    CONSTRAINT `fk_mc_group` FOREIGN KEY (`group_id`) REFERENCES `mail_groups`(`id`),
    CONSTRAINT `fk_mc_message` FOREIGN KEY (`message_id`) REFERENCES `mail_messages`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 6. Kolejka wysyłki (wypełniana przy starcie kampanii)
CREATE TABLE IF NOT EXISTS `mail_queue` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `campaign_id` INT UNSIGNED NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `recipient_name` VARCHAR(150) NULL,
    `status` ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending',
    `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `error_message` VARCHAR(500) NULL,
    `sent_at` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_campaign_status` (`campaign_id`, `status`),
    INDEX `idx_pending` (`campaign_id`, `id`) COMMENT 'Do pobierania kolejnych rekordów',
    CONSTRAINT `fk_mq_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `mail_campaigns`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 7. Log wysyłek (szczegółowy)
CREATE TABLE IF NOT EXISTS `mail_send_log` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `campaign_id` INT UNSIGNED NOT NULL,
    `queue_id` INT UNSIGNED NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `status` ENUM('sent','failed') NOT NULL,
    `error` VARCHAR(500) NULL,
    `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_campaign` (`campaign_id`),
    INDEX `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 8. Globalne rezygnacje (unsubscribe)
CREATE TABLE IF NOT EXISTS `mail_unsubscribes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `reason` VARCHAR(255) NULL,
    `unsubscribed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
