CREATE TABLE user_roles (
	user_id INTEGER NOT NULL, 
	role_id INTEGER NOT NULL, 
	PRIMARY KEY (user_id, role_id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(role_id) REFERENCES roles (id) ON DELETE CASCADE
);

CREATE INDEX ix_user_roles_role_id ON user_roles (role_id);


CREATE TABLE role_permissions (
	role_id INTEGER NOT NULL, 
	permission_id INTEGER NOT NULL, 
	PRIMARY KEY (role_id, permission_id), 
	FOREIGN KEY(role_id) REFERENCES roles (id) ON DELETE CASCADE, 
	FOREIGN KEY(permission_id) REFERENCES permissions (id) ON DELETE CASCADE
);

CREATE INDEX ix_role_permissions_permission_id ON role_permissions (permission_id);


CREATE TABLE user_company_access (
	user_id INTEGER NOT NULL, 
	company_id INTEGER NOT NULL, 
	PRIMARY KEY (user_id, company_id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE CASCADE
);


CREATE TABLE user_department_access (
	user_id INTEGER NOT NULL, 
	department_id INTEGER NOT NULL, 
	PRIMARY KEY (user_id, department_id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(department_id) REFERENCES departments (id) ON DELETE CASCADE
);


CREATE TABLE ticket_watchers (
	ticket_id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	PRIMARY KEY (ticket_id, user_id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
);


CREATE TABLE ticket_tag_map (
	ticket_id INTEGER NOT NULL, 
	ticket_tag_id INTEGER NOT NULL, 
	PRIMARY KEY (ticket_id, ticket_tag_id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(ticket_tag_id) REFERENCES ticket_tags (id) ON DELETE CASCADE
);


CREATE TABLE ticket_relations (
	left_ticket_id INTEGER NOT NULL, 
	right_ticket_id INTEGER NOT NULL, 
	PRIMARY KEY (left_ticket_id, right_ticket_id), 
	CONSTRAINT uq_ticket_relation_pair UNIQUE (left_ticket_id, right_ticket_id), 
	FOREIGN KEY(left_ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(right_ticket_id) REFERENCES tickets (id) ON DELETE CASCADE
);


CREATE TABLE permissions (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	code VARCHAR(80) NOT NULL, 
	name VARCHAR(120) NOT NULL, 
	module VARCHAR(80) NOT NULL, 
	description TEXT, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (code)
);

CREATE INDEX ix_permissions_module ON permissions (module);


CREATE TABLE roles (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(120) NOT NULL, 
	slug VARCHAR(120) NOT NULL, 
	description TEXT, 
	is_system BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	UNIQUE (slug)
);


CREATE TABLE user_permissions (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	user_id INTEGER NOT NULL, 
	permission_id INTEGER NOT NULL, 
	is_allowed BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	CONSTRAINT uq_user_permission UNIQUE (user_id, permission_id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(permission_id) REFERENCES permissions (id) ON DELETE CASCADE
);

CREATE INDEX ix_user_permissions_permission_id ON user_permissions (permission_id);

CREATE INDEX ix_user_permissions_user_id ON user_permissions (user_id);


CREATE TABLE users (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	first_name VARCHAR(100) NOT NULL, 
	last_name VARCHAR(100) NOT NULL, 
	username VARCHAR(80) NOT NULL, 
	email VARCHAR(255) NOT NULL, 
	phone VARCHAR(50), 
	telegram_chat_id VARCHAR(64), 
	title VARCHAR(120), 
	timezone VARCHAR(64) NOT NULL, 
	locale VARCHAR(20) NOT NULL, 
	user_type ENUM('INTERNAL','EXTERNAL') NOT NULL, 
	password_hash VARCHAR(255) NOT NULL, 
	password_changed_at DATETIME, 
	last_login_at DATETIME, 
	last_login_ip VARCHAR(64), 
	failed_login_attempts INTEGER NOT NULL, 
	locked_until DATETIME, 
	is_enabled BOOL NOT NULL, 
	must_change_password BOOL NOT NULL, 
	two_factor_enabled BOOL NOT NULL, 
	two_factor_secret VARCHAR(255), 
	company_id INTEGER, 
	department_id INTEGER, 
	manager_id INTEGER, 
	notes TEXT, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL, 
	FOREIGN KEY(department_id) REFERENCES departments (id) ON DELETE SET NULL, 
	FOREIGN KEY(manager_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_users_company_id ON users (company_id);

CREATE INDEX ix_users_deleted_at ON users (deleted_at);

CREATE INDEX ix_users_department_id ON users (department_id);

CREATE UNIQUE INDEX ix_users_email ON users (email);

CREATE INDEX ix_users_is_enabled ON users (is_enabled);

CREATE INDEX ix_users_locked_until ON users (locked_until);

CREATE INDEX ix_users_manager_id ON users (manager_id);

CREATE INDEX ix_users_telegram_chat_id ON users (telegram_chat_id);

CREATE INDEX ix_users_user_type ON users (user_type);

CREATE UNIQUE INDEX ix_users_username ON users (username);


CREATE TABLE folders (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	parent_id INTEGER, 
	company_id INTEGER, 
	department_id INTEGER, 
	created_by_id INTEGER, 
	name VARCHAR(150) NOT NULL, 
	slug VARCHAR(180) NOT NULL, 
	description TEXT, 
	visibility ENUM('INTERNAL','CUSTOMER','RESTRICTED') NOT NULL, 
	is_internal_only BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	CONSTRAINT uq_folder_slug_scope UNIQUE (parent_id, company_id, slug), 
	FOREIGN KEY(parent_id) REFERENCES folders (id) ON DELETE SET NULL, 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL, 
	FOREIGN KEY(department_id) REFERENCES departments (id) ON DELETE SET NULL, 
	FOREIGN KEY(created_by_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_folder_company_visibility ON folders (company_id, visibility);

CREATE INDEX ix_folders_company_id ON folders (company_id);

CREATE INDEX ix_folders_deleted_at ON folders (deleted_at);

CREATE INDEX ix_folders_department_id ON folders (department_id);

CREATE INDEX ix_folders_parent_id ON folders (parent_id);

CREATE INDEX ix_folders_visibility ON folders (visibility);


CREATE TABLE folder_permissions (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	folder_id INTEGER NOT NULL, 
	subject_type VARCHAR(30) NOT NULL, 
	subject_id INTEGER NOT NULL, 
	can_view BOOL NOT NULL, 
	can_upload BOOL NOT NULL, 
	can_delete BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	CONSTRAINT uq_folder_permission_subject UNIQUE (folder_id, subject_type, subject_id), 
	FOREIGN KEY(folder_id) REFERENCES folders (id) ON DELETE CASCADE
);

CREATE INDEX ix_folder_permissions_folder_id ON folder_permissions (folder_id);

CREATE INDEX ix_folder_permissions_subject_id ON folder_permissions (subject_id);

CREATE INDEX ix_folder_permissions_subject_type ON folder_permissions (subject_type);


CREATE TABLE files (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	company_id INTEGER, 
	folder_id INTEGER, 
	uploaded_by_id INTEGER, 
	original_name VARCHAR(255) NOT NULL, 
	stored_name VARCHAR(255) NOT NULL, 
	extension VARCHAR(20) NOT NULL, 
	mime_type VARCHAR(120) NOT NULL, 
	size_bytes BIGINT NOT NULL, 
	storage_disk VARCHAR(50) NOT NULL, 
	storage_path VARCHAR(500) NOT NULL, 
	checksum_sha256 VARCHAR(64), 
	description VARCHAR(255), 
	metadata_json JSON, 
	is_customer_visible BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL, 
	FOREIGN KEY(folder_id) REFERENCES folders (id) ON DELETE SET NULL, 
	FOREIGN KEY(uploaded_by_id) REFERENCES users (id) ON DELETE SET NULL, 
	UNIQUE (storage_path)
);

CREATE INDEX ix_file_company_folder_visible ON files (company_id, folder_id, is_customer_visible);

CREATE INDEX ix_file_uploaded_by_created ON files (uploaded_by_id, created_at);

CREATE INDEX ix_files_checksum_sha256 ON files (checksum_sha256);

CREATE INDEX ix_files_company_id ON files (company_id);

CREATE INDEX ix_files_deleted_at ON files (deleted_at);

CREATE INDEX ix_files_extension ON files (extension);

CREATE INDEX ix_files_folder_id ON files (folder_id);

CREATE INDEX ix_files_is_customer_visible ON files (is_customer_visible);

CREATE INDEX ix_files_uploaded_by_id ON files (uploaded_by_id);


CREATE TABLE companies (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(150) NOT NULL, 
	slug VARCHAR(160) NOT NULL, 
	code VARCHAR(50) NOT NULL, 
	contact_email VARCHAR(255), 
	phone VARCHAR(50), 
	address TEXT, 
	notes TEXT, 
	branding JSON, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	UNIQUE (slug), 
	UNIQUE (code)
);

CREATE INDEX ix_companies_deleted_at ON companies (deleted_at);

CREATE INDEX ix_companies_is_active ON companies (is_active);


CREATE TABLE departments (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(120) NOT NULL, 
	description TEXT, 
	manager_id INTEGER, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	FOREIGN KEY(manager_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_departments_deleted_at ON departments (deleted_at);

CREATE INDEX ix_departments_is_active ON departments (is_active);

CREATE INDEX ix_departments_manager_id ON departments (manager_id);


CREATE TABLE notifications (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	user_id INTEGER NOT NULL, 
	type ENUM('INFO','SUCCESS','WARNING','ALERT') NOT NULL, 
	title VARCHAR(150) NOT NULL, 
	message VARCHAR(255) NOT NULL, 
	link VARCHAR(255), 
	payload JSON, 
	is_read BOOL NOT NULL, 
	read_at DATETIME, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE INDEX ix_notifications_is_read ON notifications (is_read);

CREATE INDEX ix_notifications_type ON notifications (type);

CREATE INDEX ix_notifications_user_id ON notifications (user_id);


CREATE TABLE audit_logs (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	actor_id INTEGER, 
	action VARCHAR(80) NOT NULL, 
	target_type VARCHAR(80), 
	target_id VARCHAR(80), 
	company_id INTEGER, 
	ip_address VARCHAR(64), 
	user_agent VARCHAR(255), 
	success BOOL NOT NULL, 
	summary VARCHAR(255) NOT NULL, 
	details JSON, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(actor_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL
);

CREATE INDEX ix_audit_logs_action ON audit_logs (action);

CREATE INDEX ix_audit_logs_actor_id ON audit_logs (actor_id);

CREATE INDEX ix_audit_logs_company_id ON audit_logs (company_id);

CREATE INDEX ix_audit_logs_success ON audit_logs (success);

CREATE INDEX ix_audit_logs_target_id ON audit_logs (target_id);

CREATE INDEX ix_audit_logs_target_type ON audit_logs (target_type);


CREATE TABLE system_settings (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	`key` VARCHAR(120) NOT NULL, 
	value TEXT, 
	value_type VARCHAR(20) NOT NULL, 
	is_secret BOOL NOT NULL, 
	updated_by_id INTEGER, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (`key`), 
	FOREIGN KEY(updated_by_id) REFERENCES users (id) ON DELETE SET NULL
);


CREATE TABLE ticket_sequences (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	year INTEGER NOT NULL, 
	next_number INTEGER NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (year)
);


CREATE TABLE telegram_link_sessions (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	user_id INTEGER NOT NULL, 
	token VARCHAR(128) NOT NULL, 
	chat_id VARCHAR(64), 
	telegram_user_id BIGINT, 
	telegram_username VARCHAR(120), 
	telegram_first_name VARCHAR(120), 
	telegram_phone VARCHAR(50), 
	status VARCHAR(32) NOT NULL, 
	expires_at DATETIME NOT NULL, 
	completed_at DATETIME, 
	consumed_at DATETIME, 
	last_error VARCHAR(255), 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE INDEX ix_telegram_link_sessions_chat_id ON telegram_link_sessions (chat_id);

CREATE INDEX ix_telegram_link_sessions_expires_at ON telegram_link_sessions (expires_at);

CREATE INDEX ix_telegram_link_sessions_status ON telegram_link_sessions (status);

CREATE INDEX ix_telegram_link_sessions_telegram_user_id ON telegram_link_sessions (telegram_user_id);

CREATE UNIQUE INDEX ix_telegram_link_sessions_token ON telegram_link_sessions (token);

CREATE INDEX ix_telegram_link_sessions_user_id ON telegram_link_sessions (user_id);


CREATE TABLE ticket_statuses (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(80) NOT NULL, 
	code VARCHAR(80) NOT NULL, 
	color VARCHAR(20) NOT NULL, 
	sort_order INTEGER NOT NULL, 
	is_default BOOL NOT NULL, 
	is_closed_state BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	UNIQUE (code)
);

CREATE INDEX ix_ticket_statuses_is_closed_state ON ticket_statuses (is_closed_state);


CREATE TABLE ticket_priorities (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(80) NOT NULL, 
	code VARCHAR(80) NOT NULL, 
	color VARCHAR(20) NOT NULL, 
	weight INTEGER NOT NULL, 
	is_default BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (name), 
	UNIQUE (code)
);

CREATE INDEX ix_ticket_priorities_weight ON ticket_priorities (weight);


CREATE TABLE ticket_categories (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	parent_id INTEGER, 
	department_id INTEGER, 
	company_id INTEGER, 
	name VARCHAR(120) NOT NULL, 
	code VARCHAR(120) NOT NULL, 
	description TEXT, 
	customer_visible BOOL NOT NULL, 
	is_active BOOL NOT NULL, 
	sort_order INTEGER NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(parent_id) REFERENCES ticket_categories (id) ON DELETE SET NULL, 
	FOREIGN KEY(department_id) REFERENCES departments (id) ON DELETE SET NULL, 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL, 
	UNIQUE (code)
);

CREATE INDEX ix_ticket_categories_company_id ON ticket_categories (company_id);

CREATE INDEX ix_ticket_categories_customer_visible ON ticket_categories (customer_visible);

CREATE INDEX ix_ticket_categories_deleted_at ON ticket_categories (deleted_at);

CREATE INDEX ix_ticket_categories_department_id ON ticket_categories (department_id);

CREATE INDEX ix_ticket_categories_is_active ON ticket_categories (is_active);

CREATE INDEX ix_ticket_categories_parent_id ON ticket_categories (parent_id);

CREATE INDEX ix_ticket_category_company_active ON ticket_categories (company_id, is_active);


CREATE TABLE category_assignment_rules (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	category_id INTEGER NOT NULL, 
	company_id INTEGER, 
	department_id INTEGER, 
	user_id INTEGER, 
	priority_id INTEGER, 
	is_default BOOL NOT NULL, 
	notes TEXT, 
	is_active BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(category_id) REFERENCES ticket_categories (id) ON DELETE CASCADE, 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL, 
	FOREIGN KEY(department_id) REFERENCES departments (id) ON DELETE SET NULL, 
	FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(priority_id) REFERENCES ticket_priorities (id) ON DELETE SET NULL
);

CREATE INDEX ix_category_assignment_rules_category_id ON category_assignment_rules (category_id);

CREATE INDEX ix_category_assignment_rules_company_id ON category_assignment_rules (company_id);

CREATE INDEX ix_category_assignment_rules_department_id ON category_assignment_rules (department_id);

CREATE INDEX ix_category_assignment_rules_is_active ON category_assignment_rules (is_active);

CREATE INDEX ix_category_assignment_rules_priority_id ON category_assignment_rules (priority_id);

CREATE INDEX ix_category_assignment_rules_user_id ON category_assignment_rules (user_id);


CREATE TABLE ticket_tags (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(80) NOT NULL, 
	color VARCHAR(20) NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	UNIQUE (name)
);


CREATE TABLE tickets (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_number VARCHAR(30) NOT NULL, 
	title VARCHAR(255) NOT NULL, 
	description TEXT NOT NULL, 
	source ENUM('PORTAL','MANUAL','EMAIL') NOT NULL, 
	severity VARCHAR(40), 
	escalated BOOL NOT NULL, 
	reopen_count INTEGER NOT NULL, 
	due_at DATETIME, 
	sla_due_at DATETIME, 
	closed_at DATETIME, 
	custom_fields JSON, 
	company_id INTEGER, 
	department_id INTEGER, 
	category_id INTEGER, 
	subcategory_id INTEGER, 
	priority_id INTEGER, 
	status_id INTEGER, 
	created_by_id INTEGER, 
	owner_id INTEGER, 
	assigned_to_id INTEGER, 
	merged_into_id INTEGER, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	deleted_at DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(company_id) REFERENCES companies (id) ON DELETE SET NULL, 
	FOREIGN KEY(department_id) REFERENCES departments (id) ON DELETE SET NULL, 
	FOREIGN KEY(category_id) REFERENCES ticket_categories (id) ON DELETE SET NULL, 
	FOREIGN KEY(subcategory_id) REFERENCES ticket_categories (id) ON DELETE SET NULL, 
	FOREIGN KEY(priority_id) REFERENCES ticket_priorities (id) ON DELETE SET NULL, 
	FOREIGN KEY(status_id) REFERENCES ticket_statuses (id) ON DELETE SET NULL, 
	FOREIGN KEY(created_by_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(owner_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(assigned_to_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(merged_into_id) REFERENCES tickets (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_assignee_updated ON tickets (assigned_to_id, updated_at);

CREATE INDEX ix_ticket_company_status_priority ON tickets (company_id, status_id, priority_id);

CREATE INDEX ix_tickets_assigned_to_id ON tickets (assigned_to_id);

CREATE INDEX ix_tickets_category_id ON tickets (category_id);

CREATE INDEX ix_tickets_closed_at ON tickets (closed_at);

CREATE INDEX ix_tickets_company_id ON tickets (company_id);

CREATE INDEX ix_tickets_created_by_id ON tickets (created_by_id);

CREATE INDEX ix_tickets_deleted_at ON tickets (deleted_at);

CREATE INDEX ix_tickets_department_id ON tickets (department_id);

CREATE INDEX ix_tickets_due_at ON tickets (due_at);

CREATE INDEX ix_tickets_escalated ON tickets (escalated);

CREATE INDEX ix_tickets_merged_into_id ON tickets (merged_into_id);

CREATE INDEX ix_tickets_owner_id ON tickets (owner_id);

CREATE INDEX ix_tickets_priority_id ON tickets (priority_id);

CREATE INDEX ix_tickets_severity ON tickets (severity);

CREATE INDEX ix_tickets_sla_due_at ON tickets (sla_due_at);

CREATE INDEX ix_tickets_source ON tickets (source);

CREATE INDEX ix_tickets_status_id ON tickets (status_id);

CREATE INDEX ix_tickets_subcategory_id ON tickets (subcategory_id);

CREATE UNIQUE INDEX ix_tickets_ticket_number ON tickets (ticket_number);

CREATE INDEX ix_tickets_title ON tickets (title);


CREATE TABLE ticket_comments (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_id INTEGER NOT NULL, 
	author_id INTEGER, 
	content TEXT NOT NULL, 
	edited_at DATETIME, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(author_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_comments_author_id ON ticket_comments (author_id);

CREATE INDEX ix_ticket_comments_ticket_id ON ticket_comments (ticket_id);


CREATE TABLE ticket_internal_notes (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_id INTEGER NOT NULL, 
	author_id INTEGER, 
	content TEXT NOT NULL, 
	edited_at DATETIME, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(author_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_internal_notes_author_id ON ticket_internal_notes (author_id);

CREATE INDEX ix_ticket_internal_notes_ticket_id ON ticket_internal_notes (ticket_id);


CREATE TABLE ticket_activities (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_id INTEGER NOT NULL, 
	actor_id INTEGER, 
	action VARCHAR(80) NOT NULL, 
	message VARCHAR(255) NOT NULL, 
	field_name VARCHAR(80), 
	old_value VARCHAR(255), 
	new_value VARCHAR(255), 
	is_internal BOOL NOT NULL, 
	metadata_json JSON, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(actor_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_activities_action ON ticket_activities (action);

CREATE INDEX ix_ticket_activities_actor_id ON ticket_activities (actor_id);

CREATE INDEX ix_ticket_activities_ticket_id ON ticket_activities (ticket_id);


CREATE TABLE ticket_assignment_history (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_id INTEGER NOT NULL, 
	changed_by_id INTEGER, 
	previous_assignee_id INTEGER, 
	new_assignee_id INTEGER, 
	previous_department_id INTEGER, 
	new_department_id INTEGER, 
	reason VARCHAR(255), 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(changed_by_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(previous_assignee_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(new_assignee_id) REFERENCES users (id) ON DELETE SET NULL, 
	FOREIGN KEY(previous_department_id) REFERENCES departments (id) ON DELETE SET NULL, 
	FOREIGN KEY(new_department_id) REFERENCES departments (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_assignment_history_changed_by_id ON ticket_assignment_history (changed_by_id);

CREATE INDEX ix_ticket_assignment_history_new_assignee_id ON ticket_assignment_history (new_assignee_id);

CREATE INDEX ix_ticket_assignment_history_previous_assignee_id ON ticket_assignment_history (previous_assignee_id);

CREATE INDEX ix_ticket_assignment_history_ticket_id ON ticket_assignment_history (ticket_id);


CREATE TABLE ticket_mentions (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_id INTEGER NOT NULL, 
	comment_id INTEGER, 
	note_id INTEGER, 
	mentioned_user_id INTEGER NOT NULL, 
	created_by_id INTEGER, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(comment_id) REFERENCES ticket_comments (id) ON DELETE CASCADE, 
	FOREIGN KEY(note_id) REFERENCES ticket_internal_notes (id) ON DELETE CASCADE, 
	FOREIGN KEY(mentioned_user_id) REFERENCES users (id) ON DELETE CASCADE, 
	FOREIGN KEY(created_by_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_mentions_comment_id ON ticket_mentions (comment_id);

CREATE INDEX ix_ticket_mentions_created_by_id ON ticket_mentions (created_by_id);

CREATE INDEX ix_ticket_mentions_mentioned_user_id ON ticket_mentions (mentioned_user_id);

CREATE INDEX ix_ticket_mentions_note_id ON ticket_mentions (note_id);

CREATE INDEX ix_ticket_mentions_ticket_id ON ticket_mentions (ticket_id);


CREATE TABLE ticket_attachments (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	ticket_id INTEGER NOT NULL, 
	file_id INTEGER NOT NULL, 
	uploaded_by_id INTEGER, 
	description VARCHAR(255), 
	is_log_file BOOL NOT NULL, 
	created_at DATETIME NOT NULL DEFAULT now(), 
	updated_at DATETIME NOT NULL DEFAULT now(), 
	PRIMARY KEY (id), 
	CONSTRAINT uq_ticket_attachment_file UNIQUE (ticket_id, file_id), 
	FOREIGN KEY(ticket_id) REFERENCES tickets (id) ON DELETE CASCADE, 
	FOREIGN KEY(file_id) REFERENCES files (id) ON DELETE CASCADE, 
	FOREIGN KEY(uploaded_by_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX ix_ticket_attachments_file_id ON ticket_attachments (file_id);

CREATE INDEX ix_ticket_attachments_ticket_id ON ticket_attachments (ticket_id);