-- 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`;

SET NAMES utf8mb4;
SET time_zone = "+02:00";

-- Admins
DROP TABLE IF EXISTS `admins`;
CREATE TABLE `admins` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password` varchar(255) NOT NULL,
  `is_super_admin` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admins_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `admins` (`name`,`email`,`password`,`is_super_admin`,`is_active`,`created_at`,`updated_at`)
VALUES ('Demo Admin','admin@demo.co.za','$2y$10$sTqS9K1elJkRGLUANs0Fi.V4GjogIMQunB3m6tubrmxMqTPOWEo3a',1,1,NOW(),NOW());

-- Subscribers
DROP TABLE IF EXISTS `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
DROP TABLE IF EXISTS `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
DROP TABLE IF EXISTS `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;

-- App Settings
DROP TABLE IF EXISTS `app_settings`;
CREATE TABLE `app_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(120) NOT NULL,
  `value` longtext,
  `updated_by_admin_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_settings_key_unique` (`key`),
  KEY `app_settings_updated_by_admin_id_index` (`updated_by_admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queue tables (database queue)
DROP TABLE IF EXISTS `jobs`;
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;

DROP TABLE IF EXISTS `failed_jobs`;
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 settings keys (optional)
INSERT IGNORE INTO `app_settings` (`key`, `value`, `created_at`, `updated_at`) VALUES
('wa_graph_version','v20.0',NOW(),NOW()),
('wa_phone_number_id','',NOW(),NOW()),
('wa_access_token','',NOW(),NOW()),
('meta_app_secret','',NOW(),NOW()),
('smtp_host','',NOW(),NOW()),
('smtp_port','587',NOW(),NOW()),
('smtp_username','',NOW(),NOW()),
('smtp_password','',NOW(),NOW()),
('smtp_encryption','tls',NOW(),NOW()),
('mail_from_address','',NOW(),NOW()),
('mail_from_name','Green Room Broadcast',NOW(),NOW());

-- 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());