CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(64) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
role VARCHAR(32) DEFAULT 'civilian',
department VARCHAR(64) DEFAULT '',
discord_id VARCHAR(64) DEFAULT '',
fivem_license VARCHAR(128) DEFAULT '',
job_name VARCHAR(64) DEFAULT '',
job_grade INT DEFAULT 0,
link_code VARCHAR(16) DEFAULT '',
active TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS role_permissions (
id INT AUTO_INCREMENT PRIMARY KEY,
role VARCHAR(32),
permission VARCHAR(64),
UNIQUE KEY role_perm(role,permission)
);

CREATE TABLE IF NOT EXISTS calls (
id INT AUTO_INCREMENT PRIMARY KEY,
call_number INT,
call_type VARCHAR(8) DEFAULT 'I',
status VARCHAR(32) DEFAULT 'RCVD',
nature VARCHAR(255),
priority INT DEFAULT 3,
response VARCHAR(16) DEFAULT '',
address TEXT,
zone VARCHAR(64),
agency VARCHAR(64),
units VARCHAR(255) DEFAULT '',
record_id VARCHAR(64) DEFAULT '',
created_by INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS units (
id INT AUTO_INCREMENT PRIMARY KEY,
unit VARCHAR(64) UNIQUE,
status VARCHAR(64) DEFAULT 'AVAIL',
zone VARCHAR(64) DEFAULT '',
kind VARCHAR(64) DEFAULT 'LES',
description VARCHAR(255) DEFAULT '',
location TEXT,
discord_id VARCHAR(64) DEFAULT '',
fivem_license VARCHAR(128) DEFAULT '',
job_name VARCHAR(64) DEFAULT '',
job_grade INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS persons (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(128) DEFAULT '',
last_name VARCHAR(128) DEFAULT '',
dob DATE NULL,
discord_id VARCHAR(64) DEFAULT '',
fivem_license VARCHAR(128) DEFAULT '',
address TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vehicles (
id INT AUTO_INCREMENT PRIMARY KEY,
plate VARCHAR(32),
state_code VARCHAR(16),
vin VARCHAR(64),
model VARCHAR(128),
color VARCHAR(64),
owner_person_id INT DEFAULT 0,
status VARCHAR(64) DEFAULT 'VALID',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS citations (
id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT DEFAULT 0,
officer_user_id INT DEFAULT 0,
officer_name VARCHAR(128),
offender_discord_id VARCHAR(64),
offender_license VARCHAR(128),
state_code VARCHAR(32),
statute VARCHAR(255),
description TEXT,
fine_amount DECIMAL(10,2) DEFAULT 0,
status VARCHAR(32) DEFAULT 'ISSUED',
source VARCHAR(32) DEFAULT 'WEB',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS criminal_history (
id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT,
entered_by_user_id INT,
charge VARCHAR(255),
disposition VARCHAR(128),
sentence TEXT,
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS warrants (
id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT,
charge VARCHAR(255),
notes TEXT,
status VARCHAR(32) DEFAULT 'ACTIVE',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bolos (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
description TEXT,
status VARCHAR(32) DEFAULT 'ACTIVE',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS dispatch_notes (
id INT AUTO_INCREMENT PRIMARY KEY,
call_id INT,
note TEXT,
created_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);