-- Boss Of AI — MySQL 8 schema
--
-- Migrations remain the source of truth. Regenerate this file after any
-- migration change with:
--
--     php artisan schema:dump
--
-- Load into a fresh database with:
--
--     mysql -u root -p bossofai < database/schema/bossofai.sql
--
-- The migrations table at the bottom is pre-populated, so `php artisan migrate`
-- against a database loaded from this file is a no-op rather than an attempt to
-- recreate every table.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET @OLD_SQL_MODE = @@SQL_MODE, SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- ---------------------------------------------------------------------------
-- Framework
-- ---------------------------------------------------------------------------

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(20) NOT NULL DEFAULT 'user',
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `industry` varchar(255) DEFAULT NULL,
  `objective` varchar(60) DEFAULT NULL,
  `target_location` varchar(255) DEFAULT NULL,
  `typical_customer` varchar(255) DEFAULT NULL,
  `target_job_titles` json DEFAULT NULL,
  `onboarded_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_role_status_index` (`role`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Public site: tools directory, newsletter, contact
-- ---------------------------------------------------------------------------

CREATE TABLE `tool_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `tagline` varchar(255) DEFAULT NULL,
  `description` text,
  `position` smallint unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tool_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tools` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tool_category_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `summary` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `website` varchar(255) NOT NULL,
  `pricing_model` varchar(255) NOT NULL DEFAULT 'freemium',
  `pricing_note` varchar(255) DEFAULT NULL,
  `best_for` varchar(255) DEFAULT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `is_published` tinyint(1) NOT NULL DEFAULT '1',
  `click_count` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tools_slug_unique` (`slug`),
  KEY `tools_is_published_is_featured_index` (`is_published`,`is_featured`),
  KEY `tools_tool_category_id_foreign` (`tool_category_id`),
  CONSTRAINT `tools_tool_category_id_foreign` FOREIGN KEY (`tool_category_id`) REFERENCES `tool_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `articles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `excerpt` varchar(500) NOT NULL,
  `external_url` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL DEFAULT 'AI Tools',
  `read_time` varchar(255) DEFAULT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `published_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `articles_slug_unique` (`slug`),
  KEY `articles_published_at_is_featured_index` (`published_at`,`is_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `newsletter_subscribers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `source` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `unsubscribed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `newsletter_subscribers_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `contact_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `company` varchar(255) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `handled_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;

-- ---------------------------------------------------------------------------
-- Identity and plans
-- ---------------------------------------------------------------------------

CREATE TABLE `social_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `provider` varchar(40) NOT NULL,
  `provider_user_id` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `avatar_url` varchar(512) DEFAULT NULL,
  `access_token` text,
  `refresh_token` text,
  `token_expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `social_accounts_provider_provider_user_id_unique` (`provider`,`provider_user_id`),
  UNIQUE KEY `social_accounts_user_id_provider_unique` (`user_id`,`provider`),
  CONSTRAINT `social_accounts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `plans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(40) NOT NULL,
  `name` varchar(255) NOT NULL,
  `summary` varchar(255) DEFAULT NULL,
  `price_cents` int unsigned NOT NULL DEFAULT '0',
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `interval` varchar(20) NOT NULL DEFAULT 'month',
  `monthly_credits` int unsigned NOT NULL DEFAULT '0',
  `features` json DEFAULT NULL,
  `lemon_squeezy_variant_id` varchar(255) DEFAULT NULL,
  `paystack_plan_code` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `position` smallint unsigned NOT NULL DEFAULT '0',
  `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;

-- ---------------------------------------------------------------------------
-- Credit ledger
--
-- balance is a cache of the ledger and is only ever written inside a
-- transaction with this row locked. reserved holds credits for in-flight
-- operations so a parallel request cannot spend them twice.
-- ---------------------------------------------------------------------------

CREATE TABLE `credit_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `balance` int unsigned NOT NULL DEFAULT '0',
  `reserved` int unsigned NOT NULL DEFAULT '0',
  `lifetime_granted` int unsigned NOT NULL DEFAULT '0',
  `lifetime_spent` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credit_accounts_user_id_unique` (`user_id`),
  CONSTRAINT `credit_accounts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `credit_transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `credit_account_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `amount` int NOT NULL,
  `type` varchar(20) NOT NULL,
  `status` varchar(20) NOT NULL,
  `action` varchar(60) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `reference_type` varchar(255) DEFAULT NULL,
  `reference_id` bigint unsigned DEFAULT NULL,
  `idempotency_key` varchar(120) NOT NULL,
  `balance_after` int unsigned DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `settled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credit_transactions_idempotency_key_unique` (`idempotency_key`),
  KEY `credit_transactions_reference_type_reference_id_index` (`reference_type`,`reference_id`),
  KEY `credit_transactions_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `credit_transactions_user_id_status_index` (`user_id`,`status`),
  KEY `credit_transactions_credit_account_id_foreign` (`credit_account_id`),
  CONSTRAINT `credit_transactions_credit_account_id_foreign` FOREIGN KEY (`credit_account_id`) REFERENCES `credit_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `credit_transactions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Lead data
--
-- companies and contacts are shared across users and deduplicated on
-- (provider, external_id). Ownership lives on leads, which is unique per
-- (user_id, contact_id).
-- ---------------------------------------------------------------------------

CREATE TABLE `companies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `provider` varchar(40) NOT NULL,
  `external_id` varchar(120) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `domain` varchar(191) DEFAULT NULL,
  `website` varchar(512) DEFAULT NULL,
  `industry` varchar(120) DEFAULT NULL,
  `employee_count` int unsigned DEFAULT NULL,
  `employee_range` varchar(40) DEFAULT NULL,
  `city` varchar(120) DEFAULT NULL,
  `region` varchar(120) DEFAULT NULL,
  `country` varchar(120) DEFAULT NULL,
  `description` text,
  `linkedin_url` varchar(512) DEFAULT NULL,
  `technologies` json DEFAULT NULL,
  `raw` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `companies_provider_external_id_unique` (`provider`,`external_id`),
  KEY `companies_domain_index` (`domain`),
  KEY `companies_industry_country_index` (`industry`,`country`),
  KEY `companies_employee_count_index` (`employee_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `contacts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `provider` varchar(40) NOT NULL,
  `external_id` varchar(120) DEFAULT NULL,
  `first_name` varchar(120) DEFAULT NULL,
  `last_name` varchar(120) DEFAULT NULL,
  `full_name` varchar(255) NOT NULL,
  `title` varchar(191) DEFAULT NULL,
  `seniority` varchar(60) DEFAULT NULL,
  `department` varchar(60) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `email_status` varchar(30) NOT NULL DEFAULT 'unknown',
  `email_confidence` tinyint unsigned DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `revealed_at` timestamp NULL DEFAULT NULL,
  `linkedin_url` varchar(512) DEFAULT NULL,
  `location` varchar(191) DEFAULT NULL,
  `raw` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contacts_provider_external_id_unique` (`provider`,`external_id`),
  KEY `contacts_email_index` (`email`),
  KEY `contacts_company_id_seniority_index` (`company_id`,`seniority`),
  CONSTRAINT `contacts_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lead_searches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `provider` varchar(40) NOT NULL,
  `filters` json NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `results_count` int unsigned NOT NULL DEFAULT '0',
  `credits_spent` int unsigned NOT NULL DEFAULT '0',
  `failure_reason` varchar(500) DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lead_searches_user_id_created_at_index` (`user_id`,`created_at`),
  CONSTRAINT `lead_searches_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `leads` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `company_id` bigint unsigned NOT NULL,
  `contact_id` bigint unsigned NOT NULL,
  `lead_search_id` bigint unsigned DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'new',
  `is_saved` tinyint(1) NOT NULL DEFAULT '0',
  `score` tinyint unsigned DEFAULT NULL,
  `grade` varchar(2) DEFAULT NULL,
  `score_reasons` json DEFAULT NULL,
  `recommended_action` varchar(500) DEFAULT NULL,
  `scored_at` timestamp NULL DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `leads_user_id_contact_id_unique` (`user_id`,`contact_id`),
  KEY `leads_user_id_is_saved_index` (`user_id`,`is_saved`),
  KEY `leads_user_id_score_index` (`user_id`,`score`),
  KEY `leads_company_id_foreign` (`company_id`),
  KEY `leads_contact_id_foreign` (`contact_id`),
  KEY `leads_lead_search_id_foreign` (`lead_search_id`),
  CONSTRAINT `leads_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leads_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leads_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leads_lead_search_id_foreign` FOREIGN KEY (`lead_search_id`) REFERENCES `lead_searches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `lead_notes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `lead_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `body` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lead_notes_lead_id_created_at_index` (`lead_id`,`created_at`),
  KEY `lead_notes_user_id_foreign` (`user_id`),
  CONSTRAINT `lead_notes_lead_id_foreign` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `lead_notes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Structured, validated AI output. Model prose is never rendered raw.
CREATE TABLE `company_researches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `company_id` bigint unsigned NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `model` varchar(60) DEFAULT NULL,
  `summary` text,
  `business_model` varchar(500) DEFAULT NULL,
  `target_market` varchar(500) DEFAULT NULL,
  `pain_points` json DEFAULT NULL,
  `growth_signals` json DEFAULT NULL,
  `buying_signals` json DEFAULT NULL,
  `ai_opportunities` json DEFAULT NULL,
  `recommended_offer` text,
  `recommended_approach` text,
  `lead_quality` varchar(20) DEFAULT NULL,
  `reasoning` text,
  `sources` json DEFAULT NULL,
  `credits_spent` int unsigned NOT NULL DEFAULT '0',
  `failure_reason` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_researches_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `company_researches_company_id_created_at_index` (`company_id`,`created_at`),
  CONSTRAINT `company_researches_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `company_researches_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Campaigns and outreach
-- ---------------------------------------------------------------------------

CREATE TABLE `campaigns` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'draft',
  `offer` varchar(500) DEFAULT NULL,
  `tone` varchar(40) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `campaigns_user_id_status_index` (`user_id`,`status`),
  CONSTRAINT `campaigns_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `outreach_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `lead_id` bigint unsigned NOT NULL,
  `campaign_id` bigint unsigned DEFAULT NULL,
  `company_research_id` bigint unsigned DEFAULT NULL,
  `channel` varchar(20) NOT NULL DEFAULT 'email',
  `tone` varchar(40) DEFAULT NULL,
  `objective` varchar(60) DEFAULT NULL,
  `offer` varchar(500) DEFAULT NULL,
  `subject` varchar(300) DEFAULT NULL,
  `opening` text,
  `body` text,
  `cta` varchar(500) DEFAULT NULL,
  `model` varchar(60) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'draft',
  `edited_by_user` tinyint(1) NOT NULL DEFAULT '0',
  `credits_spent` int unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `outreach_messages_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `outreach_messages_campaign_id_created_at_index` (`campaign_id`,`created_at`),
  KEY `outreach_messages_lead_id_foreign` (`lead_id`),
  KEY `outreach_messages_company_research_id_foreign` (`company_research_id`),
  CONSTRAINT `outreach_messages_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `outreach_messages_lead_id_foreign` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `outreach_messages_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `outreach_messages_company_research_id_foreign` FOREIGN KEY (`company_research_id`) REFERENCES `company_researches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `campaign_leads` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `campaign_id` bigint unsigned NOT NULL,
  `lead_id` bigint unsigned NOT NULL,
  `outreach_message_id` bigint unsigned DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `campaign_leads_campaign_id_lead_id_unique` (`campaign_id`,`lead_id`),
  KEY `campaign_leads_lead_id_foreign` (`lead_id`),
  KEY `campaign_leads_outreach_message_id_foreign` (`outreach_message_id`),
  CONSTRAINT `campaign_leads_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_leads_lead_id_foreign` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `campaign_leads_outreach_message_id_foreign` FOREIGN KEY (`outreach_message_id`) REFERENCES `outreach_messages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Billing. No Stripe anywhere: Lemon Squeezy, Paystack and crypto.
-- ---------------------------------------------------------------------------

CREATE TABLE `payment_customers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `provider` varchar(40) NOT NULL,
  `external_id` varchar(191) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_customers_provider_external_id_unique` (`provider`,`external_id`),
  UNIQUE KEY `payment_customers_user_id_provider_unique` (`user_id`,`provider`),
  CONSTRAINT `payment_customers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscriptions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `plan_id` bigint unsigned NOT NULL,
  `provider` varchar(40) NOT NULL,
  `external_id` varchar(191) NOT NULL,
  `status` varchar(30) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `amount_cents` int unsigned NOT NULL DEFAULT '0',
  `current_period_start` timestamp NULL DEFAULT NULL,
  `current_period_end` timestamp NULL DEFAULT NULL,
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  `cancel_at` timestamp NULL DEFAULT NULL,
  `ends_at` timestamp NULL DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscriptions_provider_external_id_unique` (`provider`,`external_id`),
  KEY `subscriptions_user_id_status_index` (`user_id`,`status`),
  KEY `subscriptions_plan_id_foreign` (`plan_id`),
  CONSTRAINT `subscriptions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `subscriptions_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `subscription_id` bigint unsigned DEFAULT NULL,
  `provider` varchar(40) NOT NULL,
  `external_id` varchar(191) NOT NULL,
  `amount_cents` int unsigned NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `status` varchar(30) NOT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `refunded_at` timestamp NULL DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_provider_external_id_unique` (`provider`,`external_id`),
  KEY `payments_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `payments_subscription_id_foreign` (`subscription_id`),
  CONSTRAINT `payments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The webhook idempotency anchor. The unique key is what makes a redelivered
-- notification a no-op instead of a second credit grant.
CREATE TABLE `billing_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `provider` varchar(40) NOT NULL,
  `external_event_id` varchar(191) NOT NULL,
  `type` varchar(120) NOT NULL,
  `payload` json NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'received',
  `failure_reason` varchar(500) DEFAULT NULL,
  `attempts` tinyint unsigned NOT NULL DEFAULT '0',
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `billing_events_provider_external_event_id_unique` (`provider`,`external_event_id`),
  KEY `billing_events_status_created_at_index` (`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Crypto: BTC, ETH and USDT. Priced in fiat; the coin amount is quoted by the
-- processor. credited_at is the guard that stops a double grant.
CREATE TABLE `crypto_charges` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `plan_id` bigint unsigned DEFAULT NULL,
  `provider` varchar(40) NOT NULL DEFAULT 'nowpayments',
  `external_id` varchar(191) NOT NULL,
  `payment_id` varchar(191) DEFAULT NULL,
  `purchasable_type` varchar(20) NOT NULL,
  `purchasable_key` varchar(60) NOT NULL,
  `credits` int unsigned NOT NULL,
  `price_cents` int unsigned NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `asset` varchar(20) DEFAULT NULL,
  `network` varchar(30) DEFAULT NULL,
  `pay_currency` varchar(30) DEFAULT NULL,
  `pay_amount` decimal(28,10) DEFAULT NULL,
  `actually_paid` decimal(28,10) DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'created',
  `checkout_url` varchar(512) DEFAULT NULL,
  `transaction_hash` varchar(191) DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `credited_at` timestamp NULL DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `crypto_charges_provider_external_id_unique` (`provider`,`external_id`),
  KEY `crypto_charges_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `crypto_charges_status_index` (`status`),
  KEY `crypto_charges_plan_id_foreign` (`plan_id`),
  CONSTRAINT `crypto_charges_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `crypto_charges_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Operations
-- ---------------------------------------------------------------------------

-- Provider telemetry for the admin health panel. Deliberately holds no request
-- bodies, no credentials and no contact data.
CREATE TABLE `provider_calls` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `provider` varchar(40) NOT NULL,
  `operation` varchar(60) NOT NULL,
  `status` varchar(20) NOT NULL,
  `http_status` smallint unsigned DEFAULT NULL,
  `duration_ms` int unsigned DEFAULT NULL,
  `result_count` int unsigned DEFAULT NULL,
  `error_code` varchar(60) DEFAULT NULL,
  `error_message` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `provider_calls_provider_created_at_index` (`provider`,`created_at`),
  KEY `provider_calls_provider_status_index` (`provider`,`status`),
  KEY `provider_calls_user_id_foreign` (`user_id`),
  CONSTRAINT `provider_calls_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `action` varchar(120) NOT NULL,
  `subject_type` varchar(255) DEFAULT NULL,
  `subject_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_subject_type_subject_id_index` (`subject_type`,`subject_id`),
  KEY `audit_logs_action_created_at_index` (`action`,`created_at`),
  KEY `audit_logs_user_id_created_at_index` (`user_id`,`created_at`),
  CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Migration ledger
-- ---------------------------------------------------------------------------

CREATE TABLE `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2026_09_09_000001_create_tool_categories_table', 1),
('2026_09_09_000002_create_tools_table', 1),
('2026_09_09_000003_create_articles_table', 1),
('2026_09_09_000004_create_newsletter_subscribers_table', 1),
('2026_09_09_000005_create_contact_messages_table', 1),
('2026_09_09_000010_add_saas_fields_to_users_table', 1),
('2026_09_09_000011_create_social_accounts_table', 1),
('2026_09_09_000012_create_plans_table', 1),
('2026_09_09_000013_create_credit_accounts_table', 1),
('2026_09_09_000014_create_credit_transactions_table', 1),
('2026_09_09_000015_create_companies_table', 1),
('2026_09_09_000016_create_contacts_table', 1),
('2026_09_09_000017_create_lead_searches_table', 1),
('2026_09_09_000018_create_leads_table', 1),
('2026_09_09_000019_create_lead_notes_table', 1),
('2026_09_09_000020_create_company_researches_table', 1),
('2026_09_09_000021_create_campaigns_table', 1),
('2026_09_09_000022_create_outreach_messages_table', 1),
('2026_09_09_000023_create_campaign_leads_table', 1),
('2026_09_09_000024_create_payment_customers_table', 1),
('2026_09_09_000025_create_subscriptions_table', 1),
('2026_09_09_000026_create_payments_table', 1),
('2026_09_09_000027_create_billing_events_table', 1),
('2026_09_09_000028_create_provider_calls_table', 1),
('2026_09_09_000029_create_audit_logs_table', 1),
('2026_09_09_000030_create_crypto_charges_table', 1);

SET SQL_MODE = @OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS = 1;