-- phpMyAdmin SQL Dump -- version 5.2.3 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Aug 06, 2026 at 02:27 PM -- Server version: 11.4.12-MariaDB-cll-lve-log -- PHP Version: 8.4.24 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `aandblbc_aandbhubdb` -- -- -------------------------------------------------------- -- -- Table structure for table `activities` -- CREATE TABLE `activities` ( `id` bigint(20) UNSIGNED NOT NULL, `entity_type` varchar(40) NOT NULL COMMENT 'lead, customer, company, appointment, invoice...', `entity_id` bigint(20) UNSIGNED NOT NULL, `type` varchar(60) NOT NULL COMMENT 'call_logged, email_sent, stage_changed, note_added...', `description` varchar(500) NOT NULL, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Structured payload, e.g. {"from":"New","to":"Quoted"}' CHECK (json_valid(`metadata`)), `caused_by` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = system/automation generated', `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `activity_logs` -- CREATE TABLE `activity_logs` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = system-generated', `category` enum('user_action','system','security','admin') NOT NULL DEFAULT 'user_action', `action` varchar(191) NOT NULL, `entity_type` varchar(40) DEFAULT NULL, `entity_id` bigint(20) UNSIGNED DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `ai_prompts` -- CREATE TABLE `ai_prompts` ( `id` bigint(20) UNSIGNED NOT NULL, `feature` enum('ai_assistant','chatbot','voice_agent') NOT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = applies to both brands', `name` varchar(150) NOT NULL, `system_prompt` text NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `api_keys` -- CREATE TABLE `api_keys` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(120) NOT NULL, `key_prefix` varchar(12) NOT NULL COMMENT 'Non-secret prefix shown in UI, e.g. sk_live_9f2a', `key_hash` char(64) NOT NULL COMMENT 'SHA-256 of the full secret key; the raw key is shown once', `scopes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Array of permission slugs this key is limited to' CHECK (json_valid(`scopes`)), `last_used_at` datetime DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `revoked_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `appointments` -- CREATE TABLE `appointments` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `customer_id` bigint(20) UNSIGNED DEFAULT NULL, `lead_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Pre-sale assessments booked before conversion', `parent_appointment_id` bigint(20) UNSIGNED DEFAULT NULL, `service_type_id` bigint(20) UNSIGNED DEFAULT NULL, `assigned_staff_id` bigint(20) UNSIGNED DEFAULT NULL, `scheduled_start` datetime NOT NULL, `scheduled_end` datetime NOT NULL, `status` enum('scheduled','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled', `location_address` varchar(255) DEFAULT NULL, `notes` text DEFAULT NULL, `cancelled_reason` varchar(255) DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL, `recurrence_rule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{"frequency":"weekly","interval":1,"until":"2026-12-31","days_of_week":[2]} — only set on the parent row' CHECK (json_valid(`recurrence_rule`)), `is_recurring_parent` tinyint(1) NOT NULL DEFAULT 0, `reminder_minutes_before` smallint(5) UNSIGNED DEFAULT 60, `reminder_sent_at` datetime DEFAULT NULL, `google_calendar_event_id` varchar(255) DEFAULT NULL ) ; -- -------------------------------------------------------- -- -- Table structure for table `attachments` -- CREATE TABLE `attachments` ( `id` bigint(20) UNSIGNED NOT NULL, `entity_type` varchar(40) NOT NULL, `entity_id` bigint(20) UNSIGNED NOT NULL, `disk_path` varchar(500) NOT NULL COMMENT 'Path under /uploads, never web-executable', `original_name` varchar(255) NOT NULL, `mime_type` varchar(120) NOT NULL, `size_bytes` bigint(20) UNSIGNED NOT NULL, `checksum_sha256` char(64) DEFAULT NULL, `uploaded_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `audience_segments` -- CREATE TABLE `audience_segments` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(150) NOT NULL, `description` varchar(255) DEFAULT NULL, `filter_definition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`filter_definition`)), `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `automation_runs` -- CREATE TABLE `automation_runs` ( `id` bigint(20) UNSIGNED NOT NULL, `workflow_id` bigint(20) UNSIGNED NOT NULL, `entity_type` varchar(40) NOT NULL, `entity_id` bigint(20) UNSIGNED NOT NULL, `status` enum('running','completed','failed','cancelled') NOT NULL DEFAULT 'running', `current_step_id` bigint(20) UNSIGNED DEFAULT NULL, `next_step_at` datetime DEFAULT NULL, `started_at` datetime NOT NULL DEFAULT current_timestamp(), `completed_at` datetime DEFAULT NULL, `error_message` varchar(500) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `automation_steps` -- CREATE TABLE `automation_steps` ( `id` bigint(20) UNSIGNED NOT NULL, `workflow_id` bigint(20) UNSIGNED NOT NULL, `step_type` enum('send_email','wait','condition','assign_owner','update_field','webhook') NOT NULL, `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Step-specific settings, e.g. {"template_id":12,"delay_hours":24}' CHECK (json_valid(`config`)), `sort_order` smallint(5) UNSIGNED NOT NULL DEFAULT 0, `parent_branch` varchar(20) DEFAULT NULL COMMENT 'yes/no branch label for condition steps' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `automation_workflows` -- CREATE TABLE `automation_workflows` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(191) NOT NULL, `trigger_type` varchar(80) NOT NULL COMMENT 'lead_created, invoice_overdue, appointment_completed...', `trigger_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`trigger_config`)), `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `billing_accounts` -- CREATE TABLE `billing_accounts` ( `id` bigint(20) UNSIGNED NOT NULL, `plan_name` varchar(80) NOT NULL DEFAULT 'Enterprise', `monthly_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `currency` char(3) NOT NULL DEFAULT 'CAD', `billing_cycle` enum('monthly','annually') NOT NULL DEFAULT 'annually', `card_last_four` char(4) DEFAULT NULL, `card_brand` varchar(30) DEFAULT NULL, `renews_on` date DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `business_lines` -- CREATE TABLE `business_lines` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(120) NOT NULL, `slug` varchar(60) NOT NULL, `legal_name` varchar(191) DEFAULT NULL, `brand_color` char(7) DEFAULT NULL COMMENT 'Hex color, e.g. #174890', `support_email` varchar(191) DEFAULT NULL, `support_phone` varchar(30) DEFAULT NULL, `website` varchar(191) DEFAULT NULL, `logo_url` varchar(255) DEFAULT NULL, `address_line1` varchar(191) DEFAULT NULL, `city` varchar(120) DEFAULT NULL, `province` varchar(120) DEFAULT NULL, `postal_code` varchar(20) DEFAULT NULL, `facebook_url` varchar(191) DEFAULT NULL, `instagram_url` varchar(191) DEFAULT NULL, `linkedin_url` varchar(191) DEFAULT NULL, `tiktok_url` varchar(191) DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `campaigns` -- CREATE TABLE `campaigns` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = sent to both brands', `email_template_id` bigint(20) UNSIGNED DEFAULT NULL, `parent_campaign_id` bigint(20) UNSIGNED DEFAULT NULL, `name` varchar(191) NOT NULL, `subject` varchar(255) NOT NULL, `body_html` mediumtext NOT NULL, `audience_segment` varchar(120) DEFAULT NULL COMMENT 'Denormalized label, e.g. "Active Clients"', `audience_filter` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Structured segment definition for re-evaluation' CHECK (json_valid(`audience_filter`)), `status` enum('draft','scheduled','sending','sent','cancelled') NOT NULL DEFAULT 'draft', `scheduled_at` datetime DEFAULT NULL, `sent_at` datetime DEFAULT NULL, `recipients_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `opens_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `clicks_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `unsubscribes_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL, `is_recurring_parent` tinyint(1) NOT NULL DEFAULT 0, `recurrence_rule` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Same shape as appointments.recurrence_rule: {frequency, interval, until}' CHECK (json_valid(`recurrence_rule`)), `audience_segment_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set when the audience was built from a saved segment rather than an ad-hoc filter' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `campaign_recipients` -- CREATE TABLE `campaign_recipients` ( `id` bigint(20) UNSIGNED NOT NULL, `campaign_id` bigint(20) UNSIGNED NOT NULL, `customer_id` bigint(20) UNSIGNED DEFAULT NULL, `lead_id` bigint(20) UNSIGNED DEFAULT NULL, `email_address` varchar(191) NOT NULL, `status` enum('pending','sent','delivered','opened','clicked','bounced','unsubscribed') NOT NULL DEFAULT 'pending', `sent_at` datetime DEFAULT NULL, `opened_at` datetime DEFAULT NULL, `clicked_at` datetime DEFAULT NULL, `bounced_at` datetime DEFAULT NULL, `replied_at` datetime DEFAULT NULL, `unsubscribe_token` char(64) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `chatbot_settings` -- CREATE TABLE `chatbot_settings` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `bot_name` varchar(80) NOT NULL DEFAULT 'A&B Assistant', `greeting_message` varchar(500) DEFAULT NULL, `is_live` tinyint(1) NOT NULL DEFAULT 0, `handoff_keywords` varchar(255) DEFAULT NULL, `allowed_origin` varchar(255) DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `comments` -- CREATE TABLE `comments` ( `id` bigint(20) UNSIGNED NOT NULL, `entity_type` varchar(40) NOT NULL, `entity_id` bigint(20) UNSIGNED NOT NULL, `parent_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set for a threaded reply to another comment', `body` varchar(2000) NOT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `companies` -- CREATE TABLE `companies` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `name` varchar(191) NOT NULL, `industry` varchar(120) DEFAULT NULL, `website` varchar(191) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `address_line1` varchar(191) DEFAULT NULL, `address_line2` varchar(191) DEFAULT NULL, `city` varchar(120) DEFAULT NULL, `province` varchar(120) DEFAULT NULL, `postal_code` varchar(20) DEFAULT NULL, `country` varchar(2) NOT NULL DEFAULT 'CA', `contract_status` enum('prospect','active','lapsed') NOT NULL DEFAULT 'prospect', `site_count` smallint(5) UNSIGNED NOT NULL DEFAULT 1, `annual_value` decimal(12,2) NOT NULL DEFAULT 0.00, `currency` char(3) NOT NULL DEFAULT 'CAD', `owner_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Account owner (users.id)', `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `conversations` -- CREATE TABLE `conversations` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `channel` enum('chatbot','voice_agent','ai_assistant') NOT NULL, `direction` enum('inbound','outbound') DEFAULT NULL COMMENT 'Voice only', `visitor_identifier` varchar(191) DEFAULT NULL COMMENT 'Phone number, session id, or anonymous cookie id', `external_ref` varchar(100) DEFAULT NULL COMMENT 'Twilio Call SID, WhatsApp message id, etc — for correlating with provider dashboards/webhooks', `related_lead_id` bigint(20) UNSIGNED DEFAULT NULL, `related_customer_id` bigint(20) UNSIGNED DEFAULT NULL, `outcome` enum('lead_created','booked','escalated','resolved','abandoned') DEFAULT NULL, `handled_by_user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set if escalated to a human', `duration_seconds` int(10) UNSIGNED DEFAULT NULL, `recording_url` varchar(500) DEFAULT NULL COMMENT 'Voice only', `transcript_text` mediumtext DEFAULT NULL COMMENT 'Full flattened transcript, separate from the per-turn conversation_messages rows — convenient for voice calls and AI summarization', `started_at` datetime NOT NULL DEFAULT current_timestamp(), `ended_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `conversation_messages` -- CREATE TABLE `conversation_messages` ( `id` bigint(20) UNSIGNED NOT NULL, `conversation_id` bigint(20) UNSIGNED NOT NULL, `sender` enum('visitor','bot','agent') NOT NULL, `message` mediumtext NOT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `customers` -- CREATE TABLE `customers` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `company_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set when this customer is billed under a B2B account', `source_lead_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'The lead this customer was converted from, if any', `import_batch_id` char(36) DEFAULT NULL, `service_plan_id` bigint(20) UNSIGNED DEFAULT NULL, `assigned_staff_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Primary caregiver / crew lead (users.id)', `first_name` varchar(100) NOT NULL, `last_name` varchar(100) DEFAULT NULL, `email` varchar(191) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `address_line1` varchar(191) DEFAULT NULL, `address_line2` varchar(191) DEFAULT NULL, `city` varchar(120) DEFAULT NULL, `province` varchar(120) DEFAULT NULL, `postal_code` varchar(20) DEFAULT NULL, `country` varchar(2) NOT NULL DEFAULT 'CA', `status` enum('active','paused','churned') NOT NULL DEFAULT 'active', `lifetime_value` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Cached rollup, recalculated from invoices/payments', `currency` char(3) NOT NULL DEFAULT 'CAD', `customer_since` date DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `customer_contacts` -- CREATE TABLE `customer_contacts` ( `id` bigint(20) UNSIGNED NOT NULL, `customer_id` bigint(20) UNSIGNED NOT NULL, `name` varchar(191) NOT NULL, `relationship` varchar(100) DEFAULT NULL COMMENT 'e.g. spouse, facilities manager', `phone` varchar(30) DEFAULT NULL, `email` varchar(191) DEFAULT NULL, `is_primary` tinyint(1) NOT NULL DEFAULT 0, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `departments` -- CREATE TABLE `departments` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = spans both brands (e.g. Finance, IT)', `name` varchar(120) NOT NULL, `description` varchar(255) DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `email_accounts` -- CREATE TABLE `email_accounts` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = shared/team mailbox', `business_line_id` bigint(20) UNSIGNED DEFAULT NULL, `email_address` varchar(191) NOT NULL, `provider` enum('gmail','smtp','outlook') NOT NULL DEFAULT 'smtp', `credentials_encrypted` text DEFAULT NULL COMMENT 'Encrypted OAuth token or SMTP secret, never plaintext', `is_active` tinyint(1) NOT NULL DEFAULT 1, `connected_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `email_messages` -- CREATE TABLE `email_messages` ( `id` bigint(20) UNSIGNED NOT NULL, `email_account_id` bigint(20) UNSIGNED DEFAULT NULL, `direction` enum('inbound','outbound') NOT NULL, `related_lead_id` bigint(20) UNSIGNED DEFAULT NULL, `related_customer_id` bigint(20) UNSIGNED DEFAULT NULL, `from_address` varchar(191) NOT NULL, `to_address` varchar(191) NOT NULL, `subject` varchar(255) DEFAULT NULL, `body_html` mediumtext DEFAULT NULL, `status` enum('queued','sent','delivered','failed','received','read') NOT NULL DEFAULT 'queued', `is_starred` tinyint(1) NOT NULL DEFAULT 0, `sent_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `email_queue` -- CREATE TABLE `email_queue` ( `id` bigint(20) UNSIGNED NOT NULL, `campaign_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = a personal/one-off email, not part of a campaign', `campaign_recipient_id` bigint(20) UNSIGNED DEFAULT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL, `to_email` varchar(191) NOT NULL, `to_name` varchar(191) DEFAULT NULL, `from_email` varchar(191) DEFAULT NULL COMMENT 'NULL = use the workspace default from config/mail.php', `subject` varchar(255) NOT NULL, `body_html` mediumtext NOT NULL, `status` enum('pending','processing','sent','failed') NOT NULL DEFAULT 'pending', `attempts` smallint(5) UNSIGNED NOT NULL DEFAULT 0, `last_error` varchar(500) DEFAULT NULL, `scheduled_for` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Worker only sends rows where this is <= NOW()', `sent_at` datetime DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `email_templates` -- CREATE TABLE `email_templates` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL, `name` varchar(150) NOT NULL, `category` enum('homecare','janitorial','transactional','general') NOT NULL DEFAULT 'general', `subject` varchar(255) NOT NULL, `body_html` mediumtext NOT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `email_unsubscribes` -- CREATE TABLE `email_unsubscribes` ( `id` bigint(20) UNSIGNED NOT NULL, `email` varchar(191) NOT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'NULL = unsubscribed from all A&B communications, not just one brand', `reason` varchar(255) DEFAULT NULL, `unsubscribed_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `email_verification_tokens` -- CREATE TABLE `email_verification_tokens` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `token_hash` char(64) NOT NULL, `new_email` varchar(191) DEFAULT NULL, `expires_at` datetime NOT NULL, `verified_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `import_batches` -- CREATE TABLE `import_batches` ( `id` char(36) NOT NULL COMMENT 'UUID, shared with leads.import_batch_id / customers.import_batch_id', `entity_type` varchar(40) NOT NULL COMMENT 'leads, customers, companies...', `original_filename` varchar(255) DEFAULT NULL, `total_rows` int(10) UNSIGNED NOT NULL DEFAULT 0, `created_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `skipped_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `failed_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `error_log` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Array of {row, message} for failed/skipped rows' CHECK (json_valid(`error_log`)), `imported_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `integrations` -- CREATE TABLE `integrations` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(120) NOT NULL COMMENT 'QuickBooks Online, Twilio, Slack, Google Calendar...', `slug` varchar(80) NOT NULL, `status` enum('connected','not_connected','error') NOT NULL DEFAULT 'not_connected', `credentials_encrypted` text DEFAULT NULL, `connected_by` bigint(20) UNSIGNED DEFAULT NULL, `connected_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `invoices` -- CREATE TABLE `invoices` ( `id` bigint(20) UNSIGNED NOT NULL, `customer_id` bigint(20) UNSIGNED DEFAULT NULL, `company_id` bigint(20) UNSIGNED DEFAULT NULL, `invoice_number` varchar(40) NOT NULL, `status` enum('draft','sent','paid','overdue','void') NOT NULL DEFAULT 'draft', `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `tax_total` decimal(12,2) NOT NULL DEFAULT 0.00, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `currency` char(3) NOT NULL DEFAULT 'CAD', `issue_date` date NOT NULL, `due_date` date DEFAULT NULL, `paid_at` datetime DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `invoice_items` -- CREATE TABLE `invoice_items` ( `id` bigint(20) UNSIGNED NOT NULL, `invoice_id` bigint(20) UNSIGNED NOT NULL, `description` varchar(255) NOT NULL, `quantity` decimal(10,2) NOT NULL DEFAULT 1.00, `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00, `line_total` decimal(12,2) NOT NULL DEFAULT 0.00, `sort_order` smallint(5) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `knowledge_base_articles` -- CREATE TABLE `knowledge_base_articles` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL, `category` varchar(100) DEFAULT NULL, `question` varchar(500) NOT NULL, `answer` text NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `view_count` int(10) UNSIGNED NOT NULL DEFAULT 0, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `leads` -- CREATE TABLE `leads` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `lead_source_id` bigint(20) UNSIGNED DEFAULT NULL, `lead_stage_id` bigint(20) UNSIGNED NOT NULL, `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium', `company_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set when this lead belongs to a B2B account', `owner_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Sales rep assigned (users.id)', `first_name` varchar(100) NOT NULL, `last_name` varchar(100) DEFAULT NULL, `email` varchar(191) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `estimated_value` decimal(12,2) NOT NULL DEFAULT 0.00, `currency` char(3) NOT NULL DEFAULT 'CAD', `notes` text DEFAULT NULL, `lost_reason` varchar(255) DEFAULT NULL, `converted_customer_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set once the lead becomes a customer', `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL, `merged_into_lead_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Set when this lead was merged into another (the survivor)', `import_batch_id` char(36) DEFAULT NULL COMMENT 'Groups rows created by the same CSV import run' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `lead_sources` -- CREATE TABLE `lead_sources` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(100) NOT NULL, `slug` varchar(100) NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `sort_order` smallint(5) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `lead_stages` -- CREATE TABLE `lead_stages` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(60) NOT NULL, `slug` varchar(60) NOT NULL, `color_hex` char(7) DEFAULT NULL, `is_won` tinyint(1) NOT NULL DEFAULT 0, `is_lost` tinyint(1) NOT NULL DEFAULT 0, `sort_order` smallint(5) UNSIGNED NOT NULL DEFAULT 0, `is_active` tinyint(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `lead_tasks` -- CREATE TABLE `lead_tasks` ( `id` bigint(20) UNSIGNED NOT NULL, `lead_id` bigint(20) UNSIGNED NOT NULL, `title` varchar(191) NOT NULL, `due_at` datetime DEFAULT NULL, `assigned_to` bigint(20) UNSIGNED DEFAULT NULL, `is_completed` tinyint(1) NOT NULL DEFAULT 0, `completed_at` datetime DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `login_attempts` -- CREATE TABLE `login_attempts` ( `id` bigint(20) UNSIGNED NOT NULL, `email` varchar(191) NOT NULL, `ip_address` varchar(45) NOT NULL, `was_successful` tinyint(1) NOT NULL DEFAULT 0, `user_agent` varchar(255) DEFAULT NULL, `attempted_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `notes` -- CREATE TABLE `notes` ( `id` bigint(20) UNSIGNED NOT NULL, `entity_type` varchar(40) NOT NULL, `entity_id` bigint(20) UNSIGNED NOT NULL, `body` text NOT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `notifications` -- CREATE TABLE `notifications` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `type` varchar(60) NOT NULL COMMENT 'new_lead, invoice_overdue, appointment_change, ai_escalation...', `title` varchar(191) NOT NULL, `message` varchar(500) NOT NULL, `related_entity_type` varchar(40) DEFAULT NULL, `related_entity_id` bigint(20) UNSIGNED DEFAULT NULL, `is_read` tinyint(1) NOT NULL DEFAULT 0, `read_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `notification_preferences` -- CREATE TABLE `notification_preferences` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `type` varchar(60) NOT NULL, `channel` enum('in_app','email','sms') NOT NULL DEFAULT 'in_app', `is_enabled` tinyint(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `password_histories` -- CREATE TABLE `password_histories` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `password_hash` varchar(255) NOT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `password_resets` -- CREATE TABLE `password_resets` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `token_hash` char(64) NOT NULL, `ip_address` varchar(45) DEFAULT NULL, `expires_at` datetime NOT NULL, `used_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `payments` -- CREATE TABLE `payments` ( `id` bigint(20) UNSIGNED NOT NULL, `invoice_id` bigint(20) UNSIGNED NOT NULL, `amount` decimal(12,2) NOT NULL, `currency` char(3) NOT NULL DEFAULT 'CAD', `method` enum('card','bank_transfer','cash','cheque','other') NOT NULL DEFAULT 'card', `reference` varchar(120) DEFAULT NULL COMMENT 'Processor transaction id', `paid_at` datetime NOT NULL DEFAULT current_timestamp(), `recorded_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `permissions` -- CREATE TABLE `permissions` ( `id` bigint(20) UNSIGNED NOT NULL, `module` varchar(80) NOT NULL, `action` varchar(80) NOT NULL COMMENT 'view, create, update, delete, export, manage...', `slug` varchar(160) NOT NULL COMMENT 'module.action, e.g. leads.create', `description` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `rate_limit_hits` -- CREATE TABLE `rate_limit_hits` ( `id` bigint(20) UNSIGNED NOT NULL, `limiter_key` varchar(191) NOT NULL COMMENT 'e.g. "api:203.0.113.4" or "upload:user:42"', `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `remember_tokens` -- CREATE TABLE `remember_tokens` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `selector` char(24) NOT NULL, `validator_hash` char(64) NOT NULL, `user_agent` varchar(255) DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `expires_at` datetime NOT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `roles` -- CREATE TABLE `roles` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(100) NOT NULL, `slug` varchar(100) NOT NULL, `description` varchar(255) DEFAULT NULL, `is_system` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = built-in role, cannot be deleted', `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `role_permissions` -- CREATE TABLE `role_permissions` ( `role_id` bigint(20) UNSIGNED NOT NULL, `permission_id` bigint(20) UNSIGNED NOT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `schema_migrations` -- CREATE TABLE `schema_migrations` ( `id` bigint(20) UNSIGNED NOT NULL, `migration` varchar(191) NOT NULL, `batch` int(10) UNSIGNED NOT NULL, `applied_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `service_plans` -- CREATE TABLE `service_plans` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `name` varchar(120) NOT NULL, `billing_frequency` enum('one_time','weekly','biweekly','monthly','annually') NOT NULL DEFAULT 'monthly', `base_price` decimal(12,2) NOT NULL DEFAULT 0.00, `currency` char(3) NOT NULL DEFAULT 'CAD', `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `service_types` -- CREATE TABLE `service_types` ( `id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL, `name` varchar(120) NOT NULL, `default_duration_minutes` smallint(5) UNSIGNED NOT NULL DEFAULT 60, `default_price` decimal(12,2) NOT NULL DEFAULT 0.00, `currency` char(3) NOT NULL DEFAULT 'CAD', `is_active` tinyint(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `sessions` -- CREATE TABLE `sessions` ( `id` char(128) NOT NULL COMMENT 'PHP session id', `user_id` bigint(20) UNSIGNED DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `payload` mediumtext NOT NULL, `last_activity` int(10) UNSIGNED NOT NULL COMMENT 'Unix timestamp, indexed for garbage collection' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `settings` -- CREATE TABLE `settings` ( `id` bigint(20) UNSIGNED NOT NULL, `setting_key` varchar(120) NOT NULL, `setting_value` text DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `smtp_settings` -- CREATE TABLE `smtp_settings` ( `id` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Always row id=1 — single workspace-wide config', `host` varchar(191) DEFAULT NULL, `port` smallint(5) UNSIGNED DEFAULT NULL, `encryption` enum('tls','ssl','none') NOT NULL DEFAULT 'tls', `username` varchar(191) DEFAULT NULL, `password_encrypted` varchar(500) DEFAULT NULL, `from_address` varchar(191) DEFAULT NULL, `from_name` varchar(191) DEFAULT NULL, `is_configured` tinyint(1) NOT NULL DEFAULT 0, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ; -- -------------------------------------------------------- -- -- Table structure for table `taggables` -- CREATE TABLE `taggables` ( `tag_id` bigint(20) UNSIGNED NOT NULL, `entity_type` varchar(40) NOT NULL, `entity_id` bigint(20) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `tags` -- CREATE TABLE `tags` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(80) NOT NULL, `slug` varchar(80) NOT NULL, `color_hex` char(7) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `teams` -- CREATE TABLE `teams` ( `id` bigint(20) UNSIGNED NOT NULL, `department_id` bigint(20) UNSIGNED DEFAULT NULL, `business_line_id` bigint(20) UNSIGNED DEFAULT NULL, `name` varchar(120) NOT NULL, `description` varchar(255) DEFAULT NULL, `lead_user_id` bigint(20) UNSIGNED DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `team_members` -- CREATE TABLE `team_members` ( `team_id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `joined_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `training_examples` -- CREATE TABLE `training_examples` ( `id` bigint(20) UNSIGNED NOT NULL, `feature` enum('ai_assistant','chatbot','voice_agent') NOT NULL, `prompt_text` text NOT NULL, `ideal_response` text NOT NULL, `notes` varchar(500) DEFAULT NULL, `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `two_factor_recovery_codes` -- CREATE TABLE `two_factor_recovery_codes` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `code_hash` char(64) NOT NULL, `used_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` bigint(20) UNSIGNED NOT NULL, `role_id` bigint(20) UNSIGNED DEFAULT NULL, `department_id` bigint(20) UNSIGNED DEFAULT NULL, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, `email` varchar(191) NOT NULL, `email_verified_at` datetime DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `job_title` varchar(120) DEFAULT NULL, `password_hash` varchar(255) NOT NULL, `password_changed_at` datetime DEFAULT NULL, `failed_login_count` smallint(5) UNSIGNED NOT NULL DEFAULT 0, `locked_until` datetime DEFAULT NULL, `avatar_path` varchar(255) DEFAULT NULL, `status` enum('active','invited','suspended') NOT NULL DEFAULT 'invited', `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0, `two_factor_secret` varchar(191) DEFAULT NULL, `last_login_at` datetime DEFAULT NULL, `last_login_ip` varchar(45) DEFAULT NULL, `timezone` varchar(60) NOT NULL DEFAULT 'America/Toronto', `created_by` bigint(20) UNSIGNED DEFAULT NULL, `updated_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_business_lines` -- CREATE TABLE `user_business_lines` ( `user_id` bigint(20) UNSIGNED NOT NULL, `business_line_id` bigint(20) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_invitations` -- CREATE TABLE `user_invitations` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `token_hash` char(64) NOT NULL COMMENT 'SHA-256 of the raw token sent by email', `invited_by` bigint(20) UNSIGNED DEFAULT NULL, `expires_at` datetime NOT NULL, `accepted_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_permission_overrides` -- CREATE TABLE `user_permission_overrides` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `permission_id` bigint(20) UNSIGNED NOT NULL, `effect` enum('allow','deny') NOT NULL DEFAULT 'allow', `created_by` bigint(20) UNSIGNED DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_preferences` -- CREATE TABLE `user_preferences` ( `user_id` bigint(20) UNSIGNED NOT NULL, `pref_key` varchar(80) NOT NULL, `pref_value` text DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `webhook_events` -- CREATE TABLE `webhook_events` ( `id` bigint(20) UNSIGNED NOT NULL, `provider` varchar(60) NOT NULL COMMENT 'twilio, meta, whatsapp, google...', `event_type` varchar(100) DEFAULT NULL, `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)), `processed` tinyint(1) NOT NULL DEFAULT 0, `error_message` varchar(500) DEFAULT NULL, `received_at` datetime NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table `activities` -- ALTER TABLE `activities` ADD PRIMARY KEY (`id`), ADD KEY `idx_activities_entity` (`entity_type`,`entity_id`,`created_at`), ADD KEY `idx_activities_caused_by` (`caused_by`); -- -- Indexes for table `activity_logs` -- ALTER TABLE `activity_logs` ADD PRIMARY KEY (`id`), ADD KEY `idx_activity_logs_user` (`user_id`), ADD KEY `idx_activity_logs_category_time` (`category`,`created_at`), ADD KEY `idx_activity_logs_entity` (`entity_type`,`entity_id`); -- -- Indexes for table `ai_prompts` -- ALTER TABLE `ai_prompts` ADD PRIMARY KEY (`id`), ADD KEY `idx_ai_prompts_feature_active` (`feature`,`is_active`), ADD KEY `fk_ai_prompts_business_line` (`business_line_id`), ADD KEY `fk_ai_prompts_created_by` (`created_by`), ADD KEY `fk_ai_prompts_updated_by` (`updated_by`); -- -- Indexes for table `api_keys` -- ALTER TABLE `api_keys` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_api_keys_hash` (`key_hash`), ADD KEY `idx_api_keys_created_by` (`created_by`); -- -- Indexes for table `appointments` -- ALTER TABLE `appointments` ADD PRIMARY KEY (`id`), ADD KEY `idx_appointments_business_line` (`business_line_id`), ADD KEY `idx_appointments_customer` (`customer_id`), ADD KEY `idx_appointments_lead` (`lead_id`), ADD KEY `idx_appointments_staff_time` (`assigned_staff_id`,`scheduled_start`), ADD KEY `idx_appointments_status` (`status`), ADD KEY `idx_appointments_deleted_at` (`deleted_at`), ADD KEY `fk_appointments_service_type` (`service_type_id`), ADD KEY `fk_appointments_created_by` (`created_by`), ADD KEY `fk_appointments_updated_by` (`updated_by`), ADD KEY `idx_appointments_parent` (`parent_appointment_id`), ADD KEY `idx_appointments_business_line_scheduled` (`business_line_id`,`scheduled_start`); -- -- Indexes for table `attachments` -- ALTER TABLE `attachments` ADD PRIMARY KEY (`id`), ADD KEY `idx_attachments_entity` (`entity_type`,`entity_id`), ADD KEY `fk_attachments_uploaded_by` (`uploaded_by`); -- -- Indexes for table `audience_segments` -- ALTER TABLE `audience_segments` ADD PRIMARY KEY (`id`), ADD KEY `fk_audience_segments_created_by` (`created_by`); -- -- Indexes for table `automation_runs` -- ALTER TABLE `automation_runs` ADD PRIMARY KEY (`id`), ADD KEY `idx_automation_runs_workflow` (`workflow_id`), ADD KEY `idx_automation_runs_entity` (`entity_type`,`entity_id`), ADD KEY `fk_automation_runs_step` (`current_step_id`), ADD KEY `idx_automation_runs_due` (`status`,`next_step_at`), ADD KEY `idx_automation_runs_started_at` (`started_at`); -- -- Indexes for table `automation_steps` -- ALTER TABLE `automation_steps` ADD PRIMARY KEY (`id`), ADD KEY `idx_automation_steps_workflow` (`workflow_id`,`sort_order`); -- -- Indexes for table `automation_workflows` -- ALTER TABLE `automation_workflows` ADD PRIMARY KEY (`id`), ADD KEY `idx_automation_workflows_trigger` (`trigger_type`), ADD KEY `fk_automation_workflows_created_by` (`created_by`), ADD KEY `fk_automation_workflows_updated_by` (`updated_by`); -- -- Indexes for table `billing_accounts` -- ALTER TABLE `billing_accounts` ADD PRIMARY KEY (`id`); -- -- Indexes for table `business_lines` -- ALTER TABLE `business_lines` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_business_lines_slug` (`slug`); -- -- Indexes for table `campaigns` -- ALTER TABLE `campaigns` ADD PRIMARY KEY (`id`), ADD KEY `idx_campaigns_business_line` (`business_line_id`), ADD KEY `idx_campaigns_status` (`status`), ADD KEY `fk_campaigns_template` (`email_template_id`), ADD KEY `fk_campaigns_created_by` (`created_by`), ADD KEY `fk_campaigns_updated_by` (`updated_by`), ADD KEY `idx_campaigns_parent` (`parent_campaign_id`), ADD KEY `idx_campaigns_sent_at` (`sent_at`), ADD KEY `idx_campaigns_created_at` (`created_at`); -- -- Indexes for table `campaign_recipients` -- ALTER TABLE `campaign_recipients` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_campaign_recipients_unsub_token` (`unsubscribe_token`), ADD KEY `idx_campaign_recipients_campaign` (`campaign_id`), ADD KEY `idx_campaign_recipients_customer` (`customer_id`), ADD KEY `idx_campaign_recipients_lead` (`lead_id`); -- -- Indexes for table `chatbot_settings` -- ALTER TABLE `chatbot_settings` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_chatbot_settings_business_line` (`business_line_id`), ADD KEY `fk_chatbot_settings_updated_by` (`updated_by`); -- -- Indexes for table `comments` -- ALTER TABLE `comments` ADD PRIMARY KEY (`id`), ADD KEY `idx_comments_entity` (`entity_type`,`entity_id`), ADD KEY `idx_comments_parent` (`parent_id`), ADD KEY `fk_comments_created_by` (`created_by`); -- -- Indexes for table `companies` -- ALTER TABLE `companies` ADD PRIMARY KEY (`id`), ADD KEY `idx_companies_business_line` (`business_line_id`), ADD KEY `idx_companies_owner` (`owner_id`), ADD KEY `idx_companies_status` (`contract_status`), ADD KEY `idx_companies_deleted_at` (`deleted_at`), ADD KEY `fk_companies_created_by` (`created_by`), ADD KEY `fk_companies_updated_by` (`updated_by`); ALTER TABLE `companies` ADD FULLTEXT KEY `ftx_companies_name` (`name`); -- -- Indexes for table `conversations` -- ALTER TABLE `conversations` ADD PRIMARY KEY (`id`), ADD KEY `idx_conversations_business_line` (`business_line_id`), ADD KEY `idx_conversations_channel` (`channel`), ADD KEY `idx_conversations_lead` (`related_lead_id`), ADD KEY `idx_conversations_customer` (`related_customer_id`), ADD KEY `fk_conversations_handled_by` (`handled_by_user_id`), ADD KEY `idx_conversations_external_ref` (`external_ref`), ADD KEY `idx_conversations_started_at` (`started_at`); -- -- Indexes for table `conversation_messages` -- ALTER TABLE `conversation_messages` ADD PRIMARY KEY (`id`), ADD KEY `idx_conversation_messages_conversation` (`conversation_id`,`created_at`); -- -- Indexes for table `customers` -- ALTER TABLE `customers` ADD PRIMARY KEY (`id`), ADD KEY `idx_customers_business_line` (`business_line_id`), ADD KEY `idx_customers_company` (`company_id`), ADD KEY `idx_customers_status` (`status`), ADD KEY `idx_customers_staff` (`assigned_staff_id`), ADD KEY `idx_customers_deleted_at` (`deleted_at`), ADD KEY `fk_customers_source_lead` (`source_lead_id`), ADD KEY `fk_customers_service_plan` (`service_plan_id`), ADD KEY `fk_customers_created_by` (`created_by`), ADD KEY `fk_customers_updated_by` (`updated_by`), ADD KEY `idx_customers_import_batch` (`import_batch_id`), ADD KEY `idx_customers_business_line_since` (`business_line_id`,`customer_since`); ALTER TABLE `customers` ADD FULLTEXT KEY `ftx_customers_name_email` (`first_name`,`last_name`,`email`); -- -- Indexes for table `customer_contacts` -- ALTER TABLE `customer_contacts` ADD PRIMARY KEY (`id`), ADD KEY `idx_customer_contacts_customer` (`customer_id`); -- -- Indexes for table `departments` -- ALTER TABLE `departments` ADD PRIMARY KEY (`id`), ADD KEY `idx_departments_business_line` (`business_line_id`), ADD KEY `idx_departments_deleted_at` (`deleted_at`), ADD KEY `fk_departments_created_by` (`created_by`), ADD KEY `fk_departments_updated_by` (`updated_by`); -- -- Indexes for table `email_accounts` -- ALTER TABLE `email_accounts` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_email_accounts_address` (`email_address`), ADD KEY `idx_email_accounts_user` (`user_id`), ADD KEY `fk_email_accounts_business_line` (`business_line_id`); -- -- Indexes for table `email_messages` -- ALTER TABLE `email_messages` ADD PRIMARY KEY (`id`), ADD KEY `idx_email_messages_account` (`email_account_id`,`created_at`), ADD KEY `idx_email_messages_lead` (`related_lead_id`), ADD KEY `idx_email_messages_customer` (`related_customer_id`); ALTER TABLE `email_messages` ADD FULLTEXT KEY `ftx_email_messages_subject` (`subject`); -- -- Indexes for table `email_queue` -- ALTER TABLE `email_queue` ADD PRIMARY KEY (`id`), ADD KEY `idx_email_queue_status_schedule` (`status`,`scheduled_for`), ADD KEY `idx_email_queue_campaign` (`campaign_id`), ADD KEY `fk_email_queue_recipient` (`campaign_recipient_id`), ADD KEY `fk_email_queue_created_by` (`created_by`), ADD KEY `idx_email_queue_created_at` (`created_at`), ADD KEY `fk_email_queue_business_line` (`business_line_id`); -- -- Indexes for table `email_templates` -- ALTER TABLE `email_templates` ADD PRIMARY KEY (`id`), ADD KEY `idx_email_templates_business_line` (`business_line_id`), ADD KEY `idx_email_templates_category` (`category`), ADD KEY `fk_email_templates_created_by` (`created_by`), ADD KEY `fk_email_templates_updated_by` (`updated_by`); -- -- Indexes for table `email_unsubscribes` -- ALTER TABLE `email_unsubscribes` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_email_unsubscribes_email_bl` (`email`,`business_line_id`), ADD KEY `fk_email_unsubscribes_business_line` (`business_line_id`); -- -- Indexes for table `email_verification_tokens` -- ALTER TABLE `email_verification_tokens` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_email_verification_token` (`token_hash`), ADD KEY `idx_email_verification_user` (`user_id`); -- -- Indexes for table `import_batches` -- ALTER TABLE `import_batches` ADD PRIMARY KEY (`id`), ADD KEY `idx_import_batches_entity` (`entity_type`), ADD KEY `fk_import_batches_user` (`imported_by`); -- -- Indexes for table `integrations` -- ALTER TABLE `integrations` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_integrations_slug` (`slug`), ADD KEY `fk_integrations_connected_by` (`connected_by`); -- -- Indexes for table `invoices` -- ALTER TABLE `invoices` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_invoices_number` (`invoice_number`), ADD KEY `idx_invoices_customer` (`customer_id`), ADD KEY `idx_invoices_company` (`company_id`), ADD KEY `idx_invoices_status` (`status`), ADD KEY `fk_invoices_created_by` (`created_by`), ADD KEY `fk_invoices_updated_by` (`updated_by`); -- -- Indexes for table `invoice_items` -- ALTER TABLE `invoice_items` ADD PRIMARY KEY (`id`), ADD KEY `idx_invoice_items_invoice` (`invoice_id`); -- -- Indexes for table `knowledge_base_articles` -- ALTER TABLE `knowledge_base_articles` ADD PRIMARY KEY (`id`), ADD KEY `idx_knowledge_base_business_line` (`business_line_id`), ADD KEY `fk_knowledge_base_created_by` (`created_by`); ALTER TABLE `knowledge_base_articles` ADD FULLTEXT KEY `ft_knowledge_base_question` (`question`,`answer`); -- -- Indexes for table `leads` -- ALTER TABLE `leads` ADD PRIMARY KEY (`id`), ADD KEY `idx_leads_business_line` (`business_line_id`), ADD KEY `idx_leads_stage` (`lead_stage_id`), ADD KEY `idx_leads_source` (`lead_source_id`), ADD KEY `idx_leads_owner` (`owner_id`), ADD KEY `idx_leads_company` (`company_id`), ADD KEY `idx_leads_deleted_at` (`deleted_at`), ADD KEY `fk_leads_created_by` (`created_by`), ADD KEY `fk_leads_updated_by` (`updated_by`), ADD KEY `idx_leads_priority` (`priority`), ADD KEY `idx_leads_merged_into` (`merged_into_lead_id`), ADD KEY `idx_leads_import_batch` (`import_batch_id`), ADD KEY `idx_leads_business_line_created` (`business_line_id`,`created_at`); ALTER TABLE `leads` ADD FULLTEXT KEY `ftx_leads_name_email` (`first_name`,`last_name`,`email`); -- -- Indexes for table `lead_sources` -- ALTER TABLE `lead_sources` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_lead_sources_slug` (`slug`); -- -- Indexes for table `lead_stages` -- ALTER TABLE `lead_stages` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_lead_stages_slug` (`slug`); -- -- Indexes for table `lead_tasks` -- ALTER TABLE `lead_tasks` ADD PRIMARY KEY (`id`), ADD KEY `idx_lead_tasks_lead` (`lead_id`), ADD KEY `idx_lead_tasks_assigned` (`assigned_to`), ADD KEY `fk_lead_tasks_created_by` (`created_by`); -- -- Indexes for table `login_attempts` -- ALTER TABLE `login_attempts` ADD PRIMARY KEY (`id`), ADD KEY `idx_login_attempts_email_time` (`email`,`attempted_at`), ADD KEY `idx_login_attempts_ip_time` (`ip_address`,`attempted_at`); -- -- Indexes for table `notes` -- ALTER TABLE `notes` ADD PRIMARY KEY (`id`), ADD KEY `idx_notes_entity` (`entity_type`,`entity_id`), ADD KEY `fk_notes_created_by` (`created_by`); -- -- Indexes for table `notifications` -- ALTER TABLE `notifications` ADD PRIMARY KEY (`id`), ADD KEY `idx_notifications_user_unread` (`user_id`,`is_read`,`created_at`); -- -- Indexes for table `notification_preferences` -- ALTER TABLE `notification_preferences` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_notification_prefs` (`user_id`,`type`,`channel`); -- -- Indexes for table `password_histories` -- ALTER TABLE `password_histories` ADD PRIMARY KEY (`id`), ADD KEY `idx_password_histories_user` (`user_id`,`created_at`); -- -- Indexes for table `password_resets` -- ALTER TABLE `password_resets` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_password_resets_token` (`token_hash`), ADD KEY `idx_password_resets_user` (`user_id`); -- -- Indexes for table `payments` -- ALTER TABLE `payments` ADD PRIMARY KEY (`id`), ADD KEY `idx_payments_invoice` (`invoice_id`), ADD KEY `fk_payments_recorded_by` (`recorded_by`), ADD KEY `idx_payments_paid_at` (`paid_at`); -- -- Indexes for table `permissions` -- ALTER TABLE `permissions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_permissions_slug` (`slug`), ADD KEY `idx_permissions_module` (`module`); -- -- Indexes for table `rate_limit_hits` -- ALTER TABLE `rate_limit_hits` ADD PRIMARY KEY (`id`), ADD KEY `idx_rate_limit_hits_key_time` (`limiter_key`,`created_at`); -- -- Indexes for table `remember_tokens` -- ALTER TABLE `remember_tokens` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_remember_tokens_selector` (`selector`), ADD KEY `idx_remember_tokens_user` (`user_id`); -- -- Indexes for table `roles` -- ALTER TABLE `roles` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_roles_slug` (`slug`); -- -- Indexes for table `role_permissions` -- ALTER TABLE `role_permissions` ADD PRIMARY KEY (`role_id`,`permission_id`), ADD KEY `fk_role_permissions_permission` (`permission_id`); -- -- Indexes for table `schema_migrations` -- ALTER TABLE `schema_migrations` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_schema_migrations_migration` (`migration`); -- -- Indexes for table `service_plans` -- ALTER TABLE `service_plans` ADD PRIMARY KEY (`id`), ADD KEY `idx_service_plans_business_line` (`business_line_id`); -- -- Indexes for table `service_types` -- ALTER TABLE `service_types` ADD PRIMARY KEY (`id`), ADD KEY `idx_service_types_business_line` (`business_line_id`); -- -- Indexes for table `sessions` -- ALTER TABLE `sessions` ADD PRIMARY KEY (`id`), ADD KEY `idx_sessions_user` (`user_id`), ADD KEY `idx_sessions_last_activity` (`last_activity`); -- -- Indexes for table `settings` -- ALTER TABLE `settings` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_settings_key` (`setting_key`), ADD KEY `fk_settings_updated_by` (`updated_by`); -- -- Indexes for table `smtp_settings` -- ALTER TABLE `smtp_settings` ADD PRIMARY KEY (`id`), ADD KEY `fk_smtp_settings_updated_by` (`updated_by`); -- -- Indexes for table `taggables` -- ALTER TABLE `taggables` ADD PRIMARY KEY (`tag_id`,`entity_type`,`entity_id`), ADD KEY `idx_taggables_entity` (`entity_type`,`entity_id`); -- -- Indexes for table `tags` -- ALTER TABLE `tags` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_tags_slug` (`slug`); -- -- Indexes for table `teams` -- ALTER TABLE `teams` ADD PRIMARY KEY (`id`), ADD KEY `idx_teams_department` (`department_id`), ADD KEY `idx_teams_business_line` (`business_line_id`), ADD KEY `idx_teams_lead` (`lead_user_id`), ADD KEY `idx_teams_deleted_at` (`deleted_at`), ADD KEY `fk_teams_created_by` (`created_by`), ADD KEY `fk_teams_updated_by` (`updated_by`); -- -- Indexes for table `team_members` -- ALTER TABLE `team_members` ADD PRIMARY KEY (`team_id`,`user_id`), ADD KEY `idx_team_members_user` (`user_id`); -- -- Indexes for table `training_examples` -- ALTER TABLE `training_examples` ADD PRIMARY KEY (`id`), ADD KEY `fk_training_examples_created_by` (`created_by`); -- -- Indexes for table `two_factor_recovery_codes` -- ALTER TABLE `two_factor_recovery_codes` ADD PRIMARY KEY (`id`), ADD KEY `idx_2fa_recovery_user` (`user_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_users_email` (`email`), ADD KEY `idx_users_role` (`role_id`), ADD KEY `idx_users_status` (`status`), ADD KEY `idx_users_deleted_at` (`deleted_at`), ADD KEY `fk_users_created_by` (`created_by`), ADD KEY `fk_users_updated_by` (`updated_by`), ADD KEY `idx_users_department` (`department_id`); -- -- Indexes for table `user_business_lines` -- ALTER TABLE `user_business_lines` ADD PRIMARY KEY (`user_id`,`business_line_id`), ADD KEY `fk_ubl_business_line` (`business_line_id`); -- -- Indexes for table `user_invitations` -- ALTER TABLE `user_invitations` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_user_invitations_token` (`token_hash`), ADD KEY `idx_user_invitations_user` (`user_id`), ADD KEY `fk_invitations_invited_by` (`invited_by`); -- -- Indexes for table `user_permission_overrides` -- ALTER TABLE `user_permission_overrides` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uk_user_permission` (`user_id`,`permission_id`), ADD KEY `fk_upo_permission` (`permission_id`), ADD KEY `fk_upo_created_by` (`created_by`); -- -- Indexes for table `user_preferences` -- ALTER TABLE `user_preferences` ADD PRIMARY KEY (`user_id`,`pref_key`); -- -- Indexes for table `webhook_events` -- ALTER TABLE `webhook_events` ADD PRIMARY KEY (`id`), ADD KEY `idx_webhook_events_provider` (`provider`,`received_at`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `activities` -- ALTER TABLE `activities` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `activity_logs` -- ALTER TABLE `activity_logs` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `ai_prompts` -- ALTER TABLE `ai_prompts` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `api_keys` -- ALTER TABLE `api_keys` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `appointments` -- ALTER TABLE `appointments` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `attachments` -- ALTER TABLE `attachments` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `audience_segments` -- ALTER TABLE `audience_segments` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `automation_runs` -- ALTER TABLE `automation_runs` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `automation_steps` -- ALTER TABLE `automation_steps` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `automation_workflows` -- ALTER TABLE `automation_workflows` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `billing_accounts` -- ALTER TABLE `billing_accounts` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `business_lines` -- ALTER TABLE `business_lines` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `campaigns` -- ALTER TABLE `campaigns` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `campaign_recipients` -- ALTER TABLE `campaign_recipients` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `chatbot_settings` -- ALTER TABLE `chatbot_settings` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `comments` -- ALTER TABLE `comments` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `companies` -- ALTER TABLE `companies` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `conversations` -- ALTER TABLE `conversations` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `conversation_messages` -- ALTER TABLE `conversation_messages` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `customers` -- ALTER TABLE `customers` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `customer_contacts` -- ALTER TABLE `customer_contacts` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `departments` -- ALTER TABLE `departments` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `email_accounts` -- ALTER TABLE `email_accounts` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `email_messages` -- ALTER TABLE `email_messages` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `email_queue` -- ALTER TABLE `email_queue` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `email_templates` -- ALTER TABLE `email_templates` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `email_unsubscribes` -- ALTER TABLE `email_unsubscribes` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `email_verification_tokens` -- ALTER TABLE `email_verification_tokens` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `integrations` -- ALTER TABLE `integrations` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `invoices` -- ALTER TABLE `invoices` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `invoice_items` -- ALTER TABLE `invoice_items` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `knowledge_base_articles` -- ALTER TABLE `knowledge_base_articles` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `leads` -- ALTER TABLE `leads` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `lead_sources` -- ALTER TABLE `lead_sources` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `lead_stages` -- ALTER TABLE `lead_stages` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `lead_tasks` -- ALTER TABLE `lead_tasks` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `login_attempts` -- ALTER TABLE `login_attempts` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `notes` -- ALTER TABLE `notes` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `notifications` -- ALTER TABLE `notifications` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `notification_preferences` -- ALTER TABLE `notification_preferences` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `password_histories` -- ALTER TABLE `password_histories` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `password_resets` -- ALTER TABLE `password_resets` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `payments` -- ALTER TABLE `payments` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `permissions` -- ALTER TABLE `permissions` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `rate_limit_hits` -- ALTER TABLE `rate_limit_hits` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `remember_tokens` -- ALTER TABLE `remember_tokens` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `roles` -- ALTER TABLE `roles` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `schema_migrations` -- ALTER TABLE `schema_migrations` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `service_plans` -- ALTER TABLE `service_plans` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `service_types` -- ALTER TABLE `service_types` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `settings` -- ALTER TABLE `settings` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `tags` -- ALTER TABLE `tags` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `teams` -- ALTER TABLE `teams` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `training_examples` -- ALTER TABLE `training_examples` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `two_factor_recovery_codes` -- ALTER TABLE `two_factor_recovery_codes` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `user_invitations` -- ALTER TABLE `user_invitations` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `user_permission_overrides` -- ALTER TABLE `user_permission_overrides` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `webhook_events` -- ALTER TABLE `webhook_events` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `activities` -- ALTER TABLE `activities` ADD CONSTRAINT `fk_activities_caused_by` FOREIGN KEY (`caused_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `activity_logs` -- ALTER TABLE `activity_logs` ADD CONSTRAINT `fk_activity_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `ai_prompts` -- ALTER TABLE `ai_prompts` ADD CONSTRAINT `fk_ai_prompts_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_ai_prompts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_ai_prompts_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `api_keys` -- ALTER TABLE `api_keys` ADD CONSTRAINT `fk_api_keys_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `appointments` -- ALTER TABLE `appointments` ADD CONSTRAINT `fk_appointments_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_parent` FOREIGN KEY (`parent_appointment_id`) REFERENCES `appointments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_service_type` FOREIGN KEY (`service_type_id`) REFERENCES `service_types` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_staff` FOREIGN KEY (`assigned_staff_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_appointments_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `attachments` -- ALTER TABLE `attachments` ADD CONSTRAINT `fk_attachments_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `audience_segments` -- ALTER TABLE `audience_segments` ADD CONSTRAINT `fk_audience_segments_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `automation_runs` -- ALTER TABLE `automation_runs` ADD CONSTRAINT `fk_automation_runs_step` FOREIGN KEY (`current_step_id`) REFERENCES `automation_steps` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_automation_runs_workflow` FOREIGN KEY (`workflow_id`) REFERENCES `automation_workflows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `automation_steps` -- ALTER TABLE `automation_steps` ADD CONSTRAINT `fk_automation_steps_workflow` FOREIGN KEY (`workflow_id`) REFERENCES `automation_workflows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `automation_workflows` -- ALTER TABLE `automation_workflows` ADD CONSTRAINT `fk_automation_workflows_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_automation_workflows_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `campaigns` -- ALTER TABLE `campaigns` ADD CONSTRAINT `fk_campaigns_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_campaigns_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_campaigns_template` FOREIGN KEY (`email_template_id`) REFERENCES `email_templates` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_campaigns_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `campaign_recipients` -- ALTER TABLE `campaign_recipients` ADD CONSTRAINT `fk_campaign_recipients_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_campaign_recipients_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_campaign_recipients_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `chatbot_settings` -- ALTER TABLE `chatbot_settings` ADD CONSTRAINT `fk_chatbot_settings_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_chatbot_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `comments` -- ALTER TABLE `comments` ADD CONSTRAINT `fk_comments_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_comments_parent` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `companies` -- ALTER TABLE `companies` ADD CONSTRAINT `fk_companies_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_companies_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_companies_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_companies_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `conversations` -- ALTER TABLE `conversations` ADD CONSTRAINT `fk_conversations_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_conversations_customer` FOREIGN KEY (`related_customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_conversations_handled_by` FOREIGN KEY (`handled_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_conversations_lead` FOREIGN KEY (`related_lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `conversation_messages` -- ALTER TABLE `conversation_messages` ADD CONSTRAINT `fk_conversation_messages_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `customers` -- ALTER TABLE `customers` ADD CONSTRAINT `fk_customers_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_customers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_customers_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_customers_service_plan` FOREIGN KEY (`service_plan_id`) REFERENCES `service_plans` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_customers_source_lead` FOREIGN KEY (`source_lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_customers_staff` FOREIGN KEY (`assigned_staff_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_customers_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `customer_contacts` -- ALTER TABLE `customer_contacts` ADD CONSTRAINT `fk_customer_contacts_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `departments` -- ALTER TABLE `departments` ADD CONSTRAINT `fk_departments_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_departments_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_departments_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `email_accounts` -- ALTER TABLE `email_accounts` ADD CONSTRAINT `fk_email_accounts_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_accounts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `email_messages` -- ALTER TABLE `email_messages` ADD CONSTRAINT `fk_email_messages_account` FOREIGN KEY (`email_account_id`) REFERENCES `email_accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_messages_customer` FOREIGN KEY (`related_customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_messages_lead` FOREIGN KEY (`related_lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `email_queue` -- ALTER TABLE `email_queue` ADD CONSTRAINT `fk_email_queue_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_queue_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_queue_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_queue_recipient` FOREIGN KEY (`campaign_recipient_id`) REFERENCES `campaign_recipients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `email_templates` -- ALTER TABLE `email_templates` ADD CONSTRAINT `fk_email_templates_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_templates_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_email_templates_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `email_unsubscribes` -- ALTER TABLE `email_unsubscribes` ADD CONSTRAINT `fk_email_unsubscribes_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `email_verification_tokens` -- ALTER TABLE `email_verification_tokens` ADD CONSTRAINT `fk_email_verification_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `import_batches` -- ALTER TABLE `import_batches` ADD CONSTRAINT `fk_import_batches_user` FOREIGN KEY (`imported_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `integrations` -- ALTER TABLE `integrations` ADD CONSTRAINT `fk_integrations_connected_by` FOREIGN KEY (`connected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `invoices` -- ALTER TABLE `invoices` ADD CONSTRAINT `fk_invoices_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_invoices_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_invoices_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_invoices_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `invoice_items` -- ALTER TABLE `invoice_items` ADD CONSTRAINT `fk_invoice_items_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `knowledge_base_articles` -- ALTER TABLE `knowledge_base_articles` ADD CONSTRAINT `fk_knowledge_base_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_knowledge_base_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `leads` -- ALTER TABLE `leads` ADD CONSTRAINT `fk_leads_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_merged_into` FOREIGN KEY (`merged_into_lead_id`) REFERENCES `leads` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_owner` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_source` FOREIGN KEY (`lead_source_id`) REFERENCES `lead_sources` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_stage` FOREIGN KEY (`lead_stage_id`) REFERENCES `lead_stages` (`id`) ON UPDATE CASCADE, ADD CONSTRAINT `fk_leads_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `lead_tasks` -- ALTER TABLE `lead_tasks` ADD CONSTRAINT `fk_lead_tasks_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_lead_tasks_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_lead_tasks_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `notes` -- ALTER TABLE `notes` ADD CONSTRAINT `fk_notes_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `notifications` -- ALTER TABLE `notifications` ADD CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `notification_preferences` -- ALTER TABLE `notification_preferences` ADD CONSTRAINT `fk_notification_prefs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `password_histories` -- ALTER TABLE `password_histories` ADD CONSTRAINT `fk_password_histories_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `password_resets` -- ALTER TABLE `password_resets` ADD CONSTRAINT `fk_password_resets_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `payments` -- ALTER TABLE `payments` ADD CONSTRAINT `fk_payments_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_payments_recorded_by` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `remember_tokens` -- ALTER TABLE `remember_tokens` ADD CONSTRAINT `fk_remember_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `role_permissions` -- ALTER TABLE `role_permissions` ADD CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `service_plans` -- ALTER TABLE `service_plans` ADD CONSTRAINT `fk_service_plans_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE; -- -- Constraints for table `service_types` -- ALTER TABLE `service_types` ADD CONSTRAINT `fk_service_types_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON UPDATE CASCADE; -- -- Constraints for table `sessions` -- ALTER TABLE `sessions` ADD CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `settings` -- ALTER TABLE `settings` ADD CONSTRAINT `fk_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `smtp_settings` -- ALTER TABLE `smtp_settings` ADD CONSTRAINT `fk_smtp_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `taggables` -- ALTER TABLE `taggables` ADD CONSTRAINT `fk_taggables_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `teams` -- ALTER TABLE `teams` ADD CONSTRAINT `fk_teams_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_teams_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_teams_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_teams_lead_user` FOREIGN KEY (`lead_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_teams_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `team_members` -- ALTER TABLE `team_members` ADD CONSTRAINT `fk_team_members_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_team_members_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `training_examples` -- ALTER TABLE `training_examples` ADD CONSTRAINT `fk_training_examples_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `two_factor_recovery_codes` -- ALTER TABLE `two_factor_recovery_codes` ADD CONSTRAINT `fk_2fa_recovery_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `users` -- ALTER TABLE `users` ADD CONSTRAINT `fk_users_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_users_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_users_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; -- -- Constraints for table `user_business_lines` -- ALTER TABLE `user_business_lines` ADD CONSTRAINT `fk_ubl_business_line` FOREIGN KEY (`business_line_id`) REFERENCES `business_lines` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_ubl_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `user_invitations` -- ALTER TABLE `user_invitations` ADD CONSTRAINT `fk_invitations_invited_by` FOREIGN KEY (`invited_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_invitations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `user_permission_overrides` -- ALTER TABLE `user_permission_overrides` ADD CONSTRAINT `fk_upo_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, ADD CONSTRAINT `fk_upo_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_upo_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `user_preferences` -- ALTER TABLE `user_preferences` ADD CONSTRAINT `fk_user_preferences_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;