-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `prolex`
--

-- --------------------------------------------------------

--
-- Table structure for table `account_types`
--

CREATE TABLE IF NOT EXISTS `account_types` (
  `account_type_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_type_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `assignments`
--

CREATE TABLE IF NOT EXISTS `assignments` (
  `assignment_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `assignment_title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lawyer_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `task_type` enum('document_review','client_meeting','court_appearance','research','filing','correspondence','administrative') COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` enum('low','medium','high') COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_id` bigint UNSIGNED DEFAULT NULL,
  `case_id` bigint UNSIGNED DEFAULT NULL,
  `case_type_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `case_category_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `due_date` date NOT NULL,
  `due_time` time DEFAULT NULL,
  `estimated_hours` decimal(5,2) DEFAULT NULL,
  `status` enum('pending','in_progress','completed','cancelled') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `instructions` text COLLATE utf8mb4_unicode_ci,
  `documents_required` text COLLATE utf8mb4_unicode_ci,
  `court_date` date DEFAULT NULL,
  `court_time` time DEFAULT NULL,
  `court_location` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opposing_counsel` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `judge_id` bigint UNSIGNED DEFAULT NULL,
  `client_contact_info` text COLLATE utf8mb4_unicode_ci,
  `billing_notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`assignment_id`),
  KEY `lawyer_id` (`lawyer_id`),
  KEY `client_id` (`client_id`),
  KEY `case_id` (`case_id`),
  KEY `judge_id` (`judge_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `audit_logs`
--

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `log_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `action_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `table_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `record_id` bigint UNSIGNED DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `old_value` json DEFAULT NULL,
  `new_value` json DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `log_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `idx_audit_user_id` (`user_id`),
  KEY `idx_audit_action_type` (`action_type`),
  KEY `idx_audit_table_name` (`table_name`),
  KEY `idx_audit_log_time` (`log_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `cache_store`
--

CREATE TABLE IF NOT EXISTS `cache_store` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `cache_key` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cache_value` text COLLATE utf8mb4_unicode_ci,
  `expires_at` timestamp NULL DEFAULT NULL,
  `is_locked` tinyint(1) DEFAULT '0',
  `locked_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lock_expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cache_key` (`cache_key`),
  KEY `idx_cache_expires` (`expires_at`),
  KEY `idx_cache_locked` (`is_locked`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `cases`
--

CREATE TABLE IF NOT EXISTS `cases` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_id` varchar(50) NOT NULL,
  `case_name` varchar(255) NOT NULL,
  `category_code` varchar(50) NOT NULL,
  `type_id` bigint UNSIGNED NOT NULL,
  `status_code` varchar(50) NOT NULL,
  `reason_code` varchar(50) DEFAULT NULL,
  `outcome_code` bigint UNSIGNED DEFAULT NULL,
  `priority_id` bigint UNSIGNED NOT NULL,
  `role_id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED NOT NULL,
  `notes` text,
  `created_by` bigint UNSIGNED NOT NULL,
  `updated_by` bigint UNSIGNED DEFAULT NULL,
  `date_of_incident` date DEFAULT NULL,
  `court_appearance_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `case_id` (`case_id`),
  KEY `fk_cases_category` (`category_code`),
  KEY `fk_cases_type` (`type_id`),
  KEY `fk_cases_status` (`status_code`),
  KEY `fk_cases_reason` (`reason_code`),
  KEY `fk_cases_outcome` (`outcome_code`),
  KEY `fk_cases_priority` (`priority_id`),
  KEY `fk_cases_role` (`role_id`),
  KEY `fk_cases_client` (`client_id`),
  KEY `fk_cases_created_by` (`created_by`),
  KEY `fk_cases_updated_by` (`updated_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `case_categories`
--

CREATE TABLE IF NOT EXISTS `case_categories` (
  `category_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `case_categories`
--

INSERT INTO `case_categories` (`category_code`, `category_description`, `created_at`, `updated_at`) VALUES
('CIV', 'Civil Litigation', '2026-01-16 10:44:09', '2026-01-16 10:44:09'),
('CRM', 'Criminal Defense', '2026-01-16 10:44:09', '2026-01-16 10:44:09'),
('COR', 'Corporate & Commercial', '2026-01-16 10:44:09', '2026-01-16 10:44:09'),
('FAM', 'Family Law', '2026-01-16 10:44:09', '2026-01-16 10:44:09'),
('IP', 'Intellectual Property', '2026-01-16 10:44:09', '2026-01-16 10:44:09'),
('EMP', 'Employment & Labor Law', '2026-01-16 10:44:09', '2026-01-16 10:44:09'),
('IMM', 'Immigration Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('TAX', 'Taxation Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('BNK', 'Bankruptcy & Insolvency', '2026-01-22 14:06:22', '2026-01-23 11:27:59'),
('ENV', 'Environmental Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('MED', 'Medical Malpractice', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('INS', 'Insurance Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('CON', 'Construction Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('ADM', 'Administrative Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('EST', 'Estate Planning & Probate', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('INT', 'International Law', '2026-01-22 14:06:22', '2026-01-22 14:06:22'),
('BOLO', 'DSDSSS', '2026-01-23 08:54:10', '2026-01-23 11:24:48');

-- --------------------------------------------------------

--
-- Table structure for table `case_lawyers`
--

CREATE TABLE IF NOT EXISTS `case_lawyers` (
  `case_lawyer_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_id` bigint UNSIGNED NOT NULL,
  `lawyer_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lawyer_role_id` int UNSIGNED NOT NULL,
  `tier_id` bigint UNSIGNED DEFAULT NULL,
  `tariff_id` bigint UNSIGNED DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`case_lawyer_id`),
  UNIQUE KEY `unique_case_lawyer` (`case_id`,`lawyer_id`),
  KEY `lawyer_id` (`lawyer_id`),
  KEY `tier_id` (`tier_id`),
  KEY `tariff_id` (`tariff_id`),
  KEY `fk_case_lawyer_role` (`lawyer_role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `case_outcomes`
--

CREATE TABLE IF NOT EXISTS `case_outcomes` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` text,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `case_outcomes`
--

INSERT INTO `case_outcomes` (`id`, `code`, `name`, `description`, `updated_at`, `created_at`) VALUES
(1, 'WON', 'Won', 'Case resolved in favor of the client', NULL, NULL),
(2, 'LOST', 'Lost', 'Case resolved against the client', NULL, NULL),
(3, 'SETTLED', 'Settled', 'Case resolved via settlement agreement', NULL, NULL),
(4, 'DISMISSED', 'Dismissed', 'Case dismissed by the court', '2026-01-19 06:05:49', NULL),
(5, 'WITHDRAWN', 'Withdrawn', 'Case withdrawn by the client or attorney', NULL, NULL),
(6, 'PENDING', 'Pending', 'Case still ongoing, no final outcome yet', NULL, NULL),
(7, 'APPEALED', 'Appealed', 'Case outcome is under appeal', NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `case_parties`
--

CREATE TABLE IF NOT EXISTS `case_parties` (
  `case_party_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED NOT NULL,
  `party_name` varchar(255) NOT NULL,
  `role_in_case` bigint UNSIGNED NOT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`case_party_id`),
  KEY `fk_case_parties_case` (`case_id`),
  KEY `fk_case_parties_client` (`client_id`),
  KEY `fk_case_parties_role` (`role_in_case`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `case_priorities`
--

CREATE TABLE IF NOT EXISTS `case_priorities` (
  `priority_id` int NOT NULL AUTO_INCREMENT,
  `priority_name` varchar(50) NOT NULL,
  `severity_rank` int NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`priority_id`),
  UNIQUE KEY `priority_name` (`priority_name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `case_priorities`
--

INSERT INTO `case_priorities` (`priority_id`, `priority_name`, `severity_rank`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Low', 1, 'No immediate action required', 1, '2026-01-26 15:29:42', '2026-01-26 15:29:42'),
(2, 'Normal', 2, 'Standard case priority', 1, '2026-01-26 15:29:42', '2026-01-26 15:29:42'),
(3, 'Medium', 3, 'Requires attention but not urgent', 1, '2026-01-26 15:29:42', '2026-01-26 15:29:42'),
(4, 'High', 4, 'Time-sensitive case', 1, '2026-01-26 15:29:42', '2026-01-26 15:29:42'),
(5, 'Urgent', 5, 'Immediate action required', 1, '2026-01-26 15:29:42', '2026-01-26 15:29:42'),
(6, 'Critical', 6, 'Court-mandated or high-risk matter', 1, '2026-01-26 15:29:42', '2026-01-26 15:29:42');

-- --------------------------------------------------------

--
-- Table structure for table `case_reasons`
--

CREATE TABLE IF NOT EXISTS `case_reasons` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `reason_code` varchar(50) NOT NULL,
  `reason_name` varchar(255) NOT NULL,
  `reason_description` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` bigint NOT NULL,
  `updated_by` bigint NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_case_reasons_created_by` (`created_by`),
  KEY `fk_case_reasons_updated_by` (`updated_by`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `case_reasons`
--

INSERT INTO `case_reasons` (`id`, `reason_code`, `reason_name`, `reason_description`, `created_at`, `updated_at`, `created_by`, `updated_by`, `status`) VALUES
(1, 'CANCEL_DUP', 'Duplicate Case', 'The case was identified as a duplicate of an existing case and was therefore cancelled.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 0),
(2, 'CANCEL_REQ', 'Cancelled by Request', 'The case was cancelled following a formal request by the reporting party.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 0),
(3, 'CANCEL_INV', 'Invalid Case', 'The case did not meet the minimum criteria required for processing.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1),
(4, 'CANCEL_ERR', 'Created in Error', 'The case was created due to a system or user error and has been cancelled.', '2026-01-15 09:22:28', '2026-01-19 11:44:25', 1, 1, 0),
(5, 'CANCEL_NOJ', 'No Jurisdiction', 'The case falls outside the organisation’s legal or operational jurisdiction.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1),
(6, 'CANCEL_NEV', 'Insufficient Evidence', 'The case was cancelled due to a lack of supporting evidence.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1),
(7, 'CANCEL_RES', 'Resolved Externally', 'The matter was resolved outside the system prior to case processing.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1),
(8, 'CANCEL_NON', 'Non-Responsive Party', 'The involved party failed to respond within the required timeframe.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1),
(9, 'CANCEL_WDR', 'Withdrawn by Initiator', 'The case was formally withdrawn by the individual who initiated it.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1),
(10, 'CANCEL_POL', 'Policy Violation', 'The case conflicted with organisational policy and could not proceed.', '2026-01-15 09:22:28', '2026-01-15 09:22:28', 1, 1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `case_roles`
--

CREATE TABLE IF NOT EXISTS `case_roles` (
  `role_id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `case_roles`
--

INSERT INTO `case_roles` (`role_id`, `role_name`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Plaintiff', 'Party initiating the case', 1, '2026-01-26 15:30:52', '2026-01-26 15:30:52'),
(2, 'Defendant', 'Party responding to the case', 1, '2026-01-26 15:30:52', '2026-01-26 15:30:52'),
(3, 'Applicant', 'Party applying for legal relief', 1, '2026-01-26 15:30:52', '2026-01-26 15:30:52'),
(4, 'Respondent', 'Party responding to an application', 1, '2026-01-26 15:30:52', '2026-01-26 15:30:52'),
(5, 'Witnesssssss', 'Individual providing testimony', 1, '2026-01-26 15:30:52', '2026-01-27 12:20:40'),
(6, 'Third Party', 'External party involved in the case', 1, '2026-01-26 15:30:52', '2026-01-26 15:30:52');

-- --------------------------------------------------------

--
-- Table structure for table `case_statuses`
--

CREATE TABLE IF NOT EXISTS `case_statuses` (
  `status_code` varchar(10) NOT NULL,
  `status_name` varchar(255) NOT NULL,
  `status_description` text,
  `color_code` char(7) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`status_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `case_statuses`
--

INSERT INTO `case_statuses` (`status_code`, `status_name`, `status_description`, `color_code`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
('OPEN', 'Open', 'Case is active and currently being handled', '#28A745', 0, 1, '2026-01-16 14:07:34', '2026-01-16 14:07:34'),
('PEND', 'Pending', 'Case is temporarily inactive or awaiting action', '#FFC107', 0, 2, '2026-01-16 14:07:34', '2026-01-16 14:07:34'),
('CLOS', 'Closed', 'Case has been concluded and formally closeddd', '#6C757D', 0, 3, '2026-01-16 14:07:34', '2026-01-22 06:16:40'),
('WDTH', 'Withdrawn', 'Case was withdrawn by the client or counsel', '#DC3545', 0, 4, '2026-01-16 14:07:34', '2026-01-16 14:07:34'),
('TRAN', 'Transferred', 'Case has been transferred to another firm or court', '#17A2B8', 0, 5, '2026-01-16 14:07:34', '2026-01-16 14:07:34'),
('INTVN', 'Intervened', 'government intervention', '#6b7280', 1, 0, '2026-01-17 14:23:12', '2026-01-17 14:23:12');

-- --------------------------------------------------------

--
-- Table structure for table `case_types`
--

CREATE TABLE IF NOT EXISTS `case_types` (
  `type_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `type_name` varchar(200) NOT NULL,
  `type_description` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`type_id`),
  UNIQUE KEY `uq_case_types_type_name` (`type_name`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `case_types`
--

INSERT INTO `case_types` (`type_id`, `type_name`, `type_description`, `created_at`, `updated_at`) VALUES
(1, 'robbery', 'Illegal possession or taking of property using force or intimidations.', '2026-01-22 11:31:43', '2026-01-22 13:59:13'),
(2, 'assault', 'Physical attack causing bodily harm or threat thereof', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(3, 'fraud', 'Deception intended to result in financial or personal gain', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(4, 'burglary', 'Unlawful entry into a structure with intent to commit a crime', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(5, 'theft', 'Unlawful taking of another person’s property without force', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(6, 'homicide', 'Unlawful killing of another human being', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(7, 'kidnapping', 'Unlawful abduction or detention of a person', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(8, 'domestic_violence', 'Abuse or violence occurring within a domestic relationship', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(9, 'drug_trafficking', 'Illegal manufacture, distribution, or sale of controlled substances', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(10, 'money_laundering', 'Concealment of the origins of illegally obtained money', '2026-01-22 11:31:43', '2026-01-22 11:31:43'),
(11, 'cybercrime', 'Criminal activities carried out using computers or digital networksss', '2026-01-22 11:31:43', '2026-01-22 13:53:16');

-- --------------------------------------------------------

--
-- Table structure for table `cashbooks`
--

CREATE TABLE IF NOT EXISTS `cashbooks` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `cashbook_type` enum('CASH','BANK') NOT NULL,
  `is_trust` tinyint(1) NOT NULL DEFAULT '0',
  `currency_id` bigint UNSIGNED NOT NULL,
  `opening_balance` decimal(18,2) NOT NULL DEFAULT '0.00',
  `opening_balance_date` date DEFAULT NULL,
  `bank_name` varchar(255) DEFAULT NULL,
  `branch_name` varchar(255) DEFAULT NULL,
  `account_number` varchar(100) DEFAULT NULL,
  `swift_code` varchar(50) DEFAULT NULL,
  `is_reconcilable` tinyint(1) NOT NULL DEFAULT '1',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_cashbooks_currency` (`currency_id`),
  KEY `idx_cashbooks_type` (`cashbook_type`),
  KEY `idx_cashbooks_trust` (`is_trust`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `cashbooks`
--

INSERT INTO `cashbooks` (`id`, `code`, `name`, `cashbook_type`, `is_trust`, `currency_id`, `opening_balance`, `opening_balance_date`, `bank_name`, `branch_name`, `account_number`, `swift_code`, `is_reconcilable`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'cshA9FQX001', 'Office Bank Account', 'BANK', 0, 1, 0.00, '2025-01-01', 'CBZ Bank', 'Samora Machel Branch', '0102030405', 'COBZZWHX', 1, 1, '2026-01-07 13:11:49', '2026-01-07 13:11:49'),
(2, 'cshB7KLM002', 'Client Trust Bank Account', 'BANK', 1, 1, 0.00, '2025-01-01', 'Stanbic Bank', 'Borrowdale Branch', '0203040506', 'SBICZWHX', 1, 1, '2026-01-07 13:11:49', '2026-01-07 13:11:49'),
(3, 'cshC3PTX003', 'Petty Cash', 'CASH', 0, 1, 500.00, '2025-01-01', NULL, NULL, NULL, NULL, 0, 1, '2026-01-07 13:11:49', '2026-01-07 13:11:49'),
(4, 'cshD8RZQ004', 'Office Bank Account – USD', 'BANK', 0, 2, 0.00, '2025-01-01', 'FBC Bank', 'Avondale Branch', '0304050607', 'FBCPZWHX', 1, 1, '2026-01-07 13:11:49', '2026-01-07 13:11:49');

-- --------------------------------------------------------

--
-- Table structure for table `cashbook_entries`
--

CREATE TABLE IF NOT EXISTS `cashbook_entries` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `cashbook_id` bigint UNSIGNED NOT NULL,
  `entry_date` date NOT NULL,
  `module` enum('AR','AP','GL','CB') COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `reference_det` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `tr_code` enum('DR','CR') COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cashbook_entries_cashbook_id` (`cashbook_id`),
  KEY `idx_cashbook_entries_entry_date` (`entry_date`),
  KEY `idx_cashbook_entries_module` (`module`),
  KEY `idx_cashbook_entries_account_id` (`account_id`),
  KEY `idx_cashbook_entries_tr_code` (`tr_code`),
  KEY `idx_cashbook_entries_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `chat_groups`
--

CREATE TABLE IF NOT EXISTS `chat_groups` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_name` (`group_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `chat_messages`
--

CREATE TABLE IF NOT EXISTS `chat_messages` (
  `message_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id` bigint UNSIGNED NOT NULL,
  `sender_id` bigint UNSIGNED NOT NULL,
  `message_type` enum('text','file','audio','video','call','notification') COLLATE utf8mb4_unicode_ci DEFAULT 'text',
  `message` text COLLATE utf8mb4_unicode_ci,
  `file_id` bigint UNSIGNED DEFAULT NULL,
  `call_type` enum('audio','video') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `call_status` enum('missed','answered','declined') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `is_deleted` tinyint(1) DEFAULT '0',
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `read_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`message_id`),
  KEY `chat_id` (`chat_id`),
  KEY `sender_id` (`sender_id`),
  KEY `file_id` (`file_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `chat_participants`
--

CREATE TABLE IF NOT EXISTS `chat_participants` (
  `participant_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `role` enum('member','admin','owner') COLLATE utf8mb4_unicode_ci DEFAULT 'member',
  `joined_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen` timestamp NULL DEFAULT NULL,
  `is_muted` tinyint(1) DEFAULT '0',
  `is_blocked` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`participant_id`),
  KEY `chat_id` (`chat_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `chat_requests`
--

CREATE TABLE IF NOT EXISTS `chat_requests` (
  `request_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `sender_id` bigint UNSIGNED NOT NULL,
  `receiver_id` bigint UNSIGNED NOT NULL,
  `status` enum('pending','accepted','rejected') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `responded_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`request_id`),
  KEY `sender_id` (`sender_id`),
  KEY `receiver_id` (`receiver_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `chat_rooms`
--

CREATE TABLE IF NOT EXISTS `chat_rooms` (
  `chat_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_type` enum('private','group') COLLATE utf8mb4_unicode_ci DEFAULT 'private',
  `group_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `group_description` text COLLATE utf8mb4_unicode_ci,
  `group_avatar_url` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_message_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`chat_id`),
  KEY `created_by` (`created_by`),
  KEY `last_message_id` (`last_message_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `clients`
--

CREATE TABLE IF NOT EXISTS `clients` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `national_id` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_primary` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `street_address` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state_province` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employer_name` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `position_role` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_onboarded` date DEFAULT NULL,
  `assigned_lawyer` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referred_lawyer` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_id` (`client_id`),
  UNIQUE KEY `national_id` (`national_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `clients`
--

INSERT INTO `clients` (`id`, `client_id`, `first_name`, `last_name`, `national_id`, `date_of_birth`, `gender`, `phone_primary`, `email`, `street_address`, `city`, `state_province`, `country`, `employer_name`, `position_role`, `client_type`, `client_category`, `date_onboarded`, `assigned_lawyer`, `referred_lawyer`, `notes`, `created_at`, `updated_at`) VALUES
(1, 'CL-0001', 'James', 'Whitaker', 'ZW123456A', '1985-03-14', 'Male', '+263771234567', 'james.whitaker@example.com', '12 Samora Machel Ave', 'Harare', 'Harare Province', 'Zimbabwe', 'Whitaker Holdings', 'Managing Director', NULL, NULL, '2024-01-10', 'L940147', 'L200861', 'Corporate compliance advisory', '2026-01-06 10:29:08', '2026-01-06 12:44:40'),
(2, 'CL-0002', 'Emily', 'Carter', 'ZW654321B', '1990-07-22', 'Female', '+263772345678', 'emily.carter@example.com', '45 Borrowdale Road', 'Harare', 'Harare Province', 'Zimbabwe', 'Carter & Co', 'Legal Consultant', 'Individual', 'Professional', '2024-01-15', 'Adv. Ncube', NULL, 'Contract review services', '2026-01-06 10:29:08', '2026-01-06 10:29:08'),
(3, 'CL-0003', 'Michael', 'Dube', 'ZW789456C', '1978-11-05', 'Male', '+263773456789', 'michael.dube@example.com', '89 Main Street', 'Bulawayo', 'Bulawayo Province', 'Zimbabwe', 'Dube Logistics', 'Operations Manager', 'Individual', 'Commercial', '2024-02-01', 'Adv. Sibanda', 'Adv. Moyo', 'Ongoing litigation support', '2026-01-06 10:29:08', '2026-01-06 10:29:08'),
(5, 'CL-0005', 'Daniel', 'Mukasa', 'ZW456789E', '1983-09-30', 'Male', '+263775678901', 'daniel.mukasa@example.com', '101 Airport Road', 'Gweru', 'Midlands Province', 'Zimbabwe', 'Mukasa Engineering', 'Senior Engineer', NULL, NULL, '2024-02-20', 'L200861', 'L940147', 'Regulatory and safety compliance', '2026-01-06 10:29:08', '2026-01-06 12:43:58');

-- --------------------------------------------------------

--
-- Table structure for table `client_categories`
--

CREATE TABLE IF NOT EXISTS `client_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `category_code` varchar(20) NOT NULL,
  `category_description` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_code` (`category_code`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `client_categories`
--

INSERT INTO `client_categories` (`category_id`, `category_code`, `category_description`, `created_at`, `updated_at`) VALUES
(1, 'INDIV', 'Individual private clients seeking personal legal services', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(2, 'HNWI', 'High-net-worth individuals requiring complex legal and tax advisory services', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(3, 'SME', 'Small and medium-sized enterprises including startups and owner-managed businesses', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(4, 'CORP', 'Large corporations and multinational companies', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(5, 'FIN', 'Financial institutions such as banks, insurers, and investment firms', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(6, 'GOV', 'Government bodies, public authorities, and state-owned entities', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(7, 'NONP', 'Non-profit organizations, charities, and foundations', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(8, 'PROF', 'Professional services firms including accountants, consultants, and medical practices', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(9, 'REF', 'Referral-based clients introduced by third parties or panel arrangements', '2026-01-06 14:54:30', '2026-01-06 14:54:30'),
(10, 'PROB', 'Pro bono and strategic clients taken on for public interest or reputational purposes', '2026-01-06 14:54:30', '2026-01-06 14:54:30');

-- --------------------------------------------------------

--
-- Table structure for table `client_invoices`
--

CREATE TABLE IF NOT EXISTS `client_invoices` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` bigint UNSIGNED NOT NULL,
  `invoice_number` varchar(200) NOT NULL,
  `invoice_date` date NOT NULL,
  `case_reference` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `responsible_lawyer` varchar(200) NOT NULL,
  `narration` text NOT NULL,
  `gl_account` varchar(200) NOT NULL,
  `tax_id` bigint UNSIGNED NOT NULL,
  `discount` decimal(5,2) DEFAULT '0.00',
  `price` decimal(15,2) NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `disbursements` decimal(15,2) DEFAULT '0.00',
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `total` decimal(15,2) DEFAULT '0.00',
  `status` enum('draft','sent','paid','overdue') DEFAULT 'draft',
  `user_id` bigint UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `tax_id` (`tax_id`),
  KEY `currency_id` (`currency_id`),
  KEY `user_id` (`user_id`),
  KEY `idx_client_status` (`client_id`,`status`),
  KEY `idx_invoice_date` (`invoice_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_journals`
--

CREATE TABLE IF NOT EXISTS `client_journals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `client_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `narration` text COLLATE utf8mb4_unicode_ci,
  `dr` decimal(15,2) DEFAULT '0.00',
  `cr` decimal(15,2) DEFAULT '0.00',
  `status` enum('pending','approved','rejected','reversed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `client_id` int DEFAULT NULL,
  `transaction_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_code` (`transaction_code`),
  KEY `idx_date` (`date`),
  KEY `idx_client_code` (`client_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_quotations`
--

CREATE TABLE IF NOT EXISTS `client_quotations` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` bigint UNSIGNED NOT NULL,
  `quotation_number` varchar(200) NOT NULL,
  `quotation_date` date NOT NULL,
  `terms` text,
  `validity_period` int DEFAULT '30',
  `notes` text,
  `status` enum('draft','sent','accepted','rejected') DEFAULT 'draft',
  `total_amount` decimal(15,2) DEFAULT '0.00',
  `user_id` bigint UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotation_number` (`quotation_number`),
  KEY `user_id` (`user_id`),
  KEY `idx_client_status` (`client_id`,`status`),
  KEY `idx_quotation_date` (`quotation_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_quotation_lines`
--

CREATE TABLE IF NOT EXISTS `client_quotation_lines` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint UNSIGNED NOT NULL,
  `description` text NOT NULL,
  `quantity` decimal(10,2) DEFAULT '1.00',
  `unit_price` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_quotation_id` (`quotation_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_receipts`
--

CREATE TABLE IF NOT EXISTS `client_receipts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` bigint UNSIGNED NOT NULL,
  `receipt_number` varchar(200) NOT NULL,
  `receipt_date` date NOT NULL,
  `payment_method` varchar(200) NOT NULL,
  `status` enum('draft','processed') DEFAULT 'draft',
  `total_amount` decimal(15,2) DEFAULT '0.00',
  `user_id` bigint UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `user_id` (`user_id`),
  KEY `idx_client_status` (`client_id`,`status`),
  KEY `idx_receipt_date` (`receipt_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_receipt_lines`
--

CREATE TABLE IF NOT EXISTS `client_receipt_lines` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `receipt_id` bigint UNSIGNED NOT NULL,
  `description` text NOT NULL,
  `invoice_id` bigint UNSIGNED DEFAULT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `account_id` (`account_id`),
  KEY `idx_receipt_id` (`receipt_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_info`
--

CREATE TABLE IF NOT EXISTS `company_info` (
  `company_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_name` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `contact_email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_phone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `website` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tax_id` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` text COLLATE utf8mb4_unicode_ci,
  `bank_acc_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_by` bigint UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`company_id`),
  KEY `updated_by` (`updated_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `court_areas`
--

CREATE TABLE IF NOT EXISTS `court_areas` (
  `area_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `area_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'Zimbabwe',
  `specific_location` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`area_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `court_areas`
--

INSERT INTO `court_areas` (`area_code`, `area_name`, `city`, `country`, `specific_location`, `created_at`, `updated_at`) VALUES
('CA-HRE', 'Harare Magistrates Court', 'Harare', 'Zimbabwe', 'Corner Samora Machel Ave & Rotten Row', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-HHC', 'Harare High Court', 'Harare', 'Zimbabwe', 'Samora Machel Avenue', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-BYO', 'Bulawayo Magistrates Court', 'Bulawayo', 'Zimbabwe', 'Fife Street', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-BHC', 'Bulawayo High Court', 'Bulawayo', 'Zimbabwe', 'Corner Fort Street & 10th Avenue', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-GWE', 'Gweru Magistrates Court', 'Gweru', 'Zimbabwe', 'Robert Mugabe Way', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-MUT', 'Mutare Magistrates Court', 'Mutare', 'Zimbabwe', 'Aerodrome Road', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-MAS', 'Masvingo Magistrates Court', 'Masvingo', 'Zimbabwe', 'Hughes Street', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-CHI', 'Chinhoyi Magistrates Court', 'Chinhoyi', 'Zimbabwe', 'Magistrates Court Complex', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-MAR', 'Marondera Magistrates Court', 'Marondera', 'Zimbabwe', 'Fifth Street', '2026-01-28 15:28:20', '2026-01-28 15:28:20'),
('CA-VIC', 'Victoria Falls Magistrates Court', 'Victoria Falls', 'Zimbabwe', 'Parkway Drive', '2026-01-28 15:28:20', '2026-01-28 15:28:20');

-- --------------------------------------------------------

--
-- Table structure for table `court_filings`
--

CREATE TABLE IF NOT EXISTS `court_filings` (
  `filing_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `case_id` bigint UNSIGNED NOT NULL,
  `filing_type_id` bigint UNSIGNED DEFAULT NULL,
  `filing_status_id` bigint UNSIGNED DEFAULT NULL,
  `filing_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `filing_title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `filing_description` text COLLATE utf8mb4_unicode_ci,
  `court_area_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `judge_id` bigint UNSIGNED DEFAULT NULL,
  `filing_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `court_acknowledgment` date DEFAULT NULL,
  `document_path` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '1.0',
  `next_action` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `internal_notes` text COLLATE utf8mb4_unicode_ci,
  `external_notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `updated_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`filing_id`),
  UNIQUE KEY `filing_code` (`filing_code`),
  KEY `case_id` (`case_id`),
  KEY `filing_type_id` (`filing_type_id`),
  KEY `filing_status_id` (`filing_status_id`),
  KEY `court_area_id` (`court_area_id`),
  KEY `judge_id` (`judge_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `court_filing_statuses`
--

CREATE TABLE IF NOT EXISTS `court_filing_statuses` (
  `filing_status_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `status_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `display_order` int DEFAULT '0',
  `is_final` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`filing_status_id`),
  UNIQUE KEY `status_code` (`status_code`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `court_filing_statuses`
--

INSERT INTO `court_filing_statuses` (`filing_status_id`, `status_code`, `status_name`, `description`, `display_order`, `is_final`, `created_at`, `updated_at`) VALUES
(1, 'DRAFT', 'Draft', 'Filing has been drafted but not yet submitted to the court.', 1, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(2, 'SUBMITTED', 'Submitted', 'Filing has been submitted to the court and is awaiting acceptance.', 2, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(3, 'ACCEPTED', 'Accepted', 'Court has accepted the filing for processing.', 3, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(4, 'PENDING_REVIEW', 'Pending Review', 'Filing is under review by the court clerk or judge.', 4, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(5, 'REJECTED', 'Rejected', 'Filing has been rejected by the court; corrections may be required.', 5, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(6, 'RESUBMITTED', 'Resubmitted', 'Filing has been corrected and resubmitted to the court.', 6, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(7, 'UNDER_HEARING', 'Under Hearing', 'Filing is being considered in a scheduled court hearing.', 7, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(8, 'APPROVED', 'Approved', 'Filing has been approved by the court or relevant authority.', 8, 1, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(9, 'DENIED', 'Denied', 'Court has denied the filing; no further action permitted.', 9, 1, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(10, 'COMPLETED', 'Completed', 'Filing process is fully completed and closed.', 10, 1, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(11, 'WITHDRAWN', 'Withdrawn', 'Filing has been withdrawn by the submitting party.', 11, 1, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(12, 'ON_HOLD', 'On Hold', 'Filing is temporarily on hold pending additional information.', 12, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(13, 'ESCALATED', 'Escalated', 'Filing has been escalated for urgent attention or higher authority.', 13, 0, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(14, 'CANCELLED', 'Cancelled', 'Filing has been cancelled and will not proceed further.', 14, 1, '2026-01-14 09:34:18', '2026-01-14 09:34:18'),
(15, 'ARCHIVED', 'Archived', 'Filing has been archived for record-keeping purposes.', 15, 1, '2026-01-14 09:34:18', '2026-01-14 09:34:18');

-- --------------------------------------------------------

--
-- Table structure for table `court_filing_types`
--

CREATE TABLE IF NOT EXISTS `court_filing_types` (
  `filing_type_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `filing_type_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`filing_type_id`),
  UNIQUE KEY `filing_type_name` (`filing_type_name`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `court_filing_types`
--

INSERT INTO `court_filing_types` (`filing_type_id`, `filing_type_name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Summons', 'Formal initiating process in a civil action calling upon the defendant to respond.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(2, 'Declaration / Particulars of Claim', 'Document setting out the factual and legal basis of the plaintiff’s claim.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(3, 'Notice of Appearance to Defend', 'Notice filed by a defendant indicating intention to oppose the claim.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(4, 'Plea', 'Defendant’s substantive response to the plaintiff’s claim.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(5, 'Replication', 'Plaintiff’s reply to new matters raised in the plea.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(6, 'Application', 'Proceedings initiated by affidavit seeking specific relief from the court.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(7, 'Founding Affidavit', 'Primary affidavit supporting an application and setting out material facts.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(8, 'Opposing Affidavit', 'Affidavit filed in opposition to an application.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(9, 'Notice of Motion', 'Document specifying the relief sought and the basis for an application.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(10, 'Heads of Argument', 'Written legal submissions outlining arguments and authorities.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(11, 'Answering Affidavit', 'Affidavit responding to issues raised in an opposing affidavit.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(12, 'Confirmatory Affidavit', 'Affidavit confirming the correctness of statements made by another deponent.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(13, 'Notice of Set Down', 'Notice advising parties of the hearing date and time.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(14, 'Pre-Trial Conference Minute', 'Record of issues agreed and narrowed at a pre-trial conference.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(15, 'Request for Further Particulars', 'Request seeking clarification or additional details of a pleading.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(16, 'Response to Request for Further Particulars', 'Reply providing the requested clarification or details.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(17, 'Subpoena', 'Court-issued document compelling attendance or production of evidence.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(18, 'Notice of Withdrawal', 'Notice indicating withdrawal of a matter or legal practitioner.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(19, 'Consent Paper / Draft Order', 'Document recording agreed terms for endorsement by the court.', '2026-01-14 09:27:05', '2026-01-14 09:27:05'),
(20, 'Bill of Costs', 'Statement detailing legal costs claimed by a party.', '2026-01-14 09:27:05', '2026-01-14 09:27:05');

-- --------------------------------------------------------

--
-- Table structure for table `court_types`
--

CREATE TABLE IF NOT EXISTS `court_types` (
  `court_type_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `type_name` varchar(100) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`court_type_id`),
  UNIQUE KEY `type_name` (`type_name`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `court_types`
--

INSERT INTO `court_types` (`court_type_id`, `type_name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Magistrates Court', 'Handles minor criminal and civil matters', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(2, 'High Court', 'Handles serious criminal cases and major civil disputes', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(3, 'Supreme Court', 'Final court of appeal in Zimbabwe', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(4, 'Constitutional Court', 'Interprets and enforces the Constitution', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(5, 'Labour Court', 'Handles labour and employment disputes', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(6, 'Administrative Court', 'Deals with administrative and electoral matters', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(7, 'Family Court', 'Handles family law matters including divorce and custody', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(8, 'Juvenile Court', 'Handles cases involving minors', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(9, 'Commercial Court', 'Handles commercial and business disputes', '2026-01-28 15:31:22', '2026-01-28 15:31:22'),
(10, 'Small Claims Court', 'Handles low-value civil claims', '2026-01-28 15:31:22', '2026-01-28 15:31:22');

-- --------------------------------------------------------

--
-- Table structure for table `currencies`
--

CREATE TABLE IF NOT EXISTS `currencies` (
  `currency_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `currency_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `symbol` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`currency_id`),
  UNIQUE KEY `currency_code` (`currency_code`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `currencies`
--

INSERT INTO `currencies` (`currency_id`, `currency_code`, `currency_name`, `symbol`, `country`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'CUR001', 'United States Dollar', 'USD', 'United States of America', 1, '2026-01-05 07:03:06', '2026-01-05 07:03:06'),
(2, 'CUR002', 'Zimbabwe Gold', 'ZWG', 'Zimbabwe', 1, '2026-01-05 07:03:39', '2026-01-05 07:03:39'),
(3, 'CUR003', 'South African Rand', 'ZAR', 'South Africa', 1, '2026-01-05 07:04:19', '2026-01-05 07:04:19');

-- --------------------------------------------------------

--
-- Table structure for table `currency_rates`
--

CREATE TABLE IF NOT EXISTS `currency_rates` (
  `rate_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `base_currency_id` bigint UNSIGNED NOT NULL,
  `target_currency_id` bigint UNSIGNED NOT NULL,
  `rate_date` date DEFAULT NULL,
  `exchange_rate` decimal(18,6) NOT NULL,
  `source` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rate_id`),
  UNIQUE KEY `unique_currency_rate` (`base_currency_id`,`target_currency_id`,`rate_date`),
  KEY `target_currency_id` (`target_currency_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `currency_rates`
--

INSERT INTO `currency_rates` (`rate_id`, `base_currency_id`, `target_currency_id`, `rate_date`, `exchange_rate`, `source`, `created_at`, `updated_at`) VALUES
(1, 2, 1, '2026-01-05', 30.000000, 'RBZ', '2026-01-05 07:04:50', '2026-01-05 07:04:50');

-- --------------------------------------------------------

--
-- Table structure for table `departments`
--

CREATE TABLE IF NOT EXISTS `departments` (
  `department_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `department_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `department_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `department_code` (`department_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `diary_entries`
--

CREATE TABLE IF NOT EXISTS `diary_entries` (
  `diary_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `assignment_id` bigint UNSIGNED DEFAULT NULL,
  `date_time` datetime NOT NULL,
  `task_title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_case` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_type` enum('meeting','court-filing','draft-review','call','follow-up','internal-task','other') COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` enum('low','medium','high') COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','in-progress','completed','deferred') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `location` enum('office','courtroom','online-meeting','phone-call','other') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `duration` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`diary_id`),
  KEY `assignment_id` (`assignment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `features`
--

CREATE TABLE IF NOT EXISTS `features` (
  `feature_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `submodule_id` bigint UNSIGNED NOT NULL,
  `feature_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`feature_id`),
  KEY `submodule_id` (`submodule_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `filing_responsible_lawyers`
--

CREATE TABLE IF NOT EXISTS `filing_responsible_lawyers` (
  `filing_lawyer_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `filing_id` bigint UNSIGNED NOT NULL,
  `lawyer_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role_in_filing` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `assigned_date` date DEFAULT NULL,
  PRIMARY KEY (`filing_lawyer_id`),
  UNIQUE KEY `unique_filing_lawyer` (`filing_id`,`lawyer_id`),
  KEY `lawyer_id` (`lawyer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `general_journals`
--

CREATE TABLE IF NOT EXISTS `general_journals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` date NOT NULL,
  `narration` text COLLATE utf8mb4_unicode_ci,
  `transaction_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'USD',
  `amount` decimal(15,2) NOT NULL,
  `status` enum('pending','approved','rejected','reversed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_code` (`transaction_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `general_ledger_accounts`
--

CREATE TABLE IF NOT EXISTS `general_ledger_accounts` (
  `account_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `financial_category` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_type_id` bigint UNSIGNED NOT NULL,
  `department_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `has_subaccounts` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `code` (`code`),
  KEY `general_ledger_accounts_parent_id_foreign` (`parent_id`),
  KEY `account_type_id` (`account_type_id`),
  KEY `department_code` (`department_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `group_calls`
--

CREATE TABLE IF NOT EXISTS `group_calls` (
  `call_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id` bigint UNSIGNED NOT NULL,
  `initiated_by` bigint UNSIGNED NOT NULL,
  `call_type` enum('audio','video') COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('ongoing','ended','missed') COLLATE utf8mb4_unicode_ci DEFAULT 'ongoing',
  `started_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `ended_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`call_id`),
  KEY `chat_id` (`chat_id`),
  KEY `initiated_by` (`initiated_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `group_call_participants`
--

CREATE TABLE IF NOT EXISTS `group_call_participants` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `call_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `join_time` timestamp NULL DEFAULT NULL,
  `leave_time` timestamp NULL DEFAULT NULL,
  `call_status` enum('joined','missed','left') COLLATE utf8mb4_unicode_ci DEFAULT 'missed',
  PRIMARY KEY (`id`),
  KEY `call_id` (`call_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `home_currency`
--

CREATE TABLE IF NOT EXISTS `home_currency` (
  `home_currency_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `currency_id` bigint UNSIGNED NOT NULL,
  `effective_date` date DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`home_currency_id`),
  KEY `currency_id` (`currency_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `home_currency`
--

INSERT INTO `home_currency` (`home_currency_id`, `currency_id`, `effective_date`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 1, '2026-01-27', 1, '2026-01-05 07:04:25', '2026-01-27 11:59:03');

-- --------------------------------------------------------

--
-- Table structure for table `home_currency_history`
--

CREATE TABLE IF NOT EXISTS `home_currency_history` (
  `history_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `currency_id` bigint UNSIGNED NOT NULL,
  `effective_date` date DEFAULT NULL,
  `deactivated_date` date DEFAULT NULL,
  `changed_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`history_id`),
  KEY `currency_id` (`currency_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `home_currency_history`
--

INSERT INTO `home_currency_history` (`history_id`, `currency_id`, `effective_date`, `deactivated_date`, `changed_by`, `created_at`) VALUES
(1, 2, '2026-01-05', '2026-01-27', 1, '2026-01-27 13:59:03');

-- --------------------------------------------------------

--
-- Table structure for table `journal_transactions`
--

CREATE TABLE IF NOT EXISTS `journal_transactions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transaction_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `journal_type` enum('general','client','supplier') COLLATE utf8mb4_unicode_ci NOT NULL,
  `journal_id` int NOT NULL,
  `date` date NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` enum('pending','approved','rejected','reversed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_code` (`transaction_code`),
  KEY `idx_journal_type` (`journal_type`,`journal_id`),
  KEY `idx_transaction_code` (`transaction_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `judgements`
--

CREATE TABLE IF NOT EXISTS `judgements` (
  `judgement_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `judgement_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`judgement_id`),
  UNIQUE KEY `judgement_code` (`judgement_code`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `judgements`
--

INSERT INTO `judgements` (`judgement_id`, `judgement_code`, `title`, `description`, `created_at`, `updated_at`) VALUES
(1, 'JDG-FIN', 'Final Judgment', 'Conclusive determination of the case on its merits', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(2, 'JDG-INT', 'Interlocutory Judgment', 'Decision on an interim matter before final resolution', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(3, 'JDG-DEF', 'Default Judgment', 'Judgment entered due to failure of a party to appear or respond', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(4, 'JDG-SUM', 'Summary Judgment', 'Judgment given where no triable issues of fact exist', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(5, 'JDG-CON', 'Consent Judgment', 'Judgment entered with agreement of all parties', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(6, 'JDG-DEC', 'Declaratory Judgment', 'Judgment declaring rights without awarding damages', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(7, 'JDG-MAN', 'Mandatory Order', 'Judgment compelling a party to perform a specific act', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(8, 'JDG-PRO', 'Prohibitory Order', 'Judgment restraining a party from certain actions', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(9, 'JDG-COS', 'Costs Order', 'Judgment determining liability for legal costs', '2026-01-28 15:36:03', '2026-01-28 15:36:03'),
(10, 'JDG-DIS', 'Dismissal Judgment', 'Judgment dismissing a case or application', '2026-01-28 15:36:03', '2026-01-28 15:36:03');

-- --------------------------------------------------------

--
-- Table structure for table `judges`
--

CREATE TABLE IF NOT EXISTS `judges` (
  `judge_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `judge_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `full_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `court_area_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `court_type_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`judge_id`),
  UNIQUE KEY `judge_code` (`judge_code`),
  KEY `court_area_code` (`court_area_code`),
  KEY `court_type_name` (`court_type_name`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `judges`
--

INSERT INTO `judges` (`judge_id`, `judge_code`, `full_name`, `court_area_code`, `court_type_name`, `contact`, `email`, `status`, `created_at`, `updated_at`, `notes`, `is_active`) VALUES
(1, 'JDG-001', 'Justice Tendai Moyo', 'CA-HHC', 'High Court', '+263771234001', 'tendai.moyo@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Senior High Court Judge', 1),
(2, 'JDG-002', 'Justice Nomsa Dube', 'CA-BHC', 'High Court', '+263771234002', 'nomsa.dube@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Experienced in criminal law', 1),
(3, 'JDG-003', 'Justice Farai Chikore', 'CA-HRE', 'Magistrates Court', '+263771234003', 'farai.chikore@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Handles civil matters', 1),
(4, 'JDG-004', 'Justice Tapiwa Nyathi', 'CA-GWE', 'Magistrates Court', '+263771234004', 'tapiwa.nyathi@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Midlands Province', 1),
(5, 'JDG-005', 'Justice Rudo Mapfumo', 'CA-MUT', 'Magistrates Court', '+263771234005', 'rudo.mapfumo@judiciary.co.zw', 'On Leave', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Currently on study leave', 1),
(6, 'JDG-006', 'Justice Blessing Hove', 'CA-BYO', 'Magistrates Court', '+263771234006', 'blessing.hove@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Commercial disputes focus', 1),
(7, 'JDG-007', 'Justice Wellington Ncube', 'CA-MAS', 'Magistrates Court', '+263771234007', 'wellington.ncube@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Southern circuit', 1),
(8, 'JDG-008', 'Justice Chipo Katsande', 'CA-CHI', 'Magistrates Court', '+263771234008', 'chipo.katsande@judiciary.co.zw', 'Suspended', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Under administrative review', 0),
(9, 'JDG-009', 'Justice Tawanda Muzenda', 'CA-MAR', 'Magistrates Court', '+263771234009', 'tawanda.muzenda@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Land dispute specialist', 1),
(10, 'JDG-010', 'Justice Nkosana Sibanda', 'CA-VIC', 'Magistrates Court', '+263771234010', 'nkosana.sibanda@judiciary.co.zw', 'Active', '2026-01-28 15:37:50', '2026-01-28 15:37:50', 'Matabeleland North circuit', 1);

-- --------------------------------------------------------

--
-- Table structure for table `lawyers`
--

CREATE TABLE IF NOT EXISTS `lawyers` (
  `lawyer_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone_primary` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone_secondary` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lawyer_group` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `certificate_number` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_admission` date DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `lawyer_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lawyer_tier` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tier_id` int DEFAULT NULL,
  PRIMARY KEY (`lawyer_id`),
  UNIQUE KEY `email` (`email`),
  KEY `fk_lawyer_tier` (`tier_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lawyers`
--

INSERT INTO `lawyers` (`lawyer_id`, `first_name`, `last_name`, `email`, `phone_primary`, `phone_secondary`, `lawyer_group`, `certificate_number`, `date_of_admission`, `status`, `lawyer_type`, `lawyer_tier`, `notes`, `created_at`, `updated_at`, `tier_id`) VALUES
('1', 'Alice', 'Moyo', 'alice.moyo@wayne.enterprises.com', '+263772000001', '+263772000101', 'Civil Law', 'CERT001', '2018-05-15', 'ACTIVE', 'Contract Lawyer', 'Junior Associate', 'Specializes in commercial contracts', '2026-01-05 15:06:25', '2026-01-05 15:06:25', 1),
('2', 'Brian', 'Chikore', 'brian.chikore@wayne.enterprises.com', '+263772000002', '+263772000102', 'Family Law', 'CERT002', '2016-08-20', 'ACTIVE', 'Divorce Lawyer', 'Associate', 'Handles divorce and custody cases', '2026-01-05 15:06:25', '2026-01-05 15:06:25', 2),
('3', 'Clara', 'Mabhena', 'clara.mabhena@wayne.enterprises.com', '+263772000003', '+263772000103', 'Criminal Law', 'CERT003', '2012-11-30', 'ACTIVE', 'Criminal Defence Lawyer', 'Senior Associate', 'Experienced in criminal litigation', '2026-01-05 15:06:25', '2026-01-05 15:06:25', 3),
('L200861', 'Tadiwa', 'Gwena', 'tadiwagwena@gmail.com', '0777367773', NULL, '12', NULL, NULL, 'Active', 'Mining Rights Lawyer', NULL, NULL, '2026-01-06 07:36:48', '2026-01-06 08:01:53', NULL),
('L940147', 'ash', 'Gwena', 'lol@lol.com', '00000000', NULL, NULL, NULL, NULL, 'Active', 'Tax Advisory Lawyer', NULL, NULL, '2026-01-06 07:49:45', '2026-01-06 07:54:49', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `lawyer_groups`
--

CREATE TABLE IF NOT EXISTS `lawyer_groups` (
  `group_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`group_id`),
  UNIQUE KEY `group_name` (`group_name`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lawyer_groups`
--

INSERT INTO `lawyer_groups` (`group_id`, `group_name`, `created_at`, `updated_at`) VALUES
(1, 'Civil Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(2, 'Criminal Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(3, 'Family Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(4, 'Commercial / Corporate Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(5, 'Property & Conveyancing Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(6, 'Labour & Employment Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(7, 'Constitutional Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(8, 'Administrative Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(9, 'Human Rights Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(10, 'Notarial Practice', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(11, 'Intellectual Property Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(12, 'Mining & Energy Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(13, 'Environmental Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(14, 'Tax Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(15, 'Banking & Finance Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(16, 'Insolvency Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(17, 'Insurance Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(18, 'Estate Planning & Deceased Estates', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(19, 'Immigration Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(20, 'International Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(21, 'Arbitration & ADR', '2026-01-05 13:04:07', '2026-01-05 13:04:07'),
(22, 'Technology & Cyber Law', '2026-01-05 13:04:07', '2026-01-05 13:04:07');

-- --------------------------------------------------------

--
-- Table structure for table `lawyer_roles`
--

CREATE TABLE IF NOT EXISTS `lawyer_roles` (
  `lawyer_role_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `side` enum('plaintiff','defendant','prosecution','defense','neutral') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'neutral',
  `is_primary` tinyint(1) NOT NULL DEFAULT '0',
  `requires_bar_status` tinyint(1) NOT NULL DEFAULT '1',
  `jurisdiction_scope` enum('US','UK','GLOBAL') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'GLOBAL',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `sort_order` int UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`lawyer_role_id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lawyer_roles`
--

INSERT INTO `lawyer_roles` (`lawyer_role_id`, `code`, `name`, `description`, `side`, `is_primary`, `requires_bar_status`, `jurisdiction_scope`, `is_active`, `sort_order`, `created_at`, `updated_at`) VALUES
(1, 'lead_counsel', 'Lead Counsel', 'Primary attorney representing the party.', 'plaintiff', 1, 1, 'GLOBAL', 1, 1, '2026-01-29 13:05:00', '2026-01-29 11:22:29'),
(2, 'co_counsel', 'Co-Counsel', 'Secondary attorney assisting lead counsel.', 'defense', 0, 1, 'GLOBAL', 1, 2, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(3, 'prosecutor', 'Prosecuting Counsel', 'Attorney representing the state in criminal cases.', 'prosecution', 1, 1, 'GLOBAL', 1, 3, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(4, 'defense_attorney', 'Defense Attorney', 'Attorney representing the defendant in criminal cases.', 'defense', 1, 1, 'GLOBAL', 1, 4, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(5, 'plaintiff_counsel', 'Plaintiff Counsel', 'Attorney representing the plaintiff in civil cases.', 'plaintiff', 1, 1, 'GLOBAL', 1, 5, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(6, 'respondent_counsel', 'Respondent Counsel', 'Attorney representing the respondent/defendant in civil cases.', 'defendant', 1, 1, 'GLOBAL', 1, 6, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(7, 'amicus_counsel', 'Amicus Curiae', 'Neutral counsel providing expertise without representing a party.', 'neutral', 0, 1, 'GLOBAL', 1, 7, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(8, 'mediator', 'Mediator', 'Neutral third-party facilitating settlement or negotiation.', 'neutral', 0, 0, 'GLOBAL', 1, 8, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(9, 'legal_advisor', 'Legal Advisor', 'Provides legal guidance without courtroom representation.', 'neutral', 0, 0, 'GLOBAL', 1, 9, '2026-01-29 13:05:00', '2026-01-29 13:05:00'),
(10, 'junior_counsel', 'Junior Counsel', 'Supports lead or co-counsel; limited independent authority.', 'defense', 0, 1, 'GLOBAL', 1, 10, '2026-01-29 13:05:00', '2026-01-29 13:05:00');

-- --------------------------------------------------------

--
-- Table structure for table `lawyer_tiers`
--

CREATE TABLE IF NOT EXISTS `lawyer_tiers` (
  `tier_id` int NOT NULL AUTO_INCREMENT,
  `tier_name` varchar(50) NOT NULL,
  `description` text,
  `default_hourly_rate` decimal(12,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`tier_id`),
  UNIQUE KEY `tier_name` (`tier_name`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `lawyer_tiers`
--

INSERT INTO `lawyer_tiers` (`tier_id`, `tier_name`, `description`, `default_hourly_rate`, `created_at`, `updated_at`) VALUES
(1, 'Junior Associate', 'Newly admitted lawyer, limited experience', 30.00, '2026-01-05 14:20:50', '2026-01-05 14:20:50'),
(2, 'Associate', 'Mid-level lawyer handling most cases independently', 50.00, '2026-01-05 14:20:50', '2026-01-05 14:20:50'),
(3, 'Senior Associate', 'Experienced lawyer, complex cases', 80.00, '2026-01-05 14:20:50', '2026-01-05 14:20:50'),
(4, 'Partner', 'Law firm partner, strategic and high-profile cases', 120.00, '2026-01-05 14:20:50', '2026-01-05 14:20:50'),
(5, 'Advocate', 'Specialist litigator, courtroom expert', 150.00, '2026-01-05 14:20:50', '2026-01-05 14:20:50');

-- --------------------------------------------------------

--
-- Table structure for table `lawyer_types`
--

CREATE TABLE IF NOT EXISTS `lawyer_types` (
  `type_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` bigint UNSIGNED NOT NULL,
  `lawyer_type_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`type_id`),
  KEY `group_id` (`group_id`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lawyer_types`
--

INSERT INTO `lawyer_types` (`type_id`, `group_id`, `lawyer_type_name`, `created_at`, `updated_at`) VALUES
(1, 1, 'Contract Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(2, 1, 'Property Disputes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(3, 1, 'Debt Collection Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(4, 1, 'Personal Injury Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(5, 1, 'Defamation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(6, 2, 'Criminal Defence Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(7, 2, 'State Prosecutor', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(8, 2, 'White Collar Crime Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(9, 2, 'Economic Crimes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(10, 2, 'Bail & Sentencing Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(11, 3, 'Divorce Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(12, 3, 'Child Custody Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(13, 3, 'Maintenance Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(14, 3, 'Adoption Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(15, 3, 'Domestic Violence Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(16, 4, 'Company Formation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(17, 4, 'Corporate Governance Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(18, 4, 'Mergers & Acquisitions Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(19, 4, 'Commercial Litigation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(20, 4, 'Shareholder Disputes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(21, 5, 'Conveyancer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(22, 5, 'Property Transfer Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(23, 5, 'Mortgage Bond Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(24, 5, 'Land Development Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(25, 6, 'Employment Disputes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(26, 6, 'Retrenchment Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(27, 6, 'Labour Arbitration Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(28, 6, 'Collective Bargaining Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(29, 7, 'Constitutional Litigation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(30, 7, 'Judicial Review Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(31, 7, 'Electoral Law Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(32, 8, 'Regulatory Compliance Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(33, 8, 'Government Decisions Review Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(34, 9, 'Human Rights Litigation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(35, 9, 'Police Abuse Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(36, 9, 'Media Freedom Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(37, 10, 'Notary Public', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(38, 10, 'Document Authentication Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(39, 11, 'Trademark Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(40, 11, 'Copyright Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(41, 11, 'Patent Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(42, 12, 'Mining Rights Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(43, 12, 'Energy Contracts Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(44, 13, 'Environmental Compliance Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(45, 13, 'Environmental Litigation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(46, 14, 'Tax Advisory Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(47, 14, 'ZIMRA Disputes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(48, 15, 'Banking Compliance Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(49, 15, 'Loan & Securities Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(50, 16, 'Liquidation Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(51, 16, 'Corporate Rescue Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(52, 17, 'Insurance Claims Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(53, 17, 'Policy Disputes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(54, 18, 'Wills & Trusts Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(55, 18, 'Executor of Estates', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(56, 18, 'Inheritance Disputes Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(57, 19, 'Work Permit Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(58, 19, 'Citizenship Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(59, 20, 'International Arbitration Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(60, 20, 'Cross-Border Trade Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(61, 21, 'Arbitrator', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(62, 21, 'Mediator', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(63, 22, 'Cybercrime Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(64, 22, 'Data Protection Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12'),
(65, 22, 'E-Commerce Lawyer', '2026-01-05 13:46:12', '2026-01-05 13:46:12');

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '2026_01_01_203350_create_account_types_table', 1),
(2, '2026_01_01_203350_create_assignments_table', 1),
(3, '2026_01_01_203350_create_audit_logs_table', 1),
(4, '2026_01_01_203350_create_cache_store_table', 1),
(5, '2026_01_01_203350_create_cancelled_reasons_table', 1),
(6, '2026_01_01_203350_create_case_categories_table', 1),
(7, '2026_01_01_203350_create_case_lawyers_table', 1),
(8, '2026_01_01_203350_create_case_parties_table', 1),
(9, '2026_01_01_203350_create_case_statuses_table', 1),
(10, '2026_01_01_203350_create_case_types_table', 1),
(11, '2026_01_01_203350_create_cases_table', 1),
(12, '2026_01_01_203350_create_chat_groups_table', 1),
(13, '2026_01_01_203350_create_chat_messages_table', 1),
(14, '2026_01_01_203350_create_chat_participants_table', 1),
(15, '2026_01_01_203350_create_chat_requests_table', 1),
(16, '2026_01_01_203350_create_chat_rooms_table', 1),
(17, '2026_01_01_203350_create_client_categories_table', 1),
(18, '2026_01_01_203350_create_client_journals_table', 1),
(19, '2026_01_01_203350_create_clients_table', 1),
(20, '2026_01_01_203350_create_company_info_table', 1),
(21, '2026_01_01_203350_create_court_areas_table', 1),
(22, '2026_01_01_203350_create_court_filing_statuses_table', 1),
(23, '2026_01_01_203350_create_court_filing_types_table', 1),
(24, '2026_01_01_203350_create_court_filings_table', 1),
(25, '2026_01_01_203350_create_court_types_table', 1),
(26, '2026_01_01_203350_create_currencies_table', 1),
(27, '2026_01_01_203350_create_currency_rates_table', 1),
(28, '2026_01_01_203350_create_departments_table', 1),
(29, '2026_01_01_203350_create_diary_entries_table', 1),
(30, '2026_01_01_203350_create_features_table', 1),
(31, '2026_01_01_203350_create_filing_responsible_lawyers_table', 1),
(32, '2026_01_01_203350_create_general_journals_table', 1),
(33, '2026_01_01_203350_create_general_ledger_accounts_table', 1),
(34, '2026_01_01_203350_create_group_call_participants_table', 1),
(35, '2026_01_01_203350_create_group_calls_table', 1),
(36, '2026_01_01_203350_create_home_currency_history_table', 1),
(37, '2026_01_01_203350_create_home_currency_table', 1),
(38, '2026_01_01_203350_create_journal_transactions_table', 1),
(39, '2026_01_01_203350_create_judgements_table', 1),
(40, '2026_01_01_203350_create_judges_table', 1),
(41, '2026_01_01_203350_create_lawyer_groups_table', 1),
(42, '2026_01_01_203350_create_lawyer_tariffs_table', 1),
(43, '2026_01_01_203350_create_lawyer_types_table', 1),
(44, '2026_01_01_203350_create_lawyers_table', 1),
(45, '2026_01_01_203350_create_modules_table', 1),
(46, '2026_01_01_203350_create_notifications_table', 1),
(47, '2026_01_01_203350_create_password_otps_table', 1),
(48, '2026_01_01_203350_create_password_resets_table', 1),
(49, '2026_01_01_203350_create_permissions_table', 1),
(50, '2026_01_01_203350_create_prompt_conversations_table', 1),
(51, '2026_01_01_203350_create_prompt_messages_table', 1),
(52, '2026_01_01_203350_create_prompt_templates_table', 1),
(53, '2026_01_01_203350_create_prompts_table', 1),
(54, '2026_01_01_203350_create_role_permissions_table', 1),
(55, '2026_01_01_203350_create_subaccounts_table', 1),
(56, '2026_01_01_203350_create_submodules_table', 1),
(57, '2026_01_01_203350_create_supplier_ageing_analysis_table', 1),
(58, '2026_01_01_203350_create_supplier_areas_table', 1),
(59, '2026_01_01_203350_create_supplier_categories_table', 1),
(60, '2026_01_01_203350_create_supplier_journals_table', 1),
(61, '2026_01_01_203350_create_suppliers_table', 1),
(62, '2026_01_01_203350_create_system_settings_categories_table', 1),
(63, '2026_01_01_203350_create_system_settings_history_table', 1),
(64, '2026_01_01_203350_create_system_settings_table', 1),
(65, '2026_01_01_203350_create_taxes_table', 1),
(66, '2026_01_01_203350_create_transactions_table', 1),
(67, '2026_01_01_203350_create_uploaded_chat_files_table', 1),
(68, '2026_01_01_203350_create_user_chat_settings_table', 1),
(69, '2026_01_01_203350_create_user_groups_table', 1),
(70, '2026_01_01_203350_create_user_phones_table', 1),
(71, '2026_01_01_203350_create_user_roles_table', 1),
(72, '2026_01_01_203350_create_user_sessions_table', 1),
(73, '2026_01_01_203350_create_user_tiers_table', 1),
(74, '2026_01_01_203350_create_users_table', 1),
(75, '2026_01_01_203353_add_foreign_keys_to_password_resets_table', 1),
(76, '2026_01_01_203353_add_foreign_keys_to_prompt_conversations_table', 1),
(77, '2026_01_01_203353_add_foreign_keys_to_prompt_messages_table', 1),
(78, '2026_01_01_203353_add_foreign_keys_to_prompt_templates_table', 1),
(79, '2026_01_01_203353_add_foreign_keys_to_prompts_table', 1),
(80, '2026_01_01_204638_create_sessions_table', 1),
(81, '2026_01_03_072509_create_sessions_table', 2);

-- --------------------------------------------------------

--
-- Table structure for table `modules`
--

CREATE TABLE IF NOT EXISTS `modules` (
  `module_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `module_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`module_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE IF NOT EXISTS `notifications` (
  `notification_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `type` enum('message','call','group','request','system') COLLATE utf8mb4_unicode_ci DEFAULT 'message',
  `title` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8mb4_unicode_ci,
  `related_id` bigint UNSIGNED DEFAULT NULL,
  `related_type` enum('chat','call','group','system') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `read_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`notification_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

CREATE TABLE IF NOT EXISTS `permissions` (
  `permission_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `feature_id` bigint UNSIGNED NOT NULL,
  `can_view` tinyint(1) DEFAULT '0',
  `can_add` tinyint(1) DEFAULT '0',
  `can_edit` tinyint(1) DEFAULT '0',
  `can_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`permission_id`),
  KEY `feature_id` (`feature_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `prompts`
--

CREATE TABLE IF NOT EXISTS `prompts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT '0',
  `is_public` tinyint(1) DEFAULT '0',
  `usage_count` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_prompts_user_id` (`user_id`),
  KEY `idx_prompts_category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `prompt_conversations`
--

CREATE TABLE IF NOT EXISTS `prompt_conversations` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `title` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `model` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total_messages` int DEFAULT '0',
  `total_tokens` int DEFAULT '0',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_message_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_conversations_user_id` (`user_id`),
  KEY `idx_conversations_status` (`status`),
  KEY `idx_conversations_created_at` (`created_at`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `prompt_messages`
--

CREATE TABLE IF NOT EXISTS `prompt_messages` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint UNSIGNED NOT NULL,
  `role` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `tokens` int DEFAULT NULL,
  `model` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_messages_conversation_id` (`conversation_id`),
  KEY `idx_messages_created_at` (`created_at`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `prompt_templates`
--

CREATE TABLE IF NOT EXISTS `prompt_templates` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `template` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_system` tinyint(1) DEFAULT '0',
  `usage_count` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_templates_created_by` (`created_by`),
  KEY `idx_templates_category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `role_permissions`
--

CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_permission_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` bigint UNSIGNED NOT NULL,
  `permission_id` bigint UNSIGNED NOT NULL,
  PRIMARY KEY (`role_permission_id`),
  KEY `role_id` (`role_id`),
  KEY `permission_id` (`permission_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `subaccounts`
--

CREATE TABLE IF NOT EXISTS `subaccounts` (
  `subaccount_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `main_account_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `main_account_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sub_account_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sub_account_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`subaccount_id`),
  UNIQUE KEY `unique_subaccount` (`main_account_code`,`sub_account_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `submodules`
--

CREATE TABLE IF NOT EXISTS `submodules` (
  `submodule_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `module_id` bigint UNSIGNED NOT NULL,
  `submodule_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`submodule_id`),
  KEY `module_id` (`module_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `suppliers`
--

CREATE TABLE IF NOT EXISTS `suppliers` (
  `supplier_id` int NOT NULL AUTO_INCREMENT,
  `supplier_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `supplier_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `full_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_person` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_1` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_2` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `category_id` int DEFAULT NULL,
  `age_analysis_id` int DEFAULT NULL,
  `area_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`supplier_id`),
  UNIQUE KEY `supplier_code` (`supplier_code`),
  KEY `fk_supplier_category` (`category_id`),
  KEY `fk_supplier_ageing` (`age_analysis_id`),
  KEY `fk_supplier_area` (`area_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `suppliers`
--

INSERT INTO `suppliers` (`supplier_id`, `supplier_code`, `supplier_name`, `full_name`, `contact_person`, `contact_1`, `contact_2`, `email`, `address`, `category_id`, `age_analysis_id`, `area_id`, `created_at`, `updated_at`) VALUES
(1, 'SUP-001', 'Alpha Supplies', 'Alpha Supplies Pvt Ltd', 'John Moyo', '+263771000001', NULL, 'info@alphasupplies.com', '12 Samora Machel Ave, Harare', 1, NULL, 1, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(2, 'SUP-002', 'Beta Traders', 'Beta Traders Zimbabwe', 'Sarah Dube', '+263771000002', '+263242700002', 'sales@betatraders.co.zw', '45 Jason Moyo St, Bulawayo', 2, NULL, 2, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(3, 'SUP-003', 'Gamma Industries', 'Gamma Industries Ltd', 'Tawanda Chirwa', '+263771000003', NULL, 'contact@gamma.co.zw', '7 Coventry Rd, Workington', 1, NULL, 3, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(4, 'SUP-004', 'Delta Logistics', 'Delta Logistics Services', 'Nomsa Ncube', '+263771000004', NULL, 'admin@deltalogistics.com', '88 Airport Rd, Harare', 3, NULL, 1, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(5, 'SUP-005', 'Epsilon Holdings', 'Epsilon Holdings Africa', 'Brian Zhou', '+263771000005', '+263242700005', 'info@epsilon.africa', '102 Enterprise Rd, Harare', 2, NULL, 1, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(6, 'SUP-006', 'Zeta Chemicals', 'Zeta Chemicals Pvt Ltd', 'Farai Mutasa', '+263771000006', NULL, 'support@zetachem.co.zw', '15 Tilbury Rd, Willowvale', 4, NULL, 3, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(7, 'SUP-007', 'Eta Engineering', 'Eta Engineering Services', 'Clive Banda', '+263771000007', NULL, 'projects@etaeng.co.zw', '22 Seke Rd, Harare', 3, NULL, 2, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(8, 'SUP-008', 'Theta Foods', 'Theta Foods & Catering', 'Rudo Mahachi', '+263771000008', '+263242700008', 'orders@thetafoods.co.zw', '9 Chinhoyi St, Gweru', 5, NULL, 4, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(9, 'SUP-009', 'Iota Technologies', 'Iota Technologies Zimbabwe', 'Kudzai Marume', '+263771000009', NULL, 'hello@iotatech.co.zw', '60 Borrowdale Rd, Harare', 6, NULL, 1, '2026-01-07 09:06:37', '2026-01-07 09:06:37'),
(10, 'SUP-010', 'Kappa Medical', 'Kappa Medical Supplies', 'Dr. L. Nyathi', '+263771000010', NULL, 'procurement@kappamed.co.zw', '14 Hospital Rd, Mutare', 7, NULL, 5, '2026-01-07 09:06:37', '2026-01-07 09:06:37');

-- --------------------------------------------------------

--
-- Table structure for table `supplier_ageing_analysis`
--

CREATE TABLE IF NOT EXISTS `supplier_ageing_analysis` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age_code` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `ageing_group` enum('monthly','daily','custom') NOT NULL,
  `interval_type` enum('statement_date','invoice_date') NOT NULL,
  `interval_value` varchar(50) NOT NULL,
  `custom_age_groups` text,
  `custom_intervals` text,
  `actions` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_areas`
--

CREATE TABLE IF NOT EXISTS `supplier_areas` (
  `area_id` int NOT NULL AUTO_INCREMENT,
  `area_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `area_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `specific_location` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `area_code` (`area_code`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `supplier_areas`
--

INSERT INTO `supplier_areas` (`area_id`, `area_code`, `area_name`, `city`, `country`, `specific_location`, `created_at`) VALUES
(1, 'AC123', 'Downtown', 'London', 'UK', 'Warehouse 1', '2026-01-03 10:04:16');

-- --------------------------------------------------------

--
-- Table structure for table `supplier_categories`
--

CREATE TABLE IF NOT EXISTS `supplier_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `category_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_code` (`category_code`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `supplier_categories`
--

INSERT INTO `supplier_categories` (`category_id`, `category_code`, `category_name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'SUPCT001', 'OFFICE SUPPLIES', 'STATIONERY', '2026-01-14 14:24:54', '2026-01-15 06:35:02');

-- --------------------------------------------------------

--
-- Table structure for table `supplier_invoices`
--

CREATE TABLE IF NOT EXISTS `supplier_invoices` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint UNSIGNED NOT NULL,
  `invoice_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `invoice_date` date NOT NULL,
  `reference_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_person` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `narration` text COLLATE utf8mb4_unicode_ci,
  `reference_det` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gl_account` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tax_id` bigint UNSIGNED NOT NULL,
  `discount` decimal(5,2) NOT NULL DEFAULT '0.00',
  `price` decimal(15,2) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT '1.00',
  `currency_id` bigint UNSIGNED NOT NULL,
  `operation_cost` decimal(15,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `idx_supplier_invoices_supplier_id` (`supplier_id`),
  KEY `idx_supplier_invoices_invoice_date` (`invoice_date`),
  KEY `idx_supplier_invoices_invoice_number` (`invoice_number`),
  KEY `idx_supplier_invoices_gl_account` (`gl_account`),
  KEY `idx_supplier_invoices_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_journals`
--

CREATE TABLE IF NOT EXISTS `supplier_journals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `supplier_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `supplier_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `narration` text COLLATE utf8mb4_unicode_ci,
  `dr` decimal(15,2) DEFAULT '0.00',
  `cr` decimal(15,2) DEFAULT '0.00',
  `status` enum('pending','approved','rejected','reversed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `supplier_id` int DEFAULT NULL,
  `transaction_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_code` (`transaction_code`),
  KEY `idx_date` (`date`),
  KEY `idx_supplier_code` (`supplier_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings`
--

CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` bigint UNSIGNED DEFAULT NULL,
  `setting_key` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `setting_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `setting_value` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'string',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `setting_key` (`setting_key`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings_categories`
--

CREATE TABLE IF NOT EXISTS `system_settings_categories` (
  `category_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_name` (`category_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings_history`
--

CREATE TABLE IF NOT EXISTS `system_settings_history` (
  `history_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_id` bigint UNSIGNED DEFAULT NULL,
  `old_value` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `new_value` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `changed_by` bigint UNSIGNED DEFAULT NULL,
  `changed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`history_id`),
  KEY `setting_id` (`setting_id`),
  KEY `changed_by` (`changed_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `tariffs`
--

CREATE TABLE IF NOT EXISTS `tariffs` (
  `tariff_id` int NOT NULL AUTO_INCREMENT,
  `lawyer_id` int NOT NULL,
  `tier_id` int NOT NULL,
  `group_id` int NOT NULL,
  `type_id` int NOT NULL,
  `service_name` varchar(150) NOT NULL,
  `billing_method` enum('HOURLY','FIXED_FEE','PER_APPEARANCE','PER_STAGE','RETAINER_PLUS','CONTINGENCY','SUBSCRIPTION') NOT NULL,
  `rate_type` enum('PER_HOUR','PER_CASE','PER_DOCUMENT','PER_APPEARANCE','PER_CONSULTATION','PER_MONTH','PERCENTAGE') NOT NULL,
  `rate_amount` decimal(12,2) NOT NULL,
  `currency` char(3) DEFAULT 'USD',
  `billing_increment` int DEFAULT '60' COMMENT 'Minutes (15, 30, 60)',
  `minimum_fee` decimal(12,2) DEFAULT NULL,
  `maximum_fee` decimal(12,2) DEFAULT NULL,
  `retainer_required` tinyint(1) DEFAULT '0',
  `retainer_amount` decimal(12,2) DEFAULT NULL,
  `court_level` enum('MAGISTRATES','HIGH_COURT','SUPREME_COURT','CONSTITUTIONAL_COURT') DEFAULT NULL,
  `complexity_level` enum('LOW','MEDIUM','HIGH') DEFAULT 'MEDIUM',
  `tax_inclusive` tinyint(1) DEFAULT '0',
  `vat_rate` decimal(5,2) DEFAULT '0.00',
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`tariff_id`),
  KEY `fk_tariff_lawyer` (`lawyer_id`),
  KEY `fk_tariff_tier` (`tier_id`),
  KEY `fk_tariff_group` (`group_id`),
  KEY `fk_tariff_type` (`type_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `tariffs`
--

INSERT INTO `tariffs` (`tariff_id`, `lawyer_id`, `tier_id`, `group_id`, `type_id`, `service_name`, `billing_method`, `rate_type`, `rate_amount`, `currency`, `billing_increment`, `minimum_fee`, `maximum_fee`, `retainer_required`, `retainer_amount`, `court_level`, `complexity_level`, `tax_inclusive`, `vat_rate`, `effective_from`, `effective_to`, `description`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 'Contract Drafting', 'HOURLY', 'PER_HOUR', 30.00, 'USD', 60, 50.00, 500.00, 1, 100.00, 'HIGH_COURT', 'MEDIUM', 1, 15.00, '2026-01-01', NULL, 'Drafting standard commercial contracts', '2026-01-05 14:24:41', '2026-01-05 14:24:41'),
(2, 2, 2, 3, 11, 'Divorce Filing', 'FIXED_FEE', 'PER_CASE', 200.00, 'USD', 0, 200.00, 200.00, 1, 100.00, 'HIGH_COURT', 'HIGH', 1, 15.00, '2026-01-01', NULL, 'Full divorce procedure including court filing', '2026-01-05 14:24:41', '2026-01-05 14:24:41'),
(3, 3, 3, 2, 7, 'Criminal Defence - Court Appearance', 'PER_APPEARANCE', 'PER_APPEARANCE', 100.00, 'USD', 0, 100.00, 300.00, 0, 0.00, 'MAGISTRATES', 'HIGH', 0, 0.00, '2026-01-01', NULL, 'Representation in criminal matters per appearance', '2026-01-05 14:24:41', '2026-01-05 14:24:41'),
(4, 4, 4, 5, 21, 'Property Transfer', 'FIXED_FEE', 'PER_CASE', 350.00, 'USD', 0, 350.00, 350.00, 1, 150.00, 'HIGH_COURT', 'MEDIUM', 1, 15.00, '2026-01-01', NULL, 'Conveyancing and property transfer services', '2026-01-05 14:24:41', '2026-01-05 14:24:41'),
(5, 5, 5, 4, 18, 'Commercial Litigation', 'HOURLY', 'PER_HOUR', 150.00, 'USD', 60, 150.00, 1500.00, 1, 500.00, 'HIGH_COURT', 'HIGH', 1, 15.00, '2026-01-01', NULL, 'High-profile commercial disputes', '2026-01-05 14:24:41', '2026-01-05 14:24:41');

-- --------------------------------------------------------

--
-- Table structure for table `taxes`
--

CREATE TABLE IF NOT EXISTS `taxes` (
  `tax_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tax_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tax_description` text COLLATE utf8mb4_unicode_ci,
  `tax_rate` decimal(5,2) NOT NULL,
  `tax_type` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_active` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`tax_id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `taxes`
--

INSERT INTO `taxes` (`tax_id`, `tax_name`, `tax_description`, `tax_rate`, `tax_type`, `created_at`, `updated_at`, `is_active`) VALUES
(1, 'AIDS LEVY', NULL, 3.00, 'STATE AID', '2026-01-03 08:00:49', '2026-01-07 09:43:13', 1),
(2, 'VAT – Professional Legal Services', 'Value Added Tax charged on professional legal services as per ZIMRA regulations', 15.00, 'VAT', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(3, 'Withholding Tax – Legal Fees', 'Statutory withholding tax deducted on legal fees payable to practitioners', 10.00, 'WITHHOLDING', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(4, 'Capital Gains Tax – Property Transfers', 'Capital gains tax applicable on conveyancing and property transfer transactions', 20.00, 'CAPITAL_GAINS', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(5, 'Stamp Duty – Property Transfer', 'Stamp duty payable on transfer of immovable property', 4.00, 'DUTY', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(6, 'Stamp Duty – Share Transfer', 'Stamp duty payable on transfer of shares', 2.00, 'DUTY', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(7, 'Withholding Tax – Consultancy Services', 'Withholding tax on consultancy and advisory services related to legal work', 10.00, 'WITHHOLDING', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(8, 'Presumptive Tax – Professional Services', 'Presumptive tax applicable to professional service providers where applicable', 5.00, 'PRESUMPTIVE', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(9, 'VAT – Disbursements Recoverable', 'VAT charged on recoverable disbursements billed to clients', 15.00, 'VAT', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(10, 'Court Levy', 'Statutory court levy payable on filing of court documents and processes', 2.00, 'LEVY', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(11, 'Transfer Duty – Deeds Registry', 'Government transfer duty payable at the Deeds Registry during conveyancing', 3.00, 'DUTY', '2026-01-07 09:41:41', '2026-01-07 09:41:41', 1),
(12, 'VAT', 'Value Added Tax', 15.00, 'VAT', '2026-01-07 08:02:37', '2026-01-07 08:02:37', 1),
(13, 'Sales Tax', 'Sales Tax', 10.00, 'Sales Tax', '2026-01-07 08:02:37', '2026-01-07 08:02:37', 1),
(14, 'Service Tax', 'Service Tax', 12.00, 'Service Tax', '2026-01-07 08:02:37', '2026-01-07 08:02:37', 1),
(15, 'Excise Duty', 'Excise Duty', 20.00, 'Excise Duty', '2026-01-07 08:02:37', '2026-01-07 08:02:37', 1),
(16, 'No Tax', 'No Tax Applicable', 0.00, 'None', '2026-01-07 08:02:37', '2026-01-07 08:02:37', 1);

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

CREATE TABLE IF NOT EXISTS `transactions` (
  `transaction_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_code` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entry_date` date NOT NULL,
  `narration` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gl_account_id` bigint UNSIGNED NOT NULL,
  `subaccount_id` bigint UNSIGNED DEFAULT NULL,
  `contra_gl_account_id` bigint UNSIGNED DEFAULT NULL,
  `contra_subaccount_id` bigint UNSIGNED DEFAULT NULL,
  `debit` decimal(18,2) NOT NULL DEFAULT '0.00',
  `credit` decimal(18,2) NOT NULL DEFAULT '0.00',
  `quantity` decimal(18,4) DEFAULT NULL,
  `unit_price` decimal(18,2) DEFAULT NULL,
  `discount` decimal(18,2) DEFAULT '0.00',
  `tax_amount` decimal(18,2) DEFAULT '0.00',
  `tax_id` bigint UNSIGNED DEFAULT NULL,
  `line_total` decimal(18,2) NOT NULL,
  `source` enum('invoice','supplier_invoice','receipt','quotation','journal','cashbook','payment','adjustment','other') COLLATE utf8mb4_unicode_ci DEFAULT 'journal',
  `related_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_id` bigint UNSIGNED DEFAULT NULL,
  `batch_code` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_path` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,6) DEFAULT '1.000000',
  `posted` tinyint(1) DEFAULT '0',
  `posted_at` datetime DEFAULT NULL,
  `is_reconciled` tinyint(1) DEFAULT '0',
  `reconcile_reference` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_id` bigint UNSIGNED DEFAULT NULL,
  `case_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `updated_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  UNIQUE KEY `transaction_code` (`transaction_code`),
  KEY `idx_transactions_related` (`related_type`,`related_id`),
  KEY `idx_transactions_entry_date` (`entry_date`),
  KEY `gl_account_id` (`gl_account_id`),
  KEY `subaccount_id` (`subaccount_id`),
  KEY `contra_gl_account_id` (`contra_gl_account_id`),
  KEY `contra_subaccount_id` (`contra_subaccount_id`),
  KEY `tax_id` (`tax_id`),
  KEY `idx_transactions_source` (`source`),
  KEY `currency_id` (`currency_id`),
  KEY `idx_transactions_posted` (`posted`),
  KEY `idx_transactions_client` (`client_id`),
  KEY `idx_transactions_case` (`case_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uploaded_chat_files`
--

CREATE TABLE IF NOT EXISTS `uploaded_chat_files` (
  `file_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `chat_id` bigint UNSIGNED DEFAULT NULL,
  `file_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_size` bigint DEFAULT NULL,
  `file_url` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`file_id`),
  KEY `user_id` (`user_id`),
  KEY `chat_id` (`chat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_chat_settings`
--

CREATE TABLE IF NOT EXISTS `user_chat_settings` (
  `setting_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `enable_sound` tinyint(1) DEFAULT '1',
  `auto_scroll` tinyint(1) DEFAULT '1',
  `show_typing_indicators` tinyint(1) DEFAULT '1',
  `dark_mode` tinyint(1) DEFAULT '0',
  `show_online_status` tinyint(1) DEFAULT '1',
  `save_chat_history` tinyint(1) DEFAULT '1',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_groups`
--

CREATE TABLE IF NOT EXISTS `user_groups` (
  `user_group_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `group_description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_phones`
--

CREATE TABLE IF NOT EXISTS `user_phones` (
  `phone_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED NOT NULL,
  `phone_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone_type` enum('primary','secondary','work','emergency') COLLATE utf8mb4_unicode_ci DEFAULT 'primary',
  `is_verified` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`phone_id`),
  KEY `idx_user_phones_user_id` (`user_id`),
  KEY `idx_user_phones_number` (`phone_number`),
  KEY `idx_user_phones_verified` (`is_verified`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_roles`
--

CREATE TABLE IF NOT EXISTS `user_roles` (
  `user_role_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  `role_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role_description` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`user_role_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_roles`
--

INSERT INTO `user_roles` (`user_role_id`, `created_at`, `assigned_at`, `updated_at`, `role_name`, `role_description`) VALUES
(1, NULL, '2026-01-01 19:21:51', '2026-01-01 19:21:51', 'admin', 'Administrator with full access'),
(2, NULL, '2026-01-01 19:21:51', '2026-01-01 19:21:51', 'lawyer', 'Lawyer with case management access'),
(3, NULL, '2026-01-01 19:21:51', '2026-01-01 19:21:51', 'paralegal', 'Paralegal with limited access');

-- --------------------------------------------------------

--
-- Table structure for table `user_tiers`
--

CREATE TABLE IF NOT EXISTS `user_tiers` (
  `tier_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tier_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`tier_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_tiers`
--

INSERT INTO `user_tiers` (`tier_id`, `tier_name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Tier One', 'supreme user..same as 1 but has backdoor access', '2026-01-05 14:08:22', '2026-01-05 14:08:22'),
(2, 'Restricted Access', 'Limited system access with basic permissions', '2026-01-05 14:08:22', '2026-01-05 14:08:22'),
(3, 'Standard User', 'Regular system access with standard permissions', '2026-01-05 14:08:22', '2026-01-05 14:08:22'),
(4, 'Elevated Access', 'Higher security clearance with extended permissions', '2026-01-05 14:08:22', '2026-01-05 14:08:22'),
(5, 'Administrator', 'Full system access with administrative privileges', '2026-01-05 14:08:22', '2026-01-05 14:08:22'),
(6, 'System Auditor', 'Read-only access for compliance and auditing purposes', '2026-01-05 14:08:22', '2026-01-05 14:08:22');
COMMIT;

