-- =====================================================================
-- 000_baseline.sql — full schema baseline for tinker_tickets
--
-- Captured from the live production database so the schema is
-- reproducible from source (a fresh install or disaster recovery).
-- Every table uses CREATE TABLE IF NOT EXISTS, so running this against
-- an existing database is a safe no-op. FK checks are disabled during
-- creation so table order does not matter.
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============ api_keys ============
CREATE TABLE IF NOT EXISTS `api_keys` (
  `api_key_id` int(11) NOT NULL AUTO_INCREMENT,
  `key_name` varchar(100) NOT NULL,
  `key_hash` varchar(255) NOT NULL,
  `key_prefix` varchar(20) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `scope` enum('read','read_write') NOT NULL DEFAULT 'read_write',
  `created_by` int(11) DEFAULT NULL,
  `last_used` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`api_key_id`),
  UNIQUE KEY `key_hash` (`key_hash`),
  KEY `created_by` (`created_by`),
  KEY `idx_key_hash` (`key_hash`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `api_keys_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ audit_log ============
CREATE TABLE IF NOT EXISTS `audit_log` (
  `audit_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action_type` varchar(50) NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` varchar(50) DEFAULT NULL,
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`details`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`audit_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_entity` (`entity_type`,`entity_id`),
  KEY `idx_action_type` (`action_type`),
  KEY `idx_audit_log_user_created` (`user_id`,`created_at` DESC),
  KEY `idx_audit_log_action_type` (`action_type`,`created_at` DESC),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`),
  KEY `idx_audit_user` (`user_id`,`created_at`),
  CONSTRAINT `audit_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ bulk_operations ============
CREATE TABLE IF NOT EXISTS `bulk_operations` (
  `operation_id` int(11) NOT NULL AUTO_INCREMENT,
  `operation_type` varchar(50) NOT NULL,
  `ticket_ids` text NOT NULL,
  `performed_by` int(11) NOT NULL,
  `parameters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters`)),
  -- 32, not 20: 'completed_with_errors' is 21 chars (see 001_widen_bulk_operations_status.sql)
  `status` varchar(32) DEFAULT 'pending',
  `total_tickets` int(11) DEFAULT NULL,
  `processed_tickets` int(11) DEFAULT 0,
  `failed_tickets` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`operation_id`),
  KEY `idx_performed_by` (`performed_by`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `bulk_operations_ibfk_1` FOREIGN KEY (`performed_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ custom_field_definitions ============
CREATE TABLE IF NOT EXISTS `custom_field_definitions` (
  `field_id` int(11) NOT NULL AUTO_INCREMENT,
  `field_name` varchar(100) NOT NULL,
  `field_label` varchar(255) NOT NULL,
  `field_type` enum('text','textarea','select','checkbox','date','number') NOT NULL,
  `field_options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Options for select fields: {"options": ["Option 1", "Option 2"]}' CHECK (json_valid(`field_options`)),
  `category` varchar(50) DEFAULT NULL COMMENT 'NULL = applies to all categories',
  `is_required` tinyint(1) DEFAULT 0,
  `display_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`field_id`),
  KEY `idx_custom_fields_category` (`category`,`is_active`),
  KEY `idx_custom_fields_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ custom_field_values ============
CREATE TABLE IF NOT EXISTS `custom_field_values` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(9) NOT NULL,
  `field_id` int(11) NOT NULL,
  `field_value` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `unique_ticket_field` (`ticket_id`,`field_id`),
  KEY `field_id` (`field_id`),
  KEY `idx_custom_values_ticket` (`ticket_id`),
  CONSTRAINT `custom_field_values_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `custom_field_definitions` (`field_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ migrations ============
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `applied_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`),
  KEY `idx_filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ recurring_tickets ============
CREATE TABLE IF NOT EXISTS `recurring_tickets` (
  `recurring_id` int(11) NOT NULL AUTO_INCREMENT,
  `title_template` varchar(255) NOT NULL,
  `description_template` text DEFAULT NULL,
  `category` varchar(50) DEFAULT 'General',
  `type` varchar(50) DEFAULT 'Task',
  `priority` int(11) DEFAULT 4,
  `assigned_to` int(11) DEFAULT NULL,
  `schedule_type` enum('daily','weekly','monthly') NOT NULL,
  `schedule_day` int(11) DEFAULT NULL COMMENT 'Day of week (1-7) for weekly, day of month (1-31) for monthly',
  `schedule_time` time DEFAULT '09:00:00',
  `next_run_at` timestamp NOT NULL,
  `last_run_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`recurring_id`),
  KEY `assigned_to` (`assigned_to`),
  KEY `created_by` (`created_by`),
  KEY `idx_recurring_next_run` (`next_run_at`,`is_active`),
  KEY `idx_recurring_active` (`is_active`),
  CONSTRAINT `recurring_tickets_ibfk_1` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `recurring_tickets_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ saved_filters ============
CREATE TABLE IF NOT EXISTS `saved_filters` (
  `filter_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `filter_name` varchar(100) NOT NULL,
  `filter_criteria` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`filter_criteria`)),
  `is_default` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`filter_id`),
  UNIQUE KEY `unique_user_filter_name` (`user_id`,`filter_name`),
  KEY `idx_user_filters` (`user_id`,`is_default`),
  CONSTRAINT `saved_filters_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ status_transitions ============
CREATE TABLE IF NOT EXISTS `status_transitions` (
  `transition_id` int(11) NOT NULL AUTO_INCREMENT,
  `from_status` varchar(50) NOT NULL,
  `to_status` varchar(50) NOT NULL,
  `requires_comment` tinyint(1) DEFAULT 0,
  `requires_admin` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`transition_id`),
  UNIQUE KEY `unique_transition` (`from_status`,`to_status`),
  KEY `idx_from_status` (`from_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ ticket_attachments ============
