-- Clean starter database for the Laravel Ticketing App
-- Safe starter data only: no live API secrets, sessions, access tokens, or customer ticket history.
-- Designed for MySQL/MariaDB with utf8mb4 support.

CREATE DATABASE IF NOT EXISTS `ticketing`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `ticketing`;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET FOREIGN_KEY_CHECKS = 0;

-- Drop application tables first, then Laravel/system tables.
DROP TABLE IF EXISTS `attachments`;
DROP TABLE IF EXISTS `comments`;
DROP TABLE IF EXISTS `ticket_events`;
DROP TABLE IF EXISTS `ticket_agent`;
DROP TABLE IF EXISTS `messages`;
DROP TABLE IF EXISTS `tickets`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `support_categories`;
DROP TABLE IF EXISTS `client_api_keys`;
DROP TABLE IF EXISTS `password_reset_tokens`;
DROP TABLE IF EXISTS `personal_access_tokens`;
DROP TABLE IF EXISTS `sessions`;
DROP TABLE IF EXISTS `cache_locks`;
DROP TABLE IF EXISTS `cache`;
DROP TABLE IF EXISTS `failed_jobs`;
DROP TABLE IF EXISTS `job_batches`;
DROP TABLE IF EXISTS `jobs`;
DROP TABLE IF EXISTS `migrations`;

SET FOREIGN_KEY_CHECKS = 1;

-- -----------------------------------------------------------------------------
-- Client API keys
-- -----------------------------------------------------------------------------
CREATE TABLE `client_api_keys` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_name` varchar(255) NOT NULL,
  `api_key` varchar(255) NOT NULL,
  `api_secret_hash` varchar(255) NOT NULL,
  `allowed_origins` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `feature_cyber_security` tinyint(1) NOT NULL DEFAULT 0,
  `feature_cloud_storage` tinyint(1) NOT NULL DEFAULT 0,
  `feature_full_email_setup` tinyint(1) NOT NULL DEFAULT 0,
  `feature_full_management_system` tinyint(1) NOT NULL DEFAULT 0,
  `email_accounts_used` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `email_accounts_limit` int(10) UNSIGNED NOT NULL DEFAULT 20,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_api_keys_api_key_unique` (`api_key`),
  KEY `client_api_keys_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Support categories
-- -----------------------------------------------------------------------------
CREATE TABLE `support_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `support_categories_name_unique` (`name`),
  UNIQUE KEY `support_categories_slug_unique` (`slug`),
  KEY `support_categories_is_active_sort_order_index` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Users
