-- ============================================================================ -- Roote PR System - Complete Database Dump with Tanzanian Demo Data -- ============================================================================ -- Generated for: Roote Communication (Tanzania) -- Engine: MySQL -- ============================================================================ SET FOREIGN_KEY_CHECKS = 0; -- ----------------------------------------------------------- -- 1. AGENCIES -- ----------------------------------------------------------- DROP TABLE IF EXISTS `agencies`; CREATE TABLE `agencies` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `slug` VARCHAR(255) NOT NULL UNIQUE, `plan` VARCHAR(255) NOT NULL DEFAULT 'TRIAL', `subscription_status` VARCHAR(255) NOT NULL DEFAULT 'TRIALING', `status` VARCHAR(255) NOT NULL DEFAULT 'PENDING', `is_suspended` TINYINT(1) NOT NULL DEFAULT 0, `suspended_at` TIMESTAMP NULL DEFAULT NULL, `suspended_reason` VARCHAR(255) DEFAULT NULL, `logo_url` VARCHAR(255) DEFAULT NULL, `trial_ends_at` TIMESTAMP NULL DEFAULT NULL, `current_period_end` TIMESTAMP NULL DEFAULT NULL, `payment_provider` VARCHAR(255) DEFAULT NULL, `payment_provider_customer_id` VARCHAR(255) DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `agencies` (`id`, `name`, `slug`, `plan`, `subscription_status`, `status`, `is_suspended`, `created_at`, `updated_at`) VALUES ('a0000000-0000-0000-0000-000000000001', 'Roote Communication', 'roote-communication', 'AGENCY', 'ACTIVE', 'ACTIVE', 0, '2026-01-01 00:00:00', '2026-07-01 00:00:00'), ('a0000000-0000-0000-0000-000000000002', 'Empirical Media Tanzania', 'empirical-media', 'PROFESSIONAL', 'TRIALING', 'PENDING', 0, '2026-07-14 00:00:00', '2026-07-14 00:00:00'), ('a0000000-0000-0000-0000-000000000003', 'Dar es Salaam PR Hub', 'dar-pr-hub', 'STARTER', 'ACTIVE', 'ACTIVE', 0, '2026-06-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 2. SITE SETTINGS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `site_settings`; CREATE TABLE `site_settings` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `site_name` VARCHAR(255) NOT NULL DEFAULT 'Roote PR System', `logo_url` VARCHAR(255) DEFAULT NULL, `description` TEXT DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `site_settings` (`id`, `site_name`, `description`, `created_at`, `updated_at`) VALUES (1, 'Roote PR System', 'Tanzania Premier Public Relations Management Platform', '2026-01-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 3. SUBSCRIPTIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `subscriptions`; CREATE TABLE `subscriptions` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `provider` VARCHAR(255) NOT NULL, `provider_subscription_id` VARCHAR(255) NOT NULL UNIQUE, `plan` VARCHAR(255) NOT NULL, `status` VARCHAR(255) NOT NULL, `current_period_start` TIMESTAMP NOT NULL, `current_period_end` TIMESTAMP NULL DEFAULT NULL, `trial_ends_at` TIMESTAMP NULL DEFAULT NULL, `cancel_at_period_end` TINYINT(1) NOT NULL DEFAULT 0, `metadata` JSON DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, INDEX `idx_subscriptions_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `subscriptions` (`id`, `agency_id`, `provider`, `provider_subscription_id`, `plan`, `status`, `current_period_start`, `current_period_end`, `cancel_at_period_end`, `metadata`, `created_at`, `updated_at`) VALUES ('d1000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'FLUTTERWAVE', 'fw_sub_roote_001', 'AGENCY', 'ACTIVE', '2026-01-01 00:00:00', '2027-01-01 00:00:00', 0, '{"billing_cycle": "annual", "currency": "TZS", "amount": 12000000}', '2026-01-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 4. PAYMENT TRANSACTIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `payment_transactions`; CREATE TABLE `payment_transactions` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `provider` VARCHAR(255) NOT NULL, `provider_transaction_id` VARCHAR(255) DEFAULT NULL, `type` VARCHAR(255) NOT NULL, `amount` DECIMAL(10,2) NOT NULL, `currency` VARCHAR(255) NOT NULL DEFAULT 'USD', `status` VARCHAR(255) NOT NULL, `metadata` JSON DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, INDEX `idx_payment_transactions_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `payment_transactions` (`id`, `agency_id`, `provider`, `provider_transaction_id`, `type`, `amount`, `currency`, `status`, `metadata`, `created_at`) VALUES ('d2000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'FLUTTERWAVE', 'fw_txn_roote_001', 'CHECKOUT', 12000000.00, 'TZS', 'SUCCEEDED', '{"channel": "card", "installments": 1}', '2026-01-01 00:00:00'), ('d2000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'FLUTTERWAVE', 'fw_txn_roote_002', 'RENEWAL', 12000000.00, 'TZS', 'SUCCEEDED', '{"channel": "card", "billing_cycle": "annual"}', '2026-07-01 09:00:00'), ('d2000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000003', 'FLUTTERWAVE', 'fw_txn_dar_001', 'CHECKOUT', 3000000.00, 'TZS', 'SUCCEEDED', '{"channel": "mobile_money", "provider": "M-Pesa"}', '2026-06-01 10:30:00'), ('d2000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000003', 'FLUTTERWAVE', 'fw_txn_dar_002', 'CHECKOUT', 3000000.00, 'TZS', 'PENDING', '{"channel": "mobile_money", "provider": "TigoPesa"}', '2026-07-01 10:00:00'), ('d2000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000001', 'FLUTTERWAVE', 'fw_txn_refund_001', 'REFUND', 500000.00, 'TZS', 'REFUNDED', '{"reason": "Duplicate charge", "original_transaction": "fw_txn_roote_003"}', '2026-06-15 14:00:00'); -- ----------------------------------------------------------- -- 5. USERS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `full_name` VARCHAR(255) NOT NULL, `role` VARCHAR(255) NOT NULL DEFAULT 'VIEWER', `is_active` TINYINT(1) NOT NULL DEFAULT 1, `is_super_admin` TINYINT(1) NOT NULL DEFAULT 0, `remember_token` VARCHAR(100) DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `users` (`id`, `agency_id`, `email`, `password`, `full_name`, `role`, `is_active`, `is_super_admin`, `created_at`, `updated_at`) VALUES ('b0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'superadmin@empiricalcommunication.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Gabito (Super Admin)', 'OWNER', 1, 1, '2026-01-01 00:00:00', '2026-07-01 00:00:00'), ('b0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'admin@roote.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Admin User', 'ADMIN', 1, 0, '2026-01-01 00:00:00', '2026-07-01 00:00:00'), ('b0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'am@roote.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Account Manager', 'ACCOUNT_MANAGER', 1, 0, '2026-01-01 00:00:00', '2026-07-01 00:00:00'), ('b0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'viewer@roote.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Viewer User', 'VIEWER', 1, 0, '2026-01-01 00:00:00', '2026-07-01 00:00:00'), ('b0000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000002', 'owner@empiricalmedia.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Sarah Mwangi (Owner)', 'OWNER', 1, 0, '2026-07-14 00:00:00', '2026-07-14 00:00:00'), ('b0000000-0000-0000-0000-000000000006', 'a0000000-0000-0000-0000-000000000003', 'admin@darprhub.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Juma Ali (Admin)', 'ADMIN', 1, 0, '2026-06-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 6. PERSONAL ACCESS TOKENS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `personal_access_tokens`; CREATE TABLE `personal_access_tokens` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `tokenable_type` VARCHAR(255) NOT NULL, `tokenable_id` CHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `token` VARCHAR(64) NOT NULL UNIQUE, `abilities` TEXT DEFAULT NULL, `last_used_at` TIMESTAMP NULL DEFAULT NULL, `expires_at` TIMESTAMP NULL DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `personal_access_tokens` (`id`, `tokenable_type`, `tokenable_id`, `name`, `token`, `abilities`, `last_used_at`, `expires_at`, `created_at`, `updated_at`) VALUES (1, 'App\Models\User', 'b0000000-0000-0000-0000-000000000001', 'api-token', '9a8b7c6d5e4f3a2b1c0d9e8f7a6b5c4d3e2f1a0b9c8d7e6f5a4b3c2d1e0f1a2b', '["*"]', '2026-07-14 00:00:00', '2027-07-14 00:00:00', '2026-01-01 00:00:00', '2026-07-14 00:00:00'); -- ----------------------------------------------------------- -- 7. MEDIA -- ----------------------------------------------------------- DROP TABLE IF EXISTS `media`; CREATE TABLE `media` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `model_type` VARCHAR(255) NOT NULL, `model_id` CHAR(36) NOT NULL, `uuid` CHAR(36) DEFAULT NULL UNIQUE, `collection_name` VARCHAR(255) NOT NULL, `name` VARCHAR(255) NOT NULL, `file_name` VARCHAR(255) NOT NULL, `mime_type` VARCHAR(255) DEFAULT NULL, `disk` VARCHAR(255) NOT NULL, `conversions_disk` VARCHAR(255) DEFAULT NULL, `size` BIGINT UNSIGNED NOT NULL, `manipulations` JSON NOT NULL, `custom_properties` JSON NOT NULL, `generated_conversions` JSON NOT NULL, `responsive_images` JSON NOT NULL, `order_column` INT UNSIGNED DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `media` (`id`, `model_type`, `model_id`, `uuid`, `collection_name`, `name`, `file_name`, `mime_type`, `disk`, `conversions_disk`, `size`, `manipulations`, `custom_properties`, `generated_conversions`, `responsive_images`, `order_column`, `created_at`, `updated_at`) VALUES (1, 'App\Models\Client', 'c0000000-0000-0000-0000-000000000001', NULL, 'logo', 'sbl-logo', 'sbl-logo.png', 'image/png', 'public', 'public', 24576, '[]', '[]', '[]', '[]', 1, '2026-01-15 00:00:00', '2026-01-15 00:00:00'), (2, 'App\Models\Client', 'c0000000-0000-0000-0000-000000000002', NULL, 'logo', 'vodacom-logo', 'vodacom-logo.png', 'image/png', 'public', 'public', 30720, '[]', '[]', '[]', '[]', 2, '2026-01-15 00:00:00', '2026-01-15 00:00:00'); -- ----------------------------------------------------------- -- 8. REFRESH TOKENS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `refresh_tokens`; CREATE TABLE `refresh_tokens` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `user_id` CHAR(36) NOT NULL, `token_hash` VARCHAR(255) NOT NULL, `expires_at` TIMESTAMP NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `revoked_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_refresh_tokens_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `refresh_tokens` (`id`, `user_id`, `token_hash`, `expires_at`, `created_at`, `revoked_at`) VALUES ('d3000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', 'hash_sample_refresh_token_abc123', '2027-07-14 00:00:00', '2026-07-14 00:00:00', NULL); -- ----------------------------------------------------------- -- 9. PASSWORD RESET TOKENS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `password_reset_tokens`; CREATE TABLE `password_reset_tokens` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `user_id` CHAR(36) NOT NULL, `token_hash` VARCHAR(255) NOT NULL, `expires_at` TIMESTAMP NOT NULL, `used_at` TIMESTAMP NULL DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_password_reset_tokens_token_hash` (`token_hash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `password_reset_tokens` (`id`, `user_id`, `token_hash`, `expires_at`, `used_at`, `created_at`) VALUES ('d4000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', 'hash_sample_reset_token_xyz789', '2026-07-20 00:00:00', '2026-07-14 00:00:00', '2026-07-14 00:00:00'); -- ----------------------------------------------------------- -- 10. CLIENTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `clients`; CREATE TABLE `clients` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `industry` VARCHAR(255) DEFAULT NULL, `contact_name` VARCHAR(255) DEFAULT NULL, `contact_email` VARCHAR(255) DEFAULT NULL, `contact_phone` VARCHAR(255) DEFAULT NULL, `notes` TEXT DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, INDEX `idx_clients_agency_id` (`agency_id`), INDEX `idx_clients_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `clients` (`id`, `agency_id`, `name`, `industry`, `contact_name`, `contact_email`, `contact_phone`, `notes`, `created_at`, `updated_at`) VALUES ('c0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Serengeti Breweries Limited', 'Beverages & Alcohol', 'John Mwale', 'john.mwale@sbl.co.tz', '+255 712 100 200', 'Major client - premium beer brand. Located in Dar es Salaam.', '2026-01-10 00:00:00', '2026-06-15 00:00:00'), ('c0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Vodacom Tanzania', 'Telecommunications', 'Amina Juma', 'amina.juma@vodacom.co.tz', '+255 713 200 300', 'Telecommunications giant. Multiple campaigns ongoing.', '2026-01-15 00:00:00', '2026-06-20 00:00:00'), ('c0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'NMB Bank PLC', 'Banking & Finance', 'Peter Msuya', 'peter.msuya@nmb.co.tz', '+255 714 300 400', 'Top-tier bank. Focus on SME banking.', '2026-02-01 00:00:00', '2026-06-10 00:00:00'), ('c0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'Azam Media Group', 'Media & Broadcasting', 'Fatma Salim', 'fatma.salim@azam.tv', '+255 715 400 500', 'Media conglomerate. New sports channel launch.', '2026-03-01 00:00:00', '2026-06-25 00:00:00'); -- ----------------------------------------------------------- -- 11. CAMPAIGNS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `campaigns`; CREATE TABLE `campaigns` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `goal` TEXT DEFAULT NULL, `budget` DOUBLE DEFAULT NULL, `audience` TEXT DEFAULT NULL, `status` VARCHAR(255) NOT NULL DEFAULT 'DRAFT', `start_date` TIMESTAMP NOT NULL, `end_date` TIMESTAMP NULL DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, INDEX `idx_campaigns_agency_id` (`agency_id`), INDEX `idx_campaigns_client_id` (`client_id`), INDEX `idx_campaigns_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `campaigns` (`id`, `agency_id`, `client_id`, `name`, `goal`, `budget`, `audience`, `status`, `start_date`, `end_date`, `created_at`, `updated_at`) VALUES ('d0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'Serengeti Premium Lager - 2026 Brand Refresh', 'Refresh the brand image of Serengeti Premium Lager and generate positive media coverage across Tanzanian markets', 150000000, 'Tanzanian beer consumers aged 25-45, urban professionals in Dar es Salaam, Arusha, Mwanza, and Zanzibar', 'ACTIVE', '2026-03-01 00:00:00', '2026-09-30 00:00:00', '2026-02-15 00:00:00', '2026-06-30 00:00:00'), ('d0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'Vodacom M-Pawa - Financial Inclusion Drive', 'Promote financial inclusion through M-Pawa mobile money services, targeting unbanked populations in rural Tanzania', 95000000, 'Rural Tanzanians, small business owners, women entrepreneurs, youth aged 18-35', 'COMPLETED', '2026-01-15 00:00:00', '2026-06-30 00:00:00', '2026-01-10 00:00:00', '2026-06-30 00:00:00'), ('d0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'NMB SME Banking Week 2026', 'Promote NMB Bank SME banking products and services during the annual SME Banking Week', 45000000, 'Small and medium enterprise owners, entrepreneurs, business associations across Tanzania', 'ACTIVE', '2026-07-01 00:00:00', '2026-08-15 00:00:00', '2026-05-20 00:00:00', '2026-07-10 00:00:00'), ('d0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000004', 'Azam TV - New Sports Channel Launch', 'Generate buzz and viewership for Azam Media new dedicated sports channel', 75000000, 'Sports fans aged 18-55, football enthusiasts, sports bar owners, cable TV subscribers', 'PLANNING', '2026-08-01 00:00:00', '2026-12-31 00:00:00', '2026-06-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 12. CAMPAIGN PLANS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `campaign_plans`; CREATE TABLE `campaign_plans` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `campaign_id` CHAR(36) NOT NULL, `agency_id` CHAR(36) NOT NULL, `strategy` TEXT NOT NULL, `timeline` JSON NOT NULL, `media_plan` JSON NOT NULL, `kpis` JSON NOT NULL, `risk_analysis` TEXT NOT NULL, `generated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE CASCADE, INDEX `idx_campaign_plans_campaign_id` (`campaign_id`), INDEX `idx_campaign_plans_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `campaign_plans` (`id`, `campaign_id`, `agency_id`, `strategy`, `timeline`, `media_plan`, `kpis`, `risk_analysis`, `generated_at`) VALUES ('ca000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Multi-channel brand refresh campaign leveraging traditional media (radio, TV) and digital platforms (Instagram, TikTok) to reach urban and peri-urban consumers. Key focus on heritage messaging combined with modern visual identity. Press launches in Dar es Salaam, Arusha, and Mwanza.', '{"phases": [{"phase": "Research & Consumer Insights", "start": "2026-03-01", "end": "2026-03-15"}, {"phase": "Creative Development", "start": "2026-03-16", "end": "2026-04-15"}, {"phase": "Media Buying & Placement", "start": "2026-04-16", "end": "2026-05-01"}, {"phase": "Launch & Activation", "start": "2026-05-01", "end": "2026-05-15"}, {"phase": "Sustain & Monitor", "start": "2026-05-16", "end": "2026-09-30"}]}', '{"channels": [{"name": "Radio", "allocation_percent": 35, "stations": ["Clouds FM", "East Africa Radio", "Radio One", "Kiss FM"]}, {"name": "Television", "allocation_percent": 30, "stations": ["Azam TV", "ITV", "Channel 10"]}, {"name": "Digital", "allocation_percent": 25, "platforms": ["Instagram", "TikTok", "Facebook"]}, {"name": "Print & OOH", "allocation_percent": 10, "outlets": ["The Citizen", "Mwananchi", "Billboards in Dar"]}]}', '{"primary": [{"metric": "Media Impressions", "target": "10 million"}, {"metric": "Brand Sentiment", "target": "80% positive"}, {"metric": "Press Coverage", "target": "25+ articles"}], "secondary": [{"metric": "Social Media Engagement", "target": "500k interactions"}, {"metric": "Event Attendance", "target": "500+ guests"}]}', 'Risk 1: Negative consumer reaction to new branding. Mitigation: Pre-launch focus groups with target consumers. Risk 2: Competitor backlash or spoiler campaigns. Mitigation: Competitive monitoring and rapid response protocol. Risk 3: Regulatory issues with alcohol advertising. Mitigation: Legal review of all materials before publishing.', '2026-02-20 00:00:00'), ('ca000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Community-driven financial inclusion campaign focusing on education and empowerment. Use local ambassadors, village outreach programs, and Swahili-language content to build trust in M-Pawa services.', '{"phases": [{"phase": "Market Assessment", "start": "2026-01-15", "end": "2026-02-01"}, {"phase": "Ambassador Recruitment", "start": "2026-02-01", "end": "2026-02-20"}, {"phase": "Community Outreach", "start": "2026-02-21", "end": "2026-05-30"}, {"phase": "Impact Measurement", "start": "2026-06-01", "end": "2026-06-30"}]}', '{"channels": [{"name": "Community Events", "allocation_percent": 40, "regions": ["Dar es Salaam", "Mbeya", "Mwanza", "Arusha", "Dodoma"]}, {"name": "Radio", "allocation_percent": 30, "stations": ["TBC FM", "Clouds FM", "Radio Maria"]}, {"name": "Digital", "allocation_percent": 20, "platforms": ["Facebook", "WhatsApp", "YouTube"]}, {"name": "Print", "allocation_percent": 10, "outlets": ["Daily News", "Mwananchi", "Habari Leo"]}]}', '{"primary": [{"metric": "New M-Pawa Registrations", "target": "500,000"}, {"metric": "Campaign Reach", "target": "5 million Tanzanians"}, {"metric": "Media Coverage", "target": "40+ stories"}], "secondary": [{"metric": "Community Events Held", "target": "50"}, {"metric": "Ambassadors Activated", "target": "100"}]}', 'Risk 1: Low digital literacy in target rural areas. Mitigation: Use radio as primary channel with simple USSD demonstrations. Risk 2: Agent network capacity issues. Mitigation: Pre-coordination with Vodacom agent network team. Risk 3: Security concerns about mobile money. Mitigation: Educational content on security features.', '2026-01-12 00:00:00'), ('ca000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'Targeted B2B and B2C campaign to position NMB as the premier SME bank in Tanzania. Leverage SME Banking Week as anchor event with supporting press coverage and digital campaigns.', '{"phases": [{"phase": "Pre-Event Promotion", "start": "2026-07-01", "end": "2026-07-20"}, {"phase": "SME Banking Week Events", "start": "2026-07-21", "end": "2026-07-28"}, {"phase": "Post-Event Follow-up", "start": "2026-07-29", "end": "2026-08-15"}]}', '{"channels": [{"name": "Events", "allocation_percent": 45, "venues": ["NMB Head Office Dar", "Julius Nyerere Convention Centre"]}, {"name": "Print & Business Media", "allocation_percent": 25, "outlets": ["The Citizen", "Business Week", "Daily News"]}, {"name": "Digital & Social", "allocation_percent": 20, "platforms": ["LinkedIn", "Twitter", "Instagram", "Email"]}, {"name": "Radio", "allocation_percent": 10, "stations": ["East Africa Radio", "TBC FM"]}]}', '{"primary": [{"metric": "SME Loan Applications", "target": "1,000+ during week"}, {"metric": "Event Attendance", "target": "2,000+ SME owners"}, {"metric": "Media Coverage", "target": "20+ stories"}], "secondary": [{"metric": "Social Media Reach", "target": "2 million"}, {"metric": "New SME Accounts Opened", "target": "500"}]}', 'Risk 1: Low SME turnout due to competing events. Mitigation: Early registration drive with incentives. Risk 2: Negative feedback from SMEs. Mitigation: Dedicated feedback desk at events. Risk 3: Media focusing on negative banking stories. Mitigation: Proactive positive story placement before event.', '2026-05-25 00:00:00'), ('ca000000-0000-0000-0000-000000000004', 'd0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'High-visibility launch campaign for Azam TV dedicated sports channel. Target sports fans through influencer partnerships, sports club collaborations, and aggressive social media marketing.', '{"phases": [{"phase": "Pre-Launch Teaser", "start": "2026-08-01", "end": "2026-09-01"}, {"phase": "Launch Week", "start": "2026-09-01", "end": "2026-09-07"}, {"phase": "Sustain & Grow", "start": "2026-09-08", "end": "2026-12-31"}]}', '{"channels": [{"name": "Television", "allocation_percent": 30, "channels": ["Azam TV existing channels", "ITV", "Channel 10"]}, {"name": "Social Media", "allocation_percent": 35, "platforms": ["Instagram", "TikTok", "Twitter", "YouTube"]}, {"name": "Influencer Marketing", "allocation_percent": 20, "type": ["Sports personalities", "Football clubs", "Fitness influencers"]}, {"name": "Outdoor & Events", "allocation_percent": 15, "locations": ["Dar es Salaam", "Arusha", "Mwanza", "Mbeya"]}]}', '{"primary": [{"metric": "New Subscribers", "target": "100,000"}, {"metric": "Channel Reach", "target": "1 million viewers"}, {"metric": "Social Media Buzz", "target": "5 million impressions"}], "secondary": [{"metric": "Influencer Partnerships", "target": "20+"}, {"metric": "Press Coverage", "target": "30+ stories"}]}', 'Risk 1: Competition from DStv and other sports broadcasters. Mitigation: Exclusive local content and competitive pricing. Risk 2: Technical issues during launch. Mitigation: Extended testing period and 24/7 tech support. Risk 3: Low content library at launch. Mitigation: Secure multiple league broadcasting rights.', '2026-06-05 00:00:00'); -- ----------------------------------------------------------- -- 13. PRESS RELEASES -- ----------------------------------------------------------- DROP TABLE IF EXISTS `press_releases`; CREATE TABLE `press_releases` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) NOT NULL, `campaign_id` CHAR(36) DEFAULT NULL, `prompt` TEXT NOT NULL, `headline` VARCHAR(255) NOT NULL, `body_english` LONGTEXT NOT NULL, `body_swahili` LONGTEXT NOT NULL, `quotes` JSON NOT NULL, `email_subject` VARCHAR(255) NOT NULL, `social_captions` JSON NOT NULL, `status` VARCHAR(255) NOT NULL DEFAULT 'DRAFT', `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE SET NULL, INDEX `idx_press_releases_agency_id` (`agency_id`), INDEX `idx_press_releases_client_id` (`client_id`), INDEX `idx_press_releases_campaign_id` (`campaign_id`), INDEX `idx_press_releases_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `press_releases` (`id`, `agency_id`, `client_id`, `campaign_id`, `prompt`, `headline`, `body_english`, `body_swahili`, `quotes`, `email_subject`, `social_captions`, `status`, `created_at`, `updated_at`) VALUES ('e0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'Generate a press release announcing the brand refresh of Serengeti Premium Lager for 2026', 'Serengeti Premium Lager Unveils Bold New Look for 2026', 'Dar es Salaam, Tanzania - Serengeti Breweries Limited (SBL) has today unveiled a striking new look for its flagship brand, Serengeti Premium Lager, marking the first major visual overhaul in over a decade. The redesigned packaging and branding identity reflect the company commitment to innovation while honoring its rich Tanzanian heritage. The new design features a modernized label with vibrant colors that capture the spirit of the Serengeti, combined with contemporary typography that appeals to a new generation of beer drinkers. The iconic Serengeti lion remains at the heart of the design, now rendered in a bold, stylized form. This refresh represents a significant milestone for Serengeti Premium Lager. We have listened to our consumers and created a look that respects our legacy while speaking to the modern Tanzanian beer enthusiast. The new packaging will roll out across all retail channels starting May 2026, accompanied by a comprehensive marketing campaign spanning television, radio, digital platforms, and out-of-home advertising. Serengeti Breweries has invested over TZS 150 million in the brand refresh initiative.', 'Dar es Salaam, Tanzania - Serengeti Breweries Limited (SBL) leo imezindua mwonekano mpya wa kisasa wa bidhaa yake ya Serengeti Premium Lager, ikiwa ni mabadiliko makubwa ya kwanza kwa zaidi ya muongo mmoja. Muundo mpya unaonyesha kujitolea kwa kampuni katika uvumbuzi huku ukiheshimu urithi wake wa Kitanzania. Muundo huo mpya una lebo ya kisasa yenye rangi nyororo zinazovutia roho ya Serengeti, pamoja na maandishi ya kisasa yanayovutia kizazi kipya cha wanywaji bia. Simba wa Serengeti anayetambulika bado yuko katikati ya muundo, sasa akiwa amechorwa kwa namna ya kisasa na yenye ujasiri. Tumewasikiliza wateja wetu na kuunda mwonekano unaoheshimu urithi wetu huku ukizungumza na Mpenda bia wa kisasa wa Kitanzania.', '[{"author": "John Mwale", "title": "Marketing Director, Serengeti Breweries Limited", "quote": "This refresh represents a significant milestone for Serengeti Premium Lager. We have listened to our consumers and created a look that respects our legacy while speaking to the modern Tanzanian beer enthusiast."}, {"author": "Sarah Johnson", "title": "Brand Manager, Serengeti Premium Lager", "quote": "Tanzania favorite beer deserves a look that matches its premium quality. We are confident that consumers will love the fresh new appearance."}]', 'PRESS RELEASE: Serengeti Premium Lager Unveils Bold New Look for 2026', '{"twitter": "The King of Beers gets a royal makeover! @SerengetiBrew unveils a bold new look for Serengeti Premium Lager. #SerengetiPremium #BrandRefresh #Tanzania", "facebook": "We are proud to unveil the new look of Serengeti Premium Lager! After more than a decade, Tanzania favorite beer is getting a fresh, modern makeover.", "instagram": "A fresh new look for Tanzania favorite beer! The Serengeti Premium 2026 Brand Refresh is here. #SerengetiPremium", "linkedin": "Serengeti Breweries Limited is pleased to announce a comprehensive brand refresh for our flagship Serengeti Premium Lager."}', 'DRAFT', '2026-04-01 00:00:00', '2026-04-01 00:00:00'), ('e0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'Generate a feature story about Serengeti Premium Lager modernization', 'Tanzania Favorite Brew Gets a Modern Makeover', 'For over two decades, Serengeti Premium Lager has been more than just a beer - it has been a symbol of Tanzanian pride and craftsmanship. Now, as the brand enters its next chapter, Serengeti Breweries Limited is giving its flagship product a sophisticated new look that bridges heritage and modernity. The journey to the new design began with extensive consumer research across Tanzania major urban centers. Focus groups in Dar es Salaam, Arusha, Mwanza, and Zanzibar provided invaluable insights into what beer drinkers love about Serengeti Premium and what they hoped to see in its evolution. Consumers told us they wanted a brand that felt both premium and authentically Tanzanian. The result is a design that retains the beloved Serengeti lion while introducing a sleeker bottle shape, richer gold accents, and a label that tells the story of Tanzania natural beauty.', 'Kwa zaidi ya miongo miwili, Serengeti Premium Lager imekuwa zaidi ya bia tu - imekuwa ishara ya fahari ya Kitanzania na ufundi bora. Sasa, brand hii inapoingia katika sura yake mpya, Serengeti Breweries Limited inaipa bidhaa yake mwonekano mpya wa kisasa unaounganisha urithi na usasa. Safari ya muundo mpya ilianza na utafiti wa kina wa watumiaji katika miji mikubwa ya Tanzania. Vikundi vya majadiliano Dar es Salaam, Arusha, Mwanza, na Zanzibar vilitoa maarifa muhimu kuhusu kile wanywaji bia wanachokipenda kuhusu Serengeti Premium na matarajio yao kwa mustakabali wake.', '[{"author": "Sarah Johnson", "title": "Brand Manager, Serengeti Premium Lager", "quote": "Consumers told us they wanted a brand that felt both premium and authentically Tanzanian. They love the lion and it is iconic. But they wanted the overall presentation to feel more contemporary."}, {"author": "Hassan Mkude", "title": "Owner, Safari Lounge Dar es Salaam", "quote": "The new look is classy and modern. It will stand out on any shelf."}]', 'FEATURE: Tanzania Favorite Brew Gets a Modern Makeover', '{"twitter": "From heritage to modern: The story behind @SerengetiBrew stunning new look. #SerengetiPremium #Tanzania", "facebook": "Did you know? The new Serengeti Premium design was shaped by feedback from over 2,000 Tanzanian beer lovers!", "instagram": "Behind the design: How Serengeti Premium new look came to life. #SerengetiPremium #PackagingDesign", "linkedin": "Brand refresh case study: How Serengeti Breweries modernized its flagship product while maintaining authentic Tanzanian heritage."}', 'APPROVED', '2026-04-15 00:00:00', '2026-04-20 00:00:00'), ('e0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000002', 'Generate press release announcing M-Pawa reaching 5 million users milestone', 'Vodacom M-Pawa Reaches 5 Million Tanzanians', 'Dar es Salaam, Tanzania - Vodacom Tanzania PLC has announced that its flagship mobile money service, M-Pawa, has reached an unprecedented milestone of 5 million active users across the country. The achievement underscores the platform critical role in advancing financial inclusion in Tanzania. Launched in 2015, M-Pawa has grown from a simple mobile money transfer service into a comprehensive financial ecosystem offering savings, loans, insurance, and merchant payment solutions. The platform has been particularly instrumental in reaching unbanked populations in rural and peri-urban areas. Key drivers of growth include the expansion of the agent network to over 200,000 agents nationwide, the introduction of M-Pawa Kwik (a simplified USSD interface), and partnerships with government agencies for digital disbursement of social benefits.', 'Dar es Salaam, Tanzania - Vodacom Tanzania PLC imetangaza kuwa huduma yake ya fedha za simu, M-Pawa, imefikia hatua muhimu ya watumiaji milioni 5 wanaotumia huduma hiyo kwa bidii nchini kote. Mafanikio haya yanaonesha jukumu muhimu la jukwaa hili katika kuendeleza ushirikishwaji wa kifedha Tanzania. Iliyozinduliwa mwaka 2015, M-Pawa imekua kutoka huduma rahisi ya kutuma fedha hadi mfumo kamili wa kifedha unaotoa akiba, mikopo, bima, na suluhisho za malipo kwa wafanyabiashara. Jukwaa hili limekuwa muhimu hasa kwa kufikia watu ambao hawakuwa na huduma za benki katika maeneo ya vijijini.', '[{"author": "Amina Juma", "title": "Head of Mobile Financial Services, Vodacom Tanzania", "quote": "Reaching 5 million active users is a testament to the trust Tanzanians have placed in M-Pawa."}, {"author": "Halima Mushi", "title": "Small Business Owner, Kariakoo Market", "quote": "M-Pawa changed my business completely. I can now receive payments digitally and apply for small loans. It is like having a bank in my pocket."}]', 'PRESS RELEASE: Vodacom M-Pawa Reaches 5 Million Tanzanians', '{"twitter": "Big news! @VodacomTZ M-Pawa has reached 5 MILLION active users across Tanzania! #MPawa #VodacomTZ", "facebook": "We are celebrating a huge milestone! 5 million Tanzanians now use M-Pawa for their financial needs.", "instagram": "5 million strong! M-Pawa has reached 5 million active users across Tanzania. #MPawa #VodacomTZ", "linkedin": "Vodacom Tanzania announces milestone: 5 million active M-Pawa users. Key driver of financial inclusion."}', 'SENT', '2026-06-15 00:00:00', '2026-06-15 00:00:00'), ('e0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000003', 'Generate press release announcing NMB SME Banking Week 2026', 'NMB Bank Launches SME Banking Week 2026', 'Dar es Salaam, Tanzania - NMB Bank PLC has officially launched its annual SME Banking Week 2026, a week-long event series designed to empower small and medium enterprises across Tanzania with financial knowledge, networking opportunities, and access to tailored banking solutions. The event, running from July 21 to July 28, 2026, will feature workshops, panel discussions, and one-on-one consultation sessions with NMB SME banking specialists. Key themes include digital transformation for SMEs, access to finance, export readiness, and sustainable business practices. SMEs are the backbone of Tanzania economy, contributing over 40% to the country GDP. Through SME Banking Week, NMB aims to provide business owners with the tools, knowledge, and financial solutions they need to grow and thrive.', 'Dar es Salaam, Tanzania - NMB Bank PLC imezindua rasmi Wiki yake ya Kila Mwaka ya Benki za SMEs 2026, mfululizo wa hafla za wiki nzima iliyoundwa kuwawezesha wafanyabiashara wadogo na wa kati (SMEs) kote Tanzania kwa maarifa ya kifedha, fursa za mitandao, na ufikiaji wa suluhisho maalum za benki. Hafla hiyo, itakayoanza Julai 21 hadi Julai 28, 2026, itajumuisha warsha, mijadala ya jopo, na vikao vya ushauri wa ana kwa ana na wataalamu wa benki za SMEs wa NMB. SMEs ni uti wa mgongo wa uchumi wa Tanzania, wakichangia zaidi ya 40% ya Pato la Taifa.', '[{"author": "Peter Msuya", "title": "Head of SME Banking, NMB Bank PLC", "quote": "SMEs are the backbone of Tanzania economy, contributing over 40% to the country GDP. Through SME Banking Week, we aim to provide business owners with the tools and knowledge they need to thrive."}]', 'PRESS RELEASE: NMB Bank Launches SME Banking Week 2026', '{"twitter": "SME owners, this one is for you! @NMBBank launches SME Banking Week 2026 - July 21-28. #NMBSMEWeek #SME #Tanzania", "facebook": "Calling all SME owners! NMB SME Banking Week 2026 is here! Join us from July 21-28 for workshops and exclusive loan packages.", "instagram": "Your business growth starts here! NMB SME Banking Week 2026 runs July 21-28. #NMBSMEWeek #SME", "linkedin": "NMB Bank announces the launch of SME Banking Week 2026, featuring workshops and special SME financing packages."}', 'DRAFT', '2026-06-20 00:00:00', '2026-06-25 00:00:00'); -- ----------------------------------------------------------- -- 14. JOURNALISTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `journalists`; CREATE TABLE `journalists` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `full_name` VARCHAR(255) NOT NULL, `media_house` VARCHAR(255) NOT NULL, `email` VARCHAR(255) DEFAULT NULL, `phone` VARCHAR(255) DEFAULT NULL, `beats` JSON NOT NULL, `preferred_contact_method` VARCHAR(255) NOT NULL DEFAULT 'EMAIL', `notes` TEXT DEFAULT NULL, `last_contacted_at` TIMESTAMP NULL DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, INDEX `idx_journalists_agency_id` (`agency_id`), INDEX `idx_journalists_full_name` (`full_name`), INDEX `idx_journalists_media_house` (`media_house`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `journalists` (`id`, `agency_id`, `full_name`, `media_house`, `email`, `phone`, `beats`, `preferred_contact_method`, `notes`, `last_contacted_at`, `created_at`, `updated_at`) VALUES ('60000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Juma Mwinyi', 'Mwananchi', 'juma.mwinyi@mwananchi.co.tz', '+255 716 100 101', '["Politics", "Business"]', 'EMAIL', 'Senior journalist covering politics and business news.', '2026-07-10 00:00:00', '2026-01-20 00:00:00', '2026-07-10 00:00:00'), ('60000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Amina Salum', 'The Citizen', 'amina.salum@thecitizen.co.tz', '+255 717 200 202', '["Technology", "Finance"]', 'EMAIL', 'Technology and finance reporter. Very responsive to pitches.', '2026-07-12 00:00:00', '2026-01-20 00:00:00', '2026-07-12 00:00:00'), ('60000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'Baraka Hussein', 'Mwanaspoti', 'baraka.hussein@mwanaspoti.co.tz', '+255 718 300 303', '["Sports"]', 'WHATSAPP', 'Sports journalist. Prefers WhatsApp communication.', '2026-06-20 00:00:00', '2026-02-10 00:00:00', '2026-06-20 00:00:00'), ('60000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'Neema Kajuna', 'Clouds Media', 'neema.kajuna@cloudsmedia.co.tz', '+255 719 400 404', '["Entertainment", "Lifestyle"]', 'EMAIL', 'Entertainment and lifestyle reporter. Great for feature stories.', '2026-07-05 00:00:00', '2026-02-15 00:00:00', '2026-07-05 00:00:00'), ('60000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000001', 'Godfrey Mushi', 'Daily News', 'godfrey.mushi@dailynews.co.tz', '+255 720 500 505', '["Agriculture", "Environment"]', 'EMAIL', 'Agriculture and environment correspondent based in Dodoma.', '2026-06-28 00:00:00', '2026-02-20 00:00:00', '2026-06-28 00:00:00'), ('60000000-0000-0000-0000-000000000006', 'a0000000-0000-0000-0000-000000000001', 'Zainab Ramadhani', 'East African Business Week', 'zainab.ramadhani@businesweek.co.tz', '+255 721 600 606', '["Business", "Economy"]', 'EMAIL', 'Business and economy journalist with pan-East Africa coverage.', '2026-07-08 00:00:00', '2026-03-01 00:00:00', '2026-07-08 00:00:00'); -- ----------------------------------------------------------- -- 15. JOURNALIST LISTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `journalist_lists`; CREATE TABLE `journalist_lists` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `description` TEXT DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, INDEX `idx_journalist_lists_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `journalist_lists` (`id`, `agency_id`, `name`, `description`, `created_at`, `updated_at`) VALUES ('70000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Tanzania Business Journalists', 'Key business and finance journalists from major Tanzanian publications', '2026-03-01 00:00:00', '2026-03-01 00:00:00'), ('70000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Dar es Salaam Press Corps', 'Journalists based in Dar es Salaam covering various beats', '2026-03-15 00:00:00', '2026-03-15 00:00:00'); -- ----------------------------------------------------------- -- 16. JOURNALIST LIST <-> JOURNALIST (PIVOT) -- ----------------------------------------------------------- DROP TABLE IF EXISTS `journalist_list_journalist`; CREATE TABLE `journalist_list_journalist` ( `journalist_list_id` CHAR(36) NOT NULL, `journalist_id` CHAR(36) NOT NULL, PRIMARY KEY (`journalist_list_id`, `journalist_id`), FOREIGN KEY (`journalist_list_id`) REFERENCES `journalist_lists`(`id`) ON DELETE CASCADE, FOREIGN KEY (`journalist_id`) REFERENCES `journalists`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `journalist_list_journalist` (`journalist_list_id`, `journalist_id`) VALUES ('70000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000001'), ('70000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000002'), ('70000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000005'), ('70000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000006'), ('70000000-0000-0000-0000-000000000002', '60000000-0000-0000-0000-000000000001'), ('70000000-0000-0000-0000-000000000002', '60000000-0000-0000-0000-000000000002'), ('70000000-0000-0000-0000-000000000002', '60000000-0000-0000-0000-000000000003'), ('70000000-0000-0000-0000-000000000002', '60000000-0000-0000-0000-000000000004'); -- ----------------------------------------------------------- -- 17. JOURNALIST INTERACTIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `journalist_interactions`; CREATE TABLE `journalist_interactions` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `journalist_id` CHAR(36) NOT NULL, `agency_id` CHAR(36) NOT NULL, `type` VARCHAR(255) NOT NULL, `summary` TEXT NOT NULL, `created_by_user_id` CHAR(36) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`journalist_id`) REFERENCES `journalists`(`id`) ON DELETE CASCADE, INDEX `idx_journalist_interactions_journalist_id` (`journalist_id`), INDEX `idx_journalist_interactions_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `journalist_interactions` (`id`, `journalist_id`, `agency_id`, `type`, `summary`, `created_by_user_id`, `created_at`) VALUES ('cb200000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'EMAIL_SENT', 'Follow-up email regarding Serengeti Premium brand refresh story pitch. Juma confirmed interest and requested additional product images.', 'b0000000-0000-0000-0000-000000000001', '2026-07-10 00:00:00'), ('cb200000-0000-0000-0000-000000000002', '60000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'CALL', 'Phone call with Amina to discuss Vodacom 5G launch angle. She expressed strong interest in an exclusive interview with Vodacom CTO.', 'b0000000-0000-0000-0000-000000000002', '2026-07-12 00:00:00'), ('cb200000-0000-0000-0000-000000000003', '60000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000001', 'MEETING', 'In-person meeting at Daily News offices in Dodoma. Discussed NMB agricultural finance story and potential collaboration on SME banking coverage.', 'b0000000-0000-0000-0000-000000000003', '2026-06-28 00:00:00'); -- ----------------------------------------------------------- -- 18. DISTRIBUTIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `distributions`; CREATE TABLE `distributions` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `press_release_id` CHAR(36) NOT NULL, `sent_by_user_id` CHAR(36) NOT NULL, `journalist_ids` JSON NOT NULL, `sent_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`press_release_id`) REFERENCES `press_releases`(`id`) ON DELETE CASCADE, INDEX `idx_distributions_agency_id` (`agency_id`), INDEX `idx_distributions_press_release_id` (`press_release_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `distributions` (`id`, `agency_id`, `press_release_id`, `sent_by_user_id`, `journalist_ids`, `sent_at`, `created_at`) VALUES ('cb000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'e0000000-0000-0000-0000-000000000003', 'b0000000-0000-0000-0000-000000000001', '["60000000-0000-0000-0000-000000000001","60000000-0000-0000-0000-000000000002","60000000-0000-0000-0000-000000000005","60000000-0000-0000-0000-000000000006"]', '2026-06-15 10:30:00', '2026-06-15 10:30:00'); -- ----------------------------------------------------------- -- 19. DISTRIBUTION EVENTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `distribution_events`; CREATE TABLE `distribution_events` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `distribution_id` CHAR(36) NOT NULL, `journalist_id` CHAR(36) NOT NULL, `type` VARCHAR(255) NOT NULL, `occurred_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`distribution_id`) REFERENCES `distributions`(`id`) ON DELETE CASCADE, INDEX `idx_distribution_events_distribution_id` (`distribution_id`), INDEX `idx_distribution_events_journalist_id` (`journalist_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `distribution_events` (`id`, `distribution_id`, `journalist_id`, `type`, `occurred_at`) VALUES ('cb100000-0000-0000-0000-000000000001', 'cb000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000001', 'DELIVERED', '2026-06-15 10:31:00'), ('cb100000-0000-0000-0000-000000000002', 'cb000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000002', 'DELIVERED', '2026-06-15 10:31:00'), ('cb100000-0000-0000-0000-000000000003', 'cb000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000005', 'DELIVERED', '2026-06-15 10:32:00'), ('cb100000-0000-0000-0000-000000000004', 'cb000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000006', 'DELIVERED', '2026-06-15 10:32:00'), ('cb100000-0000-0000-0000-000000000005', 'cb000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000002', 'OPENED', '2026-06-15 11:15:00'), ('cb100000-0000-0000-0000-000000000006', 'cb000000-0000-0000-0000-000000000001', '60000000-0000-0000-0000-000000000006', 'OPENED', '2026-06-16 08:45:00'); -- ----------------------------------------------------------- -- 20. CRISIS SESSIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `crisis_sessions`; CREATE TABLE `crisis_sessions` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) NOT NULL, `created_by_user_id` CHAR(36) NOT NULL, `situation_description` TEXT NOT NULL, `result` JSON NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, INDEX `idx_crisis_sessions_agency_id` (`agency_id`), INDEX `idx_crisis_sessions_client_id` (`client_id`), INDEX `idx_crisis_sessions_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `crisis_sessions` (`id`, `agency_id`, `client_id`, `created_by_user_id`, `situation_description`, `result`, `created_at`) VALUES ('80000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', 'Unsubstantiated rumors circulating on social media that Serengeti Breweries is planning a product recall due to quality concerns. The rumors originated from an anonymous Facebook post and have been shared over 5,000 times. The brand needs a rapid response strategy.', '{"key_messages": ["Serengeti Breweries prioritizes consumer safety above all else", "All Serengeti products meet TBS quality standards", "The rumored product recall is completely false and unfounded"], "stakeholders": ["CEO - Mustapha Omari", "PR Team - Roote Communication", "Legal Team - SBL Legal Department", "TBS - Regulatory Affairs"], "actions": ["Issue official statement on all SBL social media channels within 2 hours", "Direct engagement with top 10 social media influencers to counter narrative", "Press release to major Tanzanian news outlets", "Monitor social media sentiment hourly for next 48 hours"], "budget_estimate": "TZS 8,500,000"}', '2026-04-20 00:00:00'); -- ----------------------------------------------------------- -- 21. NEWS ANGLE SETS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `news_angle_sets`; CREATE TABLE `news_angle_sets` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) NOT NULL, `campaign_id` CHAR(36) DEFAULT NULL, `prompt` TEXT NOT NULL, `result` JSON NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, INDEX `idx_news_angle_sets_agency_id` (`agency_id`), INDEX `idx_news_angle_sets_client_id` (`client_id`), INDEX `idx_news_angle_sets_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `news_angle_sets` (`id`, `agency_id`, `client_id`, `campaign_id`, `prompt`, `result`, `created_at`) VALUES ('90000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000002', 'Generate 5 news angles for the launch of Vodacom 5G services in Tanzania', '{"angles": [{"title": "5G Revolution in Tanzania: Vodacom Leads the Digital Charge", "description": "Vodacom is first to launch commercial 5G in Tanzania."}, {"title": "Bridging the Digital Divide: 5G for Rural Tanzania", "description": "How 5G can bring high-speed internet to underserved rural areas."}, {"title": "Tanzania Tech Economy Boost from 5G", "description": "5G will catalyze tech startup ecosystem in fintech, agritech, healthtech."}, {"title": "Affordable 5G for All Tanzanians", "description": "Vodacom strategy to make 5G accessible beyond luxury segment."}, {"title": "Building Tanzania First 5G Network", "description": "Behind the scenes engineering story of 5G rollout in Tanzania."}], "recommended_outlets": ["The Citizen", "Daily News", "Mwananchi", "TechMoran", "Business Week"]}', '2026-05-10 00:00:00'); -- ----------------------------------------------------------- -- 22. AI USAGE LOGS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `ai_usage_logs`; CREATE TABLE `ai_usage_logs` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `feature` VARCHAR(255) NOT NULL, `prompt_tokens` INT DEFAULT NULL, `completion_tokens` INT DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX `idx_ai_usage_logs_agency_id` (`agency_id`), INDEX `idx_ai_usage_logs_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `ai_usage_logs` (`id`, `agency_id`, `feature`, `prompt_tokens`, `completion_tokens`, `created_at`) VALUES ('cc000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'press_release', 1250, 850, '2026-04-01 10:00:00'), ('cc000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'campaign_plan', 2100, 1800, '2026-02-20 14:30:00'), ('cc000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'crisis_response', 950, 1200, '2026-04-20 16:00:00'), ('cc000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'news_angles', 800, 650, '2026-05-10 09:15:00'), ('cc000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000001', 'press_release', 1450, 920, '2026-06-20 11:00:00'); -- ----------------------------------------------------------- -- 23. REPORTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `reports`; CREATE TABLE `reports` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `campaign_id` CHAR(36) NOT NULL, `generated_by_user_id` CHAR(36) NOT NULL, `period_start` TIMESTAMP NOT NULL, `period_end` TIMESTAMP NOT NULL, `file_url` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE CASCADE, INDEX `idx_reports_agency_id` (`agency_id`), INDEX `idx_reports_campaign_id` (`campaign_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `reports` (`id`, `agency_id`, `campaign_id`, `generated_by_user_id`, `period_start`, `period_end`, `file_url`, `created_at`) VALUES ('f0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', '2026-03-01 00:00:00', '2026-05-31 00:00:00', '/reports/demo/report-1.html', '2026-06-01 00:00:00'), ('f0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000002', 'b0000000-0000-0000-0000-000000000002', '2026-01-15 00:00:00', '2026-06-30 00:00:00', '/reports/demo/report-2.pdf', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 24. MONITORING KEYWORDS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `monitoring_keywords`; CREATE TABLE `monitoring_keywords` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `client_id` CHAR(36) NOT NULL, `keyword` VARCHAR(255) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, UNIQUE KEY `uk_monitoring_keywords_client_keyword` (`client_id`, `keyword`), INDEX `idx_monitoring_keywords_client_id` (`client_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `monitoring_keywords` (`id`, `client_id`, `keyword`, `created_at`) VALUES ('10000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'Serengeti Breweries', '2026-01-20 00:00:00'), ('10000000-0000-0000-0000-000000000002', 'c0000000-0000-0000-0000-000000000001', 'Serengeti Premium', '2026-01-20 00:00:00'), ('10000000-0000-0000-0000-000000000003', 'c0000000-0000-0000-0000-000000000002', 'Vodacom Tanzania', '2026-01-25 00:00:00'), ('10000000-0000-0000-0000-000000000004', 'c0000000-0000-0000-0000-000000000002', 'M-Pawa', '2026-01-25 00:00:00'), ('10000000-0000-0000-0000-000000000005', 'c0000000-0000-0000-0000-000000000003', 'NMB Bank', '2026-02-05 00:00:00'), ('10000000-0000-0000-0000-000000000006', 'c0000000-0000-0000-0000-000000000003', 'NMB SME', '2026-02-05 00:00:00'); -- ----------------------------------------------------------- -- 25. MEDIA MENTIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `media_mentions`; CREATE TABLE `media_mentions` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) NOT NULL, `source_url` VARCHAR(255) NOT NULL, `source_name` VARCHAR(255) NOT NULL, `title` VARCHAR(255) NOT NULL, `snippet` TEXT NOT NULL, `published_at` TIMESTAMP NULL DEFAULT NULL, `sentiment` VARCHAR(255) DEFAULT NULL, `sentiment_reason` TEXT DEFAULT NULL, `platform` VARCHAR(255) NOT NULL DEFAULT 'NEWS', `discovered_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, INDEX `idx_media_mentions_agency_id` (`agency_id`), INDEX `idx_media_mentions_client_id` (`client_id`), INDEX `idx_media_mentions_sentiment` (`sentiment`), INDEX `idx_media_mentions_discovered_at` (`discovered_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `media_mentions` (`id`, `agency_id`, `client_id`, `source_url`, `source_name`, `title`, `snippet`, `published_at`, `sentiment`, `sentiment_reason`, `platform`, `discovered_at`) VALUES ('20000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'https://www.thecitizen.co.tz/business/sbl-record-profits', 'The Citizen', 'Serengeti Breweries Reports Record Profits', 'Serengeti Breweries Limited has announced record annual profits of TZS 85 billion, driven by strong performance of its premium brand portfolio and expanded distribution network across East Africa.', '2026-06-20 00:00:00', 'POSITIVE', 'Record profits reflect strong company performance and market leadership.', 'NEWS', '2026-06-20 08:00:00'), ('20000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'https://www.dailynews.co.tz/vodacom-5g-dar', 'Daily News', 'Vodacom Tanzania Launches 5G in Dar es Salaam', 'Vodacom Tanzania has officially launched its 5G network in Dar es Salaam, making it the first operator to offer next-generation mobile connectivity in the country.', '2026-07-01 00:00:00', 'POSITIVE', 'Positive technological milestone for Tanzania.', 'NEWS', '2026-07-01 12:00:00'), ('20000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'https://www.businessweek.co.tz/nmb-best-sme-bank', 'East African Business Week', 'NMB Bank Named Best SME Bank in East Africa', 'NMB Bank has been named the Best SME Bank in East Africa at the annual African Banking Awards, recognizing its innovative products for small and medium enterprises.', '2026-06-15 00:00:00', 'POSITIVE', 'Prestigious industry award enhances bank reputation.', 'NEWS', '2026-06-15 10:00:00'), ('20000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'https://www.mwananchi.co.tz/serengeti-beer-award', 'Mwananchi', 'Serengeti Premium Wins International Beer Award', 'Serengeti Premium Lager has won a Gold Medal at the 2026 International Beer Awards in Brussels, beating over 200 entries from 40 countries.', '2026-05-10 00:00:00', 'POSITIVE', 'International recognition for product quality.', 'NEWS', '2026-05-10 14:00:00'), ('20000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'https://twitter.com/tzconsumer/status/123456', 'Twitter User @tzconsumer', 'Concerns Raised Over Vodacom Data Prices', 'Vodacom data bundles have become too expensive for ordinary Tanzanians. A 1GB bundle costs TZS 5,000 while competitors offer TZS 3,500.', '2026-06-25 00:00:00', 'NEGATIVE', 'Customer complaint about pricing disparity with competitors.', 'TWITTER', '2026-06-25 16:30:00'), ('20000000-0000-0000-0000-000000000006', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'https://www.thecitizen.co.tz/nmb-outage', 'The Citizen', 'NMB Digital Platform Faces Outage', 'NMB Bank mobile banking platform experienced a four-hour outage, leaving thousands of customers unable to access accounts or complete transactions.', '2026-06-28 00:00:00', 'NEGATIVE', 'Service outage caused customer inconvenience and reputational harm.', 'NEWS', '2026-06-28 09:00:00'), ('20000000-0000-0000-0000-000000000007', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000004', 'https://www.dailynews.co.tz/azam-media-rwanda', 'Daily News', 'Azam Media Expands to Rwanda', 'Azam Media Group has announced its expansion into Rwanda with the launch of two new TV channels targeting the Rwandan market.', '2026-07-05 00:00:00', 'POSITIVE', 'Business expansion shows growth and market confidence.', 'NEWS', '2026-07-05 11:00:00'), ('20000000-0000-0000-0000-000000000008', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'https://www.thecitizen.co.tz/banking-sector-growth', 'The Citizen', 'Industry Insight: Tanzania Banking Sector Growth', 'Tanzania banking sector recorded steady growth of 8% driven by increased digital adoption and expansion of banking services to rural areas.', '2026-06-30 00:00:00', 'NEUTRAL', 'Factual industry analysis without strong positive or negative slant.', 'NEWS', '2026-06-30 07:00:00'), ('20000000-0000-0000-0000-000000000009', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'https://www.instagram.com/vodacomtz/p/abc123', 'Vodacom Tanzania Instagram', 'Vodacom M-Pawa Empowers Women Entrepreneurs', 'Through M-Pawa, women entrepreneurs across Tanzania are building successful businesses. Mama Fatima started her catering business with a small M-Pawa loan and now employs 10 women.', '2026-06-22 00:00:00', 'POSITIVE', 'Empowering story highlighting positive social impact of M-Pawa.', 'INSTAGRAM', '2026-06-22 18:00:00'), ('20000000-0000-0000-0000-000000000010', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'https://www.youtube.com/watch?v=sbl-ad-campaign', 'YouTube - SBL Channel', 'Behind the Scenes: New Serengeti Ad Campaign', 'Go behind the scenes of Serengeti Breweries biggest advertising campaign yet, showcasing stunning cinematography across Tanzania iconic landscapes.', '2026-05-20 00:00:00', 'POSITIVE', 'Engaging brand content showcasing production value.', 'YOUTUBE', '2026-05-20 20:00:00'), ('20000000-0000-0000-0000-000000000011', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'https://www.mwananchi.co.tz/beer-market-competition', 'Mwananchi', 'Tanzania Beer Market Competition Heats Up', 'The Tanzanian beer market is becoming increasingly competitive with international brands entering the market and local breweries fighting for market share.', '2026-06-10 00:00:00', 'NEUTRAL', 'Balanced market analysis of competitive dynamics in the beer industry.', 'NEWS', '2026-06-10 07:30:00'), ('20000000-0000-0000-0000-000000000012', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'https://www.facebook.com/nmbbank/posts/456', 'NMB Bank Facebook', 'NMB Youth Banking Initiative Gains Traction', 'Our Youth Banking Initiative has reached over 100,000 young Tanzanians since launch. Join us for free financial literacy workshops at any NMB branch.', '2026-07-08 00:00:00', 'POSITIVE', 'Positive community engagement and youth empowerment initiative.', 'FACEBOOK', '2026-07-08 14:00:00'); -- ----------------------------------------------------------- -- 26. NOTIFICATIONS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `notifications`; CREATE TABLE `notifications` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `user_id` CHAR(36) DEFAULT NULL, `type` VARCHAR(255) NOT NULL DEFAULT 'SYSTEM', `message` TEXT NOT NULL, `link` VARCHAR(255) DEFAULT NULL, `read` TINYINT(1) NOT NULL DEFAULT 0, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX `idx_notifications_agency_id` (`agency_id`), INDEX `idx_notifications_user_id` (`user_id`), INDEX `idx_notifications_read` (`read`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `notifications` (`id`, `agency_id`, `user_id`, `type`, `message`, `link`, `read`, `created_at`) VALUES ('cd000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', NULL, 'NEGATIVE_MENTION', 'Negative mention detected: Vodacom data pricing concerns trending on Twitter. Immediate monitoring recommended.', '/monitoring/mentions/20000000-0000-0000-0000-000000000005', 0, '2026-06-25 16:35:00'), ('cd000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', NULL, 'NEGATIVE_MENTION', 'Negative coverage: NMB Bank digital platform experienced outage, reported by The Citizen.', '/monitoring/mentions/20000000-0000-0000-0000-000000000006', 0, '2026-06-28 09:05:00'), ('cd000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', 'SYSTEM', 'Crisis simulation completed for Serengeti Breweries - Product Recall Rumors. Review the results and recommended actions.', '/crisis/80000000-0000-0000-0000-000000000001', 0, '2026-04-20 16:30:00'); -- ----------------------------------------------------------- -- 27. TASKS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `tasks`; CREATE TABLE `tasks` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `campaign_id` CHAR(36) NOT NULL, `agency_id` CHAR(36) NOT NULL, `title` VARCHAR(255) NOT NULL, `description` TEXT DEFAULT NULL, `assigned_to_user_id` CHAR(36) DEFAULT NULL, `status` VARCHAR(255) NOT NULL DEFAULT 'TODO', `priority` VARCHAR(255) NOT NULL DEFAULT 'MEDIUM', `due_date` TIMESTAMP NULL DEFAULT NULL, `sort_order` INT NOT NULL DEFAULT 0, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE CASCADE, FOREIGN KEY (`assigned_to_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL, INDEX `idx_tasks_campaign_id` (`campaign_id`), INDEX `idx_tasks_agency_id` (`agency_id`), INDEX `idx_tasks_assigned_to_user_id` (`assigned_to_user_id`), INDEX `idx_tasks_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tasks` (`id`, `campaign_id`, `agency_id`, `title`, `description`, `assigned_to_user_id`, `status`, `priority`, `due_date`, `sort_order`, `created_at`, `updated_at`) VALUES ('30000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Draft press release for brand refresh', 'Write and finalize the main press release announcing the Serengeti Premium brand refresh.', 'b0000000-0000-0000-0000-000000000003', 'IN_PROGRESS', 'HIGH', '2026-04-10 00:00:00', 1, '2026-03-15 00:00:00', '2026-04-01 00:00:00'), ('30000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Coordinate with radio stations', 'Contact Clouds FM, East Africa Radio, and Radio One for advertising slots.', 'b0000000-0000-0000-0000-000000000003', 'TODO', 'MEDIUM', '2026-04-20 00:00:00', 2, '2026-03-15 00:00:00', '2026-03-15 00:00:00'), ('30000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Prepare monthly report', 'Compile the final campaign performance report for Vodacom M-Pawa Financial Inclusion Drive.', 'b0000000-0000-0000-0000-000000000002', 'DONE', 'HIGH', '2026-07-05 00:00:00', 3, '2026-06-20 00:00:00', '2026-07-05 00:00:00'), ('30000000-0000-0000-0000-000000000004', 'd0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'Book venue for SME event', 'Reserve the Julius Nyerere International Convention Centre for SME Banking Week main event.', 'b0000000-0000-0000-0000-000000000003', 'IN_PROGRESS', 'URGENT', '2026-07-15 00:00:00', 4, '2026-06-01 00:00:00', '2026-07-01 00:00:00'), ('30000000-0000-0000-0000-000000000005', 'd0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'Contact journalists for coverage', 'Reach out to business and finance journalists to secure coverage for NMB SME Banking Week.', 'b0000000-0000-0000-0000-000000000003', 'TODO', 'MEDIUM', '2026-07-18 00:00:00', 5, '2026-06-15 00:00:00', '2026-06-15 00:00:00'), ('30000000-0000-0000-0000-000000000006', 'd0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'Review Azam TV launch materials', 'Review and provide feedback on Azam TV sports channel launch materials including press kit and media invite.', 'b0000000-0000-0000-0000-000000000002', 'TODO', 'LOW', '2026-08-20 00:00:00', 6, '2026-07-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 28. CAMPAIGN EVENTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `campaign_events`; CREATE TABLE `campaign_events` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `campaign_id` CHAR(36) NOT NULL, `agency_id` CHAR(36) NOT NULL, `title` VARCHAR(255) NOT NULL, `description` TEXT DEFAULT NULL, `event_date` TIMESTAMP NULL DEFAULT NULL, `location` VARCHAR(255) DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE CASCADE, INDEX `idx_campaign_events_campaign_id` (`campaign_id`), INDEX `idx_campaign_events_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `campaign_events` (`id`, `campaign_id`, `agency_id`, `title`, `description`, `event_date`, `location`, `created_at`, `updated_at`) VALUES ('40000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Serengeti Brand Launch Event', 'Official launch for the new Serengeti Premium Lager brand refresh with media and influencers.', '2026-05-10 18:00:00', 'Hyatt Regency Dar es Salaam', '2026-04-01 00:00:00', '2026-04-15 00:00:00'), ('40000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Media Breakfast', 'Exclusive media breakfast to preview the new brand direction with SBL executives.', '2026-05-05 08:00:00', 'Serena Hotel', '2026-04-01 00:00:00', '2026-04-10 00:00:00'), ('40000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'SME Banking Workshop', 'Full-day workshop for SME owners covering digital transformation and access to finance.', '2026-07-24 09:00:00', 'NMB Head Office', '2026-06-10 00:00:00', '2026-06-20 00:00:00'), ('40000000-0000-0000-0000-000000000004', 'd0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'Azam TV Sports Launch', 'Grand launch event for Azam TV dedicated sports channel featuring live screenings.', '2026-09-01 17:00:00', 'Azam Complex', '2026-07-01 00:00:00', '2026-07-10 00:00:00'); -- ----------------------------------------------------------- -- 29. CHECKLIST ITEMS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `checklist_items`; CREATE TABLE `checklist_items` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `campaign_event_id` CHAR(36) NOT NULL, `title` VARCHAR(255) NOT NULL, `is_completed` TINYINT(1) NOT NULL DEFAULT 0, `assigned_to_user_id` CHAR(36) DEFAULT NULL, `sort_order` INT NOT NULL DEFAULT 0, FOREIGN KEY (`campaign_event_id`) REFERENCES `campaign_events`(`id`) ON DELETE CASCADE, FOREIGN KEY (`assigned_to_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL, INDEX `idx_checklist_items_campaign_event_id` (`campaign_event_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `checklist_items` (`id`, `campaign_event_id`, `title`, `is_completed`, `assigned_to_user_id`, `sort_order`) VALUES ('ce000000-0000-0000-0000-000000000001', '40000000-0000-0000-0000-000000000001', 'Book venue', 1, 'b0000000-0000-0000-0000-000000000003', 1), ('ce000000-0000-0000-0000-000000000002', '40000000-0000-0000-0000-000000000001', 'Send invitations', 1, 'b0000000-0000-0000-0000-000000000003', 2), ('ce000000-0000-0000-0000-000000000003', '40000000-0000-0000-0000-000000000001', 'Prepare materials', 0, 'b0000000-0000-0000-0000-000000000003', 3), ('ce000000-0000-0000-0000-000000000004', '40000000-0000-0000-0000-000000000002', 'Compile media list', 1, 'b0000000-0000-0000-0000-000000000003', 1), ('ce000000-0000-0000-0000-000000000005', '40000000-0000-0000-0000-000000000002', 'Prepare briefing packs', 0, 'b0000000-0000-0000-0000-000000000003', 2); -- ----------------------------------------------------------- -- 30. COVERAGE ITEMS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `coverage_items`; CREATE TABLE `coverage_items` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `campaign_id` CHAR(36) DEFAULT NULL, `client_id` CHAR(36) NOT NULL, `agency_id` CHAR(36) NOT NULL, `title` VARCHAR(255) NOT NULL, `source_name` VARCHAR(255) NOT NULL, `source_url` VARCHAR(255) DEFAULT NULL, `media_type` VARCHAR(255) NOT NULL, `file_url` VARCHAR(255) DEFAULT NULL, `published_at` TIMESTAMP NULL DEFAULT NULL, `notes` TEXT DEFAULT NULL, `created_by_user_id` CHAR(36) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE SET NULL, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, INDEX `idx_coverage_items_campaign_id` (`campaign_id`), INDEX `idx_coverage_items_client_id` (`client_id`), INDEX `idx_coverage_items_agency_id` (`agency_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `coverage_items` (`id`, `campaign_id`, `client_id`, `agency_id`, `title`, `source_name`, `source_url`, `media_type`, `file_url`, `published_at`, `notes`, `created_by_user_id`, `created_at`) VALUES ('50000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'SBL Launches Premium Refresh Campaign', 'The Citizen', 'https://www.thecitizen.co.tz/sbl-refresh', 'ONLINE', '/coverage/sbl-citizen.pdf', '2026-05-12 00:00:00', 'Featured article in business section.', 'b0000000-0000-0000-0000-000000000001', '2026-05-12 10:00:00'), ('50000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000002', 'c0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Vodacom M-Pawa Hits 5M Users', 'Daily News', 'https://www.dailynews.co.tz/mpawa-5m', 'PRINT', '/coverage/mpawa-daily-news.pdf', '2026-06-16 00:00:00', 'Full-page coverage in business section.', 'b0000000-0000-0000-0000-000000000002', '2026-06-16 08:00:00'), ('50000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000003', 'c0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'NMB SME Banking Week Kicks Off', 'Mwananchi', 'https://www.mwananchi.co.tz/nmb-sme-week', 'ONLINE', '/coverage/nmb-mwananchi.pdf', '2026-07-22 00:00:00', 'Photos from opening ceremony.', 'b0000000-0000-0000-0000-000000000003', '2026-07-22 12:00:00'), ('50000000-0000-0000-0000-000000000004', 'd0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Serengeti Premium TV Commercial Airs', 'ITV Tanzania', NULL, 'TV', '/coverage/serengeti-tv-ad.mp4', '2026-05-15 00:00:00', '30-second TV commercial spot.', 'b0000000-0000-0000-0000-000000000001', '2026-05-15 18:00:00'), ('50000000-0000-0000-0000-000000000005', 'd0000000-0000-0000-0000-000000000004', 'c0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'Azam Sports Channel Launch Coverage', 'Channel 10', NULL, 'TV', '/coverage/azam-sports-launch.mp4', '2026-09-01 19:00:00', 'Live coverage of the launch event.', 'b0000000-0000-0000-0000-000000000001', '2026-09-01 20:00:00'), ('50000000-0000-0000-0000-000000000006', 'd0000000-0000-0000-0000-000000000003', 'c0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'Banking Sector Growth Analysis', 'East Africa Radio', NULL, 'RADIO', '/coverage/banking-sector-radio.mp3', '2026-06-30 07:00:00', 'Radio interview segment on banking sector.', 'b0000000-0000-0000-0000-000000000002', '2026-06-30 08:00:00'); -- ----------------------------------------------------------- -- 31. COVERAGE MEDIA -- ----------------------------------------------------------- DROP TABLE IF EXISTS `coverage_media`; CREATE TABLE `coverage_media` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) DEFAULT NULL, `campaign_id` CHAR(36) DEFAULT NULL, `coverage_item_id` CHAR(36) DEFAULT NULL, `file_name` VARCHAR(255) NOT NULL, `file_url` VARCHAR(255) NOT NULL, `media_type` VARCHAR(255) NOT NULL, `source` VARCHAR(255) DEFAULT NULL, `caption` TEXT DEFAULT NULL, `uploaded_by_id` CHAR(36) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE SET NULL, FOREIGN KEY (`coverage_item_id`) REFERENCES `coverage_items`(`id`) ON DELETE SET NULL, INDEX `idx_coverage_media_agency_id` (`agency_id`), INDEX `idx_coverage_media_client_id` (`client_id`), INDEX `idx_coverage_media_campaign_id` (`campaign_id`), INDEX `idx_coverage_media_coverage_item_id` (`coverage_item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `coverage_media` (`id`, `agency_id`, `client_id`, `campaign_id`, `coverage_item_id`, `file_name`, `file_url`, `media_type`, `source`, `caption`, `uploaded_by_id`, `created_at`) VALUES ('cf000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', '50000000-0000-0000-0000-000000000001', 'sbl-citizen-article-screenshot.png', '/coverage-media/sbl-citizen-screenshot.png', 'IMAGE', NULL, 'Screenshot of The Citizen article about SBL brand refresh', 'b0000000-0000-0000-0000-000000000001', '2026-05-12 11:00:00'), ('cf000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000002', '50000000-0000-0000-0000-000000000002', 'mpawa-print-clipping.pdf', '/coverage-media/mpawa-print.pdf', 'IMAGE', NULL, 'Scan of Daily News print article about M-Pawa', 'b0000000-0000-0000-0000-000000000002', '2026-06-16 09:00:00'), ('cf000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', '50000000-0000-0000-0000-000000000004', 'serengeti-tv-ad-clip.mp4', '/coverage-media/serengeti-tv-ad.mp4', 'VIDEO', NULL, 'Serengeti Premium TV commercial - 30 second spot', 'b0000000-0000-0000-0000-000000000001', '2026-05-15 19:00:00'); -- ----------------------------------------------------------- -- 32. COMMENTS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `comments`; CREATE TABLE `comments` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `user_id` CHAR(36) NOT NULL, `press_release_id` CHAR(36) DEFAULT NULL, `task_id` CHAR(36) DEFAULT NULL, `campaign_id` CHAR(36) DEFAULT NULL, `body` TEXT NOT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, FOREIGN KEY (`press_release_id`) REFERENCES `press_releases`(`id`) ON DELETE CASCADE, FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE, INDEX `idx_comments_agency_id` (`agency_id`), INDEX `idx_comments_press_release_id` (`press_release_id`), INDEX `idx_comments_task_id` (`task_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `comments` (`id`, `agency_id`, `user_id`, `press_release_id`, `task_id`, `campaign_id`, `body`, `created_at`, `updated_at`) VALUES ('d0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000002', 'e0000000-0000-0000-0000-000000000001', NULL, NULL, 'Great draft! Please add a quote from the CEO as well. Also update the social media captions to include the event date.', '2026-04-02 09:00:00', '2026-04-02 09:00:00'), ('d0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000003', 'e0000000-0000-0000-0000-000000000001', NULL, NULL, 'Added CEO quotes as suggested. Also updated the social captions. Ready for review.', '2026-04-02 10:30:00', '2026-04-02 10:30:00'), ('d0000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000001', NULL, '30000000-0000-0000-0000-000000000004', NULL, 'I have contacted the venue. The JNICC is available on July 24 but the deposit is due by July 10. Please proceed with the booking.', '2026-07-01 11:00:00', '2026-07-01 11:00:00'), ('d0000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'b0000000-0000-0000-0000-000000000003', NULL, '30000000-0000-0000-0000-000000000004', NULL, 'Deposit has been processed. Venue is confirmed for July 24.', '2026-07-02 14:00:00', '2026-07-02 14:00:00'); -- ----------------------------------------------------------- -- 33. INFLUENCERS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `influencers`; CREATE TABLE `influencers` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `agency_id` CHAR(36) NOT NULL, `name` VARCHAR(255) NOT NULL, `platform` VARCHAR(255) NOT NULL, `handle` VARCHAR(255) NOT NULL, `follower_count` INT DEFAULT NULL, `engagement_rate` DOUBLE DEFAULT NULL, `industry` JSON NOT NULL, `location` VARCHAR(255) DEFAULT NULL, `estimated_price_range` VARCHAR(255) DEFAULT NULL, `notes` TEXT DEFAULT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE, INDEX `idx_influencers_agency_id` (`agency_id`), INDEX `idx_influencers_platform` (`platform`), INDEX `idx_influencers_handle` (`handle`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `influencers` (`id`, `agency_id`, `name`, `platform`, `handle`, `follower_count`, `engagement_rate`, `industry`, `location`, `estimated_price_range`, `notes`, `created_at`, `updated_at`) VALUES ('d9000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'Sarah Mwangi', 'INSTAGRAM', '@sarahmwangitz', 45000, 3.8, '["Lifestyle", "Fashion", "Travel"]', 'Dar es Salaam', 'TZS 500,000 - 1,000,000', 'Lifestyle influencer with strong engagement. Good for brand campaigns.', '2026-03-01 00:00:00', '2026-06-15 00:00:00'), ('d9000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'Dennis Kiponda', 'TIKTOK', '@denniskiponda', 120000, 5.2, '["Entertainment", "Comedy", "Sports"]', 'Dar es Salaam', 'TZS 800,000 - 2,000,000', 'Top TikTok creator in Tanzania. Viral content specialist.', '2026-03-10 00:00:00', '2026-06-20 00:00:00'), ('d9000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000001', 'TZ Tech Guy', 'YOUTUBE', '@tztechguy', 85000, 4.1, '["Technology", "Gadgets", "Reviews"]', 'Arusha', 'TZS 1,000,000 - 2,500,000', 'Tech reviewer. Good for Vodacom and fintech campaigns.', '2026-03-15 00:00:00', '2026-06-10 00:00:00'), ('d9000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000001', 'Mwananchi Mtaani', 'TWITTER', '@mwananchimtaani', 32000, 6.5, '["News", "Politics", "Social Commentary"]', 'Dar es Salaam', 'TZS 300,000 - 600,000', 'Twitter influencer with high engagement on social topics.', '2026-04-01 00:00:00', '2026-06-25 00:00:00'); -- ----------------------------------------------------------- -- 34. INVOICES -- ----------------------------------------------------------- DROP TABLE IF EXISTS `invoices`; CREATE TABLE `invoices` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `invoice_number` VARCHAR(255) NOT NULL UNIQUE, `agency_id` CHAR(36) NOT NULL, `client_id` CHAR(36) NOT NULL, `campaign_id` CHAR(36) DEFAULT NULL, `status` VARCHAR(255) NOT NULL DEFAULT 'DRAFT', `issue_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `due_date` TIMESTAMP NOT NULL, `currency` VARCHAR(255) NOT NULL DEFAULT 'TZS', `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0, `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0, `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0, `total` DECIMAL(12,2) NOT NULL DEFAULT 0, `notes` TEXT DEFAULT NULL, `terms` TEXT DEFAULT NULL, `created_by_id` CHAR(36) NOT NULL, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE, FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`) ON DELETE SET NULL, INDEX `idx_invoices_agency_id` (`agency_id`), INDEX `idx_invoices_client_id` (`client_id`), INDEX `idx_invoices_invoice_number` (`invoice_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `invoices` (`id`, `invoice_number`, `agency_id`, `client_id`, `campaign_id`, `status`, `issue_date`, `due_date`, `currency`, `subtotal`, `tax_rate`, `tax_amount`, `total`, `notes`, `terms`, `created_by_id`, `created_at`, `updated_at`) VALUES ('a0000000-0000-0000-0000-000000000002', 'INV-2026-0001', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'SENT', '2026-05-01 00:00:00', '2026-06-01 00:00:00', 'TZS', 48000000.00, 18.00, 8640000.00, 56640000.00, 'Invoice for brand refresh campaign services - Phase 1', 'Payment due within 30 days. Late payment incurs 2% interest per month.', 'b0000000-0000-0000-0000-000000000001', '2026-05-01 00:00:00', '2026-05-01 00:00:00'), ('a0000000-0000-0000-0000-000000000003', 'INV-2026-0002', 'a0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000003', 'PAID', '2026-07-01 00:00:00', '2026-08-01 00:00:00', 'TZS', 47000000.00, 18.00, 8460000.00, 55460000.00, 'Invoice for SME Banking Week campaign management', 'Payment received via bank transfer on 15 July 2026.', 'b0000000-0000-0000-0000-000000000001', '2026-07-01 00:00:00', '2026-07-15 00:00:00'); -- ----------------------------------------------------------- -- 35. INVOICE LINE ITEMS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `invoice_line_items`; CREATE TABLE `invoice_line_items` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `invoice_id` CHAR(36) NOT NULL, `description` VARCHAR(255) NOT NULL, `quantity` INT NOT NULL DEFAULT 1, `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0, `amount` DECIMAL(12,2) NOT NULL DEFAULT 0, FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE, INDEX `idx_invoice_line_items_invoice_id` (`invoice_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `invoice_line_items` (`id`, `invoice_id`, `description`, `quantity`, `unit_price`, `amount`) VALUES ('d5000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000002', 'Brand Refresh Strategy Consultation', 1, 25000000.00, 25000000.00), ('d5000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000002', 'Press Release Writing & Distribution', 3, 5000000.00, 15000000.00), ('d5000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-000000000002', 'Media Monitoring (3 months)', 1, 8000000.00, 8000000.00), ('d5000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-000000000003', 'SME Banking Week Campaign Management', 1, 35000000.00, 35000000.00), ('d5000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-000000000003', 'Event Coordination & Logistics', 1, 12000000.00, 12000000.00); -- ----------------------------------------------------------- -- 36. SYSTEM ERROR LOGS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `system_error_logs`; CREATE TABLE `system_error_logs` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `category` VARCHAR(255) NOT NULL, `severity` VARCHAR(255) NOT NULL DEFAULT 'error', `message` TEXT NOT NULL, `details` TEXT DEFAULT NULL, `source` VARCHAR(255) DEFAULT NULL, `metadata` JSON DEFAULT NULL, `resolved` TINYINT(1) NOT NULL DEFAULT 0, `resolved_at` TIMESTAMP NULL DEFAULT NULL, `resolved_by` VARCHAR(255) DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX `idx_system_error_logs_category` (`category`), INDEX `idx_system_error_logs_severity` (`severity`), INDEX `idx_system_error_logs_created_at` (`created_at`), INDEX `idx_system_error_logs_resolved` (`resolved`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `system_error_logs` (`id`, `category`, `severity`, `message`, `details`, `source`, `metadata`, `resolved`, `resolved_at`, `resolved_by`, `created_at`) VALUES ('d6000000-0000-0000-0000-000000000001', 'API', 'error', 'OpenAI API rate limit exceeded', 'The API request was throttled due to rate limiting. Retry after 20 seconds.', 'AiPressReleaseService', '{"endpoint": "chat/completions", "retry_after": 20}', 1, '2026-04-01 10:05:00', 'system', '2026-04-01 10:00:00'), ('d6000000-0000-0000-0000-000000000002', 'DATABASE', 'warning', 'Slow query detected in media_mentions table', 'Query took 4.2 seconds to execute. Consider adding index on (client_id, discovered_at).', 'MentionController@index', '{"query_time_ms": 4200, "table": "media_mentions"}', 0, NULL, NULL, '2026-06-20 14:30:00'), ('d6000000-0000-0000-0000-000000000003', 'AUTH', 'critical', 'Multiple failed login attempts detected', 'IP 45.32.156.89 attempted 12 failed logins within 5 minutes for account admin@roote.co.tz', 'AuthenticateSession@handle', '{"ip": "45.32.156.89", "attempts": 12, "target_email": "admin@roote.co.tz", "time_window_seconds": 300}', 0, NULL, NULL, '2026-07-13 03:15:00'), ('d6000000-0000-0000-0000-000000000004', 'SYSTEM', 'info', 'Session garbage collection completed', 'Cleaned up 47 expired sessions from the database session store.', 'SessionHandler@gc', '{"expired_sessions_cleaned": 47, "total_sessions_before": 213}', 1, '2026-07-14 06:00:00', 'system', '2026-07-14 06:00:00'), ('d6000000-0000-0000-0000-000000000005', 'PAYMENT', 'error', 'Flutterwave payment verification failed', 'Payment verification for transaction fw_txn_demo_002 returned status "failed". Webhook received but signature mismatch.', 'PaymentWebhookController@handle', '{"transaction_id": "fw_txn_demo_002", "webhook_signature": "abc123", "expected_signature": "def456"}', 0, NULL, NULL, '2026-07-12 15:45:00'), ('d6000000-0000-0000-0000-000000000006', 'API', 'warning', 'NewsAPI daily quota at 80%', 'NewsAPI has consumed 800 out of 1000 daily requests. Consider upgrading plan or reducing poll frequency.', 'NewsApiService@fetch', '{"used": 800, "limit": 1000, "reset_time": "2026-07-15 00:00:00"}', 0, NULL, NULL, '2026-07-14 10:00:00'); -- ----------------------------------------------------------- -- 37. PLATFORM ADMINS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `platform_admins`; CREATE TABLE `platform_admins` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `full_name` VARCHAR(255) NOT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` TIMESTAMP NULL DEFAULT NULL, `updated_at` TIMESTAMP NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `platform_admins` (`id`, `email`, `password`, `full_name`, `is_active`, `created_at`, `updated_at`) VALUES ('a0000000-0000-0000-0000-0000000000ff', 'superadmin@empiricalcommunication.co.tz', '$2y$12$JHYcXsuFFp0mgwzte4WoeOJx2XOGm2eFxIfaY.ljbcUoqsMAr0HEa', 'Gabito', 1, '2026-01-01 00:00:00', '2026-07-01 00:00:00'); -- ----------------------------------------------------------- -- 38. PLATFORM ADMIN REFRESH TOKENS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `platform_admin_refresh_tokens`; CREATE TABLE `platform_admin_refresh_tokens` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `platform_admin_id` CHAR(36) NOT NULL, `token_hash` VARCHAR(255) NOT NULL, `expires_at` TIMESTAMP NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `revoked_at` TIMESTAMP NULL DEFAULT NULL, FOREIGN KEY (`platform_admin_id`) REFERENCES `platform_admins`(`id`) ON DELETE CASCADE, INDEX `idx_platform_admin_refresh_tokens_admin_id` (`platform_admin_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `platform_admin_refresh_tokens` (`id`, `platform_admin_id`, `token_hash`, `expires_at`, `created_at`, `revoked_at`) VALUES ('d7000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-0000000000ff', 'hash_platform_admin_token_abc123', '2027-07-14 00:00:00', '2026-07-14 00:00:00', NULL); -- ----------------------------------------------------------- -- 39. PLATFORM AUDIT LOGS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `platform_audit_logs`; CREATE TABLE `platform_audit_logs` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `platform_admin_id` CHAR(36) NOT NULL, `action` VARCHAR(255) NOT NULL, `target_type` VARCHAR(255) NOT NULL, `target_id` VARCHAR(255) DEFAULT NULL, `metadata` JSON DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`platform_admin_id`) REFERENCES `platform_admins`(`id`) ON DELETE CASCADE, INDEX `idx_platform_audit_logs_admin_id` (`platform_admin_id`), INDEX `idx_platform_audit_logs_created_at` (`created_at`), INDEX `idx_platform_audit_logs_target` (`target_type`, `target_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `platform_audit_logs` (`id`, `platform_admin_id`, `action`, `target_type`, `target_id`, `metadata`, `created_at`) VALUES ('d8000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-0000000000ff', 'LOGIN', 'platform_admin', 'a0000000-0000-0000-0000-0000000000ff', '{"ip": "127.0.0.1", "user_agent": "Mozilla/5.0", "browser": "Chrome"}', '2026-07-14 08:30:00'), ('d8000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-0000000000ff', 'APPROVE_AGENCY', 'agency', 'a0000000-0000-0000-0000-000000000003', '{"agency_name": "Dar es Salaam PR Hub", "plan": "STARTER"}', '2026-07-14 09:15:00'), ('d8000000-0000-0000-0000-000000000003', 'a0000000-0000-0000-0000-0000000000ff', 'UPDATE_SETTINGS', 'platform_settings', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaad', '{"key": "timezone", "old_value": "UTC", "new_value": "Africa/Dar_es_Salaam"}', '2026-07-13 14:00:00'), ('d8000000-0000-0000-0000-000000000004', 'a0000000-0000-0000-0000-0000000000ff', 'CREATE_USER', 'user', 'b0000000-0000-0000-0000-000000000006', '{"user_email": "admin@darprhub.co.tz", "agency": "Dar es Salaam PR Hub", "role": "ADMIN"}', '2026-06-01 10:00:00'), ('d8000000-0000-0000-0000-000000000005', 'a0000000-0000-0000-0000-0000000000ff', 'LOGIN', 'platform_admin', 'a0000000-0000-0000-0000-0000000000ff', '{"ip": "192.168.1.1", "user_agent": "Mozilla/5.0", "browser": "Firefox"}', '2026-07-14 17:45:00'), ('d8000000-0000-0000-0000-000000000006', 'a0000000-0000-0000-0000-0000000000ff', 'DELETE_USER', 'user', 'b0000000-0000-0000-0000-0000000000ff', '{"reason": "User requested account removal", "agency": "Roote Communication"}', '2026-07-10 11:30:00'); -- ----------------------------------------------------------- -- 40. PLATFORM SETTINGS -- ----------------------------------------------------------- DROP TABLE IF EXISTS `platform_settings`; CREATE TABLE `platform_settings` ( `id` CHAR(36) NOT NULL PRIMARY KEY, `key` VARCHAR(255) NOT NULL UNIQUE, `value` TEXT DEFAULT NULL, `type` VARCHAR(255) NOT NULL DEFAULT 'text', `description` TEXT DEFAULT NULL, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_by` VARCHAR(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `platform_settings` (`id`, `key`, `value`, `type`, `description`, `updated_at`, `updated_by`) VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'site_name', 'Roote PR System', 'text', 'The name of the platform as displayed to users', '2026-01-01 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaab', 'maintenance_mode', 'false', 'boolean', 'Enable or disable maintenance mode for the entire platform', '2026-01-01 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaac', 'default_currency', 'TZS', 'text', 'Default currency for financial transactions', '2026-01-01 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaad', 'timezone', 'Africa/Dar_es_Salaam', 'text', 'Default timezone for the platform', '2026-01-01 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaae', 'openai_api_key', '', 'password', 'OpenAI API key for AI-powered features (press release generation, content analysis)', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaf', 'newsapi_key', '', 'password', 'NewsAPI key for media monitoring and news search', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaag', 'mail_host', '', 'text', 'SMTP host for sending emails', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaah', 'mail_port', '587', 'text', 'SMTP port', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaai', 'mail_username', '', 'text', 'SMTP username', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaj', 'mail_password', '', 'password', 'SMTP password', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaak', 'mail_encryption', 'tls', 'text', 'SMTP encryption method (tls or ssl)', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaal', 'mail_from_address', 'noreply@roote.co.tz', 'text', 'Default from address for system emails', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaam', 'mail_from_name', 'Roote PR System', 'text', 'Default from name for system emails', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaan', 'password_min_length', '8', 'number', 'Minimum password length requirement', '2026-07-14 00:00:00', 'system'), ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaao', 'session_lifetime', '120', 'number', 'Session lifetime in minutes', '2026-07-14 00:00:00', 'system'); -- ============================================================================ -- END OF SQL DUMP -- ============================================================================ SET FOREIGN_KEY_CHECKS = 1; -- ============================================================================ -- DEMO CREDENTIALS (all passwords: Demo@12345678) -- ============================================================================ -- SUPER ADMIN: superadmin@empiricalcommunication.co.tz / Demo@12345678 -- → Login at /super-admin (Emerald SaaS dashboard) -- → Manage agencies, users, system health, error logs, audit trail -- → Configure API keys, platform settings, site settings -- -- AGENCY ADMIN: admin@roote.co.tz / Demo@12345678 -- → Login at /admin (Blue SaaS dashboard) -- → Full PR toolkit, manage team members, campaigns, media, reports -- -- ACCOUNT MANAGER: am@roote.co.tz / Demo@12345678 -- → Login at /admin -- → Campaigns, clients, press releases, media, tasks, crisis -- -- VIEWER: viewer@roote.co.tz / Demo@12345678 -- → Login at /admin -- → Dashboard + reports only (read-only) -- -- PENDING AGENCY OWNER: owner@empiricalmedia.co.tz / Demo@12345678 -- → Login at /admin → sees "pending approval" dashboard -- → Agency "Empirical Media Tanzania" is PENDING status -- -- DAR ES SALAAM PR HUB ADMIN: admin@darprhub.co.tz / Demo@12345678 -- → Login at /admin -- → Secondary active agency (Starter plan) -- -- ============================================================================ -- SUPER ADMIN PANEL (23 routes): -- /super-admin → Dashboard (metrics, widgets) -- /super-admin/agencies → Manage all agencies -- /super-admin/users → Manage all users -- /super-admin/payments → Cross-agency payments -- /super-admin/system-health → PHP/Laravel info, cache mgmt -- /super-admin/error-logs → System error tracking -- /super-admin/audit-logs → Admin action audit trail -- /super-admin/security → Security overview -- /super-admin/platform-settings → Key-value settings -- /super-admin/api-integrations → OpenAI, NewsAPI, SMTP config -- /super-admin/settings → Site name, logo, description -- /super-admin/profile → Name, email, password -- -- AGENCY PANEL (Blue SaaS theme): -- /admin → Agency dashboard -- /admin/campaigns → PR campaigns -- /admin/clients → Client management -- /admin/press-releases → Press release management -- /admin/reports → Report generation & history -- /admin/team-members → User management (plan-limited) -- /admin/settings → Agency settings (OWNER only)