50 lines
2.0 KiB
SQL
50 lines
2.0 KiB
SQL
CREATE TABLE `mdt_citizens` (
|
|
`citizenid` varchar(50) NOT NULL,
|
|
`firstname` varchar(128) NOT NULL,
|
|
`lastname` varchar(128) NOT NULL,
|
|
`dob` varchar(32),
|
|
`gender` enum('male','female','divers','unknown') NOT NULL DEFAULT 'unknown',
|
|
`phone` varchar(32),
|
|
`nationality` varchar(64),
|
|
`legal_status` enum('citizen','resident','visitor','illegal','unknown') NOT NULL DEFAULT 'citizen',
|
|
`address` varchar(255),
|
|
`occupation` varchar(128),
|
|
`height` varchar(32),
|
|
`eye_color` varchar(32),
|
|
`hair_color` varchar(32),
|
|
`distinguishing_marks` text,
|
|
`aliases` json NOT NULL DEFAULT ('[]'),
|
|
`licenses` json NOT NULL DEFAULT ('[]'),
|
|
`mugshot_url` varchar(512),
|
|
`notes` text,
|
|
`flags` json NOT NULL DEFAULT ('[]'),
|
|
`is_wanted` boolean NOT NULL DEFAULT false,
|
|
`created_by_citizenid` varchar(50),
|
|
`created_by_name` varchar(255),
|
|
`updated_by` varchar(50),
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `mdt_citizens_citizenid` PRIMARY KEY(`citizenid`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `mdt_vehicles` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`plate` varchar(32) NOT NULL,
|
|
`model` varchar(64),
|
|
`color` varchar(64),
|
|
`owner_citizenid` varchar(50),
|
|
`plate_status` enum('registered','forged','stolen','unknown') NOT NULL DEFAULT 'registered',
|
|
`is_stolen` boolean NOT NULL DEFAULT false,
|
|
`has_bolo` boolean NOT NULL DEFAULT false,
|
|
`notes` text,
|
|
`created_by_citizenid` varchar(50),
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `mdt_vehicles_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE INDEX `citizen_name_idx` ON `mdt_citizens` (`lastname`,`firstname`);--> statement-breakpoint
|
|
CREATE INDEX `citizen_wanted_idx` ON `mdt_citizens` (`is_wanted`);--> statement-breakpoint
|
|
CREATE INDEX `vehicle_plate_idx` ON `mdt_vehicles` (`plate`);--> statement-breakpoint
|
|
CREATE INDEX `vehicle_owner_idx` ON `mdt_vehicles` (`owner_citizenid`);
|