-- -----------------------------------------------------------------------------
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_api_key_id` bigint(20) UNSIGNED DEFAULT NULL,
  `support_category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'user',
  `is_super_admin` tinyint(1) NOT NULL DEFAULT 0,
  `department` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL COMMENT 'Legacy/display category name. Prefer support_category_id for relations.',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_client_api_key_id_index` (`client_api_key_id`),
  KEY `users_support_category_id_index` (`support_category_id`),
  KEY `users_role_index` (`role`),
  KEY `users_department_index` (`department`),
  CONSTRAINT `users_client_api_key_id_foreign`
    FOREIGN KEY (`client_api_key_id`) REFERENCES `client_api_keys` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `users_support_category_id_foreign`
    FOREIGN KEY (`support_category_id`) REFERENCES `support_categories` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `users_role_check`
    CHECK (`role` IN ('user', 'agent', 'admin'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Tickets
-- -----------------------------------------------------------------------------
CREATE TABLE `tickets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_number` varchar(50) NOT NULL,
  `support_category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
  `category` varchar(255) DEFAULT NULL COMMENT 'Legacy/display category name. Prefer support_category_id for relations.',
  `priority` varchar(50) NOT NULL DEFAULT 'medium',
  `status` varchar(50) NOT NULL DEFAULT 'open',
  `requester_id` bigint(20) UNSIGNED DEFAULT NULL,
  `requester_name` varchar(255) DEFAULT NULL,
  `requester_email` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `assigned_to_id` bigint(20) UNSIGNED DEFAULT NULL,
  `sla_due` timestamp NULL DEFAULT NULL,
  `first_response_sla` timestamp NULL DEFAULT NULL,
  `resolution_sla` timestamp NULL DEFAULT NULL,
  `first_response_at` timestamp NULL DEFAULT NULL,
  `first_response_met` tinyint(1) NOT NULL DEFAULT 0,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  `reopened_at` timestamp NULL DEFAULT NULL,
  `last_customer_reply_at` timestamp NULL DEFAULT NULL,
  `last_agent_reply_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tickets_ticket_number_unique` (`ticket_number`),
  KEY `tickets_support_category_id_index` (`support_category_id`),
  KEY `tickets_requester_id_index` (`requester_id`),
  KEY `tickets_assigned_to_id_index` (`assigned_to_id`),
  KEY `tickets_status_index` (`status`),
  KEY `tickets_priority_index` (`priority`),
  KEY `tickets_department_index` (`department`),
  KEY `tickets_created_at_index` (`created_at`),
  KEY `tickets_updated_at_index` (`updated_at`),
  KEY `tickets_status_priority_index` (`status`, `priority`),
  KEY `tickets_status_assigned_to_id_index` (`status`, `assigned_to_id`),
  KEY `tickets_requester_status_index` (`requester_id`, `status`),
  CONSTRAINT `tickets_support_category_id_foreign`
    FOREIGN KEY (`support_category_id`) REFERENCES `support_categories` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `tickets_requester_id_foreign`
    FOREIGN KEY (`requester_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `tickets_assigned_to_id_foreign`
    FOREIGN KEY (`assigned_to_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `tickets_priority_check`
    CHECK (`priority` IN ('low', 'medium', 'high', 'urgent')),
  CONSTRAINT `tickets_status_check`
    CHECK (`status` IN ('open', 'pending', 'resolved', 'closed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Ticket/agent many-to-many assignments
-- -----------------------------------------------------------------------------
CREATE TABLE `ticket_agent` (
  `ticket_id` bigint(20) UNSIGNED NOT NULL,
  `agent_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`ticket_id`, `agent_id`),
  KEY `ticket_agent_agent_id_index` (`agent_id`),
  CONSTRAINT `ticket_agent_ticket_id_foreign`
    FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ticket_agent_agent_id_foreign`
    FOREIGN KEY (`agent_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Comments
-- -----------------------------------------------------------------------------
CREATE TABLE `comments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` bigint(20) UNSIGNED NOT NULL,
  `author_id` bigint(20) UNSIGNED NOT NULL,
  `content` longtext NOT NULL,
  `is_internal` tinyint(1) NOT NULL DEFAULT 0,
  `is_first_response` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `comments_ticket_id_index` (`ticket_id`),
  KEY `comments_author_id_index` (`author_id`),
  KEY `comments_created_at_index` (`created_at`),
  CONSTRAINT `comments_ticket_id_foreign`
    FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `comments_author_id_foreign`
    FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Attachments
-- -----------------------------------------------------------------------------
CREATE TABLE `attachments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` bigint(20) UNSIGNED DEFAULT NULL,
  `comment_id` bigint(20) UNSIGNED DEFAULT NULL,
  `uploaded_by_id` bigint(20) UNSIGNED DEFAULT NULL,
  `disk` varchar(50) NOT NULL DEFAULT 'public',
  `filename` varchar(255) NOT NULL,
  `original_filename` varchar(255) DEFAULT NULL,
  `path` varchar(500) NOT NULL,
  `mime_type` varchar(255) DEFAULT NULL,
  `size` bigint(20) UNSIGNED DEFAULT NULL,
  `checksum` varchar(128) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `attachments_ticket_id_index` (`ticket_id`),
  KEY `attachments_comment_id_index` (`comment_id`),
  KEY `attachments_uploaded_by_id_index` (`uploaded_by_id`),
  CONSTRAINT `attachments_ticket_id_foreign`
    FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `attachments_comment_id_foreign`
    FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `attachments_uploaded_by_id_foreign`
    FOREIGN KEY (`uploaded_by_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Ticket event/audit trail
-- -----------------------------------------------------------------------------
CREATE TABLE `ticket_events` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` bigint(20) UNSIGNED NOT NULL,
  `actor_id` bigint(20) UNSIGNED DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ticket_events_ticket_id_index` (`ticket_id`),
  KEY `ticket_events_actor_id_index` (`actor_id`),
  KEY `ticket_events_event_type_index` (`event_type`),
  KEY `ticket_events_created_at_index` (`created_at`),
  CONSTRAINT `ticket_events_ticket_id_foreign`
    FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ticket_events_actor_id_foreign`
    FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Messages / team chat
-- -----------------------------------------------------------------------------
CREATE TABLE `messages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_uuid` varchar(100) NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `message` longtext NOT NULL,
  `room_type` varchar(50) NOT NULL DEFAULT 'general',
  `room_id` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `messages_message_uuid_unique` (`message_uuid`),
  KEY `messages_user_id_index` (`user_id`),
  KEY `messages_created_at_index` (`created_at`),
  KEY `messages_room_type_room_id_index` (`room_type`, `room_id`),
  CONSTRAINT `messages_user_id_foreign`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Laravel cache tables
-- -----------------------------------------------------------------------------
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`),
  KEY `cache_locks_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Laravel queue tables
-- -----------------------------------------------------------------------------
CREATE TABLE `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Laravel migrations
-- -----------------------------------------------------------------------------
CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Laravel auth/session tables
-- -----------------------------------------------------------------------------
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `personal_access_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`),
  CONSTRAINT `sessions_user_id_foreign`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Seed migration records
-- -----------------------------------------------------------------------------
INSERT INTO `migrations` (`migration`, `batch`) VALUES
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2026_04_02_000001_add_support_fields_to_users_table', 1),
('2026_04_02_000002_create_tickets_table', 1),
('2026_04_02_000003_create_comments_table', 1),
('2026_04_02_000004_create_attachments_table', 1),
('2026_04_02_000005_create_messages_table', 1),
('laravel_sanctum_personal_access_tokens', 1),
('2026_04_07_093204_create_client_api_keys_table', 2),
('2026_04_07_100000_add_client_api_key_id_to_users_table', 3),
('2026_04_08_150130_create_ticket_agent_table', 3),
('2026_04_09_130000_create_support_categories_table', 4),
('2026_04_10_090000_add_service_features_to_client_api_keys_table', 4),
('2026_04_30_000001_add_comment_id_to_attachments_table', 5),
('2026_05_04_000001_add_is_super_admin_to_users_table', 5),
('2026_05_06_000001_add_uploaded_by_id_to_attachments_table', 5),
('2026_05_25_000001_create_ticket_events_table', 6),
('2026_05_25_000002_clean_ticketing_schema_indexes_and_constraints', 6);

-- -----------------------------------------------------------------------------
-- Safe starter seed data
-- -----------------------------------------------------------------------------
INSERT INTO `support_categories`
  (`id`, `name`, `slug`, `is_active`, `sort_order`, `created_at`, `updated_at`)
VALUES
  (1, 'General support', 'general-support', 1, 1, NOW(), NOW()),
  (2, 'Technical support', 'technical-support', 1, 2, NOW(), NOW()),
  (3, 'Sales', 'sales', 1, 3, NOW(), NOW());

-- Demo API secret is intentionally non-production. Store only a hash in production.
-- Demo plaintext value to generate this seed hash: cs_demo_change_me
INSERT INTO `client_api_keys`
  (`id`, `client_name`, `api_key`, `api_secret_hash`, `allowed_origins`, `is_active`,
   `feature_cyber_security`, `feature_cloud_storage`, `feature_full_email_setup`,
   `feature_full_management_system`, `email_accounts_used`, `email_accounts_limit`,
   `created_at`, `updated_at`)
VALUES
  (1, 'Demo Client', 'ck_demo_change_me',
   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   JSON_ARRAY('http://localhost', 'http://127.0.0.1:8000'),
   1, 0, 0, 0, 0, 0, 20, NOW(), NOW());

-- Starter login password for all seed users is: password
INSERT INTO `users`
  (`id`, `client_api_key_id`, `support_category_id`, `name`, `email`, `email_verified_at`,
   `password`, `role`, `is_super_admin`, `department`, `category`, `created_at`, `updated_at`)
VALUES
  (1, 1, NULL, 'Admin User', 'admin@example.com', NOW(),
   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   'admin', 1, 'IT', NULL, NOW(), NOW()),
  (2, 1, 2, 'Support Agent', 'agent@example.com', NOW(),
   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   'agent', 0, 'Tech', 'Technical support', NOW(), NOW()),
  (3, 1, NULL, 'Demo User', 'user@example.com', NOW(),
   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   'user', 0, 'Operations', NULL, NOW(), NOW());

INSERT INTO `tickets`
  (`id`, `ticket_number`, `support_category_id`, `title`, `description`, `category`,
   `priority`, `status`, `requester_id`, `requester_name`, `requester_email`, `department`,
   `assigned_to_id`, `sla_due`, `first_response_sla`, `resolution_sla`,
   `first_response_at`, `first_response_met`, `last_customer_reply_at`, `created_at`, `updated_at`)
VALUES
  (1, 'TKT-DEMO-000001', 2, 'Welcome ticket',
   'This starter ticket confirms that the ticketing database is loaded and the relationships are working.',
   'Technical support', 'medium', 'open', 3, 'Demo User', 'user@example.com', 'Operations',
   2, DATE_ADD(NOW(), INTERVAL 2 DAY), DATE_ADD(NOW(), INTERVAL 4 HOUR), DATE_ADD(NOW(), INTERVAL 2 DAY),
   NULL, 0, NOW(), NOW(), NOW());

INSERT INTO `ticket_agent`
  (`ticket_id`, `agent_id`, `created_at`, `updated_at`)
VALUES
  (1, 2, NOW(), NOW());

INSERT INTO `comments`
  (`id`, `ticket_id`, `author_id`, `content`, `is_internal`, `is_first_response`, `created_at`, `updated_at`)
VALUES
  (1, 1, 3, 'This is a sample customer comment.', 0, 0, NOW(), NOW()),
  (2, 1, 2, 'This is a sample agent response.', 0, 1, NOW(), NOW());

INSERT INTO `ticket_events`
  (`ticket_id`, `actor_id`, `event_type`, `old_value`, `new_value`, `metadata`, `created_at`)
VALUES
  (1, 3, 'ticket_created', NULL, 'open', JSON_OBJECT('source', 'seed'), NOW()),
  (1, 2, 'agent_assigned', NULL, 'Support Agent', JSON_OBJECT('source', 'seed'), NOW()),
  (1, 2, 'first_response_added', NULL, 'comment:2', JSON_OBJECT('source', 'seed'), NOW());

INSERT INTO `messages`
  (`id`, `message_uuid`, `user_id`, `message`, `room_type`, `room_id`, `created_at`, `updated_at`)
VALUES
  (1, 'msg-demo-000001', 2, 'Team chat is ready.', 'general', NULL, NOW(), NOW());

-- Reset auto-increment values after explicit seed IDs.
ALTER TABLE `client_api_keys` AUTO_INCREMENT = 2;
ALTER TABLE `support_categories` AUTO_INCREMENT = 4;
ALTER TABLE `users` AUTO_INCREMENT = 4;
ALTER TABLE `tickets` AUTO_INCREMENT = 2;
ALTER TABLE `comments` AUTO_INCREMENT = 3;
ALTER TABLE `ticket_events` AUTO_INCREMENT = 4;
ALTER TABLE `messages` AUTO_INCREMENT = 2;