
CREATE TABLE `account_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `type` enum('debit','credit') NOT NULL,
  `sub_type` enum('opening_balance','fund_transfer','deposit') DEFAULT NULL,
  `amount` decimal(22,4) NOT NULL,
  `reff_no` varchar(191) DEFAULT NULL,
  `operation_date` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `transaction_id` int(11) DEFAULT NULL,
  `transaction_payment_id` int(11) DEFAULT NULL,
  `transfer_transaction_id` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `account_transactions_account_id_index` (`account_id`),
  KEY `account_transactions_transaction_id_index` (`transaction_id`),
  KEY `account_transactions_transaction_payment_id_index` (`transaction_payment_id`),
  KEY `account_transactions_transfer_transaction_id_index` (`transfer_transaction_id`),
  KEY `account_transactions_created_by_index` (`created_by`),
  KEY `account_transactions_type_index` (`type`),
  KEY `account_transactions_sub_type_index` (`sub_type`),
  KEY `account_transactions_operation_date_index` (`operation_date`)
) ENGINE=InnoDB AUTO_INCREMENT=205587 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `account_transactions` VALUES (205557,33,'',NULL,80000.0000,NULL,'2025-06-15 20:18:43',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(205558,34,'',NULL,1500000.0000,NULL,'2025-06-15 20:18:43',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(205559,35,'',NULL,300000.0000,NULL,'2025-06-15 20:18:43',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(205560,33,'',NULL,80000.0000,NULL,'2025-06-15 20:19:08',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 17:19:09','2026-06-15 17:19:09'),(205561,34,'',NULL,1500000.0000,NULL,'2025-06-15 20:19:09',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 17:19:09','2026-06-15 17:19:09'),(205562,35,'',NULL,300000.0000,NULL,'2025-06-15 20:19:09',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 17:19:09','2026-06-15 17:19:09'),(205563,33,'',NULL,80000.0000,NULL,'2025-06-15 21:11:45',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 18:11:45','2026-06-15 18:11:45'),(205564,34,'',NULL,1500000.0000,NULL,'2025-06-15 21:11:45',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 18:11:45','2026-06-15 18:11:45'),(205565,35,'',NULL,300000.0000,NULL,'2025-06-15 21:11:46',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 18:11:46','2026-06-15 18:11:46'),(205566,33,'',NULL,80000.0000,NULL,'2025-06-15 21:15:56',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 18:15:56','2026-06-15 18:15:56'),(205567,34,'',NULL,1500000.0000,NULL,'2025-06-15 21:15:56',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 18:15:56','2026-06-15 18:15:56'),(205568,35,'',NULL,300000.0000,NULL,'2025-06-15 21:15:56',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 18:15:56','2026-06-15 18:15:56'),(205569,33,'',NULL,80000.0000,NULL,'2025-06-15 22:07:58',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 19:07:58','2026-06-15 19:07:58'),(205570,34,'',NULL,1500000.0000,NULL,'2025-06-15 22:07:59',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 19:07:59','2026-06-15 19:07:59'),(205571,35,'',NULL,300000.0000,NULL,'2025-06-15 22:07:59',164,NULL,NULL,NULL,NULL,NULL,'2026-06-15 19:07:59','2026-06-15 19:07:59'),(205572,36,'',NULL,80000.0000,NULL,'2025-06-17 23:54:55',171,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(205573,37,'',NULL,1500000.0000,NULL,'2025-06-17 23:54:55',171,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(205574,38,'',NULL,300000.0000,NULL,'2025-06-17 23:54:55',171,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(205575,39,'',NULL,80000.0000,NULL,'2025-06-17 23:55:44',177,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(205576,40,'',NULL,1500000.0000,NULL,'2025-06-17 23:55:44',177,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(205577,41,'',NULL,300000.0000,NULL,'2025-06-17 23:55:44',177,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(205578,42,'',NULL,80000.0000,NULL,'2025-06-17 23:58:11',183,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(205579,43,'',NULL,1500000.0000,NULL,'2025-06-17 23:58:11',183,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(205580,44,'',NULL,300000.0000,NULL,'2025-06-17 23:58:11',183,NULL,NULL,NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(205581,45,'',NULL,80000.0000,NULL,'2025-06-18 00:00:02',189,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(205582,46,'',NULL,1500000.0000,NULL,'2025-06-18 00:00:02',189,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(205583,47,'',NULL,300000.0000,NULL,'2025-06-18 00:00:02',189,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(205584,48,'',NULL,80000.0000,NULL,'2025-06-18 00:11:54',195,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(205585,49,'',NULL,1500000.0000,NULL,'2025-06-18 00:11:54',195,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(205586,50,'',NULL,300000.0000,NULL,'2025-06-18 00:11:54',195,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54');
CREATE TABLE `account_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `parent_account_type_id` int(11) DEFAULT NULL,
  `business_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `account_types_parent_account_type_id_index` (`parent_account_type_id`),
  KEY `account_types_business_id_index` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `accounting_acc_trans_mappings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `ref_no` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `created_by` int(11) NOT NULL,
  `operation_date` datetime NOT NULL,
  `note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `accounting_account_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `business_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `account_primary_type` varchar(191) DEFAULT NULL,
  `account_type` varchar(191) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `show_balance` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `accounting_account_types` VALUES (1,'accounts_receivable',NULL,NULL,'asset','sub_type',NULL,NULL,0,NULL,NULL),(2,'current_assets',NULL,NULL,'asset','sub_type',NULL,NULL,1,NULL,NULL),(3,'cash_and_cash_equivalents',NULL,NULL,'asset','sub_type',NULL,NULL,1,NULL,NULL),(4,'fixed_assets',NULL,NULL,'asset','sub_type',NULL,NULL,1,NULL,NULL),(5,'non_current_assets',NULL,NULL,'asset','sub_type',NULL,NULL,1,NULL,NULL),(6,'accounts_payable',NULL,NULL,'liability','sub_type',NULL,NULL,0,NULL,NULL),(7,'credit_card',NULL,NULL,'liability','sub_type',NULL,NULL,1,NULL,NULL),(8,'current_liabilities',NULL,NULL,'liability','sub_type',NULL,NULL,1,NULL,NULL),(9,'non_current_liabilities',NULL,NULL,'liability','sub_type',NULL,NULL,1,NULL,NULL),(10,'owners_equity',NULL,NULL,'equity','sub_type',NULL,NULL,1,NULL,NULL),(11,'income',NULL,NULL,'income','sub_type',NULL,NULL,0,NULL,NULL),(12,'other_income',NULL,NULL,'income','sub_type',NULL,NULL,0,NULL,NULL),(13,'cost_of_sale',NULL,NULL,'expenses','sub_type',NULL,NULL,0,NULL,NULL),(14,'expenses',NULL,NULL,'expenses','sub_type',NULL,NULL,0,NULL,NULL),(15,'other_expense',NULL,NULL,'expenses','sub_type',NULL,NULL,0,NULL,NULL),(16,'accounts_receivable',NULL,NULL,NULL,'detail_type',1,'',1,NULL,NULL),(17,'allowance_for_bad_debts',NULL,NULL,NULL,'detail_type',2,'allowance_for_bad_debts_desc',1,NULL,NULL),(18,'assets_available_for_sale',NULL,NULL,NULL,'detail_type',2,'assets_available_for_sale_desc',1,NULL,NULL),(19,'development_costs',NULL,NULL,NULL,'detail_type',2,'development_costs_desc',1,NULL,NULL),(20,'employee_cash_advances',NULL,NULL,NULL,'detail_type',2,'employee_cash_advances_desc',1,NULL,NULL),(21,'inventory',NULL,NULL,NULL,'detail_type',2,'inventory_desc',1,NULL,NULL),(22,'investments_-_other',NULL,NULL,NULL,'detail_type',2,'investments_-_other_desc',1,NULL,NULL),(23,'loans_to_officers',NULL,NULL,NULL,'detail_type',2,'loans_to_officers_desc',1,NULL,NULL),(24,'loans_to_others',NULL,NULL,NULL,'detail_type',2,'loans_to_others_desc',1,NULL,NULL),(25,'loans_to_shareholders',NULL,NULL,NULL,'detail_type',2,'loans_to_shareholders_desc',1,NULL,NULL),(26,'other_current_assets',NULL,NULL,NULL,'detail_type',2,'other_current_assets_desc',1,NULL,NULL),(27,'prepaid_expenses',NULL,NULL,NULL,'detail_type',2,'prepaid_expenses_desc',1,NULL,NULL),(28,'retainage',NULL,NULL,NULL,'detail_type',2,'retainage_desc',1,NULL,NULL),(29,'undeposited_funds',NULL,NULL,NULL,'detail_type',2,'undeposited_funds_desc',1,NULL,NULL),(30,'bank',NULL,NULL,NULL,'detail_type',3,'bank_desc',1,NULL,NULL),(31,'cash_and_cash_equivalents',NULL,NULL,NULL,'detail_type',3,'cash_and_cash_equivalents_desc',1,NULL,NULL),(32,'cash_on_hand',NULL,NULL,NULL,'detail_type',3,'cash_on_hand_desc',1,NULL,NULL),(33,'client_trust_account',NULL,NULL,NULL,'detail_type',3,'client_trust_account_desc',1,NULL,NULL),(34,'money_market',NULL,NULL,NULL,'detail_type',3,'money_market_desc',1,NULL,NULL),(35,'rents_held_in_trust',NULL,NULL,NULL,'detail_type',3,'rents_held_in_trust_desc',1,NULL,NULL),(36,'savings',NULL,NULL,NULL,'detail_type',3,'savings_desc',1,NULL,NULL),(37,'accumulated_depletion',NULL,NULL,NULL,'detail_type',4,'accumulated_depletion_desc',1,NULL,NULL),(38,'accumulated_depreciation_on_property,_plant_and_equipment',NULL,NULL,NULL,'detail_type',4,'accumulated_depreciation_on_property,_plant_and_equipment_desc',1,NULL,NULL),(39,'buildings',NULL,NULL,NULL,'detail_type',4,'buildings_desc',1,NULL,NULL),(40,'depletable_assets',NULL,NULL,NULL,'detail_type',4,'depletable_assets_desc',1,NULL,NULL),(41,'furniture_and_fixtures',NULL,NULL,NULL,'detail_type',4,'furniture_and_fixtures_desc',1,NULL,NULL),(42,'land',NULL,NULL,NULL,'detail_type',4,'land_desc',1,NULL,NULL),(43,'leasehold_improvements',NULL,NULL,NULL,'detail_type',4,'leasehold_improvements_desc',1,NULL,NULL),(44,'machinery_and_equipment',NULL,NULL,NULL,'detail_type',4,'machinery_and_equipment_desc',1,NULL,NULL),(45,'other_fixed_assets',NULL,NULL,NULL,'detail_type',4,'other_fixed_assets_desc',1,NULL,NULL),(46,'vehicles',NULL,NULL,NULL,'detail_type',4,'vehicles_desc',1,NULL,NULL),(47,'accumulated_amortisation_of_non-current_assets',NULL,NULL,NULL,'detail_type',5,'accumulated_amortisation_of_non-current_assets_desc',1,NULL,NULL),(48,'assets_held_for_sale',NULL,NULL,NULL,'detail_type',5,'assets_held_for_sale_desc',1,NULL,NULL),(49,'deferred_tax',NULL,NULL,NULL,'detail_type',5,'deferred_tax_desc',1,NULL,NULL),(50,'goodwill',NULL,NULL,NULL,'detail_type',5,'goodwill_desc',1,NULL,NULL),(51,'intangible_assets',NULL,NULL,NULL,'detail_type',5,'intangible_assets_desc',1,NULL,NULL),(52,'lease_buyout',NULL,NULL,NULL,'detail_type',5,'lease_buyout_desc',1,NULL,NULL),(53,'licences',NULL,NULL,NULL,'detail_type',5,'licences_desc',1,NULL,NULL),(54,'long-term_investments',NULL,NULL,NULL,'detail_type',5,'long-term_investments_desc',1,NULL,NULL),(55,'organisational_costs',NULL,NULL,NULL,'detail_type',5,'organisational_costs_desc',1,NULL,NULL),(56,'other_non-current_assets',NULL,NULL,NULL,'detail_type',5,'other_non-current_assets_desc',1,NULL,NULL),(57,'security_deposits',NULL,NULL,NULL,'detail_type',5,'security_deposits_desc',1,NULL,NULL),(58,'accounts_payable_(a/p)',NULL,NULL,NULL,'detail_type',6,'accounts_payable_(a/p)_desc',1,NULL,NULL),(59,'credit_card',NULL,NULL,NULL,'detail_type',7,'credit_card_desc',1,NULL,NULL),(60,'accrued_liabilities',NULL,NULL,NULL,'detail_type',8,'accrued_liabilities_desc',1,NULL,NULL),(61,'client_trust_accounts_-_liabilities',NULL,NULL,NULL,'detail_type',8,'client_trust_accounts_-_liabilities_desc',1,NULL,NULL),(62,'current_tax_liability',NULL,NULL,NULL,'detail_type',8,'current_tax_liability_desc',1,NULL,NULL),(63,'current_portion_of_obligations_under_finance_leases',NULL,NULL,NULL,'detail_type',8,'current_portion_of_obligations_under_finance_leases_desc',1,NULL,NULL),(64,'dividends_payable',NULL,NULL,NULL,'detail_type',8,'dividends_payable_desc',1,NULL,NULL),(65,'income_tax_payable',NULL,NULL,NULL,'detail_type',8,'income_tax_payable_desc',1,NULL,NULL),(66,'insurance_payable',NULL,NULL,NULL,'detail_type',8,'insurance_payable_desc',1,NULL,NULL),(67,'line_of_credit',NULL,NULL,NULL,'detail_type',8,'line_of_credit_desc',1,NULL,NULL),(68,'loan_payable',NULL,NULL,NULL,'detail_type',8,'loan_payable_desc',1,NULL,NULL),(69,'other_current_liabilities',NULL,NULL,NULL,'detail_type',8,'other_current_liabilities_desc',1,NULL,NULL),(70,'payroll_clearing',NULL,NULL,NULL,'detail_type',8,'payroll_clearing_desc',1,NULL,NULL),(71,'payroll_liabilities',NULL,NULL,NULL,'detail_type',8,'payroll_liabilities_desc',1,NULL,NULL),(72,'prepaid_expenses_payable',NULL,NULL,NULL,'detail_type',8,'prepaid_expenses_payable_desc',1,NULL,NULL),(73,'rents_in_trust_-_liability',NULL,NULL,NULL,'detail_type',8,'rents_in_trust_-_liability_desc',1,NULL,NULL),(74,'sales_and_service_tax_payable',NULL,NULL,NULL,'detail_type',8,'sales_and_service_tax_payable_desc',1,NULL,NULL),(75,'accrued_holiday_payable',NULL,NULL,NULL,'detail_type',9,'accrued_holiday_payable_desc',1,NULL,NULL),(76,'accrued_non-current_liabilities',NULL,NULL,NULL,'detail_type',9,'accrued_non-current_liabilities_desc',1,NULL,NULL),(77,'liabilities_related_to_assets_held_for_sale',NULL,NULL,NULL,'detail_type',9,'liabilities_related_to_assets_held_for_sale_desc',1,NULL,NULL),(78,'long-term_debt',NULL,NULL,NULL,'detail_type',9,'long-term_debt_desc',1,NULL,NULL),(79,'notes_payable',NULL,NULL,NULL,'detail_type',9,'notes_payable_desc',1,NULL,NULL),(80,'other_non-current_liabilities',NULL,NULL,NULL,'detail_type',9,'other_non-current_liabilities_desc',1,NULL,NULL),(81,'shareholder_notes_payable',NULL,NULL,NULL,'detail_type',9,'shareholder_notes_payable_desc',1,NULL,NULL),(82,'accumulated_adjustment',NULL,NULL,NULL,'detail_type',10,'accumulated_adjustment_desc',1,NULL,NULL),(83,'dividend_disbursed',NULL,NULL,NULL,'detail_type',10,'dividend_disbursed_desc',1,NULL,NULL),(84,'equity_in_earnings_of_subsidiaries',NULL,NULL,NULL,'detail_type',10,'equity_in_earnings_of_subsidiaries_desc',1,NULL,NULL),(85,'opening_balance_equity',NULL,NULL,NULL,'detail_type',10,'opening_balance_equity_desc',1,NULL,NULL),(86,'ordinary_shares',NULL,NULL,NULL,'detail_type',10,'ordinary_shares_desc',1,NULL,NULL),(87,'other_comprehensive_income',NULL,NULL,NULL,'detail_type',10,'other_comprehensive_income_desc',1,NULL,NULL),(88,'owner\'s_equity',NULL,NULL,NULL,'detail_type',10,'owner\'s_equity_desc',1,NULL,NULL),(89,'paid-in_capital_or_surplus',NULL,NULL,NULL,'detail_type',10,'paid-in_capital_or_surplus_desc',1,NULL,NULL),(90,'partner_contributions',NULL,NULL,NULL,'detail_type',10,'partner_contributions_desc',1,NULL,NULL),(91,'partner_distributions',NULL,NULL,NULL,'detail_type',10,'partner_distributions_desc',1,NULL,NULL),(92,'partner\'s_equity',NULL,NULL,NULL,'detail_type',10,'partner\'s_equity_desc',1,NULL,NULL),(93,'preferred_shares',NULL,NULL,NULL,'detail_type',10,'preferred_shares_desc',1,NULL,NULL),(94,'retained_earnings',NULL,NULL,NULL,'detail_type',10,'retained_earnings_desc',1,NULL,NULL),(95,'share_capital',NULL,NULL,NULL,'detail_type',10,'share_capital_desc',1,NULL,NULL),(96,'treasury_shares',NULL,NULL,NULL,'detail_type',10,'treasury_shares_desc',1,NULL,NULL),(97,'discounts/refunds_given',NULL,NULL,NULL,'detail_type',11,'discounts/refunds_given_desc',1,NULL,NULL),(98,'non-profit_income',NULL,NULL,NULL,'detail_type',11,'non-profit_income_desc',1,NULL,NULL),(99,'other_primary_income',NULL,NULL,NULL,'detail_type',11,'other_primary_income_desc',1,NULL,NULL),(100,'revenue_-_general',NULL,NULL,NULL,'detail_type',11,'revenue_-_general_desc',1,NULL,NULL),(101,'sales_-_retail',NULL,NULL,NULL,'detail_type',11,'sales_-_retail_desc',1,NULL,NULL),(102,'sales_-_wholesale',NULL,NULL,NULL,'detail_type',11,'sales_-_wholesale_desc',1,NULL,NULL),(103,'sales_of_product_income',NULL,NULL,NULL,'detail_type',11,'sales_of_product_income_desc',1,NULL,NULL),(104,'service/fee_income',NULL,NULL,NULL,'detail_type',11,'service/fee_income_desc',1,NULL,NULL),(105,'unapplied_cash_payment_income',NULL,NULL,NULL,'detail_type',11,'unapplied_cash_payment_income_desc',1,NULL,NULL),(106,'dividend_income',NULL,NULL,NULL,'detail_type',12,'dividend_income_desc',1,NULL,NULL),(107,'interest_earned',NULL,NULL,NULL,'detail_type',12,'interest_earned_desc',1,NULL,NULL),(108,'loss_on_disposal_of_assets',NULL,NULL,NULL,'detail_type',12,'loss_on_disposal_of_assets_desc',1,NULL,NULL),(109,'other_investment_income',NULL,NULL,NULL,'detail_type',12,'other_investment_income_desc',1,NULL,NULL),(110,'other_miscellaneous_income',NULL,NULL,NULL,'detail_type',12,'other_miscellaneous_income_desc',1,NULL,NULL),(111,'other_operating_income',NULL,NULL,NULL,'detail_type',12,'other_operating_income_desc',1,NULL,NULL),(112,'tax-exempt_interest',NULL,NULL,NULL,'detail_type',12,'tax-exempt_interest_desc',1,NULL,NULL),(113,'unrealised_loss_on_securities,_net_of_tax',NULL,NULL,NULL,'detail_type',12,'unrealised_loss_on_securities,_net_of_tax_desc',1,NULL,NULL),(114,'cost_of_labour_-_cos',NULL,NULL,NULL,'detail_type',13,'cost_of_labour_-_cos_desc',1,NULL,NULL),(115,'equipment_rental_-_cos',NULL,NULL,NULL,'detail_type',13,'equipment_rental_-_cos_desc',1,NULL,NULL),(116,'freight_and_delivery_-_cos',NULL,NULL,NULL,'detail_type',13,'freight_and_delivery_-_cos_desc',1,NULL,NULL),(117,'other_costs_of_sales_-_cos',NULL,NULL,NULL,'detail_type',13,'other_costs_of_sales_-_cos_desc',1,NULL,NULL),(118,'supplies_and_materials_-_cos',NULL,NULL,NULL,'detail_type',13,'supplies_and_materials_-_cos_desc',1,NULL,NULL),(119,'advertising/promotional',NULL,NULL,NULL,'detail_type',14,'advertising/promotional_desc',1,NULL,NULL),(120,'amortisation_expense',NULL,NULL,NULL,'detail_type',14,'amortisation_expense_desc',1,NULL,NULL),(121,'auto',NULL,NULL,NULL,'detail_type',14,'auto_desc',1,NULL,NULL),(122,'bad_debts',NULL,NULL,NULL,'detail_type',14,'bad_debts_desc',1,NULL,NULL),(123,'bank_charges',NULL,NULL,NULL,'detail_type',14,'bank_charges_desc',1,NULL,NULL),(124,'charitable_contributions',NULL,NULL,NULL,'detail_type',14,'charitable_contributions_desc',1,NULL,NULL),(125,'commissions_and_fees',NULL,NULL,NULL,'detail_type',14,'commissions_and_fees_desc',1,NULL,NULL),(126,'cost_of_labour',NULL,NULL,NULL,'detail_type',14,'cost_of_labour_desc',1,NULL,NULL),(127,'dues_and_subscriptions',NULL,NULL,NULL,'detail_type',14,'dues_and_subscriptions_desc',1,NULL,NULL),(128,'equipment_rental',NULL,NULL,NULL,'detail_type',14,'equipment_rental_desc',1,NULL,NULL),(129,'finance_costs',NULL,NULL,NULL,'detail_type',14,'finance_costs_desc',1,NULL,NULL),(130,'income_tax_expense',NULL,NULL,NULL,'detail_type',14,'income_tax_expense_desc',1,NULL,NULL),(131,'insurance',NULL,NULL,NULL,'detail_type',14,'insurance_desc',1,NULL,NULL),(132,'interest_paid',NULL,NULL,NULL,'detail_type',14,'interest_paid_desc',1,NULL,NULL),(133,'legal_and_professional_fees',NULL,NULL,NULL,'detail_type',14,'legal_and_professional_fees_desc',1,NULL,NULL),(134,'loss_on_discontinued_operations,_net_of_tax',NULL,NULL,NULL,'detail_type',14,'loss_on_discontinued_operations,_net_of_tax_desc',1,NULL,NULL),(135,'management_compensation',NULL,NULL,NULL,'detail_type',14,'management_compensation_desc',1,NULL,NULL),(136,'meals_and_entertainment',NULL,NULL,NULL,'detail_type',14,'meals_and_entertainment_desc',1,NULL,NULL),(137,'office/general_administrative_expenses',NULL,NULL,NULL,'detail_type',14,'office/general_administrative_expenses_desc',1,NULL,NULL),(138,'other_miscellaneous_service_cost',NULL,NULL,NULL,'detail_type',14,'other_miscellaneous_service_cost_desc',1,NULL,NULL),(139,'other_selling_expenses',NULL,NULL,NULL,'detail_type',14,'other_selling_expenses_desc',1,NULL,NULL),(140,'payroll_expenses',NULL,NULL,NULL,'detail_type',14,'payroll_expenses_desc',1,NULL,NULL),(141,'rent_or_lease_of_buildings',NULL,NULL,NULL,'detail_type',14,'rent_or_lease_of_buildings_desc',1,NULL,NULL),(142,'repair_and_maintenance',NULL,NULL,NULL,'detail_type',14,'repair_and_maintenance_desc',1,NULL,NULL),(143,'shipping_and_delivery_expense',NULL,NULL,NULL,'detail_type',14,'shipping_and_delivery_expense_desc',1,NULL,NULL),(144,'supplies_and_materials',NULL,NULL,NULL,'detail_type',14,'supplies_and_materials_desc',1,NULL,NULL),(145,'taxes_paid',NULL,NULL,NULL,'detail_type',14,'taxes_paid_desc',1,NULL,NULL),(146,'travel_expenses_-_general_and_admin_expenses',NULL,NULL,NULL,'detail_type',14,'travel_expenses_-_general_and_admin_expenses_desc',1,NULL,NULL),(147,'travel_expenses_-_selling_expense',NULL,NULL,NULL,'detail_type',14,'travel_expenses_-_selling_expense_desc',1,NULL,NULL),(148,'unapplied_cash_bill_payment_expense',NULL,NULL,NULL,'detail_type',14,'unapplied_cash_bill_payment_expense_desc',1,NULL,NULL),(149,'utilities',NULL,NULL,NULL,'detail_type',14,'utilities_desc',1,NULL,NULL),(150,'amortisation',NULL,NULL,NULL,'detail_type',15,'amortisation_desc',1,NULL,NULL),(151,'depreciation',NULL,NULL,NULL,'detail_type',15,'depreciation_desc',1,NULL,NULL),(152,'exchange_gain_or_loss',NULL,NULL,NULL,'detail_type',15,'exchange_gain_or_loss_desc',1,NULL,NULL),(153,'other_expense',NULL,NULL,NULL,'detail_type',15,'other_expense_desc',1,NULL,NULL),(154,'penalties_and_settlements',NULL,NULL,NULL,'detail_type',15,'penalties_and_settlements_desc',1,NULL,NULL);
CREATE TABLE `accounting_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `gl_code` varchar(191) DEFAULT NULL,
  `business_id` int(11) NOT NULL,
  `account_primary_type` varchar(191) DEFAULT NULL,
  `account_sub_type_id` bigint(20) DEFAULT NULL,
  `detail_type_id` bigint(20) DEFAULT NULL,
  `parent_account_id` bigint(20) DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `status` varchar(191) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `accounting_accounts` VALUES (1,'Accounts Payable (A/P)',NULL,10,'liability',6,58,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(2,'Credit Card',NULL,10,'liability',7,59,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(3,'Wage expenses',NULL,10,'expenses',14,140,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(4,'Utilities',NULL,10,'expenses',14,149,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(5,'Unrealised loss on securities, net of tax',NULL,10,'income',12,113,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(6,'Undeposited Funds',NULL,10,'asset',2,29,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(7,'Uncategorised Income',NULL,10,'income',11,103,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(8,'Uncategorised Expense',NULL,10,'expenses',14,138,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(9,'Uncategorised Asset',NULL,10,'asset',2,26,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(10,'Unapplied Cash Payment Income',NULL,10,'income',11,105,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(11,'Travel expenses - selling expense',NULL,10,'expenses',14,147,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(12,'Travel expenses - general and admin expenses',NULL,10,'expenses',14,146,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(13,'Supplies',NULL,10,'expenses',14,145,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(14,'Subcontractors - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(15,'Stationery and printing',NULL,10,'expenses',14,137,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(16,'Short-term debit',NULL,10,'liability',8,69,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(17,'Shipping and delivery expense',NULL,10,'expenses',14,143,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(18,'Share capital',NULL,10,'equity',10,95,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(19,'Sales of Product Income',NULL,10,'income',11,103,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(20,'Sales - wholesale',NULL,10,'income',11,102,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(21,'Sales - retail',NULL,10,'income',11,101,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(22,'Sales',NULL,10,'income',11,103,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(23,'Revenue - General',NULL,10,'income',11,100,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(24,'Retained Earnings',NULL,10,'equity',10,94,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(25,'Repair and maintenance',NULL,10,'expenses',14,142,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(26,'Rent or lease payments',NULL,10,'expenses',14,141,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(27,'Reconciliation Discrepancies',NULL,10,'expenses',15,153,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(28,'Purchases',NULL,10,'expenses',14,144,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(29,'Property, plant and equipment',NULL,10,'asset',4,42,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(30,'Prepaid Expenses',NULL,10,'asset',2,27,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(31,'Payroll liabilities',NULL,10,'liability',8,71,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(32,'Payroll Expenses',NULL,10,'expenses',14,140,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(33,'Payroll Clearing',NULL,10,'liability',8,70,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(34,'Overhead - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(35,'Other Types of Expenses-Advertising Expenses',NULL,10,'expenses',14,119,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(36,'Other selling expenses',NULL,10,'expenses',14,139,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(37,'Other operating income (expenses)',NULL,10,'income',12,111,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(38,'Other general and administrative expenses',NULL,10,'expenses',14,137,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(39,'Other comprehensive income',NULL,10,'equity',10,87,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(40,'Other - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(41,'Office expenses',NULL,10,'expenses',14,137,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(42,'Meals and entertainment',NULL,10,'expenses',14,137,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(43,'Materials - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(44,'Management compensation',NULL,10,'expenses',14,135,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(45,'Loss on disposal of assets',NULL,10,'income',12,108,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(46,'Loss on discontinued operations, net of tax',NULL,10,'expenses',14,134,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(47,'Long-term investments',NULL,10,'asset',5,54,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(48,'Long-term debt',NULL,10,'liability',9,78,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(49,'Liabilities related to assets held for sale',NULL,10,'liability',9,77,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(50,'Legal and professional fees',NULL,10,'expenses',14,133,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(51,'Inventory Asset',NULL,10,'asset',2,21,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(52,'Inventory',NULL,10,'asset',2,21,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(53,'Interest income',NULL,10,'income',12,107,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(54,'Interest expense',NULL,10,'expenses',14,132,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(55,'Intangibles',NULL,10,'asset',5,51,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(56,'Insurance - Liability',NULL,10,'expenses',14,131,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(57,'Insurance - General',NULL,10,'expenses',14,131,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(58,'Insurance - Disability',NULL,10,'expenses',14,131,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(59,'Income tax payable',NULL,10,'liability',8,65,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(60,'Income tax expense',NULL,10,'expenses',14,130,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(61,'Goodwill',NULL,10,'asset',5,50,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(62,'Freight and delivery - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(63,'Equity in earnings of subsidiaries',NULL,10,'equity',10,84,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(64,'Equipment rental',NULL,10,'expenses',14,128,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(65,'Dues and Subscriptions',NULL,10,'expenses',14,127,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(66,'Dividends payable',NULL,10,'liability',8,64,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(67,'Dividend income',NULL,10,'income',12,106,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(68,'Dividend disbursed',NULL,10,'equity',10,83,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(69,'Discounts given - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(70,'Direct labour - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(71,'Deferred tax assets',NULL,10,'asset',5,49,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(72,'Cost of sales',NULL,10,'expenses',13,118,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(73,'Commissions and fees',NULL,10,'expenses',14,125,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(74,'Change in inventory - COS',NULL,10,'expenses',13,114,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(75,'Cash and cash equivalents',NULL,10,'asset',3,31,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(76,'Billable Expense Income',NULL,10,'income',11,103,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(77,'Bank charges',NULL,10,'expenses',14,123,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(78,'Bad debts',NULL,10,'expenses',14,122,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(79,'Available for sale assets (short-term)',NULL,10,'asset',2,18,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(80,'Assets held for sale',NULL,10,'asset',5,48,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(81,'Amortisation expense',NULL,10,'expenses',14,120,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(82,'Allowance for bad debts',NULL,10,'asset',2,17,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(83,'Accumulated depreciation on property, plant and equipment',NULL,10,'asset',4,38,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(84,'Accrued non-current liabilities',NULL,10,'liability',9,76,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(85,'Accrued liabilities',NULL,10,'liability',8,60,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(86,'Accrued holiday payable',NULL,10,'liability',9,75,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03'),(87,'Accounts Receivable (A/R)',NULL,10,'asset',1,16,NULL,NULL,'active',195,'2026-06-20 21:15:03','2026-06-20 21:15:03');
CREATE TABLE `accounting_accounts_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `accounting_account_id` bigint(20) unsigned NOT NULL,
  `acc_trans_mapping_id` int(11) DEFAULT NULL COMMENT 'id form accounting_acc_trans_mapping table',
  `transaction_id` int(11) DEFAULT NULL COMMENT 'id form transactions table',
  `transaction_payment_id` int(11) DEFAULT NULL COMMENT 'id form transaction_payments table',
  `amount` decimal(22,4) NOT NULL,
  `type` varchar(100) NOT NULL COMMENT 'debit, credit etc',
  `sub_type` varchar(100) NOT NULL,
  `map_type` varchar(100) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `operation_date` datetime NOT NULL,
  `note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `accounting_budgets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `accounting_account_id` bigint(20) unsigned NOT NULL,
  `financial_year` int(11) NOT NULL,
  `jan` decimal(22,4) DEFAULT NULL,
  `feb` decimal(22,4) DEFAULT NULL,
  `mar` decimal(22,4) DEFAULT NULL,
  `apr` decimal(22,4) DEFAULT NULL,
  `may` decimal(22,4) DEFAULT NULL,
  `jun` decimal(22,4) DEFAULT NULL,
  `jul` decimal(22,4) DEFAULT NULL,
  `aug` decimal(22,4) DEFAULT NULL,
  `sep` decimal(22,4) DEFAULT NULL,
  `oct` decimal(22,4) DEFAULT NULL,
  `nov` decimal(22,4) DEFAULT NULL,
  `dec` decimal(22,4) DEFAULT NULL,
  `quarter_1` decimal(22,4) DEFAULT NULL,
  `quarter_2` decimal(22,4) DEFAULT NULL,
  `quarter_3` decimal(22,4) DEFAULT NULL,
  `quarter_4` decimal(22,4) DEFAULT NULL,
  `yearly` decimal(22,4) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `name` varchar(191) NOT NULL,
  `account_number` varchar(191) NOT NULL,
  `account_details` text DEFAULT NULL,
  `account_type_id` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `is_closed` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accounts_business_id_index` (`business_id`),
  KEY `accounts_account_type_id_index` (`account_type_id`),
  KEY `accounts_created_by_index` (`created_by`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `accounts` VALUES (33,5,'Cash Drawer — Reception','VET-CASH-01',NULL,NULL,NULL,164,0,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(34,5,'NBE Current Account','VET-NBE-2025',NULL,NULL,NULL,164,0,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(35,5,'CIB USD Account','VET-CIB-USD',NULL,NULL,NULL,164,0,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(36,6,'Cash Drawer — Reception','VET-CASH-01',NULL,NULL,NULL,171,0,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(37,6,'NBE Current Account','VET-NBE-2025',NULL,NULL,NULL,171,0,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(38,6,'CIB USD Account','VET-CIB-USD',NULL,NULL,NULL,171,0,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(39,7,'Cash Drawer — Reception','VET-CASH-01',NULL,NULL,NULL,177,0,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(40,7,'NBE Current Account','VET-NBE-2025',NULL,NULL,NULL,177,0,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(41,7,'CIB USD Account','VET-CIB-USD',NULL,NULL,NULL,177,0,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(42,8,'Cash Drawer — Reception','VET-CASH-01',NULL,NULL,NULL,183,0,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(43,8,'NBE Current Account','VET-NBE-2025',NULL,NULL,NULL,183,0,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(44,8,'CIB USD Account','VET-CIB-USD',NULL,NULL,NULL,183,0,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(45,9,'Cash Drawer — Reception','VET-CASH-01',NULL,NULL,NULL,189,0,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(46,9,'NBE Current Account','VET-NBE-2025',NULL,NULL,NULL,189,0,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(47,9,'CIB USD Account','VET-CIB-USD',NULL,NULL,NULL,189,0,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(48,10,'Cash Drawer — Reception','VET-CASH-01',NULL,NULL,NULL,195,0,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(49,10,'NBE Current Account','VET-NBE-2025',NULL,NULL,NULL,195,0,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(50,10,'CIB USD Account','VET-CIB-USD',NULL,NULL,NULL,195,0,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54');
CREATE TABLE `activity_incidents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `business_id` int(10) unsigned DEFAULT NULL,
  `type` varchar(20) NOT NULL,
  `status_code` smallint(5) unsigned DEFAULT NULL,
  `route` varchar(255) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `exception_class` varchar(191) DEFAULT NULL,
  `file` varchar(255) DEFAULT NULL,
  `line` int(10) unsigned DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `request_id` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_incidents_business_id_type_index` (`business_id`,`type`),
  KEY `activity_incidents_business_id_created_at_index` (`business_id`,`created_at`),
  KEY `activity_incidents_user_id_index` (`user_id`),
  KEY `activity_incidents_business_id_index` (`business_id`),
  KEY `activity_incidents_request_id_index` (`request_id`),
  KEY `activity_incidents_created_at_index` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `activity_incidents` VALUES (1,NULL,NULL,'exception',500,'/','GET','Class \"PaymentAccountController\" does not exist','ReflectionException','D:\\xampp\\htdocs\\saas-one-vetone\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Console\\RouteListCommand.php',234,'https://localhost/saas-one/public','127.0.0.1','Symfony','c1a892c6-9746-45e0-8c68-ef0159debe7c','2026-07-10 05:42:33'),(2,NULL,NULL,'exception',500,'/','GET','Class \"PaymentAccountController\" does not exist','ReflectionException','D:\\xampp\\htdocs\\saas-one-vetone\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Console\\RouteListCommand.php',234,'https://localhost/saas-one/public','127.0.0.1','Symfony','54b7087f-39c8-4c21-b0c3-e5bf3465248c','2026-07-10 05:42:53'),(3,NULL,NULL,'exception',500,'/','GET','Class \"PaymentAccountController\" does not exist','ReflectionException','D:\\xampp\\htdocs\\saas-one-vetone\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Console\\RouteListCommand.php',234,'https://localhost/saas-one/public','127.0.0.1','Symfony','ed68c83a-ae39-4300-9b2e-319d91154e44','2026-07-10 05:42:54'),(4,NULL,NULL,'exception',500,'/','GET','Class \"PaymentAccountController\" does not exist','ReflectionException','D:\\xampp\\htdocs\\saas-one-vetone\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Console\\RouteListCommand.php',234,'https://localhost/saas-one/public','127.0.0.1','Symfony','eadd7c6e-37cf-4e1f-9a92-b9a23ff2585a','2026-07-10 05:42:55'),(5,NULL,NULL,'exception',500,'/','GET','Class \"PaymentAccountController\" does not exist','ReflectionException','D:\\xampp\\htdocs\\saas-one-vetone\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Console\\RouteListCommand.php',234,'https://localhost/saas-one/public','127.0.0.1','Symfony','77608720-eaeb-4fa0-a77d-1f205fc1ab2e','2026-07-10 05:42:56');
CREATE TABLE `activity_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `log_name` varchar(191) DEFAULT NULL,
  `description` text NOT NULL,
  `subject_id` int(11) DEFAULT NULL,
  `subject_type` varchar(191) DEFAULT NULL,
  `event` varchar(191) DEFAULT NULL,
  `business_id` int(11) DEFAULT NULL,
  `causer_id` int(11) DEFAULT NULL,
  `causer_type` varchar(191) DEFAULT NULL,
  `properties` text DEFAULT NULL,
  `batch_uuid` char(36) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_log_log_name_index` (`log_name`)
) ENGINE=InnoDB AUTO_INCREMENT=550425 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `activity_log` VALUES (550414,'default','login',164,'App\\User',NULL,5,164,'App\\User','[]',NULL,'2026-06-15 17:35:51','2026-06-15 17:35:51'),(550415,'default','login',164,'App\\User',NULL,5,164,'App\\User','[]',NULL,'2026-06-15 20:19:57','2026-06-15 20:19:57'),(550416,'default','login',164,'App\\User',NULL,5,164,'App\\User','[]',NULL,'2026-06-16 01:14:28','2026-06-16 01:14:28'),(550417,'default','login',164,'App\\User',NULL,5,164,'App\\User','[]',NULL,'2026-06-16 14:10:10','2026-06-16 14:10:10'),(550418,'default','login',195,'App\\User',NULL,10,195,'App\\User','[]',NULL,'2026-06-18 18:38:50','2026-06-18 18:38:50'),(550419,'default','login',195,'App\\User',NULL,10,195,'App\\User','[]',NULL,'2026-06-18 20:15:50','2026-06-18 20:15:50'),(550420,'default','login',195,'App\\User',NULL,10,195,'App\\User','[]',NULL,'2026-06-20 13:40:04','2026-06-20 13:40:04'),(550421,'default','login',195,'App\\User',NULL,10,195,'App\\User','[]',NULL,'2026-06-20 15:32:44','2026-06-20 15:32:44'),(550422,'default','login',195,'App\\User',NULL,10,195,'App\\User','[]',NULL,'2026-06-20 20:50:51','2026-06-20 20:50:51'),(550423,'default','login',195,'App\\User',NULL,10,195,'App\\User','[]',NULL,'2026-06-20 21:08:52','2026-06-20 21:08:52'),(550424,'default','login',211,'App\\User',NULL,12,211,'App\\User','[]',NULL,'2026-07-10 06:57:45','2026-07-10 06:57:45');
CREATE TABLE `ai_feedback` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `module` varchar(60) DEFAULT NULL,
  `feature` varchar(80) DEFAULT NULL,
  `action` varchar(30) NOT NULL,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ai_feedback_business_id_created_at_index` (`business_id`,`created_at`),
  KEY `ai_feedback_business_id_feature_action_index` (`business_id`,`feature`,`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ai_providers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `name` varchar(50) NOT NULL DEFAULT 'default',
  `business_vertical` varchar(40) NOT NULL DEFAULT 'general',
  `provider` varchar(20) NOT NULL DEFAULT 'gemini',
  `base_url` varchar(255) DEFAULT NULL,
  `api_key` text DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 0,
  `temperature` decimal(3,2) NOT NULL DEFAULT 0.40,
  `max_tokens` int(10) unsigned NOT NULL DEFAULT 1024,
  `monthly_token_budget` int(10) unsigned DEFAULT NULL,
  `use_separate_transcription` tinyint(1) NOT NULL DEFAULT 0,
  `t_provider` varchar(30) DEFAULT NULL,
  `t_base_url` varchar(255) DEFAULT NULL,
  `t_api_key` text DEFAULT NULL,
  `t_model` varchar(100) DEFAULT NULL,
  `t_language_hint` varchar(10) NOT NULL DEFAULT 'auto',
  `t_silence_limit_sec` smallint(5) unsigned NOT NULL DEFAULT 30,
  `t_clean_non_medical` tinyint(1) NOT NULL DEFAULT 1,
  `auto_structure_soap` tinyint(1) NOT NULL DEFAULT 1,
  `consent_warning` tinyint(1) NOT NULL DEFAULT 1,
  `system_prompt` text DEFAULT NULL,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ai_providers_business_id_name_unique` (`business_id`,`name`),
  KEY `ai_providers_business_id_index` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ai_providers` VALUES (7,10,'default','vet_clinic','openai_compatible','https://api.groq.com/openai/v1','eyJpdiI6IjJ5Y0lTT3A1K1pNZmlCaW4xVDRvNUE9PSIsInZhbHVlIjoiZlJ5OC9xTGVXVXY0SHViVTNTa3QvR1F3SlNjenlwUllDUEYxNDBlU0YvcXhNQU5oNHJOSlFyREY4V3ZFQTFoR1RXL3BmNU13MnA1eVh2YWV5N1B4dkE9PSIsIm1hYyI6ImU5YWRkMjZjZTI2OTJiMWY1MjIxZTQzNTcwMDE5N2JkMzAxMzcwMTkyMDlhNWFjYjQ1ZTZkM2NlZTI5NDg3ZTMiLCJ0YWciOiIifQ==','llama-3.3-70b-versatile',1,0.40,1024,NULL,1,'webspeech',NULL,'eyJpdiI6InlTMFpOWjNPWVBVTnhRR3FJbWdWVVE9PSIsInZhbHVlIjoiUzJ6WUszV3MvN3dpRW8wbGEvTGk0cGQwTlJvcGdqNnM2aDFObkwyL2lRZFg3bHJMeFBsZGVSaDJOTGJWeTNwdmVRM25BU1Y3WFE5TEVlRnpEc3NDUEE9PSIsIm1hYyI6ImMzM2Y0YjRhNTgyNDNiMzI5YWMzODRjZTI1Y2ExY2VmZjBkMGE0MzY2Y2Q0Njg3ZjIyNWU0MWE1ZDdlOWJhMmEiLCJ0YWciOiIifQ==','whisper-large-v3-turbo','auto',30,1,1,1,NULL,'{\"prompts\":[]}','2026-06-17 21:11:55','2026-06-25 15:34:11'),(8,5,'default','vet_clinic','openai_compatible','https://api.groq.com/openai/v1','eyJpdiI6InZzRUNXTllHZklLNXh4N1BFdWp6bVE9PSIsInZhbHVlIjoiTGVWSU9aSnZqdjhzWSt0NllUMURDTDZEOEE1VjBQMGFuemg1QnVHNjFQakJlYktyWDBpL3RtY0N4ZzJoWnFZdzh0WXdKSjhDNC8vWHpOUDRtQnl5UVE9PSIsIm1hYyI6IjY4NzNiZDJiM2ViYmVkYThkYzYxZmIwNDE4YmZlYWUzMjM0YjUxNDViYTIzNDc3N2Q1YTVkMzQ1ODc2NDQ3YjciLCJ0YWciOiIifQ==','llama-3.3-70b-versatile',1,0.30,1024,NULL,1,'webspeech',NULL,NULL,NULL,'auto',30,1,1,1,NULL,NULL,'2026-06-20 01:11:37','2026-06-25 03:21:03'),(9,10,'fallback','vet_clinic','openai_compatible','https://router.bynara.id/v1','eyJpdiI6Imt4aTRPV2c1ZU1UWG9CYXhnblYzVXc9PSIsInZhbHVlIjoidUVjODc5OVNoeWxRQVRVQ0Q2Y3FOVmx5TEg1d0hsU24yOUlUNW9veHl1Z0ZuKzVpQ24wMnliY1FhMDZHbk5WWjBYRzRSQ2ptNG8xUzhOWHIwS3J4WFE9PSIsIm1hYyI6ImZhZDgxYmI1NjYyNzhiYmQzMzQ1ZTY0MmY1YTQxZmMwNjAzMDQ0MmEzMzY5ZmVmNjNmM2Q5NTcwODVmNGQxN2YiLCJ0YWciOiIifQ==','mistral-large',1,0.40,1024,NULL,0,NULL,NULL,NULL,NULL,'auto',30,1,1,1,NULL,NULL,'2026-06-25 02:49:25','2026-06-25 03:02:54'),(10,5,'fallback','vet_clinic','openai_compatible','https://router.bynara.id/v1','eyJpdiI6ImFaT2ZnMGtlMnJ6bitldWFQd1A0K3c9PSIsInZhbHVlIjoiTU1XQXd1eUhmWkx5Y1VxVnlYckovV3hvbUpCQk9SdnQ3NVNRTk5Oc2RzUDVGajU2V3hsdFRzK1VRV0x4WXJOU08wRkZxeW5rR1lYMGJ3U25BbFhtOVE9PSIsIm1hYyI6IjA0OTFkYmUwYWMyNzdlNTYxMzIwNzc3MTdjMTFlM2NiNDdlNWFkOTkxMTkzZDZhZjZkYjViNTBlMGYxMWFkZjUiLCJ0YWciOiIifQ==','mistral-large',1,0.40,1024,NULL,0,NULL,NULL,NULL,NULL,'auto',30,1,1,1,NULL,NULL,'2026-06-25 03:21:03','2026-06-25 03:21:03');
CREATE TABLE `ai_usage_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `module` varchar(40) DEFAULT NULL,
  `feature` varchar(40) DEFAULT NULL,
  `provider` varchar(20) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `tokens_in` int(10) unsigned NOT NULL DEFAULT 0,
  `tokens_out` int(10) unsigned NOT NULL DEFAULT 0,
  `ok` tinyint(1) NOT NULL DEFAULT 1,
  `error` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ai_usage_logs_business_id_created_at_index` (`business_id`,`created_at`),
  KEY `ai_usage_logs_business_id_index` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ai_usage_logs` VALUES (21,10,'VetClinic','extract_symptoms','openai_compatible','mistral-large',369,121,1,NULL,'2026-06-25 02:44:38','2026-06-25 02:44:38'),(22,10,'VetClinic','clean_transcript','openai_compatible','mistral-large',342,28,1,NULL,'2026-06-25 02:44:40','2026-06-25 02:44:40'),(23,10,'test','failover_probe','openai_compatible','nonexistent-model-zzz',0,0,0,'API error 404: The requested model does not exist.','2026-06-25 02:49:26','2026-06-25 02:49:26'),(24,10,'test','failover_probe:fallback','openai_compatible','mimo-v2.5-pro-free',22,15,1,NULL,'2026-06-25 02:49:29','2026-06-25 02:49:29'),(25,10,'test','groq_fb_probe','openai_compatible','nonexistent-zzz',0,0,0,'API error 404: The requested model does not exist.','2026-06-25 02:59:20','2026-06-25 02:59:20'),(26,10,'test','groq_fb_probe:fallback','openai_compatible','llama-3.3-70b-versatile',45,2,1,NULL,'2026-06-25 02:59:20','2026-06-25 02:59:20'),(27,10,'VetClinic','clean_transcript','openai_compatible','mistral-large',342,28,1,NULL,'2026-06-25 03:00:36','2026-06-25 03:00:36'),(28,10,'test','primary_check','openai_compatible','llama-3.3-70b-versatile',61,30,1,NULL,'2026-06-25 03:02:55','2026-06-25 03:02:55'),(29,10,'VetClinic','clean_transcript','openai_compatible','llama-3.3-70b-versatile',343,22,1,NULL,'2026-06-25 03:22:32','2026-06-25 03:22:32'),(30,10,'VetClinic','rx_safety_check','openai_compatible','llama-3.3-70b-versatile',0,0,0,'Request failed: cURL error 28: Failed to connect to api.groq.com port 443 after 10008 ms: Timeout was reached (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) for https://api.groq.com/openai/v1/chat/completions','2026-06-26 01:48:20','2026-06-26 01:48:20'),(31,10,'VetClinic','rx_safety_check:fallback','openai_compatible','mistral-large',61,199,1,NULL,'2026-06-26 01:48:31','2026-06-26 01:48:31'),(32,10,'VetClinic','rx_safety_check','openai_compatible','llama-3.3-70b-versatile',305,63,1,NULL,'2026-06-26 01:51:57','2026-06-26 01:51:57'),(33,10,'AiCore','lab_interpret','openai_compatible','llama-3.3-70b-versatile',265,109,1,NULL,'2026-06-26 02:50:34','2026-06-26 02:50:34');
CREATE TABLE `allergy_patient_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `known_allergens` text DEFAULT NULL,
  `ige_level` decimal(8,2) DEFAULT NULL,
  `anaphylaxis_history` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `allergy_patient_profiles_clc_patient_id_unique` (`clc_patient_id`),
  CONSTRAINT `allergy_patient_profiles_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `andrology_patient_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `semen_volume` decimal(8,2) DEFAULT NULL,
  `sperm_count` decimal(8,2) DEFAULT NULL,
  `motility` int(11) DEFAULT NULL,
  `erectile_function` varchar(40) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `andrology_patient_profiles_clc_patient_id_unique` (`clc_patient_id`),
  CONSTRAINT `andrology_patient_profiles_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audiology_patient_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `right_db` int(11) DEFAULT NULL,
  `left_db` int(11) DEFAULT NULL,
  `tympanometry` varchar(40) DEFAULT NULL,
  `hearing_aid` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `audiology_patient_profiles_clc_patient_id_unique` (`clc_patient_id`),
  CONSTRAINT `audiology_patient_profiles_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `barcodes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `width` double(22,4) DEFAULT NULL,
  `height` double(22,4) DEFAULT NULL,
  `paper_width` double(22,4) DEFAULT NULL,
  `paper_height` double(22,4) DEFAULT NULL,
  `top_margin` double(22,4) DEFAULT NULL,
  `left_margin` double(22,4) DEFAULT NULL,
  `row_distance` double(22,4) DEFAULT NULL,
  `col_distance` double(22,4) DEFAULT NULL,
  `stickers_in_one_row` int(11) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `is_continuous` tinyint(1) NOT NULL DEFAULT 0,
  `stickers_in_one_sheet` int(11) DEFAULT NULL,
  `business_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `barcodes_business_id_foreign` (`business_id`),
  CONSTRAINT `barcodes_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `barcodes` VALUES (14,'20 Labels per Sheet','Sheet Size: 8.5\" x 11\", Label Size: 4\" x 1\", Labels per sheet: 20',4.0000,1.0000,8.5000,11.0000,0.5000,0.1250,0.0000,0.1875,2,0,0,20,NULL,'2017-12-18 04:13:44','2017-12-18 04:13:44'),(15,'30 Labels per sheet','Sheet Size: 8.5\" x 11\", Label Size: 2.625\" x 1\", Labels per sheet: 30',2.6250,1.0000,8.5000,11.0000,0.5000,0.1880,0.0000,0.1250,3,0,0,30,NULL,'2017-12-18 04:04:39','2017-12-18 04:10:40'),(16,'32 Labels per sheet','Sheet Size: 8.5\" x 11\", Label Size: 2\" x 1.25\", Labels per sheet: 32',2.0000,1.2500,8.5000,11.0000,0.5000,0.2500,0.0000,0.0000,4,0,0,32,NULL,'2017-12-18 03:55:40','2017-12-18 03:55:40'),(17,'40 Labels per sheet','Sheet Size: 8.5\" x 11\", Label Size: 2\" x 1\", Labels per sheet: 40',2.0000,1.0000,8.5000,11.0000,0.5000,0.2500,0.0000,0.0000,4,0,0,40,NULL,'2017-12-18 03:58:40','2017-12-18 03:58:40'),(18,'50 Labels per Sheet','Sheet Size: 8.5\" x 11\", Label Size: 1.5\" x 1\", Labels per sheet: 50',1.5000,1.0000,8.5000,11.0000,0.5000,0.5000,0.0000,0.0000,5,0,0,50,NULL,'2017-12-18 03:51:10','2017-12-18 03:51:10'),(19,'Continuous Rolls - 31.75mm x 25.4mm','Label Size: 31.75mm x 25.4mm, Gap: 3.18mm',1.2500,1.0000,1.2500,0.0000,0.1250,0.0000,0.1250,0.0000,1,0,1,NULL,NULL,'2017-12-18 03:51:10','2017-12-18 03:51:10');
CREATE TABLE `bariatric_patient_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `bmi` decimal(8,2) DEFAULT NULL,
  `weight_kg` decimal(8,2) DEFAULT NULL,
  `comorbidities` text DEFAULT NULL,
  `procedure` varchar(40) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bariatric_patient_profiles_clc_patient_id_unique` (`clc_patient_id`),
  CONSTRAINT `bariatric_patient_profiles_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_addons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `variation_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `category` varchar(60) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `unit` varchar(30) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_addons_business_id_is_active_index` (`business_id`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_api_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `guard` enum('client','employee') NOT NULL,
  `owner_id` int(10) unsigned NOT NULL,
  `token` varchar(80) NOT NULL,
  `abilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`abilities`)),
  `last_used_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_api_tokens_token_unique` (`token`),
  KEY `bms_api_tokens_guard_owner_id_index` (`guard`,`owner_id`),
  KEY `bms_api_tokens_business_id_index` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_areas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_areas_business_id_is_active_index` (`business_id`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_asset_movements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `asset_id` bigint(20) unsigned NOT NULL,
  `from_location_id` int(10) unsigned DEFAULT NULL,
  `from_hall_id` bigint(20) unsigned DEFAULT NULL,
  `to_location_id` int(10) unsigned DEFAULT NULL,
  `to_hall_id` bigint(20) unsigned DEFAULT NULL,
  `transfer_id` bigint(20) unsigned DEFAULT NULL,
  `quantity` decimal(10,2) DEFAULT NULL,
  `condition` varchar(30) DEFAULT NULL,
  `reason` varchar(40) DEFAULT NULL,
  `moved_by` int(10) unsigned DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_asset_movements_business_id_asset_id_index` (`business_id`,`asset_id`),
  KEY `bms_asset_movements_transfer_id_index` (`transfer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `code` varchar(60) NOT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'other',
  `location_id` int(10) unsigned DEFAULT NULL,
  `hall_id` bigint(20) unsigned DEFAULT NULL,
  `condition` varchar(30) NOT NULL DEFAULT 'good',
  `status` varchar(30) NOT NULL DEFAULT 'available',
  `quantity` int(10) unsigned NOT NULL DEFAULT 1,
  `unit_value` decimal(12,2) DEFAULT NULL,
  `serial_no` varchar(120) DEFAULT NULL,
  `purchased_at` date DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_assets_business_id_code_unique` (`business_id`,`code`),
  KEY `bms_assets_business_id_status_index` (`business_id`,`status`),
  KEY `bms_assets_business_id_category_index` (`business_id`,`category`),
  KEY `bms_assets_hall_id_index` (`hall_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_booking_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_id` bigint(20) unsigned NOT NULL,
  `type` varchar(40) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `user_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_booking_events_business_id_booking_id_index` (`business_id`,`booking_id`),
  KEY `bms_booking_events_business_id_type_index` (`business_id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_booking_halls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_id` bigint(20) unsigned NOT NULL,
  `hall_id` bigint(20) unsigned NOT NULL,
  `event_date` date NOT NULL,
  `slot` enum('full_day','morning','evening') NOT NULL DEFAULT 'full_day',
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `package_id` bigint(20) unsigned DEFAULT NULL,
  `addons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`addons`)),
  `package_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`package_items`)),
  `price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_booking_halls_business_id_booking_id_index` (`business_id`,`booking_id`),
  KEY `bms_booking_halls_hall_id_event_date_index` (`hall_id`,`event_date`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_bookings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_no` varchar(40) DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `status` enum('inquiry','hold','tentative','confirmed','contracted','completed','cancelled') NOT NULL DEFAULT 'inquiry',
  `event_type` varchar(60) DEFAULT NULL,
  `event_date` date DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `attendees` int(10) unsigned DEFAULT NULL,
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `deposit_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `erp_invoice_id` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `confirmed_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `cancelled_by` int(10) unsigned DEFAULT NULL,
  `cancellation_reason` varchar(255) DEFAULT NULL,
  `cancellation_reason_code` varchar(60) DEFAULT NULL,
  `penalty_percent` decimal(6,3) DEFAULT NULL,
  `penalty_amount` decimal(15,2) DEFAULT NULL,
  `refund_amount` decimal(15,2) DEFAULT NULL,
  `rescheduled_from` date DEFAULT NULL,
  `reschedule_count` int(10) unsigned NOT NULL DEFAULT 0,
  `contracted_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_bookings_business_id_status_index` (`business_id`,`status`),
  KEY `bms_bookings_business_id_event_date_index` (`business_id`,`event_date`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_change_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `booking_id` bigint(20) unsigned DEFAULT NULL,
  `type` varchar(30) NOT NULL DEFAULT 'edit',
  `message` text NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `staff_note` varchar(500) DEFAULT NULL,
  `decided_by` int(10) unsigned DEFAULT NULL,
  `decided_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_change_requests_business_id_status_index` (`business_id`,`status`),
  KEY `bms_change_requests_business_id_contact_id_index` (`business_id`,`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_client_otps` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `phone` varchar(191) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `channel` varchar(20) NOT NULL DEFAULT 'phone',
  `code` varchar(10) NOT NULL,
  `expires_at` datetime NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `consumed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_client_otps_business_id_phone_index` (`business_id`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_id` bigint(20) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `type` varchar(30) NOT NULL,
  `disk` varchar(30) NOT NULL DEFAULT 'local',
  `path` varchar(255) NOT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT 1,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `generated_by` int(10) unsigned DEFAULT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `accepted_ip` varchar(64) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_documents_business_id_booking_id_index` (`business_id`,`booking_id`),
  KEY `bms_documents_business_id_type_index` (`business_id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_hall_addon` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `hall_id` bigint(20) unsigned NOT NULL,
  `addon_id` bigint(20) unsigned NOT NULL,
  `price_override` decimal(15,2) DEFAULT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_hall_addon_hall_id_addon_id_unique` (`hall_id`,`addon_id`),
  KEY `bms_hall_addon_business_id_hall_id_index` (`business_id`,`hall_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_hall_availability` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `hall_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `slot` enum('full_day','morning','evening') NOT NULL DEFAULT 'full_day',
  `status` enum('hold','tentative','booked','blocked') NOT NULL DEFAULT 'hold',
  `booking_id` bigint(20) unsigned DEFAULT NULL,
  `hold_token` varchar(64) DEFAULT NULL,
  `hold_expires_at` datetime DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_hall_availability_hall_id_date_slot_unique` (`hall_id`,`date`,`slot`),
  KEY `bms_hall_availability_business_id_date_index` (`business_id`,`date`),
  KEY `bms_hall_availability_hold_expires_at_index` (`hold_expires_at`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_hall_media` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `hall_id` bigint(20) unsigned NOT NULL,
  `type` enum('image','video','360') NOT NULL DEFAULT 'image',
  `disk` varchar(60) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `thumbnail_path` varchar(255) DEFAULT NULL,
  `title` varchar(191) DEFAULT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `is_cover` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_hall_media_business_id_hall_id_index` (`business_id`,`hall_id`),
  KEY `bms_hall_media_event_type_index` (`event_type`)
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_halls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `venue_id` bigint(20) unsigned NOT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `area_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `code` varchar(60) DEFAULT NULL,
  `capacity_min` int(10) unsigned NOT NULL DEFAULT 0,
  `capacity_max` int(10) unsigned NOT NULL DEFAULT 0,
  `venue_type` enum('open','closed','both') NOT NULL DEFAULT 'closed',
  `fnb_levels` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`fnb_levels`)),
  `min_budget` decimal(15,2) NOT NULL DEFAULT 0.00,
  `base_price` decimal(15,2) DEFAULT NULL,
  `area_sqm` decimal(10,2) DEFAULT NULL,
  `hall_manager_id` int(10) unsigned DEFAULT NULL,
  `hall_manager_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`hall_manager_ids`)),
  `features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`features`)),
  `description` text DEFAULT NULL,
  `description_ar` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_halls_business_id_is_active_index` (`business_id`,`is_active`),
  KEY `bms_halls_venue_id_index` (`venue_id`),
  KEY `bms_halls_business_id_location_id_index` (`business_id`,`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_lead_halls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `lead_id` bigint(20) unsigned NOT NULL,
  `hall_id` bigint(20) unsigned NOT NULL,
  `package_id` bigint(20) unsigned DEFAULT NULL,
  `addons` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`addons`)),
  `package_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`package_items`)),
  `score` tinyint(3) unsigned DEFAULT NULL,
  `estimated_total` decimal(15,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_lead_halls_business_id_lead_id_index` (`business_id`,`lead_id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_leads` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `whatsapp` varchar(30) DEFAULT NULL,
  `wizard_session_id` bigint(20) unsigned DEFAULT NULL,
  `stage` varchar(50) NOT NULL DEFAULT 'wizard_incomplete',
  `source` varchar(40) DEFAULT NULL,
  `assigned_to` int(10) unsigned DEFAULT NULL,
  `event_type` varchar(60) DEFAULT NULL,
  `attendees` int(10) unsigned DEFAULT NULL,
  `fnb_level` enum('high_tea','drinks_only','set_menu','open_buffet','custom') DEFAULT NULL,
  `venue_preference` enum('open','closed','both') DEFAULT NULL,
  `budget_min` decimal(15,2) DEFAULT NULL,
  `budget_max` decimal(15,2) DEFAULT NULL,
  `candidate_dates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`candidate_dates`)),
  `area_id` bigint(20) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `last_activity_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_leads_business_id_stage_index` (`business_id`,`stage`),
  KEY `bms_leads_business_id_assigned_to_index` (`business_id`,`assigned_to`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_maintenance_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `code` varchar(40) NOT NULL,
  `hall_id` bigint(20) unsigned DEFAULT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `asset_id` bigint(20) unsigned DEFAULT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'other',
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `title` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('submitted','reviewed','assigned','in_progress','resolved','verified','cancelled') NOT NULL DEFAULT 'submitted',
  `reported_by` int(10) unsigned DEFAULT NULL,
  `assigned_to` int(10) unsigned DEFAULT NULL,
  `due_at` datetime DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`photos`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_maintenance_requests_business_id_code_unique` (`business_id`,`code`),
  KEY `bms_maintenance_requests_business_id_status_index` (`business_id`,`status`),
  KEY `bms_maintenance_requests_business_id_priority_index` (`business_id`,`priority`),
  KEY `bms_maintenance_requests_hall_id_index` (`hall_id`),
  KEY `bms_maintenance_requests_assigned_to_index` (`assigned_to`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_maintenance_updates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `request_id` bigint(20) unsigned NOT NULL,
  `from_status` varchar(20) DEFAULT NULL,
  `to_status` varchar(20) DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_maintenance_updates_business_id_request_id_index` (`business_id`,`request_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_match_runs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `source` enum('client','employee') NOT NULL DEFAULT 'client',
  `wizard_session_id` bigint(20) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `criteria` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`criteria`)),
  `results` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`results`)),
  `top_score` tinyint(3) unsigned DEFAULT NULL,
  `matched_count` int(10) unsigned NOT NULL DEFAULT 0,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_match_runs_business_id_lead_id_index` (`business_id`,`lead_id`),
  KEY `bms_match_runs_business_id_wizard_session_id_index` (`business_id`,`wizard_session_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_matching_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `threshold` tinyint(3) unsigned NOT NULL DEFAULT 60,
  `weights` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`weights`)),
  `updated_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_matching_settings_business_id_unique` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_office_visits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `wizard_session_id` bigint(20) unsigned DEFAULT NULL,
  `visit_date` date NOT NULL,
  `visit_time` time DEFAULT NULL,
  `status` enum('requested','scheduled','done','cancelled') NOT NULL DEFAULT 'requested',
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_office_visits_business_id_visit_date_index` (`business_id`,`visit_date`),
  KEY `bms_office_visits_business_id_lead_id_index` (`business_id`,`lead_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_package_hall` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `package_id` bigint(20) unsigned NOT NULL,
  `hall_id` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_package_hall_package_id_hall_id_unique` (`package_id`,`hall_id`),
  KEY `bms_package_hall_business_id_hall_id_index` (`business_id`,`hall_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_package_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `package_id` bigint(20) unsigned NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `variation_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `description_ar` text DEFAULT NULL,
  `media` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`media`)),
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `contents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`contents`)),
  `category` varchar(40) DEFAULT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `qty_mode` enum('fixed','per_guest') NOT NULL DEFAULT 'fixed',
  `is_optional` tinyint(1) NOT NULL DEFAULT 0,
  `is_addon` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `unit_cost` decimal(15,2) DEFAULT NULL,
  `addon_price` decimal(12,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_package_items_business_id_package_id_index` (`business_id`,`package_id`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_packages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `venue_id` bigint(20) unsigned DEFAULT NULL,
  `hall_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `description_ar` text DEFAULT NULL,
  `fnb_level` enum('high_tea','drinks_only','set_menu','open_buffet','custom') DEFAULT NULL,
  `price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `price_per_person` tinyint(1) NOT NULL DEFAULT 0,
  `min_pax` int(10) unsigned DEFAULT NULL,
  `max_pax` int(10) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_packages_business_id_hall_id_index` (`business_id`,`hall_id`),
  KEY `bms_packages_business_id_venue_id_index` (`business_id`,`venue_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_payment_schedules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_id` bigint(20) unsigned NOT NULL,
  `label` varchar(60) NOT NULL,
  `kind` enum('deposit','installment','balance') NOT NULL DEFAULT 'installment',
  `due_date` date DEFAULT NULL,
  `percent` decimal(6,3) NOT NULL DEFAULT 0.000,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','partial','paid','overdue','cancelled') NOT NULL DEFAULT 'pending',
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_payment_schedules_business_id_booking_id_index` (`business_id`,`booking_id`),
  KEY `bms_payment_schedules_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_product_media` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `type` enum('image','video','360') NOT NULL DEFAULT 'image',
  `disk` varchar(60) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `thumbnail_path` varchar(255) DEFAULT NULL,
  `title` varchar(191) DEFAULT NULL,
  `is_cover` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`meta`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_product_media_business_id_product_id_index` (`business_id`,`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_product_meta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `description_ar` text DEFAULT NULL,
  `is_addon` tinyint(1) NOT NULL DEFAULT 0,
  `addon_price` decimal(15,2) DEFAULT NULL,
  `addon_unit` varchar(30) DEFAULT NULL,
  `variation_labels_ar` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variation_labels_ar`)),
  `default_unit_ar` varchar(30) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_product_meta_product_unique` (`product_id`),
  KEY `bms_product_meta_business_id_is_addon_index` (`business_id`,`is_addon`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_push_subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `guard` enum('client','employee') NOT NULL DEFAULT 'employee',
  `subject_id` int(10) unsigned NOT NULL,
  `endpoint` text NOT NULL,
  `endpoint_hash` char(64) NOT NULL,
  `p256dh` varchar(255) DEFAULT NULL,
  `auth` varchar(255) DEFAULT NULL,
  `ua` varchar(255) DEFAULT NULL,
  `last_used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_push_endpoint_unique` (`endpoint_hash`),
  KEY `bms_push_subscriptions_business_id_guard_subject_id_index` (`business_id`,`guard`,`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_qc_checklists` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `event_type` varchar(60) DEFAULT NULL,
  `stage` varchar(20) NOT NULL DEFAULT 'pre_event',
  `items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`items`)),
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_qc_checklists_business_id_is_active_index` (`business_id`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_qc_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `inspection_id` bigint(20) unsigned NOT NULL,
  `type` varchar(20) NOT NULL,
  `detail` varchar(500) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_qc_events_business_id_inspection_id_index` (`business_id`,`inspection_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_qc_inspections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_id` bigint(20) unsigned NOT NULL,
  `checklist_id` bigint(20) unsigned DEFAULT NULL,
  `stage` varchar(20) NOT NULL DEFAULT 'pre_event',
  `items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`items`)),
  `score` int(10) unsigned DEFAULT NULL,
  `status` enum('open','passed','failed') NOT NULL DEFAULT 'open',
  `inspector_id` int(10) unsigned DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_qc_inspections_business_id_booking_id_index` (`business_id`,`booking_id`),
  KEY `bms_qc_inspections_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `key` varchar(100) NOT NULL,
  `value` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_settings_business_id_key_unique` (`business_id`,`key`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_task_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `task_id` bigint(20) unsigned NOT NULL,
  `type` varchar(20) NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_task_events_business_id_task_id_index` (`business_id`,`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_task_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `title` varchar(191) NOT NULL,
  `department` varchar(40) NOT NULL,
  `event_type` varchar(60) DEFAULT NULL,
  `offset_days` int(11) NOT NULL DEFAULT 0,
  `offset_hour` tinyint(3) unsigned DEFAULT NULL,
  `assignee_role` varchar(60) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_task_templates_business_id_is_active_index` (`business_id`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `booking_id` bigint(20) unsigned NOT NULL,
  `template_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(191) NOT NULL,
  `department` varchar(40) NOT NULL,
  `due_at` datetime DEFAULT NULL,
  `assignee_id` int(10) unsigned DEFAULT NULL,
  `assignee_role` varchar(60) DEFAULT NULL,
  `status` enum('pending','in_progress','done','cancelled') NOT NULL DEFAULT 'pending',
  `completed_at` datetime DEFAULT NULL,
  `completed_by` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `accepted_by` int(10) unsigned DEFAULT NULL,
  `read_at` datetime DEFAULT NULL,
  `received_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_tasks_business_id_booking_id_index` (`business_id`,`booking_id`),
  KEY `bms_tasks_business_id_status_index` (`business_id`,`status`),
  KEY `bms_tasks_assignee_id_status_index` (`assignee_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_transfer_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `transfer_id` bigint(20) unsigned NOT NULL,
  `kind` enum('consumable','asset') NOT NULL DEFAULT 'consumable',
  `product_id` int(10) unsigned DEFAULT NULL,
  `variation_id` int(10) unsigned DEFAULT NULL,
  `asset_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `accepted_quantity` decimal(10,2) DEFAULT NULL,
  `condition` varchar(60) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_transfer_items_business_id_transfer_id_index` (`business_id`,`transfer_id`),
  KEY `bms_transfer_items_asset_id_index` (`asset_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_transfer_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `type` enum('warehouse_to_hall','hall_to_hall') NOT NULL DEFAULT 'warehouse_to_hall',
  `from_location_id` int(10) unsigned DEFAULT NULL,
  `from_hall_id` bigint(20) unsigned DEFAULT NULL,
  `to_hall_id` bigint(20) unsigned NOT NULL,
  `to_location_id` int(10) unsigned DEFAULT NULL,
  `booking_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('requested','in_transit','accepted','rejected','cancelled') NOT NULL DEFAULT 'requested',
  `requested_by` int(10) unsigned DEFAULT NULL,
  `accepted_by` int(10) unsigned DEFAULT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `stock_refs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`stock_refs`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_transfer_requests_business_id_status_index` (`business_id`,`status`),
  KEY `bms_transfer_requests_to_hall_id_index` (`to_hall_id`),
  KEY `bms_transfer_requests_business_id_booking_id_index` (`business_id`,`booking_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_venues` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `area_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `code` varchar(60) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `working_hours` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`working_hours`)),
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bms_venues_business_id_is_active_index` (`business_id`,`is_active`),
  KEY `bms_venues_business_id_location_id_index` (`business_id`,`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bms_wizard_sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `token` varchar(64) NOT NULL,
  `status` enum('incomplete','complete','converted','abandoned') NOT NULL DEFAULT 'incomplete',
  `current_step` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `contact_name` varchar(191) DEFAULT NULL,
  `contact_phone` varchar(30) DEFAULT NULL,
  `contact_whatsapp` varchar(30) DEFAULT NULL,
  `contact_email` varchar(191) DEFAULT NULL,
  `source` varchar(40) DEFAULT NULL,
  `source_detail` varchar(191) DEFAULT NULL,
  `event_type` varchar(60) DEFAULT NULL,
  `attendees` int(10) unsigned DEFAULT NULL,
  `fnb_level` enum('high_tea','drinks_only','set_menu','open_buffet','custom') DEFAULT NULL,
  `venue_preference` enum('open','closed','both') DEFAULT NULL,
  `candidate_dates` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`candidate_dates`)),
  `budget_min` decimal(15,2) DEFAULT NULL,
  `budget_max` decimal(15,2) DEFAULT NULL,
  `area_id` bigint(20) unsigned DEFAULT NULL,
  `answers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`answers`)),
  `contact_id` int(10) unsigned DEFAULT NULL,
  `lead_id` bigint(20) unsigned DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `last_activity_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bms_wizard_sessions_token_unique` (`token`),
  KEY `bms_wizard_sessions_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bookings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` int(10) unsigned NOT NULL,
  `waiter_id` int(10) unsigned DEFAULT NULL,
  `table_id` int(10) unsigned DEFAULT NULL,
  `correspondent_id` int(11) DEFAULT NULL,
  `business_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `booking_start` datetime NOT NULL,
  `booking_end` datetime NOT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `booking_status` varchar(191) NOT NULL,
  `booking_note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bookings_contact_id_foreign` (`contact_id`),
  KEY `bookings_business_id_foreign` (`business_id`),
  KEY `bookings_created_by_foreign` (`created_by`),
  KEY `bookings_table_id_index` (`table_id`),
  KEY `bookings_waiter_id_index` (`waiter_id`),
  KEY `bookings_location_id_index` (`location_id`),
  KEY `bookings_booking_status_index` (`booking_status`),
  KEY `bookings_correspondent_id_index` (`correspondent_id`),
  CONSTRAINT `bookings_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bookings_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bookings_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brands` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `name` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brands_business_id_foreign` (`business_id`),
  KEY `brands_created_by_foreign` (`created_by`),
  CONSTRAINT `brands_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `brands_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `business` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `demo_slug` varchar(191) DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `start_date` date DEFAULT NULL,
  `tax_number_1` varchar(100) DEFAULT NULL,
  `tax_label_1` varchar(10) DEFAULT NULL,
  `tax_number_2` varchar(100) DEFAULT NULL,
  `tax_label_2` varchar(10) DEFAULT NULL,
  `code_label_1` varchar(191) DEFAULT NULL,
  `code_1` varchar(191) DEFAULT NULL,
  `code_label_2` varchar(191) DEFAULT NULL,
  `code_2` varchar(191) DEFAULT NULL,
  `default_sales_tax` int(10) unsigned DEFAULT NULL,
  `default_profit_percent` double(5,2) NOT NULL DEFAULT 0.00,
  `owner_id` int(10) unsigned NOT NULL,
  `time_zone` varchar(191) NOT NULL DEFAULT 'Asia/Kolkata',
  `fy_start_month` tinyint(4) NOT NULL DEFAULT 1,
  `accounting_method` enum('fifo','lifo','avco') NOT NULL DEFAULT 'fifo',
  `default_sales_discount` decimal(5,2) DEFAULT NULL,
  `sell_price_tax` enum('includes','excludes') NOT NULL DEFAULT 'includes',
  `logo` varchar(191) DEFAULT NULL,
  `sku_prefix` varchar(191) DEFAULT NULL,
  `enable_product_expiry` tinyint(1) NOT NULL DEFAULT 0,
  `expiry_type` enum('add_expiry','add_manufacturing') NOT NULL DEFAULT 'add_expiry',
  `on_product_expiry` enum('keep_selling','stop_selling','auto_delete') NOT NULL DEFAULT 'keep_selling',
  `stop_selling_before` int(11) NOT NULL COMMENT 'Stop selling expied item n days before expiry',
  `enable_tooltip` tinyint(1) NOT NULL DEFAULT 1,
  `purchase_in_diff_currency` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Allow purchase to be in different currency then the business currency',
  `purchase_currency_id` int(10) unsigned DEFAULT NULL,
  `p_exchange_rate` decimal(20,3) NOT NULL DEFAULT 1.000,
  `transaction_edit_days` int(10) unsigned NOT NULL DEFAULT 30,
  `stock_expiry_alert_days` int(10) unsigned NOT NULL DEFAULT 30,
  `keyboard_shortcuts` text DEFAULT NULL,
  `pos_settings` text DEFAULT NULL,
  `essentials_settings` longtext DEFAULT NULL,
  `weighing_scale_setting` text NOT NULL COMMENT 'used to store the configuration of weighing scale',
  `enable_brand` tinyint(1) NOT NULL DEFAULT 1,
  `enable_category` tinyint(1) NOT NULL DEFAULT 1,
  `enable_sub_category` tinyint(1) NOT NULL DEFAULT 1,
  `enable_price_tax` tinyint(1) NOT NULL DEFAULT 1,
  `enable_purchase_status` tinyint(1) DEFAULT 1,
  `enable_lot_number` tinyint(1) NOT NULL DEFAULT 0,
  `default_unit` int(11) DEFAULT NULL,
  `enable_sub_units` tinyint(1) NOT NULL DEFAULT 0,
  `enable_racks` tinyint(1) NOT NULL DEFAULT 0,
  `enable_row` tinyint(1) NOT NULL DEFAULT 0,
  `enable_position` tinyint(1) NOT NULL DEFAULT 0,
  `enable_editing_product_from_purchase` tinyint(1) NOT NULL DEFAULT 1,
  `sales_cmsn_agnt` enum('logged_in_user','user','cmsn_agnt') DEFAULT NULL,
  `item_addition_method` tinyint(1) NOT NULL DEFAULT 1,
  `enable_inline_tax` tinyint(1) NOT NULL DEFAULT 1,
  `currency_symbol_placement` enum('before','after') NOT NULL DEFAULT 'before',
  `enabled_modules` text DEFAULT NULL,
  `module_entitlements` text DEFAULT NULL,
  `date_format` varchar(191) NOT NULL DEFAULT 'm/d/Y',
  `time_format` enum('12','24') NOT NULL DEFAULT '24',
  `currency_precision` tinyint(4) NOT NULL DEFAULT 2,
  `quantity_precision` tinyint(4) NOT NULL DEFAULT 2,
  `ref_no_prefixes` text DEFAULT NULL,
  `theme_color` char(20) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `accounting_settings` text DEFAULT NULL,
  `crm_settings` text DEFAULT NULL,
  `enable_rp` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'rp is the short form of reward points',
  `rp_name` varchar(191) DEFAULT NULL COMMENT 'rp is the short form of reward points',
  `amount_for_unit_rp` decimal(22,4) NOT NULL DEFAULT 1.0000 COMMENT 'rp is the short form of reward points',
  `min_order_total_for_rp` decimal(22,4) NOT NULL DEFAULT 1.0000 COMMENT 'rp is the short form of reward points',
  `max_rp_per_order` int(11) DEFAULT NULL COMMENT 'rp is the short form of reward points',
  `redeem_amount_per_unit_rp` decimal(22,4) NOT NULL DEFAULT 1.0000 COMMENT 'rp is the short form of reward points',
  `min_order_total_for_redeem` decimal(22,4) NOT NULL DEFAULT 1.0000 COMMENT 'rp is the short form of reward points',
  `min_redeem_point` int(11) DEFAULT NULL COMMENT 'rp is the short form of reward points',
  `max_redeem_point` int(11) DEFAULT NULL COMMENT 'rp is the short form of reward points',
  `rp_expiry_period` int(11) DEFAULT NULL COMMENT 'rp is the short form of reward points',
  `rp_expiry_type` enum('month','year') NOT NULL DEFAULT 'year' COMMENT 'rp is the short form of reward points',
  `email_settings` text DEFAULT NULL,
  `sms_settings` text DEFAULT NULL,
  `custom_labels` text DEFAULT NULL,
  `common_settings` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `demo_created_at` timestamp NULL DEFAULT NULL,
  `demo_expires_at` timestamp NULL DEFAULT NULL,
  `demo_status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `business_owner_id_foreign` (`owner_id`),
  KEY `business_currency_id_foreign` (`currency_id`),
  KEY `business_default_sales_tax_foreign` (`default_sales_tax`),
  KEY `business_demo_slug_index` (`demo_slug`),
  KEY `business_demo_expires_at_index` (`demo_expires_at`),
  CONSTRAINT `business_currency_id_foreign` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `business_default_sales_tax_foreign` FOREIGN KEY (`default_sales_tax`) REFERENCES `tax_rates` (`id`),
  CONSTRAINT `business_owner_id_foreign` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `business` VALUES (10,NULL,'VET One',35,'2025-06-18','EG-VET-001','VAT',NULL,NULL,NULL,NULL,NULL,NULL,NULL,25.00,195,'Africa/Cairo',1,'fifo',0.00,'includes','vet-one-logo.png',NULL,0,'add_expiry','keep_selling',0,1,0,NULL,1.000,30,30,'{\"pos\":{\"express_checkout\":\"shift+e\",\"pay_n_ckeckout\":\"shift+p\",\"draft\":\"shift+d\",\"cancel\":\"shift+c\",\"recent_product_quantity\":\"f2\",\"weighing_scale\":null,\"edit_discount\":\"shift+i\",\"edit_order_tax\":\"shift+t\",\"add_payment_row\":\"shift+r\",\"finalize_payment\":\"shift+f\",\"add_new_product\":\"f4\"}}','{\"amount_rounding_method\":null,\"cmmsn_calculation_type\":\"invoice_value\",\"razor_pay_key_id\":null,\"razor_pay_key_secret\":null,\"stripe_public_key\":null,\"stripe_secret_key\":null,\"display_screen_heading\":null,\"cash_denominations\":null,\"enable_cash_denomination_on\":\"pos_screen\",\"disable_pay_checkout\":0,\"disable_draft\":0,\"disable_express_checkout\":0,\"hide_product_suggestion\":0,\"hide_recent_trans\":0,\"disable_discount\":0,\"disable_order_tax\":0,\"is_pos_subtotal_editable\":0}',NULL,'{\"label_prefix\":null,\"product_sku_length\":\"4\",\"qty_length\":\"3\",\"qty_length_decimal\":\"2\"}',1,1,1,1,1,0,NULL,0,0,0,0,1,NULL,1,0,'before','[\"purchases\",\"add_sale\",\"pos_sale\",\"stock_transfers\",\"stock_adjustment\",\"expenses\",\"account\",\"subscription\",\"types_of_service\"]','[\"VetClinic\",\"VetAdmission\",\"VetAI\",\"VetPortal\",\"VetSite\",\"VetStaffPortal\",\"NX600Lab\",\"IdexxLab\",\"AbaxisLab\"]','m/d/Y','24',2,2,'{\"purchase\":\"PO\",\"purchase_return\":null,\"purchase_requisition\":null,\"purchase_order\":null,\"stock_transfer\":\"ST\",\"stock_adjustment\":\"SA\",\"sell_return\":\"CN\",\"expense\":\"EP\",\"contacts\":\"CO\",\"purchase_payment\":\"PP\",\"sell_payment\":\"SP\",\"expense_payment\":null,\"business_location\":\"BL\",\"username\":null,\"subscription\":null,\"draft\":null,\"sales_order\":null}',NULL,NULL,NULL,NULL,0,NULL,1.0000,1.0000,NULL,1.0000,1.0000,NULL,NULL,NULL,'year','{\"mail_driver\":\"smtp\",\"mail_host\":null,\"mail_port\":null,\"mail_username\":null,\"mail_password\":null,\"mail_encryption\":null,\"mail_from_address\":null,\"mail_from_name\":null}','{\"sms_service\":\"other\",\"nexmo_key\":null,\"nexmo_secret\":null,\"nexmo_from\":null,\"twilio_sid\":null,\"twilio_token\":null,\"twilio_from\":null,\"url\":null,\"send_to_param_name\":\"to\",\"msg_param_name\":\"text\",\"request_method\":\"post\",\"header_1\":null,\"header_val_1\":null,\"header_2\":null,\"header_val_2\":null,\"header_3\":null,\"header_val_3\":null,\"param_1\":null,\"param_val_1\":null,\"param_2\":null,\"param_val_2\":null,\"param_3\":null,\"param_val_3\":null,\"param_4\":null,\"param_val_4\":null,\"param_5\":null,\"param_val_5\":null,\"param_6\":null,\"param_val_6\":null,\"param_7\":null,\"param_val_7\":null,\"param_8\":null,\"param_val_8\":null,\"param_9\":null,\"param_val_9\":null,\"param_10\":null,\"param_val_10\":null}','{\"payments\":{\"custom_pay_1\":null,\"custom_pay_2\":null,\"custom_pay_3\":null,\"custom_pay_4\":null,\"custom_pay_5\":null,\"custom_pay_6\":null,\"custom_pay_7\":null},\"contact\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null,\"custom_field_5\":null,\"custom_field_6\":null,\"custom_field_7\":null,\"custom_field_8\":null,\"custom_field_9\":null,\"custom_field_10\":null},\"product\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null,\"custom_field_5\":null,\"custom_field_6\":null,\"custom_field_7\":null,\"custom_field_8\":null,\"custom_field_9\":null,\"custom_field_10\":null,\"custom_field_11\":null,\"custom_field_12\":null,\"custom_field_13\":null,\"custom_field_14\":null,\"custom_field_15\":null,\"custom_field_16\":null,\"custom_field_17\":null,\"custom_field_18\":null,\"custom_field_19\":null,\"custom_field_20\":null},\"product_cf_details\":{\"1\":{\"type\":null,\"dropdown_options\":null},\"2\":{\"type\":null,\"dropdown_options\":null},\"3\":{\"type\":null,\"dropdown_options\":null},\"4\":{\"type\":null,\"dropdown_options\":null},\"5\":{\"type\":null,\"dropdown_options\":null},\"6\":{\"type\":null,\"dropdown_options\":null},\"7\":{\"type\":null,\"dropdown_options\":null},\"8\":{\"type\":null,\"dropdown_options\":null},\"9\":{\"type\":null,\"dropdown_options\":null},\"10\":{\"type\":null,\"dropdown_options\":null},\"11\":{\"type\":null,\"dropdown_options\":null},\"12\":{\"type\":null,\"dropdown_options\":null},\"13\":{\"type\":null,\"dropdown_options\":null},\"14\":{\"type\":null,\"dropdown_options\":null},\"15\":{\"type\":null,\"dropdown_options\":null},\"16\":{\"type\":null,\"dropdown_options\":null},\"17\":{\"type\":null,\"dropdown_options\":null},\"18\":{\"type\":null,\"dropdown_options\":null},\"19\":{\"type\":null,\"dropdown_options\":null},\"20\":{\"type\":null,\"dropdown_options\":null}},\"location\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null},\"user\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null},\"purchase\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null},\"purchase_shipping\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null,\"custom_field_5\":null},\"sell\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null},\"shipping\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null,\"custom_field_5\":null},\"types_of_service\":{\"custom_field_1\":null,\"custom_field_2\":null,\"custom_field_3\":null,\"custom_field_4\":null,\"custom_field_5\":null,\"custom_field_6\":null}}','{\"default_credit_limit\":null,\"default_datatable_page_entries\":\"25\"}',1,'2026-06-17 21:11:53','2026-06-24 22:21:49',NULL,NULL,NULL);
CREATE TABLE `business_locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `location_id` varchar(191) DEFAULT NULL,
  `name` varchar(256) NOT NULL,
  `landmark` text DEFAULT NULL,
  `country` varchar(100) NOT NULL,
  `state` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL,
  `zip_code` char(7) NOT NULL,
  `invoice_scheme_id` int(10) unsigned NOT NULL,
  `sale_invoice_scheme_id` int(11) DEFAULT NULL,
  `invoice_layout_id` int(10) unsigned NOT NULL,
  `sale_invoice_layout_id` int(11) DEFAULT NULL,
  `selling_price_group_id` int(11) DEFAULT NULL,
  `print_receipt_on_invoice` tinyint(1) DEFAULT 1,
  `receipt_printer_type` enum('browser','printer') NOT NULL DEFAULT 'browser',
  `printer_id` int(11) DEFAULT NULL,
  `mobile` varchar(191) DEFAULT NULL,
  `alternate_number` varchar(191) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `website` varchar(191) DEFAULT NULL,
  `featured_products` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `default_payment_accounts` text DEFAULT NULL,
  `custom_field1` varchar(191) DEFAULT NULL,
  `custom_field2` varchar(191) DEFAULT NULL,
  `custom_field3` varchar(191) DEFAULT NULL,
  `custom_field4` varchar(191) DEFAULT NULL,
  `accounting_default_map` text DEFAULT NULL COMMENT 'Default transactions mapping of accounting module',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `business_locations_business_id_index` (`business_id`),
  KEY `business_locations_invoice_scheme_id_foreign` (`invoice_scheme_id`),
  KEY `business_locations_invoice_layout_id_foreign` (`invoice_layout_id`),
  KEY `business_locations_sale_invoice_layout_id_index` (`sale_invoice_layout_id`),
  KEY `business_locations_selling_price_group_id_index` (`selling_price_group_id`),
  KEY `business_locations_receipt_printer_type_index` (`receipt_printer_type`),
  KEY `business_locations_printer_id_index` (`printer_id`),
  CONSTRAINT `business_locations_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `business_locations_invoice_layout_id_foreign` FOREIGN KEY (`invoice_layout_id`) REFERENCES `invoice_layouts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `business_locations_invoice_scheme_id_foreign` FOREIGN KEY (`invoice_scheme_id`) REFERENCES `invoice_schemes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `business_locations` VALUES (31,10,'BL0001','VET One Clinic — New Cairo','90th Street','Egypt','Cairo','New Cairo','11835',12,NULL,12,12,NULL,1,'browser',NULL,'+201092288093','','','https://vetone.test',NULL,1,'{\"cash\":{\"is_enabled\":1,\"account\":null},\"card\":{\"is_enabled\":1,\"account\":null},\"cheque\":{\"is_enabled\":1,\"account\":null},\"bank_transfer\":{\"is_enabled\":1,\"account\":null},\"other\":{\"is_enabled\":1,\"account\":null},\"custom_pay_1\":{\"is_enabled\":1,\"account\":null},\"custom_pay_2\":{\"is_enabled\":1,\"account\":null},\"custom_pay_3\":{\"is_enabled\":1,\"account\":null},\"custom_pay_4\":{\"is_enabled\":1,\"account\":null},\"custom_pay_5\":{\"is_enabled\":1,\"account\":null},\"custom_pay_6\":{\"is_enabled\":1,\"account\":null},\"custom_pay_7\":{\"is_enabled\":1,\"account\":null}}',NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:53','2026-06-17 21:11:53'),(32,10,'BL0002','VET One Clinic — Maadi','Road 9','Egypt','Cairo','Maadi','11835',12,NULL,12,12,NULL,1,'browser',NULL,'+201092288093','','','https://vetone.test',NULL,1,'{\"cash\":{\"is_enabled\":1,\"account\":null},\"card\":{\"is_enabled\":1,\"account\":null},\"cheque\":{\"is_enabled\":1,\"account\":null},\"bank_transfer\":{\"is_enabled\":1,\"account\":null},\"other\":{\"is_enabled\":1,\"account\":null},\"custom_pay_1\":{\"is_enabled\":1,\"account\":null},\"custom_pay_2\":{\"is_enabled\":1,\"account\":null},\"custom_pay_3\":{\"is_enabled\":1,\"account\":null},\"custom_pay_4\":{\"is_enabled\":1,\"account\":null},\"custom_pay_5\":{\"is_enabled\":1,\"account\":null},\"custom_pay_6\":{\"is_enabled\":1,\"account\":null},\"custom_pay_7\":{\"is_enabled\":1,\"account\":null}}',NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:53','2026-06-17 21:11:53'),(33,10,'BL0003','VET One Central Pharmacy Store','Industrial Zone','Egypt','Cairo','New Cairo','11835',12,NULL,12,12,NULL,1,'browser',NULL,'+201092288093','','','https://vetone.test',NULL,1,'{\"cash\":{\"is_enabled\":1,\"account\":null},\"card\":{\"is_enabled\":1,\"account\":null},\"cheque\":{\"is_enabled\":1,\"account\":null},\"bank_transfer\":{\"is_enabled\":1,\"account\":null},\"other\":{\"is_enabled\":1,\"account\":null},\"custom_pay_1\":{\"is_enabled\":1,\"account\":null},\"custom_pay_2\":{\"is_enabled\":1,\"account\":null},\"custom_pay_3\":{\"is_enabled\":1,\"account\":null},\"custom_pay_4\":{\"is_enabled\":1,\"account\":null},\"custom_pay_5\":{\"is_enabled\":1,\"account\":null},\"custom_pay_6\":{\"is_enabled\":1,\"account\":null},\"custom_pay_7\":{\"is_enabled\":1,\"account\":null}}',NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:53','2026-06-17 21:11:53');
CREATE TABLE `cardiology_patient_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `bp_systolic` int(11) DEFAULT NULL,
  `bp_diastolic` int(11) DEFAULT NULL,
  `ejection_fraction` int(11) DEFAULT NULL,
  `rhythm` varchar(40) DEFAULT NULL,
  `nyha` varchar(40) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cardiology_patient_profiles_clc_patient_id_unique` (`clc_patient_id`),
  CONSTRAINT `cardiology_patient_profiles_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cardiothoracic_patient_profiles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `procedure` varchar(120) DEFAULT NULL,
  `ejection_fraction` int(11) DEFAULT NULL,
  `asa_grade` varchar(40) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cardiothoracic_patient_profiles_clc_patient_id_unique` (`clc_patient_id`),
  CONSTRAINT `cardiothoracic_patient_profiles_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cash_denominations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `amount` decimal(22,4) NOT NULL,
  `total_count` int(11) NOT NULL,
  `model_type` varchar(191) NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cash_denominations_model_type_model_id_index` (`model_type`,`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cash_register_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cash_register_id` int(10) unsigned NOT NULL,
  `amount` decimal(22,4) NOT NULL DEFAULT 0.0000,
  `pay_method` varchar(191) DEFAULT NULL,
  `type` enum('debit','credit') NOT NULL,
  `transaction_type` varchar(191) DEFAULT NULL,
  `transaction_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cash_register_transactions_cash_register_id_foreign` (`cash_register_id`),
  KEY `cash_register_transactions_transaction_id_index` (`transaction_id`),
  KEY `cash_register_transactions_type_index` (`type`),
  KEY `cash_register_transactions_transaction_type_index` (`transaction_type`),
  CONSTRAINT `cash_register_transactions_cash_register_id_foreign` FOREIGN KEY (`cash_register_id`) REFERENCES `cash_registers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=140232 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cash_registers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `location_id` int(11) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `status` enum('close','open') NOT NULL DEFAULT 'open',
  `closed_at` datetime DEFAULT NULL,
  `closing_amount` decimal(22,4) NOT NULL DEFAULT 0.0000,
  `total_card_slips` int(11) NOT NULL DEFAULT 0,
  `total_cheques` int(11) NOT NULL DEFAULT 0,
  `denominations` text DEFAULT NULL,
  `closing_note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cash_registers_business_id_foreign` (`business_id`),
  KEY `cash_registers_user_id_foreign` (`user_id`),
  KEY `cash_registers_location_id_index` (`location_id`),
  CONSTRAINT `cash_registers_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cash_registers_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `business_id` int(10) unsigned NOT NULL,
  `short_code` varchar(191) DEFAULT NULL,
  `parent_id` int(11) NOT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `category_type` varchar(191) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `slug` varchar(191) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `categories_business_id_foreign` (`business_id`),
  KEY `categories_created_by_foreign` (`created_by`),
  KEY `categories_parent_id_index` (`parent_id`),
  CONSTRAINT `categories_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `categories_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=213 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `categories` VALUES (162,'Vaccines',5,'VACC',0,164,'product',NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(163,'Pharmacy',5,'PHAR',0,164,'product',NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(164,'Pet Food & Retail',5,'PETF',0,164,'product',NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(165,'Surgical Supplies',5,'SURG',0,164,'product',NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(166,'Boarding',5,'BOAR',0,164,'product',NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(167,'Clinical Services',5,'CLIN',0,164,'product',NULL,NULL,NULL,'2026-06-15 17:18:43','2026-06-15 17:18:43'),(168,'Vaccines',6,'VACC',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(169,'Pharmacy',6,'PHAR',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(170,'Pet Food & Retail',6,'PETF',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(171,'Surgical Supplies',6,'SURG',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(172,'Boarding',6,'BOAR',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(173,'Clinical Services',6,'CLIN',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(174,'Diagnostic Exams',6,'DIAG',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(175,'Laboratory Tests',6,'LABO',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(176,'Controlled Drugs',6,'CONT',0,171,'product',NULL,NULL,NULL,'2026-06-17 20:54:55','2026-06-17 20:54:55'),(177,'Vaccines',7,'VACC',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(178,'Pharmacy',7,'PHAR',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(179,'Pet Food & Retail',7,'PETF',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(180,'Surgical Supplies',7,'SURG',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(181,'Boarding',7,'BOAR',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(182,'Clinical Services',7,'CLIN',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(183,'Diagnostic Exams',7,'DIAG',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(184,'Laboratory Tests',7,'LABO',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(185,'Controlled Drugs',7,'CONT',0,177,'product',NULL,NULL,NULL,'2026-06-17 20:55:44','2026-06-17 20:55:44'),(186,'Vaccines',8,'VACC',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(187,'Pharmacy',8,'PHAR',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(188,'Pet Food & Retail',8,'PETF',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(189,'Surgical Supplies',8,'SURG',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(190,'Boarding',8,'BOAR',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(191,'Clinical Services',8,'CLIN',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(192,'Diagnostic Exams',8,'DIAG',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(193,'Laboratory Tests',8,'LABO',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(194,'Controlled Drugs',8,'CONT',0,183,'product',NULL,NULL,NULL,'2026-06-17 20:58:11','2026-06-17 20:58:11'),(195,'Vaccines',9,'VACC',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(196,'Pharmacy',9,'PHAR',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(197,'Pet Food & Retail',9,'PETF',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(198,'Surgical Supplies',9,'SURG',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(199,'Boarding',9,'BOAR',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(200,'Clinical Services',9,'CLIN',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(201,'Diagnostic Exams',9,'DIAG',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(202,'Laboratory Tests',9,'LABO',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(203,'Controlled Drugs',9,'CONT',0,189,'product',NULL,NULL,NULL,'2026-06-17 21:00:02','2026-06-17 21:00:02'),(204,'Vaccines',10,'VACC',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(205,'Pharmacy',10,'PHAR',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(206,'Pet Food & Retail',10,'PETF',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(207,'Surgical Supplies',10,'SURG',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(208,'Boarding',10,'BOAR',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(209,'Clinical Services',10,'CLIN',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(210,'Diagnostic Exams',10,'DIAG',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(211,'Laboratory Tests',10,'LABO',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54'),(212,'Controlled Drugs',10,'CONT',0,195,'product',NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54');
CREATE TABLE `categorizables` (
  `category_id` int(11) NOT NULL,
  `categorizable_type` varchar(191) NOT NULL,
  `categorizable_id` bigint(20) unsigned NOT NULL,
  KEY `categorizables_categorizable_type_categorizable_id_index` (`categorizable_type`,`categorizable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_documents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `document_name` varchar(191) NOT NULL,
  `document_type` enum('lab_report','xray','prescription','consent_form','discharge_summary','other') NOT NULL DEFAULT 'other',
  `description` text DEFAULT NULL,
  `file_name` varchar(191) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` bigint(20) unsigned DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_appointment_documents_appointment_id_foreign` (`appointment_id`),
  CONSTRAINT `clc_appointment_documents_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `clc_appointments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_edit_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned NOT NULL,
  `requested_by` int(10) unsigned NOT NULL,
  `reason` text NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(10) unsigned DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `review_notes` text DEFAULT NULL,
  `consumed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_appointment_edit_requests_business_id_index` (`business_id`),
  KEY `clc_appointment_edit_requests_appointment_id_index` (`appointment_id`),
  KEY `clc_appointment_edit_requests_requested_by_index` (`requested_by`),
  KEY `clc_appointment_edit_requests_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_exam_findings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned NOT NULL,
  `template_id` int(10) unsigned NOT NULL,
  `template_item_id` int(10) unsigned NOT NULL,
  `region_snapshot` varchar(80) DEFAULT NULL,
  `item_label_snapshot` varchar(200) NOT NULL,
  `side` varchar(8) DEFAULT NULL,
  `value` text DEFAULT NULL,
  `numeric_value` decimal(10,3) DEFAULT NULL,
  `values_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`values_json`)),
  `free_text` text DEFAULT NULL,
  `severity` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `is_critical` tinyint(1) NOT NULL DEFAULT 0,
  `recorded_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_exam_finding_unique` (`appointment_id`,`template_item_id`,`side`),
  KEY `clc_exam_finding_critical` (`appointment_id`,`is_critical`),
  KEY `clc_appointment_exam_findings_business_id_index` (`business_id`),
  KEY `clc_appointment_exam_findings_appointment_id_index` (`appointment_id`),
  KEY `clc_appointment_exam_findings_template_id_index` (`template_id`),
  KEY `clc_appointment_exam_findings_template_item_id_index` (`template_item_id`),
  KEY `clc_appointment_exam_findings_severity_index` (`severity`),
  KEY `clc_appointment_exam_findings_is_critical_index` (`is_critical`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_notes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `appointment_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(150) DEFAULT NULL,
  `notes` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_appointment_notes_appointment_id_index` (`appointment_id`),
  CONSTRAINT `clc_appointment_notes_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `clc_appointments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_reasons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_appointment_reasons_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_status_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned NOT NULL,
  `from_status` varchar(30) DEFAULT NULL,
  `to_status` varchar(30) NOT NULL,
  `changed_by` int(10) unsigned DEFAULT NULL,
  `changed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_appointment_status_logs_business_id_index` (`business_id`),
  KEY `clc_appointment_status_logs_appointment_id_index` (`appointment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointment_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `color` varchar(20) NOT NULL DEFAULT '#2980b9',
  `icon` varchar(50) NOT NULL DEFAULT 'fa-calendar-check',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_appointment_types_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_appointments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `specialty_id` int(10) unsigned DEFAULT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `doctor_id` int(10) unsigned DEFAULT NULL,
  `department_id` int(10) unsigned DEFAULT NULL,
  `schedule_id` int(10) unsigned DEFAULT NULL,
  `ref_no` varchar(30) NOT NULL,
  `appointment_date` date NOT NULL,
  `appointment_time` time NOT NULL,
  `duration_minutes` smallint(5) unsigned NOT NULL DEFAULT 30,
  `weight_kg` decimal(8,2) DEFAULT NULL,
  `temperature` decimal(4,1) DEFAULT NULL,
  `heart_rate` smallint(5) unsigned DEFAULT NULL,
  `respiratory_rate` smallint(5) unsigned DEFAULT NULL,
  `systolic_bp` smallint(5) unsigned DEFAULT NULL,
  `diastolic_bp` smallint(5) unsigned DEFAULT NULL,
  `spo2` tinyint(3) unsigned DEFAULT NULL,
  `blood_glucose` decimal(6,1) DEFAULT NULL,
  `bmi` decimal(5,1) DEFAULT NULL,
  `extra_vitals` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`extra_vitals`)),
  `soap_subjective` text DEFAULT NULL,
  `soap_objective` text DEFAULT NULL,
  `soap_assessment` text DEFAULT NULL,
  `soap_investigations` text DEFAULT NULL,
  `soap_lab_investigations` text DEFAULT NULL,
  `soap_exam_investigations` text DEFAULT NULL,
  `exam_results` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`exam_results`)),
  `next_visit_date` date DEFAULT NULL,
  `next_visit_time` time DEFAULT NULL,
  `next_visit_doctor_id` int(10) unsigned DEFAULT NULL,
  `next_visit_department_id` int(10) unsigned DEFAULT NULL,
  `next_visit_reason` varchar(255) DEFAULT NULL,
  `next_visit_type_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`next_visit_type_ids`)),
  `next_visit_reason_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`next_visit_reason_ids`)),
  `next_visit_notes` text DEFAULT NULL,
  `next_appointment_id` int(10) unsigned DEFAULT NULL,
  `soap_plan` text DEFAULT NULL,
  `soap_plan_meds_json` text DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `reason_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`reason_ids`)),
  `type` enum('checkup','vaccination','surgery','grooming','emergency','other') NOT NULL DEFAULT 'checkup',
  `type_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`type_ids`)),
  `status` enum('pending','confirmed','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
  `arrived_at` timestamp NULL DEFAULT NULL,
  `consultation_started_at` timestamp NULL DEFAULT NULL,
  `consultation_ended_at` timestamp NULL DEFAULT NULL,
  `waiting_minutes` int(10) unsigned DEFAULT NULL,
  `consultation_minutes` int(10) unsigned DEFAULT NULL,
  `has_overlap` tinyint(1) NOT NULL DEFAULT 0,
  `notes` text DEFAULT NULL,
  `doctor_notes` text DEFAULT NULL,
  `erp_sell_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `vitals_critical_count` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `vitals_abnormal_count` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `vitals_critical_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`vitals_critical_json`)),
  `findings_critical_count` smallint(5) unsigned NOT NULL DEFAULT 0,
  `last_critical_check_at` timestamp NULL DEFAULT NULL,
  `ai_subjective_hash` varchar(40) DEFAULT NULL,
  `ai_exam_suggestions_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_exam_suggestions_json`)),
  `ai_symptoms_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_symptoms_json`)),
  `ai_advice_updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_appointments_ref_no_unique` (`ref_no`),
  KEY `clc_appointments_contact_id_foreign` (`contact_id`),
  KEY `clc_appointments_patient_id_foreign` (`patient_id`),
  KEY `clc_appointments_doctor_id_foreign` (`doctor_id`),
  KEY `clc_appointments_department_id_foreign` (`department_id`),
  KEY `clc_appointments_business_id_appointment_date_index` (`business_id`,`appointment_date`),
  KEY `clc_appointments_business_id_status_index` (`business_id`,`status`),
  KEY `clc_appointments_location_id_foreign` (`location_id`),
  KEY `clc_appointments_schedule_id_foreign` (`schedule_id`),
  KEY `clc_appointments_next_visit_doctor_id_index` (`next_visit_doctor_id`),
  KEY `clc_appointments_next_visit_department_id_index` (`next_visit_department_id`),
  KEY `clc_appointments_next_appointment_id_index` (`next_appointment_id`),
  KEY `clc_appointments_vitals_critical_count_index` (`vitals_critical_count`),
  KEY `clc_appointments_findings_critical_count_index` (`findings_critical_count`),
  KEY `clc_appointments_ai_subjective_hash_index` (`ai_subjective_hash`),
  KEY `clc_appointments_specialty_id_index` (`specialty_id`),
  CONSTRAINT `clc_appointments_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_appointments_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_appointments_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `clc_departments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_appointments_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `clc_doctors` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_appointments_location_id_foreign` FOREIGN KEY (`location_id`) REFERENCES `clc_clinic_locations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_appointments_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_appointments_schedule_id_foreign` FOREIGN KEY (`schedule_id`) REFERENCES `clc_doctor_schedules` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_appointments_specialty_id_foreign` FOREIGN KEY (`specialty_id`) REFERENCES `clc_specialties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_authorized_vets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `doctor_id` int(10) unsigned NOT NULL,
  `schedules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`schedules`)),
  `permit_no` varchar(80) DEFAULT NULL,
  `valid_until` date DEFAULT NULL,
  `document_path` varchar(191) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_authorized_vets_business_id_doctor_id_unique` (`business_id`,`doctor_id`),
  KEY `clc_authorized_vets_business_id_index` (`business_id`),
  KEY `clc_authorized_vets_doctor_id_index` (`doctor_id`),
  KEY `clc_authorized_vets_valid_until_index` (`valid_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_birth_death_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `record_type` enum('birth','death') NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `doctor_id` int(10) unsigned DEFAULT NULL,
  `event_date` date NOT NULL,
  `event_time` time DEFAULT NULL,
  `weight_kg` decimal(8,2) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_birth_death_records_patient_id_foreign` (`patient_id`),
  KEY `clc_birth_death_records_business_id_record_type_index` (`business_id`,`record_type`),
  CONSTRAINT `clc_birth_death_records_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_birth_death_records_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_block_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `enable_auto_block` tinyint(1) NOT NULL DEFAULT 0,
  `block_rating_threshold` tinyint(3) unsigned NOT NULL DEFAULT 25,
  `min_ratings_to_evaluate` tinyint(3) unsigned NOT NULL DEFAULT 3,
  `block_zero_credit_limit` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_block_settings_business_id_unique` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_clinic_locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `business_location_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_clinic_locations_business_id_status_index` (`business_id`,`status`),
  KEY `clc_clinic_locations_business_location_id_foreign` (`business_location_id`),
  CONSTRAINT `clc_clinic_locations_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_clinic_locations_business_location_id_foreign` FOREIGN KEY (`business_location_id`) REFERENCES `business_locations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_clinical_photos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `specialty_id` int(10) unsigned DEFAULT NULL,
  `phase` enum('baseline','before','after','progress','other') NOT NULL DEFAULT 'progress',
  `body_area` varchar(80) DEFAULT NULL,
  `caption` varchar(255) DEFAULT NULL,
  `taken_at` date DEFAULT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_name` varchar(191) DEFAULT NULL,
  `file_size` bigint(20) unsigned DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_clinical_photos_business_id_clc_patient_id_index` (`business_id`,`clc_patient_id`),
  KEY `clc_clinical_photos_clc_patient_id_body_area_index` (`clc_patient_id`,`body_area`),
  CONSTRAINT `clc_clinical_photos_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_confidential_notes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `subject_type` varchar(20) NOT NULL,
  `subject_id` bigint(20) unsigned NOT NULL,
  `visibility` enum('normal','confidential') NOT NULL DEFAULT 'normal',
  `title` varchar(191) DEFAULT NULL,
  `body` text NOT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `updated_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_confidential_notes_subject_type_subject_id_index` (`subject_type`,`subject_id`),
  KEY `clc_confidential_notes_business_id_index` (`business_id`),
  KEY `clc_confidential_notes_visibility_index` (`visibility`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_consent_signatures` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `consent_id` int(10) unsigned NOT NULL,
  `method` varchar(20) NOT NULL DEFAULT 'canvas',
  `signed_by_name` varchar(191) NOT NULL,
  `signed_by_relation` varchar(60) DEFAULT NULL,
  `png_path` varchar(255) NOT NULL,
  `file_mime` varchar(100) DEFAULT NULL,
  `file_size` int(10) unsigned DEFAULT NULL,
  `original_filename` varchar(191) DEFAULT NULL,
  `png_hash_sha256` char(64) NOT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `signed_at` timestamp NULL DEFAULT NULL,
  `superseded_at` timestamp NULL DEFAULT NULL,
  `captured_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_consent_signatures_png_hash_sha256_index` (`png_hash_sha256`),
  KEY `clc_consent_signatures_consent_id_superseded_at_index` (`consent_id`,`superseded_at`),
  KEY `clc_consent_signatures_consent_id_index` (`consent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_consent_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `code` varchar(60) NOT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'general',
  `name_en` varchar(191) NOT NULL,
  `name_ar` varchar(191) DEFAULT NULL,
  `body_en` longtext DEFAULT NULL,
  `body_ar` longtext DEFAULT NULL,
  `requires_witness` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_consent_templates_business_id_index` (`business_id`),
  KEY `clc_consent_templates_code_index` (`code`),
  KEY `clc_consent_templates_category_index` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_consents` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `consent_template_id` int(10) unsigned DEFAULT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `patient_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `code` varchar(60) DEFAULT NULL,
  `category` varchar(40) DEFAULT NULL,
  `title` varchar(191) NOT NULL,
  `language` enum('en','ar','both') NOT NULL DEFAULT 'both',
  `body_en` longtext DEFAULT NULL,
  `body_ar` longtext DEFAULT NULL,
  `signatory_name` varchar(191) DEFAULT NULL,
  `signatory_relationship` varchar(60) DEFAULT NULL,
  `witness_name` varchar(191) DEFAULT NULL,
  `issued_by` int(10) unsigned DEFAULT NULL,
  `issued_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `signed_file_path` varchar(191) DEFAULT NULL,
  `signed_file_name` varchar(191) DEFAULT NULL,
  `signed_file_size` int(10) unsigned DEFAULT NULL,
  `signed_mime_type` varchar(100) DEFAULT NULL,
  `signed_uploaded_at` timestamp NULL DEFAULT NULL,
  `signed_uploaded_by` int(10) unsigned DEFAULT NULL,
  `archived_at` timestamp NULL DEFAULT NULL,
  `archived_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_consents_business_id_index` (`business_id`),
  KEY `clc_consents_consent_template_id_index` (`consent_template_id`),
  KEY `clc_consents_appointment_id_index` (`appointment_id`),
  KEY `clc_consents_patient_id_index` (`patient_id`),
  KEY `clc_consents_contact_id_index` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_controlled_daily_seals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `seal_date` date NOT NULL,
  `movement_count` int(10) unsigned NOT NULL DEFAULT 0,
  `first_movement_id` int(10) unsigned DEFAULT NULL,
  `last_movement_id` int(10) unsigned DEFAULT NULL,
  `prev_hash` varchar(64) DEFAULT NULL,
  `hash` varchar(64) NOT NULL,
  `sealed_at` timestamp NULL DEFAULT NULL,
  `sealed_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_controlled_daily_seals_business_id_seal_date_unique` (`business_id`,`seal_date`),
  KEY `clc_controlled_daily_seals_business_id_index` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_controlled_reconciliations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `reference` varchar(30) NOT NULL,
  `medicine_id` int(10) unsigned NOT NULL,
  `batch_no` varchar(60) DEFAULT NULL,
  `expected_qty` decimal(14,3) NOT NULL,
  `counted_qty` decimal(14,3) NOT NULL,
  `variance` decimal(14,3) NOT NULL,
  `unit` varchar(30) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `stocktake_at` datetime NOT NULL,
  `witnessed_by` int(10) unsigned DEFAULT NULL,
  `movement_id` int(10) unsigned DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_controlled_reconciliations_reference_unique` (`reference`),
  KEY `clc_controlled_reconciliations_business_id_medicine_id_index` (`business_id`,`medicine_id`),
  KEY `clc_controlled_reconciliations_business_id_index` (`business_id`),
  KEY `clc_controlled_reconciliations_medicine_id_index` (`medicine_id`),
  KEY `clc_controlled_reconciliations_batch_no_index` (`batch_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_controlled_stock_movements` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `serial_no` varchar(30) NOT NULL,
  `medicine_id` int(10) unsigned NOT NULL,
  `direction` enum('in','out','adjust') NOT NULL,
  `qty` decimal(12,3) NOT NULL,
  `unit` varchar(30) DEFAULT NULL,
  `batch_no` varchar(60) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `balance_after` decimal(14,3) NOT NULL DEFAULT 0.000,
  `reason_code` varchar(30) NOT NULL,
  `source_type` varchar(40) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  `source_line_id` bigint(20) unsigned DEFAULT NULL,
  `patient_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `prescription_id` int(10) unsigned DEFAULT NULL,
  `stocktake_id` int(10) unsigned DEFAULT NULL,
  `doctor_id` int(10) unsigned DEFAULT NULL,
  `witness_user_id` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_controlled_stock_movements_serial_no_unique` (`serial_no`),
  UNIQUE KEY `clc_cm_source_unique` (`business_id`,`source_type`,`source_id`,`source_line_id`),
  KEY `clc_controlled_stock_movements_business_id_index` (`business_id`),
  KEY `clc_controlled_stock_movements_medicine_id_index` (`medicine_id`),
  KEY `clc_controlled_stock_movements_batch_no_index` (`batch_no`),
  KEY `clc_controlled_stock_movements_reason_code_index` (`reason_code`),
  KEY `clc_controlled_stock_movements_patient_id_index` (`patient_id`),
  KEY `clc_controlled_stock_movements_contact_id_index` (`contact_id`),
  KEY `clc_controlled_stock_movements_appointment_id_index` (`appointment_id`),
  KEY `clc_controlled_stock_movements_prescription_id_index` (`prescription_id`),
  KEY `clc_controlled_stock_movements_source_type_index` (`source_type`),
  KEY `clc_controlled_stock_movements_stocktake_id_index` (`stocktake_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_departments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_departments_business_id_index` (`business_id`),
  KEY `clc_departments_location_id_foreign` (`location_id`),
  CONSTRAINT `clc_departments_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_departments_location_id_foreign` FOREIGN KEY (`location_id`) REFERENCES `clc_clinic_locations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_doctor_change_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `requested_by` int(10) unsigned NOT NULL,
  `current_doctor_id` int(10) unsigned DEFAULT NULL,
  `requested_doctor_id` int(10) unsigned NOT NULL,
  `scope` enum('this_appointment','set_preferred') NOT NULL DEFAULT 'this_appointment',
  `reason` text NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(10) unsigned DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `review_notes` text DEFAULT NULL,
  `consumed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_doctor_change_requests_business_id_index` (`business_id`),
  KEY `clc_doctor_change_requests_appointment_id_index` (`appointment_id`),
  KEY `clc_doctor_change_requests_requested_by_index` (`requested_by`),
  KEY `clc_doctor_change_requests_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_doctor_departments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doctor_id` int(10) unsigned NOT NULL,
  `department_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_doctor_departments_doctor_id_department_id_unique` (`doctor_id`,`department_id`),
  KEY `clc_doctor_departments_department_id_foreign` (`department_id`),
  CONSTRAINT `clc_doctor_departments_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `clc_departments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_doctor_departments_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `clc_doctors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_doctor_schedules` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doctor_id` int(10) unsigned NOT NULL,
  `department_id` int(10) unsigned DEFAULT NULL,
  `day_of_week` tinyint(3) unsigned NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `slot_duration_minutes` smallint(5) unsigned NOT NULL DEFAULT 30,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_doctor_schedules_department_id_foreign` (`department_id`),
  KEY `clc_doctor_schedules_doctor_id_department_id_day_of_week_index` (`doctor_id`,`department_id`,`day_of_week`),
  CONSTRAINT `clc_doctor_schedules_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `clc_departments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_doctor_schedules_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `clc_doctors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_doctors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `department_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `specialization` varchar(150) DEFAULT NULL,
  `about` text DEFAULT NULL,
  `consultation_fee` decimal(15,4) NOT NULL DEFAULT 0.0000,
  `product_id` int(10) unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `color` varchar(9) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_doctors_user_id_foreign` (`user_id`),
  KEY `clc_doctors_department_id_foreign` (`department_id`),
  KEY `clc_doctors_business_id_status_index` (`business_id`,`status`),
  KEY `clc_doctors_product_id_index` (`product_id`),
  CONSTRAINT `clc_doctors_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_doctors_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `clc_departments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_doctors_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_evaluation_questions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `category` enum('pet_owner','clinic') NOT NULL,
  `question_text` text NOT NULL,
  `response_type` enum('rating','text','yes_no') NOT NULL DEFAULT 'rating',
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_evaluation_questions_business_id_index` (`business_id`),
  KEY `clc_evaluation_questions_category_index` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_evaluation_responses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `evaluation_id` int(10) unsigned NOT NULL,
  `question_id` int(10) unsigned DEFAULT NULL,
  `question_text_snapshot` text NOT NULL,
  `response_type` enum('rating','text','yes_no') NOT NULL,
  `rating` tinyint(3) unsigned DEFAULT NULL,
  `text_answer` text DEFAULT NULL,
  `yes_no_answer` tinyint(1) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_evaluation_responses_evaluation_id_index` (`evaluation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_evaluations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned NOT NULL,
  `category` enum('pet_owner','clinic') NOT NULL,
  `evaluator_user_id` int(10) unsigned DEFAULT NULL,
  `doctor_id` int(10) unsigned DEFAULT NULL,
  `patient_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `avg_rating` decimal(3,2) DEFAULT NULL,
  `flagged_for_review` tinyint(1) NOT NULL DEFAULT 0,
  `reviewed_by` int(10) unsigned DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `review_notes` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_eval_apt_cat_unique` (`appointment_id`,`category`),
  KEY `clc_evaluations_business_id_index` (`business_id`),
  KEY `clc_evaluations_appointment_id_index` (`appointment_id`),
  KEY `clc_evaluations_category_index` (`category`),
  KEY `clc_evaluations_evaluator_user_id_index` (`evaluator_user_id`),
  KEY `clc_evaluations_doctor_id_index` (`doctor_id`),
  KEY `clc_evaluations_patient_id_index` (`patient_id`),
  KEY `clc_evaluations_contact_id_index` (`contact_id`),
  KEY `clc_evaluations_flagged_for_review_index` (`flagged_for_review`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_exam_not_done_reasons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_exam_not_done_reasons_business_id_index` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_exam_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `active_general_template_id` int(10) unsigned DEFAULT NULL,
  `visible_specialist_template_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`visible_specialist_template_ids`)),
  `enable_ai_suggest` tinyint(1) NOT NULL DEFAULT 0,
  `enable_comparison_with_previous` tinyint(1) NOT NULL DEFAULT 1,
  `enable_voice_fill` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_exam_settings_business_id_unique` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_exam_template_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` int(10) unsigned NOT NULL,
  `region` varchar(80) NOT NULL,
  `item_label` varchar(200) NOT NULL,
  `response_type` enum('select','multi_select','grade','measurement','text','yes_no','body_map') NOT NULL DEFAULT 'select',
  `options_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options_json`)),
  `unit` varchar(20) DEFAULT NULL,
  `normal_low` decimal(10,3) DEFAULT NULL,
  `normal_high` decimal(10,3) DEFAULT NULL,
  `critical_low` decimal(10,3) DEFAULT NULL,
  `critical_high` decimal(10,3) DEFAULT NULL,
  `lateralisation` varchar(12) NOT NULL DEFAULT 'none',
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `help_text` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_exam_tpl_item_order` (`template_id`,`region`,`sort_order`),
  KEY `clc_exam_template_items_template_id_index` (`template_id`),
  KEY `clc_exam_template_items_region_index` (`region`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_exam_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `specialty_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `kind` enum('general','specialist','breed') NOT NULL,
  `species_scope` varchar(30) NOT NULL DEFAULT 'all',
  `display_label` varchar(140) DEFAULT NULL,
  `icon` varchar(60) DEFAULT NULL,
  `colour` varchar(12) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_exam_tpl_lookup` (`business_id`,`kind`,`is_active`),
  KEY `clc_exam_templates_business_id_index` (`business_id`),
  KEY `clc_exam_templates_kind_index` (`kind`),
  KEY `clc_exam_templates_species_scope_index` (`species_scope`),
  KEY `clc_exam_templates_is_active_index` (`is_active`),
  KEY `clc_exam_templates_specialty_id_index` (`specialty_id`),
  CONSTRAINT `clc_exam_templates_specialty_id_foreign` FOREIGN KEY (`specialty_id`) REFERENCES `clc_specialties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_imaging_modalities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `code` varchar(10) NOT NULL,
  `name` varchar(80) NOT NULL,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_imaging_modalities_business_id_index` (`business_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_imaging_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `ref_no` varchar(30) NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `modality_id` int(10) unsigned NOT NULL,
  `body_region` varchar(120) DEFAULT NULL,
  `indication` text DEFAULT NULL,
  `priority` enum('routine','urgent','stat') NOT NULL DEFAULT 'routine',
  `status` enum('requested','in_progress','reported','cancelled') NOT NULL DEFAULT 'requested',
  `report` text DEFAULT NULL,
  `requested_by` int(10) unsigned DEFAULT NULL,
  `reported_by` int(10) unsigned DEFAULT NULL,
  `requested_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `reported_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_imaging_requests_ref_no_unique` (`ref_no`),
  KEY `clc_imaging_requests_business_id_index` (`business_id`),
  KEY `clc_imaging_requests_patient_id_index` (`patient_id`),
  KEY `clc_imaging_requests_appointment_id_index` (`appointment_id`),
  KEY `clc_imaging_requests_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_imaging_studies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `imaging_request_id` int(10) unsigned NOT NULL,
  `study_uid` varchar(80) DEFAULT NULL,
  `file_path` varchar(255) NOT NULL,
  `original_name` varchar(191) DEFAULT NULL,
  `file_mime` varchar(100) DEFAULT NULL,
  `file_size` int(10) unsigned DEFAULT NULL,
  `thumb_path` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `uploaded_by` int(10) unsigned DEFAULT NULL,
  `uploaded_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_imaging_studies_imaging_request_id_index` (`imaging_request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_medical_conditions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `icd_code` varchar(20) DEFAULT NULL,
  `is_chronic` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_medical_conditions_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_medical_reports` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `doctor_id` int(10) unsigned DEFAULT NULL,
  `report_type` enum('lab','xray','ultrasound','ecg','other') NOT NULL DEFAULT 'lab',
  `source` varchar(30) NOT NULL DEFAULT 'manual',
  `exam_key` varchar(100) DEFAULT NULL,
  `report_title` varchar(200) NOT NULL,
  `findings` text DEFAULT NULL,
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_medical_reports_patient_id_foreign` (`patient_id`),
  KEY `clc_medical_reports_business_id_patient_id_index` (`business_id`,`patient_id`),
  KEY `clc_medrep_apt_examkey_idx` (`appointment_id`,`exam_key`),
  CONSTRAINT `clc_medical_reports_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_medical_reports_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_medicine_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_medicine_categories_business_id_status_index` (`business_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_medicines` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `generic_name` varchar(150) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `controlled_schedule` varchar(30) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_medicines_category_id_foreign` (`category_id`),
  KEY `clc_medicines_business_id_status_index` (`business_id`,`status`),
  KEY `clc_medicines_controlled_schedule_index` (`controlled_schedule`),
  CONSTRAINT `clc_medicines_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_medicines_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `clc_medicine_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_module_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `key` varchar(100) NOT NULL,
  `value` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_module_settings_business_id_key_unique` (`business_id`,`key`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_patient_conditions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `patient_id` int(10) unsigned NOT NULL,
  `condition_id` int(10) unsigned NOT NULL,
  `diagnosed_date` date DEFAULT NULL,
  `diagnosed_by` int(10) unsigned DEFAULT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `severity` enum('mild','moderate','severe') DEFAULT NULL,
  `status` enum('active','resolved','chronic') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_patient_conditions_condition_id_foreign` (`condition_id`),
  KEY `clc_patient_conditions_patient_id_index` (`patient_id`),
  CONSTRAINT `clc_patient_conditions_condition_id_foreign` FOREIGN KEY (`condition_id`) REFERENCES `clc_medical_conditions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_patient_conditions_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_patient_contact_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_phone` varchar(30) DEFAULT NULL,
  `emergency_contact_relation` varchar(50) DEFAULT NULL,
  `occupation` varchar(100) DEFAULT NULL,
  `referred_by` varchar(100) DEFAULT NULL,
  `preferred_doctor_id` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `email_confirmed` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Owner has confirmed email — vaccine reminder emails will only be sent to confirmed addresses',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `email_confirm_token` varchar(100) DEFAULT NULL,
  `email_confirm_sent_at` timestamp NULL DEFAULT NULL,
  `is_blocked` tinyint(1) NOT NULL DEFAULT 0,
  `blocked_at` timestamp NULL DEFAULT NULL,
  `blocked_reason` text DEFAULT NULL,
  `blocked_by` int(10) unsigned DEFAULT NULL,
  `credit_limit_backup` decimal(14,2) DEFAULT NULL,
  `block_avg_rating` decimal(3,2) DEFAULT NULL,
  `block_rating_count` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_patient_contact_details_contact_id_unique` (`contact_id`),
  KEY `clc_patient_contact_details_business_id_index` (`business_id`),
  KEY `clc_patient_contact_details_preferred_doctor_id_index` (`preferred_doctor_id`),
  KEY `clc_patient_contact_details_is_blocked_index` (`is_blocked`),
  CONSTRAINT `clc_patient_contact_details_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_patient_contact_details_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_patient_treatment_plans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `clc_patient_id` int(10) unsigned NOT NULL,
  `package_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `total_sessions` smallint(5) unsigned NOT NULL DEFAULT 1,
  `price` decimal(22,4) DEFAULT NULL,
  `status` enum('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `started_at` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_patient_treatment_plans_clc_patient_id_foreign` (`clc_patient_id`),
  KEY `clc_plans_biz_pat_status_idx` (`business_id`,`clc_patient_id`,`status`),
  CONSTRAINT `clc_patient_treatment_plans_clc_patient_id_foreign` FOREIGN KEY (`clc_patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_patients` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `default_specialty_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `patient_name` varchar(100) DEFAULT NULL,
  `gender` enum('male','female','unknown') NOT NULL DEFAULT 'unknown',
  `dob` date DEFAULT NULL,
  `bloodgroup` varchar(20) DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `chronic_conditions` text DEFAULT NULL,
  `family_history` text DEFAULT NULL,
  `species` varchar(50) DEFAULT NULL,
  `breed` varchar(100) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `weight_kg` decimal(8,2) DEFAULT NULL,
  `microchip_no` varchar(50) DEFAULT NULL,
  `serial_no` varchar(50) DEFAULT NULL,
  `preferred_doctor_id` int(10) unsigned DEFAULT NULL,
  `neutered` tinyint(1) NOT NULL DEFAULT 0,
  `deceased` tinyint(1) NOT NULL DEFAULT 0,
  `deceased_date` date DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `cert_verify_token` varchar(64) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `height_cm` decimal(5,1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_patients_cert_verify_token_unique` (`cert_verify_token`),
  KEY `clc_patients_contact_id_foreign` (`contact_id`),
  KEY `clc_patients_business_id_contact_id_index` (`business_id`,`contact_id`),
  KEY `clc_patients_business_id_status_index` (`business_id`,`status`),
  KEY `clc_patients_preferred_doctor_id_index` (`preferred_doctor_id`),
  KEY `clc_patients_default_specialty_id_foreign` (`default_specialty_id`),
  CONSTRAINT `clc_patients_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_patients_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_patients_default_specialty_id_foreign` FOREIGN KEY (`default_specialty_id`) REFERENCES `clc_specialties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_preferred_doctor_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned DEFAULT NULL,
  `old_doctor_id` int(10) unsigned DEFAULT NULL,
  `new_doctor_id` int(10) unsigned DEFAULT NULL,
  `changed_by` int(10) unsigned DEFAULT NULL,
  `source` enum('manual','doctor_change_request') NOT NULL DEFAULT 'manual',
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `reason` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_preferred_doctor_history_business_id_index` (`business_id`),
  KEY `clc_preferred_doctor_history_contact_id_index` (`contact_id`),
  KEY `clc_preferred_doctor_history_patient_id_index` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_prescription_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `prescription_id` int(10) unsigned NOT NULL,
  `medicine_id` int(10) unsigned DEFAULT NULL,
  `medicine_name` varchar(150) NOT NULL,
  `dosage` varchar(100) DEFAULT NULL,
  `frequency` varchar(100) DEFAULT NULL,
  `duration_days` smallint(5) unsigned DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `quantity` smallint(5) unsigned NOT NULL DEFAULT 1,
  `purchase_line_id` int(10) unsigned DEFAULT NULL,
  `dispensed_qty` decimal(22,4) DEFAULT NULL,
  `dispensed_at` timestamp NULL DEFAULT NULL,
  `dispensed_by` int(10) unsigned DEFAULT NULL,
  `location_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_prescription_items_prescription_id_index` (`prescription_id`),
  CONSTRAINT `clc_prescription_items_prescription_id_foreign` FOREIGN KEY (`prescription_id`) REFERENCES `clc_prescriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_prescriptions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `ref_no` varchar(30) NOT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `doctor_id` int(10) unsigned DEFAULT NULL,
  `prescribed_date` date NOT NULL,
  `valid_until` date DEFAULT NULL,
  `diagnosis` text DEFAULT NULL,
  `status` enum('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_prescriptions_ref_no_unique` (`ref_no`),
  KEY `clc_prescriptions_appointment_id_foreign` (`appointment_id`),
  KEY `clc_prescriptions_contact_id_foreign` (`contact_id`),
  KEY `clc_prescriptions_patient_id_foreign` (`patient_id`),
  KEY `clc_prescriptions_doctor_id_foreign` (`doctor_id`),
  KEY `clc_prescriptions_business_id_patient_id_index` (`business_id`,`patient_id`),
  CONSTRAINT `clc_prescriptions_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `clc_appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_prescriptions_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_prescriptions_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_prescriptions_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `clc_doctors` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_prescriptions_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_quotation_meta` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `transaction_id` int(10) unsigned NOT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `patient_id` int(10) unsigned DEFAULT NULL,
  `approval_token` varchar(64) DEFAULT NULL,
  `status` enum('draft','sent','viewed','approved','rejected','expired') NOT NULL DEFAULT 'draft',
  `sent_at` timestamp NULL DEFAULT NULL,
  `viewed_at` timestamp NULL DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `approved_by_name` varchar(191) DEFAULT NULL,
  `approval_ip` varchar(45) DEFAULT NULL,
  `approval_signature_path` varchar(255) DEFAULT NULL,
  `rejected_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `converted_sell_id` int(10) unsigned DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_quotation_meta_transaction_id_unique` (`transaction_id`),
  UNIQUE KEY `clc_quotation_meta_approval_token_unique` (`approval_token`),
  KEY `clc_quotation_meta_business_id_index` (`business_id`),
  KEY `clc_quotation_meta_appointment_id_index` (`appointment_id`),
  KEY `clc_quotation_meta_patient_id_index` (`patient_id`),
  KEY `clc_quotation_meta_status_index` (`status`),
  KEY `clc_quotation_meta_converted_sell_id_index` (`converted_sell_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_reminder_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `vaccination_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `channel` enum('email','wa') NOT NULL DEFAULT 'email',
  `template_type` enum('email_due','email_overdue','wa_due','wa_overdue') NOT NULL,
  `sent_to` varchar(255) DEFAULT NULL COMMENT 'Email or phone number used',
  `status` enum('sent','failed') NOT NULL DEFAULT 'sent',
  `error_message` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_reminder_logs_vaccination_id_channel_template_type_index` (`vaccination_id`,`channel`,`template_type`),
  KEY `clc_reminder_logs_business_id_sent_at_index` (`business_id`,`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_reminder_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `days_before_due` tinyint(3) unsigned NOT NULL DEFAULT 7 COMMENT 'Send reminder X days before the scheduled vaccine date',
  `send_overdue` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Also send reminders for overdue vaccinations',
  `overdue_reminder_interval` tinyint(3) unsigned NOT NULL DEFAULT 7 COMMENT 'Re-send overdue reminder every X days (0 = only once)',
  `max_reminders_per_vaccine` tinyint(3) unsigned NOT NULL DEFAULT 3 COMMENT 'Maximum reminder emails per vaccination record',
  `email_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sms_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `wa_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `schedule_time` varchar(5) NOT NULL DEFAULT '08:00' COMMENT 'Preferred time to run daily reminders (HH:MM, display only)',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_reminder_settings_business_id_unique` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_reminder_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL COMMENT 'NULL = system default, set to business_id for customised copy',
  `type` enum('email_due','email_overdue','wa_due','wa_overdue','sms_due','sms_overdue') NOT NULL,
  `name` varchar(100) NOT NULL,
  `subject` varchar(255) DEFAULT NULL COMMENT 'Email subject (null for WA)',
  `body` text NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_system_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_role_landing_pages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  `landing_url` varchar(191) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_role_landing_pages_business_id_role_id_unique` (`business_id`,`role_id`),
  KEY `clc_role_landing_pages_business_id_index` (`business_id`),
  KEY `clc_role_landing_pages_role_id_index` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_role_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  `key` varchar(60) NOT NULL,
  `value` varchar(191) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_role_settings_business_id_role_id_key_unique` (`business_id`,`role_id`,`key`),
  KEY `clc_role_settings_business_id_index` (`business_id`),
  KEY `clc_role_settings_role_id_index` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_soap_options` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `section` enum('complaint','lab_test','exam_test','frequency','body_system') NOT NULL,
  `species` varchar(20) NOT NULL DEFAULT 'all',
  `group_name` varchar(60) DEFAULT NULL,
  `group_icon` varchar(40) DEFAULT NULL,
  `group_color` varchar(20) DEFAULT NULL,
  `group_bg` varchar(20) DEFAULT NULL,
  `label` varchar(120) NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_soap_options_business_id_index` (`business_id`),
  KEY `clc_soap_options_product_id_index` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_specialties` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `code` varchar(60) NOT NULL,
  `name` varchar(120) NOT NULL,
  `slug` varchar(120) DEFAULT NULL,
  `group` varchar(80) DEFAULT NULL,
  `icon` varchar(60) DEFAULT NULL,
  `color` varchar(20) DEFAULT NULL,
  `is_structural` tinyint(1) NOT NULL DEFAULT 0,
  `driver_key` varchar(60) DEFAULT NULL,
  `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`config`)),
  `capabilities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`capabilities`)),
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_specialties_business_id_code_unique` (`business_id`,`code`),
  KEY `clc_specialties_business_id_is_active_index` (`business_id`,`is_active`),
  CONSTRAINT `clc_specialties_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_system_findings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `system_key` varchar(40) NOT NULL,
  `value` varchar(60) NOT NULL,
  `label` varchar(160) NOT NULL,
  `supports_severity` tinyint(1) NOT NULL DEFAULT 0,
  `supports_lr` tinyint(1) NOT NULL DEFAULT 0,
  `is_critical` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clc_sysf_uniq` (`business_id`,`system_key`,`value`),
  KEY `clc_system_findings_business_id_index` (`business_id`),
  KEY `clc_system_findings_system_key_index` (`system_key`),
  KEY `clc_system_findings_is_critical_index` (`is_critical`),
  KEY `clc_system_findings_status_index` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_treatment_packages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `specialty_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `total_sessions` smallint(5) unsigned NOT NULL DEFAULT 1,
  `interval_days` smallint(5) unsigned DEFAULT NULL,
  `price` decimal(22,4) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_treatment_packages_business_id_is_active_index` (`business_id`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_treatment_plan_sessions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `plan_id` int(10) unsigned NOT NULL,
  `session_no` smallint(5) unsigned NOT NULL,
  `status` enum('scheduled','completed','skipped') NOT NULL DEFAULT 'scheduled',
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `performed_by` int(10) unsigned DEFAULT NULL,
  `performed_at` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_treatment_plan_sessions_plan_id_status_index` (`plan_id`,`status`),
  CONSTRAINT `clc_treatment_plan_sessions_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `clc_patient_treatment_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_vaccinations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `vaccine_type_id` int(10) unsigned NOT NULL,
  `dose` varchar(100) DEFAULT NULL,
  `appointment_id` int(10) unsigned DEFAULT NULL,
  `medicine_id` int(10) unsigned DEFAULT NULL,
  `batch_no` varchar(50) DEFAULT NULL,
  `administered_date` date NOT NULL,
  `next_due_date` date DEFAULT NULL,
  `administered_by` int(10) unsigned DEFAULT NULL,
  `weight_at_time` decimal(8,2) DEFAULT NULL,
  `temperature_at_time` decimal(5,2) DEFAULT NULL,
  `status` enum('scheduled','administered','overdue','cancelled') NOT NULL DEFAULT 'administered',
  `taken_status` enum('taken_in','taken_out','not_taken') DEFAULT NULL COMMENT 'taken_in=at clinic, taken_out=external, not_taken=scheduled/not yet given',
  `reminder_sent` tinyint(1) NOT NULL DEFAULT 0,
  `waived_at` timestamp NULL DEFAULT NULL,
  `waived_reason` varchar(191) DEFAULT NULL,
  `waived_note` varchar(191) DEFAULT NULL,
  `waived_by` bigint(20) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_vaccinations_patient_id_foreign` (`patient_id`),
  KEY `clc_vaccinations_contact_id_foreign` (`contact_id`),
  KEY `clc_vaccinations_vaccine_type_id_foreign` (`vaccine_type_id`),
  KEY `clc_vaccinations_appointment_id_foreign` (`appointment_id`),
  KEY `clc_vaccinations_business_id_patient_id_index` (`business_id`,`patient_id`),
  KEY `clc_vaccinations_business_id_next_due_date_index` (`business_id`,`next_due_date`),
  CONSTRAINT `clc_vaccinations_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `clc_appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clc_vaccinations_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_vaccinations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_vaccinations_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `clc_patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clc_vaccinations_vaccine_type_id_foreign` FOREIGN KEY (`vaccine_type_id`) REFERENCES `clc_vaccine_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_vaccine_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `manufacturer` varchar(100) DEFAULT NULL,
  `category` enum('core_vaccine','non_core_vaccine','spot_on','deworming','oral','nasal','topical','preventive','other') NOT NULL DEFAULT 'core_vaccine',
  `species_target` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`species_target`)),
  `description` text DEFAULT NULL,
  `valid_for_months` smallint(5) unsigned NOT NULL DEFAULT 12,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_vaccine_types_business_id_status_index` (`business_id`,`status`),
  KEY `clc_vaccine_types_product_id_index` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_vital_ranges` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned DEFAULT NULL,
  `species` varchar(40) NOT NULL,
  `life_stage` enum('pediatric','adult','senior','all') NOT NULL DEFAULT 'all',
  `temperature_low` decimal(5,2) DEFAULT NULL,
  `temperature_high` decimal(5,2) DEFAULT NULL,
  `heart_rate_low` smallint(5) unsigned DEFAULT NULL,
  `heart_rate_high` smallint(5) unsigned DEFAULT NULL,
  `respiratory_rate_low` smallint(5) unsigned DEFAULT NULL,
  `respiratory_rate_high` smallint(5) unsigned DEFAULT NULL,
  `weight_low_kg` decimal(6,2) DEFAULT NULL,
  `weight_high_kg` decimal(6,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_vital_ranges_business_id_index` (`business_id`),
  KEY `clc_vital_ranges_species_index` (`species`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_wellness_benefit_usage` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_id` int(10) unsigned NOT NULL,
  `benefit_id` int(10) unsigned NOT NULL,
  `used_at` datetime NOT NULL,
  `qty` decimal(8,2) NOT NULL DEFAULT 1.00,
  `discounted_amount` decimal(22,4) DEFAULT NULL,
  `source_type` varchar(30) DEFAULT NULL,
  `source_id` int(10) unsigned DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_wellness_benefit_usage_subscription_id_index` (`subscription_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_wellness_plan_benefits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `plan_id` int(10) unsigned NOT NULL,
  `kind` enum('visits','vaccines','products','discount_pct') NOT NULL,
  `label` varchar(120) NOT NULL,
  `quantity_per_year` decimal(8,2) DEFAULT NULL,
  `value` decimal(22,4) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_wellness_plan_benefits_plan_id_index` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_wellness_plans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` text DEFAULT NULL,
  `price_monthly` decimal(22,4) DEFAULT NULL,
  `price_annual` decimal(22,4) DEFAULT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_wellness_plans_business_id_index` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clc_wellness_subscriptions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `patient_id` int(10) unsigned NOT NULL,
  `plan_id` int(10) unsigned NOT NULL,
  `status` enum('active','paused','cancelled','expired') NOT NULL DEFAULT 'active',
  `billing_cycle` enum('monthly','annual') NOT NULL DEFAULT 'monthly',
  `started_at` date NOT NULL,
  `renews_at` date DEFAULT NULL,
  `ended_at` date DEFAULT NULL,
  `erp_recurring_transaction_id` int(10) unsigned DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clc_wellness_subscriptions_business_id_patient_id_index` (`business_id`,`patient_id`),
  KEY `clc_wellness_subscriptions_business_id_index` (`business_id`),
  KEY `clc_wellness_subscriptions_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `type` varchar(191) NOT NULL,
  `contact_type` varchar(191) DEFAULT NULL,
  `land_mark` varchar(191) DEFAULT NULL,
  `street_name` varchar(191) DEFAULT NULL,
  `building_number` varchar(191) DEFAULT NULL,
  `additional_number` varchar(191) DEFAULT NULL,
  `supplier_business_name` varchar(191) DEFAULT NULL,
  `name` varchar(191) DEFAULT NULL,
  `prefix` varchar(191) DEFAULT NULL,
  `first_name` varchar(191) DEFAULT NULL,
  `middle_name` varchar(191) DEFAULT NULL,
  `last_name` varchar(191) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `contact_id` varchar(191) DEFAULT NULL,
  `contact_status` varchar(191) NOT NULL DEFAULT 'active',
  `tax_number` varchar(191) DEFAULT NULL,
  `city` varchar(191) DEFAULT NULL,
  `state` varchar(191) DEFAULT NULL,
  `country` varchar(191) DEFAULT NULL,
  `address_line_1` text DEFAULT NULL,
  `address_line_2` text DEFAULT NULL,
  `zip_code` varchar(191) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `mobile` varchar(191) NOT NULL,
  `bms_portal_password` varchar(191) DEFAULT NULL,
  `landline` varchar(191) DEFAULT NULL,
  `alternate_number` varchar(191) DEFAULT NULL,
  `pay_term_number` int(11) DEFAULT NULL,
  `pay_term_type` enum('days','months') DEFAULT NULL,
  `credit_limit` decimal(22,4) DEFAULT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `converted_by` int(11) DEFAULT NULL,
  `converted_on` datetime DEFAULT NULL,
  `balance` decimal(22,4) NOT NULL DEFAULT 0.0000,
  `total_rp` int(11) NOT NULL DEFAULT 0 COMMENT 'rp is the short form of reward points',
  `total_rp_used` int(11) NOT NULL DEFAULT 0 COMMENT 'rp is the short form of reward points',
  `total_rp_expired` int(11) NOT NULL DEFAULT 0 COMMENT 'rp is the short form of reward points',
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `shipping_address` text DEFAULT NULL,
  `shipping_custom_field_details` longtext DEFAULT NULL,
  `is_export` tinyint(1) NOT NULL DEFAULT 0,
  `export_custom_field_1` varchar(191) DEFAULT NULL,
  `export_custom_field_2` varchar(191) DEFAULT NULL,
  `export_custom_field_3` varchar(191) DEFAULT NULL,
  `export_custom_field_4` varchar(191) DEFAULT NULL,
  `export_custom_field_5` varchar(191) DEFAULT NULL,
  `export_custom_field_6` varchar(191) DEFAULT NULL,
  `position` varchar(191) DEFAULT NULL,
  `customer_group_id` int(11) DEFAULT NULL,
  `crm_source` varchar(191) DEFAULT NULL,
  `crm_life_stage` varchar(191) DEFAULT NULL,
  `custom_field1` varchar(191) DEFAULT NULL,
  `custom_field2` varchar(191) DEFAULT NULL,
  `custom_field3` varchar(191) DEFAULT NULL,
  `custom_field4` varchar(191) DEFAULT NULL,
  `custom_field5` varchar(191) DEFAULT NULL,
  `custom_field6` varchar(191) DEFAULT NULL,
  `custom_field7` varchar(191) DEFAULT NULL,
  `custom_field8` varchar(191) DEFAULT NULL,
  `custom_field9` varchar(191) DEFAULT NULL,
  `custom_field10` varchar(191) DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `vc_has_whatsapp` tinyint(1) DEFAULT NULL,
  `drm_has_whatsapp` tinyint(1) DEFAULT NULL,
  `dnt_has_whatsapp` tinyint(1) DEFAULT NULL,
  `clc_has_whatsapp` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contacts_business_id_foreign` (`business_id`),
  KEY `contacts_created_by_foreign` (`created_by`),
  KEY `contacts_type_index` (`type`),
  KEY `contacts_contact_status_index` (`contact_status`),
  KEY `contacts_crm_source_index` (`crm_source`),
  KEY `contacts_crm_life_stage_index` (`crm_life_stage`),
  KEY `contacts_converted_by_index` (`converted_by`),
  KEY `contacts_vc_has_whatsapp_index` (`vc_has_whatsapp`),
  KEY `contacts_drm_has_whatsapp_index` (`drm_has_whatsapp`),
  KEY `contacts_dnt_has_whatsapp_index` (`dnt_has_whatsapp`),
  KEY `contacts_clc_has_whatsapp_index` (`clc_has_whatsapp`),
  CONSTRAINT `contacts_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `contacts_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `contacts` VALUES (6088,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Walk-In Customer',NULL,NULL,NULL,NULL,NULL,'CO0001','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'',NULL,NULL,NULL,NULL,NULL,0.0000,195,NULL,NULL,0.0000,0,0,0,1,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:53','2026-06-17 21:11:53',NULL,NULL,NULL,NULL),(6089,10,'supplier',NULL,NULL,NULL,NULL,NULL,'Cairo Vet Pharma Distributors','Cairo Vet Pharma Distributors',NULL,NULL,NULL,NULL,NULL,'CO0002','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6090,10,'supplier',NULL,NULL,NULL,NULL,NULL,'Nile Pet Food Supplies','Nile Pet Food Supplies',NULL,NULL,NULL,NULL,NULL,'CO0003','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6091,10,'supplier',NULL,NULL,NULL,NULL,NULL,'MedVet Surgical Supplies','MedVet Surgical Supplies',NULL,NULL,NULL,NULL,NULL,'CO0004','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6092,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Ahmed Saleh',NULL,NULL,NULL,NULL,NULL,'CO0005','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6093,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Mona Khalil',NULL,NULL,NULL,NULL,NULL,'CO0006','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6094,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Karim Adel',NULL,NULL,NULL,NULL,NULL,'CO0007','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6095,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Sara Ibrahim',NULL,NULL,NULL,NULL,NULL,'CO0008','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6096,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Khaled Hassan',NULL,NULL,NULL,NULL,NULL,'CO0009','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6097,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Layla Mahmoud',NULL,NULL,NULL,NULL,NULL,'CO0010','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6098,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Youssef Nabil',NULL,NULL,NULL,NULL,NULL,'CO0011','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6099,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Dina Farouk',NULL,NULL,NULL,NULL,NULL,'CO0012','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6100,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Hany Wagdy',NULL,NULL,NULL,NULL,NULL,'CO0013','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6101,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Nour Saad',NULL,NULL,NULL,NULL,NULL,'CO0014','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6102,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Mostafa Zaki',NULL,NULL,NULL,NULL,NULL,'CO0015','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL),(6103,10,'customer',NULL,NULL,NULL,NULL,NULL,NULL,'Rania Samir',NULL,NULL,NULL,NULL,NULL,'CO0016','active',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+201092288093',NULL,NULL,NULL,NULL,NULL,NULL,195,NULL,NULL,0.0000,0,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-06-17 21:11:54','2026-06-17 21:11:54',NULL,NULL,NULL,NULL);
CREATE TABLE `crm_call_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `call_type` varchar(191) DEFAULT NULL,
  `mobile_number` varchar(191) NOT NULL,
  `mobile_name` varchar(191) DEFAULT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crm_call_logs_business_id_index` (`business_id`),
  KEY `crm_call_logs_user_id_index` (`user_id`),
  KEY `crm_call_logs_contact_id_index` (`contact_id`),
  KEY `crm_call_logs_created_by_index` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `crm_campaigns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `business_id` int(10) unsigned NOT NULL,
  `name` varchar(191) NOT NULL,
  `campaign_type` enum('sms','email') NOT NULL DEFAULT 'email',
  `subject` varchar(191) DEFAULT NULL,
  `email_body` text DEFAULT NULL,
  `sms_body` text DEFAULT NULL,
  `sent_on` datetime DEFAULT NULL,
  `contact_ids` text NOT NULL,
  `additional_info` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crm_campaigns_business_id_foreign` (`business_id`),
  KEY `crm_campaigns_created_by_index` (`created_by`),
  CONSTRAINT `crm_campaigns_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