CREATE TABLE IF NOT EXISTS `ticket_attachments` (
  `attachment_id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(9) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `original_filename` varchar(255) NOT NULL,
  `file_size` int(11) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `uploaded_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`attachment_id`),
  KEY `idx_attachments_ticket` (`ticket_id`),
  KEY `idx_attachments_uploaded_by` (`uploaded_by`),
  CONSTRAINT `ticket_attachments_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ ticket_comments ============
CREATE TABLE IF NOT EXISTS `ticket_comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_comment_id` int(11) DEFAULT NULL,
  `thread_depth` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `ticket_id` varchar(10) DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `comment_text` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `markdown_enabled` tinyint(1) DEFAULT 0,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `fk_comments_user_id` (`user_id`),
  KEY `idx_comments_ticket_created` (`ticket_id`,`created_at` DESC),
  KEY `idx_parent_comment` (`parent_comment_id`),
  CONSTRAINT `fk_comments_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_parent_comment` FOREIGN KEY (`parent_comment_id`) REFERENCES `ticket_comments` (`comment_id`) ON DELETE CASCADE,
  CONSTRAINT `ticket_comments_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ ticket_dependencies ============
CREATE TABLE IF NOT EXISTS `ticket_dependencies` (
  `dependency_id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(9) NOT NULL,
  `depends_on_id` varchar(9) NOT NULL,
  `dependency_type` enum('blocks','blocked_by','relates_to','duplicates') DEFAULT 'blocks',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`dependency_id`),
  UNIQUE KEY `unique_dependency` (`ticket_id`,`depends_on_id`,`dependency_type`),
  KEY `idx_ticket_id` (`ticket_id`),
  KEY `idx_depends_on_id` (`depends_on_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `ticket_dependencies_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ ticket_templates ============
CREATE TABLE IF NOT EXISTS `ticket_templates` (
  `template_id` int(11) NOT NULL AUTO_INCREMENT,
  `template_name` varchar(100) NOT NULL,
  `title_template` varchar(255) NOT NULL,
  `description_template` text NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `default_priority` int(11) DEFAULT 4,
  `created_by` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`template_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_template_name` (`template_name`),
  CONSTRAINT `ticket_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ ticket_watchers ============
CREATE TABLE IF NOT EXISTS `ticket_watchers` (
  `ticket_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`ticket_id`,`user_id`),
  KEY `idx_watcher_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ tickets ============
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` varchar(9) NOT NULL,
  `title` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `visibility` enum('public','internal','confidential') DEFAULT 'public',
  `visibility_groups` varchar(500) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'Open',
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `closed_at` timestamp NULL DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT 1 CHECK (`priority` between 1 and 6),
  `hash` varchar(64) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ticket_id` (`ticket_id`),
  UNIQUE KEY `unique_hash` (`hash`),
  KEY `fk_tickets_updated_by` (`updated_by`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_tickets_created_at` (`created_at`),
  KEY `idx_assigned_to` (`assigned_to`),
  KEY `idx_tickets_status` (`status`),
  KEY `idx_tickets_status_priority_created` (`status`,`priority`,`created_at` DESC),
  KEY `idx_tickets_visibility` (`visibility`),
  KEY `idx_tickets_category` (`category`),
  KEY `idx_tickets_type` (`type`),
  KEY `idx_tickets_priority` (`priority`),
  KEY `idx_tickets_updated_at` (`updated_at`),
  KEY `idx_tickets_created_by` (`created_by`),
  KEY `idx_tickets_assigned_to` (`assigned_to`),
  KEY `idx_tickets_status_created` (`status`,`created_at`),
  KEY `idx_tickets_assigned_status` (`assigned_to`,`status`),
  KEY `idx_tickets_visibility_status` (`visibility`,`status`),
  KEY `idx_tickets_closed_at` (`closed_at`),
  FULLTEXT KEY `ft_title_description` (`title`,`description`),
  CONSTRAINT `fk_tickets_assigned_to` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tickets_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tickets_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ user_preferences ============
CREATE TABLE IF NOT EXISTS `user_preferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `preference_key` varchar(100) NOT NULL,
  `preference_value` text DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_pref` (`user_id`,`preference_key`),
  KEY `idx_user_preferences_user_key` (`user_id`,`preference_key`),
  CONSTRAINT `user_preferences_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============ users ============
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `groups` text DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT 0,
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


SET FOREIGN_KEY_CHECKS = 1;