--
-- Host: localhost    Database: tjsmedical_losalisos
-- ------------------------------------------------------


--
-- Table structure for table `audios_informe`
--

CREATE TABLE `audios_informe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `informe_id` int(11) DEFAULT NULL COMMENT 'ID del informe asociado (puede ser NULL si es audio independiente)',
  `estudio_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ID del estudio en Orthanc',
  `usuario_id` int(11) NOT NULL COMMENT 'ID del usuario que grabó el audio',
  `tipo_grabacion` enum('simple','sincronizada') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Tipo de grabación utilizada',
  `nombre_archivo` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombre del archivo de audio',
  `nombre_original` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nombre original del archivo',
  `ruta_archivo` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Ruta relativa del archivo en el servidor',
  `duracion_segundos` decimal(10,2) DEFAULT NULL COMMENT 'Duración del audio en segundos',
  `tamano_bytes` bigint(20) DEFAULT NULL COMMENT 'Tamaño del archivo en bytes',
  `tipo_mime` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'audio/webm' COMMENT 'Tipo MIME del archivo',
  `datos_sincronizacion` json DEFAULT NULL COMMENT 'Datos de sincronización palabra-tiempo del módulo sync-editor',
  `transcripcion_texto` longtext COLLATE utf8mb4_unicode_ci COMMENT 'Transcripción completa del audio',
  `calidad_audio` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Calidad del audio (alta, media, baja)',
  `dispositivo_grabacion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Información del dispositivo de grabación',
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_modificacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `activo` tinyint(1) DEFAULT '1' COMMENT 'Indica si el audio está activo o fue eliminado',
  PRIMARY KEY (`id`),
  KEY `idx_informe_id` (`informe_id`),
  KEY `idx_estudio_id` (`estudio_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_tipo_grabacion` (`tipo_grabacion`),
  KEY `idx_fecha_creacion` (`fecha_creacion`),
  KEY `idx_activo` (`activo`),
  CONSTRAINT `audios_informe_ibfk_1` FOREIGN KEY (`informe_id`) REFERENCES `informes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `audios_informe_ibfk_2` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `configuracion`
--

