-- Deploy live 2026-05-23
-- Newsletter: rate limiting, queue jobs, SMTP dedicat, token dezabonare
-- Rulează în phpMyAdmin pe baza de date live, apoi: php artisan migrate --force

-- newsletter_sends: coloane noi
ALTER TABLE `newsletter_sends`
    ADD COLUMN IF NOT EXISTS `segment`      VARCHAR(20)  NULL AFTER `format`,
    ADD COLUMN IF NOT EXISTS `scheduled_at` TIMESTAMP    NULL AFTER `started_at`;

-- newsletter_subscribers: token unic pentru dezabonare
ALTER TABLE `newsletter_subscribers`
    ADD COLUMN IF NOT EXISTS `token` VARCHAR(64) NULL AFTER `ip`;

-- Generează token pentru abonații existenți (fiecare unic)
UPDATE `newsletter_subscribers` SET `token` = SUBSTRING(MD5(RAND()), 1, 40) WHERE `token` IS NULL;

-- Index unic pe token
ALTER TABLE `newsletter_subscribers`
    ADD UNIQUE INDEX IF NOT EXISTS `newsletter_subscribers_token_unique` (`token`);

-- Tabel jobs pentru Laravel Queue
CREATE TABLE IF NOT EXISTS `jobs` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue`        VARCHAR(255)    NOT NULL,
    `payload`      LONGTEXT        NOT NULL,
    `attempts`     TINYINT UNSIGNED NOT NULL,
    `reserved_at`  INT UNSIGNED    DEFAULT NULL,
    `available_at` INT UNSIGNED    NOT NULL,
    `created_at`   INT UNSIGNED    NOT NULL,
    PRIMARY KEY (`id`),
    KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabel failed_jobs
CREATE TABLE IF NOT EXISTS `failed_jobs` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid`       VARCHAR(255)    NOT NULL,
    `connection` TEXT            NOT NULL,
    `queue`      TEXT            NOT NULL,
    `payload`    LONGTEXT        NOT NULL,
    `exception`  LONGTEXT        NOT NULL,
    `failed_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migrări înregistrate
INSERT IGNORE INTO `migrations` (`migration`, `batch`) VALUES
    ('2026_05_23_100000_create_newsletter_sends_table', 99),
    ('2026_05_23_200000_add_segment_to_newsletter_sends_table', 99),
    ('2026_05_23_220721_add_token_to_newsletter_subscribers_table', 99),
    ('2026_05_23_300000_enhance_newsletter_tables', 99);

-- ⚠️  Activare Queue pe server (SSH):
-- 1. Setează în .env:  QUEUE_CONNECTION=database
-- 2. Rulează: php artisan queue:work --daemon --sleep=3 --tries=1
-- 3. Sau configurează Supervisor pentru auto-restart.
