/*M!999999\- enable the sandbox mode */ 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_affiliate_creatives` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `external_creative_id` varchar(80) NOT NULL,
  `creative_type` varchar(40) DEFAULT NULL,
  `name` varchar(190) DEFAULT NULL,
  `format` varchar(80) DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `target_url` varchar(500) DEFAULT NULL,
  `tracking_url` varchar(500) DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `html_code` mediumtext DEFAULT NULL,
  `text_code` text DEFAULT NULL,
  `csv_url` varchar(500) DEFAULT NULL,
  `rss_url` varchar(500) DEFAULT NULL,
  `status` varchar(40) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_sync_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_creative_ext` (`network_id`,`external_creative_id`),
  KEY `idx_cr_program` (`program_id`),
  KEY `idx_cr_type` (`creative_type`),
  CONSTRAINT `fk_cr_net` FOREIGN KEY (`network_id`) REFERENCES `crm_affiliate_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=44400 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_affiliate_networks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `type` varchar(40) NOT NULL DEFAULT 'adcell',
  `api_base_url` varchar(255) DEFAULT NULL,
  `api_key` varchar(255) DEFAULT NULL,
  `api_secret` varchar(255) DEFAULT NULL,
  `publisher_id` varchar(80) DEFAULT NULL,
  `username` varchar(120) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `auth_type` enum('api_key','basic_auth','token') NOT NULL DEFAULT 'api_key',
  `sync_interval` int(11) NOT NULL DEFAULT 30,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_sync_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_net_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_affiliate_programs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int(10) unsigned NOT NULL,
  `external_program_id` varchar(80) NOT NULL,
  `name` varchar(190) NOT NULL,
  `advertiser_name` varchar(190) DEFAULT NULL,
  `category` varchar(120) DEFAULT NULL,
  `status` varchar(40) DEFAULT NULL,
  `commission_model` varchar(190) DEFAULT NULL,
  `commission_value` text DEFAULT NULL,
  `approval_status` varchar(40) DEFAULT NULL,
  `program_url` varchar(255) DEFAULT NULL,
  `tracking_base_url` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `logo_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_sync_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_prog_ext` (`network_id`,`external_program_id`),
  KEY `idx_prog_cat` (`category`),
  KEY `idx_prog_status` (`status`),
  CONSTRAINT `fk_prog_net` FOREIGN KEY (`network_id`) REFERENCES `crm_affiliate_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=793 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_affiliate_statistics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `creative_id` int(10) unsigned DEFAULT NULL,
  `date` date NOT NULL,
  `month` tinyint(3) unsigned NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `views` int(11) NOT NULL DEFAULT 0,
  `clicks` int(11) NOT NULL DEFAULT 0,
  `leads` int(11) NOT NULL DEFAULT 0,
  `sales` int(11) NOT NULL DEFAULT 0,
  `open_commission` decimal(12,2) NOT NULL DEFAULT 0.00,
  `confirmed_commission` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cancelled_commission` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_commission` decimal(12,2) NOT NULL DEFAULT 0.00,
  `conversion_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `click_through_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `cancellation_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stat_net_date` (`network_id`,`date`),
  KEY `idx_stat_ym` (`year`,`month`),
  KEY `idx_stat_program` (`program_id`),
  KEY `idx_stat_creative` (`creative_id`),
  CONSTRAINT `fk_stat_net` FOREIGN KEY (`network_id`) REFERENCES `crm_affiliate_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=619 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_affiliate_sync_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `network_id` int(10) unsigned DEFAULT NULL,
  `sync_type` varchar(40) NOT NULL,
  `status` enum('success','error','partial','running') NOT NULL DEFAULT 'running',
  `message` varchar(255) DEFAULT NULL,
  `records_found` int(11) NOT NULL DEFAULT 0,
  `records_created` int(11) NOT NULL DEFAULT 0,
  `records_updated` int(11) NOT NULL DEFAULT 0,
  `error_payload` text DEFAULT NULL,
  `started_at` datetime NOT NULL DEFAULT current_timestamp(),
  `finished_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_synclog_net` (`network_id`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=424 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_cron_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `job` varchar(80) NOT NULL,
  `message` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_cron_job` (`job`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company` varchar(190) DEFAULT NULL,
  `contact_name` varchar(190) DEFAULT NULL,
  `address1` varchar(190) DEFAULT NULL,
  `zip` varchar(20) DEFAULT NULL,
  `city` varchar(120) DEFAULT NULL,
  `country` varchar(80) NOT NULL DEFAULT 'Deutschland',
  `email` varchar(190) DEFAULT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `vat_id` varchar(40) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `mail_template_id` int(10) unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cust_company` (`company`),
  KEY `idx_cust_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_doc_files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int(10) unsigned DEFAULT NULL,
  `doc_id` int(10) unsigned DEFAULT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `mime` varchar(120) DEFAULT NULL,
  `size` int(10) unsigned DEFAULT NULL,
  `is_text` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_docfile_project` (`project_id`),
  KEY `idx_docfile_doc` (`doc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_doc_projects` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `slug` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_docproj_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_doc_versions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_id` int(10) unsigned NOT NULL,
  `version_no` int(11) NOT NULL,
  `content` longtext DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `author` varchar(120) DEFAULT NULL,
  `source` varchar(40) NOT NULL DEFAULT 'web',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_ver_doc` (`doc_id`,`version_no`),
  CONSTRAINT `fk_ver_doc` FOREIGN KEY (`doc_id`) REFERENCES `crm_docs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_docs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int(10) unsigned NOT NULL,
  `title` varchar(190) NOT NULL,
  `content` longtext DEFAULT NULL,
  `format` enum('markdown','text','html') NOT NULL DEFAULT 'markdown',
  `current_version` int(11) NOT NULL DEFAULT 1,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_doc_project` (`project_id`),
  CONSTRAINT `fk_doc_project` FOREIGN KEY (`project_id`) REFERENCES `crm_doc_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_domain_change_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned DEFAULT NULL,
  `provider_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(64) NOT NULL,
  `record_type` varchar(10) DEFAULT NULL,
  `record_name` varchar(255) DEFAULT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `result` varchar(16) DEFAULT 'ok',
  `api_response` text DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `user_name` varchar(120) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_domain` (`domain_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_domain_dns_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned NOT NULL,
  `external_record_id` varchar(190) DEFAULT NULL,
  `type` varchar(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` text DEFAULT NULL,
  `ttl` int(10) unsigned DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `proxied` tinyint(1) NOT NULL DEFAULT 0,
  `comment` varchar(255) DEFAULT NULL,
  `raw_json` text DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_domain` (`domain_id`),
  KEY `idx_ext` (`external_record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_domain_providers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(16) NOT NULL,
  `name` varchar(120) NOT NULL,
  `credentials_encrypted` text DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'new',
  `last_check` datetime DEFAULT NULL,
  `last_message` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_domains` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `provider_id` int(10) unsigned DEFAULT NULL,
  `domain` varchar(190) NOT NULL,
  `external_zone_id` varchar(190) DEFAULT NULL,
  `nameservers` text DEFAULT NULL,
  `dns_provider` varchar(32) DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'active',
  `last_sync` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_domain` (`domain`),
  KEY `idx_provider` (`provider_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_f2b_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `user_name` varchar(120) DEFAULT NULL,
  `action` varchar(64) NOT NULL,
  `target` varchar(190) DEFAULT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `ip` varchar(64) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_f2baudit` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_f2b_blocklist` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(64) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_f2bblock_ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_f2b_filter` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `badwords` text DEFAULT NULL,
  `rules` text DEFAULT NULL,
  `ignoreregex` text DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_f2bfilter_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_f2b_jail` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `filter` varchar(120) DEFAULT NULL,
  `logpath` varchar(255) DEFAULT NULL,
  `port` varchar(120) DEFAULT NULL,
  `maxretry` int(11) DEFAULT NULL,
  `findtime` varchar(20) DEFAULT NULL,
  `bantime` varchar(20) DEFAULT NULL,
  `action` varchar(190) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_f2bjail_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` int(10) unsigned DEFAULT NULL,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `mime` varchar(120) DEFAULT NULL,
  `size` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_file_ticket` (`ticket_id`),
  KEY `idx_file_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_geoip_cache` (
  `ip` varchar(64) NOT NULL,
  `country` varchar(4) DEFAULT NULL,
  `checked_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_invoice_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `title` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 1.000,
  `unit` varchar(40) NOT NULL DEFAULT 'Stk.',
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 19.00,
  `line_net` decimal(12,2) NOT NULL DEFAULT 0.00,
  `line_tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `line_gross` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_item_invoice` (`invoice_id`),
  CONSTRAINT `fk_item_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `crm_invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_invoice_payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_on` date NOT NULL,
  `method` varchar(60) DEFAULT NULL,
  `note` varchar(190) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_pay_invoice` (`invoice_id`),
  CONSTRAINT `fk_pay_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `crm_invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(60) DEFAULT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `customer_suggest_dismissed` tinyint(1) NOT NULL DEFAULT 0,
  `bill_company` varchar(190) DEFAULT NULL,
  `bill_name` varchar(190) DEFAULT NULL,
  `bill_address1` varchar(190) DEFAULT NULL,
  `bill_zip` varchar(20) DEFAULT NULL,
  `bill_city` varchar(120) DEFAULT NULL,
  `bill_country` varchar(80) DEFAULT NULL,
  `bill_email` varchar(190) DEFAULT NULL,
  `bill_vat_id` varchar(40) DEFAULT NULL,
  `invoice_date` date NOT NULL,
  `service_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `payment_term_days` int(11) NOT NULL DEFAULT 14,
  `status` enum('draft','open','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `text_top` text DEFAULT NULL,
  `text_bottom` text DEFAULT NULL,
  `subtotal_net` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_gross` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pdf_path` varchar(255) DEFAULT NULL,
  `recurring_id` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inv_number` (`invoice_number`),
  KEY `idx_inv_status` (`status`),
  KEY `idx_inv_customer` (`customer_id`),
  KEY `idx_inv_date` (`invoice_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_mail_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `ticket_id` int(10) unsigned DEFAULT NULL,
  `template_id` int(10) unsigned DEFAULT NULL,
  `to_email` varchar(190) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `status` enum('sent','failed') NOT NULL,
  `error` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_log_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_mail_template_rules` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` enum('customer','status') NOT NULL,
  `match_value` varchar(120) NOT NULL,
  `template_id` int(10) unsigned NOT NULL,
  `priority` int(11) NOT NULL DEFAULT 10,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_rule_scope` (`scope`,`match_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_mail_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body_html` mediumtext NOT NULL,
  `body_text` mediumtext DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `status_key` varchar(40) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tpl_default` (`is_default`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `price_net` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 19.00,
  `unit` varchar(40) NOT NULL DEFAULT 'Stk.',
  `category` varchar(120) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_prod_active` (`active`),
  KEY `idx_prod_cat` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_prompt_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `color` varchar(20) DEFAULT NULL,
  `sort` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_cat_parent` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_prompts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(190) NOT NULL,
  `body` longtext NOT NULL,
  `type` enum('image','web','text','other') NOT NULL DEFAULT 'text',
  `tags` varchar(255) DEFAULT NULL,
  `is_favorite` tinyint(1) NOT NULL DEFAULT 0,
  `usage_count` int(11) NOT NULL DEFAULT 0,
  `notes` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_prompt_cat` (`category_id`),
  KEY `idx_prompt_fav` (`is_favorite`),
  KEY `idx_prompt_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_recurring_invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(190) NOT NULL,
  `source_invoice_id` int(10) unsigned DEFAULT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `interval_type` enum('monthly','quarterly','semiannual','annual') NOT NULL DEFAULT 'monthly',
  `start_date` date NOT NULL,
  `next_run` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `auto_send` tinyint(1) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `last_run` date DEFAULT NULL,
  `blueprint` longtext NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_rec_active` (`active`),
  KEY `idx_rec_next` (`next_run`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_reminders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(190) NOT NULL,
  `message` text DEFAULT NULL,
  `remind_at` datetime NOT NULL,
  `channel` enum('pushover','email','both') NOT NULL DEFAULT 'pushover',
  `to_email` varchar(190) DEFAULT NULL,
  `priority` tinyint(4) NOT NULL DEFAULT 0,
  `status` enum('pending','sent','failed','done') NOT NULL DEFAULT 'pending',
  `sent_at` datetime DEFAULT NULL,
  `related_type` varchar(40) DEFAULT NULL,
  `related_id` int(10) unsigned DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_rem_due` (`status`,`remind_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_search_metrics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `source` enum('gsc','bing') NOT NULL,
  `dimension` enum('total','query','page','country','device','appearance') NOT NULL DEFAULT 'total',
  `dim_value` varchar(512) DEFAULT NULL,
  `date` date NOT NULL,
  `clicks` int(11) NOT NULL DEFAULT 0,
  `impressions` int(11) NOT NULL DEFAULT 0,
  `ctr` decimal(7,4) NOT NULL DEFAULT 0.0000,
  `position` decimal(7,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sm_site_src_date` (`website_id`,`source`,`date`),
  KEY `idx_sm_dim` (`website_id`,`source`,`dimension`,`date`),
  CONSTRAINT `fk_sm_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1618 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_server_admin_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `user_name` varchar(120) DEFAULT NULL,
  `area` varchar(32) NOT NULL,
  `resource` varchar(190) DEFAULT NULL,
  `action` varchar(64) NOT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `result` varchar(16) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_server_backup_runs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `backup_id` int(10) unsigned NOT NULL,
  `started_at` datetime NOT NULL,
  `finished_at` datetime DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'running',
  `size_bytes` bigint(20) unsigned DEFAULT NULL,
  `archive_name` varchar(255) DEFAULT NULL,
  `location` varchar(700) DEFAULT NULL,
  `log` longtext DEFAULT NULL,
  `trigger` varchar(16) NOT NULL DEFAULT 'manual',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_backup` (`backup_id`),
  KEY `idx_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_server_backups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `content_db` tinyint(1) NOT NULL DEFAULT 1,
  `content_web` tinyint(1) NOT NULL DEFAULT 0,
  `content_config` tinyint(1) NOT NULL DEFAULT 0,
  `db_names` text DEFAULT NULL,
  `web_paths` text DEFAULT NULL,
  `config_paths` text DEFAULT NULL,
  `destination` varchar(16) NOT NULL DEFAULT 'local',
  `dest_path` varchar(500) DEFAULT NULL,
  `dest_host` varchar(190) DEFAULT NULL,
  `dest_port` int(11) DEFAULT NULL,
  `dest_user` varchar(190) DEFAULT NULL,
  `dest_pass_enc` text DEFAULT NULL,
  `dest_remote` varchar(190) DEFAULT NULL,
  `archive_pass_enc` text DEFAULT NULL,
  `retention` int(11) NOT NULL DEFAULT 7,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_server_cronjob_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `user_name` varchar(120) DEFAULT NULL,
  `server_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(64) NOT NULL,
  `target` varchar(190) DEFAULT NULL,
  `result` varchar(16) DEFAULT NULL,
  `detail` text DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_server_cronjob_runs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cronjob_id` int(10) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL DEFAULT 1,
  `started_at` datetime NOT NULL,
  `finished_at` datetime DEFAULT NULL,
  `duration` decimal(10,2) DEFAULT NULL,
  `exit_code` int(11) DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'running',
  `trigger` varchar(16) NOT NULL DEFAULT 'manual',
  `stdout` longtext DEFAULT NULL,
  `stderr` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cronjob` (`cronjob_id`),
  KEY `idx_started` (`started_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=5402 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_server_cronjobs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_id` int(10) unsigned NOT NULL DEFAULT 1,
  `name` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `run_user` varchar(64) NOT NULL DEFAULT 'root',
  `schedule` varchar(120) NOT NULL,
  `command` text NOT NULL,
  `working_directory` varchar(255) DEFAULT NULL,
  `timeout` int(10) unsigned NOT NULL DEFAULT 3600,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `source` varchar(16) NOT NULL DEFAULT 'managed',
  `origin` varchar(255) DEFAULT NULL,
  `notify_on_error` tinyint(1) NOT NULL DEFAULT 0,
  `notify_on_success` tinyint(1) NOT NULL DEFAULT 0,
  `notify_email` varchar(190) DEFAULT NULL,
  `last_run_at` datetime DEFAULT NULL,
  `last_exit_code` int(11) DEFAULT NULL,
  `last_status` varchar(16) DEFAULT NULL,
  `avg_runtime` decimal(10,2) DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_server` (`server_id`),
  KEY `idx_active` (`active`),
  KEY `idx_source` (`source`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_servers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `hostname` varchar(190) DEFAULT NULL,
  `type` varchar(24) NOT NULL DEFAULT 'local',
  `ssh_host` varchar(190) DEFAULT NULL,
  `ssh_user` varchar(64) DEFAULT NULL,
  `ssh_port` int(11) DEFAULT 22,
  `is_local` tinyint(1) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_settings` (
  `skey` varchar(80) NOT NULL,
  `svalue` mediumtext DEFAULT NULL,
  PRIMARY KEY (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_ticket_messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` int(10) unsigned NOT NULL,
  `author_user_id` int(10) unsigned DEFAULT NULL,
  `author_name` varchar(190) DEFAULT NULL,
  `is_public` tinyint(1) NOT NULL DEFAULT 1,
  `body` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_msg_ticket` (`ticket_id`),
  CONSTRAINT `fk_msg_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `crm_tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_ticket_notes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `body` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_note_ticket` (`ticket_id`),
  CONSTRAINT `fk_note_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `crm_tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_tickets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_number` varchar(40) DEFAULT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `subject` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('open','in_progress','waiting_customer','done','closed') NOT NULL DEFAULT 'open',
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `assigned_to` int(10) unsigned DEFAULT NULL,
  `requester_name` varchar(190) DEFAULT NULL,
  `requester_email` varchar(190) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ticket_number` (`ticket_number`),
  KEY `idx_ticket_status` (`status`),
  KEY `idx_ticket_prio` (`priority`),
  KEY `idx_ticket_assigned` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','office','support') NOT NULL DEFAULT 'office',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_visitor_breakdown` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `dimension` enum('page','referrer','campaign','device') NOT NULL,
  `label` varchar(512) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `source` varchar(190) DEFAULT NULL,
  `medium` varchar(190) DEFAULT NULL,
  `hits` int(11) NOT NULL DEFAULT 0,
  `captured_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_vb` (`website_id`,`dimension`,`hits`),
  CONSTRAINT `fk_vb_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=731560 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_visitor_conversions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `ext_id` varchar(40) NOT NULL,
  `conversion_type` varchar(120) DEFAULT NULL,
  `reference_id` varchar(190) DEFAULT NULL,
  `value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) DEFAULT NULL,
  `meta` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `imported_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vc` (`website_id`,`ext_id`),
  KEY `idx_vc_date` (`website_id`,`created_at`),
  CONSTRAINT `fk_vc_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=191918 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_visitor_daily` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `visitors` int(11) NOT NULL DEFAULT 0,
  `pageviews` int(11) NOT NULL DEFAULT 0,
  `avg_duration` decimal(8,2) NOT NULL DEFAULT 0.00,
  `conversions` int(11) NOT NULL DEFAULT 0,
  `conversion_value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `form_abandonments` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vd` (`website_id`,`date`),
  CONSTRAINT `fk_vd_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_visitor_sessions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `visit_id` varchar(40) NOT NULL,
  `session_hash` varchar(64) DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `duration_seconds` int(11) NOT NULL DEFAULT 0,
  `country` varchar(8) DEFAULT NULL,
  `device` varchar(40) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `referrer` varchar(255) DEFAULT NULL,
  `utm_source` varchar(190) DEFAULT NULL,
  `utm_medium` varchar(190) DEFAULT NULL,
  `utm_campaign` varchar(190) DEFAULT NULL,
  `entry_page` varchar(512) DEFAULT NULL,
  `exit_page` varchar(512) DEFAULT NULL,
  `pageviews` int(11) NOT NULL DEFAULT 0,
  `is_bounce` tinyint(1) NOT NULL DEFAULT 0,
  `conv_count` int(11) NOT NULL DEFAULT 0,
  `conv_value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pages_json` mediumtext DEFAULT NULL,
  `events_json` mediumtext DEFAULT NULL,
  `conversions_json` text DEFAULT NULL,
  `imported_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vs` (`website_id`,`visit_id`),
  KEY `idx_vs_started` (`website_id`,`started_at`),
  CONSTRAINT `fk_vs_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3236 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_vpn_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `user_name` varchar(120) DEFAULT NULL,
  `action` varchar(64) NOT NULL,
  `target` varchar(190) DEFAULT NULL,
  `result` varchar(16) DEFAULT NULL,
  `detail` text DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_vpn_connections` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vpn_user_id` int(10) unsigned DEFAULT NULL,
  `username` varchar(64) NOT NULL,
  `public_ip` varchar(45) DEFAULT NULL,
  `vpn_ip` varchar(45) DEFAULT NULL,
  `country` varchar(4) DEFAULT NULL,
  `device` varchar(190) DEFAULT NULL,
  `connected_at` datetime NOT NULL,
  `disconnected_at` datetime DEFAULT NULL,
  `bytes_in` bigint(20) unsigned NOT NULL DEFAULT 0,
  `bytes_out` bigint(20) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`vpn_user_id`),
  KEY `idx_username` (`username`),
  KEY `idx_connected` (`connected_at`),
  KEY `idx_open` (`disconnected_at`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_vpn_devices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vpn_user_id` int(10) unsigned NOT NULL,
  `device` varchar(190) DEFAULT NULL,
  `public_ip` varchar(45) DEFAULT NULL,
  `country` varchar(4) DEFAULT NULL,
  `first_seen_at` datetime DEFAULT NULL,
  `last_seen_at` datetime DEFAULT NULL,
  `seen_count` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dev` (`vpn_user_id`,`device`,`public_ip`),
  KEY `idx_user` (`vpn_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_vpn_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `display_name` varchar(190) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `expires_at` date DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `last_ip` varchar(45) DEFAULT NULL,
  `last_country` varchar(4) DEFAULT NULL,
  `cert_serial` varchar(64) DEFAULT NULL,
  `cert_expires_at` datetime DEFAULT NULL,
  `cert_status` varchar(16) DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  KEY `idx_active` (`active`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_website_checks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `status` enum('up','down') NOT NULL,
  `code` int(11) DEFAULT NULL,
  `ms` int(11) DEFAULT NULL,
  `error` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_check_site` (`website_id`,`created_at`),
  CONSTRAINT `fk_check_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_website_programs` (
  `website_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`website_id`,`program_id`),
  KEY `idx_wp_program` (`program_id`),
  CONSTRAINT `fk_wp_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_website_seo_scan_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scan_id` int(10) unsigned NOT NULL,
  `website_id` int(10) unsigned DEFAULT NULL,
  `url` varchar(700) DEFAULT NULL,
  `category` varchar(32) NOT NULL,
  `type` varchar(16) NOT NULL,
  `priority` varchar(16) NOT NULL DEFAULT 'Mittel',
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `recommendation` text DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT 'Offen',
  `data_json` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_item_scan` (`scan_id`),
  KEY `idx_item_site` (`website_id`),
  CONSTRAINT `fk_seoitem_scan` FOREIGN KEY (`scan_id`) REFERENCES `crm_website_seo_scans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=494 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_website_seo_scans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `finished_at` datetime DEFAULT NULL,
  `status` enum('running','done','error') NOT NULL DEFAULT 'running',
  `score` tinyint(3) unsigned DEFAULT NULL,
  `pages_checked` int(11) NOT NULL DEFAULT 0,
  `problems_count` int(11) NOT NULL DEFAULT 0,
  `warnings_count` int(11) NOT NULL DEFAULT 0,
  `strengths_count` int(11) NOT NULL DEFAULT 0,
  `raw_summary_json` mediumtext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_scan_site` (`website_id`,`status`,`id`),
  CONSTRAINT `fk_seoscan_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `crm_websites` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `url` varchar(255) NOT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `notify` tinyint(1) NOT NULL DEFAULT 1,
  `last_status` enum('up','down','unknown') NOT NULL DEFAULT 'unknown',
  `last_code` int(11) DEFAULT NULL,
  `last_ms` int(11) DEFAULT NULL,
  `last_checked` datetime DEFAULT NULL,
  `last_error` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `gsc_property` varchar(255) DEFAULT NULL,
  `bing_site` varchar(255) DEFAULT NULL,
  `tracking_url` varchar(255) DEFAULT NULL,
  `tracking_key` varchar(190) DEFAULT NULL,
  `tracking_synced` datetime DEFAULT NULL,
  `conversion_tags` mediumtext DEFAULT NULL,
  `favicon_data` mediumtext DEFAULT NULL,
  `favicon_hash` varchar(32) DEFAULT NULL,
  `favicon_checked` datetime DEFAULT NULL,
  `screenshot_data` mediumtext DEFAULT NULL,
  `screenshot_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_web_active` (`active`),
  KEY `idx_web_status` (`last_status`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;