02-05-2026
ALTER TABLE `cas_case_directions` CHANGE `hearing_dir_id` `hearing_dir_id` INT(11) NULL DEFAULT NULL;

30-04-2026
ALTER TABLE `comp_cases` CHANGE `contact_case` `contact_case` INT(11) NOT NULL;


24-04-2025
CREATE TABLE IF NOT EXISTS `cas_time_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL,
  `timeEntries_id` varchar(255) NOT NULL,
  `timeEntries_company_id` varchar(255) NOT NULL,
  `timeEntries_client_id` varchar(255) NOT NULL,
  `timeEntries_case_id` varchar(255) NOT NULL,
  `timeEntries_user_id` varchar(255) NOT NULL,
  `timeEntries_task_desc` text DEFAULT NULL,
  `timeEntries_start_time` time DEFAULT NULL,
  `timeEntries_end_time` time DEFAULT NULL,
  `timeEntries_total_time` varchar(20) DEFAULT NULL,
  `timeEntries_billable` tinyint(4) NOT NULL,
  `timeEntries_hr_rate` decimal(10,2) NOT NULL,
  `timeEntries_total_charge` decimal(10,2) NOT NULL,
  `timeEntries_date_record` datetime DEFAULT NULL,
  `timeEntries_invoice_status` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime DEFAULT current_timestamp(),
  `updated_on` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

21-04-2024
ALTER TABLE `sit_site_details` ADD `case_id` VARCHAR(256) NOT NULL AFTER `id`;

CREATE TABLE IF NOT EXISTS `cas_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL,
  `document_id` varchar(255) NOT NULL,
  `document_company_id` varchar(255) DEFAULT NULL,
  `document_client_id` varchar(255) DEFAULT NULL,
  `document_case_id` varchar(255) DEFAULT NULL,
  `document_title` varchar(255) DEFAULT NULL,
  `document_type` int(11) DEFAULT NULL,
  `document_file` varchar(255) DEFAULT NULL,
  `document_upload_date` datetime DEFAULT NULL,
  `document_uploaded_by` varchar(11) DEFAULT NULL,
  `document_version_number` varchar(50) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_on` datetime DEFAULT current_timestamp(),
  `updated_on` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`cases_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL DEFAULT 0,
  `note_id` varchar(255) NOT NULL,
  `note_company_id` varchar(255) DEFAULT NULL,
  `note_client_id` varchar(255) DEFAULT NULL,
  `note_case_id` varchar(255) DEFAULT NULL,
  `note_title` varchar(255) DEFAULT NULL,
  `note_content` text DEFAULT NULL,
  `note_type` int(11) DEFAULT NULL,
  `note_created_by` int(11) DEFAULT NULL,
  `note_date_created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_on` datetime DEFAULT current_timestamp(),
  `updated_on` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_reminders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL DEFAULT 0,
  `reminder_id` varchar(256) NOT NULL,
  `reminder_company_id` varchar(256) DEFAULT NULL,
  `reminder_client_id` varchar(256) DEFAULT NULL,
  `reminder_case_id` varchar(256) DEFAULT NULL,
  `reminder_task_id` varchar(256) DEFAULT NULL,
  `reminder_title` varchar(256) DEFAULT NULL,
  `reminder_description` text DEFAULT NULL,
  `reminder_dates` datetime DEFAULT NULL,
  `reminder_time` time DEFAULT NULL,
  `reminder_status` int(11) DEFAULT NULL,
  `reminder_created_by` int(11) DEFAULT NULL,
  `reminder_date_created` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_on` datetime DEFAULT current_timestamp(),
  `updated_on` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`cases_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL DEFAULT 0,
  `task_id` varchar(256) NOT NULL,
  `task_company_id` varchar(256) NOT NULL,
  `task_client_id` varchar(256) NOT NULL,
  `task_case_id` varchar(256) NOT NULL,
  `task_title` varchar(256) NOT NULL,
  `task_description` text DEFAULT NULL,
  `task_due_date` datetime DEFAULT NULL,
  `task_priority` int(11) DEFAULT NULL,
  `task_assigned_user` int(11) DEFAULT NULL,
  `task_status` int(11) DEFAULT NULL,
  `task_date_created` datetime DEFAULT NULL,
  `task_complete_date` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT 0,
  `created_on` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cas_hearing_prepar_checklist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) DEFAULT NULL,
  `checklist_item_id` varchar(50) DEFAULT NULL,
  `hearing_prepar_company_id` varchar(256) DEFAULT NULL,
  `hearing_prepar_case_id` varchar(256) DEFAULT NULL,
  `hearing_prepar_id` varchar(255) DEFAULT NULL,
  `checklist_item_title` varchar(255) DEFAULT NULL,
  `checklist_item_description` text DEFAULT NULL,
  `hearing_prepar_due_date` datetime DEFAULT NULL,
  `hearing_prepar_assigned_user` int(11) DEFAULT NULL,
  `hearing_prepar_complete_date` datetime DEFAULT NULL,
  `hearing_prepar_status` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_on` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_case_directions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` varchar(256) NOT NULL,
  `hearing_dir_id` varchar(256) DEFAULT NULL,
  `direction_id` varchar(256) DEFAULT NULL,
  `hearing_case_id` varchar(256) NOT NULL,
  `direction_description` text DEFAULT NULL,
  `responsibe_party` varchar(255) DEFAULT NULL,
  `deadline_date` datetime DEFAULT NULL,
  `reminder_date` datetime DEFAULT NULL,
  `direction_status` varchar(50) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cases_id` (`cases_id`),
  KEY `hearing_id` (`hearing_dir_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_case_hearing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL,
  `hearing_id` varchar(100) DEFAULT NULL,
  `hearing_type` varchar(100) NOT NULL,
  `court_name` varchar(255) DEFAULT NULL,
  `hearing_date` datetime DEFAULT NULL,
  `hearing_time` time DEFAULT NULL,
  `court_room` varchar(100) DEFAULT NULL,
  `judge` varchar(255) DEFAULT NULL,
  `hearing_format` varchar(100) DEFAULT NULL,
  `hearing_outcome` text DEFAULT NULL,
  `date_created` date DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cases_id` (`cases_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_case_timeline` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cases_id` int(11) NOT NULL,
  `activity_type` varchar(100) NOT NULL,
  `activity_date_time` datetime DEFAULT NULL,
  `activity_number` varchar(100) DEFAULT NULL,
  `activity_description` text DEFAULT NULL,
  `related_record_id` varchar(256) DEFAULT NULL,
  `case_timeline_user` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `cases_id` (`cases_id`),
  KEY `case_timeline_user` (`case_timeline_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `cas_cases_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_number` varchar(255) NOT NULL,
  `company_id` int(11) NOT NULL,
  `client_name` varchar(255) NOT NULL,
  `case_title` varchar(255) NOT NULL,
  `case_type` int(11) NOT NULL,
  `case_reference_number` varchar(255) NOT NULL,
  `date_opened` datetime NOT NULL,
  `case_status` int(11) NOT NULL,
  `next_hearing_date` datetime NOT NULL,
  `next_deadline_date` datetime NOT NULL,
  `opposing_party_contact_id` varchar(255) NOT NULL,
  `opposing_solicitor_contact_id` varchar(255) NOT NULL,
  `archive_status` int(11) NOT NULL,
  `user_assigned` varchar(255) NOT NULL,
  `is_archive` int(11) NOT NULL,
  `archived_by` int(11) NOT NULL,
  `archived_on` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `job_number` (`case_number`),
  KEY `client_id` (`client_name`),
  KEY `is_archive` (`is_archive`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- 07-04-2026
-- ALTER TABLE `job_details` CHANGE `job_number` `case_number` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
-- ALTER TABLE `job_details` ADD `company_id` VARCHAR(255) NOT NULL AFTER `case_number`;
-- ALTER TABLE `job_details` CHANGE `job_description` `case_title` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
-- ALTER TABLE `job_details` ADD `case_type` VARCHAR(255) NOT NULL AFTER `case_title`, ADD `case_reference_number` VARCHAR(255) NOT NULL AFTER `case_type`, ADD `date_opened` DATETIME NOT NULL AFTER `case_reference_number`, ADD `case_status` INT(11) NOT NULL AFTER `date_opened`;
-- ALTER TABLE `job_details` CHANGE `job_start_dt` `next_hearing_date` DATETIME NOT NULL;
-- ALTER TABLE `job_details` CHANGE `job_end_dt` `next_deadline_date` DATETIME NOT NULL;
-- ALTER TABLE `job_details` ADD `opposing_party_contact_id` VARCHAR(255) NOT NULL AFTER `next_deadline_date`, ADD `opposing_solicitor_contact_id` VARCHAR(255) NOT NULL AFTER `opposing_party_contact_id`, ADD `archive_status` INT(11) NOT NULL AFTER `opposing_solicitor_contact_id`;

-- RENAME TABLE `demo8_mckenzies_friends`.`job_details` TO `demo8_mckenzies_friends`.`cas_cases_details`;
