CREATE TABLE `mdt_audit_log` (
	`id` int AUTO_INCREMENT NOT NULL,
	`actor_citizenid` varchar(50),
	`actor_name` varchar(255),
	`action` varchar(64) NOT NULL,
	`target_type` varchar(32),
	`target_id` varchar(64),
	`meta` json,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `mdt_audit_log_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_bolos` (
	`id` int AUTO_INCREMENT NOT NULL,
	`kind` enum('person','vehicle') NOT NULL,
	`reference` varchar(64) NOT NULL,
	`label` varchar(255) NOT NULL,
	`reason` text,
	`active` boolean NOT NULL DEFAULT true,
	`created_by_citizenid` varchar(50),
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `mdt_bolos_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_case_charges` (
	`id` int AUTO_INCREMENT NOT NULL,
	`case_id` int NOT NULL,
	`catalog_id` int NOT NULL,
	`count` int NOT NULL DEFAULT 1,
	`fine` int NOT NULL DEFAULT 0,
	`jail_time` int NOT NULL DEFAULT 0,
	`points` int NOT NULL DEFAULT 0,
	CONSTRAINT `mdt_case_charges_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_cases` (
	`id` int AUTO_INCREMENT NOT NULL,
	`title` varchar(255) NOT NULL,
	`suspect_citizenid` varchar(50) NOT NULL,
	`status` enum('open','closed','dismissed') NOT NULL DEFAULT 'open',
	`narrative` text,
	`total_fine` int NOT NULL DEFAULT 0,
	`total_jail_time` int NOT NULL DEFAULT 0,
	`officer_citizenid` varchar(50),
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `mdt_cases_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_charges_catalog` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(32) NOT NULL,
	`title` varchar(255) NOT NULL,
	`category` varchar(64) NOT NULL DEFAULT 'sonstiges',
	`fine` int NOT NULL DEFAULT 0,
	`jail_time` int NOT NULL DEFAULT 0,
	`points` int NOT NULL DEFAULT 0,
	`active` boolean NOT NULL DEFAULT true,
	CONSTRAINT `mdt_charges_catalog_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_dispatch_calls` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(32) NOT NULL,
	`title` varchar(255) NOT NULL,
	`description` text,
	`department` enum('police','ems','fire') NOT NULL DEFAULT 'police',
	`priority` enum('low','medium','high') NOT NULL DEFAULT 'medium',
	`status` enum('pending','assigned','enroute','onscene','closed') NOT NULL DEFAULT 'pending',
	`location` varchar(255),
	`coords_x` varchar(32),
	`coords_y` varchar(32),
	`coords_z` varchar(32),
	`caller_citizenid` varchar(50),
	`assigned_officers` json NOT NULL DEFAULT ('[]'),
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `mdt_dispatch_calls_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_person_records` (
	`citizenid` varchar(50) NOT NULL,
	`notes` text,
	`flags` json NOT NULL DEFAULT ('[]'),
	`is_wanted` boolean NOT NULL DEFAULT false,
	`mugshot_url` varchar(512),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`updated_by` varchar(50),
	CONSTRAINT `mdt_person_records_citizenid` PRIMARY KEY(`citizenid`)
);
--> statement-breakpoint
CREATE TABLE `mdt_users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`citizenid` varchar(50),
	`discord_id` varchar(50),
	`name` varchar(255) NOT NULL,
	`callsign` varchar(32),
	`roles` json NOT NULL DEFAULT ('[]'),
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `mdt_users_id` PRIMARY KEY(`id`),
	CONSTRAINT `mdt_users_citizenid_unique` UNIQUE(`citizenid`),
	CONSTRAINT `mdt_users_discord_id_unique` UNIQUE(`discord_id`)
);
--> statement-breakpoint
CREATE TABLE `mdt_warrants` (
	`id` int AUTO_INCREMENT NOT NULL,
	`citizenid` varchar(50) NOT NULL,
	`reason` text NOT NULL,
	`status` enum('active','served','revoked','expired') NOT NULL DEFAULT 'active',
	`case_id` int,
	`issued_by_citizenid` varchar(50),
	`issued_at` timestamp NOT NULL DEFAULT (now()),
	`expires_at` timestamp,
	CONSTRAINT `mdt_warrants_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE INDEX `audit_actor_idx` ON `mdt_audit_log` (`actor_citizenid`);--> statement-breakpoint
CREATE INDEX `audit_action_idx` ON `mdt_audit_log` (`action`);--> statement-breakpoint
CREATE INDEX `suspect_idx` ON `mdt_cases` (`suspect_citizenid`);--> statement-breakpoint
CREATE INDEX `warrant_citizen_idx` ON `mdt_warrants` (`citizenid`);