-- BizQuote Pro database backup
-- Database: straitad_getbizquote
-- Created: 2026-07-30T04:43:27+02:00

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `api_keys`;
CREATE TABLE `api_keys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `token_prefix` varchar(20) NOT NULL,
  `token_hash` char(64) NOT NULL,
  `scopes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`scopes`)),
  `last_used_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token_hash` (`token_hash`),
  KEY `fk_api_keys_user` (`user_id`),
  KEY `idx_api_keys_company` (`company_id`,`revoked_at`),
  CONSTRAINT `fk_api_keys_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_api_keys_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `api_keys` (`id`, `company_id`, `user_id`, `name`, `token_prefix`, `token_hash`, `scopes`, `last_used_at`, `expires_at`, `revoked_at`, `created_at`) VALUES ('1', '4', '4', 'Sample API Test Key', 'bqp_4b6ec6f3', '10c7c9c3da1549a6503802d624f5be598457c8510f37d78cfb19e00b5f5b57bd', '[\"clients:read\",\"clients:write\",\"products:read\",\"invoices:read\"]', '2026-07-11 09:19:47', NULL, '2026-07-11 09:20:03', '2026-07-11 01:19:47');

DROP TABLE IF EXISTS `appointments`;
CREATE TABLE `appointments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `project_id` bigint(20) unsigned DEFAULT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `assigned_user_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `starts_at` datetime NOT NULL,
  `ends_at` datetime DEFAULT NULL,
  `status` enum('scheduled','confirmed','in_progress','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  `client_visible` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_appointments_project` (`project_id`),
  KEY `fk_appointments_client` (`client_id`),
  KEY `fk_appointments_user` (`assigned_user_id`),
  KEY `idx_appointments_company_start` (`company_id`,`starts_at`,`status`),
  CONSTRAINT `fk_appointments_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_appointments_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_appointments_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_appointments_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `appointments` (`id`, `company_id`, `project_id`, `client_id`, `assigned_user_id`, `title`, `location`, `notes`, `starts_at`, `ends_at`, `status`, `client_visible`, `created_at`, `updated_at`) VALUES ('1', '4', '1', '10', '23', 'check school work', 'Mau lodge', NULL, '2026-07-16 09:35:00', '2026-07-16 11:35:00', 'scheduled', '1', '2026-07-16 01:35:33', NULL);

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(120) NOT NULL,
  `entity_type` varchar(80) DEFAULT NULL,
  `entity_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_audit_company` (`company_id`,`created_at`),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `audit_logs` (`id`, `company_id`, `user_id`, `action`, `entity_type`, `entity_id`, `ip_address`, `user_agent`, `payload`, `created_at`) VALUES ('1', '4', '4', 'team_user_created', 'user', '23', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '{\"email\":\"momo@gmail.com\",\"role\":\"sales\"}', '2026-07-11 14:46:56');
INSERT INTO `audit_logs` (`id`, `company_id`, `user_id`, `action`, `entity_type`, `entity_id`, `ip_address`, `user_agent`, `payload`, `created_at`) VALUES ('2', '4', '4', 'subscription_plan_changed', 'company_subscription', '4', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '{\"from\":{\"plan_id\":2,\"plan_code\":\"professional\",\"billing_cycle\":\"monthly\"},\"to\":{\"plan_id\":1,\"plan_code\":\"starter\",\"billing_cycle\":\"monthly\"}}', '2026-07-14 04:56:04');
INSERT INTO `audit_logs` (`id`, `company_id`, `user_id`, `action`, `entity_type`, `entity_id`, `ip_address`, `user_agent`, `payload`, `created_at`) VALUES ('3', '4', '4', 'subscription_plan_config_updated', 'subscription_plan', '1', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '{\"from\":{\"name\":\"Starter\",\"code\":\"starter\",\"monthly_price\":\"19.00\",\"annual_price\":\"190.00\",\"status\":\"active\"},\"to\":{\"name\":\"Starter\",\"code\":\"starter\",\"monthly_price\":\"19.00\",\"annual_price\":\"190.00\",\"status\":\"active\"}}', '2026-07-15 01:37:02');
INSERT INTO `audit_logs` (`id`, `company_id`, `user_id`, `action`, `entity_type`, `entity_id`, `ip_address`, `user_agent`, `payload`, `created_at`) VALUES ('4', '4', '4', 'subscription_plan_changed', 'company_subscription', '4', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '{\"from\":{\"plan_id\":1,\"plan_code\":\"starter\",\"billing_cycle\":\"monthly\"},\"to\":{\"plan_id\":2,\"plan_code\":\"professional\",\"billing_cycle\":\"monthly\"}}', '2026-07-15 01:39:25');

DROP TABLE IF EXISTS `bank_transactions`;
CREATE TABLE `bank_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `transaction_date` date NOT NULL,
  `description` varchar(255) NOT NULL,
  `reference` varchar(190) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `transaction_type` enum('credit','debit') NOT NULL,
  `matched_payment_id` bigint(20) unsigned DEFAULT NULL,
  `matched_expense_id` bigint(20) unsigned DEFAULT NULL,
  `import_batch` varchar(80) DEFAULT NULL,
  `status` enum('unmatched','matched','ignored') NOT NULL DEFAULT 'unmatched',
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_bank_transactions_payment` (`matched_payment_id`),
  KEY `fk_bank_transactions_expense` (`matched_expense_id`),
  KEY `idx_bank_transactions_company` (`company_id`,`status`,`transaction_date`),
  CONSTRAINT `fk_bank_transactions_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bank_transactions_expense` FOREIGN KEY (`matched_expense_id`) REFERENCES `expenses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_bank_transactions_payment` FOREIGN KEY (`matched_payment_id`) REFERENCES `payments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(160) NOT NULL,
  `code` varchar(30) NOT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone` varchar(80) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `timezone` varchar(80) NOT NULL DEFAULT 'Africa/Gaborone',
  `currency` varchar(10) NOT NULL DEFAULT 'BWP',
  `locale` varchar(20) NOT NULL DEFAULT 'en_BW',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_branches_company_code` (`company_id`,`code`),
  CONSTRAINT `fk_branches_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `branches` (`id`, `company_id`, `name`, `code`, `email`, `phone`, `address`, `timezone`, `currency`, `locale`, `status`, `created_at`, `updated_at`) VALUES ('1', '1', 'Demo Company', 'HQ', 'owner@example.com', NULL, NULL, 'Africa/Gaborone', 'BWP', 'en_BW', 'active', '2026-07-10 11:45:53', NULL);
INSERT INTO `branches` (`id`, `company_id`, `name`, `code`, `email`, `phone`, `address`, `timezone`, `currency`, `locale`, `status`, `created_at`, `updated_at`) VALUES ('2', '4', 'BizQuote Demo Company', 'HQ', 'owner@bizquote.test', '+267 390 0000', 'Plot 123, Gaborone CBD\nBotswana', 'Africa/Gaborone', 'BWP', 'en_BW', 'active', '2026-07-10 11:45:53', NULL);

DROP TABLE IF EXISTS `client_documents`;
CREATE TABLE `client_documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `portal_account_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `file_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `visibility` enum('client','internal') NOT NULL DEFAULT 'client',
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_client_documents_company` (`company_id`),
  KEY `fk_client_documents_account` (`portal_account_id`),
  KEY `idx_client_documents_client` (`client_id`,`created_at`),
  CONSTRAINT `fk_client_documents_account` FOREIGN KEY (`portal_account_id`) REFERENCES `client_portal_accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_client_documents_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_client_documents_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `client_portal_accounts`;
CREATE TABLE `client_portal_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `email` varchar(190) NOT NULL,
  `status` enum('active','disabled') NOT NULL DEFAULT 'active',
  `magic_token_hash` varchar(64) DEFAULT NULL,
  `magic_token_expires_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_client_portal_account` (`company_id`,`client_id`,`email`),
  KEY `fk_client_portal_accounts_client` (`client_id`),
  KEY `idx_client_portal_magic` (`magic_token_hash`,`magic_token_expires_at`),
  CONSTRAINT `fk_client_portal_accounts_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_client_portal_accounts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `client_requests`;
