Installation - mx_mdt

1. SQL

Lancez le sql.sql dans votre base de donnée :

sqlCopier
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT ;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS ;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION ;
SET NAMES utf8mb4 ;

CREATE TABLE `mdt_absences` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `requester_discord_id` varchar(64) DEFAULT NULL,
  `requester_name` varchar(120) NOT NULL,
  `requester_grade` varchar(120) DEFAULT NULL,
  `requester_avatar` text DEFAULT NULL,
  `absence_type` varchar(32) NOT NULL DEFAULT 'OTHER',
  `priority` varchar(16) NOT NULL DEFAULT 'NORMAL',
  `status` varchar(16) NOT NULL DEFAULT 'pending',
  `reason` varchar(255) NOT NULL,
  `notes` mediumtext DEFAULT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `return_at` datetime DEFAULT NULL,
  `cover_agent` varchar(120) DEFAULT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `decided_by_discord_id` varchar(64) DEFAULT NULL,
  `decided_by_name` varchar(120) DEFAULT NULL,
  `decision_note` mediumtext DEFAULT NULL,
  `decided_at` datetime DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_agents` (
  `id` int(11) NOT NULL,
  `discord_id` varchar(50) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  `grade_role_id` varchar(50) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `arrival_date` date DEFAULT NULL,
  `rank` varchar(100) DEFAULT NULL,
  `function_label` varchar(100) DEFAULT NULL,
  `badge_number` varchar(50) DEFAULT NULL,
  `full_name` varchar(150) DEFAULT NULL,
  `post` varchar(150) DEFAULT NULL,
  `service_strip` int(1) DEFAULT 0,
  `last_strip_date` date DEFAULT NULL,
  `activity_status` varchar(100) DEFAULT NULL,
  `first_division` varchar(100) DEFAULT NULL,
  `second_division` varchar(100) DEFAULT NULL,
  `last_promotion` date DEFAULT NULL,
  `first_speciality` varchar(100) DEFAULT NULL,
  `second_speciality` varchar(100) DEFAULT NULL,
  `sanctions` text DEFAULT NULL,
  `awards` text DEFAULT NULL,
  `off_duty_firearm` tinyint(1) DEFAULT 0,
  `google_mail` varchar(150) DEFAULT NULL,
  `patrol_detachment_id` int(11) DEFAULT NULL,
  `matricule` varchar(50) DEFAULT NULL,
  `custom_avatar` varchar(255) DEFAULT NULL,
  `discord_avatar` varchar(255) DEFAULT NULL,
  `discord_left_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_arrest_reports` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `content` longtext DEFAULT NULL,
  `miranda_read` varchar(10) DEFAULT 'yes',
  `cooperative` varchar(10) DEFAULT 'yes',
  `lawyer` varchar(255) DEFAULT '',
  `prosecutor` varchar(255) DEFAULT '',
  `judge` varchar(255) DEFAULT '',
  `responsible_id` int(11) DEFAULT NULL,
  `responsible_name` varchar(255) DEFAULT '',
  `status` varchar(50) DEFAULT 'pending',
  `linked_ois_report_id` int(11) DEFAULT NULL,
  `total_fine` int(11) DEFAULT 0,
  `total_points` int(11) DEFAULT 0,
  `suspects` longtext DEFAULT NULL,
  `agents` longtext DEFAULT NULL,
  `charges` longtext DEFAULT NULL,
  `evidence` longtext DEFAULT NULL,
  `comments` longtext DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_bips` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `channel_id` varchar(512) NOT NULL,
  `color_theme` varchar(32) DEFAULT 'danger',
  `code_label` varchar(120) DEFAULT 'CODE 4 déclaré par',
  `priority` varchar(32) DEFAULT 'urgent',
  `location` varchar(255) NOT NULL,
  `radio_frequency` varchar(64) DEFAULT NULL,
  `incident_commander` varchar(255) DEFAULT NULL,
  `units_requested` varchar(255) DEFAULT NULL,
  `status` varchar(32) DEFAULT 'active',
  `closed_at_text` varchar(32) DEFAULT NULL,
  `situation` text DEFAULT NULL,
  `engaged_agents_text` text DEFAULT NULL,
  `extra_mentions` text DEFAULT NULL,
  `target_scope_json` longtext DEFAULT NULL,
  `target_summary` text DEFAULT NULL,
  `sender_name` varchar(255) DEFAULT NULL,
  `sender_department` varchar(255) DEFAULT NULL,
  `discord_message_id` varchar(64) DEFAULT NULL,
  `thread_id` varchar(64) DEFAULT NULL,
  `discord_jump_url` text DEFAULT NULL,
  `bot_status` varchar(32) DEFAULT 'pending',
  `sent_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `closed_by` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_bracelets` (
  `id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `bracelet_number` varchar(32) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `citizen_name` varchar(255) DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'ACTIVE',
  `bracelet_type` varchar(64) NOT NULL DEFAULT 'GPS',
  `risk_level` varchar(32) NOT NULL DEFAULT 'STANDARD',
  `assigned_at` bigint(20) NOT NULL,
  `expires_at` bigint(20) DEFAULT NULL,
  `removed_at` bigint(20) DEFAULT NULL,
  `zone_label` varchar(255) DEFAULT NULL,
  `zone_details` text DEFAULT NULL,
  `last_known_location` varchar(255) DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `conditions_text` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `assigned_by` varchar(255) DEFAULT NULL,
  `removed_by` varchar(255) DEFAULT NULL,
  `removal_reason` text DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_chat_categories` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `name` varchar(80) NOT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_chat_channels` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `name` varchar(80) NOT NULL,
  `topic` varchar(255) DEFAULT NULL,
  `is_private` tinyint(1) NOT NULL DEFAULT 1,
  `position` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_chat_channel_acl` (
  `id` int(11) NOT NULL,
  `channel_id` int(11) NOT NULL,
  `allow_grade_role_id` varchar(32) DEFAULT NULL,
  `allow_agent_discord_id` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_chat_mentions` (
  `id` bigint(20) NOT NULL,
  `department_id` int(11) NOT NULL,
  `channel_id` int(11) NOT NULL,
  `message_id` bigint(20) NOT NULL,
  `mentioned_discord_id` varchar(32) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_chat_messages` (
  `id` bigint(20) NOT NULL,
  `department_id` int(11) NOT NULL,
  `channel_id` int(11) NOT NULL,
  `author_discord_id` varchar(32) NOT NULL,
  `author_name` varchar(80) NOT NULL,
  `author_avatar` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_chat_reads` (
  `department_id` int(11) NOT NULL,
  `channel_id` int(11) NOT NULL,
  `reader_discord_id` varchar(32) NOT NULL,
  `last_read_message_id` bigint(20) NOT NULL DEFAULT 0,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_citizens` (
  `id` int(11) NOT NULL,
  `identifier` varchar(64) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `address_type` varchar(100) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `zip` varchar(20) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `profession` varchar(50) DEFAULT NULL,
  `nationality` varchar(50) DEFAULT NULL,
  `photo_url` text DEFAULT NULL,
  `driver_license` tinyint(1) DEFAULT 0,
  `motorcycle_license` tinyint(1) DEFAULT 0,
  `truck_license` tinyint(1) DEFAULT 0,
  `boat_license` tinyint(1) DEFAULT 0,
  `pilot_license` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_citizen_owners` (
  `citizen_id` int(11) NOT NULL,
  `owner_identifier` varchar(120) NOT NULL,
  `owner_name` varchar(120) NOT NULL DEFAULT '',
  `created_at` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_complaints` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `complaint_number` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'ENREGISTREE',
  `priority` varchar(16) NOT NULL DEFAULT 'NORMALE',
  `type` varchar(64) NOT NULL DEFAULT 'PLAINTE',
  `filing_method` varchar(32) DEFAULT 'EN_PERSONNE',
  `jurisdiction` varchar(64) DEFAULT NULL,
  `complainant_citizen_id` int(11) DEFAULT NULL,
  `complainant_name` varchar(128) DEFAULT NULL,
  `complainant_phone` varchar(32) DEFAULT NULL,
  `complainant_email` varchar(128) DEFAULT NULL,
  `complainant_address` varchar(255) DEFAULT NULL,
  `complainant_is_anonymous` tinyint(1) NOT NULL DEFAULT 0,
  `represented_by` varchar(128) DEFAULT NULL,
  `accused_kind` varchar(16) NOT NULL DEFAULT 'UNKNOWN',
  `accused_citizen_id` int(11) DEFAULT NULL,
  `accused_name` varchar(128) DEFAULT NULL,
  `accused_company` varchar(128) DEFAULT NULL,
  `accused_address` varchar(255) DEFAULT NULL,
  `accused_contact` varchar(128) DEFAULT NULL,
  `incident_datetime` datetime DEFAULT NULL,
  `reported_datetime` datetime DEFAULT NULL,
  `location_label` varchar(255) DEFAULT NULL,
  `location_details` text DEFAULT NULL,
  `summary` varchar(255) DEFAULT NULL,
  `narrative` longtext DEFAULT NULL,
  `alleged_offences` text DEFAULT NULL,
  `damages` text DEFAULT NULL,
  `injuries` text DEFAULT NULL,
  `requested_action` text DEFAULT NULL,
  `confidentiality_level` varchar(16) NOT NULL DEFAULT 'STANDARD',
  `witnesses_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`witnesses_json`)),
  `officers_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`officers_json`)),
  `evidence_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`evidence_json`)),
  `attachments_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments_json`)),
  `timeline_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`timeline_json`)),
  `tags_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags_json`)),
  `assigned_agent_name` varchar(128) DEFAULT NULL,
  `internal_notes` longtext DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `closure_reason` text DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  `client_request_id` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_departments` (
  `id` int(11) NOT NULL,
  `category` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `logo` text DEFAULT NULL,
  `discord_role_id` varchar(50) NOT NULL,
  `discord_webhook_url` varchar(512) DEFAULT NULL,
  `bip_webhook_url` varchar(512) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_props` (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `label`         varchar(100) NOT NULL DEFAULT 'MDT',
  `model`         varchar(100) NOT NULL,
  `x`             float        NOT NULL,
  `y`             float        NOT NULL,
  `z`             float        NOT NULL,
  `rx`            float        NOT NULL DEFAULT 0,
  `ry`            float        NOT NULL DEFAULT 0,
  `rz`            float        NOT NULL DEFAULT 0,
  `department_id` int(11)      DEFAULT NULL,
  `created_by`             varchar(100) DEFAULT NULL,
  `placed_by_identifier`   varchar(100) DEFAULT NULL,
  `created_at`             bigint(20)   NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_dispatch_agents` (
  `identifier` varchar(64) NOT NULL,
  `department_id` int(11) NOT NULL,
  `agent_name` varchar(128) NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'on_duty',
  `unit_callsign` varchar(32) DEFAULT NULL,
  `discord_id` varchar(64) DEFAULT NULL,
  `updated_at` bigint(20) NOT NULL,
  UNIQUE KEY `uq_dispatch_agent` (`identifier`, `department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_dispatch_calls` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(128) NOT NULL,
  `subtitle` varchar(255) DEFAULT NULL,
  `location_label` varchar(255) DEFAULT NULL,
  `code` varchar(32) DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT 2,
  `x` double DEFAULT NULL,
  `y` double DEFAULT NULL,
  `z` double DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'PENDING',
  `assigned_callsign` varchar(32) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `web_notify` tinyint(1) NOT NULL DEFAULT 0,
  `web_assign_callsign` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_dispatch_units` (
  `callsign` varchar(32) NOT NULL,
  `department_id` int(11) NOT NULL,
  `status` varchar(64) NOT NULL DEFAULT 'En patrouille',
  `vehicles` varchar(255) DEFAULT NULL,
  `sector` varchar(100) DEFAULT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_dispatch_unit_members` (
  `callsign` varchar(32) NOT NULL,
  `department_id` int(11) NOT NULL,
  `identifier` varchar(64) NOT NULL,
  `agent_name` varchar(128) NOT NULL,
  `added_at` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_dispatch_board` (
  `department_id` int(11) NOT NULL,
  `dispatcher` varchar(128) NOT NULL DEFAULT '-',
  `co_dispatcher` varchar(128) NOT NULL DEFAULT '-',
  `supervisor` varchar(128) NOT NULL DEFAULT 'Non attribué',
  `watch_commander` varchar(128) NOT NULL DEFAULT '-',
  `radio` varchar(255) NOT NULL DEFAULT '',
  `alert` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_document_categories` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `icon` varchar(16) DEFAULT '?',
  `color` varchar(16) DEFAULT '#5b8cff',
  `permissions_json` longtext DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_document_links` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(160) NOT NULL,
  `url` text NOT NULL,
  `description` text DEFAULT NULL,
  `tags_json` longtext DEFAULT NULL,
  `icon` varchar(16) DEFAULT '?',
  `color` varchar(16) DEFAULT '#5b8cff',
  `access_level` varchar(32) DEFAULT 'department',
  `permissions_json` longtext DEFAULT NULL,
  `is_pinned` tinyint(1) DEFAULT 0,
  `open_in_new_tab` tinyint(1) DEFAULT 1,
  `created_by` varchar(120) DEFAULT NULL,
  `updated_by` varchar(120) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_doc_comments` (
  `id` int(11) NOT NULL,
  `doc_id` int(11) NOT NULL,
  `author` varchar(80) NOT NULL,
  `body` text NOT NULL,
  `anchor` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `resolved` tinyint(1) DEFAULT 0,
  `resolved_by` varchar(80) DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_doc_versions` (
  `id` int(11) NOT NULL,
  `doc_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `saved_by` varchar(80) NOT NULL,
  `saved_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_doj_ccw` (
  `id` int(11) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `ccw_number` varchar(32) NOT NULL,
  `status` varchar(24) NOT NULL DEFAULT 'ACTIVE',
  `weapon_type` varchar(120) DEFAULT NULL,
  `issued_at` date DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `issued_by` varchar(120) DEFAULT NULL,
  `jurisdiction` varchar(120) DEFAULT NULL,
  `restrictions` text DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `notes` longtext DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_doj_criminal_records` (
  `id` int(11) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `record_number` varchar(80) NOT NULL,
  `title` varchar(255) NOT NULL,
  `category` varchar(120) DEFAULT NULL,
  `severity` varchar(60) DEFAULT 'MISDEMEANOR',
  `status` varchar(60) DEFAULT 'ACTIVE',
  `charges_json` longtext DEFAULT NULL,
  `sentence_text` longtext DEFAULT NULL,
  `notes` longtext DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `mdt_doj_judgements` (
  `id` int(11) NOT NULL,
  `case_number` varchar(64) NOT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `citizen_name` varchar(255) NOT NULL,
  `judge_name` varchar(255) NOT NULL,
  `decree_number` varchar(128) NOT NULL,
  `court_name` varchar(255) NOT NULL,
  `case_title` text DEFAULT NULL,
  `procedure_opening` longtext DEFAULT NULL,
  `legal_interpretation` longtext DEFAULT NULL,
  `decree_context` longtext DEFAULT NULL,
  `decree_motivation` longtext DEFAULT NULL,
  `decree_will` longtext DEFAULT NULL,
  `decree_consequences` longtext DEFAULT NULL,
  `court_judgement` longtext DEFAULT NULL,
  `final_ruling` longtext DEFAULT NULL,
  `city` varchar(255) DEFAULT 'LOS SANTOS, SAN ANDREAS',
  `judgement_date` varchar(32) DEFAULT NULL,
  `signature_label` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_doj_warrants` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `citizen_name` varchar(255) NOT NULL,
  `warrant_number` varchar(80) NOT NULL,
  `case_number` varchar(80) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'ACTIVE',
  `warrant_type` varchar(80) NOT NULL DEFAULT 'ARREST',
  `court_name` varchar(255) DEFAULT 'United States District Court',
  `court_district` varchar(255) DEFAULT 'Central District of San Andreas',
  `issued_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `judge_name` varchar(255) DEFAULT NULL,
  `prosecutor_name` varchar(255) DEFAULT NULL,
  `signed_city` varchar(120) DEFAULT 'Los Santos',
  `signed_state` varchar(120) DEFAULT 'San Andreas',
  `priority` varchar(50) NOT NULL DEFAULT 'NORMAL',
  `bail_amount` int(11) NOT NULL DEFAULT 0,
  `danger_level` varchar(50) NOT NULL DEFAULT 'STANDARD',
  `reason` text DEFAULT NULL,
  `charges_text` longtext DEFAULT NULL,
  `narrative` longtext DEFAULT NULL,
  `internal_notes` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_drive_activity` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `item_id` int(11) DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `message` varchar(255) NOT NULL,
  `actor` varchar(80) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_drive_items` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `type` enum('folder','doc') NOT NULL,
  `name` varchar(255) NOT NULL,
  `content` longtext DEFAULT NULL,
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `trashed` tinyint(1) DEFAULT 0,
  `trashed_at` timestamp NULL DEFAULT NULL,
  `trashed_by` varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_drive_permissions` (
  `id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `scope` enum('private','department','roles','users') NOT NULL DEFAULT 'department',
  `role_ids` text DEFAULT NULL,
  `user_ids` text DEFAULT NULL,
  `can_view` tinyint(1) DEFAULT 1,
  `can_edit` tinyint(1) DEFAULT 1,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_drive_starred` (
  `id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `player_identifier` varchar(120) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_events` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `agent_identifier` varchar(120) NOT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `title` varchar(120) NOT NULL,
  `description` text DEFAULT NULL,
  `location` varchar(160) DEFAULT NULL,
  `category` varchar(50) DEFAULT 'service',
  `color` varchar(20) DEFAULT 'blue',
  `status` varchar(30) DEFAULT 'planned',
  `all_day` tinyint(1) NOT NULL DEFAULT 0,
  `starts_at` bigint(20) NOT NULL,
  `ends_at` bigint(20) NOT NULL,
  `remind_before` int(11) NOT NULL DEFAULT 0,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_fines` (
  `id` int(11) NOT NULL,
  `fine_number` varchar(32) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `citizen_name` varchar(120) DEFAULT NULL,
  `issued_at` varchar(32) DEFAULT NULL,
  `due_at` varchar(16) DEFAULT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'autre',
  `status` varchar(24) NOT NULL DEFAULT 'unpaid',
  `title` varchar(255) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `legal_reference` varchar(120) DEFAULT NULL,
  `total_amount` int(11) NOT NULL DEFAULT 0,
  `paid_amount` int(11) NOT NULL DEFAULT 0,
  `balance_amount` int(11) NOT NULL DEFAULT 0,
  `cad_ref` varchar(120) DEFAULT NULL,
  `bodycam_ref` varchar(120) DEFAULT NULL,
  `source_department_id` int(11) DEFAULT NULL,
  `source_department_label` varchar(120) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `internal_note` text DEFAULT NULL,
  `officer_name` varchar(120) DEFAULT NULL,
  `officer_grade` varchar(120) DEFAULT NULL,
  `items_json` longtext DEFAULT NULL,
  `tags_json` longtext DEFAULT NULL,
  `cancelled_at` bigint(20) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_forms` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `form_type` enum('FORM','EXAM') NOT NULL DEFAULT 'FORM',
  `status` enum('DRAFT','PUBLISHED','ARCHIVED') NOT NULL DEFAULT 'PUBLISHED',
  `time_limit_minutes` int(11) NOT NULL DEFAULT 0,
  `allowed_discord_ids` longtext DEFAULT NULL,
  `settings_json` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_form_questions` (
  `id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `label` text NOT NULL,
  `description` text DEFAULT NULL,
  `question_type` enum('short_text','long_text','single_choice','multiple_choice','dropdown','date') NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `options_json` longtext DEFAULT NULL,
  `correct_answers_json` longtext DEFAULT NULL,
  `points` decimal(10,2) NOT NULL DEFAULT 0.00,
  `position` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_form_submissions` (
  `id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `player_name` varchar(255) DEFAULT NULL,
  `player_identifier` varchar(255) DEFAULT NULL,
  `discord_id` varchar(64) DEFAULT NULL,
  `started_at` bigint(20) DEFAULT NULL,
  `submitted_at` bigint(20) DEFAULT NULL,
  `status` enum('IN_PROGRESS','SUBMITTED','AUTO_SUBMITTED','GRADED') NOT NULL DEFAULT 'SUBMITTED',
  `answers_json` longtext DEFAULT NULL,
  `score` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_score` decimal(10,2) NOT NULL DEFAULT 0.00,
  `correction_json` longtext DEFAULT NULL,
  `grader_name` varchar(255) DEFAULT NULL,
  `graded_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_fto_cases` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `case_number` varchar(50) DEFAULT NULL,
  `trainee_agent_id` int(11) DEFAULT NULL,
  `trainee_name` varchar(255) DEFAULT NULL,
  `trainee_badge` varchar(120) DEFAULT NULL,
  `trainee_rank` varchar(120) DEFAULT NULL,
  `trainee_avatar` text DEFAULT NULL,
  `primary_fto_agent_id` int(11) DEFAULT NULL,
  `primary_fto_name` varchar(255) DEFAULT NULL,
  `secondary_fto_agent_id` int(11) DEFAULT NULL,
  `secondary_fto_name` varchar(255) DEFAULT NULL,
  `status` varchar(60) DEFAULT 'ACTIVE',
  `phase` varchar(60) DEFAULT 'PHASE_1',
  `priority` varchar(60) DEFAULT 'NORMAL',
  `academy_class` varchar(120) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `target_end_date` datetime DEFAULT NULL,
  `hours_completed` decimal(10,2) DEFAULT 0.00,
  `shifts_completed` int(11) DEFAULT 0,
  `score_overall` decimal(10,2) DEFAULT 0.00,
  `strengths` mediumtext DEFAULT NULL,
  `improvement_goals` mediumtext DEFAULT NULL,
  `remedial_plan` mediumtext DEFAULT NULL,
  `command_notes` mediumtext DEFAULT NULL,
  `recommendation` mediumtext DEFAULT NULL,
  `internal_flags_json` longtext DEFAULT NULL,
  `custom_fields_json` longtext DEFAULT NULL,
  `skills_snapshot_json` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_fto_case_events` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `case_id` int(11) NOT NULL,
  `event_type` varchar(80) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` mediumtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_fto_evaluations` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `case_id` int(11) NOT NULL,
  `evaluation_number` varchar(50) DEFAULT NULL,
  `phase` varchar(60) DEFAULT NULL,
  `status` varchar(60) DEFAULT 'SATISFACTORY',
  `shift_type` varchar(120) DEFAULT NULL,
  `patrol_area` varchar(255) DEFAULT NULL,
  `shift_date` datetime DEFAULT NULL,
  `start_time` varchar(20) DEFAULT NULL,
  `end_time` varchar(20) DEFAULT NULL,
  `fto_agent_id` int(11) DEFAULT NULL,
  `fto_name` varchar(255) DEFAULT NULL,
  `partner_names_json` longtext DEFAULT NULL,
  `calls_handled` int(11) DEFAULT 0,
  `arrests_count` int(11) DEFAULT 0,
  `citations_count` int(11) DEFAULT 0,
  `reports_count` int(11) DEFAULT 0,
  `score_total` decimal(10,2) DEFAULT 0.00,
  `skills_scores_json` longtext DEFAULT NULL,
  `observations_positive` mediumtext DEFAULT NULL,
  `observations_negative` mediumtext DEFAULT NULL,
  `narrative` mediumtext DEFAULT NULL,
  `safety_issues` mediumtext DEFAULT NULL,
  `recommendation` mediumtext DEFAULT NULL,
  `attachments_json` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_gangs` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `aliases` varchar(255) DEFAULT NULL,
  `type` varchar(50) DEFAULT 'other',
  `status` varchar(30) DEFAULT 'active',
  `threat_level` varchar(30) DEFAULT 'medium',
  `colors` varchar(120) DEFAULT NULL,
  `photo_url` text DEFAULT NULL,
  `territory` varchar(255) DEFAULT NULL,
  `secondary_areas` varchar(255) DEFAULT NULL,
  `crimes` text DEFAULT NULL,
  `known_vehicles` text DEFAULT NULL,
  `known_weapons` text DEFAULT NULL,
  `leader_name` varchar(120) DEFAULT NULL,
  `last_activity_at` datetime DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `modus_operandi` text DEFAULT NULL,
  `relations_notes` text DEFAULT NULL,
  `internal_notes` text DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `members_json` longtext DEFAULT NULL,
  `nationality` varchar(120) DEFAULT NULL,
  `estimated_strength` int(11) DEFAULT 0,
  `finances_notes` text DEFAULT NULL,
  `communication_methods` text DEFAULT NULL,
  `signs_graffiti` text DEFAULT NULL,
  `intel_source` varchar(255) DEFAULT NULL,
  `intel_reliability` varchar(30) DEFAULT 'UNVERIFIED',
  `active_cases` varchar(255) DEFAULT NULL,
  `color` varchar(7) DEFAULT '#ef4444',
  `territory_json` longtext DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `updated_by` varchar(120) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_grades` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `label` varchar(100) NOT NULL,
  `level` int(11) NOT NULL,
  `discord_role_id` varchar(50) NOT NULL,
  `permissions` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_incident_reports` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `report_number` varchar(50) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `template_label` varchar(255) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'draft',
  `priority` varchar(50) DEFAULT 'normal',
  `incident_type` varchar(100) DEFAULT NULL,
  `incident_datetime` datetime DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `zone` varchar(255) DEFAULT NULL,
  `reporting_officer` varchar(255) DEFAULT NULL,
  `assisting_officers_json` longtext DEFAULT NULL,
  `involved_citizens_json` longtext DEFAULT NULL,
  `witnesses_json` longtext DEFAULT NULL,
  `vehicles_json` longtext DEFAULT NULL,
  `evidence_json` longtext DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `narrative` longtext DEFAULT NULL,
  `actions_taken` longtext DEFAULT NULL,
  `charges_recommended` text DEFAULT NULL,
  `internal_notes` longtext DEFAULT NULL,
  `rendered_html` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_investigations` (
  `id` int(11) NOT NULL,
  `case_number` varchar(32) NOT NULL,
  `primary_department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `investigation_type` varchar(50) NOT NULL DEFAULT 'OTHER',
  `priority` varchar(20) NOT NULL DEFAULT 'MEDIUM',
  `status` varchar(30) NOT NULL DEFAULT 'OPEN',
  `classification` varchar(30) NOT NULL DEFAULT 'STANDARD',
  `opened_at` bigint(20) DEFAULT NULL,
  `last_activity_at` bigint(20) DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `lead_investigator` varchar(255) DEFAULT NULL,
  `task_force` varchar(255) DEFAULT NULL,
  `targets` longtext DEFAULT NULL,
  `victims` longtext DEFAULT NULL,
  `locations` longtext DEFAULT NULL,
  `offences` longtext DEFAULT NULL,
  `facts` longtext DEFAULT NULL,
  `leads` longtext DEFAULT NULL,
  `evidence` longtext DEFAULT NULL,
  `evidence_photos` text DEFAULT NULL,
  `timeline` longtext DEFAULT NULL,
  `warrants_ref` text DEFAULT NULL,
  `related_cases` text DEFAULT NULL,
  `internal_notes` longtext DEFAULT NULL,
  `closure_summary` longtext DEFAULT NULL,
  `threat_level` varchar(20) NOT NULL DEFAULT 'MEDIUM',
  `modus_operandi` longtext DEFAULT NULL,
  `gang_org` varchar(255) DEFAULT NULL,
  `operations` longtext DEFAULT NULL,
  `informants` longtext DEFAULT NULL,
  `court_docs` varchar(255) DEFAULT NULL,
  `asset_seizures` text DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_investigation_departments` (
  `id` int(11) NOT NULL,
  `investigation_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_mailboxes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `department_id` int(11) NOT NULL,
  `owner_identifier` varchar(120) NOT NULL,
  `owner_discord_id` varchar(64) DEFAULT NULL,
  `agent_id` int(11) DEFAULT NULL,
  `local_part` varchar(120) NOT NULL,
  `domain_part` varchar(120) NOT NULL,
  `address` varchar(255) NOT NULL,
  `display_name` varchar(120) NOT NULL,
  `signature` text DEFAULT NULL,
  `color` varchar(24) NOT NULL DEFAULT '#5b8cff',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_mailbox_members` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `mailbox_id` bigint(20) UNSIGNED NOT NULL,
  `identifier` varchar(120) NOT NULL,
  `role` enum('owner','editor','viewer') NOT NULL DEFAULT 'owner',
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_mail_messages` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `department_id` int(11) NOT NULL,
  `thread_key` varchar(120) NOT NULL,
  `sender_mailbox_id` bigint(20) UNSIGNED DEFAULT NULL,
  `reply_to_message_id` bigint(20) UNSIGNED DEFAULT NULL,
  `from_address` varchar(255) NOT NULL,
  `from_name` varchar(120) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body_html` longtext DEFAULT NULL,
  `body_text` longtext DEFAULT NULL,
  `attachments_json` longtext DEFAULT NULL,
  `is_draft` tinyint(1) NOT NULL DEFAULT 0,
  `sent_at` bigint(20) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_mail_message_boxes` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `mailbox_id` bigint(20) UNSIGNED NOT NULL,
  `message_id` bigint(20) UNSIGNED NOT NULL,
  `box_type` enum('inbox','sent','drafts','trash','archive') NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `is_starred` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` bigint(20) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_mail_message_recipients` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `message_id` bigint(20) UNSIGNED NOT NULL,
  `mailbox_id` bigint(20) UNSIGNED DEFAULT NULL,
  `recipient_type` enum('to','cc','bcc') NOT NULL,
  `address` varchar(255) NOT NULL,
  `display_name` varchar(120) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_medical_admissions` (
  `id` int(11) NOT NULL,
  `admission_number` varchar(32) DEFAULT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `patient_name` varchar(120) NOT NULL,
  `patient_phone` varchar(64) DEFAULT NULL,
  `emergency_contact` varchar(255) DEFAULT NULL,
  `started_at` bigint(20) DEFAULT NULL,
  `discharged_at` bigint(20) DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'admitted',
  `priority` tinyint(4) NOT NULL DEFAULT 2,
  `ward` varchar(32) NOT NULL DEFAULT 'ER',
  `arrival_mode` varchar(32) NOT NULL DEFAULT 'EMS',
  `room` varchar(64) DEFAULT NULL,
  `bed` varchar(64) DEFAULT NULL,
  `chief_complaint` varchar(255) DEFAULT NULL,
  `doctor_name` varchar(120) DEFAULT NULL,
  `nurse_name` varchar(120) DEFAULT NULL,
  `temperature` varchar(32) DEFAULT NULL,
  `blood_pressure` varchar(32) DEFAULT NULL,
  `pulse` varchar(32) DEFAULT NULL,
  `spo2` varchar(32) DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `diagnosis` longtext DEFAULT NULL,
  `treatment` longtext DEFAULT NULL,
  `discharge_notes` longtext DEFAULT NULL,
  `notes` longtext DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `mdt_medical_patients` (
  `id` int(11) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `blood_type` varchar(10) DEFAULT NULL,
  `height_cm` int(11) DEFAULT NULL,
  `weight_kg` int(11) DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `current_medications` text DEFAULT NULL,
  `medical_history` text DEFAULT NULL,
  `known_conditions` text DEFAULT NULL,
  `risk_level` varchar(50) DEFAULT NULL,
  `profile_status` varchar(50) DEFAULT NULL,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_phone` varchar(50) DEFAULT NULL,
  `emergency_contact_relation` varchar(50) DEFAULT NULL,
  `insurance_provider` varchar(100) DEFAULT NULL,
  `primary_physician` varchar(100) DEFAULT NULL,
  `admission_status` varchar(50) DEFAULT NULL,
  `last_visit_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `updated_by` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_medical_reports` (
  `id` int(11) NOT NULL,
  `report_number` varchar(40) DEFAULT NULL,
  `patient_id` int(11) NOT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `occured_at` datetime DEFAULT NULL,
  `report_type` varchar(40) DEFAULT 'CONSULTATION',
  `priority` varchar(20) DEFAULT 'NORMAL',
  `status` varchar(20) DEFAULT 'OPEN',
  `outcome` varchar(30) DEFAULT 'TREATED',
  `location` varchar(255) DEFAULT NULL,
  `physician_name` varchar(120) DEFAULT NULL,
  `physician_badge` varchar(60) DEFAULT NULL,
  `admission_ref` varchar(80) DEFAULT NULL,
  `diagnosis` varchar(255) DEFAULT NULL,
  `severity` varchar(20) DEFAULT 'MINOR',
  `recovery_time` varchar(100) DEFAULT NULL,
  `blood_pressure` varchar(40) DEFAULT NULL,
  `heart_rate` varchar(40) DEFAULT NULL,
  `temperature` varchar(40) DEFAULT NULL,
  `oxygen_saturation` varchar(40) DEFAULT NULL,
  `pain_level` varchar(20) DEFAULT NULL,
  `hospitalized` tinyint(1) DEFAULT 0,
  `treatment` longtext DEFAULT NULL,
  `followup_notes` longtext DEFAULT NULL,
  `internal_notes` longtext DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_ois_reports` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `report_number` varchar(32) DEFAULT NULL,
  `status` varchar(24) DEFAULT 'draft',
  `occurred_at` datetime DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `officer_name` varchar(120) DEFAULT NULL,
  `officer_badge` varchar(60) DEFAULT NULL,
  `officer_rank` varchar(100) DEFAULT NULL,
  `unit_callsign` varchar(60) DEFAULT NULL,
  `weapon_type` varchar(60) DEFAULT NULL,
  `shots_fired` int(11) DEFAULT 0,
  `subject_name` varchar(120) DEFAULT NULL,
  `present_officers` text DEFAULT NULL,
  `initial_reason` text DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `narrative` longtext DEFAULT NULL,
  `evidence` text DEFAULT NULL,
  `injuries` text DEFAULT NULL,
  `aftermath` text DEFAULT NULL,
  `internal_notes` text DEFAULT NULL,
  `created_by` varchar(120) DEFAULT NULL,
  `creator_discord_id` varchar(50) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_operation_reports` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `report_number` varchar(40) NOT NULL,
  `template_id` int(11) DEFAULT NULL,
  `template_label` varchar(255) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `operation_type` varchar(120) DEFAULT NULL,
  `status` varchar(60) DEFAULT 'draft',
  `priority` varchar(60) DEFAULT 'normal',
  `operation_date` varchar(40) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `commander_name` varchar(255) DEFAULT NULL,
  `supervisor_name` varchar(255) DEFAULT NULL,
  `department_label` varchar(120) DEFAULT NULL,
  `units_involved_json` longtext DEFAULT NULL,
  `personnel_json` longtext DEFAULT NULL,
  `objectives_json` longtext DEFAULT NULL,
  `timeline_json` longtext DEFAULT NULL,
  `assets_json` longtext DEFAULT NULL,
  `outcome_json` longtext DEFAULT NULL,
  `summary` longtext DEFAULT NULL,
  `execution_details` longtext DEFAULT NULL,
  `intel_notes` longtext DEFAULT NULL,
  `use_of_force` longtext DEFAULT NULL,
  `evidence_notes` longtext DEFAULT NULL,
  `recommendations` longtext DEFAULT NULL,
  `internal_notes` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_by_avatar` longtext DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_photos` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `description` text DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `file_size` int(11) NOT NULL DEFAULT 0,
  `image_url` text NOT NULL,
  `thumb_url` text DEFAULT NULL,
  `fivemanage_id` varchar(255) DEFAULT NULL,
  `fivemanage_path` varchar(500) DEFAULT NULL,
  `uploaded_by` varchar(255) NOT NULL DEFAULT '',
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_recruitment_academy` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `start_date` varchar(50) DEFAULT NULL,
  `end_date` varchar(50) DEFAULT NULL,
  `location_label` varchar(255) DEFAULT NULL,
  `instructor_name` varchar(255) DEFAULT NULL,
  `capacity` int(11) NOT NULL DEFAULT 0,
  `status` varchar(50) NOT NULL DEFAULT 'draft',
  `modules` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_recruitment_candidates` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `form_id` int(11) DEFAULT NULL,
  `academy_session_id` int(11) DEFAULT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `discord_id` varchar(64) DEFAULT NULL,
  `full_name` varchar(255) NOT NULL,
  `date_of_birth` varchar(50) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `city` varchar(120) DEFAULT NULL,
  `motivation` text DEFAULT NULL,
  `experience_text` text DEFAULT NULL,
  `strengths` text DEFAULT NULL,
  `weaknesses` text DEFAULT NULL,
  `notes_internal` text DEFAULT NULL,
  `source_label` varchar(120) DEFAULT 'MDT',
  `status` varchar(50) NOT NULL DEFAULT 'new',
  `recruiter_name` varchar(255) DEFAULT NULL,
  `academy_status` varchar(50) NOT NULL DEFAULT 'not_scheduled',
  `final_decision` varchar(50) NOT NULL DEFAULT 'pending',
  `final_comment` text DEFAULT NULL,
  `answers` longtext DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_recruitment_candidate_exams` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `candidate_id` int(11) NOT NULL,
  `academy_id` int(11) DEFAULT NULL,
  `form_id` int(11) DEFAULT NULL,
  `exam_title` varchar(255) NOT NULL,
  `exam_type` varchar(50) NOT NULL DEFAULT 'academy_exam',
  `assigned_by` varchar(255) DEFAULT NULL,
  `assigned_at` bigint(20) NOT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'assigned',
  `score` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_score` decimal(10,2) NOT NULL DEFAULT 100.00,
  `grader_name` varchar(255) DEFAULT NULL,
  `grader_comment` text DEFAULT NULL,
  `answers` longtext DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_recruitment_forms` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `form_type` varchar(50) NOT NULL DEFAULT 'application',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `pass_score` int(11) NOT NULL DEFAULT 70,
  `duration_minutes` int(11) NOT NULL DEFAULT 0,
  `questions` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_routesheets` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `state_json` longtext DEFAULT NULL,
  `background_image` longtext DEFAULT NULL,
  `revision` int(11) NOT NULL DEFAULT 0,
  `created_by` varchar(120) DEFAULT NULL,
  `updated_by` varchar(120) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  `archived` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_routesheet_ops` (
  `id` bigint(20) NOT NULL,
  `board_id` int(11) NOT NULL,
  `revision` int(11) NOT NULL,
  `author` varchar(120) DEFAULT NULL,
  `op_type` varchar(50) NOT NULL,
  `payload` longtext NOT NULL,
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_seizures` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `seizure_number` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'UNDER_SEAL',
  `category` varchar(32) NOT NULL DEFAULT 'OTHER',
  `estimated_value` decimal(14,2) NOT NULL DEFAULT 0.00,
  `title` varchar(255) NOT NULL,
  `summary` text DEFAULT NULL,
  `legal_basis` text DEFAULT NULL,
  `related_citizen_id` int(11) DEFAULT NULL,
  `related_citizen_name` varchar(255) DEFAULT NULL,
  `related_vehicle_plate` varchar(64) DEFAULT NULL,
  `related_case_number` varchar(64) DEFAULT NULL,
  `location_label` varchar(255) DEFAULT NULL,
  `storage_location` varchar(255) DEFAULT NULL,
  `seized_at` datetime DEFAULT NULL,
  `officer_name` varchar(255) DEFAULT NULL,
  `supervisor_name` varchar(255) DEFAULT NULL,
  `image_data` longtext DEFAULT NULL,
  `items_json` longtext DEFAULT NULL,
  `custody_json` longtext DEFAULT NULL,
  `notes` longtext DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_service_sessions` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `identifier` varchar(64) NOT NULL,
  `discord_id` varchar(32) DEFAULT NULL,
  `agent_name` varchar(128) NOT NULL,
  `started_at` bigint(20) NOT NULL,
  `ended_at` bigint(20) DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_settings` (
  `id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `value` text DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `image` varchar(2048) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_statements` (
  `id` int(11) NOT NULL,
  `statement_number` varchar(50) DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `citizen_name` varchar(255) DEFAULT NULL,
  `type` varchar(50) DEFAULT 'WITNESS',
  `location` varchar(255) DEFAULT NULL,
  `officer_name` varchar(255) DEFAULT NULL,
  `officer_badge` varchar(100) DEFAULT NULL,
  `case_ref` varchar(100) DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `rights_read` tinyint(1) DEFAULT 0,
  `signature` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_stp_cases` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `case_number` varchar(50) DEFAULT NULL,
  `trainee_agent_id` int(11) DEFAULT NULL,
  `trainee_name` varchar(255) DEFAULT NULL,
  `trainee_badge` varchar(120) DEFAULT NULL,
  `trainee_rank` varchar(120) DEFAULT NULL,
  `trainee_avatar` text DEFAULT NULL,
  `primary_fto_agent_id` int(11) DEFAULT NULL,
  `primary_fto_name` varchar(255) DEFAULT NULL,
  `secondary_fto_agent_id` int(11) DEFAULT NULL,
  `secondary_fto_name` varchar(255) DEFAULT NULL,
  `status` varchar(60) DEFAULT 'ACTIVE',
  `phase` varchar(60) DEFAULT 'PHASE_1',
  `priority` varchar(60) DEFAULT 'NORMAL',
  `academy_class` varchar(120) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `target_end_date` datetime DEFAULT NULL,
  `hours_completed` decimal(10,2) DEFAULT 0.00,
  `shifts_completed` int(11) DEFAULT 0,
  `score_overall` decimal(10,2) DEFAULT 0.00,
  `strengths` mediumtext DEFAULT NULL,
  `improvement_goals` mediumtext DEFAULT NULL,
  `remedial_plan` mediumtext DEFAULT NULL,
  `command_notes` mediumtext DEFAULT NULL,
  `recommendation` mediumtext DEFAULT NULL,
  `internal_flags_json` longtext DEFAULT NULL,
  `custom_fields_json` longtext DEFAULT NULL,
  `skills_snapshot_json` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL,
  `updated_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_stp_case_events` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `case_id` int(11) NOT NULL,
  `event_type` varchar(80) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` mediumtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_stp_evaluations` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `case_id` int(11) NOT NULL,
  `evaluation_number` varchar(50) DEFAULT NULL,
  `phase` varchar(60) DEFAULT NULL,
  `status` varchar(60) DEFAULT 'SATISFACTORY',
  `shift_type` varchar(120) DEFAULT NULL,
  `patrol_area` varchar(255) DEFAULT NULL,
  `shift_date` datetime DEFAULT NULL,
  `start_time` varchar(20) DEFAULT NULL,
  `end_time` varchar(20) DEFAULT NULL,
  `fto_agent_id` int(11) DEFAULT NULL,
  `fto_name` varchar(255) DEFAULT NULL,
  `partner_names_json` longtext DEFAULT NULL,
  `calls_handled` int(11) DEFAULT 0,
  `arrests_count` int(11) DEFAULT 0,
  `citations_count` int(11) DEFAULT 0,
  `reports_count` int(11) DEFAULT 0,
  `score_total` decimal(10,2) DEFAULT 0.00,
  `skills_scores_json` longtext DEFAULT NULL,
  `observations_positive` mediumtext DEFAULT NULL,
  `observations_negative` mediumtext DEFAULT NULL,
  `narrative` mediumtext DEFAULT NULL,
  `safety_issues` mediumtext DEFAULT NULL,
  `recommendation` mediumtext DEFAULT NULL,
  `attachments_json` longtext DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `created_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_summons` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `target_type` varchar(20) NOT NULL,
  `target_id` int(11) NOT NULL,
  `target_name` varchar(120) DEFAULT '',
  `dept_label` varchar(60) DEFAULT '',
  `dept_logo` text DEFAULT NULL,
  `issuer_type` varchar(20) DEFAULT 'department',
  `issuer_label` varchar(80) DEFAULT '',
  `issuer_logo` text DEFAULT NULL,
  `template_id` int(11) DEFAULT 0,
  `template_label` varchar(80) DEFAULT '',
  `date` varchar(20) DEFAULT '',
  `time` varchar(20) DEFAULT '',
  `location` varchar(160) DEFAULT '',
  `reason` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `created_by` varchar(80) DEFAULT '',
  `created_at` bigint(20) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_traffic_tickets` (
  `id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `ticket_number` varchar(32) NOT NULL,
  `citizen_id` int(11) NOT NULL,
  `citizen_name` varchar(128) DEFAULT NULL,
  `issued_at` datetime DEFAULT NULL,
  `location` varchar(128) NOT NULL,
  `type` varchar(32) NOT NULL,
  `code` varchar(32) DEFAULT NULL,
  `plate` varchar(16) DEFAULT NULL,
  `vehicle_desc` varchar(128) DEFAULT NULL,
  `fine_amount` int(11) NOT NULL DEFAULT 0,
  `points` int(11) NOT NULL DEFAULT 0,
  `status` varchar(16) NOT NULL DEFAULT 'unpaid',
  `court_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `bodycam_ref` varchar(64) DEFAULT NULL,
  `cad_ref` varchar(64) DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `speed_limit` int(11) DEFAULT NULL,
  `speed_method` varchar(16) DEFAULT NULL,
  `officer_name` varchar(64) DEFAULT NULL,
  `officer_grade` varchar(64) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_vehicles` (
  `id` int(11) NOT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `plate` varchar(16) NOT NULL,
  `model` varchar(64) DEFAULT NULL,
  `color` varchar(32) DEFAULT NULL,
  `class` varchar(32) DEFAULT NULL,
  `vin` varchar(32) DEFAULT NULL,
  `insurance` varchar(16) DEFAULT 'UNKNOWN',
  `registration` varchar(16) DEFAULT 'UNKNOWN',
  `status` varchar(16) DEFAULT 'ACTIVE',
  `flagged` tinyint(1) DEFAULT 0,
  `notes` text DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_vehicle_events` (
  `id` int(11) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `type` varchar(24) NOT NULL,
  `title` varchar(64) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `author` varchar(64) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `mdt_absences`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_absences_department` (`department_id`),
  ADD KEY `idx_absences_status` (`status`),
  ADD KEY `idx_absences_dates` (`start_at`,`end_at`);

ALTER TABLE `mdt_agents`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `discord_id` (`discord_id`,`department_id`),
  ADD KEY `idx_mdt_agents_department_discord` (`department_id`,`discord_id`);

ALTER TABLE `mdt_arrest_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_arrest_reports_department` (`department_id`),
  ADD KEY `idx_mdt_arrest_reports_responsible` (`responsible_id`);

ALTER TABLE `mdt_bips`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_bips_department_id` (`department_id`),
  ADD KEY `idx_mdt_bips_status` (`status`);

ALTER TABLE `mdt_bracelets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_bracelet_number` (`bracelet_number`),
  ADD KEY `idx_bracelet_citizen_id` (`citizen_id`),
  ADD KEY `idx_bracelet_status` (`status`),
  ADD KEY `idx_bracelet_department_id` (`department_id`);

ALTER TABLE `mdt_chat_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`);

ALTER TABLE `mdt_chat_channels`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `category_id` (`category_id`);

ALTER TABLE `mdt_chat_channel_acl`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_grade` (`channel_id`,`allow_grade_role_id`),
  ADD UNIQUE KEY `uniq_agent` (`channel_id`,`allow_agent_discord_id`),
  ADD KEY `channel_id` (`channel_id`);

ALTER TABLE `mdt_chat_mentions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mentioned_discord_id` (`mentioned_discord_id`),
  ADD KEY `message_id` (`message_id`);

ALTER TABLE `mdt_chat_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `channel_id` (`channel_id`),
  ADD KEY `author_discord_id` (`author_discord_id`);

ALTER TABLE `mdt_chat_reads`
  ADD PRIMARY KEY (`department_id`,`channel_id`,`reader_discord_id`);

ALTER TABLE `mdt_citizens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_mdt_citizens_identifier` (`identifier`);

ALTER TABLE `mdt_citizen_owners`
  ADD PRIMARY KEY (`citizen_id`),
  ADD KEY `idx_mdt_citizen_owner_identifier` (`owner_identifier`);

ALTER TABLE `mdt_complaints`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_client_request` (`client_request_id`),
  ADD KEY `idx_mdt_complaints_department` (`department_id`),
  ADD KEY `idx_mdt_complaints_status` (`status`),
  ADD KEY `idx_mdt_complaints_complainant` (`complainant_citizen_id`),
  ADD KEY `idx_mdt_complaints_accused` (`accused_citizen_id`);

ALTER TABLE `mdt_departments`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mdt_dispatch_agents`
  ADD PRIMARY KEY (`identifier`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `status` (`status`),
  ADD KEY `unit_callsign` (`unit_callsign`),
  ADD KEY `idx_mdt_dispatch_agents_discord_id` (`discord_id`);

ALTER TABLE `mdt_dispatch_calls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `status` (`status`),
  ADD KEY `assigned_callsign` (`assigned_callsign`),
  ADD KEY `created_at` (`created_at`);

ALTER TABLE `mdt_dispatch_units`
  ADD PRIMARY KEY (`callsign`),
  ADD KEY `department_id` (`department_id`);

ALTER TABLE `mdt_dispatch_unit_members`
  ADD PRIMARY KEY (`callsign`,`identifier`),
  ADD KEY `identifier` (`identifier`);

ALTER TABLE `mdt_document_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_document_categories_department` (`department_id`);

ALTER TABLE `mdt_document_links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_document_links_department` (`department_id`),
  ADD KEY `idx_mdt_document_links_category` (`category_id`);

ALTER TABLE `mdt_doc_comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_comments_doc` (`doc_id`,`resolved`);

ALTER TABLE `mdt_doc_versions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_versions_doc` (`doc_id`);

ALTER TABLE `mdt_doj_ccw`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_mdt_doj_ccw_number` (`ccw_number`),
  ADD KEY `idx_mdt_doj_ccw_citizen` (`citizen_id`);

ALTER TABLE `mdt_doj_criminal_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_doj_criminal_records_citizen_id` (`citizen_id`),
  ADD KEY `idx_mdt_doj_criminal_records_record_number` (`record_number`);

ALTER TABLE `mdt_doj_judgements`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mdt_doj_warrants`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_warrant_number` (`warrant_number`),
  ADD KEY `idx_department` (`department_id`),
  ADD KEY `idx_citizen` (`citizen_id`),
  ADD KEY `idx_status` (`status`);

ALTER TABLE `mdt_drive_activity`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_activity_dept` (`department_id`,`created_at`);

ALTER TABLE `mdt_drive_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_drive_dept_parent` (`department_id`,`parent_id`),
  ADD KEY `idx_drive_dept_trashed` (`department_id`,`trashed`),
  ADD KEY `idx_drive_dept_type` (`department_id`,`type`);

ALTER TABLE `mdt_drive_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_perm_item` (`item_id`);

ALTER TABLE `mdt_drive_starred`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_star` (`item_id`,`player_identifier`);

ALTER TABLE `mdt_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_events_agent` (`agent_identifier`),
  ADD KEY `idx_mdt_events_department` (`department_id`),
  ADD KEY `idx_mdt_events_range` (`starts_at`,`ends_at`);

ALTER TABLE `mdt_fines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_mdt_fines_number` (`fine_number`),
  ADD KEY `idx_mdt_fines_citizen` (`citizen_id`),
  ADD KEY `idx_mdt_fines_status` (`status`),
  ADD KEY `idx_mdt_fines_created` (`created_at`);

ALTER TABLE `mdt_forms`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mdt_form_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_form_questions_form_id` (`form_id`);

ALTER TABLE `mdt_form_submissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_form_submissions_form_id` (`form_id`),
  ADD KEY `idx_form_submissions_discord_id` (`discord_id`);

ALTER TABLE `mdt_fto_cases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_fto_cases_department` (`department_id`),
  ADD KEY `idx_mdt_fto_cases_trainee` (`trainee_agent_id`);

ALTER TABLE `mdt_fto_case_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_fto_case_events_case` (`case_id`),
  ADD KEY `idx_mdt_fto_case_events_department` (`department_id`);

ALTER TABLE `mdt_fto_evaluations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_fto_evaluations_case` (`case_id`),
  ADD KEY `idx_mdt_fto_evaluations_department` (`department_id`);

ALTER TABLE `mdt_gangs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_gangs_department` (`department_id`),
  ADD KEY `idx_mdt_gangs_status` (`status`),
  ADD KEY `idx_mdt_gangs_threat` (`threat_level`);

ALTER TABLE `mdt_grades`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`);

ALTER TABLE `mdt_incident_reports`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mdt_investigations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_case_number` (`case_number`),
  ADD KEY `idx_primary_department` (`primary_department_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_priority` (`priority`);

ALTER TABLE `mdt_investigation_departments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_investigation_department` (`investigation_id`,`department_id`),
  ADD KEY `idx_department_id` (`department_id`);

ALTER TABLE `mdt_mailboxes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_mailbox_address` (`address`),
  ADD KEY `idx_mailboxes_department` (`department_id`),
  ADD KEY `idx_mailboxes_owner` (`owner_identifier`,`department_id`);

ALTER TABLE `mdt_mailbox_members`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_mailbox_member` (`mailbox_id`,`identifier`),
  ADD KEY `idx_mailbox_members_identifier` (`identifier`);

ALTER TABLE `mdt_mail_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mail_messages_department` (`department_id`),
  ADD KEY `idx_mail_messages_thread` (`thread_key`),
  ADD KEY `idx_mail_messages_sender` (`sender_mailbox_id`);

ALTER TABLE `mdt_mail_message_boxes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_mail_copy` (`mailbox_id`,`message_id`,`box_type`),
  ADD KEY `idx_mail_boxes_mailbox` (`mailbox_id`,`box_type`),
  ADD KEY `idx_mail_boxes_message` (`message_id`);

ALTER TABLE `mdt_mail_message_recipients`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mail_recipients_message` (`message_id`),
  ADD KEY `idx_mail_recipients_mailbox` (`mailbox_id`);

ALTER TABLE `mdt_medical_admissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_admission_number` (`admission_number`),
  ADD KEY `idx_citizen_id` (`citizen_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_priority` (`priority`),
  ADD KEY `idx_started_at` (`started_at`);

ALTER TABLE `mdt_medical_patients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_patient` (`citizen_id`);

ALTER TABLE `mdt_medical_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_medical_reports_patient_id` (`patient_id`),
  ADD KEY `idx_medical_reports_citizen_id` (`citizen_id`),
  ADD KEY `idx_medical_reports_report_number` (`report_number`);

ALTER TABLE `mdt_ois_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_ois_department` (`department_id`),
  ADD KEY `idx_mdt_ois_status` (`status`);

ALTER TABLE `mdt_operation_reports`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_mdt_operation_report_number` (`report_number`),
  ADD KEY `idx_mdt_operation_reports_department` (`department_id`),
  ADD KEY `idx_mdt_operation_reports_created_at` (`created_at`);

ALTER TABLE `mdt_photos`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_department_id` (`department_id`);

ALTER TABLE `mdt_recruitment_academy`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_recruitment_academy_department` (`department_id`),
  ADD KEY `idx_recruitment_academy_status` (`status`);

ALTER TABLE `mdt_recruitment_candidates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_recruitment_candidates_department` (`department_id`),
  ADD KEY `idx_recruitment_candidates_status` (`status`),
  ADD KEY `idx_recruitment_candidates_form` (`form_id`);

ALTER TABLE `mdt_recruitment_candidate_exams`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_recruitment_candidate_exams_department` (`department_id`),
  ADD KEY `idx_recruitment_candidate_exams_candidate` (`candidate_id`);

ALTER TABLE `mdt_recruitment_forms`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_recruitment_forms_department` (`department_id`),
  ADD KEY `idx_recruitment_forms_type` (`form_type`);

ALTER TABLE `mdt_routesheets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_routesheets_department` (`department_id`),
  ADD KEY `idx_routesheets_archived` (`archived`);

ALTER TABLE `mdt_routesheet_ops`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_routesheet_ops_board` (`board_id`),
  ADD KEY `idx_routesheet_ops_revision` (`revision`);

ALTER TABLE `mdt_seizures`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_mdt_seizure_number` (`seizure_number`),
  ADD KEY `idx_mdt_seizures_department` (`department_id`),
  ADD KEY `idx_mdt_seizures_citizen` (`related_citizen_id`);

ALTER TABLE `mdt_service_sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `identifier` (`identifier`),
  ADD KEY `started_at` (`started_at`);

ALTER TABLE `mdt_settings`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mdt_statements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_statements_department` (`department_id`),
  ADD KEY `idx_statements_citizen` (`citizen_id`);

ALTER TABLE `mdt_stp_cases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_fto_cases_department` (`department_id`),
  ADD KEY `idx_mdt_fto_cases_trainee` (`trainee_agent_id`);

ALTER TABLE `mdt_stp_case_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_fto_case_events_case` (`case_id`),
  ADD KEY `idx_mdt_fto_case_events_department` (`department_id`);

ALTER TABLE `mdt_stp_evaluations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_mdt_fto_evaluations_case` (`case_id`),
  ADD KEY `idx_mdt_fto_evaluations_department` (`department_id`);

ALTER TABLE `mdt_summons`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `mdt_traffic_tickets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_ticket_number` (`ticket_number`),
  ADD KEY `idx_citizen_id` (`citizen_id`),
  ADD KEY `idx_department_id` (`department_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_issued_at` (`issued_at`),
  ADD KEY `idx_plate` (`plate`);

ALTER TABLE `mdt_vehicles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_citizen` (`citizen_id`),
  ADD KEY `idx_plate` (`plate`),
  ADD KEY `idx_status` (`status`);

ALTER TABLE `mdt_vehicle_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_vehicle` (`vehicle_id`);

ALTER TABLE `mdt_absences`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_agents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10284;

ALTER TABLE `mdt_arrest_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_bips`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_bracelets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_chat_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_chat_channels`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_chat_channel_acl`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_chat_mentions`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_chat_messages`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_citizens`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_complaints`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

ALTER TABLE `mdt_dispatch_calls`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_document_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_document_links`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_doc_comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_doc_versions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_doj_ccw`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_doj_criminal_records`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_doj_judgements`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_doj_warrants`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_drive_activity`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_drive_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_drive_permissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_drive_starred`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_fines`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_forms`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_form_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_form_submissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_fto_cases`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_fto_case_events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_fto_evaluations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_gangs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_grades`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_incident_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_investigations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `mdt_investigation_departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `mdt_mailboxes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_mailbox_members`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_mail_messages`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_mail_message_boxes`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_mail_message_recipients`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_medical_admissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_medical_patients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_medical_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_ois_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_operation_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_photos`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_recruitment_academy`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_recruitment_candidates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_recruitment_candidate_exams`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_recruitment_forms`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_routesheets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_routesheet_ops`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_seizures`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_service_sessions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=52;

ALTER TABLE `mdt_statements`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_stp_cases`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_stp_case_events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_stp_evaluations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_summons`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_traffic_tickets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_vehicles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_vehicle_events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `mdt_grades`
  ADD CONSTRAINT `mdt_grades_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `mdt_departments` (`id`) ON DELETE CASCADE;

ALTER TABLE `mdt_investigation_departments`
  ADD CONSTRAINT `fk_mdt_inv_dept_investigation` FOREIGN KEY (`investigation_id`) REFERENCES `mdt_investigations` (`id`) ON DELETE CASCADE;
COMMIT;

CREATE TABLE `mdt_annuaire` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `discord_id` varchar(50) NOT NULL,
  `full_name` varchar(150) DEFAULT NULL,
  `firstname` varchar(100) DEFAULT NULL,
  `lastname` varchar(100) DEFAULT NULL,
  `phone_number` varchar(50) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `custom_avatar` varchar(255) DEFAULT NULL,
  `grade` varchar(100) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dept_discord` (`department_id`, `discord_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mdt_wiretaps (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  phone_number  VARCHAR(30) NOT NULL,
  agent_name    VARCHAR(100),
  department_id INT,
  reason        VARCHAR(255),
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status        VARCHAR(20) DEFAULT 'ACTIVE',
  INDEX idx_wt_dept (department_id)
);

CREATE TABLE IF NOT EXISTS mdt_wiretap_calls (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  wiretap_id     INT NOT NULL,
  caller_number  VARCHAR(30),
  callee_number  VARCHAR(30),
  started_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ended_at       TIMESTAMP NULL,
  duration       INT DEFAULT 0,
  status         VARCHAR(20) DEFAULT 'ONGOING',
  INDEX idx_wtc_wiretap (wiretap_id)
);

CREATE TABLE IF NOT EXISTS mdt_spreadsheets (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  department_id    INT NOT NULL,
  title            VARCHAR(100) NOT NULL DEFAULT 'Nouveau tableau',
  sheets_json      LONGTEXT NOT NULL,
  permissions_json TEXT DEFAULT NULL,
  created_by       VARCHAR(100),
  updated_by       VARCHAR(100),
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_ss_dept (department_id)
);

CREATE TABLE IF NOT EXISTS `mdt_patrol_detachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL DEFAULT 'Patrol Detachment',
  `color` varchar(20) DEFAULT 'blue',
  `created_by` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_pd_department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_patrol_sections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `detachment_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT 'Section',
  `section_order` int(11) NOT NULL DEFAULT 0,
  `grade_ids` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ps_detachment` (`detachment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_patrol_agent_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agent_id` int(11) NOT NULL,
  `detachment_id` int(11) NOT NULL,
  `remark_wc` text DEFAULT NULL,
  `remark_supervisor` text DEFAULT NULL,
  `last_rdv_date` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_agent_det` (`agent_id`,`detachment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_patrol_rdv_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agent_id` int(11) NOT NULL,
  `detachment_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `supervisor_name` varchar(150) DEFAULT NULL,
  `report_date` date DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_prdv_agent` (`agent_id`),
  KEY `idx_prdv_detachment` (`detachment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mdt_printer_state` (
  `prop_id`   int(11) NOT NULL,
  `ink_black` int(11) NOT NULL DEFAULT 100,
  `ink_color` int(11) NOT NULL DEFAULT 100,
  PRIMARY KEY (`prop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `mdt_license_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner` varchar(60) NOT NULL,
  `license_type` varchar(60) NOT NULL,
  `points_lost` int(11) NOT NULL DEFAULT 0,
  `max_points` int(11) NOT NULL DEFAULT 12,
  `status` enum('active','suspended','revoked') NOT NULL DEFAULT 'active',
  `suspended_until` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `updated_by` varchar(100) DEFAULT NULL,
  `updated_at` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_license_record` (`owner`, `license_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELIMITER $$

CREATE TRIGGER `trg_esx_users_to_mdt_citizens_insert`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `mdt_citizens` (identifier, firstname, lastname, birthdate, gender, phone)
    VALUES (
        NEW.identifier,
        NEW.firstname,
        NEW.lastname,
        STR_TO_DATE(NEW.dateofbirth, '%d/%m/%Y'),
        CASE WHEN NEW.sex = 0 THEN 'Homme' WHEN NEW.sex = 1 THEN 'Femme' ELSE 'Inconnu' END,
        NEW.phone_number
    )
    ON DUPLICATE KEY UPDATE
        firstname = COALESCE(NULLIF(NEW.firstname, ''), firstname),
        lastname  = COALESCE(NULLIF(NEW.lastname,  ''), lastname),
        birthdate = COALESCE(STR_TO_DATE(NEW.dateofbirth, '%d/%m/%Y'), birthdate),
        gender    = CASE WHEN NEW.sex = 0 THEN 'Homme' WHEN NEW.sex = 1 THEN 'Femme' ELSE gender END,
        phone     = COALESCE(NULLIF(NEW.phone_number, ''), phone);
END$$

CREATE TRIGGER `trg_esx_users_to_mdt_citizens_update`
AFTER UPDATE ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `mdt_citizens` (identifier, firstname, lastname, birthdate, gender, phone)
    VALUES (
        NEW.identifier,
        NEW.firstname,
        NEW.lastname,
        STR_TO_DATE(NEW.dateofbirth, '%d/%m/%Y'),
        CASE WHEN NEW.sex = 0 THEN 'Homme' WHEN NEW.sex = 1 THEN 'Femme' ELSE 'Inconnu' END,
        NEW.phone_number
    )
    ON DUPLICATE KEY UPDATE
        firstname = COALESCE(NULLIF(NEW.firstname, ''), firstname),
        lastname  = COALESCE(NULLIF(NEW.lastname,  ''), lastname),
        birthdate = COALESCE(STR_TO_DATE(NEW.dateofbirth, '%d/%m/%Y'), birthdate),
        gender    = CASE WHEN NEW.sex = 0 THEN 'Homme' WHEN NEW.sex = 1 THEN 'Femme' ELSE gender END,
        phone     = COALESCE(NULLIF(NEW.phone_number, ''), phone);
END$$

DELIMITER ;

SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT ;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS ;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION ;

ALTER TABLE mdt_traffic_tickets ADD COLUMN IF NOT EXISTS items JSON DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `mdt_print_logs` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ref`              VARCHAR(20)  NOT NULL,
    `doc_type`         VARCHAR(50)  NOT NULL DEFAULT '',
    `doc_label`        VARCHAR(200) NOT NULL DEFAULT '',
    `printed_by`       VARCHAR(100) NOT NULL DEFAULT '',
    `color`            TINYINT(1)   NOT NULL DEFAULT 0,
    `printer_id`       INT UNSIGNED,
    `department_id`    INT UNSIGNED,
    `department_label` VARCHAR(100) NOT NULL DEFAULT '',
    `created_at`       BIGINT       NOT NULL DEFAULT 0,
    INDEX(`department_id`),
    INDEX(`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `mdt_deaths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `death_number` varchar(32) DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `citizen_name` varchar(200) NOT NULL,
  `date_of_death` datetime NOT NULL,
  `cause` enum('naturelle','accidentelle','violente','suspecte','inconnue') NOT NULL DEFAULT 'inconnue',
  `location` varchar(255) DEFAULT NULL,
  `responsible_doctor` varchar(150) DEFAULT NULL,
  `notes` mediumtext DEFAULT NULL,
  `linked_report` varchar(64) DEFAULT NULL,
  `created_by` varchar(150) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_deaths_dept` (`department_id`),
  KEY `idx_deaths_date` (`date_of_death`),
  KEY `idx_deaths_cause` (`cause`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_shifts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `agent_name` varchar(150) NOT NULL,
  `agent_discord_id` varchar(64) DEFAULT NULL,
  `shift_date` date NOT NULL,
  `shift_type` enum('day','night','h24') NOT NULL DEFAULT 'day',
  `role` enum('urgentiste','chirurgien','infirmier','medecin_legiste','autre') NOT NULL DEFAULT 'urgentiste',
  `status` enum('prevu','confirme','annule') NOT NULL DEFAULT 'prevu',
  `notes` text DEFAULT NULL,
  `created_by` varchar(150) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shifts_dept` (`department_id`),
  KEY `idx_shifts_date` (`shift_date`),
  KEY `idx_shifts_agent_discord` (`agent_discord_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_beds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `bed_number` varchar(20) NOT NULL,
  `service` enum('Urgences','Reanimation','Bloc_operatoire','Psychiatrie') NOT NULL DEFAULT 'Urgences',
  `status` enum('disponible','occupe','nettoyage','maintenance') NOT NULL DEFAULT 'disponible',
  `patient_name` varchar(200) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `updated_by` varchar(150) DEFAULT NULL,
  `updated_at` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_beds_dept` (`department_id`),
  KEY `idx_beds_service` (`service`),
  KEY `idx_beds_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_psychiatric_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `record_number` varchar(32) DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  `citizen_id` int(11) DEFAULT NULL,
  `citizen_name` varchar(200) NOT NULL,
  `diagnosis` varchar(500) DEFAULT NULL,
  `treatment` mediumtext DEFAULT NULL,
  `risk_level` enum('stable','modere','eleve','critique') NOT NULL DEFAULT 'stable',
  `sessions_json` longtext DEFAULT NULL,
  `notes` mediumtext DEFAULT NULL,
  `created_by` varchar(150) DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_psy_dept` (`department_id`),
  KEY `idx_psy_risk` (`risk_level`),
  KEY `idx_psy_citizen` (`citizen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_doj_resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` enum('judge','room','clerk','bgp') NOT NULL DEFAULT 'judge',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_doj_res_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_doj_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resource_id` int(11) NOT NULL,
  `week_start` date NOT NULL,
  `day_of_week` tinyint(4) NOT NULL DEFAULT 0,
  `slot_start` varchar(5) NOT NULL,
  `slot_end` varchar(5) DEFAULT NULL,
  `event_type` enum('unavailable','hearing','telework','other_rdv','oath') NOT NULL DEFAULT 'hearing',
  `title` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_doj_ev_resource` (`resource_id`),
  KEY `idx_doj_ev_week` (`week_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_doj_bgp_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_doj_bgp_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `present` tinyint(1) NOT NULL DEFAULT 0,
  `recorded_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_member_date` (`member_id`,`date`),
  KEY `idx_doj_att_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_doj_resource_settings` (
  `resource_id` int(11) NOT NULL,
  `grid_start` varchar(5) NOT NULL DEFAULT '21:00',
  `grid_end` varchar(5) NOT NULL DEFAULT '01:00',
  `slot_minutes` int(11) NOT NULL DEFAULT 15,
  `blocked_slots` varchar(500) NOT NULL DEFAULT '21:00,00:15,00:30,00:45,01:00',
  PRIMARY KEY (`resource_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mdt_doj_schedule_settings` (
  `setting_key` varchar(50) NOT NULL,
  `setting_value` varchar(500) NOT NULL DEFAULT '',
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `mdt_doj_schedule_settings` (`setting_key`, `setting_value`) VALUES
('grid_start',     '21:00'),
('grid_end',       '01:00'),
('slot_minutes',   '15'),
('blocked_slots',  '21:00,00:15,00:30,00:45,01:00');

2. server.cfg

jsCopier
ensure mx_mdt