-- Base estrutural para o modulo de CID:
-- 1) repositorio estruturado completo (CID-10 e CID-O)
-- 2) catalogo operacional usado pelo sistema
-- 3) logs de sincronizacao/importacao

CREATE TABLE IF NOT EXISTS `wpjy_cids_catalogo` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `codigo` varchar(20) NOT NULL,
  `descricao` varchar(255) NOT NULL,
  `versao` varchar(20) NOT NULL DEFAULT 'CID-10',
  `origem` varchar(100) DEFAULT NULL,
  `ativo` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_cids_catalogo_versao_codigo` (`versao`, `codigo`),
  KEY `idx_cids_catalogo_codigo` (`codigo`),
  KEY `idx_cids_catalogo_descricao` (`descricao`),
  KEY `idx_cids_catalogo_ativo` (`ativo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_cids_import_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `source_type` varchar(50) NOT NULL,
  `source_ref` varchar(255) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_hash` char(64) DEFAULT NULL,
  `versao` varchar(20) NOT NULL DEFAULT 'CID-10',
  `preferencia` varchar(30) DEFAULT NULL,
  `total_lidos` int unsigned NOT NULL DEFAULT '0',
  `total_processados` int unsigned NOT NULL DEFAULT '0',
  `total_inseridos` int unsigned NOT NULL DEFAULT '0',
  `total_atualizados` int unsigned NOT NULL DEFAULT '0',
  `total_ignorados` int unsigned NOT NULL DEFAULT '0',
  `executed_by` bigint unsigned DEFAULT NULL,
  `details_json` longtext,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cids_import_logs_versao` (`versao`),
  KEY `idx_cids_import_logs_source_type` (`source_type`),
  KEY `idx_cids_import_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_repositorio_cid10_capitulos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `numcap` int NOT NULL,
  `catinic` varchar(10) DEFAULT NULL,
  `catfim` varchar(10) DEFAULT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `descrabrev` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_repo_cid10_capitulos_numcap` (`numcap`),
  KEY `idx_repo_cid10_capitulos_catinic` (`catinic`),
  KEY `idx_repo_cid10_capitulos_catfim` (`catfim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_repositorio_cid10_grupos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `catinic` varchar(10) NOT NULL,
  `catfim` varchar(10) NOT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `descrabrev` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_repo_cid10_grupos_faixa` (`catinic`, `catfim`),
  KEY `idx_repo_cid10_grupos_catinic` (`catinic`),
  KEY `idx_repo_cid10_grupos_catfim` (`catfim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_repositorio_cid10_categorias` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cat` varchar(10) NOT NULL,
  `classif` char(1) DEFAULT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `descrabrev` varchar(255) DEFAULT NULL,
  `refer` varchar(20) DEFAULT NULL,
  `excluidos` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_repo_cid10_categorias_cat` (`cat`),
  KEY `idx_repo_cid10_categorias_classif` (`classif`),
  KEY `idx_repo_cid10_categorias_refer` (`refer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_repositorio_cid10_subcategorias` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `subcat` varchar(10) NOT NULL,
  `classif` char(1) DEFAULT NULL,
  `restrsexo` char(1) DEFAULT NULL,
  `causaobito` char(1) DEFAULT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `descrabrev` varchar(255) DEFAULT NULL,
  `refer` varchar(20) DEFAULT NULL,
  `excluidos` text,
  `cat3` varchar(10) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_repo_cid10_subcategorias_subcat` (`subcat`),
  KEY `idx_repo_cid10_subcategorias_cat3` (`cat3`),
  KEY `idx_repo_cid10_subcategorias_classif` (`classif`),
  KEY `idx_repo_cid10_subcategorias_restrsexo` (`restrsexo`),
  KEY `idx_repo_cid10_subcategorias_causaobito` (`causaobito`),
  KEY `idx_repo_cid10_subcategorias_refer` (`refer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_repositorio_cido_grupos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `catinic` varchar(20) NOT NULL,
  `catfim` varchar(20) NOT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `refer` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_repo_cido_grupos_faixa` (`catinic`, `catfim`),
  KEY `idx_repo_cido_grupos_refer` (`refer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_repositorio_cido_categorias` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `cat` varchar(20) NOT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `refer` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_repo_cido_categorias_cat` (`cat`),
  KEY `idx_repo_cido_categorias_refer` (`refer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;