-- 2026-04-17
-- Estrutura base para operacoes colaborativas do ecossistema dev/workspace.
-- Objetivos:
-- 1) garantir as tabelas de incidentes, postmortems, runbooks e decisoes
-- 2) garantir as tabelas de qualidade, custos, sugestoes e snapshots diarios
-- 3) manter a execucao idempotente em ambientes que ainda nao receberam a base TIIX

CREATE TABLE IF NOT EXISTS `wpjy_dev_cost_centers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `ativo` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dev_cost_centers_slug` (`slug`),
  KEY `idx_dev_cost_centers_ativo` (`ativo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_incidents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_contrato` BIGINT UNSIGNED NULL DEFAULT NULL,
  `id_tarefa` BIGINT UNSIGNED NULL DEFAULT NULL,
  `titulo` VARCHAR(180) NOT NULL,
  `descricao` MEDIUMTEXT NULL DEFAULT NULL,
  `status` ENUM('aberto','mitigado','resolvido','cancelado') NOT NULL DEFAULT 'aberto',
  `severidade` ENUM('sev4','sev3','sev2','sev1') NOT NULL DEFAULT 'sev3',
  `started_at` DATETIME NOT NULL,
  `mitigated_at` DATETIME NULL DEFAULT NULL,
  `resolved_at` DATETIME NULL DEFAULT NULL,
  `owner_user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dev_incidents_contrato` (`id_contrato`),
  KEY `idx_dev_incidents_tarefa` (`id_tarefa`),
  KEY `idx_dev_incidents_status` (`status`),
  KEY `idx_dev_incidents_severidade` (`severidade`),
  KEY `idx_dev_incidents_owner` (`owner_user_id`),
  KEY `idx_dev_incidents_started_at` (`started_at`),
  KEY `idx_dev_incidents_contrato_status` (`id_contrato`, `status`),
  CONSTRAINT `fk_dev_incidents_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `wpjy_users` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_dev_incidents_task` FOREIGN KEY (`id_tarefa`) REFERENCES `wpjy_tarefas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_postmortems` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `incident_id` BIGINT UNSIGNED NOT NULL,
  `root_cause` MEDIUMTEXT NULL DEFAULT NULL,
  `impacto` MEDIUMTEXT NULL DEFAULT NULL,
  `acao_imediata` MEDIUMTEXT NULL DEFAULT NULL,
  `acao_preventiva` MEDIUMTEXT NULL DEFAULT NULL,
  `owner_user_id` BIGINT UNSIGNED NULL 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_dev_postmortem_incident` (`incident_id`),
  KEY `idx_dev_postmortem_owner` (`owner_user_id`),
  KEY `idx_dev_postmortem_created_at` (`created_at`),
  CONSTRAINT `fk_dev_postmortems_incident` FOREIGN KEY (`incident_id`) REFERENCES `wpjy_dev_incidents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_dev_postmortems_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `wpjy_users` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_quality_events` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_tarefa` BIGINT UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `tipo` ENUM('bug_pos_entrega','retrabalho','falha_deploy','rollback','incidente','hotfix') NOT NULL,
  `severidade` ENUM('baixa','media','alta','critica') NOT NULL DEFAULT 'media',
  `descricao` MEDIUMTEXT NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dev_quality_tarefa` (`id_tarefa`),
  KEY `idx_dev_quality_user` (`user_id`),
  KEY `idx_dev_quality_tipo` (`tipo`),
  KEY `idx_dev_quality_severidade` (`severidade`),
  KEY `idx_dev_quality_created_at` (`created_at`),
  KEY `idx_dev_quality_tarefa_tipo` (`id_tarefa`, `tipo`),
  CONSTRAINT `fk_dev_quality_events_task` FOREIGN KEY (`id_tarefa`) REFERENCES `wpjy_tarefas` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_dev_quality_events_user` FOREIGN KEY (`user_id`) REFERENCES `wpjy_users` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_runbooks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `titulo` VARCHAR(180) NOT NULL,
  `categoria` VARCHAR(80) NULL DEFAULT NULL,
  `conteudo` LONGTEXT NOT NULL,
  `ativo` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dev_runbooks_categoria` (`categoria`),
  KEY `idx_dev_runbooks_ativo` (`ativo`),
  KEY `idx_dev_runbooks_created_by` (`created_by`),
  KEY `idx_dev_runbooks_created_at` (`created_at`),
  CONSTRAINT `fk_dev_runbooks_created_by` FOREIGN KEY (`created_by`) REFERENCES `wpjy_users` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_decisions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `titulo` VARCHAR(180) NOT NULL,
  `contexto` MEDIUMTEXT NULL DEFAULT NULL,
  `decisao` MEDIUMTEXT NOT NULL,
  `consequencias` MEDIUMTEXT NULL DEFAULT NULL,
  `status` ENUM('proposta','aceita','rejeitada','substituida') NOT NULL DEFAULT 'aceita',
  `modulo_slug` VARCHAR(120) NULL DEFAULT NULL,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_dev_decisions_status` (`status`),
  KEY `idx_dev_decisions_modulo` (`modulo_slug`),
  KEY `idx_dev_decisions_created_by` (`created_by`),
  KEY `idx_dev_decisions_created_at` (`created_at`),
  CONSTRAINT `fk_dev_decisions_created_by` FOREIGN KEY (`created_by`) REFERENCES `wpjy_users` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_daily_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `data_ref` DATE NOT NULL,
  `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `equipe_id` INT NULL DEFAULT NULL,
  `horas_trabalhadas` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  `tarefas_concluidas` INT NOT NULL DEFAULT 0,
  `tarefas_bloqueadas` INT NOT NULL DEFAULT 0,
  `bugs_gerados` INT NOT NULL DEFAULT 0,
  `bugs_resolvidos` INT NOT NULL DEFAULT 0,
  `retrabalho_horas` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  `throughput` DECIMAL(8,2) NULL DEFAULT NULL,
  `lead_time_medio` DECIMAL(10,2) NULL DEFAULT NULL,
  `cycle_time_medio` DECIMAL(10,2) NULL DEFAULT NULL,
  `previsibilidade_pct` DECIMAL(5,2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dev_snapshot` (`data_ref`, `user_id`, `equipe_id`),
  KEY `idx_dev_daily_data` (`data_ref`),
  KEY `idx_dev_daily_user` (`user_id`),
  KEY `idx_dev_daily_equipe` (`equipe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_task_costing` (
  `task_id` BIGINT UNSIGNED NOT NULL,
  `cost_center_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `custo_estimado` DECIMAL(10,2) NULL DEFAULT NULL,
  `custo_real` DECIMAL(10,2) NULL DEFAULT NULL,
  `receita_relacionada` DECIMAL(10,2) NULL DEFAULT NULL,
  `margem_estimada` DECIMAL(10,2) NULL DEFAULT NULL,
  `margem_real` DECIMAL(10,2) NULL DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`task_id`),
  KEY `idx_dev_task_costing_cost_center` (`cost_center_id`),
  KEY `idx_dev_task_costing_updated_at` (`updated_at`),
  CONSTRAINT `fk_dev_task_costing_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `wpjy_dev_cost_centers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_dev_task_costing_task` FOREIGN KEY (`task_id`) REFERENCES `wpjy_tarefas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `chk_dev_task_costing_custo_estimado` CHECK ((`custo_estimado` IS NULL OR `custo_estimado` >= 0)),
  CONSTRAINT `chk_dev_task_costing_custo_real` CHECK ((`custo_real` IS NULL OR `custo_real` >= 0)),
  CONSTRAINT `chk_dev_task_costing_receita` CHECK ((`receita_relacionada` IS NULL OR `receita_relacionada` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_assignment_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `score_total` DECIMAL(8,2) NOT NULL,
  `score_skill` DECIMAL(8,2) NOT NULL,
  `score_carga` DECIMAL(8,2) NOT NULL,
  `score_qualidade` DECIMAL(8,2) NOT NULL,
  `score_disponibilidade` DECIMAL(8,2) NOT NULL,
  `score_contexto` DECIMAL(8,2) NOT NULL,
  `detalhes_json` JSON NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dev_assignment_scores_task_user` (`task_id`, `user_id`),
  KEY `idx_dev_assign_task` (`task_id`),
  KEY `idx_dev_assign_user` (`user_id`),
  KEY `idx_dev_assign_created` (`created_at`),
  CONSTRAINT `fk_dev_assignment_scores_task` FOREIGN KEY (`task_id`) REFERENCES `wpjy_tarefas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_dev_assignment_scores_user` FOREIGN KEY (`user_id`) REFERENCES `wpjy_users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dev_task_knowledge_links` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_tarefa` BIGINT UNSIGNED NOT NULL,
  `alvo_tipo` ENUM('decision','runbook','postmortem','doc','kb') NOT NULL,
  `alvo_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `url` MEDIUMTEXT NULL DEFAULT NULL,
  `titulo` VARCHAR(180) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_dev_task_knowledge_tarefa` (`id_tarefa`),
  KEY `idx_dev_task_knowledge_alvo` (`alvo_tipo`, `alvo_id`),
  CONSTRAINT `fk_dev_task_knowledge_task` FOREIGN KEY (`id_tarefa`) REFERENCES `wpjy_tarefas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;