-- =====================================================================
--  Roote - fresh install database
--  Generated 2026-08-27 from schema at commit 4e29284
-- =====================================================================
--
--  HOW TO IMPORT
--    Create an EMPTY database on the server first, then in cPanel /
--    phpMyAdmin: select that empty database -> Import -> choose this file.
--
--  This file deliberately contains no CREATE DATABASE or USE statement,
--  so it imports into whatever database name your host gave you
--  (shared hosts usually prefix them, e.g. cpaneluser_roote).
--
--  WHAT IS INSIDE
--    - Full schema, 70 tables, migrations table already marked complete,
--      so nothing further needs to be run.
--    - Subscription plans and default site settings.
--    - One super-admin account and one contributor account.
--
--  CHANGE BOTH PASSWORDS AFTER THE FIRST SIGN-IN.
-- =====================================================================

-- MariaDB dump 10.19  Distrib 10.4.32-MariaDB, for Win64 (AMD64)
--
-- Source schema: Roote
-- ------------------------------------------------------
-- Server version	10.4.32-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `agencies`
--

DROP TABLE IF EXISTS `agencies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `agencies` (
  `id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `status` varchar(191) NOT NULL DEFAULT 'ACTIVE',
  `account_type` varchar(191) NOT NULL DEFAULT 'AGENCY',
  `slug` varchar(191) NOT NULL,
  `email` varchar(191) DEFAULT NULL,
  `phone` varchar(191) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `plan` varchar(191) NOT NULL DEFAULT 'TRIAL',
  `subscription_status` varchar(191) NOT NULL DEFAULT 'TRIALING',
  `is_suspended` tinyint(1) NOT NULL DEFAULT 0,
  `suspended_at` timestamp NULL DEFAULT NULL,
  `suspended_reason` varchar(191) DEFAULT NULL,
  `logo_url` varchar(191) DEFAULT NULL,
  `emv_rates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`emv_rates`)),
  `outlet_rates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`outlet_rates`)),
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  `current_period_end` timestamp NULL DEFAULT NULL,
  `payment_provider` varchar(191) DEFAULT NULL,
  `payment_provider_customer_id` varchar(191) DEFAULT NULL,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `agencies_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `agencies`
--

LOCK TABLES `agencies` WRITE;
/*!40000 ALTER TABLE `agencies` DISABLE KEYS */;
INSERT INTO `agencies` VALUES ('01a04467-c866-71ed-9ed5-8e0aa004d7cf','Roote Platform','ACTIVE','AGENCY','roote-platform',NULL,NULL,NULL,'AGENCY','ACTIVE',0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-08-27 15:07:27','2026-08-27 15:07:27');
/*!40000 ALTER TABLE `agencies` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `ai_usage_logs`
--

DROP TABLE IF EXISTS `ai_usage_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ai_usage_logs` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `feature` varchar(191) NOT NULL,
  `prompt_tokens` int(11) DEFAULT NULL,
  `completion_tokens` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `ai_usage_logs_agency_id_index` (`agency_id`),
  KEY `ai_usage_logs_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `ai_usage_logs`
--

