-- BizQuote Pro database backup
-- Database: bizquote_pro
-- Created: 2026-07-03T05:11:34+02:00

SET FOREIGN_KEY_CHECKS=0;

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 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=11 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 15: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 15: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 15: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 18:05:07', NULL);

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=5 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 12:02:44', '2026-06-29 14: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 15:56:30', '2026-06-29 17: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=5 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 12: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 15:56:30', 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=14 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', '4');

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=16 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 15:56:30', NULL, '2026-06-14 15: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 15:56:30', NULL, '2026-06-15 15: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 15:56:30', NULL, '2026-06-19 15: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 15:56:30', NULL, '2026-06-20 15: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 17:58:06', NULL, '2026-06-29 17: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 17:58:20', '2026-06-29 17: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 17:58:20', NULL, '2026-06-29 17: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 17:59:00', NULL, '2026-06-29 17: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 18:05:28', NULL, '2026-06-29 18: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-30 05:57:37', NULL, '2026-06-30 05: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-30 06:03:18', '2026-06-30 06: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-30 06:03:18', NULL, '2026-06-30 06: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 10:53:13', NULL, '2026-07-01 10: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 12:02:26', NULL, '2026-07-01 12: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 12:02:56', NULL, '2026-07-01 12:02:56');

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 15: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 15: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 15:56:30', NULL);

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=8 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 15: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 15: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 15: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 10:26:08');

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=4 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 20:05:21', '2026-06-29 20: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 20:05:21', NULL, '2026-06-29 20: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 20: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=6 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 15:56:30', '2026-06-29 20: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 15:56:30', '2026-06-29 15: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', '0.00', '21090.00', '0.00', 'sent', '', 'Valid until expiry date. Prices subject to stock availability.', 'ef3799dcfa24df86c30715b19909e41a363a5d4215b97e7b', '2026-07-01 10:26:08', 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=71 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-30 07: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-30 07: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 16: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 16: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 17: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 17: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 17: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 17: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 17: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 18: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 18: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 18: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 18: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 18: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 18: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 18: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 18: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 18: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 18: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 18: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 19: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 19: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 19: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 19: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 19: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 19: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 19: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 19: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 19: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-07-01 07: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 09: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 10: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 10: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 10: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 10: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 10: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 10: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 23: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 23: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 23: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 23: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 23:25:39');

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=7 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-30 07: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-07-01 07: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 09: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 09: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 09: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 09:59:35', NULL);

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=46 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 09: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 10:04:08', '2026-07-01 10: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 23:02:19', '2026-07-02 23: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 23:02:19', '2026-07-02 23: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 23:02:19', '2026-07-02 23: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 23:02:19', '2026-07-02 23: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 23:05:52', '2026-07-02 23: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 23:05:52', '2026-07-02 23: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 ('40', '4', NULL, 'Video Preview 0000', 'video', 'https://elements-resized.envatousercontent.com/elements-video-cover-images/7e1a5621-6679-409a-b0cf-ac25ed27af59/video_preview/video_preview_0000.jpg?w=400&h=225&cf_fit=cover&q=85&format=auto&s=05e7372362347f22b48f837466a7f9fd1bdfc1b82b238a99cee4257b4282a181', NULL, 'failed', '1', 'That page did not expose a downloadable image or video file. Try the browser media address, a platform download URL, or upload the file manually.', 'https://elements-resized.envatousercontent.com1', 'bulk-queued', 'warning', 'Web page URL detected. Save it as inspiration, or use a direct media file for the strongest preview.', '2026-07-02 23:05:52', '2026-07-02 23:06:18');
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 23:07:22', '2026-07-02 23: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 23:07:22', '2026-07-02 23: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 23:07:22', '2026-07-02 23: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 23:07:22', '2026-07-02 23: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 23:07:22', '2026-07-02 23:07:54');

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=2 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 20:05:21', NULL);

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=3 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 15:56:30');

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=13 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 15: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', NULL, '18500.00', '7000.00', '14.00', 'active', '2026-06-29 15: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 ('11', '4', 'Support & Maintenance', 'MAINT-001', 'Services', 'Monthly technical support package', NULL, '3200.00', '900.00', '14.00', 'active', '2026-06-29 15: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 15:56:30', NULL);

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 15: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 17: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 15: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 15: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 15: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=11 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 15: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 15: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 15: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 15: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 17: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 18:05:24');

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 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=9 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 20:07:47', '2026-06-15 20:07:47', '2026-06-19 20:07:47', '2026-06-29 15:56:30', '2026-06-29 20: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 20:07:47', NULL, NULL, '2026-06-29 15:56:30', '2026-06-29 20: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 15:56:30', '2026-06-29 15: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 17:58:06', '2026-06-29 17:58:20', '2026-06-29 17:59:00', '2026-06-29 17:57:11', '2026-06-29 17: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 18:05:28', '2026-06-30 06:03:18', NULL, '2026-06-29 18:05:24', '2026-07-01 10: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 20: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 20: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 20: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 20: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 20: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=3 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-07-09', NULL, NULL, '1', 'active', '2026-06-29 15:56:30', NULL);

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=9 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 ('5', '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-01 09:54:01');
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 ('6', '19', NULL, 'image', 'http://localhost:8000/uploads/marketing-media/4/20260701091720-screenshot-2026-05-18-141317-6a7c4ee7.png', 'uploads/marketing-media/4/20260701091720-screenshot-2026-05-18-141317-6a7c4ee7.png', 'Local upload', '1', '2026-07-01 09:54:01');
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 10:05:09');

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=21 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.\r\n\r\n#BusinessGrowth #BotswanaBusiness #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 09:54:06', '2026-07-01 09:54:23', '0', '0', '0', '0', NULL, NULL, '2026-07-02 09:53:00', NULL, '2026-07-01 09:42:05', '2026-07-01 09:54:23');
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 10:05:52', '2026-07-01 10:06:01', '0', '0', '0', '0', NULL, NULL, '2026-07-01 11:56:00', NULL, '2026-07-01 09:59:35', '2026-07-01 10:06:01');

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 'BWP',
  `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=2 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-03 04:37:47', 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,
  `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`, `features`, `status`, `created_at`) VALUES ('1', 'Starter', 'starter', '199.00', '1990.00', '2', '50', '50', '10', '{\"pdf\": true, \"email\": true}', 'active', '2026-06-29 12: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`, `features`, `status`, `created_at`) VALUES ('2', 'Professional', 'professional', '499.00', '4990.00', '10', '500', '500', '100', '{\"payments\": true, \"ai_marketing\": true, \"reports\": true}', 'active', '2026-06-29 12: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`, `features`, `status`, `created_at`) VALUES ('3', 'Enterprise', 'enterprise', '1499.00', '14990.00', '50', '5000', '5000', '1000', '{\"api\": true, \"advanced_reports\": true, \"custom_branding\": true}', 'active', '2026-06-29 12:00:40');

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=5 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 12: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 15:56:30', NULL);

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 15: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 15: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 15:56:30', NULL);

SET FOREIGN_KEY_CHECKS=1;