-- Green Room Broadcast - Minimal Database
-- Generated 2026-02-16 07:20:08 (Africa/Johannesburg)
-- Import into phpMyAdmin, then set DB_* in .env

CREATE DATABASE IF NOT EXISTS `green_room_broadcast` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `green_room_broadcast`;


-- Subscribers
CREATE TABLE `subscribers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `full_name` varchar(190) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `wa_phone_e164` varchar(40) DEFAULT NULL,
  `wa_opt_in` tinyint(1) NOT NULL DEFAULT 1,
  `email_opt_in` tinyint(1) NOT NULL DEFAULT 1,
  `branch` varchar(120) DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `last_opt_in_at` timestamp NULL DEFAULT NULL,
  `last_opt_out_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscribers_email_unique` (`email`),
  UNIQUE KEY `subscribers_wa_phone_e164_unique` (`wa_phone_e164`),
  KEY `subscribers_email_index` (`email`),
  KEY `subscribers_wa_phone_e164_index` (`wa_phone_e164`),
  KEY `subscribers_branch_index` (`branch`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campaigns
CREATE TABLE `campaigns` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `source` varchar(50) NOT NULL DEFAULT 'webhook',
  `source_url` varchar(500) DEFAULT NULL,
  `payload` json DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'queued',
  `total_targets` int unsigned NOT NULL DEFAULT 0,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaigns_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campaign Logs
CREATE TABLE `campaign_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `campaign_id` bigint unsigned NOT NULL,
  `subscriber_id` bigint unsigned NOT NULL,
  `channel` varchar(20) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'queued',
  `provider_message_id` varchar(190) DEFAULT NULL,
  `error_code` varchar(60) DEFAULT NULL,
  `error_message` text,
  `provider_payload` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `campaign_logs_unique` (`campaign_id`,`subscriber_id`,`channel`),
  KEY `campaign_logs_campaign_id_index` (`campaign_id`),
  KEY `campaign_logs_subscriber_id_index` (`subscriber_id`),
  KEY `campaign_logs_channel_index` (`channel`),
  KEY `campaign_logs_status_index` (`status`),
  KEY `campaign_logs_provider_message_id_index` (`provider_message_id`),
  CONSTRAINT `campaign_logs_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_logs_subscriber_id_foreign` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queue tables (database queue)
CREATE TABLE `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 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `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 COLLATE=utf8mb4_unicode_ci;

-- Sample subscriber (optional)
INSERT INTO `subscribers` (`full_name`,`email`,`wa_phone_e164`,`wa_opt_in`,`email_opt_in`,`branch`,`tags`,`created_at`,`updated_at`)
VALUES ('Test Subscriber','test@example.com','+27821234567',1,1,'Durban',JSON_ARRAY('test'),NOW(),NOW());