CREATE TABLE `client_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `portal_account_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_user_id` bigint(20) unsigned DEFAULT NULL,
  `subject` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `preferred_date` date DEFAULT NULL,
  `status` enum('new','reviewing','scheduled','completed','cancelled') NOT NULL DEFAULT 'new',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_client_requests_account` (`portal_account_id`),
  KEY `fk_client_requests_user` (`assigned_user_id`),
  KEY `idx_client_requests_company_status` (`company_id`,`status`,`created_at`),
  KEY `idx_client_requests_client` (`client_id`,`created_at`),
  CONSTRAINT `fk_client_requests_account` FOREIGN KEY (`portal_account_id`) REFERENCES `client_portal_accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_client_requests_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_client_requests_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_client_requests_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(160) NOT NULL,
  `company_name` varchar(160) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone` varchar(80) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `tax_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clients_company_name` (`company_id`,`name`),
  KEY `idx_clients_email` (`email`),
  CONSTRAINT `fk_clients_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `clients` (`id`, `company_id`, `name`, `company_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('7', '4', 'Delta Engineering', 'Delta Engineering Pty Ltd', 'accounts@delta.test', '+267 390 1001', 'Airport Road\nGaborone', NULL, 'Preferred construction client', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `clients` (`id`, `company_id`, `name`, `company_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('8', '4', 'Naledi Foods', 'Naledi Foods Group', 'finance@naledi.test', '+267 390 1002', 'Industrial Site\nFrancistown', NULL, 'Monthly packaging buyer', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `clients` (`id`, `company_id`, `name`, `company_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('9', '4', 'Kalahari Solar', 'Kalahari Solar Installers', 'admin@kalaharisolar.test', '+267 390 1003', 'Commerce Park\nGaborone', NULL, 'Often requests deposits', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `clients` (`id`, `company_id`, `name`, `company_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('10', '4', 'Comp', 'CompClicks', 'gadgetsfund@gmail.com', '72877872', 'Bag 76 Maun', '8787908', NULL, 'active', '2026-06-29 10:05:07', '2026-07-11 14:50:35');
INSERT INTO `clients` (`id`, `company_id`, `name`, `company_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('28', '4', 'checking', 'momo', 'momo@gmail.com', NULL, NULL, NULL, 'a new lead', 'active', '2026-07-16 01:11:17', NULL);

DROP TABLE IF EXISTS `commerce_order_items`;
CREATE TABLE `commerce_order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sales_order_id` bigint(20) unsigned DEFAULT NULL,
  `purchase_order_id` bigint(20) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` decimal(12,3) NOT NULL DEFAULT 1.000,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_commerce_items_product` (`product_id`),
  KEY `idx_commerce_items_sales_order` (`sales_order_id`),
  KEY `idx_commerce_items_purchase_order` (`purchase_order_id`),
  CONSTRAINT `fk_commerce_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_commerce_items_purchase_order` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_commerce_items_sales_order` FOREIGN KEY (`sales_order_id`) REFERENCES `sales_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `companies`;
CREATE TABLE `companies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(160) NOT NULL,
  `email` varchar(160) DEFAULT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `letterhead_pdf_path` varchar(255) DEFAULT NULL,
  `stamp_image_path` varchar(255) DEFAULT NULL,
  `tax_number` varchar(100) DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'BWP',
  `quotation_prefix` varchar(20) NOT NULL DEFAULT 'QT',
  `invoice_prefix` varchar(20) NOT NULL DEFAULT 'INV',
  `status` enum('trial','active','suspended','cancelled') NOT NULL DEFAULT 'trial',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_companies_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `companies` (`id`, `name`, `email`, `phone`, `address`, `logo`, `letterhead_pdf_path`, `stamp_image_path`, `tax_number`, `currency`, `quotation_prefix`, `invoice_prefix`, `status`, `created_at`, `updated_at`) VALUES ('1', 'Demo Company', 'owner@example.com', NULL, NULL, NULL, 'C:\\xampp\\htdocs\\bizquote-pro/storage/letterheads/company-1-letterhead.pdf', NULL, NULL, 'BWP', 'QT', 'INV', 'active', '2026-06-29 04:02:44', '2026-06-29 06:11:17');
INSERT INTO `companies` (`id`, `name`, `email`, `phone`, `address`, `logo`, `letterhead_pdf_path`, `stamp_image_path`, `tax_number`, `currency`, `quotation_prefix`, `invoice_prefix`, `status`, `created_at`, `updated_at`) VALUES ('4', 'BizQuote Demo Company', 'owner@bizquote.test', '+267 390 0000', 'Plot 123, Gaborone CBD\nBotswana', NULL, 'C:\\xampp\\htdocs\\bizquote-pro/storage/letterheads/company-4-letterhead.pdf', 'C:\\xampp\\htdocs\\bizquote-pro/storage/stamps/company-4-stamp.png', 'BW-TAX-998877', 'BWP', 'QT', 'INV', 'active', '2026-06-29 07:56:30', '2026-06-29 09:51:39');

DROP TABLE IF EXISTS `company_subscriptions`;
CREATE TABLE `company_subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `billing_cycle` enum('monthly','annual') NOT NULL DEFAULT 'monthly',
  `status` enum('trial','active','past_due','cancelled','expired') NOT NULL DEFAULT 'trial',
  `starts_at` date NOT NULL,
  `ends_at` date DEFAULT NULL,
  `next_billing_at` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sub_company` (`company_id`),
  KEY `fk_sub_plan` (`plan_id`),
  CONSTRAINT `fk_sub_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sub_plan` FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `company_subscriptions` (`id`, `company_id`, `plan_id`, `billing_cycle`, `status`, `starts_at`, `ends_at`, `next_billing_at`, `created_at`, `updated_at`) VALUES ('1', '1', '2', 'monthly', 'trial', '2026-06-29', NULL, '2026-07-13', '2026-06-29 04:02:44', NULL);
INSERT INTO `company_subscriptions` (`id`, `company_id`, `plan_id`, `billing_cycle`, `status`, `starts_at`, `ends_at`, `next_billing_at`, `created_at`, `updated_at`) VALUES ('4', '4', '2', 'monthly', 'active', '2026-06-29', NULL, '2026-07-29', '2026-06-29 07:56:30', '2026-07-15 01:39:25');

DROP TABLE IF EXISTS `deal_activities`;
CREATE TABLE `deal_activities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `deal_id` bigint(20) unsigned DEFAULT NULL,
  `client_id` bigint(20) unsigned DEFAULT NULL,
  `quotation_id` bigint(20) unsigned DEFAULT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `activity_type` varchar(80) NOT NULL,
  `subject` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `occurred_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_deal_activities_user` (`user_id`),
  KEY `fk_deal_activities_lead` (`lead_id`),
  KEY `fk_deal_activities_client` (`client_id`),
  KEY `fk_deal_activities_quote` (`quotation_id`),
  KEY `fk_deal_activities_invoice` (`invoice_id`),
  KEY `idx_deal_activities_company_date` (`company_id`,`occurred_at`),
  KEY `idx_deal_activities_deal` (`deal_id`,`occurred_at`),
  CONSTRAINT `fk_deal_activities_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deal_activities_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deal_activities_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deal_activities_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deal_activities_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deal_activities_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deal_activities_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('2', '4', '4', NULL, NULL, NULL, NULL, NULL, 'task_reopen', 'Reopen task: Request feedback from Sample Client', NULL, '{\"task_id\":1}', '2026-07-15 12:11:16', '2026-07-15 04:11:16');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('3', '4', '4', NULL, NULL, NULL, NULL, NULL, 'task_reassigned', 'Task reassigned: Request feedback from Sample Client', NULL, '{\"task_id\":1,\"from\":4,\"to\":4}', '2026-07-16 08:41:20', '2026-07-16 00:41:20');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('4', '4', '4', NULL, NULL, NULL, NULL, NULL, 'task_start', 'Start task: check again', NULL, '{\"task_id\":3}', '2026-07-16 08:41:37', '2026-07-16 00:41:37');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('5', '4', '4', NULL, NULL, NULL, NULL, NULL, 'task_reassigned', 'Task reassigned: Request feedback from Sample Client', NULL, '{\"task_id\":1,\"from\":4,\"to\":4}', '2026-07-16 08:41:48', '2026-07-16 00:41:48');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('6', '4', '4', NULL, NULL, NULL, NULL, NULL, 'tasks_bulk_reassigned', '0 tasks reassigned', NULL, '{\"task_ids\":[],\"to\":23}', '2026-07-16 08:41:54', '2026-07-16 00:41:54');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('7', '4', '4', NULL, NULL, NULL, NULL, NULL, 'task_completed', 'Task completed: check again', 'its done\nManager override: yehey', '{\"task_id\":3}', '2026-07-16 08:42:16', '2026-07-16 00:42:16');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('8', '4', '4', '1', NULL, NULL, NULL, NULL, 'lead_created', 'Lead created', 'manual lead assigned to the pipeline.', NULL, '2026-07-16 08:47:56', '2026-07-16 00:47:56');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('9', '4', '4', NULL, NULL, NULL, NULL, NULL, 'task_completed', 'Task completed: Request feedback from Sample Client', NULL, '{\"task_id\":1}', '2026-07-16 08:51:05', '2026-07-16 00:51:05');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('10', '4', '4', '1', NULL, NULL, NULL, NULL, 'task_snoozed', 'Task rescheduled: Contact checking within one business day', NULL, '{\"task_id\":7,\"due_at\":\"2026-07-16T10:51\"}', '2026-07-16 08:51:20', '2026-07-16 00:51:20');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('11', '4', '4', '1', NULL, NULL, NULL, NULL, 'status_changed', 'Lead marked contacted', NULL, NULL, '2026-07-16 08:59:27', '2026-07-16 00:59:27');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('12', '4', '4', '1', NULL, NULL, NULL, NULL, 'status_changed', 'Lead marked qualified', NULL, NULL, '2026-07-16 09:01:49', '2026-07-16 01:01:49');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('13', '4', '4', '1', NULL, NULL, NULL, NULL, 'status_changed', 'Lead marked disqualified', NULL, NULL, '2026-07-16 09:02:00', '2026-07-16 01:02:00');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('14', '4', '4', '1', '1', '28', NULL, NULL, 'lead_qualified', 'Lead qualified into a deal', NULL, NULL, '2026-07-16 09:11:17', '2026-07-16 01:11:17');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('15', '4', '4', '1', NULL, NULL, NULL, NULL, 'status_changed', 'Lead marked disqualified', NULL, NULL, '2026-07-16 09:13:52', '2026-07-16 01:13:52');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('16', '4', '4', '1', NULL, NULL, NULL, NULL, 'status_changed', 'Lead marked contacted', NULL, NULL, '2026-07-16 09:19:13', '2026-07-16 01:19:13');
INSERT INTO `deal_activities` (`id`, `company_id`, `user_id`, `lead_id`, `deal_id`, `client_id`, `quotation_id`, `invoice_id`, `activity_type`, `subject`, `description`, `metadata`, `occurred_at`, `created_at`) VALUES ('17', '4', NULL, '2', NULL, NULL, NULL, NULL, 'lead_captured', 'Lead captured from A track link test', NULL, NULL, '2026-07-16 09:49:17', '2026-07-16 01:49:17');

DROP TABLE IF EXISTS `deals`;
CREATE TABLE `deals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `stage_id` bigint(20) unsigned NOT NULL,
  `owner_user_id` bigint(20) unsigned DEFAULT NULL,
  `quotation_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `probability` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `expected_close_date` date DEFAULT NULL,
  `next_action` varchar(255) DEFAULT NULL,
  `next_action_at` datetime DEFAULT NULL,
  `lost_reason` varchar(255) DEFAULT NULL,
  `won_at` datetime DEFAULT NULL,
  `lost_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_deals_lead` (`lead_id`),
  KEY `fk_deals_client` (`client_id`),
  KEY `fk_deals_stage` (`stage_id`),
  KEY `fk_deals_owner` (`owner_user_id`),
  KEY `fk_deals_quote` (`quotation_id`),
  KEY `idx_deals_company_stage` (`company_id`,`stage_id`),
  KEY `idx_deals_company_close` (`company_id`,`expected_close_date`),
  KEY `idx_deals_company_owner_stage` (`company_id`,`owner_user_id`,`stage_id`),
  CONSTRAINT `fk_deals_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_deals_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_deals_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_deals_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_deals_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_deals_stage` FOREIGN KEY (`stage_id`) REFERENCES `sales_pipeline_stages` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `deals` (`id`, `company_id`, `lead_id`, `client_id`, `stage_id`, `owner_user_id`, `quotation_id`, `title`, `value`, `probability`, `expected_close_date`, `next_action`, `next_action_at`, `lost_reason`, `won_at`, `lost_at`, `created_at`, `updated_at`) VALUES ('1', '4', '1', '28', '7', '4', NULL, 'momo opportunity', '25832.00', '10', NULL, NULL, NULL, NULL, NULL, NULL, '2026-07-16 01:11:17', NULL);

DROP TABLE IF EXISTS `document_counters`;
CREATE TABLE `document_counters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `counter_key` varchar(60) NOT NULL,
  `next_number` bigint(20) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_document_counter` (`company_id`,`counter_key`),
  CONSTRAINT `fk_counter_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `document_counters` (`id`, `company_id`, `counter_key`, `next_number`) VALUES ('6', '4', 'quotation', '6');
INSERT INTO `document_counters` (`id`, `company_id`, `counter_key`, `next_number`) VALUES ('8', '4', 'invoice', '5');

DROP TABLE IF EXISTS `email_logs`;
CREATE TABLE `email_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `mailable_type` varchar(60) NOT NULL,
  `mailable_id` bigint(20) unsigned NOT NULL,
  `recipient` varchar(190) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `status` enum('queued','sent','failed','opened','clicked') NOT NULL DEFAULT 'queued',
  `error_message` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `opened_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email_logs_company` (`company_id`,`mailable_type`,`mailable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('1', '4', 'quotation', '4', 'accounts@delta.test', 'Quotation sent: Website and Retainer Proposal', 'sent', NULL, '2026-06-14 07:56:30', NULL, '2026-06-14 07:56:30');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('2', '4', 'quotation_viewed', '4', 'owner@bizquote.test', 'Quotation viewed: QT demo', 'sent', NULL, '2026-06-15 07:56:30', NULL, '2026-06-15 07:56:30');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('3', '4', 'quotation_accepted', '4', 'owner@bizquote.test', 'Quotation accepted: QT demo', 'sent', NULL, '2026-06-19 07:56:30', NULL, '2026-06-19 07:56:30');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('4', '4', 'invoice', '3', 'accounts@delta.test', 'Invoice sent: demo', 'sent', NULL, '2026-06-20 07:56:30', NULL, '2026-06-20 07:56:30');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('5', '4', 'quotation', '7', 'admin@kalaharisolar.test', 'Quotation QT-2026-00004', 'sent', NULL, '2026-06-29 09:58:06', NULL, '2026-06-29 09:58:06');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('6', '4', 'quotation', '7', 'admin@kalaharisolar.test', 'Quotation viewed by client', 'opened', NULL, NULL, '2026-06-29 09:58:20', '2026-06-29 09:58:20');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('7', '4', 'quotation_viewed', '7', 'owner@bizquote.test', 'Quotation viewed: QT-2026-00004', 'sent', NULL, '2026-06-29 09:58:20', NULL, '2026-06-29 09:58:20');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('8', '4', 'quotation_accepted', '7', 'owner@bizquote.test', 'Quotation accepted: QT-2026-00004', 'sent', NULL, '2026-06-29 09:59:00', NULL, '2026-06-29 09:59:00');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('9', '4', 'quotation', '8', 'gadgetsfund@gmail.com', 'Quotation QT-2026-00005', 'sent', NULL, '2026-06-29 10:05:28', NULL, '2026-06-29 10:05:28');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('10', '4', 'whatsapp_quotation', '8', '26772877872', 'WhatsApp quotation handoff: QT-2026-00005', 'sent', NULL, '2026-06-29 21:57:37', NULL, '2026-06-29 21:57:37');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('11', '4', 'quotation', '8', 'gadgetsfund@gmail.com', 'Quotation viewed by client', 'opened', NULL, NULL, '2026-06-29 22:03:18', '2026-06-29 22:03:18');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('12', '4', 'quotation_viewed', '8', 'owner@bizquote.test', 'Quotation viewed: QT-2026-00005', 'sent', NULL, '2026-06-29 22:03:18', NULL, '2026-06-29 22:03:18');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('13', '4', 'payment_reminder', '5', 'gadgetsfund@gmail.com', 'Payment reminder: INV-2026-00003', 'sent', NULL, '2026-07-01 02:53:13', NULL, '2026-07-01 02:53:13');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('14', '4', 'payment_reminder', '3', 'accounts@delta.test', 'Payment reminder: INV-2026-00001', 'sent', NULL, '2026-07-01 04:02:26', NULL, '2026-07-01 04:02:26');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('15', '4', 'payment_reminder', '5', 'gadgetsfund@gmail.com', 'Payment reminder: INV-2026-00003', 'sent', NULL, '2026-07-01 04:02:56', NULL, '2026-07-01 04:02:56');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('16', '4', 'payment_receipt', '3', 'gadgetsfund@gmail.com', 'Payment receipt for invoice INV-2026-00003', 'sent', NULL, '2026-07-10 08:37:31', NULL, '2026-07-10 08:37:31');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('17', '4', 'overdue_reminder', '4', 'admin@kalaharisolar.test', 'Overdue invoice reminder: INV-2026-00002', 'sent', NULL, '2026-07-10 13:13:36', NULL, '2026-07-10 13:13:36');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('18', '4', 'invoice', '17', 'accounts@delta.test', 'Invoice INV-2026-00004', 'sent', NULL, '2026-07-22 22:22:46', NULL, '2026-07-22 14:22:46');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('19', '4', 'overdue_reminder', '4', 'admin@kalaharisolar.test', 'Overdue invoice reminder: INV-2026-00002', 'sent', NULL, '2026-07-29 00:47:57', NULL, '2026-07-28 16:47:57');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('20', '4', 'overdue_reminder', '3', 'accounts@delta.test', 'Overdue invoice reminder: INV-2026-00001', 'sent', NULL, '2026-07-29 00:47:58', NULL, '2026-07-28 16:47:58');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('21', '4', 'overdue_reminder', '4', 'admin@kalaharisolar.test', 'Overdue invoice reminder: INV-2026-00002', 'sent', NULL, '2026-07-30 04:16:56', NULL, '2026-07-29 20:16:56');
INSERT INTO `email_logs` (`id`, `company_id`, `mailable_type`, `mailable_id`, `recipient`, `subject`, `status`, `error_message`, `sent_at`, `opened_at`, `created_at`) VALUES ('22', '4', 'overdue_reminder', '3', 'accounts@delta.test', 'Overdue invoice reminder: INV-2026-00001', 'sent', NULL, '2026-07-30 04:16:57', NULL, '2026-07-29 20:16:57');

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `vendor_id` bigint(20) unsigned DEFAULT NULL,
  `expense_number` varchar(60) DEFAULT NULL,
  `category` varchar(100) NOT NULL DEFAULT 'General',
  `description` varchar(255) NOT NULL,
  `expense_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(12,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,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('unpaid','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'unpaid',
  `payment_method` enum('cash','bank_transfer','card','mobile_money','manual') NOT NULL DEFAULT 'manual',
  `reference` varchar(160) DEFAULT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_expenses_company_date` (`company_id`,`expense_date`),
  KEY `idx_expenses_company_status` (`company_id`,`status`),
  KEY `idx_expenses_vendor` (`vendor_id`),
  CONSTRAINT `fk_expenses_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_expenses_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `expenses` (`id`, `company_id`, `vendor_id`, `expense_number`, `category`, `description`, `expense_date`, `due_date`, `subtotal`, `tax_amount`, `total`, `paid_amount`, `status`, `payment_method`, `reference`, `receipt_path`, `notes`, `paid_at`, `created_at`, `updated_at`) VALUES ('4', '4', '4', NULL, 'Software', 'Cloud hosting and backups', '2026-06-17', '2026-07-06', '1450.00', '203.00', '1653.00', '1653.00', 'paid', 'card', 'CLOUD-001', NULL, NULL, '2026-06-29 15:56:30', '2026-06-29 07:56:30', NULL);
INSERT INTO `expenses` (`id`, `company_id`, `vendor_id`, `expense_number`, `category`, `description`, `expense_date`, `due_date`, `subtotal`, `tax_amount`, `total`, `paid_amount`, `status`, `payment_method`, `reference`, `receipt_path`, `notes`, `paid_at`, `created_at`, `updated_at`) VALUES ('5', '4', '5', NULL, 'Office Supplies', 'Printer paper and stationery', '2026-06-23', '2026-07-06', '620.00', '86.80', '706.80', '706.80', 'paid', 'cash', 'SUP-104', NULL, NULL, '2026-06-29 15:56:30', '2026-06-29 07:56:30', NULL);
INSERT INTO `expenses` (`id`, `company_id`, `vendor_id`, `expense_number`, `category`, `description`, `expense_date`, `due_date`, `subtotal`, `tax_amount`, `total`, `paid_amount`, `status`, `payment_method`, `reference`, `receipt_path`, `notes`, `paid_at`, `created_at`, `updated_at`) VALUES ('6', '4', '6', NULL, 'Marketing', 'Social campaign ad spend', '2026-06-26', '2026-07-06', '2800.00', '392.00', '3192.00', '0.00', 'unpaid', 'manual', 'AD-OPEN', NULL, NULL, NULL, '2026-06-29 07:56:30', NULL);

DROP TABLE IF EXISTS `guide_download_leads`;
CREATE TABLE `guide_download_leads` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(190) NOT NULL,
  `name` varchar(120) DEFAULT NULL,
  `guide_slug` varchar(100) NOT NULL DEFAULT 'bizquote-pro-user-guide',
  `download_token_hash` char(64) NOT NULL,
  `email_status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `email_error` varchar(500) DEFAULT NULL,
  `email_sent_at` datetime DEFAULT NULL,
  `downloaded_at` datetime DEFAULT NULL,
  `download_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 `uq_guide_download_token` (`download_token_hash`),
  KEY `idx_guide_download_email` (`email`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `guide_download_leads` (`id`, `email`, `name`, `guide_slug`, `download_token_hash`, `email_status`, `email_error`, `email_sent_at`, `downloaded_at`, `download_count`, `created_at`, `updated_at`) VALUES ('2', 'mokgothut@gmail.com', NULL, 'bizquote-pro-user-guide', '9864c2530747a633c06abd88fe4635f1322a53bc122c940a685065bfbc165909', 'sent', NULL, '2026-07-14 09:13:41', '2026-07-14 09:14:04', '1', '2026-07-14 01:13:36', '2026-07-14 01:14:04');

DROP TABLE IF EXISTS `inventory_movements`;
CREATE TABLE `inventory_movements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `movement_type` enum('opening','purchase','sale','adjustment','transfer_in','transfer_out','return') NOT NULL,
  `quantity` decimal(12,3) NOT NULL,
  `unit_cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reference_type` varchar(60) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_inventory_movements_warehouse` (`warehouse_id`),
  KEY `fk_inventory_movements_product` (`product_id`),
  KEY `fk_inventory_movements_user` (`created_by`),
  KEY `idx_inventory_movements_product` (`company_id`,`product_id`,`created_at`),
  CONSTRAINT `fk_inventory_movements_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inventory_movements_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_inventory_movements_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inventory_movements_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `inventory_warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventory_movements` (`id`, `company_id`, `warehouse_id`, `product_id`, `movement_type`, `quantity`, `unit_cost`, `reference_type`, `reference_id`, `note`, `created_by`, `created_at`) VALUES ('1', '4', '2', '12', 'purchase', '52.000', '11.00', NULL, NULL, NULL, '4', '2026-07-16 01:37:08');

DROP TABLE IF EXISTS `inventory_stock_levels`;
CREATE TABLE `inventory_stock_levels` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity_on_hand` decimal(12,3) NOT NULL DEFAULT 0.000,
  `quantity_reserved` decimal(12,3) NOT NULL DEFAULT 0.000,
  `reorder_level` decimal(12,3) NOT NULL DEFAULT 0.000,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_stock` (`warehouse_id`,`product_id`),
  KEY `fk_inventory_stock_product` (`product_id`),
  KEY `idx_inventory_stock_company` (`company_id`,`product_id`),
  CONSTRAINT `fk_inventory_stock_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inventory_stock_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inventory_stock_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `inventory_warehouses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventory_stock_levels` (`id`, `company_id`, `warehouse_id`, `product_id`, `quantity_on_hand`, `quantity_reserved`, `reorder_level`, `updated_at`) VALUES ('1', '4', '2', '12', '52.000', '0.000', '5.000', '2026-07-16 01:37:08');

DROP TABLE IF EXISTS `inventory_warehouses`;
CREATE TABLE `inventory_warehouses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `branch_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(160) NOT NULL,
  `code` varchar(30) NOT NULL,
  `address` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_warehouse_code` (`company_id`,`code`),
  KEY `fk_inventory_warehouses_branch` (`branch_id`),
  CONSTRAINT `fk_inventory_warehouses_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inventory_warehouses_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventory_warehouses` (`id`, `company_id`, `branch_id`, `name`, `code`, `address`, `status`, `created_at`) VALUES ('1', '1', '1', 'Demo Company Stock', 'MAIN', NULL, 'active', '2026-07-10 11:45:53');
INSERT INTO `inventory_warehouses` (`id`, `company_id`, `branch_id`, `name`, `code`, `address`, `status`, `created_at`) VALUES ('2', '4', '2', 'BizQuote Demo Company Stock', 'MAIN', NULL, 'active', '2026-07-10 11:45:53');

DROP TABLE IF EXISTS `invoice_items`;
CREATE TABLE `invoice_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `description` text NOT NULL,
  `quantity` decimal(12,2) NOT NULL DEFAULT 1.00,
  `unit_price` 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,
  `line_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_invoice_items_invoice` (`invoice_id`),
  KEY `fk_invoice_items_product` (`product_id`),
  CONSTRAINT `fk_invoice_items_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_invoice_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `invoice_items` (`id`, `invoice_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('4', '3', '10', 'Website Build Package', '1.00', '18500.00', '14.00', '2590.00', '21090.00', '2026-06-29 07:56:30');
INSERT INTO `invoice_items` (`id`, `invoice_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('5', '3', '9', 'Business Consulting Retainer', '1.00', '8500.00', '14.00', '1190.00', '9690.00', '2026-06-29 07:56:30');
INSERT INTO `invoice_items` (`id`, `invoice_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('6', '4', '12', 'Brand Identity Kit', '1.00', '7800.00', '14.00', '1092.00', '8892.00', '2026-06-29 07:56:30');
INSERT INTO `invoice_items` (`id`, `invoice_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('7', '5', '10', 'Responsive website design and implementation', '1.00', '18500.00', '14.00', '2590.00', '21090.00', '2026-07-01 02:26:08');
INSERT INTO `invoice_items` (`id`, `invoice_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('19', '17', '10', 'Website Build Package', '1.00', '18500.00', '14.00', '2590.00', '21090.00', '2026-07-22 14:22:44');
INSERT INTO `invoice_items` (`id`, `invoice_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('20', '17', '9', 'Business Consulting Retainer', '1.00', '8500.00', '14.00', '1190.00', '9690.00', '2026-07-22 14:22:44');

DROP TABLE IF EXISTS `invoice_milestones`;
CREATE TABLE `invoice_milestones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `title` varchar(160) NOT NULL,
  `description` text DEFAULT NULL,
  `percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `due_date` date DEFAULT NULL,
  `status` enum('pending','requested','paid','waived') NOT NULL DEFAULT 'pending',
  `requested_at` timestamp NULL DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_milestones_invoice` (`invoice_id`,`status`,`due_date`),
  CONSTRAINT `fk_invoice_milestones_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `invoice_milestones` (`id`, `invoice_id`, `title`, `description`, `percent`, `amount`, `due_date`, `status`, `requested_at`, `paid_at`, `created_at`, `updated_at`) VALUES ('1', '3', 'Kickoff deposit', NULL, '30.00', '9000.00', '2026-06-21', 'paid', NULL, '2026-06-21 12:05:21', '2026-06-29 12:05:21', NULL);
INSERT INTO `invoice_milestones` (`id`, `invoice_id`, `title`, `description`, `percent`, `amount`, `due_date`, `status`, `requested_at`, `paid_at`, `created_at`, `updated_at`) VALUES ('2', '3', 'Design approval', NULL, '35.00', '10500.00', '2026-07-04', 'requested', '2026-06-29 12:05:21', NULL, '2026-06-29 12:05:21', NULL);
INSERT INTO `invoice_milestones` (`id`, `invoice_id`, `title`, `description`, `percent`, `amount`, `due_date`, `status`, `requested_at`, `paid_at`, `created_at`, `updated_at`) VALUES ('3', '3', 'Launch handover', NULL, '35.00', '10500.00', '2026-07-19', 'pending', NULL, NULL, '2026-06-29 12:05:21', NULL);

DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `quotation_id` bigint(20) unsigned DEFAULT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `invoice_number` varchar(60) NOT NULL,
  `title` varchar(180) NOT NULL DEFAULT 'Invoice',
  `issue_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `deposit_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `balance_due` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_request_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','sent','viewed','partially_paid','paid','overdue','cancelled','refunded') NOT NULL DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `public_token` varchar(120) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `public_token` (`public_token`),
  UNIQUE KEY `uq_invoice_number` (`company_id`,`invoice_number`),
  KEY `fk_invoices_client` (`client_id`),
  KEY `fk_invoices_quote` (`quotation_id`),
  KEY `idx_invoices_company_status` (`company_id`,`status`),
  KEY `idx_invoices_due` (`due_date`),
  CONSTRAINT `fk_invoices_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_invoices_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_invoices_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `invoices` (`id`, `company_id`, `quotation_id`, `client_id`, `invoice_number`, `title`, `issue_date`, `due_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `paid_amount`, `balance_due`, `payment_request_amount`, `status`, `notes`, `terms`, `public_token`, `created_at`, `updated_at`) VALUES ('3', '4', '4', '7', 'INV-2026-00001', 'Website and Retainer Proposal', '2026-06-20', '2026-07-04', '27000.00', '3780.00', '1500.00', '9000.00', '29280.00', '9000.00', '20280.00', '9000.00', 'partially_paid', 'Deposit required before project kickoff.', 'Deposit payable on acceptance. Remaining balance due on delivery.', '0e91a18b7e710b2ec3f904b3396a8415f14a3b94c27bb155', '2026-06-29 07:56:30', '2026-06-29 12:07:47');
INSERT INTO `invoices` (`id`, `company_id`, `quotation_id`, `client_id`, `invoice_number`, `title`, `issue_date`, `due_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `paid_amount`, `balance_due`, `payment_request_amount`, `status`, `notes`, `terms`, `public_token`, `created_at`, `updated_at`) VALUES ('4', '4', '6', '9', 'INV-2026-00002', 'Overdue Branding Invoice', '2026-05-25', '2026-06-09', '7800.00', '1092.00', '0.00', '0.00', '8892.00', '0.00', '8892.00', '0.00', 'overdue', 'Legacy branding work.', 'Payment due within 14 days.', 'cc31c2bc2d2da0bc5790727d9283d550080723560d614506', '2026-06-29 07:56:30', '2026-06-29 07:56:30');
INSERT INTO `invoices` (`id`, `company_id`, `quotation_id`, `client_id`, `invoice_number`, `title`, `issue_date`, `due_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `paid_amount`, `balance_due`, `payment_request_amount`, `status`, `notes`, `terms`, `public_token`, `created_at`, `updated_at`) VALUES ('5', '4', '8', '10', 'INV-2026-00003', 'Quotation', '2026-07-01', '2026-07-15', '18500.00', '2590.00', '0.00', '0.00', '21090.00', '21090.00', '0.00', '0.00', 'paid', '', 'Valid until expiry date. Prices subject to stock availability.', 'ef3799dcfa24df86c30715b19909e41a363a5d4215b97e7b', '2026-07-01 02:26:08', '2026-07-10 08:37:25');
INSERT INTO `invoices` (`id`, `company_id`, `quotation_id`, `client_id`, `invoice_number`, `title`, `issue_date`, `due_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `paid_amount`, `balance_due`, `payment_request_amount`, `status`, `notes`, `terms`, `public_token`, `created_at`, `updated_at`) VALUES ('17', '4', NULL, '7', 'INV-2026-00004', 'Website and Retainer Proposal', '2026-07-22', '2026-08-05', '27000.00', '3780.00', '1500.00', '9000.00', '29280.00', '0.00', '29280.00', '9000.00', 'sent', 'Deposit required before project kickoff.', 'Deposit payable on acceptance. Remaining balance due on delivery.', 'bfc9d2146818f12f626563ee305bba6754abb67512b876ed', '2026-07-22 14:22:44', NULL);

DROP TABLE IF EXISTS `leads`;
CREATE TABLE `leads` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `owner_user_id` bigint(20) unsigned DEFAULT NULL,
  `campaign_id` bigint(20) unsigned DEFAULT NULL,
  `social_post_id` bigint(20) unsigned DEFAULT NULL,
  `converted_client_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(160) NOT NULL,
  `company_name` varchar(160) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone` varchar(80) DEFAULT NULL,
  `source` varchar(80) NOT NULL DEFAULT 'manual',
  `source_detail` varchar(190) DEFAULT NULL,
  `status` enum('new','contacted','qualified','disqualified','converted') NOT NULL DEFAULT 'new',
  `estimated_value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `external_id` varchar(190) DEFAULT NULL,
  `attribution_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attribution_json`)),
  `first_contacted_at` datetime DEFAULT NULL,
  `qualified_at` datetime DEFAULT NULL,
  `converted_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_leads_owner` (`owner_user_id`),
  KEY `fk_leads_campaign` (`campaign_id`),
  KEY `fk_leads_social_post` (`social_post_id`),
  KEY `fk_leads_client` (`converted_client_id`),
  KEY `idx_leads_company_status` (`company_id`,`status`,`created_at`),
  KEY `idx_leads_company_source` (`company_id`,`source`),
  KEY `idx_leads_external` (`company_id`,`external_id`),
  KEY `idx_leads_company_owner_status` (`company_id`,`owner_user_id`,`status`),
  CONSTRAINT `fk_leads_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `marketing_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leads_client` FOREIGN KEY (`converted_client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leads_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_leads_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leads_social_post` FOREIGN KEY (`social_post_id`) REFERENCES `social_posts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `leads` (`id`, `company_id`, `owner_user_id`, `campaign_id`, `social_post_id`, `converted_client_id`, `name`, `company_name`, `email`, `phone`, `source`, `source_detail`, `status`, `estimated_value`, `notes`, `external_id`, `attribution_json`, `first_contacted_at`, `qualified_at`, `converted_at`, `created_at`, `updated_at`) VALUES ('1', '4', '4', '10', NULL, '28', 'checking', 'momo', 'momo@gmail.com', NULL, 'manual', NULL, 'contacted', '25832.00', 'a new lead', NULL, NULL, '2026-07-16 08:59:27', '2026-07-16 09:01:49', NULL, '2026-07-16 00:47:56', '2026-07-16 01:19:13');
INSERT INTO `leads` (`id`, `company_id`, `owner_user_id`, `campaign_id`, `social_post_id`, `converted_client_id`, `name`, `company_name`, `email`, `phone`, `source`, `source_detail`, `status`, `estimated_value`, `notes`, `external_id`, `attribution_json`, `first_contacted_at`, `qualified_at`, `converted_at`, `created_at`, `updated_at`) VALUES ('2', '4', NULL, NULL, NULL, NULL, 'Kabo Motho', 'Hellos', 'gadgetsfund@gmail.com', '72877872', 'campaign', 'A track link test', 'new', '0.00', 'Checking the link attributes', NULL, '{\"marketing_link_id\":1,\"visit_id\":2,\"utm_source\":null,\"utm_medium\":null}', NULL, NULL, NULL, '2026-07-16 01:49:17', NULL);

DROP TABLE IF EXISTS `marketing_activity_logs`;
CREATE TABLE `marketing_activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `campaign_id` bigint(20) unsigned DEFAULT NULL,
  `social_post_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `description` varchar(255) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_marketing_activity_campaign` (`campaign_id`),
  KEY `fk_marketing_activity_post` (`social_post_id`),
  KEY `idx_marketing_activity_company` (`company_id`,`created_at`),
  CONSTRAINT `fk_marketing_activity_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `marketing_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_marketing_activity_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_marketing_activity_post` FOREIGN KEY (`social_post_id`) REFERENCES `social_posts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('1', '4', '1', NULL, 'auto_campaign_created', 'Generated 7 product poster drafts.', NULL, '2026-06-29 23:02:40');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('2', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-29 23:04:50');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('3', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 08:38:58');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('4', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 08:56:57');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('5', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 09:01:48');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('6', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 09:04:57');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('7', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 09:33:28');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('8', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 09:34:19');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('9', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 09:55:42');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('10', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:10:27');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('11', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:15:49');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('12', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:17:17');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('13', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:23:28');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('14', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:32:54');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('15', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:32:54');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('16', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:32:54');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('17', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:32:54');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('18', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:33:33');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('19', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:33:58');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('20', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 10:40:07');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('21', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 11:04:06');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('22', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 11:14:56');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('23', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-06-30 11:15:46');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('24', '4', NULL, NULL, 'bulk_media_imported', 'Queued 2 media assets for download.', NULL, '2026-06-30 11:17:18');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('25', '4', NULL, NULL, 'bulk_media_imported', 'Queued 3 media assets for download.', NULL, '2026-06-30 11:29:35');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('26', '4', NULL, NULL, 'media_deleted', 'Deleted 2 media assets.', NULL, '2026-06-30 11:53:00');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('27', '4', NULL, NULL, 'media_deleted', 'Deleted 2 media assets.', NULL, '2026-06-30 11:53:13');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('28', '4', NULL, NULL, 'media_deleted', 'Deleted 4 media assets.', NULL, '2026-06-30 11:53:23');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('29', '4', NULL, NULL, 'media_deleted', 'Deleted 1 media assets.', NULL, '2026-06-30 11:53:30');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('30', '4', '2', NULL, 'draft_created', 'Marketing draft saved.', NULL, '2026-06-30 23:11:14');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('32', '4', '1', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"facebook\",\"content_preview\":\"[Trust] Choose Business Consulting Retainer when you need practical delivery, clear communication, and professional support.\"}', '2026-07-01 01:08:16');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('33', '4', '1', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"facebook\",\"content_preview\":\"[Benefit] Business Consulting Retainer helps your business work smarter with less stress. Ask us for a quote today.\"}', '2026-07-01 01:14:57');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('34', '4', '1', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"instagram\",\"content_preview\":\"[Problem\\/Solution] Still looking for a dependable way to improve operations? Business Consulting Retainer is built to solve that gap.\"}', '2026-07-01 01:15:11');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('35', '4', '2', NULL, 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-01 01:16:53');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('36', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-07-01 01:17:20');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('37', '4', '1', NULL, 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-01 01:17:53');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('38', '4', '1', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"linkedin\",\"content_preview\":\"[FAQ] Wondering if Business Consulting Retainer is right for your business? Send us your requirements and we will guide you.\"}', '2026-07-01 01:18:01');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('39', '4', '1', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"tiktok\",\"content_preview\":\"[Behind the scenes] A good result starts with a clear process. Here is how we approach Business Consulting Retainer: listen, plan, deliver.\"}', '2026-07-01 01:18:06');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('40', '4', '1', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"whatsapp\",\"content_preview\":\"[Urgency] Planning your next project? Book Business Consulting Retainer early and avoid last-minute pressure.\"}', '2026-07-01 01:18:11');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('41', '4', '2', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"facebook\",\"content_preview\":\"Businesses choose Website Build Package when they need dependable delivery and practical results. http:\\/\\/localhost:8000\\/quotations\\/create #BusinessGrowth #Botsw\"}', '2026-07-01 01:20:30');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('42', '4', '3', NULL, 'auto_campaign_created', 'Generated 7 product poster drafts.', NULL, '2026-07-01 01:23:20');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('43', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"instagram\",\"content_preview\":\"[Reel idea] Show the before, the process, and the final result. Caption: Website Build Package turns business needs into action.\"}', '2026-07-01 01:24:23');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('44', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"facebook\",\"content_preview\":\"[Trust] Choose Website Build Package when you need practical delivery, clear communication, and professional support.\"}', '2026-07-01 01:24:28');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('45', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"tiktok\",\"content_preview\":\"[Behind the scenes] A good result starts with a clear process. Here is how we approach Website Build Package: listen, plan, deliver.\"}', '2026-07-01 01:24:31');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('46', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"whatsapp\",\"content_preview\":\"[Urgency] Planning your next project? Book Website Build Package early and avoid last-minute pressure.\"}', '2026-07-01 01:24:35');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('47', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"linkedin\",\"content_preview\":\"[FAQ] Wondering if Website Build Package is right for your business? Send us your requirements and we will guide you.\"}', '2026-07-01 01:24:38');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('48', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"instagram\",\"content_preview\":\"[Problem\\/Solution] Still looking for a dependable way to improve operations? Website Build Package is built to solve that gap.\"}', '2026-07-01 01:24:42');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('49', '4', '3', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"facebook\",\"content_preview\":\"[Benefit] Website Build Package helps your business work smarter with less stress. Ask us for a quote today.\"}', '2026-07-01 01:24:45');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('50', '4', '4', NULL, 'auto_campaign_created', 'Generated 3 product poster drafts.', NULL, '2026-07-01 01:25:56');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('51', '4', '4', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"linkedin\",\"content_preview\":\"[FAQ] Wondering if Website Build Package is right for your business? Send us your requirements and we will guide you.\"}', '2026-07-01 01:26:05');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('52', '4', '4', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"instagram\",\"content_preview\":\"[Problem\\/Solution] Still looking for a dependable way to improve operations? Website Build Package is built to solve that gap.\"}', '2026-07-01 01:26:08');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('53', '4', '4', NULL, 'draft_deleted', 'Marketing draft post deleted.', '{\"platform\":\"facebook\",\"content_preview\":\"[Benefit] Website Build Package helps your business work smarter with less stress. Ask us for a quote today.\"}', '2026-07-01 01:26:11');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('54', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-07-01 01:51:37');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('55', '4', '5', '19', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-01 01:51:57');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('56', '4', NULL, '19', 'metrics_updated', 'Marketing performance metrics updated.', NULL, '2026-07-01 01:53:10');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('57', '4', '5', '19', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-01 01:53:28');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('58', '4', '5', '19', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-01 01:54:01');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('59', '4', '6', NULL, 'draft_created', 'Marketing draft saved.', NULL, '2026-07-01 01:59:35');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('60', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-07-01 02:00:26');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('61', '4', NULL, NULL, 'media_deleted', 'Deleted 1 media assets.', NULL, '2026-07-01 02:01:02');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('62', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-07-01 02:01:55');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('63', '4', NULL, NULL, 'media_deleted', 'Deleted 1 media assets.', NULL, '2026-07-01 02:03:19');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('64', '4', NULL, NULL, 'media_saved', 'Media asset saved to library.', NULL, '2026-07-01 02:04:08');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('65', '4', '6', '20', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-01 02:05:09');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('66', '4', NULL, NULL, 'bulk_media_imported', 'Queued 4 media assets for download.', NULL, '2026-07-02 15:02:19');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('67', '4', NULL, NULL, 'media_deleted', 'Deleted 1 media assets.', NULL, '2026-07-02 15:04:00');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('68', '4', NULL, NULL, 'bulk_media_imported', 'Queued 4 media assets for download.', NULL, '2026-07-02 15:05:52');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('69', '4', NULL, NULL, 'bulk_media_imported', 'Queued 5 media assets for download.', NULL, '2026-07-02 15:07:22');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('70', '4', NULL, NULL, 'media_deleted', 'Deleted 1 media assets.', NULL, '2026-07-02 15:25:39');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('71', '4', '7', '21', 'product_social_post_created_with_ai', 'Created Facebook image draft using AI API key.', '{\"product_id\":10,\"platform\":\"facebook\",\"image_path\":\"uploads\\/marketing-media\\/4\\/20260701095137-screenshot-2026-06-13-020052-d60837fb.png\",\"ai_message\":\"OpenAI generation failed: Failed to connect to 127.0.0.1 port 9 after 2037 ms: Couldn\'t connect to server Offline suggestions are shown.\"}', '2026-07-04 12:24:12');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('72', '4', '7', '22', 'product_social_post_created_with_ai', 'Created Whatsapp image draft using AI API key.', '{\"product_id\":10,\"platform\":\"whatsapp\",\"image_path\":\"uploads\\/marketing-media\\/4\\/20260701095137-screenshot-2026-06-13-020052-d60837fb.png\",\"ai_message\":\"OpenAI generation failed: Failed to connect to 127.0.0.1 port 9 after 2022 ms: Couldn\'t connect to server Offline suggestions are shown.\"}', '2026-07-04 12:24:14');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('73', '4', '7', '21', 'product_social_post_regenerated_with_openai', 'Regenerated Facebook image draft with OpenAI API key.', '{\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\"}', '2026-07-04 12:33:11');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('74', '4', '7', '22', 'product_social_post_regenerated_with_openai', 'Regenerated Whatsapp image draft with OpenAI API key.', '{\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\"}', '2026-07-04 12:33:19');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('75', '4', NULL, NULL, 'media_deleted', 'Deleted 1 media assets.', NULL, '2026-07-05 04:12:40');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('76', '4', NULL, NULL, 'ai_generator_used', 'Generated AI creative tool output.', NULL, '2026-07-05 04:26:51');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('77', '4', NULL, NULL, 'ai_generator_used', 'Generated AI creative tool output.', NULL, '2026-07-05 04:44:30');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('78', '4', NULL, NULL, 'ai_generator_used', 'Generated AI creative tool output.', NULL, '2026-07-05 05:04:13');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('79', '4', '8', NULL, 'draft_created', 'Marketing draft saved.', NULL, '2026-07-05 05:06:19');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('80', '4', '8', '23', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-05 05:08:05');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('81', '4', '8', '23', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-05 05:09:02');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('82', '4', '8', '23', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-05 05:31:46');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('83', '4', '8', '23', 'draft_updated', 'Marketing draft post updated.', NULL, '2026-07-05 05:35:42');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('84', '4', '8', '23', 'draft_updated', 'Approved marketing post edited and kept approved.', NULL, '2026-07-05 06:00:42');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('85', '4', '8', '23', 'draft_updated', 'Approved marketing post edited and kept approved.', NULL, '2026-07-05 06:01:29');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('86', '4', '9', NULL, 'draft_created', 'Marketing draft saved.', NULL, '2026-07-05 06:13:08');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('87', '4', NULL, NULL, 'ai_generator_used', 'Generated AI creative tool output.', NULL, '2026-07-15 01:42:09');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('88', '4', NULL, NULL, 'ai_generator_used', 'Generated AI creative tool output.', NULL, '2026-07-15 01:53:59');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('89', '4', '10', NULL, 'draft_created', 'Marketing draft saved.', NULL, '2026-07-15 01:55:18');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('90', '4', '10', '25', 'draft_updated', 'Approved marketing post edited and kept approved.', NULL, '2026-07-15 02:47:42');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('91', '4', '5', '19', 'draft_updated', 'Approved marketing post edited and kept approved.', NULL, '2026-07-15 02:52:08');
INSERT INTO `marketing_activity_logs` (`id`, `company_id`, `campaign_id`, `social_post_id`, `action`, `description`, `payload`, `created_at`) VALUES ('92', '4', '5', '19', 'draft_updated', 'Approved marketing post edited and kept approved.', NULL, '2026-07-15 02:52:40');

DROP TABLE IF EXISTS `marketing_campaigns`;
CREATE TABLE `marketing_campaigns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(180) NOT NULL,
  `platform` varchar(60) NOT NULL,
  `objective` varchar(120) DEFAULT NULL,
  `media_brief` text DEFAULT NULL,
  `generated_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`generated_content`)),
  `status` enum('draft','scheduled','published','completed') NOT NULL DEFAULT 'draft',
  `scheduled_at` datetime DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_campaign_company` (`company_id`),
  KEY `fk_campaign_product` (`product_id`),
  CONSTRAINT `fk_campaign_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_campaign_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('1', '4', '9', 'Business Consulting Retainer Auto Poster', 'multi', 'Automated product promotion', 'Use saved media assets or product visuals for each post.', '{\"automation\":\"product_poster\",\"days\":7}', 'draft', NULL, NULL, '2026-06-29 23:02:40', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('2', '4', '10', 'Campaign - Facebook', 'facebook', 'Awareness and quote requests', 'Use a clean real-world visual of Website Build Package in use. For reels, show the problem, the product/service in action, and the result in under 20 seconds.', '{\"post\":\"Businesses choose Website Build Package when they need dependable delivery and practical results. http:\\/\\/localhost:8000\\/quotations\\/create #BusinessGrowth #BotswanaBusiness{product_name} {quote_link}\",\"hashtags\":[\"#BusinessGrowth\",\"#Digital\"],\"media_type\":\"video\",\"media_url\":\"https:\\/\\/www.tiktok.com\\/@just.mbaali_\\/video\\/7648298953322106132?is_from_webapp=1&sender_device=pc&web_id=7625726305422771720\",\"media_source\":\"\"}', 'draft', NULL, NULL, '2026-06-30 23:11:14', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('3', '4', '10', 'Website Build Package Auto Poster', 'multi', 'Automated product promotion', 'Use saved media assets or product visuals for each post.', '{\"automation\":\"product_poster\",\"days\":7}', 'draft', NULL, NULL, '2026-07-01 01:23:20', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('4', '4', '10', 'Website Build Package Auto Poster', 'multi', 'Automated product promotion', 'Use saved media assets or product visuals for each post.', '{\"automation\":\"product_poster\",\"days\":3}', 'draft', NULL, NULL, '2026-07-01 01:25:56', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('5', '4', '12', 'Campaign - Whatsapp', 'whatsapp', 'Awareness and quote requests', 'Use a clean real-world visual of Brand Identity Kit in use. For reels, show the problem, the product/service in action, and the result in under 20 seconds.', '{\"post\":\"Looking for a reliable solution? Try Brand Identity Kit today and experience professional value built for your business needs. Contact us for a quotation.\",\"hashtags\":[\"#BusinessGrowth\",\"#BotswanaBusiness\",\"#SmartSolutions\"],\"media_type\":\"none\",\"media_url\":\"\",\"media_path\":null,\"media_source\":\"\"}', 'draft', NULL, NULL, '2026-07-01 01:42:05', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('6', '4', '9', 'Campaign - Facebook', 'facebook', 'Awareness and quote requests', 'Use a clean real-world visual of Business Consulting Retainer in use. For reels, show the problem, the product/service in action, and the result in under 20 seconds.', '{\"post\":\"Still losing time on manual work? Business Consulting Retainer helps you move faster, look sharper {product_name}, and serve customers better. Contact us today for a quotation.  {whatsapp_link} {hashtags}\",\"hashtags\":[\"#BusinessGrowth\",\"#BotswanaBusiness\",\"#SmartSolutions\"],\"media_type\":\"none\",\"media_url\":\"https:\\/\\/stock.adobe.com\\/video\\/computer-night-and-male-developer-working-in-the-office-developing-an-online-website-project-technology-business-and-professional-man-coder-doing-coding-research-on-a-pc-in-workplace-for-overtime\\/609534804?clickref=1011lDgG3ecW&mv=affiliate&mv2=pz&as_camptype=search-top&as_channel=affiliate&as_source=partnerize&as_campaign=cheezy&asset_id=227985706\",\"media_path\":null,\"media_source\":\"https:\\/\\/stock.adobe.com\\/\"}', 'draft', NULL, NULL, '2026-07-01 01:59:35', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('7', '4', '10', 'Website Build Package Facebook / WhatsApp Image Post', 'facebook,whatsapp', 'Product sales and quote requests', 'Use the saved product image.', '{\"automation\":\"api_key_image_post_test\",\"platforms\":[\"facebook\",\"whatsapp\"],\"product_image\":\"uploads\\/marketing-media\\/4\\/20260701095137-screenshot-2026-06-13-020052-d60837fb.png\"}', 'draft', NULL, NULL, '2026-07-04 12:24:10', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('8', '4', NULL, 'AI Generator - Facebook', 'facebook', 'AI generated campaign draft', 'Create a clean, modern educational website hero image for a tutoring and study-resource platform called ThutoNest. Show diverse students from primary, secondary, and high school ages studying with books, worksheets, and a laptop in a bright, organized learning space. Include subtle visual elements like exam papers, study guides, and digital learning resources. Style should be professional, welcoming, and trustworthy, with soft blue, green, and white tones. Leave space for headline text on the left and a call-to-action button on the right. No logos, no watermarks, no copyrighted characters.', '{\"post\":\"Headline: Learn Smarter with ThutoNest\\r\\nSubheadline: Access past exam papers, study guides, worksheets, and tutoring support designed to help students prepare with confidence.\\r\\nCTA Button: Explore Resources\",\"hashtags\":[\"#ThutoNest\",\"#StudyResources\",\"#PastExamPapers\",\"#TutoringSupport\",\"#StudentSuccess\"],\"media_type\":\"image\",\"media_url\":\"http:\\/\\/localhost:8000\\/uploads\\/marketing-media\\/4\\/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg\",\"media_path\":\"uploads\\/marketing-media\\/4\\/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg\",\"media_source\":\"AI Generator\"}', 'draft', NULL, NULL, '2026-07-05 05:06:19', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('9', '4', '11', 'Campaign - Facebook', 'facebook', 'Awareness and quote requests', 'Create a clean, professional Facebook visual showing a business computer setup, a support dashboard, or a technician-style branded graphic. Include the service name, monthly price, and a simple call to action such as \'Request a Quote\'. Keep the design clear, modern, and easy to read on mobile.', '{\"post\":\"Monthly technical support made simple. Our {product_name} package is available at BWP {price} and is designed to help your business stay up and running. Need ongoing help with your systems? Message us for a quote today.{hashtags}{category}\\n\\n#SupportAndMaintenance #TechnicalSupport #BusinessSupport #MonthlyMaintenance #SmallBusinessBotswana #BotswanaBusiness #TechSupport #BusinessServices\",\"hashtags\":[\"#SupportAndMaintenance\",\"#TechnicalSupport\",\"#BusinessSupport\",\"#MonthlyMaintenance\",\"#SmallBusinessBotswana\",\"#BotswanaBusiness\",\"#TechSupport\",\"#BusinessServices\"],\"media_type\":\"image\",\"media_url\":\"http:\\/\\/localhost:8000\\/uploads\\/marketing-media\\/4\\/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg\",\"media_path\":\"uploads\\/marketing-media\\/4\\/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg\",\"media_source\":\"AI Generator\"}', 'draft', NULL, NULL, '2026-07-05 06:13:08', NULL);
INSERT INTO `marketing_campaigns` (`id`, `company_id`, `product_id`, `name`, `platform`, `objective`, `media_brief`, `generated_content`, `status`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('10', '4', NULL, 'Custom AI Creative - Facebook', 'facebook', 'Custom AI creative draft', 'Professional marketing visual for a multi-school management system: a clean modern school administration dashboard displayed on a laptop or large screen, surrounded by subtle icons representing multiple campuses, students, schedules, reports, communication, and attendance. Bright, polished corporate style, blue and white color palette, minimal clutter, high-tech education theme, realistic lighting, modern office or school setting, designed for a social media post.', '{\"post\":\"SchoolPixels is a multi-school management system designed for the needs of modern schools. From centralizing operations to improving coordination across multiple campuses, it helps school teams work more efficiently and stay organized. A smarter way to manage today\\u2019s growing school network. Reach out for a quote or purchase inquiry.\\n\\n#SchoolManagement #MultiSchoolSystem #EducationTechnology #SchoolAdministration #EdTech #SchoolOperations #ModernSchools\",\"hashtags\":[\"#SchoolManagement\",\"#MultiSchoolSystem\",\"#EducationTechnology\",\"#SchoolAdministration\",\"#EdTech\",\"#SchoolOperations\",\"#ModernSchools\"],\"media_type\":\"image\",\"media_url\":\"http:\\/\\/localhost:8000\\/uploads\\/marketing-media\\/4\\/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg\",\"media_path\":\"uploads\\/marketing-media\\/4\\/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg\",\"media_source\":\"AI Generator\"}', 'draft', NULL, NULL, '2026-07-15 01:55:18', NULL);

DROP TABLE IF EXISTS `marketing_link_visits`;
CREATE TABLE `marketing_link_visits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `marketing_link_id` bigint(20) unsigned NOT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `referrer` text DEFAULT NULL,
  `utm_source` varchar(120) DEFAULT NULL,
  `utm_medium` varchar(120) DEFAULT NULL,
  `utm_campaign` varchar(160) DEFAULT NULL,
  `ip_hash` varchar(64) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `visited_at` datetime NOT NULL,
  `converted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_marketing_link_visits_lead` (`lead_id`),
  KEY `idx_marketing_link_visits_link_date` (`marketing_link_id`,`visited_at`),
  CONSTRAINT `fk_marketing_link_visits_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_marketing_link_visits_link` FOREIGN KEY (`marketing_link_id`) REFERENCES `marketing_links` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `marketing_link_visits` (`id`, `marketing_link_id`, `lead_id`, `referrer`, `utm_source`, `utm_medium`, `utm_campaign`, `ip_hash`, `user_agent`, `visited_at`, `converted_at`) VALUES ('1', '1', NULL, 'http://localhost:8000/sales?tab=attribution', NULL, NULL, NULL, 'eff8e7ca506627fe15dda5e0e512fcaad70b6d520f37cc76597fdb4f2d83a1a3', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '2026-07-16 09:22:14', NULL);
INSERT INTO `marketing_link_visits` (`id`, `marketing_link_id`, `lead_id`, `referrer`, `utm_source`, `utm_medium`, `utm_campaign`, `ip_hash`, `user_agent`, `visited_at`, `converted_at`) VALUES ('2', '1', '2', 'http://localhost:8000/sales?tab=attribution', NULL, NULL, NULL, 'eff8e7ca506627fe15dda5e0e512fcaad70b6d520f37cc76597fdb4f2d83a1a3', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '2026-07-16 09:48:13', '2026-07-16 09:49:17');
INSERT INTO `marketing_link_visits` (`id`, `marketing_link_id`, `lead_id`, `referrer`, `utm_source`, `utm_medium`, `utm_campaign`, `ip_hash`, `user_agent`, `visited_at`, `converted_at`) VALUES ('3', '1', NULL, 'http://localhost:8000/l/58d9d87df9997b6a0c661998f6333ec2', NULL, NULL, NULL, 'eff8e7ca506627fe15dda5e0e512fcaad70b6d520f37cc76597fdb4f2d83a1a3', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/150.0.0.0 Safari/537.36', '2026-07-16 09:49:17', NULL);
INSERT INTO `marketing_link_visits` (`id`, `marketing_link_id`, `lead_id`, `referrer`, `utm_source`, `utm_medium`, `utm_campaign`, `ip_hash`, `user_agent`, `visited_at`, `converted_at`) VALUES ('4', '1', NULL, 'http://localhost:8000/sales?tab=attribution', NULL, NULL, NULL, 'eff8e7ca506627fe15dda5e0e512fcaad70b6d520f37cc76597fdb4f2d83a1a3', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:152.0) Gecko/20100101 Firefox/152.0', '2026-07-16 09:51:27', NULL);

DROP TABLE IF EXISTS `marketing_links`;
CREATE TABLE `marketing_links` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `campaign_id` bigint(20) unsigned DEFAULT NULL,
  `social_post_id` bigint(20) unsigned DEFAULT NULL,
  `token` varchar(80) NOT NULL,
  `label` varchar(160) NOT NULL,
  `destination_type` enum('lead_form','whatsapp','website') NOT NULL DEFAULT 'lead_form',
  `destination_url` text DEFAULT NULL,
  `default_source` varchar(80) NOT NULL DEFAULT 'campaign',
  `click_count` int(10) unsigned NOT NULL DEFAULT 0,
  `lead_count` int(10) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','paused') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_marketing_link_token` (`token`),
  KEY `fk_marketing_links_campaign` (`campaign_id`),
  KEY `fk_marketing_links_social_post` (`social_post_id`),
  KEY `idx_marketing_links_company` (`company_id`,`status`),
  CONSTRAINT `fk_marketing_links_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `marketing_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_marketing_links_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_marketing_links_social_post` FOREIGN KEY (`social_post_id`) REFERENCES `social_posts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `marketing_links` (`id`, `company_id`, `campaign_id`, `social_post_id`, `token`, `label`, `destination_type`, `destination_url`, `default_source`, `click_count`, `lead_count`, `status`, `created_at`, `updated_at`) VALUES ('1', '4', NULL, NULL, '58d9d87df9997b6a0c661998f6333ec2', 'A track link test', 'lead_form', NULL, 'campaign', '4', '1', 'active', '2026-07-16 01:22:08', '2026-07-16 01:51:27');

DROP TABLE IF EXISTS `marketing_media_assets`;
CREATE TABLE `marketing_media_assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `media_type` enum('image','video','reel','link') NOT NULL DEFAULT 'image',
  `media_url` text NOT NULL,
  `local_path` varchar(255) DEFAULT NULL,
  `download_status` enum('remote','downloaded','failed') NOT NULL DEFAULT 'remote',
  `download_attempts` int(10) unsigned NOT NULL DEFAULT 0,
  `last_download_error` text DEFAULT NULL,
  `source` varchar(120) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `quality_status` enum('ready','warning','blocked') NOT NULL DEFAULT 'warning',
  `quality_notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_media_assets_product` (`product_id`),
  KEY `idx_media_assets_company` (`company_id`,`media_type`),
  CONSTRAINT `fk_media_assets_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_media_assets_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('29', '4', NULL, 'pixels', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'downloaded', '0', NULL, 'Local upload', 'local-upload local-upload', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-01 01:51:37', NULL);
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('32', '4', NULL, 'free-stock-video/going-down-a-curved', 'video', 'https://mixkit.co/free-stock-video/going-down-a-curved-highway-through-a-mountain-range-41576/', 'uploads/marketing-media/4/20260701100422-58648f0d8091.mp4', 'downloaded', '0', NULL, NULL, 'queued-download', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-01 02:04:08', '2026-07-01 02:04:26');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('33', '4', NULL, 'Man Working On His Laptop 308', 'video', 'https://mixkit.co/free-stock-video/man-working-on-his-laptop-308/', 'uploads/marketing-media/4/20260702230231-7b5530115de5.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:02:19', '2026-07-02 15:02:38');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('34', '4', NULL, 'Business Man Exposing Graphs 42648', 'video', 'https://mixkit.co/free-stock-video/business-man-exposing-graphs-42648/', 'uploads/marketing-media/4/20260702230239-3818de2690df.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:02:19', '2026-07-02 15:02:43');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('35', '4', NULL, 'Open Office Space 914', 'video', 'https://mixkit.co/free-stock-video/open-office-space-914/', 'uploads/marketing-media/4/20260702230244-3492e7483446.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:02:19', '2026-07-02 15:02:48');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('36', '4', NULL, 'Person On Social Media While Serving Coffee 4919', 'video', 'https://mixkit.co/free-stock-video/person-on-social-media-while-serving-coffee-4919/', 'uploads/marketing-media/4/20260702230249-9ca184d96634.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:02:19', '2026-07-02 15:02:53');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('37', '4', NULL, 'Blonde Woman Is Sitting At Table In Big Mall Drink L328ZEE', 'video', 'https://elements.envato.com/blonde-woman-is-sitting-at-table-in-big-mall-drink-L328ZEE', 'uploads/marketing-media/4/20260702230603-00fb05c7a200.mp4', 'downloaded', '0', NULL, 'https://elements-resized.envatousercontent.com1', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:05:52', '2026-07-02 15:06:03');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('38', '4', NULL, '1012 720', 'video', 'https://assets.mixkit.co/videos/1012/1012-720.mp4', 'uploads/marketing-media/4/20260702230604-0c6ecd10a2fa.mp4', 'downloaded', '0', NULL, 'https://elements-resized.envatousercontent.com1', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:05:52', '2026-07-02 15:06:08');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('41', '4', NULL, 'Street With People Walking At Dusk 3428', 'video', 'https://mixkit.co/free-stock-video/street-with-people-walking-at-dusk-3428/', 'uploads/marketing-media/4/20260702230727-9b2628487824.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:07:22', '2026-07-02 15:07:32');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('42', '4', NULL, 'Children Playing With A Dancing Fountain 3469', 'video', 'https://mixkit.co/free-stock-video/children-playing-with-a-dancing-fountain-3469/', 'uploads/marketing-media/4/20260702230733-36242421ec6d.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:07:22', '2026-07-02 15:07:38');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('43', '4', NULL, 'Subway Entrance In A Pedestrian Zone 4154', 'video', 'https://mixkit.co/free-stock-video/subway-entrance-in-a-pedestrian-zone-4154/', 'uploads/marketing-media/4/20260702230739-43019d2ef1a0.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:07:22', '2026-07-02 15:07:44');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('44', '4', NULL, 'Person Typing On A Computer In Detail 4907', 'video', 'https://mixkit.co/free-stock-video/person-typing-on-a-computer-in-detail-4907/', 'uploads/marketing-media/4/20260702230744-da94609c446b.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:07:22', '2026-07-02 15:07:49');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('45', '4', NULL, 'Dashboard Of A Car 72', 'video', 'https://mixkit.co/free-stock-video/dashboard-of-a-car-72/', 'uploads/marketing-media/4/20260702230749-e56ffbf2794e.mp4', 'downloaded', '0', NULL, 'https://mixkit.co', 'bulk-queued', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-02 15:07:22', '2026-07-02 15:07:54');
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('46', '4', NULL, 'AI generated - Web developement company, that need more sales', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'downloaded', '0', NULL, 'AI Generator', 'ai-generated Funny', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-05 04:44:30', NULL);
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('47', '4', NULL, 'AI generated - Act like a senior education marketing strategist, website co', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg', 'uploads/marketing-media/4/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg', 'downloaded', '0', NULL, 'AI Generator', 'ai-generated Professional', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-05 05:04:13', NULL);
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('48', '4', NULL, 'AI generated - a post to help sell/market SchoolPixels School management sy', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260715094209-a-post-to-help-sell-market-schoolpixels-school-m-505bcf10.jpg', 'uploads/marketing-media/4/20260715094209-a-post-to-help-sell-market-schoolpixels-school-m-505bcf10.jpg', 'downloaded', '0', NULL, 'AI Generator', 'ai-generated Professional', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-15 01:42:09', NULL);
INSERT INTO `marketing_media_assets` (`id`, `company_id`, `product_id`, `title`, `media_type`, `media_url`, `local_path`, `download_status`, `download_attempts`, `last_download_error`, `source`, `tags`, `quality_status`, `quality_notes`, `created_at`, `updated_at`) VALUES ('49', '4', NULL, 'AI generated - create a professional post to help sell SchoolPixels Managem', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg', 'uploads/marketing-media/4/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg', 'downloaded', '0', NULL, 'AI Generator', 'ai-generated Professional', 'ready', 'Downloaded and stored locally for previews and posting.', '2026-07-15 01:53:59', NULL);

DROP TABLE IF EXISTS `payment_gateway_accounts`;
CREATE TABLE `payment_gateway_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `provider` varchar(60) NOT NULL,
  `display_name` varchar(120) NOT NULL,
  `merchant_identifier` varchar(160) DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_payment_gateway_accounts_company` (`company_id`,`status`,`sort_order`),
  CONSTRAINT `fk_payment_gateway_accounts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payment_gateway_accounts` (`id`, `company_id`, `provider`, `display_name`, `merchant_identifier`, `instructions`, `status`, `sort_order`, `created_at`, `updated_at`) VALUES ('1', '4', 'dpo', 'VISA', 'bizquotepro', 'send the proof of payment', 'active', '0', '2026-07-16 00:12:37', '2026-07-16 00:13:07');

DROP TABLE IF EXISTS `payment_proofs`;
CREATE TABLE `payment_proofs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `client_name` varchar(160) NOT NULL,
  `client_email` varchar(190) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reference` varchar(160) DEFAULT NULL,
  `method` enum('bank_transfer','cash','card','mobile_money','manual') NOT NULL DEFAULT 'bank_transfer',
  `bank_branch_name` varchar(160) DEFAULT NULL,
  `bank_location` varchar(160) DEFAULT NULL,
  `bank_account_number` varchar(80) DEFAULT NULL,
  `mobile_money_provider` varchar(40) DEFAULT NULL,
  `mobile_money_number` varchar(80) DEFAULT NULL,
  `proof_path` varchar(255) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `status` enum('pending','matched','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_payment_proofs_company_status` (`company_id`,`status`,`created_at`),
  KEY `idx_payment_proofs_invoice` (`invoice_id`,`status`),
  CONSTRAINT `fk_payment_proofs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payment_proofs_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payment_proofs` (`id`, `company_id`, `invoice_id`, `client_name`, `client_email`, `amount`, `reference`, `method`, `bank_branch_name`, `bank_location`, `bank_account_number`, `mobile_money_provider`, `mobile_money_number`, `proof_path`, `note`, `status`, `reviewed_at`, `created_at`, `updated_at`) VALUES ('1', '4', '3', 'Lebo M.', 'accounts@delta.test', '10500.00', 'BANK-PENDING-447', 'bank_transfer', NULL, NULL, NULL, NULL, NULL, NULL, 'Paid against design approval milestone.', 'pending', NULL, '2026-06-29 12:05:21', NULL);
INSERT INTO `payment_proofs` (`id`, `company_id`, `invoice_id`, `client_name`, `client_email`, `amount`, `reference`, `method`, `bank_branch_name`, `bank_location`, `bank_account_number`, `mobile_money_provider`, `mobile_money_number`, `proof_path`, `note`, `status`, `reviewed_at`, `created_at`, `updated_at`) VALUES ('2', '4', '5', 'Comp', 'gadgetsfund@gmail.com', '21090.00', NULL, 'cash', NULL, NULL, NULL, NULL, NULL, 'C:\\xampp\\htdocs\\bizquote-pro/storage/payment-proofs/20260710163653-556d738ab6fc.pdf', 'paid cash', 'matched', '2026-07-10 08:37:25', '2026-07-10 08:36:53', '2026-07-10 08:37:25');

DROP TABLE IF EXISTS `payment_webhook_events`;
CREATE TABLE `payment_webhook_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `provider` varchar(60) NOT NULL,
  `external_id` varchar(190) DEFAULT NULL,
  `signature_valid` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `error_message` varchar(255) DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_webhook_provider_external` (`provider`,`external_id`),
  KEY `fk_payment_webhook_events_company` (`company_id`),
  KEY `idx_payment_webhook_events_status` (`status`,`created_at`),
  CONSTRAINT `fk_payment_webhook_events_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `payment_proof_id` bigint(20) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `method` enum('cash','bank_transfer','card','mobile_money','dpo','paypal','manual') NOT NULL DEFAULT 'manual',
  `reference` varchar(160) DEFAULT NULL,
  `bank_branch_name` varchar(160) DEFAULT NULL,
  `bank_location` varchar(160) DEFAULT NULL,
  `bank_account_number` varchar(80) DEFAULT NULL,
  `mobile_money_provider` varchar(40) DEFAULT NULL,
  `mobile_money_number` varchar(80) DEFAULT NULL,
  `gateway` varchar(60) DEFAULT NULL,
  `gateway_payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`gateway_payload`)),
  `status` enum('pending','successful','failed','refunded') NOT NULL DEFAULT 'successful',
  `paid_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_payments_company_paid` (`company_id`,`paid_at`),
  KEY `idx_payments_invoice` (`invoice_id`),
  CONSTRAINT `fk_payments_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payments_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payments` (`id`, `company_id`, `invoice_id`, `payment_proof_id`, `amount`, `method`, `reference`, `bank_branch_name`, `bank_location`, `bank_account_number`, `mobile_money_provider`, `mobile_money_number`, `gateway`, `gateway_payload`, `status`, `paid_at`, `created_at`) VALUES ('2', '4', '3', NULL, '9000.00', 'bank_transfer', 'DEMO-EFT-001', NULL, NULL, NULL, NULL, NULL, 'manual', NULL, 'successful', '2026-06-21 15:56:30', '2026-06-29 07:56:30');
INSERT INTO `payments` (`id`, `company_id`, `invoice_id`, `payment_proof_id`, `amount`, `method`, `reference`, `bank_branch_name`, `bank_location`, `bank_account_number`, `mobile_money_provider`, `mobile_money_number`, `gateway`, `gateway_payload`, `status`, `paid_at`, `created_at`) VALUES ('3', '4', '5', '2', '21090.00', 'cash', NULL, NULL, NULL, NULL, NULL, NULL, 'proof', NULL, 'successful', '2026-07-10 16:37:25', '2026-07-10 08:37:25');

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(180) NOT NULL,
  `sku` varchar(80) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_products_company_name` (`company_id`,`name`),
  KEY `idx_products_sku` (`company_id`,`sku`),
  CONSTRAINT `fk_products_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `products` (`id`, `company_id`, `name`, `sku`, `category`, `description`, `image_path`, `unit_price`, `cost_price`, `tax_rate`, `status`, `created_at`, `updated_at`) VALUES ('9', '4', 'Business Consulting Retainer', 'CONS-001', 'Services', 'Monthly strategy and operational support', NULL, '8500.00', '2500.00', '14.00', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `products` (`id`, `company_id`, `name`, `sku`, `category`, `description`, `image_path`, `unit_price`, `cost_price`, `tax_rate`, `status`, `created_at`, `updated_at`) VALUES ('10', '4', 'Website Build Package', 'WEB-001', 'Digital', 'Responsive website design and implementation', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', '18500.00', '7000.00', '14.00', 'active', '2026-06-29 07:56:30', '2026-07-04 12:24:10');
INSERT INTO `products` (`id`, `company_id`, `name`, `sku`, `category`, `description`, `image_path`, `unit_price`, `cost_price`, `tax_rate`, `status`, `created_at`, `updated_at`) VALUES ('11', '4', 'Support & Maintenance', 'MAINT-001', 'Services', 'Monthly technical support package', NULL, '3200.00', '900.00', '14.00', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `products` (`id`, `company_id`, `name`, `sku`, `category`, `description`, `image_path`, `unit_price`, `cost_price`, `tax_rate`, `status`, `created_at`, `updated_at`) VALUES ('12', '4', 'Brand Identity Kit', 'BRAND-001', 'Creative', 'Logo, typography, colors and stationery design', NULL, '7800.00', '2200.00', '14.00', 'active', '2026-06-29 07:56:30', '2026-07-16 01:36:42');

DROP TABLE IF EXISTS `project_costs`;
CREATE TABLE `project_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `vendor_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL DEFAULT 'Materials',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost_date` date NOT NULL,
  `billable` tinyint(1) NOT NULL DEFAULT 0,
  `receipt_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_project_costs_company` (`company_id`),
  KEY `fk_project_costs_vendor` (`vendor_id`),
  KEY `idx_project_costs_project_date` (`project_id`,`cost_date`),
  CONSTRAINT `fk_project_costs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_project_costs_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_project_costs_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `project_tasks`;
CREATE TABLE `project_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) unsigned NOT NULL,
  `assigned_user_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('todo','in_progress','blocked','completed') NOT NULL DEFAULT 'todo',
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `due_date` date DEFAULT NULL,
  `estimated_minutes` int(10) unsigned NOT NULL DEFAULT 0,
  `billable` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_project_tasks_user` (`assigned_user_id`),
  KEY `idx_project_tasks_project_status` (`project_id`,`status`,`due_date`),
  CONSTRAINT `fk_project_tasks_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_project_tasks_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `project_tasks` (`id`, `project_id`, `assigned_user_id`, `title`, `description`, `status`, `priority`, `due_date`, `estimated_minutes`, `billable`, `sort_order`, `completed_at`, `created_at`, `updated_at`) VALUES ('1', '1', '23', 'projects chekcing', 'checking the projects', 'completed', 'normal', '2026-07-17', '30', '1', '0', '2026-07-16 09:33:57', '2026-07-16 01:25:52', '2026-07-16 01:33:57');

DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `deal_id` bigint(20) unsigned DEFAULT NULL,
  `quotation_id` bigint(20) unsigned DEFAULT NULL,
  `manager_user_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('planned','in_progress','on_hold','completed','cancelled') NOT NULL DEFAULT 'planned',
  `billing_type` enum('fixed','hourly','milestone') NOT NULL DEFAULT 'fixed',
  `budget` decimal(12,2) NOT NULL DEFAULT 0.00,
  `start_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_projects_client` (`client_id`),
  KEY `fk_projects_deal` (`deal_id`),
  KEY `fk_projects_quote` (`quotation_id`),
  KEY `fk_projects_manager` (`manager_user_id`),
  KEY `idx_projects_company_status` (`company_id`,`status`),
  KEY `idx_projects_due` (`company_id`,`due_date`),
  CONSTRAINT `fk_projects_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_projects_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_projects_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_projects_manager` FOREIGN KEY (`manager_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_projects_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `projects` (`id`, `company_id`, `client_id`, `deal_id`, `quotation_id`, `manager_user_id`, `name`, `description`, `status`, `billing_type`, `budget`, `start_date`, `due_date`, `completed_at`, `created_at`, `updated_at`) VALUES ('1', '4', '10', NULL, NULL, '4', 'Schools', 'School work', 'completed', 'fixed', '5642.05', '2026-07-16', '2026-07-17', '2026-07-16 09:36:03', '2026-07-16 00:52:38', '2026-07-16 01:36:03');

DROP TABLE IF EXISTS `proposal_templates`;
CREATE TABLE `proposal_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(160) NOT NULL,
  `title` varchar(180) NOT NULL DEFAULT 'Proposal',
  `introduction` text DEFAULT NULL,
  `scope_template` text DEFAULT NULL,
  `terms_template` text DEFAULT NULL,
  `acceptance_text` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_proposal_templates_company` (`company_id`,`status`),
  CONSTRAINT `fk_proposal_templates_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('1', '1', 'Professional Proposal', 'Business Proposal', 'Thank you for the opportunity to present this proposal. The recommended solution is designed around your stated goals and the outcomes discussed.', 'The scope includes the products, services, deliverables, and package options listed in this proposal.', 'Work begins after acceptance and any required deposit. Changes outside the agreed scope may require a revised quotation.', 'By signing, the client confirms authority to accept this proposal and agrees to its scope, pricing, and terms.', 'active', '2026-07-10 11:29:51', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('2', '4', 'Professional Proposal', 'Business Proposal', 'Thank you for the opportunity to present this proposal. The recommended solution is designed around your stated goals and the outcomes discussed.', 'The scope includes the products, services, deliverables, and package options listed in this proposal.', 'Work begins after acceptance and any required deposit. Changes outside the agreed scope may require a revised quotation.', 'By signing, the client confirms authority to accept this proposal and agrees to its scope, pricing, and terms.', 'active', '2026-07-10 11:29:51', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('4', '1', 'Professional Services Proposal', 'Professional Services Proposal', 'Thank you for the opportunity to support your organisation. This proposal presents a practical, outcome-focused engagement designed to solve the priorities discussed, create measurable value, and give both teams a clear path from approval to delivery.', 'ENGAGEMENT OBJECTIVES\n• Confirm priorities, success measures, stakeholders, and constraints.\n• Deliver the professional services and outputs listed in this quotation.\n• Maintain clear communication, progress reporting, and decision tracking.\n\nDELIVERY APPROACH\n1. Kickoff and discovery\n2. Planning and confirmation of deliverables\n3. Execution with scheduled progress updates\n4. Review, handover, and closeout\n\nCLIENT RESPONSIBILITIES\nThe client will provide timely access to information, systems, decision-makers, approvals, and feedback required to keep the engagement on schedule.\n\nOUT OF SCOPE\nAny work not expressly listed in this proposal will be assessed separately and may require a written change request.', 'Validity: This proposal is valid until the quotation expiry date.\nCommencement: Work starts after written acceptance and receipt of any required deposit.\nPayment: Invoices are payable according to the payment schedule shown in the quotation.\nChanges: Scope, timing, or deliverable changes require written agreement and may affect fees.\nDelays: Client-caused delays may move delivery dates and require rescheduling.\nConfidentiality: Both parties will protect confidential business information shared during the engagement.\nIntellectual property: Final paid deliverables transfer to the client; pre-existing tools, methods, and reusable know-how remain with their original owner.\nCancellation: Completed work and committed third-party costs remain payable.', 'By signing, the client confirms authority to approve this engagement and accepts its scope, pricing, responsibilities, payment schedule, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('5', '1', 'Website & Software Project', 'Digital Solution Project Proposal', 'This proposal outlines a structured plan to design and deliver a reliable digital solution that supports your business goals, serves its users well, and can be maintained after launch. The approach balances speed, quality, security, and clear approval points.', 'PROJECT OUTCOMES\n• A production-ready digital solution aligned with the approved requirements.\n• Responsive, accessible interfaces for the agreed devices and browsers.\n• Tested workflows, configured deployment, and handover documentation.\n\nPROJECT PHASES\n1. Discovery: requirements, users, content, integrations, and success criteria.\n2. Experience design: information architecture, wireframes, and visual direction.\n3. Build: implementation of approved features and integrations.\n4. Quality assurance: functional, responsive, security, and acceptance testing.\n5. Launch: deployment, training, documentation, and post-launch observation.\n\nASSUMPTIONS\nThe client supplies approved content, brand assets, system access, and consolidated feedback. One authorised representative provides final approvals.\n\nEXCLUSIONS\nHosting, licences, paid media, third-party subscriptions, content production, data cleansing, and features not itemised are excluded unless expressly included.', 'Validity: Pricing remains valid until the quotation expiry date.\nDeposit and milestones: Work is scheduled after the required deposit; milestone invoices are due before the next phase begins.\nRevisions: Each design phase includes reasonable consolidated revisions; new directions or approved-scope changes are chargeable.\nAcceptance testing: The client will test within five business days of delivery. Unreported material defects after that period are treated as accepted, without limiting any stated warranty.\nWarranty: Included work carries a 30-day defect-correction period after launch. New features and third-party changes are excluded.\nContent and access: Delivery dates depend on timely content, credentials, decisions, and feedback.\nOwnership: Final paid custom deliverables transfer to the client. Open-source components, licensed materials, and pre-existing tools retain their original licences.\nSecurity: Both parties will protect credentials and promptly report suspected security incidents.', 'By signing, the client approves the proposed digital project, authorises commencement under the stated milestones, and accepts the scope, exclusions, responsibilities, pricing, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('6', '1', 'Monthly Retainer & Managed Services', 'Managed Services Retainer Proposal', 'This proposal establishes an ongoing service partnership that provides predictable access to expertise, proactive support, and transparent monthly priorities. The retainer is designed to improve continuity while keeping work visible and commercially controlled.', 'SERVICE COVERAGE\n• Recurring services and deliverables listed in the quotation.\n• Monthly planning and prioritisation with an agreed point of contact.\n• Progress updates covering work completed, next actions, risks, and decisions.\n• Reasonable support during the service hours stated in the quotation.\n\nMONTHLY OPERATING RHYTHM\n1. Confirm priorities and available capacity.\n2. Execute approved work in priority order.\n3. Review results, open items, and recommendations.\n4. Agree the following month’s focus.\n\nCAPACITY AND SCOPE\nThe retainer reserves the stated monthly capacity. Work beyond that capacity, urgent out-of-hours requests, travel, and third-party costs require separate approval. Unused capacity does not roll over unless stated otherwise.', 'Term: The service runs monthly from the agreed start date.\nBilling: Retainer fees are invoiced in advance and are payable before the service month begins.\nPriorities: The client may reprioritise work within the agreed scope and available capacity.\nResponse times: Response targets are service objectives, not guaranteed resolution times, unless a separate service-level agreement is included.\nUnused capacity: Reserved capacity expires at month-end unless the quotation states a rollover allowance.\nPause or cancellation: Either party may end the retainer with 30 days written notice. Fees already committed for the notice period remain payable.\nThird parties: Platforms, advertising spend, licences, hosting, couriers, and other external charges are billed separately with approval.\nConfidentiality and ownership: Confidential information is protected; final paid deliverables belong to the client subject to third-party licences and pre-existing tools.', 'By signing, the client authorises the recurring managed service and accepts the monthly capacity, prioritisation process, fees, notice period, responsibilities, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('7', '1', 'Marketing Campaign & Content', 'Marketing Growth Campaign Proposal', 'This proposal presents a focused marketing engagement built to turn clear commercial goals into consistent campaigns, useful content, and measurable learning. Activity will be planned around the audience, offer, channels, and budget approved by the client.', 'CAMPAIGN FOUNDATION\n• Confirm the business objective, target audience, offer, message, channels, and conversion action.\n• Establish the approved campaign calendar and measurement plan.\n\nDELIVERABLES\n• Strategy, creative, content, and campaign services itemised in the quotation.\n• Drafts submitted for approval before scheduled publication or launch.\n• Performance review covering agreed indicators, insights, and recommended next actions.\n\nAPPROVAL WORKFLOW\nThe client will provide brand assets, accurate product information, access to required platforms, and one consolidated approval response. Publication dates depend on timely approval.\n\nEXCLUSIONS\nAdvertising spend, influencers, printing, photography, video production, stock assets, platform subscriptions, and community responses are excluded unless specifically itemised.', 'Campaign period: Services run for the dates or milestones stated in the quotation.\nPayment: Strategy and production begin after the required deposit or advance invoice is paid. Media and third-party costs must be funded before placement.\nApprovals: The client is responsible for factual, legal, pricing, and offer approval. Approved content may be scheduled or published.\nRevisions: Reasonable revisions within the approved direction are included; new concepts, reshoots, or scope changes are chargeable.\nPerformance: Marketing results depend on market conditions, budget, offer, platform behaviour, and client operations; no specific sales outcome is guaranteed.\nPlatform risk: Third-party platforms may change policies, access, algorithms, or availability without notice.\nUsage rights: Final paid creative is licensed or transferred as stated; third-party assets remain subject to their licences.\nCancellation: Completed work, booked production, and committed media or supplier costs remain payable.', 'By signing, the client approves the campaign direction and authorises delivery under the stated approval process, budget, scope, measurement approach, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('8', '1', 'Product Supply & Installation', 'Supply, Delivery & Installation Proposal', 'Thank you for the opportunity to supply this solution. This proposal sets out the products, delivery responsibilities, installation activities, and acceptance process required for an orderly handover and reliable commercial outcome.', 'SUPPLY\n• Products, quantities, specifications, and accessories listed in the quotation.\n• Standard manufacturer documentation and warranty information where applicable.\n\nDELIVERY AND INSTALLATION\n• Delivery to the agreed site during normal working hours.\n• Installation, configuration, testing, or commissioning only where itemised.\n• Basic user handover for the installed solution where included.\n\nSITE REQUIREMENTS\nThe client will provide safe access, an authorised site contact, suitable power/connectivity, required permits, clear working areas, and any building work not expressly included.\n\nACCEPTANCE\nProducts will be checked against the delivery note. Installed work will be demonstrated where applicable, and material issues must be recorded at handover.\n\nEXCLUSIONS\nCivil works, electrical alterations, specialist lifting, after-hours access, storage caused by site delays, and consumables are excluded unless itemised.', 'Validity and availability: Prices are valid until expiry and remain subject to supplier availability and exchange-rate changes before deposit.\nDeposit: Procurement begins after receipt of the required deposit and cleared funds.\nDelivery estimates: Lead times are estimates and may be affected by suppliers, customs, carriers, weather, and site readiness.\nRisk and title: Risk passes on delivery; ownership passes after full payment, to the extent permitted by law.\nInspection: Visible shortages or damage must be noted on delivery.\nWarranty: Manufacturer warranties apply to eligible products. Workmanship defects must be reported within 30 days. Misuse, unauthorised changes, consumables, and external faults are excluded.\nSite delays: Failed delivery, unsafe conditions, or an unready site may result in storage, return, travel, and rescheduling charges.\nReturns: Custom, configured, opened, or specially ordered goods are non-returnable unless defective or agreed in writing.', 'By signing, the client confirms the specifications and site requirements, authorises procurement, and accepts the pricing, delivery assumptions, installation scope, warranty, payment terms, and exclusions.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('9', '1', 'Consulting & Discovery Engagement', 'Consulting & Discovery Proposal', 'This engagement is designed to replace assumptions with evidence and give decision-makers a clear, prioritised path forward. The work combines stakeholder input, structured analysis, and practical recommendations that can guide investment and execution.', 'ENGAGEMENT QUESTIONS\nThe discovery will investigate the business challenge, current state, stakeholder needs, constraints, risks, and viable improvement opportunities relevant to the quoted engagement.\n\nMETHOD\n• Kickoff and confirmation of objectives and stakeholders.\n• Review of available documents, data, systems, and existing processes.\n• Interviews, workshops, or observations included in the quotation.\n• Analysis of findings, options, dependencies, and risks.\n• Presentation of recommendations and an actionable roadmap.\n\nDELIVERABLES\nThe client receives the reports, workshop outputs, recommendations, or roadmap specifically listed in the quotation.\n\nBOUNDARIES\nRecommendations are based on information available during the engagement. Legal, tax, audit, engineering certification, and implementation services are excluded unless expressly stated.', 'Scheduling: Sessions are arranged with reasonable notice and depend on stakeholder availability.\nPayment: Fees follow the deposit or milestone schedule in the quotation.\nInformation quality: The client is responsible for providing complete and accurate information; conclusions may change if material facts are withheld or later corrected.\nRescheduling: Sessions moved with less than two business days notice may be treated as delivered or charged for committed time and travel.\nRecommendations: Advice reflects professional judgement using the agreed scope and information available; business outcomes remain the client’s responsibility.\nConfidentiality: Interview notes, internal information, and findings will be handled confidentially and shared only with authorised participants.\nOwnership: Final paid reports belong to the client for internal use. Underlying frameworks, methods, templates, and general know-how remain with their original owner.\nAdditional work: Implementation, extended analysis, or new questions require separate written approval.', 'By signing, the client authorises the discovery engagement, confirms access to the required stakeholders and information, and accepts the deliverables, limitations, fees, schedule, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('10', '4', 'Professional Services Proposal', 'Professional Services Proposal', 'Thank you for the opportunity to support your organisation. This proposal presents a practical, outcome-focused engagement designed to solve the priorities discussed, create measurable value, and give both teams a clear path from approval to delivery.', 'ENGAGEMENT OBJECTIVES\n• Confirm priorities, success measures, stakeholders, and constraints.\n• Deliver the professional services and outputs listed in this quotation.\n• Maintain clear communication, progress reporting, and decision tracking.\n\nDELIVERY APPROACH\n1. Kickoff and discovery\n2. Planning and confirmation of deliverables\n3. Execution with scheduled progress updates\n4. Review, handover, and closeout\n\nCLIENT RESPONSIBILITIES\nThe client will provide timely access to information, systems, decision-makers, approvals, and feedback required to keep the engagement on schedule.\n\nOUT OF SCOPE\nAny work not expressly listed in this proposal will be assessed separately and may require a written change request.', 'Validity: This proposal is valid until the quotation expiry date.\nCommencement: Work starts after written acceptance and receipt of any required deposit.\nPayment: Invoices are payable according to the payment schedule shown in the quotation.\nChanges: Scope, timing, or deliverable changes require written agreement and may affect fees.\nDelays: Client-caused delays may move delivery dates and require rescheduling.\nConfidentiality: Both parties will protect confidential business information shared during the engagement.\nIntellectual property: Final paid deliverables transfer to the client; pre-existing tools, methods, and reusable know-how remain with their original owner.\nCancellation: Completed work and committed third-party costs remain payable.', 'By signing, the client confirms authority to approve this engagement and accepts its scope, pricing, responsibilities, payment schedule, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('11', '4', 'Website & Software Project', 'Digital Solution Project Proposal', 'This proposal outlines a structured plan to design and deliver a reliable digital solution that supports your business goals, serves its users well, and can be maintained after launch. The approach balances speed, quality, security, and clear approval points.', 'PROJECT OUTCOMES\n• A production-ready digital solution aligned with the approved requirements.\n• Responsive, accessible interfaces for the agreed devices and browsers.\n• Tested workflows, configured deployment, and handover documentation.\n\nPROJECT PHASES\n1. Discovery: requirements, users, content, integrations, and success criteria.\n2. Experience design: information architecture, wireframes, and visual direction.\n3. Build: implementation of approved features and integrations.\n4. Quality assurance: functional, responsive, security, and acceptance testing.\n5. Launch: deployment, training, documentation, and post-launch observation.\n\nASSUMPTIONS\nThe client supplies approved content, brand assets, system access, and consolidated feedback. One authorised representative provides final approvals.\n\nEXCLUSIONS\nHosting, licences, paid media, third-party subscriptions, content production, data cleansing, and features not itemised are excluded unless expressly included.', 'Validity: Pricing remains valid until the quotation expiry date.\nDeposit and milestones: Work is scheduled after the required deposit; milestone invoices are due before the next phase begins.\nRevisions: Each design phase includes reasonable consolidated revisions; new directions or approved-scope changes are chargeable.\nAcceptance testing: The client will test within five business days of delivery. Unreported material defects after that period are treated as accepted, without limiting any stated warranty.\nWarranty: Included work carries a 30-day defect-correction period after launch. New features and third-party changes are excluded.\nContent and access: Delivery dates depend on timely content, credentials, decisions, and feedback.\nOwnership: Final paid custom deliverables transfer to the client. Open-source components, licensed materials, and pre-existing tools retain their original licences.\nSecurity: Both parties will protect credentials and promptly report suspected security incidents.', 'By signing, the client approves the proposed digital project, authorises commencement under the stated milestones, and accepts the scope, exclusions, responsibilities, pricing, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('12', '4', 'Monthly Retainer & Managed Services', 'Managed Services Retainer Proposal', 'This proposal establishes an ongoing service partnership that provides predictable access to expertise, proactive support, and transparent monthly priorities. The retainer is designed to improve continuity while keeping work visible and commercially controlled.', 'SERVICE COVERAGE\n• Recurring services and deliverables listed in the quotation.\n• Monthly planning and prioritisation with an agreed point of contact.\n• Progress updates covering work completed, next actions, risks, and decisions.\n• Reasonable support during the service hours stated in the quotation.\n\nMONTHLY OPERATING RHYTHM\n1. Confirm priorities and available capacity.\n2. Execute approved work in priority order.\n3. Review results, open items, and recommendations.\n4. Agree the following month’s focus.\n\nCAPACITY AND SCOPE\nThe retainer reserves the stated monthly capacity. Work beyond that capacity, urgent out-of-hours requests, travel, and third-party costs require separate approval. Unused capacity does not roll over unless stated otherwise.', 'Term: The service runs monthly from the agreed start date.\nBilling: Retainer fees are invoiced in advance and are payable before the service month begins.\nPriorities: The client may reprioritise work within the agreed scope and available capacity.\nResponse times: Response targets are service objectives, not guaranteed resolution times, unless a separate service-level agreement is included.\nUnused capacity: Reserved capacity expires at month-end unless the quotation states a rollover allowance.\nPause or cancellation: Either party may end the retainer with 30 days written notice. Fees already committed for the notice period remain payable.\nThird parties: Platforms, advertising spend, licences, hosting, couriers, and other external charges are billed separately with approval.\nConfidentiality and ownership: Confidential information is protected; final paid deliverables belong to the client subject to third-party licences and pre-existing tools.', 'By signing, the client authorises the recurring managed service and accepts the monthly capacity, prioritisation process, fees, notice period, responsibilities, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('13', '4', 'Marketing Campaign & Content', 'Marketing Growth Campaign Proposal', 'This proposal presents a focused marketing engagement built to turn clear commercial goals into consistent campaigns, useful content, and measurable learning. Activity will be planned around the audience, offer, channels, and budget approved by the client.', 'CAMPAIGN FOUNDATION\n• Confirm the business objective, target audience, offer, message, channels, and conversion action.\n• Establish the approved campaign calendar and measurement plan.\n\nDELIVERABLES\n• Strategy, creative, content, and campaign services itemised in the quotation.\n• Drafts submitted for approval before scheduled publication or launch.\n• Performance review covering agreed indicators, insights, and recommended next actions.\n\nAPPROVAL WORKFLOW\nThe client will provide brand assets, accurate product information, access to required platforms, and one consolidated approval response. Publication dates depend on timely approval.\n\nEXCLUSIONS\nAdvertising spend, influencers, printing, photography, video production, stock assets, platform subscriptions, and community responses are excluded unless specifically itemised.', 'Campaign period: Services run for the dates or milestones stated in the quotation.\nPayment: Strategy and production begin after the required deposit or advance invoice is paid. Media and third-party costs must be funded before placement.\nApprovals: The client is responsible for factual, legal, pricing, and offer approval. Approved content may be scheduled or published.\nRevisions: Reasonable revisions within the approved direction are included; new concepts, reshoots, or scope changes are chargeable.\nPerformance: Marketing results depend on market conditions, budget, offer, platform behaviour, and client operations; no specific sales outcome is guaranteed.\nPlatform risk: Third-party platforms may change policies, access, algorithms, or availability without notice.\nUsage rights: Final paid creative is licensed or transferred as stated; third-party assets remain subject to their licences.\nCancellation: Completed work, booked production, and committed media or supplier costs remain payable.', 'By signing, the client approves the campaign direction and authorises delivery under the stated approval process, budget, scope, measurement approach, and terms.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('14', '4', 'Product Supply & Installation', 'Supply, Delivery & Installation Proposal', 'Thank you for the opportunity to supply this solution. This proposal sets out the products, delivery responsibilities, installation activities, and acceptance process required for an orderly handover and reliable commercial outcome.', 'SUPPLY\n• Products, quantities, specifications, and accessories listed in the quotation.\n• Standard manufacturer documentation and warranty information where applicable.\n\nDELIVERY AND INSTALLATION\n• Delivery to the agreed site during normal working hours.\n• Installation, configuration, testing, or commissioning only where itemised.\n• Basic user handover for the installed solution where included.\n\nSITE REQUIREMENTS\nThe client will provide safe access, an authorised site contact, suitable power/connectivity, required permits, clear working areas, and any building work not expressly included.\n\nACCEPTANCE\nProducts will be checked against the delivery note. Installed work will be demonstrated where applicable, and material issues must be recorded at handover.\n\nEXCLUSIONS\nCivil works, electrical alterations, specialist lifting, after-hours access, storage caused by site delays, and consumables are excluded unless itemised.', 'Validity and availability: Prices are valid until expiry and remain subject to supplier availability and exchange-rate changes before deposit.\nDeposit: Procurement begins after receipt of the required deposit and cleared funds.\nDelivery estimates: Lead times are estimates and may be affected by suppliers, customs, carriers, weather, and site readiness.\nRisk and title: Risk passes on delivery; ownership passes after full payment, to the extent permitted by law.\nInspection: Visible shortages or damage must be noted on delivery.\nWarranty: Manufacturer warranties apply to eligible products. Workmanship defects must be reported within 30 days. Misuse, unauthorised changes, consumables, and external faults are excluded.\nSite delays: Failed delivery, unsafe conditions, or an unready site may result in storage, return, travel, and rescheduling charges.\nReturns: Custom, configured, opened, or specially ordered goods are non-returnable unless defective or agreed in writing.', 'By signing, the client confirms the specifications and site requirements, authorises procurement, and accepts the pricing, delivery assumptions, installation scope, warranty, payment terms, and exclusions.', 'active', '2026-07-11 01:11:14', NULL);
INSERT INTO `proposal_templates` (`id`, `company_id`, `name`, `title`, `introduction`, `scope_template`, `terms_template`, `acceptance_text`, `status`, `created_at`, `updated_at`) VALUES ('15', '4', 'Consulting & Discovery Engagement', 'Consulting & Discovery Proposal', 'This engagement is designed to replace assumptions with evidence and give decision-makers a clear, prioritised path forward. The work combines stakeholder input, structured analysis, and practical recommendations that can guide investment and execution.', 'ENGAGEMENT QUESTIONS\nThe discovery will investigate the business challenge, current state, stakeholder needs, constraints, risks, and viable improvement opportunities relevant to the quoted engagement.\n\nMETHOD\n• Kickoff and confirmation of objectives and stakeholders.\n• Review of available documents, data, systems, and existing processes.\n• Interviews, workshops, or observations included in the quotation.\n• Analysis of findings, options, dependencies, and risks.\n• Presentation of recommendations and an actionable roadmap.\n\nDELIVERABLES\nThe client receives the reports, workshop outputs, recommendations, or roadmap specifically listed in the quotation.\n\nBOUNDARIES\nRecommendations are based on information available during the engagement. Legal, tax, audit, engineering certification, and implementation services are excluded unless expressly stated.', 'Scheduling: Sessions are arranged with reasonable notice and depend on stakeholder availability.\nPayment: Fees follow the deposit or milestone schedule in the quotation.\nInformation quality: The client is responsible for providing complete and accurate information; conclusions may change if material facts are withheld or later corrected.\nRescheduling: Sessions moved with less than two business days notice may be treated as delivered or charged for committed time and travel.\nRecommendations: Advice reflects professional judgement using the agreed scope and information available; business outcomes remain the client’s responsibility.\nConfidentiality: Interview notes, internal information, and findings will be handled confidentially and shared only with authorised participants.\nOwnership: Final paid reports belong to the client for internal use. Underlying frameworks, methods, templates, and general know-how remain with their original owner.\nAdditional work: Implementation, extended analysis, or new questions require separate written approval.', 'By signing, the client authorises the discovery engagement, confirms access to the required stakeholders and information, and accepts the deliverables, limitations, fees, schedule, and terms.', 'active', '2026-07-11 01:11:14', NULL);

DROP TABLE IF EXISTS `purchase_orders`;
CREATE TABLE `purchase_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `vendor_id` bigint(20) unsigned NOT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `order_number` varchar(60) NOT NULL,
  `order_date` date NOT NULL,
  `expected_date` date DEFAULT NULL,
  `status` enum('draft','ordered','part_received','received','cancelled') NOT NULL DEFAULT 'draft',
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_purchase_order_number` (`company_id`,`order_number`),
  KEY `fk_purchase_orders_vendor` (`vendor_id`),
  KEY `fk_purchase_orders_warehouse` (`warehouse_id`),
  CONSTRAINT `fk_purchase_orders_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_purchase_orders_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`),
  CONSTRAINT `fk_purchase_orders_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `inventory_warehouses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `quotation_acceptances`;
CREATE TABLE `quotation_acceptances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `selected_package_option_id` bigint(20) unsigned DEFAULT NULL,
  `signer_name` varchar(160) NOT NULL,
  `signer_email` varchar(190) DEFAULT NULL,
  `signature_text` varchar(190) NOT NULL,
  `accepted_terms` tinyint(1) NOT NULL DEFAULT 1,
  `ip_address` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_quote_acceptances_quote` (`quotation_id`,`created_at`),
  CONSTRAINT `fk_quote_acceptances_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quotation_acceptances` (`id`, `quotation_id`, `selected_package_option_id`, `signer_name`, `signer_email`, `signature_text`, `accepted_terms`, `ip_address`, `user_agent`, `created_at`) VALUES ('2', '4', NULL, 'Lebo M.', 'accounts@delta.test', 'Lebo M.', '1', '127.0.0.1', 'Demo Seeder', '2026-06-19 07:56:30');
INSERT INTO `quotation_acceptances` (`id`, `quotation_id`, `selected_package_option_id`, `signer_name`, `signer_email`, `signature_text`, `accepted_terms`, `ip_address`, `user_agent`, `created_at`) VALUES ('3', '7', NULL, 'Kalahari Solar', 'admin@kalaharisolar.test', 'dadd', '1', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36', '2026-06-29 09:59:00');

DROP TABLE IF EXISTS `quotation_comments`;
CREATE TABLE `quotation_comments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `author_type` enum('client','user') NOT NULL,
  `author_name` varchar(160) NOT NULL,
  `author_email` varchar(190) DEFAULT NULL,
  `comment` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_quote_comments_quote` (`quotation_id`,`created_at`),
  CONSTRAINT `fk_quote_comments_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quotation_comments` (`id`, `quotation_id`, `author_type`, `author_name`, `author_email`, `comment`, `created_at`) VALUES ('4', '4', 'client', 'Lebo M.', 'accounts@delta.test', 'Can we split the remaining balance across two milestones?', '2026-06-29 07:56:30');
INSERT INTO `quotation_comments` (`id`, `quotation_id`, `author_type`, `author_name`, `author_email`, `comment`, `created_at`) VALUES ('5', '4', 'user', 'Demo Owner', 'owner@bizquote.test', 'Yes, milestone billing is fine. We will reflect that on the invoice.', '2026-06-29 07:56:30');
INSERT INTO `quotation_comments` (`id`, `quotation_id`, `author_type`, `author_name`, `author_email`, `comment`, `created_at`) VALUES ('6', '5', 'client', 'Naledi Finance', 'finance@naledi.test', 'Please confirm if this includes phone support.', '2026-06-29 07:56:30');

DROP TABLE IF EXISTS `quotation_items`;
CREATE TABLE `quotation_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `description` text NOT NULL,
  `quantity` decimal(12,2) NOT NULL DEFAULT 1.00,
  `unit_price` 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,
  `line_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_quote_items_quote` (`quotation_id`),
  KEY `fk_quote_items_product` (`product_id`),
  CONSTRAINT `fk_quote_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_quote_items_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quotation_items` (`id`, `quotation_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('5', '4', '10', 'Website Build Package', '1.00', '18500.00', '14.00', '2590.00', '21090.00', '2026-06-29 07:56:30');
INSERT INTO `quotation_items` (`id`, `quotation_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('6', '4', '9', 'Business Consulting Retainer', '1.00', '8500.00', '14.00', '1190.00', '9690.00', '2026-06-29 07:56:30');
INSERT INTO `quotation_items` (`id`, `quotation_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('7', '5', '11', 'Support & Maintenance', '1.00', '3200.00', '14.00', '448.00', '3648.00', '2026-06-29 07:56:30');
INSERT INTO `quotation_items` (`id`, `quotation_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('8', '6', '12', 'Brand Identity Kit', '1.00', '7800.00', '14.00', '1092.00', '8892.00', '2026-06-29 07:56:30');
INSERT INTO `quotation_items` (`id`, `quotation_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('9', '7', '10', 'Responsive website design and implementation', '1.00', '18500.00', '14.00', '2590.00', '21090.00', '2026-06-29 09:57:11');
INSERT INTO `quotation_items` (`id`, `quotation_id`, `product_id`, `description`, `quantity`, `unit_price`, `tax_rate`, `tax_amount`, `line_total`, `created_at`) VALUES ('10', '8', '10', 'Responsive website design and implementation', '1.00', '18500.00', '14.00', '2590.00', '21090.00', '2026-06-29 10:05:24');

DROP TABLE IF EXISTS `quotation_proposals`;
CREATE TABLE `quotation_proposals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `proposal_template_id` bigint(20) unsigned DEFAULT NULL,
  `introduction` text DEFAULT NULL,
  `scope_text` text DEFAULT NULL,
  `acceptance_text` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_quotation_proposal` (`quotation_id`),
  KEY `fk_quotation_proposals_template` (`proposal_template_id`),
  CONSTRAINT `fk_quotation_proposals_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_quotation_proposals_template` FOREIGN KEY (`proposal_template_id`) REFERENCES `proposal_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `quotation_revisions`;
CREATE TABLE `quotation_revisions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `revision_number` int(11) NOT NULL DEFAULT 1,
  `summary` varchar(255) NOT NULL,
  `snapshot_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`snapshot_json`)),
  `created_by` varchar(160) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_quote_revisions_quote` (`quotation_id`,`revision_number`),
  CONSTRAINT `fk_quote_revisions_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `quotations`;
CREATE TABLE `quotations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `quote_number` varchar(60) NOT NULL,
  `revision_number` int(11) NOT NULL DEFAULT 1,
  `title` varchar(180) NOT NULL DEFAULT 'Quotation',
  `issue_date` date NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `deposit_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','sent','viewed','accepted','rejected','expired','converted') NOT NULL DEFAULT 'draft',
  `approval_status` enum('not_required','pending','approved','rejected') NOT NULL DEFAULT 'not_required',
  `approval_reason` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `public_token` varchar(120) NOT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `viewed_at` timestamp NULL DEFAULT NULL,
  `accepted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `public_token` (`public_token`),
  UNIQUE KEY `uq_quote_number` (`company_id`,`quote_number`),
  KEY `fk_quotes_client` (`client_id`),
  KEY `idx_quotes_company_status` (`company_id`,`status`),
  CONSTRAINT `fk_quotes_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_quotes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=736 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quotations` (`id`, `company_id`, `client_id`, `quote_number`, `revision_number`, `title`, `issue_date`, `expiry_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `status`, `approval_status`, `approval_reason`, `notes`, `terms`, `public_token`, `sent_at`, `viewed_at`, `accepted_at`, `created_at`, `updated_at`) VALUES ('4', '4', '7', 'QT-2026-00001', '1', 'Website and Retainer Proposal', '2026-06-11', '2026-07-11', '27000.00', '3780.00', '1500.00', '9000.00', '29280.00', 'accepted', 'not_required', NULL, 'Deposit required before project kickoff.', 'Deposit payable on acceptance. Remaining balance due on delivery.', 'b08d8c4e64ed396fc9f98645e3528fd19784d9638580fbff', '2026-06-14 12:07:47', '2026-06-15 12:07:47', '2026-06-19 12:07:47', '2026-06-29 07:56:30', '2026-06-29 12:07:47');
INSERT INTO `quotations` (`id`, `company_id`, `client_id`, `quote_number`, `revision_number`, `title`, `issue_date`, `expiry_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `status`, `approval_status`, `approval_reason`, `notes`, `terms`, `public_token`, `sent_at`, `viewed_at`, `accepted_at`, `created_at`, `updated_at`) VALUES ('5', '4', '8', 'QT-2026-00002', '1', 'Monthly Support Agreement', '2026-06-22', '2026-07-20', '3200.00', '448.00', '0.00', '0.00', '3648.00', 'sent', 'not_required', NULL, 'Recurring support agreement for operations team.', 'Billed monthly in advance.', 'ec02724cb2e6e3024815f7cf2a1c481bbbf1b4d19a906ce9', '2026-06-24 12:07:47', NULL, NULL, '2026-06-29 07:56:30', '2026-06-29 12:07:47');
INSERT INTO `quotations` (`id`, `company_id`, `client_id`, `quote_number`, `revision_number`, `title`, `issue_date`, `expiry_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `status`, `approval_status`, `approval_reason`, `notes`, `terms`, `public_token`, `sent_at`, `viewed_at`, `accepted_at`, `created_at`, `updated_at`) VALUES ('6', '4', '9', 'QT-2026-00003', '1', 'Overdue Branding Invoice', '2026-05-15', '2026-05-30', '7800.00', '1092.00', '0.00', '0.00', '8892.00', 'converted', 'not_required', NULL, 'Legacy branding work.', 'Payment due within 14 days.', 'a12ebf37231b6b79b49e2e0258ff63a0fd564c0cfdb42461', NULL, NULL, NULL, '2026-06-29 07:56:30', '2026-06-29 07:56:30');
INSERT INTO `quotations` (`id`, `company_id`, `client_id`, `quote_number`, `revision_number`, `title`, `issue_date`, `expiry_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `status`, `approval_status`, `approval_reason`, `notes`, `terms`, `public_token`, `sent_at`, `viewed_at`, `accepted_at`, `created_at`, `updated_at`) VALUES ('7', '4', '9', 'QT-2026-00004', '1', 'Quotation', '2026-06-29', '2026-07-13', '18500.00', '2590.00', '0.00', '0.00', '21090.00', 'accepted', 'not_required', NULL, '', 'Valid until expiry date. Prices subject to stock availability.', '5106f4fa246f114b62438ff19abca38c3bc3888b1a997395', '2026-06-29 09:58:06', '2026-06-29 09:58:20', '2026-06-29 09:59:00', '2026-06-29 09:57:11', '2026-06-29 09:59:00');
INSERT INTO `quotations` (`id`, `company_id`, `client_id`, `quote_number`, `revision_number`, `title`, `issue_date`, `expiry_date`, `subtotal`, `tax_total`, `discount_amount`, `deposit_amount`, `total`, `status`, `approval_status`, `approval_reason`, `notes`, `terms`, `public_token`, `sent_at`, `viewed_at`, `accepted_at`, `created_at`, `updated_at`) VALUES ('8', '4', '10', 'QT-2026-00005', '1', 'Quotation', '2026-06-29', '2026-07-13', '18500.00', '2590.00', '0.00', '0.00', '21090.00', 'converted', 'not_required', NULL, '', 'Valid until expiry date. Prices subject to stock availability.', '4796c7ce14662d0fb307cc4920f675d3f7aac2fd688aec2b', '2026-06-29 10:05:28', '2026-06-29 22:03:18', NULL, '2026-06-29 10:05:24', '2026-07-01 02:26:08');

DROP TABLE IF EXISTS `quote_package_options`;
CREATE TABLE `quote_package_options` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `is_recommended` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_quote_packages_quote` (`quotation_id`,`sort_order`),
  CONSTRAINT `fk_quote_packages_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `quote_package_options` (`id`, `quotation_id`, `name`, `description`, `amount`, `is_recommended`, `sort_order`, `created_at`) VALUES ('1', '4', 'Launch', 'Website build with standard launch support.', '22000.00', '0', '1', '2026-06-29 12:07:47');
INSERT INTO `quote_package_options` (`id`, `quotation_id`, `name`, `description`, `amount`, `is_recommended`, `sort_order`, `created_at`) VALUES ('2', '4', 'Growth', 'Website build, consulting retainer, analytics and 30-day support.', '30780.00', '1', '2', '2026-06-29 12:07:47');
INSERT INTO `quote_package_options` (`id`, `quotation_id`, `name`, `description`, `amount`, `is_recommended`, `sort_order`, `created_at`) VALUES ('3', '4', 'Scale', 'Growth package plus priority support and conversion optimization.', '42800.00', '0', '3', '2026-06-29 12:07:47');
INSERT INTO `quote_package_options` (`id`, `quotation_id`, `name`, `description`, `amount`, `is_recommended`, `sort_order`, `created_at`) VALUES ('4', '5', 'Care', 'Core support and monthly reporting.', '7800.00', '0', '1', '2026-06-29 12:07:47');
INSERT INTO `quote_package_options` (`id`, `quotation_id`, `name`, `description`, `amount`, `is_recommended`, `sort_order`, `created_at`) VALUES ('5', '5', 'Care Plus', 'Core support plus faster turnaround and quarterly strategy.', '11800.00', '1', '2', '2026-06-29 12:07:47');

DROP TABLE IF EXISTS `recurring_invoice_profiles`;
CREATE TABLE `recurring_invoice_profiles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `source_invoice_id` bigint(20) unsigned NOT NULL,
  `name` varchar(180) NOT NULL,
  `frequency` enum('weekly','monthly','quarterly','annual') NOT NULL DEFAULT 'monthly',
  `next_run_date` date NOT NULL,
  `last_run_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `auto_send` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','paused','cancelled','completed') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_recurring_client` (`client_id`),
  KEY `fk_recurring_source_invoice` (`source_invoice_id`),
  KEY `idx_recurring_company_next` (`company_id`,`status`,`next_run_date`),
  CONSTRAINT `fk_recurring_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_recurring_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_recurring_source_invoice` FOREIGN KEY (`source_invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `recurring_invoice_profiles` (`id`, `company_id`, `client_id`, `source_invoice_id`, `name`, `frequency`, `next_run_date`, `last_run_date`, `end_date`, `auto_send`, `status`, `created_at`, `updated_at`) VALUES ('2', '4', '7', '3', 'Naledi Monthly Support', 'monthly', '2026-08-09', '2026-07-22', NULL, '1', 'active', '2026-06-29 07:56:30', '2026-07-22 14:22:46');

DROP TABLE IF EXISTS `sales_orders`;
CREATE TABLE `sales_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `quotation_id` bigint(20) unsigned DEFAULT NULL,
  `order_number` varchar(60) NOT NULL,
  `order_date` date NOT NULL,
  `status` enum('draft','confirmed','fulfilled','cancelled') NOT NULL DEFAULT 'draft',
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sales_order_number` (`company_id`,`order_number`),
  KEY `fk_sales_orders_client` (`client_id`),
  KEY `fk_sales_orders_quote` (`quotation_id`),
  CONSTRAINT `fk_sales_orders_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_sales_orders_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sales_orders_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `sales_pipeline_stages`;
CREATE TABLE `sales_pipeline_stages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `color` varchar(20) NOT NULL DEFAULT '#4f76a2',
  `probability` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `position` int(11) NOT NULL DEFAULT 0,
  `stage_type` enum('open','won','lost') NOT NULL DEFAULT 'open',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pipeline_stage_slug` (`company_id`,`slug`),
  KEY `idx_pipeline_stages_company_position` (`company_id`,`position`),
  CONSTRAINT `fk_pipeline_stages_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('1', '1', 'New opportunity', 'new-opportunity', '#526d8b', '10', '10', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('2', '1', 'Qualified', 'qualified', '#2a9bb7', '30', '20', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('3', '1', 'Quote sent', 'quote-sent', '#4f76a2', '55', '30', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('4', '1', 'Negotiation', 'negotiation', '#c8963e', '75', '40', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('5', '1', 'Won', 'won', '#198f72', '100', '50', 'won', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('6', '1', 'Lost', 'lost', '#b94b5a', '0', '60', 'lost', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('7', '4', 'New opportunity', 'new-opportunity', '#526d8b', '10', '10', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('8', '4', 'Qualified', 'qualified', '#2a9bb7', '30', '20', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('9', '4', 'Quote sent', 'quote-sent', '#4f76a2', '55', '30', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('10', '4', 'Negotiation', 'negotiation', '#c8963e', '75', '40', 'open', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('11', '4', 'Won', 'won', '#198f72', '100', '50', 'won', '2026-07-10 08:14:57', NULL);
INSERT INTO `sales_pipeline_stages` (`id`, `company_id`, `name`, `slug`, `color`, `probability`, `position`, `stage_type`, `created_at`, `updated_at`) VALUES ('12', '4', 'Lost', 'lost', '#b94b5a', '0', '60', 'lost', '2026-07-10 08:14:57', NULL);

DROP TABLE IF EXISTS `sales_tasks`;
CREATE TABLE `sales_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `assigned_user_id` bigint(20) unsigned DEFAULT NULL,
  `created_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `completed_by_user_id` bigint(20) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `deal_id` bigint(20) unsigned DEFAULT NULL,
  `client_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(180) NOT NULL,
  `description` text DEFAULT NULL,
  `due_at` datetime DEFAULT NULL,
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `status` enum('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open',
  `assigned_at` datetime DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `completion_note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sales_tasks_user` (`assigned_user_id`),
  KEY `fk_sales_tasks_lead` (`lead_id`),
  KEY `fk_sales_tasks_deal` (`deal_id`),
  KEY `fk_sales_tasks_client` (`client_id`),
  KEY `idx_sales_tasks_company_status` (`company_id`,`status`,`due_at`),
  KEY `idx_sales_tasks_assignee_status` (`company_id`,`assigned_user_id`,`status`,`due_at`),
  CONSTRAINT `fk_sales_tasks_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sales_tasks_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sales_tasks_deal` FOREIGN KEY (`deal_id`) REFERENCES `deals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sales_tasks_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sales_tasks_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('1', '4', '4', NULL, '4', NULL, NULL, NULL, 'Request feedback from Sample Client', 'Confirm handover, request feedback, and discuss the next opportunity.', '2026-07-13 09:19:47', 'normal', 'completed', '2026-07-16 08:41:48', NULL, '2026-07-16 08:51:05', NULL, NULL, '2026-07-11 01:19:47', '2026-07-16 00:51:05');
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('2', '4', '23', NULL, '23', NULL, NULL, NULL, 'Update so work', 'Please address the above', '2026-07-15 00:16:00', 'high', 'completed', '2026-07-15 11:16:35', NULL, '2026-07-15 11:18:08', NULL, NULL, '2026-07-15 03:16:35', '2026-07-15 03:18:08');
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('3', '4', '23', NULL, '4', NULL, NULL, NULL, 'check again', 'check again', '2026-07-15 00:18:00', 'urgent', 'completed', '2026-07-15 11:19:00', '2026-07-16 08:41:37', '2026-07-16 08:42:16', NULL, 'its done\nManager override: yehey', '2026-07-15 03:19:00', '2026-07-16 00:42:16');
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('7', '4', '4', NULL, NULL, '1', NULL, NULL, 'Contact checking within one business day', 'Review the new enquiry, confirm their need, and record the next step.', '2026-07-16 10:51:00', 'high', 'open', '2026-07-16 08:47:56', NULL, NULL, NULL, NULL, '2026-07-16 00:47:56', '2026-07-16 00:51:20');
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('8', '4', '4', NULL, NULL, NULL, NULL, NULL, 'Request feedback from Comp', 'Confirm handover, request feedback, and discuss the next opportunity.', '2026-07-18 09:36:03', 'normal', 'open', '2026-07-16 09:36:03', NULL, NULL, NULL, NULL, '2026-07-16 01:36:03', NULL);
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('9', '4', '4', NULL, NULL, NULL, NULL, NULL, 'Request feedback from Sample Client', 'Confirm handover, request feedback, and discuss the next opportunity.', '2026-07-18 09:41:35', 'normal', 'open', '2026-07-16 09:41:35', NULL, NULL, NULL, NULL, '2026-07-16 01:41:35', NULL);
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('10', '4', '4', NULL, NULL, NULL, NULL, NULL, 'Request feedback from Sample Client', 'Confirm handover, request feedback, and discuss the next opportunity.', '2026-07-18 09:42:08', 'normal', 'open', '2026-07-16 09:42:08', NULL, NULL, NULL, NULL, '2026-07-16 01:42:08', NULL);
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('11', '4', '4', NULL, NULL, NULL, NULL, NULL, 'Request feedback from Sample Client', 'Confirm handover, request feedback, and discuss the next opportunity.', '2026-07-18 09:42:15', 'normal', 'open', '2026-07-16 09:42:15', NULL, NULL, NULL, NULL, '2026-07-16 01:42:15', NULL);
INSERT INTO `sales_tasks` (`id`, `company_id`, `assigned_user_id`, `created_by_user_id`, `completed_by_user_id`, `lead_id`, `deal_id`, `client_id`, `title`, `description`, `due_at`, `priority`, `status`, `assigned_at`, `started_at`, `completed_at`, `cancelled_at`, `completion_note`, `created_at`, `updated_at`) VALUES ('12', '4', '4', NULL, NULL, '2', NULL, NULL, 'Contact Kabo Motho within one business day', 'Review the new enquiry, confirm their need, and record the next step.', '2026-07-17 09:49:17', 'high', 'open', '2026-07-16 09:49:17', NULL, NULL, NULL, NULL, '2026-07-16 01:49:17', NULL);

DROP TABLE IF EXISTS `signature_certificates`;
CREATE TABLE `signature_certificates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `quotation_id` bigint(20) unsigned NOT NULL,
  `quotation_acceptance_id` bigint(20) unsigned NOT NULL,
  `certificate_token` varchar(100) NOT NULL,
  `document_hash` varchar(64) NOT NULL,
  `signer_name` varchar(160) NOT NULL,
  `signer_email` varchar(190) DEFAULT NULL,
  `signature_method` enum('typed','drawn','uploaded') NOT NULL DEFAULT 'typed',
  `signing_order` int(11) NOT NULL DEFAULT 1,
  `ip_address` varchar(80) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `signed_payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`signed_payload`)),
  `signed_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_signature_certificate_token` (`certificate_token`),
  UNIQUE KEY `uq_signature_acceptance` (`quotation_acceptance_id`),
  KEY `fk_signature_certificates_company` (`company_id`),
  KEY `fk_signature_certificates_quote` (`quotation_id`),
  CONSTRAINT `fk_signature_certificates_acceptance` FOREIGN KEY (`quotation_acceptance_id`) REFERENCES `quotation_acceptances` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_signature_certificates_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_signature_certificates_quote` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `social_accounts`;
CREATE TABLE `social_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `platform` enum('facebook','instagram','linkedin','x','tiktok') NOT NULL,
  `account_name` varchar(180) NOT NULL,
  `provider_account_id` varchar(190) DEFAULT NULL,
  `access_token` text DEFAULT NULL,
  `refresh_token` text DEFAULT NULL,
  `scopes` text DEFAULT NULL,
  `token_type` varchar(60) DEFAULT NULL,
  `token_expires_at` datetime DEFAULT NULL,
  `raw_response` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`raw_response`)),
  `connected_at` timestamp NULL DEFAULT NULL,
  `status` enum('connected','expired','disabled') NOT NULL DEFAULT 'connected',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_social_accounts_company` (`company_id`),
  CONSTRAINT `fk_social_accounts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `social_post_media`;
CREATE TABLE `social_post_media` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `social_post_id` bigint(20) unsigned NOT NULL,
  `marketing_media_asset_id` bigint(20) unsigned DEFAULT NULL,
  `media_type` enum('image','video','reel','link') NOT NULL DEFAULT 'image',
  `media_url` text NOT NULL,
  `media_path` varchar(255) DEFAULT NULL,
  `media_source` varchar(120) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_social_post_media_asset` (`marketing_media_asset_id`),
  KEY `idx_social_post_media_post` (`social_post_id`,`sort_order`),
  CONSTRAINT `fk_social_post_media_asset` FOREIGN KEY (`marketing_media_asset_id`) REFERENCES `marketing_media_assets` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_social_post_media_post` FOREIGN KEY (`social_post_id`) REFERENCES `social_posts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('8', '20', '32', 'video', 'https://mixkit.co/free-stock-video/going-down-a-curved-highway-through-a-mountain-range-41576/', 'uploads/marketing-media/4/20260701100422-58648f0d8091.mp4', NULL, '0', '2026-07-01 02:05:09');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('9', '21', NULL, 'image', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'Product image', '0', '2026-07-04 12:24:12');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('10', '22', NULL, 'image', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'Product image', '0', '2026-07-04 12:24:14');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('19', '23', '47', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg', 'uploads/marketing-media/4/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg', 'AI Generator', '0', '2026-07-05 06:01:29');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('20', '23', '46', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'AI Generator', '1', '2026-07-05 06:01:29');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('21', '24', '46', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'AI Generator', '0', '2026-07-05 06:13:08');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('23', '25', '49', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg', 'uploads/marketing-media/4/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg', 'AI Generator', '0', '2026-07-15 02:47:42');
INSERT INTO `social_post_media` (`id`, `social_post_id`, `marketing_media_asset_id`, `media_type`, `media_url`, `media_path`, `media_source`, `sort_order`, `created_at`) VALUES ('25', '19', '29', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'Local upload', '0', '2026-07-15 02:52:40');

DROP TABLE IF EXISTS `social_post_metrics`;
CREATE TABLE `social_post_metrics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `social_post_id` bigint(20) unsigned NOT NULL,
  `impressions` int(11) NOT NULL DEFAULT 0,
  `reach` int(11) NOT NULL DEFAULT 0,
  `likes` int(11) NOT NULL DEFAULT 0,
  `comments` int(11) NOT NULL DEFAULT 0,
  `shares` int(11) NOT NULL DEFAULT 0,
  `clicks` int(11) NOT NULL DEFAULT 0,
  `engagement_rate` decimal(8,4) NOT NULL DEFAULT 0.0000,
  `captured_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_social_metrics_post_date` (`social_post_id`,`captured_at`),
  CONSTRAINT `fk_social_metrics_post` FOREIGN KEY (`social_post_id`) REFERENCES `social_posts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `social_posts`;
CREATE TABLE `social_posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `social_account_id` bigint(20) unsigned DEFAULT NULL,
  `campaign_id` bigint(20) unsigned DEFAULT NULL,
  `external_post_id` varchar(180) DEFAULT NULL,
  `platform` varchar(60) NOT NULL,
  `content` text NOT NULL,
  `media_type` enum('none','image','video','reel') NOT NULL DEFAULT 'none',
  `media_url` text DEFAULT NULL,
  `media_source` varchar(120) DEFAULT NULL,
  `media_path` varchar(255) DEFAULT NULL,
  `status` enum('draft','scheduled','published','failed') NOT NULL DEFAULT 'draft',
  `approval_status` enum('draft','review','approved','rejected') NOT NULL DEFAULT 'draft',
  `approval_note` text DEFAULT NULL,
  `approval_requested_at` timestamp NULL DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `performance_quotes` int(11) NOT NULL DEFAULT 0,
  `performance_leads` int(11) NOT NULL DEFAULT 0,
  `performance_clicks` int(11) NOT NULL DEFAULT 0,
  `performance_views` int(11) NOT NULL DEFAULT 0,
  `recycled_from_post_id` bigint(20) unsigned DEFAULT NULL,
  `recycle_interval_days` int(11) DEFAULT NULL,
  `scheduled_at` datetime DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_social_posts_company` (`company_id`),
  KEY `fk_social_posts_account` (`social_account_id`),
  KEY `fk_social_posts_campaign` (`campaign_id`),
  CONSTRAINT `fk_social_posts_account` FOREIGN KEY (`social_account_id`) REFERENCES `social_accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_social_posts_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `marketing_campaigns` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_social_posts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('19', '4', NULL, '5', NULL, 'whatsapp', 'Looking for a reliable solution? Try Brand Identity Kit today and experience professional value built for your business needs. Contact us for a quotation.\n\n#SmartSolutions', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'Local upload', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'draft', 'approved', NULL, '2026-07-01 01:54:06', '2026-07-01 01:54:23', '0', '0', '0', '0', NULL, NULL, '2026-07-02 09:53:00', NULL, '2026-07-01 01:42:05', '2026-07-15 02:52:40');
INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('20', '4', NULL, '6', NULL, 'facebook', 'Still losing time on manual work? Business Consulting Retainer helps you move faster, look sharper {product_name}, and serve customers better. Contact us today for a quotation.  {whatsapp_link} {hashtags}\r\n\r\n#BusinessGrowth #BotswanaBusiness #SmartSolutions', 'video', 'https://mixkit.co/free-stock-video/going-down-a-curved-highway-through-a-mountain-range-41576/', 'Media Library', 'uploads/marketing-media/4/20260701100422-58648f0d8091.mp4', 'draft', 'approved', NULL, '2026-07-01 02:05:52', '2026-07-01 02:06:01', '0', '0', '0', '0', NULL, NULL, '2026-07-01 11:56:00', NULL, '2026-07-01 01:59:35', '2026-07-01 02:06:01');
INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('21', '4', NULL, '7', NULL, 'facebook', 'Looking for a professional website that helps your business look credible and easy to find online? Our Website Build Package includes responsive website design and implementation for BWP 18,500.00. Ideal for businesses that want a clean, modern website that works well on phones and desktops. Request a quote today and let’s discuss your business needs.', 'image', NULL, 'Product image', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'draft', 'draft', NULL, NULL, NULL, '0', '0', '0', '0', NULL, NULL, NULL, NULL, '2026-07-04 12:24:12', '2026-07-04 12:33:11');
INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('22', '4', NULL, '7', NULL, 'whatsapp', 'Need a professional website for your business? Our Website Build Package gives you a responsive, modern design built to help customers find your business on any device. Price: BWP 18,500.00. If you\'d like a quote or want to discuss your website needs, send us a message today.', 'image', NULL, 'Product image', 'uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png', 'draft', 'draft', NULL, NULL, NULL, '0', '0', '0', '0', NULL, NULL, NULL, NULL, '2026-07-04 12:24:14', '2026-07-04 12:33:19');
INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('23', '4', NULL, '8', NULL, 'facebook', 'Headline: Learn Smarter with ThutoNest\nSubheadline: Access past exam papers, study guides, worksheets, and tutoring support designed to help students prepare with confidence. {product_name}\nCTA Button: Explore Resources\n\n#ThutoNest #StudyResources #PastExamPapers #TutoringSupport #StudentSuccess', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg', 'AI Generator', 'uploads/marketing-media/4/20260705130413-act-like-a-senior-education-marketing-strategist-5333a1d8.jpg', 'draft', 'approved', NULL, '2026-07-05 05:08:12', '2026-07-05 05:31:50', '0', '0', '0', '0', NULL, NULL, '2026-07-05 15:08:00', NULL, '2026-07-05 05:06:19', '2026-07-05 06:01:29');
INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('24', '4', NULL, '9', NULL, 'facebook', 'Monthly technical support made simple. Our {product_name} package is available at BWP {price} and is designed to help your business stay up and running. Need ongoing help with your systems? Message us for a quote today.{hashtags}{category}\n\n#SupportAndMaintenance #TechnicalSupport #BusinessSupport #MonthlyMaintenance #SmallBusinessBotswana #BotswanaBusiness #TechSupport #BusinessServices', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'AI Generator', 'uploads/marketing-media/4/20260705124430-web-developement-company-that-need-more-sales-8e0eaaae.jpg', 'draft', 'draft', NULL, NULL, NULL, '0', '0', '0', '0', NULL, NULL, '2026-07-05 15:12:00', NULL, '2026-07-05 06:13:08', NULL);
INSERT INTO `social_posts` (`id`, `company_id`, `social_account_id`, `campaign_id`, `external_post_id`, `platform`, `content`, `media_type`, `media_url`, `media_source`, `media_path`, `status`, `approval_status`, `approval_note`, `approval_requested_at`, `approved_at`, `performance_quotes`, `performance_leads`, `performance_clicks`, `performance_views`, `recycled_from_post_id`, `recycle_interval_days`, `scheduled_at`, `published_at`, `created_at`, `updated_at`) VALUES ('25', '4', NULL, '10', NULL, 'facebook', 'SchoolPixels is a multi-school management system designed for the needs of modern schools. From centralizing operations to improving coordination across multiple campuses, it helps school teams work more efficiently and stay organized. A smarter way to manage today’s growing school network. Reach out for a quote or purchase inquiry.\n\n#EducationTechnology #SchoolAdministration #EdTech #SchoolOperations #ModernSchools', 'image', 'http://localhost:8000/uploads/marketing-media/4/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg', 'AI Generator', 'uploads/marketing-media/4/20260715095359-create-a-professional-post-to-help-sell-schoolpi-bb75ff56.jpg', 'draft', 'approved', NULL, '2026-07-15 01:55:36', '2026-07-15 01:56:01', '0', '0', '0', '0', NULL, NULL, '2026-07-15 11:53:00', NULL, '2026-07-15 01:55:18', '2026-07-15 02:47:42');

DROP TABLE IF EXISTS `subscription_payment_attempts`;
CREATE TABLE `subscription_payment_attempts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `company_subscription_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `gateway` varchar(40) NOT NULL DEFAULT 'dpo',
  `reference` varchar(160) NOT NULL,
  `transaction_token` varchar(190) DEFAULT NULL,
  `status` enum('pending','successful','failed','cancelled') NOT NULL DEFAULT 'pending',
  `raw_response` mediumtext DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sub_pay_subscription` (`company_subscription_id`),
  KEY `idx_sub_pay_company_status` (`company_id`,`status`,`created_at`),
  KEY `idx_sub_pay_token` (`transaction_token`),
  CONSTRAINT `fk_sub_pay_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sub_pay_subscription` FOREIGN KEY (`company_subscription_id`) REFERENCES `company_subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `subscription_payment_attempts` (`id`, `company_id`, `company_subscription_id`, `amount`, `currency`, `gateway`, `reference`, `transaction_token`, `status`, `raw_response`, `completed_at`, `created_at`, `updated_at`) VALUES ('1', '4', '4', '499.00', 'BWP', 'dpo', 'BQP-SUB-4-4-20260703043747', NULL, 'pending', NULL, NULL, '2026-07-02 20:37:47', NULL);
INSERT INTO `subscription_payment_attempts` (`id`, `company_id`, `company_subscription_id`, `amount`, `currency`, `gateway`, `reference`, `transaction_token`, `status`, `raw_response`, `completed_at`, `created_at`, `updated_at`) VALUES ('2', '4', '4', '1499.00', 'BWP', 'dpo', 'BQP-SUB-4-4-20260704053216', NULL, 'pending', NULL, NULL, '2026-07-03 21:32:16', NULL);
INSERT INTO `subscription_payment_attempts` (`id`, `company_id`, `company_subscription_id`, `amount`, `currency`, `gateway`, `reference`, `transaction_token`, `status`, `raw_response`, `completed_at`, `created_at`, `updated_at`) VALUES ('3', '4', '4', '817.48', 'BWP', 'dpo', 'BQP-SUB-4-4-20260711224426', NULL, 'pending', NULL, NULL, '2026-07-11 14:44:26', NULL);
INSERT INTO `subscription_payment_attempts` (`id`, `company_id`, `company_subscription_id`, `amount`, `currency`, `gateway`, `reference`, `transaction_token`, `status`, `raw_response`, `completed_at`, `created_at`, `updated_at`) VALUES ('4', '4', '4', '262.80', 'BWP', 'dpo', 'BQP-SUB-4-4-20260714125633', NULL, 'pending', NULL, NULL, '2026-07-14 04:56:33', NULL);

DROP TABLE IF EXISTS `subscription_plans`;
CREATE TABLE `subscription_plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  `monthly_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `annual_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `max_users` int(11) NOT NULL DEFAULT 1,
  `max_quotes_per_month` int(11) NOT NULL DEFAULT 25,
  `max_invoices_per_month` int(11) NOT NULL DEFAULT 25,
  `ai_posts_per_month` int(11) NOT NULL DEFAULT 10,
  `ai_text_generations_per_month` int(11) NOT NULL DEFAULT 10,
  `ai_images_per_month` int(11) NOT NULL DEFAULT 0,
  `ai_video_seconds_per_month` int(11) NOT NULL DEFAULT 0,
  `features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`features`)),
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `subscription_plans` (`id`, `name`, `code`, `monthly_price`, `annual_price`, `max_users`, `max_quotes_per_month`, `max_invoices_per_month`, `ai_posts_per_month`, `ai_text_generations_per_month`, `ai_images_per_month`, `ai_video_seconds_per_month`, `features`, `status`, `created_at`) VALUES ('1', 'Starter', 'starter', '19.00', '190.00', '2', '50', '50', '10', '10', '2', '0', '{\"tagline\":\"Best for lean teams sending their first quotes.\",\"badge\":\"Starter\",\"pdf\":true,\"email\":true,\"payments\":false,\"ai_marketing\":false,\"reports\":false,\"advanced_reports\":false,\"api\":false,\"custom_branding\":false}', 'active', '2026-06-29 04:00:40');
INSERT INTO `subscription_plans` (`id`, `name`, `code`, `monthly_price`, `annual_price`, `max_users`, `max_quotes_per_month`, `max_invoices_per_month`, `ai_posts_per_month`, `ai_text_generations_per_month`, `ai_images_per_month`, `ai_video_seconds_per_month`, `features`, `status`, `created_at`) VALUES ('2', 'Professional', 'professional', '59.00', '590.00', '10', '500', '500', '100', '100', '20', '0', '{\"tagline\":\"Best for growing teams using AI and payments.\",\"badge\":\"Recommended\",\"pdf\":true,\"email\":true,\"payments\":true,\"ai_marketing\":true,\"reports\":true,\"advanced_reports\":false,\"api\":false,\"custom_branding\":false}', 'active', '2026-06-29 04:00:40');
INSERT INTO `subscription_plans` (`id`, `name`, `code`, `monthly_price`, `annual_price`, `max_users`, `max_quotes_per_month`, `max_invoices_per_month`, `ai_posts_per_month`, `ai_text_generations_per_month`, `ai_images_per_month`, `ai_video_seconds_per_month`, `features`, `status`, `created_at`) VALUES ('3', 'Enterprise', 'enterprise', '199.00', '1990.00', '50', '5000', '5000', '1000', '1000', '100', '0', '{\"tagline\":\"Best for larger teams needing scale and control.\",\"badge\":\"Scale\",\"pdf\":true,\"email\":true,\"payments\":true,\"ai_marketing\":true,\"reports\":true,\"advanced_reports\":true,\"api\":true,\"custom_branding\":true}', 'active', '2026-06-29 04:00:40');

DROP TABLE IF EXISTS `support_request_messages`;
CREATE TABLE `support_request_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `support_request_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `sender_type` enum('requester','staff','system') NOT NULL DEFAULT 'staff',
  `sender_name` varchar(120) NOT NULL,
  `sender_email` varchar(190) DEFAULT NULL,
  `message` text NOT NULL,
  `email_status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `email_error` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_support_message_request` (`support_request_id`,`created_at`),
  KEY `fk_support_message_user` (`user_id`),
  CONSTRAINT `fk_support_message_request` FOREIGN KEY (`support_request_id`) REFERENCES `support_requests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_support_message_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `support_request_messages` (`id`, `support_request_id`, `user_id`, `sender_type`, `sender_name`, `sender_email`, `message`, `email_status`, `email_error`, `created_at`) VALUES ('2', '2', '4', 'staff', 'Demo Owner', 'owner@bizquote.test', 'i saw ur message', 'sent', NULL, '2026-07-14 01:49:39');
INSERT INTO `support_request_messages` (`id`, `support_request_id`, `user_id`, `sender_type`, `sender_name`, `sender_email`, `message`, `email_status`, `email_error`, `created_at`) VALUES ('6', '7', '4', 'staff', 'Demo Owner', 'owner@bizquote.test', 'solved the issue', 'sent', NULL, '2026-07-14 02:03:06');

DROP TABLE IF EXISTS `support_requests`;
CREATE TABLE `support_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_number` varchar(32) NOT NULL,
  `public_token_hash` char(64) NOT NULL,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `email` varchar(190) NOT NULL,
  `category` varchar(60) NOT NULL,
  `subject` varchar(160) NOT NULL,
  `message` text NOT NULL,
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `status` enum('new','open','in_progress','waiting','resolved','closed') NOT NULL DEFAULT 'new',
  `source_page` varchar(255) DEFAULT NULL,
  `support_email_status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `requester_email_status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `support_email_error` varchar(500) DEFAULT NULL,
  `requester_email_error` varchar(500) DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_support_ticket_number` (`ticket_number`),
  UNIQUE KEY `uq_support_public_token` (`public_token_hash`),
  KEY `idx_support_status_created` (`status`,`created_at`),
  KEY `idx_support_company_created` (`company_id`,`created_at`),
  KEY `idx_support_user_created` (`user_id`,`created_at`),
  CONSTRAINT `fk_support_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_support_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `support_requests` (`id`, `ticket_number`, `public_token_hash`, `company_id`, `user_id`, `name`, `email`, `category`, `subject`, `message`, `priority`, `status`, `source_page`, `support_email_status`, `requester_email_status`, `support_email_error`, `requester_email_error`, `resolved_at`, `created_at`, `updated_at`) VALUES ('2', 'BQP-260714-82CF3E', 'b33917744f300a7a5a94def1441998ba76ba5cc1f32f2e8aa7356ee6ca2dd483', '4', '4', 'Demo Owner', 'mokgothut@gmail.com', 'quotes', 'I need help using BizQuote Pro.', 'Hi, I need help using BizQuote Pro.', 'normal', 'waiting', '/help', 'sent', 'sent', NULL, NULL, NULL, '2026-07-14 01:33:47', '2026-07-14 01:49:43');
INSERT INTO `support_requests` (`id`, `ticket_number`, `public_token_hash`, `company_id`, `user_id`, `name`, `email`, `category`, `subject`, `message`, `priority`, `status`, `source_page`, `support_email_status`, `requester_email_status`, `support_email_error`, `requester_email_error`, `resolved_at`, `created_at`, `updated_at`) VALUES ('7', 'BQP-260714-D25AF0', '9661540386fdd0ddd846730d418bbf77009c776d357e1260d90934cf80d24be8', '4', '23', 'momo', 'momo@gmail.com', 'quotes', 'I need help using BizQuote Pro.', 'Hi, I need help using BizQuote Pro.', 'normal', 'closed', '/help', 'sent', 'sent', NULL, NULL, '2026-07-14 10:03:17', '2026-07-14 02:01:43', '2026-07-14 02:03:48');

DROP TABLE IF EXISTS `time_entries`;
CREATE TABLE `time_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `project_task_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `work_date` date NOT NULL,
  `duration_minutes` int(10) unsigned NOT NULL,
  `hourly_rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `description` varchar(255) DEFAULT NULL,
  `billable` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_time_entries_company` (`company_id`),
  KEY `fk_time_entries_task` (`project_task_id`),
  KEY `fk_time_entries_user` (`user_id`),
  KEY `fk_time_entries_invoice` (`invoice_id`),
  KEY `idx_time_entries_project_date` (`project_id`,`work_date`),
  CONSTRAINT `fk_time_entries_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_time_entries_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_time_entries_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_time_entries_task` FOREIGN KEY (`project_task_id`) REFERENCES `project_tasks` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_time_entries_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `time_entries` (`id`, `company_id`, `project_id`, `project_task_id`, `user_id`, `invoice_id`, `work_date`, `duration_minutes`, `hourly_rate`, `description`, `billable`, `created_at`) VALUES ('1', '4', '1', '1', '4', NULL, '2026-07-16', '30', '55.00', 'Completd', '1', '2026-07-16 01:34:41');

DROP TABLE IF EXISTS `usage_events`;
CREATE TABLE `usage_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `resource` varchar(60) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `source_type` varchar(80) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_usage_user` (`user_id`),
  KEY `idx_usage_company_resource_month` (`company_id`,`resource`,`created_at`),
  KEY `idx_usage_user_resource_month` (`company_id`,`user_id`,`resource`,`created_at`),
  CONSTRAINT `fk_usage_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_usage_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('1', '4', '4', 'ai_posts', '2', 'product_social_posts', '7', '{\"product_id\":10,\"platforms\":[\"facebook\",\"whatsapp\"],\"image_path\":\"uploads/marketing-media/4/20260701095137-screenshot-2026-06-13-020052-d60837fb.png\",\"created_by_script\":\"bqp-create-image-social-posts\"}', '2026-07-04 12:24:14');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('2', '4', '4', 'ai_posts', '2', 'ai_generator_tool', NULL, '{\"output_type\":\"image_and_text\",\"variant_count\":1,\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\",\"image_model\":null}', '2026-07-05 04:26:51');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('3', '4', '4', 'ai_posts', '2', 'ai_generator_tool', NULL, '{\"output_type\":\"image_and_text\",\"variant_count\":1,\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\",\"image_model\":\"gpt-image-2\",\"image_format\":\"jpeg\"}', '2026-07-05 04:44:30');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('4', '4', '4', 'ai_posts', '2', 'ai_generator_tool', NULL, '{\"output_type\":\"image_and_text\",\"variant_count\":1,\"platform\":\"facebook\",\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\",\"image_model\":\"gpt-image-2\",\"image_format\":\"jpeg\"}', '2026-07-05 05:04:13');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('5', '4', '4', 'ai_posts', '1', 'marketing_generate', NULL, '{\"platform\":\"facebook\",\"product_id\":11,\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\"}', '2026-07-05 06:09:15');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('19', '4', '4', 'ai_images', '1', 'ai_generator_tool', NULL, '{\"output_type\":\"image_and_text\",\"variant_count\":2,\"platform\":\"facebook\",\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\",\"image_model\":\"gpt-image-2\",\"image_format\":\"jpeg\"}', '2026-07-15 01:42:09');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('24', '4', '4', 'ai_text_generations', '2', 'ai_generator_tool', NULL, '{\"output_type\":\"image_and_text\",\"variant_count\":2,\"platform\":\"facebook\",\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\",\"image_model\":\"gpt-image-2\",\"image_format\":\"jpeg\"}', '2026-07-15 01:53:59');
INSERT INTO `usage_events` (`id`, `company_id`, `user_id`, `resource`, `quantity`, `source_type`, `source_id`, `metadata`, `created_at`) VALUES ('25', '4', '4', 'ai_images', '1', 'ai_generator_tool', NULL, '{\"output_type\":\"image_and_text\",\"variant_count\":2,\"platform\":\"facebook\",\"provider\":\"openai\",\"model\":\"gpt-5.4-mini\",\"image_model\":\"gpt-image-2\",\"image_format\":\"jpeg\"}', '2026-07-15 01:53:59');

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(160) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('super_admin','owner','admin','sales','accountant','marketing','viewer') NOT NULL DEFAULT 'owner',
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_users_company_role` (`company_id`,`role`),
  CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users` (`id`, `company_id`, `name`, `email`, `password_hash`, `role`, `status`, `last_login_at`, `created_at`, `updated_at`) VALUES ('1', '1', 'CompClicks', 'owner@example.com', '$2y$10$QnV3jksAIcTaJl/kWelcruurYC3wrMciubikuJvOmeHHTjhA/yeNq', 'owner', 'active', NULL, '2026-06-29 04:02:44', NULL);
INSERT INTO `users` (`id`, `company_id`, `name`, `email`, `password_hash`, `role`, `status`, `last_login_at`, `created_at`, `updated_at`) VALUES ('4', '4', 'Demo Owner', 'owner@bizquote.test', '$2y$10$WCwRPCrPFjvc7ii4eXONjuK2a8crIi45FMuf5WzeuqiuYFR6L.Q7.', 'owner', 'active', NULL, '2026-06-29 07:56:30', NULL);
INSERT INTO `users` (`id`, `company_id`, `name`, `email`, `password_hash`, `role`, `status`, `last_login_at`, `created_at`, `updated_at`) VALUES ('23', '4', 'momo', 'momo@gmail.com', '$2y$10$Ep6NJWnROJ5OSWvWJDQ2K.4hdwIIJ1wTgLidI/xryIJAm8Qxp3PSW', 'sales', 'active', NULL, '2026-07-11 14:46:56', NULL);

DROP TABLE IF EXISTS `vendor_bills`;
CREATE TABLE `vendor_bills` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `vendor_id` bigint(20) unsigned NOT NULL,
  `purchase_order_id` bigint(20) unsigned DEFAULT NULL,
  `bill_number` varchar(80) NOT NULL,
  `bill_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','open','part_paid','paid','void') NOT NULL DEFAULT 'open',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_vendor_bills_vendor` (`vendor_id`),
  KEY `fk_vendor_bills_po` (`purchase_order_id`),
  KEY `idx_vendor_bills_company_due` (`company_id`,`status`,`due_date`),
  CONSTRAINT `fk_vendor_bills_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vendor_bills_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_vendor_bills_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(180) NOT NULL,
  `contact_name` varchar(160) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone` varchar(80) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `tax_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_vendors_company_name` (`company_id`,`name`),
  CONSTRAINT `fk_vendors_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `vendors` (`id`, `company_id`, `name`, `contact_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('4', '4', 'CloudHost Botswana', NULL, 'billing@cloudhost.test', NULL, NULL, NULL, 'Software supplier', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `vendors` (`id`, `company_id`, `name`, `contact_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('5', '4', 'Office Supplies Hub', NULL, 'accounts@officesupplies.test', NULL, NULL, NULL, 'Office Supplies supplier', 'active', '2026-06-29 07:56:30', NULL);
INSERT INTO `vendors` (`id`, `company_id`, `name`, `contact_name`, `email`, `phone`, `address`, `tax_number`, `notes`, `status`, `created_at`, `updated_at`) VALUES ('6', '4', 'Gaborone Media Buyers', NULL, 'finance@mediabuyers.test', NULL, NULL, NULL, 'Marketing supplier', 'active', '2026-06-29 07:56:30', NULL);

DROP TABLE IF EXISTS `webhook_deliveries`;
CREATE TABLE `webhook_deliveries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_id` bigint(20) unsigned NOT NULL,
  `event_name` varchar(120) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)),
  `status` enum('pending','delivered','retrying','failed') NOT NULL DEFAULT 'pending',
  `attempt_count` int(10) unsigned NOT NULL DEFAULT 0,
  `response_code` int(11) DEFAULT NULL,
  `response_body` varchar(1000) DEFAULT NULL,
  `next_attempt_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_webhook_deliveries_subscription` (`subscription_id`),
  KEY `idx_webhook_deliveries_queue` (`status`,`next_attempt_at`),
  CONSTRAINT `fk_webhook_deliveries_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `webhook_subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `webhook_deliveries` (`id`, `subscription_id`, `event_name`, `payload`, `status`, `attempt_count`, `response_code`, `response_body`, `next_attempt_at`, `delivered_at`, `created_at`, `updated_at`) VALUES ('1', '2', 'invoice.created', '{\"id\":\"1ebbc5aef1244888731f2c0d\",\"event\":\"invoice.created\",\"created_at\":\"2026-07-11T07:19:48+00:00\",\"data\":{\"id\":1001,\"invoice_number\":\"INV-SAMPLE-001\",\"total\":1250,\"currency\":\"BWP\",\"status\":\"sent\"}}', 'delivered', '1', '200', 'Local HMAC signature self-test passed', NULL, '2026-07-11 09:19:48', '2026-07-11 01:19:48', '2026-07-11 01:19:48');

DROP TABLE IF EXISTS `webhook_subscriptions`;
CREATE TABLE `webhook_subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `endpoint_url` varchar(500) NOT NULL,
  `signing_secret` varchar(190) NOT NULL,
  `events` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`events`)),
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_webhook_subscriptions_company` (`company_id`,`status`),
  CONSTRAINT `fk_webhook_subscriptions_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `webhook_subscriptions` (`id`, `company_id`, `name`, `endpoint_url`, `signing_secret`, `events`, `status`, `created_at`, `updated_at`) VALUES ('1', '1', 'Sample: Webhook Test Receiver', 'https://example.com/replace-with-your-webhook-test-url', 'whsec_3bb3a911b557b96d89c1c2d43271cb966927d90060daee9b', '[\"invoice.created\",\"payment.received\",\"quote.accepted\",\"project.completed\"]', 'inactive', '2026-07-11 01:19:28', NULL);
INSERT INTO `webhook_subscriptions` (`id`, `company_id`, `name`, `endpoint_url`, `signing_secret`, `events`, `status`, `created_at`, `updated_at`) VALUES ('2', '4', 'Sample: Webhook Test Receiver', 'https://example.com/replace-with-your-webhook-test-url', 'whsec_3788fd53b7456b5fa870a5b001d342095f1b3aff870bed0c', '[\"invoice.created\",\"payment.received\",\"quote.accepted\",\"project.completed\"]', 'inactive', '2026-07-11 01:19:28', NULL);

DROP TABLE IF EXISTS `workflow_rules`;
CREATE TABLE `workflow_rules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(160) NOT NULL,
  `trigger_event` varchar(100) NOT NULL,
  `entity_type` varchar(80) NOT NULL,
  `conditions_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`conditions_json`)),
  `actions_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`actions_json`)),
  `status` enum('active','paused') NOT NULL DEFAULT 'active',
  `run_count` int(10) unsigned NOT NULL DEFAULT 0,
  `last_run_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_workflow_rules_trigger` (`company_id`,`trigger_event`,`status`),
  CONSTRAINT `fk_workflow_rules_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('1', '1', 'Sample: New lead follow-up', 'lead_created', 'lead', '[]', '[{\"type\":\"create_task\",\"title\":\"Contact {{name}} within one business day\",\"description\":\"Review the new enquiry, confirm their need, and record the next step.\",\"priority\":\"high\",\"due_days\":1,\"assigned_user_id\":1}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('2', '1', 'Sample: Qualified lead discovery', 'lead_qualified', 'lead', '[{\"field\":\"value\",\"operator\":\"greater_than\",\"value\":\"0\"}]', '[{\"type\":\"create_task\",\"title\":\"Prepare discovery for {{name}}\",\"description\":\"Confirm decision-makers, budget, timing, and success criteria.\",\"priority\":\"high\",\"due_days\":0,\"assigned_user_id\":1}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('3', '1', 'Sample: Accepted quote kickoff', 'quotation_accepted', 'deal', '[]', '[{\"type\":\"create_task\",\"title\":\"Start onboarding {{name}}\",\"description\":\"Confirm deposit, kickoff date, responsibilities, and delivery plan.\",\"priority\":\"urgent\",\"due_days\":0,\"assigned_user_id\":1}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('4', '1', 'Sample: Overdue invoice review', 'invoice_overdue', 'invoice', '[{\"field\":\"value\",\"operator\":\"greater_than\",\"value\":\"0\"}]', '[{\"type\":\"create_task\",\"title\":\"Review overdue invoice for {{name}}\",\"description\":\"Check payment history and contact the client with the invoice reference.\",\"priority\":\"urgent\",\"due_days\":0,\"assigned_user_id\":1}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('5', '1', 'Sample: Project completion follow-up', 'project_completed', 'project', '[]', '[{\"type\":\"create_task\",\"title\":\"Request feedback from {{name}}\",\"description\":\"Confirm handover, request feedback, and discuss the next opportunity.\",\"priority\":\"normal\",\"due_days\":2,\"assigned_user_id\":1}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('6', '4', 'Sample: New lead follow-up', 'lead_created', 'lead', '[]', '[{\"type\":\"create_task\",\"title\":\"Contact {{name}} within one business day\",\"description\":\"Review the new enquiry, confirm their need, and record the next step.\",\"priority\":\"high\",\"due_days\":1,\"assigned_user_id\":4}]', 'active', '2', '2026-07-16 09:49:17', '2026-07-11 01:16:50', '2026-07-16 01:49:17');
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('7', '4', 'Sample: Qualified lead discovery', 'lead_qualified', 'lead', '[{\"field\":\"value\",\"operator\":\"greater_than\",\"value\":\"0\"}]', '[{\"type\":\"create_task\",\"title\":\"Prepare discovery for {{name}}\",\"description\":\"Confirm decision-makers, budget, timing, and success criteria.\",\"priority\":\"high\",\"due_days\":0,\"assigned_user_id\":4}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('8', '4', 'Sample: Accepted quote kickoff', 'quotation_accepted', 'deal', '[]', '[{\"type\":\"create_task\",\"title\":\"Start onboarding {{name}}\",\"description\":\"Confirm deposit, kickoff date, responsibilities, and delivery plan.\",\"priority\":\"urgent\",\"due_days\":0,\"assigned_user_id\":4}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('9', '4', 'Sample: Overdue invoice review', 'invoice_overdue', 'invoice', '[{\"field\":\"value\",\"operator\":\"greater_than\",\"value\":\"0\"}]', '[{\"type\":\"create_task\",\"title\":\"Review overdue invoice for {{name}}\",\"description\":\"Check payment history and contact the client with the invoice reference.\",\"priority\":\"urgent\",\"due_days\":0,\"assigned_user_id\":4}]', 'active', '0', NULL, '2026-07-11 01:16:50', NULL);
INSERT INTO `workflow_rules` (`id`, `company_id`, `name`, `trigger_event`, `entity_type`, `conditions_json`, `actions_json`, `status`, `run_count`, `last_run_at`, `created_at`, `updated_at`) VALUES ('10', '4', 'Sample: Project completion follow-up', 'project_completed', 'project', '[]', '[{\"type\":\"create_task\",\"title\":\"Request feedback from {{name}}\",\"description\":\"Confirm handover, request feedback, and discuss the next opportunity.\",\"priority\":\"normal\",\"due_days\":2,\"assigned_user_id\":4}]', 'active', '5', '2026-07-16 09:42:15', '2026-07-11 01:16:50', '2026-07-16 01:42:15');

DROP TABLE IF EXISTS `workflow_runs`;
CREATE TABLE `workflow_runs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `workflow_rule_id` bigint(20) unsigned NOT NULL,
  `entity_type` varchar(80) NOT NULL,
  `entity_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('running','completed','failed','skipped') NOT NULL DEFAULT 'running',
  `input_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`input_json`)),
  `output_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`output_json`)),
  `error_message` text DEFAULT NULL,
  `retry_count` int(10) unsigned NOT NULL DEFAULT 0,
  `started_at` datetime NOT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_workflow_runs_company_date` (`company_id`,`started_at`),
  KEY `idx_workflow_runs_rule` (`workflow_rule_id`,`status`),
  CONSTRAINT `fk_workflow_runs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_workflow_runs_rule` FOREIGN KEY (`workflow_rule_id`) REFERENCES `workflow_rules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('1', '4', '10', 'project', NULL, 'completed', '{\"name\":\"Sample Client\",\"email\":\"sample@example.invalid\",\"phone\":\"+267 7000 0000\",\"value\":25000,\"status\":\"sample\",\"owner_user_id\":4,\"invoice_number\":\"INV-SAMPLE-001\",\"project\":\"Sample Implementation\"}', '[{\"action\":\"create_task\",\"task_id\":1}]', NULL, '0', '2026-07-11 09:19:47', '2026-07-11 09:19:47', '2026-07-11 01:19:47');
INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('2', '4', '6', 'lead', '1', 'completed', '{\"name\":\"checking\",\"email\":\"momo@gmail.com\",\"phone\":\"\",\"source\":\"manual\",\"value\":25832,\"status\":\"new\",\"owner_user_id\":4}', '[{\"action\":\"create_task\",\"task_id\":7}]', NULL, '0', '2026-07-16 08:47:56', '2026-07-16 08:47:56', '2026-07-16 00:47:56');
INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('3', '4', '10', 'project', '1', 'completed', '{\"name\":\"Comp\",\"email\":\"gadgetsfund@gmail.com\",\"phone\":\"72877872\",\"project\":\"Schools\",\"value\":5642.05,\"status\":\"completed\",\"owner_user_id\":4}', '[{\"action\":\"create_task\",\"task_id\":8}]', NULL, '0', '2026-07-16 09:36:03', '2026-07-16 09:36:03', '2026-07-16 01:36:03');
INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('4', '4', '10', 'project', NULL, 'completed', '{\"name\":\"Sample Client\",\"email\":\"sample@example.invalid\",\"phone\":\"+267 7000 0000\",\"value\":25000,\"status\":\"sample\",\"owner_user_id\":4,\"invoice_number\":\"INV-SAMPLE-001\",\"project\":\"Sample Implementation\"}', '[{\"action\":\"create_task\",\"task_id\":9}]', NULL, '0', '2026-07-16 09:41:35', '2026-07-16 09:41:35', '2026-07-16 01:41:35');
INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('5', '4', '10', 'project', NULL, 'completed', '{\"name\":\"Sample Client\",\"email\":\"sample@example.invalid\",\"phone\":\"+267 7000 0000\",\"value\":25000,\"status\":\"sample\",\"owner_user_id\":4,\"invoice_number\":\"INV-SAMPLE-001\",\"project\":\"Sample Implementation\"}', '[{\"action\":\"create_task\",\"task_id\":10}]', NULL, '0', '2026-07-16 09:42:08', '2026-07-16 09:42:08', '2026-07-16 01:42:08');
INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('6', '4', '10', 'project', NULL, 'completed', '{\"name\":\"Sample Client\",\"email\":\"sample@example.invalid\",\"phone\":\"+267 7000 0000\",\"value\":25000,\"status\":\"sample\",\"owner_user_id\":4,\"invoice_number\":\"INV-SAMPLE-001\",\"project\":\"Sample Implementation\"}', '[{\"action\":\"create_task\",\"task_id\":11}]', NULL, '0', '2026-07-16 09:42:15', '2026-07-16 09:42:15', '2026-07-16 01:42:15');
INSERT INTO `workflow_runs` (`id`, `company_id`, `workflow_rule_id`, `entity_type`, `entity_id`, `status`, `input_json`, `output_json`, `error_message`, `retry_count`, `started_at`, `completed_at`, `created_at`) VALUES ('7', '4', '6', 'lead', '2', 'completed', '{\"name\":\"Kabo Motho\",\"email\":\"gadgetsfund@gmail.com\",\"phone\":\"72877872\",\"source\":\"campaign\",\"campaign\":\"A track link test\",\"value\":0,\"status\":\"new\"}', '[{\"action\":\"create_task\",\"task_id\":12}]', NULL, '0', '2026-07-16 09:49:17', '2026-07-16 09:49:17', '2026-07-16 01:49:17');

SET FOREIGN_KEY_CHECKS=1;