-- Minimal legacy fixture for import tests

CREATE TABLE IF NOT EXISTS `companies` (
  `id` int(11) NOT NULL,
  `name` varchar(75) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `contact_email` varchar(55) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
);

INSERT INTO `companies` (`id`, `name`, `email`, `address`, `contact_email`, `phone`, `created_at`, `updated_at`, `deleted_at`) VALUES
(50, 'Maya E.A Limited', 'info@maya.com', '', '', '', '2025-12-10 03:22:56', '2025-12-10 03:22:56', NULL);

CREATE TABLE IF NOT EXISTS `departments` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
);

INSERT INTO `departments` (`id`, `name`, `description`, `manager_id`, `created_at`) VALUES
(1, 'IT Support', NULL, NULL, '2025-12-10 11:05:35');

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` text DEFAULT NULL
);

INSERT INTO `roles` (`id`, `name`, `description`) VALUES
(1, 'admin', 'System Administrator with full access'),
(2, 'technician', 'Support Technician with ticket management access'),
(3, 'client', 'Client user with ticket submission access');

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `email` varchar(55) NOT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `email_verification_token` varchar(255) DEFAULT NULL,
  `email_verified` tinyint(1) DEFAULT 0,
  `name` varchar(150) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `password_reset_token` varchar(255) DEFAULT NULL,
  `password_reset_expires` datetime DEFAULT NULL
);

INSERT INTO `users` (`id`, `email`, `password_hash`, `email_verification_token`, `email_verified`, `name`, `first_name`, `last_name`, `phone`, `company_id`, `is_active`, `created_at`, `updated_at`, `last_login`, `password_reset_token`, `password_reset_expires`) VALUES
(4, 'samwel@techronltd.co.ke', 'sha256:dfb3b4d2e6ee1eb3d5c5a572e41ae9d8:dc00fb39d0280a792842e020d7b93f4525f0e5349c7f01cbedf45cde83113447', NULL, 1, '', 'Samwel', 'Karanja', '0793878068', NULL, 1, '2025-12-10 04:19:48', '2025-12-10 04:19:48', NULL, NULL, NULL),
(8, 'ma@mayaealtd.com', 'LEGACY_TEST_HASH', NULL, 1, '', 'Mwikali', 'Mwelii', '0743590980', 50, 1, '2026-01-12 04:20:55', '2026-01-12 04:20:55', NULL, NULL, NULL);

CREATE TABLE IF NOT EXISTS `user_roles` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL
);

INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
(4, 2),
(8, 3);

CREATE TABLE IF NOT EXISTS `tickets` (
  `id` int(11) NOT NULL,
  `ticket_no` varchar(30) NOT NULL,
  `user_id` int(11) NOT NULL,
  `subject` varchar(155) NOT NULL,
  `description` text DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `status` enum('open','pending','resolved','closed') DEFAULT 'open',
  `assignee_id` int(11) DEFAULT NULL,
  `source` enum('portal','email','api') DEFAULT 'portal',
  `sla_due` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp()
);

INSERT INTO `tickets` (`id`, `ticket_no`, `user_id`, `subject`, `description`, `department_id`, `priority`, `status`, `assignee_id`, `source`, `sla_due`, `created_at`, `updated_at`) VALUES
(5, 'TKT-2026-EAB30F', 8, 'Hardware Ticket', 'Biometrics issue', 1, 'high', 'closed', 4, 'portal', NULL, '2026-01-28 03:24:06', '2026-01-29 12:54:19');

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL,
  `ticket_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `body` text NOT NULL,
  `is_internal` tinyint(1) DEFAULT 0,
  `channel` enum('portal','email','system') DEFAULT 'portal',
  `email_message_id` varchar(255) DEFAULT NULL,
  `email_in_reply_to` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `is_system_message` tinyint(1) DEFAULT 0
);

INSERT INTO `messages` (`id`, `ticket_id`, `user_id`, `body`, `is_internal`, `channel`, `email_message_id`, `email_in_reply_to`, `created_at`, `is_system_message`) VALUES
(19, 5, 8, 'Unable to capture fingerprints', 0, 'portal', NULL, NULL, '2026-01-28 03:24:09', 0),
(20, 5, 4, 'I will be coming to check it', 0, 'portal', NULL, NULL, '2026-01-28 06:05:37', 0);

CREATE TABLE IF NOT EXISTS `attachments` (
  `id` int(11) NOT NULL,
  `message_id` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `filepath` varchar(255) NOT NULL,
  `mime` varchar(100) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
);

INSERT INTO `attachments` (`id`, `message_id`, `filename`, `filepath`, `mime`, `size`, `uploaded_by`, `created_at`) VALUES
(1, 19, 'photo.jpeg', '6979d5b6f2b0f.jpeg', 'image/jpeg', 128, 8, '2026-01-28 03:24:09');