CREATE TABLE `configuracion` (
  `clave` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `valor` text COLLATE utf8mb4_unicode_ci,
  `descripcion` text COLLATE utf8mb4_unicode_ci,
  `fecha_modificacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`clave`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `estudios`
--

CREATE TABLE `estudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `paciente_id` int(11) DEFAULT NULL,
  `orthanc_study_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_id_pacs` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_name_pacs` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_date` date DEFAULT NULL,
  `study_time` time DEFAULT NULL,
  `modality` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_description` text COLLATE utf8mb4_unicode_ci,
  `accession_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referring_physician` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `series_count` int(11) DEFAULT '0',
  `instances_count` int(11) DEFAULT '0',
  `study_instance_uid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `viewer_url` text COLLATE utf8mb4_unicode_ci,
  `status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'COMPLETADO',
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_actualizacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_estudios_paciente` (`paciente_id`),
  KEY `idx_estudios_orthanc` (`orthanc_study_id`),
  KEY `idx_estudios_pacs_patient` (`patient_id_pacs`),
  KEY `idx_estudios_fecha` (`study_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `informe_audios`
--

CREATE TABLE `informe_audios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `informe_id` int(11) NOT NULL,
  `archivo_path` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `duracion` decimal(10,2) DEFAULT NULL,
  `sync_data` json DEFAULT NULL,
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audios_informe` (`informe_id`),
  CONSTRAINT `informe_audios_ibfk_1` FOREIGN KEY (`informe_id`) REFERENCES `informes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `informes`
--

CREATE TABLE `informes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `estudio_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ID del estudio en Orthanc',
  `usuario_id` int(11) NOT NULL COMMENT 'ID del médico que crea el informe',
  `patient_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID del paciente',
  `patient_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nombre del paciente',
  `modality` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Modalidad del estudio (CT, MR, etc.)',
  `study_description` text COLLATE utf8mb4_unicode_ci COMMENT 'Descripción del estudio',
  `titulo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Título del informe',
  `contenido_html` longtext COLLATE utf8mb4_unicode_ci COMMENT 'Contenido HTML del informe desde TinyMCE',
  `contenido_texto` text COLLATE utf8mb4_unicode_ci COMMENT 'Contenido en texto plano para búsquedas',
  `estado` enum('borrador','finalizado','revisado','firmado') COLLATE utf8mb4_unicode_ci DEFAULT 'borrador',
  `version` int(11) DEFAULT '1' COMMENT 'Versión del informe para control de cambios',
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_modificacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fecha_finalizacion` timestamp NULL DEFAULT NULL COMMENT 'Fecha cuando se finalizó el informe',
  `notas_revision` text COLLATE utf8mb4_unicode_ci COMMENT 'Notas de revisión o comentarios',
  `study_instance_uid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Study Instance UID del estudio DICOM',
  `study_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Study ID del estudio DICOM',
  `series_instance_uid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Series Instance UID (opcional)',
  `accession_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Accession Number del estudio',
  `pacs_series_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID de la serie en Orthanc donde se almacenó el PDF. Usado para eliminar la serie completa al actualizar el informe.',
  `pdf_path` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Ruta relativa del PDF del informe para visualización/descarga desde portal del paciente',
  `pacs_instance_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pacs_study_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_estudio_id` (`estudio_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_patient_id` (`patient_id`),
  KEY `idx_estado` (`estado`),
  KEY `idx_fecha_creacion` (`fecha_creacion`),
  KEY `idx_informes_estudio` (`estudio_id`),
  KEY `idx_informes_patient` (`patient_id`),
  KEY `idx_informes_modality` (`modality`),
  KEY `idx_informes_estado` (`estado`),
  KEY `idx_informes_fecha` (`fecha_creacion`),
  KEY `idx_informes_study_instance_uid` (`study_instance_uid`),
  KEY `idx_informes_study_id` (`study_id`),
  KEY `idx_informes_accession_number` (`accession_number`),
  KEY `idx_pacs_series_id` (`pacs_series_id`),
  CONSTRAINT `informes_ibfk_1` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=751 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `informes_historial`
--

CREATE TABLE `informes_historial` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `informe_id` int(11) NOT NULL,
  `version_anterior` int(11) NOT NULL,
  `contenido_html_anterior` longtext COLLATE utf8mb4_unicode_ci,
  `estado_anterior` enum('borrador','finalizado','revisado','firmado') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `usuario_modificacion` int(11) NOT NULL,
  `fecha_cambio` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `motivo_cambio` text COLLATE utf8mb4_unicode_ci COMMENT 'Razón del cambio de versión',
  PRIMARY KEY (`id`),
  KEY `usuario_modificacion` (`usuario_modificacion`),
  KEY `idx_informe_id` (`informe_id`),
  KEY `idx_fecha_cambio` (`fecha_cambio`),
  CONSTRAINT `informes_historial_ibfk_1` FOREIGN KEY (`informe_id`) REFERENCES `informes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `informes_historial_ibfk_2` FOREIGN KEY (`usuario_modificacion`) REFERENCES `usuarios` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `mobile_sessions`
--

CREATE TABLE `mobile_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `study_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` datetime NOT NULL,
  `status` enum('active','connected','expired') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `last_activity` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `patient_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `modality` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_description` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_study_id` (`study_id`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `mobile_sessions_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=185 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `mobile_temp_images`
--

CREATE TABLE `mobile_temp_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `temp_path` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('pending','accepted','rejected') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `pacientes`
--

CREATE TABLE `pacientes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_interno` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `idpaciente` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nombre` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `telefono` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `domicilio` text COLLATE utf8mb4_unicode_ci,
  `search_enabled_types` enum('idpaciente','id_interno','ambos') COLLATE utf8mb4_unicode_ci DEFAULT 'idpaciente' COMMENT 'Tipos de ID habilitados para búsqueda: idpaciente (solo PACS), id_interno (solo interno), ambos (cualquiera)',
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_actualizacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_interno` (`id_interno`),
  KEY `idx_pacientes_search_types` (`search_enabled_types`),
  KEY `idx_pacientes_idpaciente` (`idpaciente`),
  KEY `idx_pacientes_nombre` (`nombre`),
  KEY `idx_pacientes_email` (`email`(250))
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `plantillas`
--

CREATE TABLE `plantillas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ID único de la plantilla (ej: rx_torax)',
  `nombre` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombre descriptivo de la plantilla',
  `contenido_html` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Contenido HTML de la plantilla',
  `usuario_id` int(11) DEFAULT NULL COMMENT 'ID del usuario que creó la plantilla (NULL para plantillas del sistema)',
  `activo` tinyint(1) DEFAULT '1' COMMENT 'Indica si la plantilla está activa',
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `fecha_modificacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última modificación',
  PRIMARY KEY (`id`),
  UNIQUE KEY `template_id` (`template_id`),
  KEY `idx_template_id` (`template_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_activo` (`activo`),
  KEY `idx_fecha_creacion` (`fecha_creacion`),
  CONSTRAINT `plantillas_ibfk_1` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `sesiones`
--

CREATE TABLE `sesiones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usuario_id` int(11) NOT NULL,
  `token_sesion` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_expiracion` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activa` tinyint(1) DEFAULT '1',
  `ultima_actividad` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token_sesion` (`token_sesion`),
  KEY `idx_sesiones_token` (`token_sesion`),
  KEY `idx_sesiones_usuario` (`usuario_id`),
  CONSTRAINT `sesiones_ibfk_1` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2234 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `study_antecedents`
--

CREATE TABLE `study_antecedents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `study_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci COMMENT 'Notas y antecedentes médicos',
  `created_by` int(11) NOT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_study_id` (`study_id`),
  KEY `idx_created_by` (`created_by`),
  CONSTRAINT `study_antecedents_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=278 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `study_antecedents_files`
--

CREATE TABLE `study_antecedents_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `antecedent_id` int(11) NOT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_type` enum('image','document','camera_capture','image_upload','file_upload') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_size` int(11) NOT NULL,
  `mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uploaded_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_antecedent_id` (`antecedent_id`),
  KEY `idx_file_type` (`file_type`),
  CONSTRAINT `study_antecedents_files_ibfk_1` FOREIGN KEY (`antecedent_id`) REFERENCES `study_antecedents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `study_assignments`
--

CREATE TABLE `study_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `study_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `assigned_by` int(11) NOT NULL,
  `assigned_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `patient_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_date` date DEFAULT NULL,
  `study_time` time DEFAULT NULL,
  `modality` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_description` text COLLATE utf8mb4_unicode_ci,
  `accession_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referring_physician` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `study_instance_uid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `series_count` int(11) DEFAULT '0',
  `instances_count` int(11) DEFAULT '0',
  `viewer_url` text COLLATE utf8mb4_unicode_ci,
  `orthanc_study_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_birth_date` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `patient_sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `institution_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_study_user` (`study_id`,`user_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_assigned_by` (`assigned_by`),
  CONSTRAINT `study_assignments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `study_assignments_ibfk_2` FOREIGN KEY (`assigned_by`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=824 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `study_subassignments`
--

CREATE TABLE `study_subassignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `study_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `main_user_id` int(11) NOT NULL COMMENT 'Usuario principal al que se asignó originalmente el estudio',
  `subassigned_to_user_id` int(11) NOT NULL COMMENT 'Usuario hijo al que se derivó el estudio',
  `assigned_by_user_id` int(11) NOT NULL COMMENT 'Usuario que realizó la derivación',
  `subassigned_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha y hora de la derivación',
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `idx_study_main` (`study_id`,`main_user_id`),
  KEY `idx_subassigned_to` (`subassigned_to_user_id`),
  KEY `idx_main_user` (`main_user_id`),
  KEY `idx_assigned_by` (`assigned_by_user_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_assigned_by_user` FOREIGN KEY (`assigned_by_user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_main_user` FOREIGN KEY (`main_user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_subassigned_user` FOREIGN KEY (`subassigned_to_user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `study_subassignments_ibfk_1` FOREIGN KEY (`main_user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `study_subassignments_ibfk_2` FOREIGN KEY (`subassigned_to_user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `study_subassignments_ibfk_3` FOREIGN KEY (`assigned_by_user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `system_permissions`
--

CREATE TABLE `system_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permission_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `permission_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permission_key` (`permission_key`),
  KEY `idx_category` (`category`),
  KEY `idx_permission_key` (`permission_key`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `user_audit_logs`
--

CREATE TABLE `user_audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `action` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_user_id` int(11) DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_target_user_id` (`target_user_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `user_audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_audit_logs_ibfk_2` FOREIGN KEY (`target_user_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `user_sessions`
--

CREATE TABLE `user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `expires_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_token` (`session_token`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_user_expires` (`user_id`,`expires_at`),
  CONSTRAINT `user_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `usuarios`
--

CREATE TABLE `usuarios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `apellido` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `telefono` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `matricula_profesional` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verificado` tinyint(1) DEFAULT '0',
  `token_verificacion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fecha_creacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fecha_actualizacion` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `activo` tinyint(1) DEFAULT '1',
  `nivel` enum('root','admin','user') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'user',
  `padre_id` int(11) DEFAULT NULL,
  `especialidad` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permisos` json DEFAULT NULL,
  `ultimo_acceso` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dicom_aetitle` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Application Entity Title (AE Title) para DICOM',
  `dicom_puerto` int(11) DEFAULT NULL COMMENT 'Puerto para conexión DICOM',
  `dicom_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Dirección IP para conexión DICOM',
  `dicom_viewer` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'UDV',
  `instituciones_permitidas` json DEFAULT NULL COMMENT 'Array JSON con nombres de instituciones permitidas para filtrar estudios',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_usuarios_email` (`email`),
  KEY `idx_usuarios_matricula` (`matricula_profesional`),
  KEY `idx_nivel` (`nivel`),
  KEY `idx_padre_id` (`padre_id`),
  KEY `idx_activo` (`activo`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Temporary table structure for view `usuarios_con_jerarquia`
--

/*!50001 DROP VIEW IF EXISTS `usuarios_con_jerarquia`*/;
/*!50001 CREATE VIEW `usuarios_con_jerarquia` AS SELECT 
 1 AS `id`,
 1 AS `nombre`,
 1 AS `apellido`,
 1 AS `email`,
 1 AS `telefono`,
 1 AS `matricula_profesional`,
 1 AS `nivel`,
 1 AS `padre_id`,
 1 AS `especialidad`,
 1 AS `activo`,
 1 AS `permisos`,
 1 AS `created_at`,
 1 AS `padre_nombre`,
 1 AS `padre_apellido`,
 1 AS `padre_email`,
 1 AS `padre_nivel`,
 1 AS `jerarquia_completa`,
 1 AS `hijos_count`,
 1 AS `dependientes_count`*/;

--
-- Dumping routines for database 'tjsmedical_losalisos'
--
/*!50003 DROP FUNCTION IF EXISTS `GetUserHierarchy` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GetUserHierarchy`(`userId` INT) RETURNS text CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    READS SQL DATA
    DETERMINISTIC
BEGIN
        DECLARE hierarchy TEXT DEFAULT '';
        DECLARE currentUserId INT DEFAULT userId;
        DECLARE parentId INT DEFAULT NULL;
        DECLARE userName VARCHAR(200) DEFAULT '';
        
        WHILE currentUserId IS NOT NULL DO
            SELECT padre_id, CONCAT(nombre, ' ', apellido) 
            INTO parentId, userName
            FROM usuarios 
            WHERE id = currentUserId AND activo = 1;
            
            IF hierarchy = '' THEN
                SET hierarchy = userName;
            ELSE
                SET hierarchy = CONCAT(userName, ' > ', hierarchy);
            END IF;
            
            SET currentUserId = parentId;
        END WHILE;
        
        RETURN hierarchy;
    END ;;
DELIMITER ;

--
-- Final view structure for view `usuarios_con_jerarquia`
--

/*!50001 DROP VIEW IF EXISTS `usuarios_con_jerarquia`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `usuarios_con_jerarquia` AS select `u`.`id` AS `id`,`u`.`nombre` AS `nombre`,`u`.`apellido` AS `apellido`,`u`.`email` AS `email`,`u`.`telefono` AS `telefono`,`u`.`matricula_profesional` AS `matricula_profesional`,`u`.`nivel` AS `nivel`,`u`.`padre_id` AS `padre_id`,`u`.`especialidad` AS `especialidad`,`u`.`activo` AS `activo`,`u`.`permisos` AS `permisos`,`u`.`created_at` AS `created_at`,`p`.`nombre` AS `padre_nombre`,`p`.`apellido` AS `padre_apellido`,`p`.`email` AS `padre_email`,`p`.`nivel` AS `padre_nivel`,`GetUserHierarchy`(`u`.`id`) AS `jerarquia_completa`,(select count(0) from `usuarios` `h` where ((`h`.`padre_id` = `u`.`id`) and (`h`.`activo` = 1))) AS `hijos_count`,(select count(0) from `usuarios` `d` where ((`d`.`padre_id` = `u`.`id`) and (`d`.`activo` = 1))) AS `dependientes_count` from (`usuarios` `u` left join `usuarios` `p` on((`u`.`padre_id` = `p`.`id`))) where (`u`.`activo` = 1) order by `u`.`nivel` desc,`u`.`nombre` */;