LOCK TABLES `ai_usage_logs` WRITE;
/*!40000 ALTER TABLE `ai_usage_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `ai_usage_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `announcements`
--

DROP TABLE IF EXISTS `announcements`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `announcements` (
  `id` char(36) NOT NULL,
  `title` varchar(191) NOT NULL,
  `body` text NOT NULL,
  `type` varchar(191) NOT NULL DEFAULT 'info',
  `audience` varchar(191) NOT NULL DEFAULT 'all',
  `target_agency_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`target_agency_ids`)),
  `starts_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `announcements`
--

LOCK TABLES `announcements` WRITE;
/*!40000 ALTER TABLE `announcements` DISABLE KEYS */;
/*!40000 ALTER TABLE `announcements` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `app_logs`
--

DROP TABLE IF EXISTS `app_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `app_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(191) NOT NULL DEFAULT 'info',
  `message` text NOT NULL,
  `details` text DEFAULT NULL,
  `stack_trace` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `app_logs_level_index` (`level`),
  KEY `app_logs_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `app_logs`
--

LOCK TABLES `app_logs` WRITE;
/*!40000 ALTER TABLE `app_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `app_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `campaign_assets`
--

DROP TABLE IF EXISTS `campaign_assets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `campaign_assets` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) DEFAULT NULL,
  `campaign_id` char(36) NOT NULL,
  `file_name` varchar(191) NOT NULL,
  `file_url` varchar(191) NOT NULL,
  `kind` varchar(191) NOT NULL DEFAULT 'PHOTO',
  `caption` varchar(191) DEFAULT NULL,
  `credit` varchar(191) DEFAULT NULL,
  `file_size` bigint(20) unsigned DEFAULT NULL,
  `uploaded_by_user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign_assets_agency_id_foreign` (`agency_id`),
  KEY `campaign_assets_campaign_id_kind_index` (`campaign_id`,`kind`),
  CONSTRAINT `campaign_assets_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_assets_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `campaign_assets`
--

LOCK TABLES `campaign_assets` WRITE;
/*!40000 ALTER TABLE `campaign_assets` DISABLE KEYS */;
/*!40000 ALTER TABLE `campaign_assets` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `campaign_distributions`
--

DROP TABLE IF EXISTS `campaign_distributions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `campaign_distributions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) NOT NULL,
  `press_release_id` char(36) DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `type` varchar(191) NOT NULL DEFAULT 'INFLUENCER',
  `handles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`handles`)),
  `contact_email` varchar(191) DEFAULT NULL,
  `contact_phone` varchar(191) DEFAULT NULL,
  `status` varchar(191) NOT NULL DEFAULT 'PENDING',
  `sent_at` timestamp NULL DEFAULT NULL,
  `delivery_status` varchar(191) NOT NULL DEFAULT 'NOT_SENT',
  `delivery_channel` varchar(191) DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `delivery_error` text DEFAULT NULL,
  `delivery_attempts` smallint(5) unsigned NOT NULL DEFAULT 0,
  `posted_at` timestamp NULL DEFAULT NULL,
  `media_mention_id` char(36) DEFAULT NULL,
  `coverage_item_id` char(36) DEFAULT NULL,
  `posted_url` varchar(191) DEFAULT NULL,
  `detected_via` varchar(191) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign_distributions_client_id_foreign` (`client_id`),
  KEY `campaign_distributions_coverage_item_id_foreign` (`coverage_item_id`),
  KEY `campaign_distributions_campaign_id_status_index` (`campaign_id`,`status`),
  KEY `campaign_distributions_agency_id_index` (`agency_id`),
  CONSTRAINT `campaign_distributions_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_distributions_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_distributions_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_distributions_coverage_item_id_foreign` FOREIGN KEY (`coverage_item_id`) REFERENCES `coverage_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `campaign_distributions`
--

LOCK TABLES `campaign_distributions` WRITE;
/*!40000 ALTER TABLE `campaign_distributions` DISABLE KEYS */;
/*!40000 ALTER TABLE `campaign_distributions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `campaign_events`
--

DROP TABLE IF EXISTS `campaign_events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `campaign_events` (
  `id` char(36) NOT NULL,
  `campaign_id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `title` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `event_date` timestamp NULL DEFAULT NULL,
  `location` varchar(191) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign_events_campaign_id_index` (`campaign_id`),
  KEY `campaign_events_agency_id_index` (`agency_id`),
  CONSTRAINT `campaign_events_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `campaign_events`
--

LOCK TABLES `campaign_events` WRITE;
/*!40000 ALTER TABLE `campaign_events` DISABLE KEYS */;
/*!40000 ALTER TABLE `campaign_events` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `campaign_plans`
--

DROP TABLE IF EXISTS `campaign_plans`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `campaign_plans` (
  `id` char(36) NOT NULL,
  `campaign_id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `strategy` text NOT NULL,
  `timeline` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`timeline`)),
  `media_plan` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`media_plan`)),
  `kpis` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`kpis`)),
  `risk_analysis` text NOT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `campaign_plans_campaign_id_index` (`campaign_id`),
  KEY `campaign_plans_agency_id_index` (`agency_id`),
  CONSTRAINT `campaign_plans_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `campaign_plans`
--

LOCK TABLES `campaign_plans` WRITE;
/*!40000 ALTER TABLE `campaign_plans` DISABLE KEYS */;
/*!40000 ALTER TABLE `campaign_plans` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `campaigns`
--

DROP TABLE IF EXISTS `campaigns`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `campaigns` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `campaign_type` varchar(191) DEFAULT NULL,
  `campaign_description` text DEFAULT NULL,
  `summary_headline` varchar(191) DEFAULT NULL,
  `summary_location` varchar(191) DEFAULT NULL,
  `summary_main_story` longtext DEFAULT NULL,
  `summary_key_figures` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`summary_key_figures`)),
  `summary_key_people` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`summary_key_people`)),
  `summary_quotes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`summary_quotes`)),
  `goal` text DEFAULT NULL,
  `budget` double DEFAULT NULL,
  `audience` text DEFAULT NULL,
  `status` varchar(191) NOT NULL DEFAULT 'DRAFT',
  `start_date` timestamp NULL DEFAULT NULL,
  `end_date` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaigns_agency_id_index` (`agency_id`),
  KEY `campaigns_client_id_index` (`client_id`),
  KEY `campaigns_status_index` (`status`),
  CONSTRAINT `campaigns_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaigns_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `campaigns`
--

LOCK TABLES `campaigns` WRITE;
/*!40000 ALTER TABLE `campaigns` DISABLE KEYS */;
/*!40000 ALTER TABLE `campaigns` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `checklist_items`
--

DROP TABLE IF EXISTS `checklist_items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `checklist_items` (
  `id` char(36) NOT NULL,
  `campaign_event_id` char(36) NOT NULL,
  `title` varchar(191) NOT NULL,
  `is_completed` tinyint(1) NOT NULL DEFAULT 0,
  `assigned_to_user_id` char(36) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `checklist_items_assigned_to_user_id_foreign` (`assigned_to_user_id`),
  KEY `checklist_items_campaign_event_id_index` (`campaign_event_id`),
  CONSTRAINT `checklist_items_assigned_to_user_id_foreign` FOREIGN KEY (`assigned_to_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `checklist_items_campaign_event_id_foreign` FOREIGN KEY (`campaign_event_id`) REFERENCES `campaign_events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `checklist_items`
--

LOCK TABLES `checklist_items` WRITE;
/*!40000 ALTER TABLE `checklist_items` DISABLE KEYS */;
/*!40000 ALTER TABLE `checklist_items` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `clients`
--

DROP TABLE IF EXISTS `clients`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `clients` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `logo_url` varchar(191) DEFAULT NULL,
  `website` varchar(191) DEFAULT NULL,
  `default_primary_color` varchar(191) DEFAULT NULL,
  `default_accent_color` varchar(191) DEFAULT NULL,
  `industry` varchar(191) DEFAULT NULL,
  `contact_name` varchar(191) DEFAULT NULL,
  `contact_email` varchar(191) DEFAULT NULL,
  `contact_phone` varchar(191) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clients_agency_id_index` (`agency_id`),
  KEY `clients_name_index` (`name`),
  CONSTRAINT `clients_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clients`
--

LOCK TABLES `clients` WRITE;
/*!40000 ALTER TABLE `clients` DISABLE KEYS */;
/*!40000 ALTER TABLE `clients` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `comments`
--

DROP TABLE IF EXISTS `comments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `comments` (
  `id` char(36) NOT NULL,
  `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,
  PRIMARY KEY (`id`),
  KEY `comments_user_id_foreign` (`user_id`),
  KEY `comments_agency_id_index` (`agency_id`),
  KEY `comments_press_release_id_index` (`press_release_id`),
  KEY `comments_task_id_index` (`task_id`),
  CONSTRAINT `comments_press_release_id_foreign` FOREIGN KEY (`press_release_id`) REFERENCES `press_releases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `comments_task_id_foreign` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `comments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `comments`
--

LOCK TABLES `comments` WRITE;
/*!40000 ALTER TABLE `comments` DISABLE KEYS */;
/*!40000 ALTER TABLE `comments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `coverage_items`
--

DROP TABLE IF EXISTS `coverage_items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coverage_items` (
  `id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `client_id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `title` varchar(191) NOT NULL,
  `source_name` varchar(191) NOT NULL,
  `source_url` varchar(191) DEFAULT NULL,
  `program` varchar(191) DEFAULT NULL,
  `broadcast_time` varchar(191) DEFAULT NULL,
  `page_section` varchar(191) DEFAULT NULL,
  `publisher` varchar(191) DEFAULT NULL,
  `platform_type` varchar(191) DEFAULT NULL,
  `engagement_likes` bigint(20) unsigned DEFAULT NULL,
  `engagement_comments` bigint(20) unsigned DEFAULT NULL,
  `engagement_shares` bigint(20) unsigned DEFAULT NULL,
  `engagement_views` bigint(20) unsigned DEFAULT NULL,
  `media_type` varchar(191) NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `include_in_report` tinyint(1) NOT NULL DEFAULT 1,
  `file_url` varchar(191) DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `coverage_items_campaign_id_index` (`campaign_id`),
  KEY `coverage_items_client_id_index` (`client_id`),
  KEY `coverage_items_agency_id_index` (`agency_id`),
  KEY `coverage_items_campaign_id_media_type_index` (`campaign_id`,`media_type`),
  CONSTRAINT `coverage_items_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `coverage_items_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `coverage_items`
--

LOCK TABLES `coverage_items` WRITE;
/*!40000 ALTER TABLE `coverage_items` DISABLE KEYS */;
/*!40000 ALTER TABLE `coverage_items` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `coverage_media`
--

DROP TABLE IF EXISTS `coverage_media`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coverage_media` (
  `id` char(36) NOT NULL,
  `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(191) NOT NULL,
  `file_url` varchar(191) NOT NULL,
  `media_type` varchar(191) NOT NULL,
  `source` varchar(191) DEFAULT NULL,
  `caption` text DEFAULT NULL,
  `uploaded_by_id` char(36) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `coverage_media_agency_id_index` (`agency_id`),
  KEY `coverage_media_client_id_index` (`client_id`),
  KEY `coverage_media_campaign_id_index` (`campaign_id`),
  KEY `coverage_media_coverage_item_id_index` (`coverage_item_id`),
  CONSTRAINT `coverage_media_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `coverage_media_coverage_item_id_foreign` FOREIGN KEY (`coverage_item_id`) REFERENCES `coverage_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `coverage_media`
--

LOCK TABLES `coverage_media` WRITE;
/*!40000 ALTER TABLE `coverage_media` DISABLE KEYS */;
/*!40000 ALTER TABLE `coverage_media` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `crisis_sessions`
--

DROP TABLE IF EXISTS `crisis_sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `crisis_sessions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `is_auto_detected` tinyint(1) NOT NULL DEFAULT 0,
  `created_by_user_id` char(36) DEFAULT NULL,
  `situation_description` text NOT NULL,
  `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`result`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `crisis_sessions_agency_id_index` (`agency_id`),
  KEY `crisis_sessions_client_id_index` (`client_id`),
  KEY `crisis_sessions_created_at_index` (`created_at`),
  CONSTRAINT `crisis_sessions_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `crisis_sessions`
--

LOCK TABLES `crisis_sessions` WRITE;
/*!40000 ALTER TABLE `crisis_sessions` DISABLE KEYS */;
/*!40000 ALTER TABLE `crisis_sessions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `distribution_events`
--

DROP TABLE IF EXISTS `distribution_events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `distribution_events` (
  `id` char(36) NOT NULL,
  `distribution_id` char(36) NOT NULL,
  `journalist_id` char(36) NOT NULL,
  `type` varchar(191) NOT NULL,
  `occurred_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `distribution_events_distribution_id_index` (`distribution_id`),
  KEY `distribution_events_journalist_id_index` (`journalist_id`),
  CONSTRAINT `distribution_events_distribution_id_foreign` FOREIGN KEY (`distribution_id`) REFERENCES `distributions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `distribution_events`
--

LOCK TABLES `distribution_events` WRITE;
/*!40000 ALTER TABLE `distribution_events` DISABLE KEYS */;
/*!40000 ALTER TABLE `distribution_events` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `distributions`
--

DROP TABLE IF EXISTS `distributions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `distributions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `press_release_id` char(36) NOT NULL,
  `sent_by_user_id` char(36) NOT NULL,
  `journalist_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`journalist_ids`)),
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `distributions_agency_id_index` (`agency_id`),
  KEY `distributions_press_release_id_index` (`press_release_id`),
  CONSTRAINT `distributions_press_release_id_foreign` FOREIGN KEY (`press_release_id`) REFERENCES `press_releases` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `distributions`
--

LOCK TABLES `distributions` WRITE;
/*!40000 ALTER TABLE `distributions` DISABLE KEYS */;
/*!40000 ALTER TABLE `distributions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `evidence_assets`
--

DROP TABLE IF EXISTS `evidence_assets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `evidence_assets` (
  `id` char(36) NOT NULL,
  `evidence_item_id` char(36) NOT NULL,
  `file_name` varchar(191) NOT NULL,
  `file_url` varchar(191) NOT NULL,
  `asset_type` varchar(191) NOT NULL,
  `source` varchar(191) DEFAULT NULL,
  `caption` text DEFAULT NULL,
  `uploaded_by_id` char(36) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `legacy_coverage_media_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `evidence_assets_legacy_coverage_media_id_unique` (`legacy_coverage_media_id`),
  KEY `evidence_assets_evidence_item_id_index` (`evidence_item_id`),
  CONSTRAINT `evidence_assets_evidence_item_id_foreign` FOREIGN KEY (`evidence_item_id`) REFERENCES `evidence_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `evidence_assets`
--

LOCK TABLES `evidence_assets` WRITE;
/*!40000 ALTER TABLE `evidence_assets` DISABLE KEYS */;
/*!40000 ALTER TABLE `evidence_assets` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `evidence_items`
--

DROP TABLE IF EXISTS `evidence_items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `evidence_items` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `title` varchar(191) NOT NULL,
  `source_name` varchar(191) DEFAULT NULL,
  `source_url` varchar(191) DEFAULT NULL,
  `media_type` varchar(191) NOT NULL,
  `source` varchar(191) NOT NULL DEFAULT 'MANUAL',
  `sentiment` varchar(191) DEFAULT NULL,
  `sentiment_reason` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `discovered_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `legacy_coverage_item_id` char(36) DEFAULT NULL,
  `legacy_media_mention_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `evidence_items_legacy_coverage_item_id_unique` (`legacy_coverage_item_id`),
  UNIQUE KEY `evidence_items_legacy_media_mention_id_unique` (`legacy_media_mention_id`),
  KEY `evidence_items_agency_id_index` (`agency_id`),
  KEY `evidence_items_client_id_index` (`client_id`),
  KEY `evidence_items_campaign_id_index` (`campaign_id`),
  KEY `evidence_items_sentiment_index` (`sentiment`),
  KEY `evidence_items_published_at_index` (`published_at`),
  CONSTRAINT `evidence_items_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `evidence_items_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `evidence_items_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `evidence_items`
--

LOCK TABLES `evidence_items` WRITE;
/*!40000 ALTER TABLE `evidence_items` DISABLE KEYS */;
/*!40000 ALTER TABLE `evidence_items` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `field_responses`
--

DROP TABLE IF EXISTS `field_responses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `field_responses` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `field_study_id` char(36) NOT NULL,
  `panelist_id` char(36) DEFAULT NULL,
  `panelist_ref` varchar(32) DEFAULT NULL,
  `channel` varchar(20) NOT NULL DEFAULT 'USSD',
  `session_id` varchar(191) DEFAULT NULL,
  `answers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`answers`)),
  `ward` varchar(191) DEFAULT NULL,
  `district` varchar(191) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `outlet_name` varchar(191) DEFAULT NULL,
  `observed_price` decimal(12,2) DEFAULT NULL,
  `product_present` tinyint(1) DEFAULT NULL,
  `media` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`media`)),
  `photo_hash` varchar(64) DEFAULT NULL,
  `verification_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `auto_verification_status` varchar(20) DEFAULT NULL,
  `verification_flags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`verification_flags`)),
  `verified_at` timestamp NULL DEFAULT NULL,
  `verified_by_user_id` char(36) DEFAULT NULL,
  `overridden_at` timestamp NULL DEFAULT NULL,
  `overridden_by_user_id` char(36) DEFAULT NULL,
  `review_note` varchar(191) DEFAULT NULL,
  `duration_seconds` int(10) unsigned DEFAULT NULL,
  `reward_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reward_paid_at` timestamp NULL DEFAULT NULL,
  `is_billable` tinyint(1) NOT NULL DEFAULT 0,
  `submitted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `field_responses_study_panelist_unique` (`field_study_id`,`panelist_id`),
  KEY `field_responses_panelist_id_foreign` (`panelist_id`),
  KEY `field_responses_field_study_id_verification_status_index` (`field_study_id`,`verification_status`),
  KEY `field_responses_agency_id_submitted_at_index` (`agency_id`,`submitted_at`),
  KEY `field_responses_photo_hash_index` (`photo_hash`),
  KEY `field_responses_overridden_at_index` (`overridden_at`),
  CONSTRAINT `field_responses_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_responses_field_study_id_foreign` FOREIGN KEY (`field_study_id`) REFERENCES `field_studies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_responses_panelist_id_foreign` FOREIGN KEY (`panelist_id`) REFERENCES `panelists` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `field_responses`
--

LOCK TABLES `field_responses` WRITE;
/*!40000 ALTER TABLE `field_responses` DISABLE KEYS */;
/*!40000 ALTER TABLE `field_responses` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `field_studies`
--

DROP TABLE IF EXISTS `field_studies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `field_studies` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `public_label` varchar(60) DEFAULT NULL,
  `objective` text DEFAULT NULL,
  `type` varchar(30) NOT NULL DEFAULT 'CONSUMER_SURVEY',
  `target_wards` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`target_wards`)),
  `target_region` varchar(191) DEFAULT NULL,
  `target_responses` int(10) unsigned NOT NULL DEFAULT 100,
  `min_trust_score` tinyint(3) unsigned NOT NULL DEFAULT 40,
  `price_per_response` decimal(12,2) NOT NULL DEFAULT 4000.00,
  `platform_price_per_response` decimal(12,2) NOT NULL DEFAULT 2500.00,
  `reward_per_response` decimal(12,2) NOT NULL DEFAULT 800.00,
  `status` varchar(20) NOT NULL DEFAULT 'DRAFT',
  `recurrence` varchar(20) NOT NULL DEFAULT 'NONE',
  `tracker_parent_id` char(36) DEFAULT NULL,
  `wave` smallint(5) unsigned NOT NULL DEFAULT 1,
  `starts_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `dial_key` varchar(10) DEFAULT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `field_studies_client_id_foreign` (`client_id`),
  KEY `field_studies_agency_id_status_index` (`agency_id`,`status`),
  KEY `field_studies_campaign_id_index` (`campaign_id`),
  KEY `field_studies_dial_key_index` (`dial_key`),
  KEY `field_studies_tracker_parent_id_wave_index` (`tracker_parent_id`,`wave`),
  KEY `field_studies_recurrence_index` (`recurrence`),
  CONSTRAINT `field_studies_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_studies_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `field_studies_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_studies_tracker_parent_id_foreign` FOREIGN KEY (`tracker_parent_id`) REFERENCES `field_studies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `field_studies`
--

LOCK TABLES `field_studies` WRITE;
/*!40000 ALTER TABLE `field_studies` DISABLE KEYS */;
/*!40000 ALTER TABLE `field_studies` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `field_study_invitations`
--

DROP TABLE IF EXISTS `field_study_invitations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `field_study_invitations` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `field_study_id` char(36) NOT NULL,
  `panelist_id` char(36) NOT NULL,
  `channel` varchar(20) NOT NULL DEFAULT 'SMS',
  `status` varchar(20) NOT NULL DEFAULT 'QUEUED',
  `sent_at` timestamp NULL DEFAULT NULL,
  `responded_at` timestamp NULL DEFAULT NULL,
  `failed_reason` varchar(191) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invitations_study_panelist_unique` (`field_study_id`,`panelist_id`),
  KEY `field_study_invitations_agency_id_foreign` (`agency_id`),
  KEY `field_study_invitations_field_study_id_status_index` (`field_study_id`,`status`),
  KEY `field_study_invitations_panelist_id_created_at_index` (`panelist_id`,`created_at`),
  CONSTRAINT `field_study_invitations_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_study_invitations_field_study_id_foreign` FOREIGN KEY (`field_study_id`) REFERENCES `field_studies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_study_invitations_panelist_id_foreign` FOREIGN KEY (`panelist_id`) REFERENCES `panelists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `field_study_invitations`
--

LOCK TABLES `field_study_invitations` WRITE;
/*!40000 ALTER TABLE `field_study_invitations` DISABLE KEYS */;
/*!40000 ALTER TABLE `field_study_invitations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `field_study_questions`
--

DROP TABLE IF EXISTS `field_study_questions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `field_study_questions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `field_study_id` char(36) NOT NULL,
  `position` smallint(5) unsigned NOT NULL DEFAULT 1,
  `prompt` varchar(500) NOT NULL,
  `prompt_sw` varchar(500) DEFAULT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'CHOICE',
  `maps_to` varchar(30) DEFAULT NULL,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `is_required` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `field_study_questions_agency_id_foreign` (`agency_id`),
  KEY `field_study_questions_field_study_id_position_index` (`field_study_id`,`position`),
  CONSTRAINT `field_study_questions_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `field_study_questions_field_study_id_foreign` FOREIGN KEY (`field_study_id`) REFERENCES `field_studies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `field_study_questions`
--

LOCK TABLES `field_study_questions` WRITE;
/*!40000 ALTER TABLE `field_study_questions` DISABLE KEYS */;
/*!40000 ALTER TABLE `field_study_questions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `influencers`
--

DROP TABLE IF EXISTS `influencers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `influencers` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `platform` varchar(191) NOT NULL,
  `handle` varchar(191) NOT NULL,
  `follower_count` int(11) DEFAULT NULL,
  `engagement_rate` double DEFAULT NULL,
  `industry` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`industry`)),
  `location` varchar(191) DEFAULT NULL,
  `estimated_price_range` varchar(191) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `influencers_agency_id_index` (`agency_id`),
  KEY `influencers_platform_index` (`platform`),
  KEY `influencers_handle_index` (`handle`),
  CONSTRAINT `influencers_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `influencers`
--

LOCK TABLES `influencers` WRITE;
/*!40000 ALTER TABLE `influencers` DISABLE KEYS */;
/*!40000 ALTER TABLE `influencers` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `invoice_line_items`
--

DROP TABLE IF EXISTS `invoice_line_items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `invoice_line_items` (
  `id` char(36) NOT NULL,
  `invoice_id` char(36) NOT NULL,
  `description` varchar(191) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `invoice_line_items_invoice_id_index` (`invoice_id`),
  CONSTRAINT `invoice_line_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `invoice_line_items`
--

LOCK TABLES `invoice_line_items` WRITE;
/*!40000 ALTER TABLE `invoice_line_items` DISABLE KEYS */;
/*!40000 ALTER TABLE `invoice_line_items` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `invoices`
--

DROP TABLE IF EXISTS `invoices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `invoices` (
  `id` char(36) NOT NULL,
  `invoice_number` varchar(191) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `status` varchar(191) NOT NULL DEFAULT 'DRAFT',
  `issue_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `due_date` timestamp NULL DEFAULT NULL,
  `currency` varchar(191) NOT NULL DEFAULT 'TZS',
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoices_agency_number_unique` (`agency_id`,`invoice_number`),
  KEY `invoices_campaign_id_foreign` (`campaign_id`),
  KEY `invoices_agency_id_index` (`agency_id`),
  KEY `invoices_client_id_index` (`client_id`),
  KEY `invoices_invoice_number_index` (`invoice_number`),
  CONSTRAINT `invoices_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `invoices`
--

LOCK TABLES `invoices` WRITE;
/*!40000 ALTER TABLE `invoices` DISABLE KEYS */;
/*!40000 ALTER TABLE `invoices` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `job_batches`
--

DROP TABLE IF EXISTS `job_batches`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `job_batches` (
  `id` varchar(191) NOT NULL,
  `name` varchar(191) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `job_batches`
--

LOCK TABLES `job_batches` WRITE;
/*!40000 ALTER TABLE `job_batches` DISABLE KEYS */;
/*!40000 ALTER TABLE `job_batches` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `jobs`
--

DROP TABLE IF EXISTS `jobs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(191) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `jobs`
--

LOCK TABLES `jobs` WRITE;
/*!40000 ALTER TABLE `jobs` DISABLE KEYS */;
/*!40000 ALTER TABLE `jobs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `journalist_interactions`
--

DROP TABLE IF EXISTS `journalist_interactions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `journalist_interactions` (
  `id` char(36) NOT NULL,
  `journalist_id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `type` varchar(191) NOT NULL,
  `summary` text NOT NULL,
  `created_by_user_id` char(36) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `journalist_interactions_journalist_id_index` (`journalist_id`),
  KEY `journalist_interactions_agency_id_index` (`agency_id`),
  CONSTRAINT `journalist_interactions_journalist_id_foreign` FOREIGN KEY (`journalist_id`) REFERENCES `journalists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `journalist_interactions`
--

LOCK TABLES `journalist_interactions` WRITE;
/*!40000 ALTER TABLE `journalist_interactions` DISABLE KEYS */;
/*!40000 ALTER TABLE `journalist_interactions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `journalist_list_journalist`
--

DROP TABLE IF EXISTS `journalist_list_journalist`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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`),
  KEY `journalist_list_journalist_journalist_id_foreign` (`journalist_id`),
  CONSTRAINT `journalist_list_journalist_journalist_id_foreign` FOREIGN KEY (`journalist_id`) REFERENCES `journalists` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journalist_list_journalist_journalist_list_id_foreign` FOREIGN KEY (`journalist_list_id`) REFERENCES `journalist_lists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `journalist_list_journalist`
--

LOCK TABLES `journalist_list_journalist` WRITE;
/*!40000 ALTER TABLE `journalist_list_journalist` DISABLE KEYS */;
/*!40000 ALTER TABLE `journalist_list_journalist` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `journalist_lists`
--

DROP TABLE IF EXISTS `journalist_lists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `journalist_lists` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `journalist_lists_agency_id_index` (`agency_id`),
  CONSTRAINT `journalist_lists_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `journalist_lists`
--

LOCK TABLES `journalist_lists` WRITE;
/*!40000 ALTER TABLE `journalist_lists` DISABLE KEYS */;
/*!40000 ALTER TABLE `journalist_lists` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `journalists`
--

DROP TABLE IF EXISTS `journalists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `journalists` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `full_name` varchar(191) NOT NULL,
  `media_house` varchar(191) NOT NULL,
  `email` varchar(191) DEFAULT NULL,
  `phone` varchar(191) DEFAULT NULL,
  `beats` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`beats`)),
  `preferred_contact_method` varchar(191) 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,
  PRIMARY KEY (`id`),
  KEY `journalists_agency_id_index` (`agency_id`),
  KEY `journalists_full_name_index` (`full_name`),
  KEY `journalists_media_house_index` (`media_house`),
  CONSTRAINT `journalists_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `journalists`
--

LOCK TABLES `journalists` WRITE;
/*!40000 ALTER TABLE `journalists` DISABLE KEYS */;
/*!40000 ALTER TABLE `journalists` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `login_attempts`
--

DROP TABLE IF EXISTS `login_attempts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `login_attempts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(191) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(191) DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `login_attempts_email_created_at_index` (`email`,`created_at`),
  KEY `login_attempts_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `login_attempts`
--

LOCK TABLES `login_attempts` WRITE;
/*!40000 ALTER TABLE `login_attempts` DISABLE KEYS */;
/*!40000 ALTER TABLE `login_attempts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `media`
--

DROP TABLE IF EXISTS `media`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `media` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `model_type` varchar(191) NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  `uuid` char(36) DEFAULT NULL,
  `collection_name` varchar(191) NOT NULL,
  `name` varchar(191) NOT NULL,
  `file_name` varchar(191) NOT NULL,
  `mime_type` varchar(191) DEFAULT NULL,
  `disk` varchar(191) NOT NULL,
  `conversions_disk` varchar(191) DEFAULT NULL,
  `size` bigint(20) unsigned NOT NULL,
  `manipulations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`manipulations`)),
  `custom_properties` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`custom_properties`)),
  `generated_conversions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`generated_conversions`)),
  `responsive_images` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`responsive_images`)),
  `order_column` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `media_uuid_unique` (`uuid`),
  KEY `media_model_type_model_id_index` (`model_type`,`model_id`),
  KEY `media_order_column_index` (`order_column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `media`
--

LOCK TABLES `media` WRITE;
/*!40000 ALTER TABLE `media` DISABLE KEYS */;
/*!40000 ALTER TABLE `media` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `media_list_contacts`
--

DROP TABLE IF EXISTS `media_list_contacts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `media_list_contacts` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `media_list_id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `type` varchar(191) NOT NULL DEFAULT 'INFLUENCER',
  `handles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`handles`)),
  `contact_email` varchar(191) DEFAULT NULL,
  `contact_phone` varchar(191) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `media_list_contacts_agency_id_foreign` (`agency_id`),
  KEY `media_list_contacts_media_list_id_index` (`media_list_id`),
  CONSTRAINT `media_list_contacts_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `media_list_contacts_media_list_id_foreign` FOREIGN KEY (`media_list_id`) REFERENCES `media_lists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `media_list_contacts`
--

LOCK TABLES `media_list_contacts` WRITE;
/*!40000 ALTER TABLE `media_list_contacts` DISABLE KEYS */;
/*!40000 ALTER TABLE `media_list_contacts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `media_lists`
--

DROP TABLE IF EXISTS `media_lists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `media_lists` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `description` varchar(191) DEFAULT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `media_lists_agency_id_index` (`agency_id`),
  CONSTRAINT `media_lists_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `media_lists`
--

LOCK TABLES `media_lists` WRITE;
/*!40000 ALTER TABLE `media_lists` DISABLE KEYS */;
/*!40000 ALTER TABLE `media_lists` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `media_mentions`
--

DROP TABLE IF EXISTS `media_mentions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `media_mentions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `keyword_id` char(36) DEFAULT NULL,
  `source_url` varchar(191) NOT NULL,
  `source_name` varchar(191) NOT NULL,
  `title` varchar(191) NOT NULL,
  `snippet` text NOT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `sentiment` varchar(191) DEFAULT NULL,
  `sentiment_reason` text DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'NEW',
  `coverage_item_id` char(36) DEFAULT NULL,
  `platform` varchar(191) NOT NULL DEFAULT 'NEWS',
  `author` varchar(191) DEFAULT NULL,
  `engagement_likes` bigint(20) unsigned DEFAULT NULL,
  `engagement_comments` bigint(20) unsigned DEFAULT NULL,
  `engagement_shares` bigint(20) unsigned DEFAULT NULL,
  `engagement_views` bigint(20) unsigned DEFAULT NULL,
  `engagement_checked_at` timestamp NULL DEFAULT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `discovered_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `media_mentions_agency_id_index` (`agency_id`),
  KEY `media_mentions_client_id_index` (`client_id`),
  KEY `media_mentions_sentiment_index` (`sentiment`),
  KEY `media_mentions_discovered_at_index` (`discovered_at`),
  KEY `media_mentions_source_url_index` (`source_url`),
  KEY `media_mentions_client_id_discovered_at_index` (`client_id`,`discovered_at`),
  KEY `media_mentions_campaign_id_index` (`campaign_id`),
  KEY `media_mentions_status_index` (`status`),
  KEY `media_mentions_coverage_item_id_index` (`coverage_item_id`),
  KEY `media_mentions_keyword_id_index` (`keyword_id`),
  CONSTRAINT `media_mentions_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `media_mentions`
--

LOCK TABLES `media_mentions` WRITE;
/*!40000 ALTER TABLE `media_mentions` DISABLE KEYS */;
/*!40000 ALTER TABLE `media_mentions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `migrations`
--

DROP TABLE IF EXISTS `migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(191) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `migrations`
--

LOCK TABLES `migrations` WRITE;
/*!40000 ALTER TABLE `migrations` DISABLE KEYS */;
INSERT INTO `migrations` VALUES (1,'2026_07_12_155634_create_all_tables',1),(2,'2026_07_12_211942_create_personal_access_tokens_table',1),(3,'2026_07_12_220000_create_media_table',1),(4,'2026_07_13_114628_create_site_settings_table',1),(5,'2026_07_13_114638_add_is_super_admin_to_users_table',1),(6,'2026_07_13_114640_add_status_to_agencies_table',1),(7,'2026_07_18_120000_add_report_metadata',1),(8,'2026_07_18_140000_add_super_admin_tables',1),(9,'2026_07_18_160000_add_last_login_to_users',1),(10,'2026_07_19_000001_create_jobs_table',1),(11,'2026_07_19_000002_create_job_batches_table',1),(12,'2026_07_19_000003_add_performance_indexes',1),(13,'2026_07_21_000001_create_app_logs_table',1),(14,'2026_07_21_100001_add_monitoring_and_fcm_fields',1),(15,'2026_07_21_114542_add_monitoring_fields_to_media_mentions_and_users',1),(16,'2026_07_21_150000_add_campaign_communication_fields',1),(17,'2026_07_21_151000_add_coverage_detail_fields',1),(18,'2026_07_23_180848_create_evidence_tables',1),(19,'2026_07_24_010000_fix_platform_audit_logs_admin_reference',1),(20,'2026_07_24_020000_fix_notifications_table_schema',1),(21,'2026_07_24_030000_add_contact_fields_to_agencies_table',1),(22,'2026_07_25_120000_add_logo_url_to_clients',1),(23,'2026_07_25_130000_add_emv_rates_to_agencies',1),(24,'2026_07_25_140000_add_engagement_metrics_to_coverage_items',1),(25,'2026_07_25_150000_add_registrations_enabled_to_site_settings',1),(26,'2026_07_31_000000_add_campaign_id_to_media_mentions',1),(27,'2026_08_01_000000_add_status_and_coverage_item_id_to_media_mentions',1),(28,'2026_08_01_010000_make_coverage_items_created_by_nullable',1),(29,'2026_08_01_020000_add_competitor_tracking',1),(30,'2026_08_01_030000_add_auto_detected_to_crisis_sessions',1),(31,'2026_08_01_040000_create_pitch_drafts_table',1),(32,'2026_08_01_050000_create_whatsapp_intakes_table',1),(33,'2026_08_02_010000_add_missing_user_profile_columns',1),(34,'2026_08_03_010000_create_plans_table',1),(35,'2026_08_03_020000_create_support_tables',1),(36,'2026_08_03_030000_add_read_counts_to_support_conversations',1),(37,'2026_08_03_040000_drop_feature_flags_table',1),(38,'2026_08_05_010000_backfill_coverage_evidence_sync',1),(39,'2026_08_13_120000_update_plan_features_report_template_copy',1),(40,'2026_08_13_130000_add_account_type_to_agencies',1),(41,'2026_08_14_010000_add_report_branding_to_clients',1),(42,'2026_08_15_140000_add_outlet_rates_to_agencies',1),(43,'2026_08_15_150000_add_report_ordering_to_coverage_items',1),(44,'2026_08_15_160000_create_campaign_distributions_table',1),(45,'2026_08_15_190000_create_media_lists_table',1),(46,'2026_08_15_200000_add_engagement_to_media_mentions',1),(47,'2026_08_15_210000_create_campaign_assets_table',1),(48,'2026_08_15_210100_add_delivery_tracking_to_campaign_distributions',1),(49,'2026_08_15_220000_make_optional_press_release_fields_nullable',1),(50,'2026_08_20_010000_add_ai_design_to_reports',1),(51,'2026_08_21_010000_add_renewal_dunning_to_subscriptions',1),(52,'2026_08_21_020000_add_notification_delivery_and_email_prefs',1),(53,'2026_08_21_030000_add_ui_preferences_to_users',1),(54,'2026_08_21_040000_create_page_visits_table',1),(55,'2026_08_21_050000_add_locale_to_users',1),(56,'2026_08_21_060000_add_settings_to_agencies',1),(57,'2026_08_26_010000_create_panelists_table',1),(58,'2026_08_26_010100_create_field_studies_table',1),(59,'2026_08_26_010200_create_field_study_questions_table',1),(60,'2026_08_26_010300_create_field_responses_table',1),(61,'2026_08_26_020000_create_panel_recruiters_table',1),(62,'2026_08_26_020100_create_panel_reward_entries_table',1),(63,'2026_08_26_020200_create_panel_payouts_table',1),(64,'2026_08_26_020300_create_field_study_invitations_table',1),(65,'2026_08_26_020400_add_review_to_field_responses',1),(66,'2026_08_27_010000_create_panel_whatsapp_sessions_table',1),(67,'2026_08_27_020000_scope_invoice_numbers_to_agency',1),(68,'2026_08_27_030000_add_platform_pricing_and_trackers',1),(69,'2026_08_27_040000_add_tour_completed_to_users',1),(70,'2026_08_28_010000_create_work_submissions_table',1),(71,'2026_08_28_010100_allow_users_without_an_agency',1);
/*!40000 ALTER TABLE `migrations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `monitoring_keywords`
--

DROP TABLE IF EXISTS `monitoring_keywords`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `monitoring_keywords` (
  `id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `keyword` varchar(191) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'OWN',
  `competitor_label` varchar(191) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `monitoring_keywords_client_id_keyword_unique` (`client_id`,`keyword`),
  KEY `monitoring_keywords_client_id_index` (`client_id`),
  CONSTRAINT `monitoring_keywords_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `monitoring_keywords`
--

LOCK TABLES `monitoring_keywords` WRITE;
/*!40000 ALTER TABLE `monitoring_keywords` DISABLE KEYS */;
/*!40000 ALTER TABLE `monitoring_keywords` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `news_angle_sets`
--

DROP TABLE IF EXISTS `news_angle_sets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `news_angle_sets` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `prompt` text NOT NULL,
  `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`result`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `news_angle_sets_agency_id_index` (`agency_id`),
  KEY `news_angle_sets_client_id_index` (`client_id`),
  KEY `news_angle_sets_created_at_index` (`created_at`),
  CONSTRAINT `news_angle_sets_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `news_angle_sets`
--

LOCK TABLES `news_angle_sets` WRITE;
/*!40000 ALTER TABLE `news_angle_sets` DISABLE KEYS */;
/*!40000 ALTER TABLE `news_angle_sets` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `notifications`
--

DROP TABLE IF EXISTS `notifications`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `notifications` (
  `id` char(36) NOT NULL,
  `type` varchar(191) NOT NULL,
  `notifiable_type` varchar(191) NOT NULL,
  `notifiable_id` char(36) NOT NULL,
  `data` text NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `emailed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_notifiable_type_notifiable_id_index` (`notifiable_type`,`notifiable_id`),
  KEY `notifications_escalation_idx` (`read_at`,`emailed_at`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `notifications`
--

LOCK TABLES `notifications` WRITE;
/*!40000 ALTER TABLE `notifications` DISABLE KEYS */;
/*!40000 ALTER TABLE `notifications` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `page_visits`
--

DROP TABLE IF EXISTS `page_visits`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `page_visits` (
  `id` char(36) NOT NULL,
  `visitor_id` varchar(64) NOT NULL,
  `session_id` varchar(64) DEFAULT NULL,
  `path` varchar(512) NOT NULL,
  `referrer_host` varchar(255) DEFAULT NULL,
  `referrer` varchar(512) DEFAULT NULL,
  `utm_source` varchar(120) DEFAULT NULL,
  `utm_medium` varchar(120) DEFAULT NULL,
  `utm_campaign` varchar(120) DEFAULT NULL,
  `device_type` varchar(20) DEFAULT NULL,
  `browser` varchar(60) DEFAULT NULL,
  `platform` varchar(60) DEFAULT NULL,
  `country` varchar(2) DEFAULT NULL,
  `ip_hash` varchar(64) DEFAULT NULL,
  `is_bot` tinyint(1) NOT NULL DEFAULT 0,
  `user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `page_visits_visitor_id_index` (`visitor_id`),
  KEY `page_visits_session_id_index` (`session_id`),
  KEY `page_visits_referrer_host_index` (`referrer_host`),
  KEY `page_visits_utm_source_index` (`utm_source`),
  KEY `page_visits_device_type_index` (`device_type`),
  KEY `page_visits_country_index` (`country`),
  KEY `page_visits_ip_hash_index` (`ip_hash`),
  KEY `page_visits_is_bot_index` (`is_bot`),
  KEY `page_visits_user_id_index` (`user_id`),
  KEY `page_visits_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `page_visits`
--

LOCK TABLES `page_visits` WRITE;
/*!40000 ALTER TABLE `page_visits` DISABLE KEYS */;
/*!40000 ALTER TABLE `page_visits` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panel_payout_items`
--

DROP TABLE IF EXISTS `panel_payout_items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panel_payout_items` (
  `id` char(36) NOT NULL,
  `panel_payout_id` char(36) NOT NULL,
  `panelist_id` char(36) NOT NULL,
  `msisdn` varchar(20) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `provider_reference` varchar(191) DEFAULT NULL,
  `failed_reason` varchar(191) DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payout_items_batch_panelist_unique` (`panel_payout_id`,`panelist_id`),
  KEY `panel_payout_items_panelist_id_foreign` (`panelist_id`),
  KEY `panel_payout_items_panel_payout_id_status_index` (`panel_payout_id`,`status`),
  CONSTRAINT `panel_payout_items_panel_payout_id_foreign` FOREIGN KEY (`panel_payout_id`) REFERENCES `panel_payouts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `panel_payout_items_panelist_id_foreign` FOREIGN KEY (`panelist_id`) REFERENCES `panelists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panel_payout_items`
--

LOCK TABLES `panel_payout_items` WRITE;
/*!40000 ALTER TABLE `panel_payout_items` DISABLE KEYS */;
/*!40000 ALTER TABLE `panel_payout_items` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panel_payouts`
--

DROP TABLE IF EXISTS `panel_payouts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panel_payouts` (
  `id` char(36) NOT NULL,
  `reference` varchar(30) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'DRAFT',
  `panelist_count` int(10) unsigned NOT NULL DEFAULT 0,
  `total_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `channel` varchar(20) NOT NULL DEFAULT 'AIRTIME',
  `provider` varchar(40) DEFAULT NULL,
  `minimum_balance` decimal(12,2) NOT NULL DEFAULT 1000.00,
  `created_by_user_id` char(36) DEFAULT NULL,
  `approved_by_user_id` char(36) DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `executed_at` timestamp NULL DEFAULT NULL,
  `failure_reason` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `panel_payouts_reference_unique` (`reference`),
  KEY `panel_payouts_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panel_payouts`
--

LOCK TABLES `panel_payouts` WRITE;
/*!40000 ALTER TABLE `panel_payouts` DISABLE KEYS */;
/*!40000 ALTER TABLE `panel_payouts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panel_recruiters`
--

DROP TABLE IF EXISTS `panel_recruiters`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panel_recruiters` (
  `id` char(36) NOT NULL,
  `code` varchar(20) NOT NULL,
  `name` varchar(191) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'AGENT',
  `phone` varchar(20) DEFAULT NULL,
  `region` varchar(191) DEFAULT NULL,
  `district` varchar(191) DEFAULT NULL,
  `ward` varchar(191) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
  `commission_per_signup` decimal(12,2) NOT NULL DEFAULT 1000.00,
  `commission_owed` decimal(12,2) NOT NULL DEFAULT 0.00,
  `commission_paid` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `suspended_at` timestamp NULL DEFAULT NULL,
  `suspended_reason` varchar(191) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `panel_recruiters_code_unique` (`code`),
  KEY `panel_recruiters_status_type_index` (`status`,`type`),
  KEY `panel_recruiters_region_ward_index` (`region`,`ward`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panel_recruiters`
--

LOCK TABLES `panel_recruiters` WRITE;
/*!40000 ALTER TABLE `panel_recruiters` DISABLE KEYS */;
/*!40000 ALTER TABLE `panel_recruiters` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panel_reward_entries`
--

DROP TABLE IF EXISTS `panel_reward_entries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panel_reward_entries` (
  `id` char(36) NOT NULL,
  `panelist_id` char(36) NOT NULL,
  `type` varchar(20) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `balance_after` decimal(12,2) NOT NULL,
  `field_response_id` char(36) DEFAULT NULL,
  `panel_payout_id` char(36) DEFAULT NULL,
  `description` varchar(191) DEFAULT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reward_entries_response_type_unique` (`field_response_id`,`type`),
  KEY `panel_reward_entries_panelist_id_created_at_index` (`panelist_id`,`created_at`),
  KEY `panel_reward_entries_type_index` (`type`),
  CONSTRAINT `panel_reward_entries_field_response_id_foreign` FOREIGN KEY (`field_response_id`) REFERENCES `field_responses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `panel_reward_entries_panelist_id_foreign` FOREIGN KEY (`panelist_id`) REFERENCES `panelists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panel_reward_entries`
--

LOCK TABLES `panel_reward_entries` WRITE;
/*!40000 ALTER TABLE `panel_reward_entries` DISABLE KEYS */;
/*!40000 ALTER TABLE `panel_reward_entries` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panel_whatsapp_sessions`
--

DROP TABLE IF EXISTS `panel_whatsapp_sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panel_whatsapp_sessions` (
  `id` char(36) NOT NULL,
  `msisdn` varchar(20) NOT NULL,
  `panelist_id` char(36) DEFAULT NULL,
  `field_study_id` char(36) DEFAULT NULL,
  `step` varchar(30) NOT NULL DEFAULT 'AWAITING_STUDY',
  `question_index` smallint(5) unsigned NOT NULL DEFAULT 0,
  `answers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`answers`)),
  `media` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`media`)),
  `photo_hash` varchar(64) DEFAULT NULL,
  `outlet_name` varchar(191) DEFAULT NULL,
  `offered_study_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`offered_study_ids`)),
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `panel_whatsapp_sessions_msisdn_unique` (`msisdn`),
  KEY `panel_whatsapp_sessions_panelist_id_foreign` (`panelist_id`),
  KEY `panel_whatsapp_sessions_field_study_id_foreign` (`field_study_id`),
  KEY `panel_whatsapp_sessions_expires_at_index` (`expires_at`),
  CONSTRAINT `panel_whatsapp_sessions_field_study_id_foreign` FOREIGN KEY (`field_study_id`) REFERENCES `field_studies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `panel_whatsapp_sessions_panelist_id_foreign` FOREIGN KEY (`panelist_id`) REFERENCES `panelists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panel_whatsapp_sessions`
--

LOCK TABLES `panel_whatsapp_sessions` WRITE;
/*!40000 ALTER TABLE `panel_whatsapp_sessions` DISABLE KEYS */;
/*!40000 ALTER TABLE `panel_whatsapp_sessions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panelists`
--

DROP TABLE IF EXISTS `panelists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panelists` (
  `id` char(36) NOT NULL,
  `msisdn` varchar(20) NOT NULL,
  `name` varchar(191) DEFAULT NULL,
  `region` varchar(191) DEFAULT NULL,
  `district` varchar(191) DEFAULT NULL,
  `ward` varchar(191) DEFAULT NULL,
  `age_band` varchar(20) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `tier` varchar(20) NOT NULL DEFAULT 'USSD',
  `status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `suspended_at` timestamp NULL DEFAULT NULL,
  `suspended_reason` varchar(191) DEFAULT NULL,
  `recruited_via` varchar(30) DEFAULT NULL,
  `recruiter_ref` varchar(191) DEFAULT NULL,
  `recruiter_id` char(36) DEFAULT NULL,
  `referred_by_panelist_id` char(36) DEFAULT NULL,
  `reward_balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `lifetime_earned` decimal(12,2) NOT NULL DEFAULT 0.00,
  `completed_count` int(10) unsigned NOT NULL DEFAULT 0,
  `rejected_count` int(10) unsigned NOT NULL DEFAULT 0,
  `trust_score` tinyint(3) unsigned NOT NULL DEFAULT 50,
  `profile` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`profile`)),
  `registered_at` timestamp NULL DEFAULT NULL,
  `last_active_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `panelists_msisdn_unique` (`msisdn`),
  KEY `panelists_status_tier_index` (`status`,`tier`),
  KEY `panelists_region_district_ward_index` (`region`,`district`,`ward`),
  KEY `panelists_trust_score_index` (`trust_score`),
  KEY `panelists_recruiter_id_index` (`recruiter_id`),
  CONSTRAINT `panelists_recruiter_id_foreign` FOREIGN KEY (`recruiter_id`) REFERENCES `panel_recruiters` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panelists`
--

LOCK TABLES `panelists` WRITE;
/*!40000 ALTER TABLE `panelists` DISABLE KEYS */;
/*!40000 ALTER TABLE `panelists` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `password_reset_tokens`
--

DROP TABLE IF EXISTS `password_reset_tokens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `password_reset_tokens` (
  `id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `token_hash` varchar(191) NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `password_reset_tokens_user_id_foreign` (`user_id`),
  KEY `password_reset_tokens_token_hash_index` (`token_hash`),
  CONSTRAINT `password_reset_tokens_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `password_reset_tokens`
--

LOCK TABLES `password_reset_tokens` WRITE;
/*!40000 ALTER TABLE `password_reset_tokens` DISABLE KEYS */;
/*!40000 ALTER TABLE `password_reset_tokens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `payment_transactions`
--

DROP TABLE IF EXISTS `payment_transactions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `payment_transactions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `provider` varchar(191) NOT NULL,
  `provider_transaction_id` varchar(191) DEFAULT NULL,
  `type` varchar(191) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(191) NOT NULL DEFAULT 'USD',
  `status` varchar(191) NOT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `payment_transactions_agency_id_index` (`agency_id`),
  KEY `payment_transactions_status_created_at_index` (`status`,`created_at`),
  CONSTRAINT `payment_transactions_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `payment_transactions`
--

LOCK TABLES `payment_transactions` WRITE;
/*!40000 ALTER TABLE `payment_transactions` DISABLE KEYS */;
/*!40000 ALTER TABLE `payment_transactions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `personal_access_tokens`
--

DROP TABLE IF EXISTS `personal_access_tokens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `personal_access_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(191) NOT NULL,
  `tokenable_id` bigint(20) unsigned NOT NULL,
  `name` varchar(191) NOT NULL,
  `token` varchar(64) NOT NULL,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `personal_access_tokens`
--

LOCK TABLES `personal_access_tokens` WRITE;
/*!40000 ALTER TABLE `personal_access_tokens` DISABLE KEYS */;
/*!40000 ALTER TABLE `personal_access_tokens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pitch_drafts`
--

DROP TABLE IF EXISTS `pitch_drafts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pitch_drafts` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `created_by_user_id` char(36) DEFAULT NULL,
  `prospect_name` varchar(191) NOT NULL,
  `industry` varchar(191) DEFAULT NULL,
  `context` text DEFAULT NULL,
  `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`result`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `pitch_drafts_agency_id_index` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pitch_drafts`
--

LOCK TABLES `pitch_drafts` WRITE;
/*!40000 ALTER TABLE `pitch_drafts` DISABLE KEYS */;
/*!40000 ALTER TABLE `pitch_drafts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `plans`
--

DROP TABLE IF EXISTS `plans`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `plans` (
  `id` char(36) NOT NULL,
  `key` varchar(191) NOT NULL,
  `name` varchar(191) NOT NULL,
  `price_value` bigint(20) unsigned NOT NULL DEFAULT 0,
  `period_days` int(10) unsigned NOT NULL DEFAULT 30,
  `is_popular` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `max_users` int(10) unsigned NOT NULL,
  `max_campaigns` int(10) unsigned NOT NULL,
  `max_mentions` int(10) unsigned NOT NULL,
  `has_ai_access` tinyint(1) NOT NULL DEFAULT 0,
  `has_full_style_gallery` tinyint(1) NOT NULL DEFAULT 0,
  `has_crisis_tools` tinyint(1) NOT NULL DEFAULT 0,
  `has_influencer_module` tinyint(1) NOT NULL DEFAULT 0,
  `can_customize_report_colors` tinyint(1) NOT NULL DEFAULT 0,
  `has_share_of_voice` tinyint(1) NOT NULL DEFAULT 0,
  `features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`features`)),
  `cta_label` varchar(191) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plans_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `plans`
--

LOCK TABLES `plans` WRITE;
/*!40000 ALTER TABLE `plans` DISABLE KEYS */;
INSERT INTO `plans` VALUES ('7ea2bc4f-2f29-440a-8f3d-5010ff708753','TRIAL','Trial',0,14,0,1,1,1,50,1,0,0,0,0,0,'[\"Up to 1 campaign\",\"Up to 50 media mentions tracked\",\"Basic report templates\",\"Email support\",\"1 team member\"]','Get Started Free','2026-08-27 15:07:21','2026-08-27 15:07:21'),('957ef15c-2289-4f6a-8841-b916c2cf995b','AGENCY','Agency',700000,30,0,4,999,9999,999999,1,1,1,1,1,1,'[\"Unlimited campaigns\",\"Unlimited media mentions\",\"NBC-style report template with custom brand colors\",\"White-label reports\",\"Dedicated account manager\",\"24\\/7 priority support\",\"Everything in Professional\",\"API access\",\"Custom integrations\",\"Multi-agency management\"]','Choose Agency','2026-08-27 15:07:21','2026-08-27 15:07:21'),('d9a4d8f3-2d29-48f8-aa8a-dbb91156f3a5','STARTER','Starter',150000,30,0,2,3,5,500,0,0,0,0,0,0,'[\"Up to 5 campaigns\",\"Up to 500 media mentions tracked\",\"Professional NBC-style report template\",\"Email & chat support\",\"Up to 3 team members\",\"Press release distribution\",\"Basic media monitoring\"]','Choose Starter','2026-08-27 15:07:21','2026-08-27 15:07:21'),('f7fa44d4-eebd-4fad-940c-cac1f264db30','PROFESSIONAL','Professional',350000,30,1,3,10,9999,2000,1,1,1,1,0,1,'[\"Unlimited campaigns\",\"Up to 2,000 media mentions tracked\",\"Polished NBC-style report template\",\"Priority email, chat & phone support\",\"Up to 10 team members\",\"Press release distribution\",\"Advanced media monitoring\",\"Crisis management tools\",\"Influencer management\",\"AI-powered insights\"]','Choose Professional','2026-08-27 15:07:21','2026-08-27 15:07:21');
/*!40000 ALTER TABLE `plans` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `platform_admin_refresh_tokens`
--

DROP TABLE IF EXISTS `platform_admin_refresh_tokens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `platform_admin_refresh_tokens` (
  `id` char(36) NOT NULL,
  `platform_admin_id` char(36) NOT NULL,
  `token_hash` varchar(191) NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `revoked_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `platform_admin_refresh_tokens_platform_admin_id_index` (`platform_admin_id`),
  CONSTRAINT `platform_admin_refresh_tokens_platform_admin_id_foreign` FOREIGN KEY (`platform_admin_id`) REFERENCES `platform_admins` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `platform_admin_refresh_tokens`
--

LOCK TABLES `platform_admin_refresh_tokens` WRITE;
/*!40000 ALTER TABLE `platform_admin_refresh_tokens` DISABLE KEYS */;
/*!40000 ALTER TABLE `platform_admin_refresh_tokens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `platform_admins`
--

DROP TABLE IF EXISTS `platform_admins`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `platform_admins` (
  `id` char(36) NOT NULL,
  `email` varchar(191) NOT NULL,
  `password` varchar(191) NOT NULL,
  `full_name` varchar(191) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform_admins_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `platform_admins`
--

LOCK TABLES `platform_admins` WRITE;
/*!40000 ALTER TABLE `platform_admins` DISABLE KEYS */;
/*!40000 ALTER TABLE `platform_admins` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `platform_audit_logs`
--

DROP TABLE IF EXISTS `platform_audit_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `platform_audit_logs` (
  `id` char(36) NOT NULL,
  `admin_user_id` char(36) NOT NULL,
  `action` varchar(191) NOT NULL,
  `target_type` varchar(191) NOT NULL,
  `target_id` varchar(191) DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `platform_audit_logs_platform_admin_id_index` (`admin_user_id`),
  KEY `platform_audit_logs_created_at_index` (`created_at`),
  KEY `platform_audit_logs_target_type_target_id_index` (`target_type`,`target_id`),
  CONSTRAINT `platform_audit_logs_admin_user_id_foreign` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `platform_audit_logs`
--

LOCK TABLES `platform_audit_logs` WRITE;
/*!40000 ALTER TABLE `platform_audit_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `platform_audit_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `platform_settings`
--

DROP TABLE IF EXISTS `platform_settings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `platform_settings` (
  `id` char(36) NOT NULL,
  `key` varchar(191) NOT NULL,
  `value` text DEFAULT NULL,
  `type` varchar(191) NOT NULL DEFAULT 'text',
  `description` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform_settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `platform_settings`
--

LOCK TABLES `platform_settings` WRITE;
/*!40000 ALTER TABLE `platform_settings` DISABLE KEYS */;
/*!40000 ALTER TABLE `platform_settings` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `press_releases`
--

DROP TABLE IF EXISTS `press_releases`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `press_releases` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `client_id` char(36) NOT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `prompt` text NOT NULL,
  `headline` varchar(191) NOT NULL,
  `body_english` longtext NOT NULL,
  `body_swahili` longtext DEFAULT NULL,
  `quotes` longtext DEFAULT NULL,
  `email_subject` varchar(191) DEFAULT NULL,
  `social_captions` longtext DEFAULT NULL,
  `status` varchar(191) NOT NULL DEFAULT 'DRAFT',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `press_releases_agency_id_index` (`agency_id`),
  KEY `press_releases_client_id_index` (`client_id`),
  KEY `press_releases_campaign_id_index` (`campaign_id`),
  KEY `press_releases_status_index` (`status`),
  KEY `press_releases_client_id_created_at_index` (`client_id`,`created_at`),
  CONSTRAINT `press_releases_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `press_releases_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `press_releases`
--

LOCK TABLES `press_releases` WRITE;
/*!40000 ALTER TABLE `press_releases` DISABLE KEYS */;
/*!40000 ALTER TABLE `press_releases` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `refresh_tokens`
--

DROP TABLE IF EXISTS `refresh_tokens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `refresh_tokens` (
  `id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `token_hash` varchar(191) NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `revoked_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `refresh_tokens_user_id_index` (`user_id`),
  CONSTRAINT `refresh_tokens_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `refresh_tokens`
--

LOCK TABLES `refresh_tokens` WRITE;
/*!40000 ALTER TABLE `refresh_tokens` DISABLE KEYS */;
/*!40000 ALTER TABLE `refresh_tokens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `reports`
--

DROP TABLE IF EXISTS `reports`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `reports` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `campaign_id` char(36) NOT NULL,
  `title` varchar(191) DEFAULT NULL,
  `type` varchar(191) NOT NULL DEFAULT 'coverage',
  `template` varchar(191) DEFAULT NULL,
  `render_engine` varchar(16) NOT NULL DEFAULT 'template',
  `design_template` longtext DEFAULT NULL,
  `design_meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`design_meta`)),
  `language` varchar(5) NOT NULL DEFAULT 'en',
  `generated_by_user_id` char(36) NOT NULL,
  `period_start` timestamp NULL DEFAULT NULL,
  `period_end` timestamp NULL DEFAULT NULL,
  `file_url` varchar(191) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `reports_agency_id_index` (`agency_id`),
  KEY `reports_campaign_id_index` (`campaign_id`),
  CONSTRAINT `reports_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `reports`
--

LOCK TABLES `reports` WRITE;
/*!40000 ALTER TABLE `reports` DISABLE KEYS */;
/*!40000 ALTER TABLE `reports` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `site_settings`
--

DROP TABLE IF EXISTS `site_settings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `site_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site_name` varchar(191) NOT NULL DEFAULT 'Roote PR System',
  `logo_url` varchar(191) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `registrations_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `site_settings`
--

LOCK TABLES `site_settings` WRITE;
/*!40000 ALTER TABLE `site_settings` DISABLE KEYS */;
INSERT INTO `site_settings` VALUES (1,'Roote PR System',NULL,NULL,1,'2026-08-27 15:07:27','2026-08-27 15:07:27');
/*!40000 ALTER TABLE `site_settings` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `subscriptions`
--

DROP TABLE IF EXISTS `subscriptions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `subscriptions` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `provider` varchar(191) NOT NULL,
  `provider_subscription_id` varchar(191) NOT NULL,
  `plan` varchar(191) NOT NULL,
  `status` varchar(191) NOT NULL,
  `current_period_start` timestamp NULL DEFAULT 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,
  `renewal_attempts` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `last_renewal_attempt_at` timestamp NULL DEFAULT NULL,
  `renewal_failure_reason` varchar(191) DEFAULT NULL,
  `grace_ends_at` timestamp NULL DEFAULT NULL,
  `renewal_reminder_sent_at` timestamp NULL DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscriptions_provider_subscription_id_unique` (`provider_subscription_id`),
  KEY `subscriptions_agency_id_index` (`agency_id`),
  KEY `subscriptions_status_current_period_end_index` (`status`,`current_period_end`),
  CONSTRAINT `subscriptions_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `subscriptions`
--

LOCK TABLES `subscriptions` WRITE;
/*!40000 ALTER TABLE `subscriptions` DISABLE KEYS */;
/*!40000 ALTER TABLE `subscriptions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `support_conversations`
--

DROP TABLE IF EXISTS `support_conversations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `support_conversations` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `last_message_at` timestamp NULL DEFAULT NULL,
  `last_message_preview` varchar(191) DEFAULT NULL,
  `last_message_by` varchar(191) DEFAULT NULL,
  `message_count` int(10) unsigned NOT NULL DEFAULT 0,
  `agency_read_count` int(10) unsigned NOT NULL DEFAULT 0,
  `admin_read_count` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `support_conversations_agency_id_unique` (`agency_id`),
  CONSTRAINT `support_conversations_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `support_conversations`
--

LOCK TABLES `support_conversations` WRITE;
/*!40000 ALTER TABLE `support_conversations` DISABLE KEYS */;
/*!40000 ALTER TABLE `support_conversations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `support_messages`
--

DROP TABLE IF EXISTS `support_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `support_messages` (
  `id` char(36) NOT NULL,
  `conversation_id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `sender_type` varchar(191) NOT NULL,
  `user_id` char(36) DEFAULT NULL,
  `body` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `support_messages_user_id_foreign` (`user_id`),
  KEY `support_messages_conversation_id_index` (`conversation_id`),
  KEY `support_messages_agency_id_index` (`agency_id`),
  CONSTRAINT `support_messages_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `support_messages_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `support_conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `support_messages_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `support_messages`
--

LOCK TABLES `support_messages` WRITE;
/*!40000 ALTER TABLE `support_messages` DISABLE KEYS */;
/*!40000 ALTER TABLE `support_messages` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `system_backups`
--

DROP TABLE IF EXISTS `system_backups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `system_backups` (
  `id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `file_path` varchar(191) DEFAULT NULL,
  `file_size` varchar(191) DEFAULT NULL,
  `type` varchar(191) NOT NULL DEFAULT 'manual',
  `status` varchar(191) NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `included_tables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`included_tables`)),
  `created_by` char(36) DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `system_backups`
--

LOCK TABLES `system_backups` WRITE;
/*!40000 ALTER TABLE `system_backups` DISABLE KEYS */;
/*!40000 ALTER TABLE `system_backups` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `system_error_logs`
--

DROP TABLE IF EXISTS `system_error_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `system_error_logs` (
  `id` char(36) NOT NULL,
  `category` varchar(191) NOT NULL,
  `severity` varchar(191) NOT NULL DEFAULT 'error',
  `message` text NOT NULL,
  `details` text DEFAULT NULL,
  `source` varchar(191) DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `resolved` tinyint(1) NOT NULL DEFAULT 0,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolved_by` varchar(191) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `system_error_logs_category_index` (`category`),
  KEY `system_error_logs_severity_index` (`severity`),
  KEY `system_error_logs_created_at_index` (`created_at`),
  KEY `system_error_logs_resolved_index` (`resolved`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `system_error_logs`
--

LOCK TABLES `system_error_logs` WRITE;
/*!40000 ALTER TABLE `system_error_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `system_error_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `tasks`
--

DROP TABLE IF EXISTS `tasks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tasks` (
  `id` char(36) NOT NULL,
  `campaign_id` char(36) NOT NULL,
  `agency_id` char(36) NOT NULL,
  `title` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `assigned_to_user_id` char(36) DEFAULT NULL,
  `status` varchar(191) NOT NULL DEFAULT 'TODO',
  `priority` varchar(191) NOT NULL DEFAULT 'MEDIUM',
  `due_date` timestamp NULL DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tasks_campaign_id_index` (`campaign_id`),
  KEY `tasks_agency_id_index` (`agency_id`),
  KEY `tasks_assigned_to_user_id_index` (`assigned_to_user_id`),
  KEY `tasks_status_index` (`status`),
  CONSTRAINT `tasks_assigned_to_user_id_foreign` FOREIGN KEY (`assigned_to_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tasks_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tasks`
--

LOCK TABLES `tasks` WRITE;
/*!40000 ALTER TABLE `tasks` DISABLE KEYS */;
/*!40000 ALTER TABLE `tasks` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) DEFAULT NULL,
  `is_super_admin` tinyint(1) NOT NULL DEFAULT 0,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `last_seen_at` timestamp NULL DEFAULT NULL,
  `email_notifications` tinyint(1) NOT NULL DEFAULT 1,
  `marketing_opt_in` tinyint(1) NOT NULL DEFAULT 0,
  `marketing_opt_in_at` timestamp NULL DEFAULT NULL,
  `unsubscribe_token` varchar(64) DEFAULT NULL,
  `email` varchar(191) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(191) NOT NULL,
  `full_name` varchar(191) NOT NULL,
  `username` varchar(191) DEFAULT NULL,
  `phone` varchar(191) DEFAULT NULL,
  `avatar_url` varchar(191) DEFAULT NULL,
  `ui_accent` varchar(24) NOT NULL DEFAULT 'default',
  `ui_density` varchar(24) NOT NULL DEFAULT 'comfortable',
  `locale` varchar(5) DEFAULT NULL,
  `tour_completed_at` timestamp NULL DEFAULT NULL,
  `role` varchar(191) NOT NULL DEFAULT 'VIEWER',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `fcm_token` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_unsubscribe_token_unique` (`unsubscribe_token`),
  KEY `users_agency_id_foreign` (`agency_id`),
  CONSTRAINT `users_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES ('01a04467-c8c6-71e5-93d6-21dad8bf66df','01a04467-c866-71ed-9ed5-8e0aa004d7cf',1,NULL,NULL,1,0,NULL,NULL,'platform-admin@roote.co.tz',NULL,'$2y$10$Vz.DrvQ02nikpiMCdjrBtOrNRRoNIASubpLvt.HLQ95ZgODFVDxTe','Gabito',NULL,NULL,NULL,'default','comfortable',NULL,NULL,'OWNER',1,NULL,'2026-08-27 15:07:27','2026-08-27 15:07:27',NULL),('01a04467-c91c-702a-8a69-f8ac807c04f1',NULL,0,NULL,NULL,0,0,NULL,NULL,'linda@roote.co.tz',NULL,'$2y$10$Zg2Y0T6YegIAdZ5TaHXy6.4LtK6K6jEOdTseUcOgVj90GSKpI19xq','Linda Mwakasege',NULL,NULL,NULL,'default','comfortable',NULL,NULL,'CONTRIBUTOR',1,NULL,'2026-08-27 15:07:27','2026-08-27 15:07:27',NULL);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `whatsapp_intakes`
--

DROP TABLE IF EXISTS `whatsapp_intakes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `whatsapp_intakes` (
  `id` char(36) NOT NULL,
  `agency_id` char(36) DEFAULT NULL,
  `matched_user_id` char(36) DEFAULT NULL,
  `from_phone` varchar(191) NOT NULL,
  `message_type` varchar(20) NOT NULL,
  `media_url` varchar(191) DEFAULT NULL,
  `text_body` text DEFAULT NULL,
  `ai_tagged_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_tagged_data`)),
  `status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `coverage_item_id` char(36) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `whatsapp_intakes_agency_id_index` (`agency_id`),
  KEY `whatsapp_intakes_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `whatsapp_intakes`
--

LOCK TABLES `whatsapp_intakes` WRITE;
/*!40000 ALTER TABLE `whatsapp_intakes` DISABLE KEYS */;
/*!40000 ALTER TABLE `whatsapp_intakes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `work_submissions`
--

DROP TABLE IF EXISTS `work_submissions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `work_submissions` (
  `id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `type` varchar(32) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)),
  `title` varchar(200) NOT NULL,
  `dedupe_key` varchar(200) DEFAULT NULL,
  `source_url` varchar(500) DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'PENDING',
  `reward_amount` int(10) unsigned NOT NULL,
  `reviewed_by` char(36) DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `reject_reason` varchar(300) DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `work_submissions_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `work_submissions_user_id_status_index` (`user_id`,`status`),
  KEY `work_submissions_type_status_index` (`type`,`status`),
  KEY `work_submissions_dedupe_key_index` (`dedupe_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `work_submissions`
--

LOCK TABLES `work_submissions` WRITE;
/*!40000 ALTER TABLE `work_submissions` DISABLE KEYS */;
/*!40000 ALTER TABLE `work_submissions` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-08-27 21:07:41