backup.sql

-- MySQL dump 10.13  Distrib 8.0.44, for Win64 (x86_64)
--
-- Host: localhost    Database: fraude_projeto
-- ------------------------------------------------------
-- Server version	8.0.44

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `alerta_fraude`
--

DROP TABLE IF EXISTS `alerta_fraude`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `alerta_fraude` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_transacao` int NOT NULL,
  `tipo_alerta` enum('valor_atipico','frequencia_elevada','horario_atipico','localizacao_incomum','dispositivo_nao_cadastrado','multiplas_tentativas','regra_combinada') NOT NULL,
  `score_risco` decimal(5,2) NOT NULL,
  `data_alerta` datetime DEFAULT CURRENT_TIMESTAMP,
  `status_alerta` enum('aberto','investigado','confirmado') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_alerta_transacoes` (`id_transacao`),
  KEY `idx_alerta_id_transacao` (`id_transacao`),
  KEY `idx_alerta_tipo_score` (`tipo_alerta`,`score_risco`),
  CONSTRAINT `alerta_fraude_ibfk_1` FOREIGN KEY (`id_transacao`) REFERENCES `transacoes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `alerta_fraude`
--

LOCK TABLES `alerta_fraude` WRITE;
/*!40000 ALTER TABLE `alerta_fraude` DISABLE KEYS */;
INSERT INTO `alerta_fraude` VALUES (1,2,'valor_atipico',78.50,'2026-01-12 10:17:23','investigado'),(2,4,'frequencia_elevada',65.00,'2026-01-12 10:17:23','aberto'),(3,7,'horario_atipico',82.30,'2026-01-12 10:17:23','investigado'),(4,13,'localizacao_incomum',90.00,'2026-01-12 10:17:23','confirmado'),(5,3,'horario_atipico',88.50,'2026-02-23 09:45:10','investigado');
/*!40000 ALTER TABLE `alerta_fraude` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_log_alerta` BEFORE UPDATE ON `alerta_fraude` FOR EACH ROW BEGIN
    IF OLD.status_alerta <> NEW.status_alerta THEN
        INSERT INTO log_alerta
        VALUES (
            OLD.id,
            OLD.status_alerta,
            NEW.status_alerta,
            NOW()
        );
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `clientes`
--

DROP TABLE IF EXISTS `clientes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clientes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tipo_pessoa` enum('PF','PJ') NOT NULL,
  `data_nascimento` date NOT NULL,
  `score_credito` decimal(10,2) DEFAULT NULL,
  `renda` decimal(10,2) DEFAULT NULL,
  `status_cliente` enum('ativo','suspenso','bloqueado','em_investigacao','encerrado') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clientes_tipo_pessoa` (`tipo_pessoa`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clientes`
--

LOCK TABLES `clientes` WRITE;
/*!40000 ALTER TABLE `clientes` DISABLE KEYS */;
INSERT INTO `clientes` VALUES (1,'PF','1988-05-12',720.50,5500.00,'em_investigacao'),(2,'PF','1995-09-03',680.00,4200.00,'encerrado'),(3,'PF','1979-11-20',810.00,12000.00,'ativo'),(4,'PJ','2005-01-01',NULL,45000.00,'ativo'),(5,'PF','1992-07-18',640.00,3800.00,'em_investigacao'),(6,'PF','1985-03-30',590.00,3200.00,'suspenso'),(7,'PJ','2010-01-01',NULL,80000.00,'ativo'),(8,'PF','2000-12-10',710.00,NULL,'ativo'),(9,'PF','1998-04-22',660.00,4800.00,'bloqueado'),(10,'PF','1975-08-15',830.00,15000.00,'ativo');
/*!40000 ALTER TABLE `clientes` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_log_status_cliente` BEFORE UPDATE ON `clientes` FOR EACH ROW BEGIN
    IF OLD.status_cliente <> NEW.status_cliente THEN
        INSERT INTO log_status_cliente
        VALUES (
            OLD.id,
            OLD.status_cliente,
            NEW.status_cliente,
            NOW()
        );
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_backup_cliente` BEFORE DELETE ON `clientes` FOR EACH ROW BEGIN
    INSERT INTO clientes_excluidos
    VALUES (
        OLD.id,
        OLD.tipo_pessoa,
        OLD.data_nascimento,
        OLD.score_credito,
        OLD.renda,
        OLD.status_cliente,
        NOW()
    );
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `clientes_excluidos`
--

DROP TABLE IF EXISTS `clientes_excluidos`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clientes_excluidos` (
  `id` int DEFAULT NULL,
  `tipo_pessoa` varchar(10) DEFAULT NULL,
  `data_nascimento` datetime DEFAULT NULL,
  `score_credito` decimal(10,2) DEFAULT NULL,
  `renda` decimal(10,2) DEFAULT NULL,
  `status_cliente` varchar(20) DEFAULT NULL,
  `data_exclusao` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clientes_excluidos`
--

LOCK TABLES `clientes_excluidos` WRITE;
/*!40000 ALTER TABLE `clientes_excluidos` DISABLE KEYS */;
/*!40000 ALTER TABLE `clientes_excluidos` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `contas`
--

DROP TABLE IF EXISTS `contas`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `contas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_cliente` int NOT NULL,
  `tipo_conta` enum('conta_corrente','conta_popanca','conta_virtual','conta_empresarial','cartao_credito') NOT NULL,
  `data_abertura` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `limite_credito` decimal(10,2) NOT NULL,
  `status_conta` enum('ativa','inativa','bloqueada','suspensa','encerrada','aguardando_aprovacao') NOT NULL DEFAULT 'aguardando_aprovacao',
  PRIMARY KEY (`id`),
  KEY `idx_contas_id_cliente` (`id_cliente`),
  CONSTRAINT `contas_ibfk_1` FOREIGN KEY (`id_cliente`) REFERENCES `clientes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `contas`
--

LOCK TABLES `contas` WRITE;
/*!40000 ALTER TABLE `contas` DISABLE KEYS */;
INSERT INTO `contas` VALUES (1,1,'conta_corrente','2026-01-12 10:12:38',5000.00,'ativa'),(2,2,'conta_virtual','2026-01-12 10:12:38',3000.00,'ativa'),(3,3,'cartao_credito','2026-01-12 10:12:38',15000.00,'ativa'),(4,4,'conta_empresarial','2026-01-12 10:12:38',50000.00,'ativa'),(5,5,'conta_corrente','2026-01-12 10:12:38',4000.00,'ativa'),(6,6,'conta_corrente','2026-01-12 10:12:38',2500.00,'suspensa'),(7,7,'conta_empresarial','2026-01-12 10:12:38',90000.00,'ativa'),(8,8,'conta_virtual','2026-01-12 10:12:38',3500.00,'ativa'),(9,9,'cartao_credito','2026-01-12 10:12:38',6000.00,'bloqueada'),(10,10,'conta_corrente','2026-01-12 10:12:38',8000.00,'ativa');
/*!40000 ALTER TABLE `contas` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_log_status_conta` BEFORE UPDATE ON `contas` FOR EACH ROW BEGIN
    IF OLD.status_conta <> NEW.status_conta THEN
        INSERT INTO log_status_conta
        VALUES (
            OLD.id,
            OLD.status_conta,
            NEW.status_conta,
            NOW()
        );
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `investigacoes`
--

DROP TABLE IF EXISTS `investigacoes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `investigacoes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_alerta` int NOT NULL,
  `analista_responsavel` varchar(60) NOT NULL,
  `resultado` enum('fraude','falso_positivo') NOT NULL,
  `data_fechamento` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_investigacoes_alerta` (`id_alerta`),
  KEY `idx_investigacoes_resultado` (`resultado`),
  CONSTRAINT `investigacoes_ibfk_1` FOREIGN KEY (`id_alerta`) REFERENCES `alerta_fraude` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `investigacoes`
--

LOCK TABLES `investigacoes` WRITE;
/*!40000 ALTER TABLE `investigacoes` DISABLE KEYS */;
INSERT INTO `investigacoes` VALUES (1,1,'Ana Souza','falso_positivo','2024-01-15 00:00:00'),(2,3,'Carlos Lima','fraude','2024-01-18 00:00:00'),(3,4,'Marina Alves','fraude','2024-01-20 00:00:00'),(4,5,'Ana Souza','fraude',NULL);
/*!40000 ALTER TABLE `investigacoes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `investigacoes_regras`
--

DROP TABLE IF EXISTS `investigacoes_regras`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `investigacoes_regras` (
  `id_investigacao` int NOT NULL,
  `id_regra` int NOT NULL,
  PRIMARY KEY (`id_investigacao`,`id_regra`),
  KEY `idx_regras_invest_regras` (`id_regra`),
  KEY `idx_ir_id_regra` (`id_regra`),
  CONSTRAINT `investigacoes_regras_ibfk_1` FOREIGN KEY (`id_investigacao`) REFERENCES `investigacoes` (`id`),
  CONSTRAINT `investigacoes_regras_ibfk_2` FOREIGN KEY (`id_regra`) REFERENCES `regras_risco` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `investigacoes_regras`
--

LOCK TABLES `investigacoes_regras` WRITE;
/*!40000 ALTER TABLE `investigacoes_regras` DISABLE KEYS */;
INSERT INTO `investigacoes_regras` VALUES (2,1),(3,1),(2,3),(3,4);
/*!40000 ALTER TABLE `investigacoes_regras` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `log_alerta`
--

DROP TABLE IF EXISTS `log_alerta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `log_alerta` (
  `id_alerta` int DEFAULT NULL,
  `status_antigo` varchar(20) DEFAULT NULL,
  `status_novo` varchar(20) DEFAULT NULL,
  `data_alteracao` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `log_alerta`
--

LOCK TABLES `log_alerta` WRITE;
/*!40000 ALTER TABLE `log_alerta` DISABLE KEYS */;
/*!40000 ALTER TABLE `log_alerta` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `log_status_cliente`
--

DROP TABLE IF EXISTS `log_status_cliente`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `log_status_cliente` (
  `id_cliente` int DEFAULT NULL,
  `status_antigo` varchar(20) DEFAULT NULL,
  `status_novo` varchar(20) DEFAULT NULL,
  `data_alteracao` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `log_status_cliente`
--

LOCK TABLES `log_status_cliente` WRITE;
/*!40000 ALTER TABLE `log_status_cliente` DISABLE KEYS */;
/*!40000 ALTER TABLE `log_status_cliente` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `log_status_conta`
--

DROP TABLE IF EXISTS `log_status_conta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `log_status_conta` (
  `id_conta` int DEFAULT NULL,
  `status_antigo` varchar(20) DEFAULT NULL,
  `status_novo` varchar(20) DEFAULT NULL,
  `data_alteracao` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `log_status_conta`
--

LOCK TABLES `log_status_conta` WRITE;
/*!40000 ALTER TABLE `log_status_conta` DISABLE KEYS */;
/*!40000 ALTER TABLE `log_status_conta` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `regras_risco`
--

DROP TABLE IF EXISTS `regras_risco`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `regras_risco` (
  `id` int NOT NULL AUTO_INCREMENT,
  `descricao` varchar(255) NOT NULL,
  `valor_limite` decimal(10,2) DEFAULT NULL,
  `tipo_regra` enum('valor','frequencia','horario','localizacao','dispositivo','tentativas','combinada') NOT NULL,
  `ativo` enum('ativo','inativo') NOT NULL,
  `nivel_risco` enum('baixo','medio','alto') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_regras_tipo_regra` (`tipo_regra`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `regras_risco`
--

LOCK TABLES `regras_risco` WRITE;
/*!40000 ALTER TABLE `regras_risco` DISABLE KEYS */;
INSERT INTO `regras_risco` VALUES (1,'Transa├º├úo acima de R$ 5.000',5000.00,'valor','ativo','alto'),(2,'M├║ltiplas transa├º├Áes em curto per├¡odo',NULL,'frequencia','ativo','medio'),(3,'Transa├º├úo fora do hor├írio padr├úo',NULL,'horario','ativo','medio'),(4,'Localiza├º├úo diferente do padr├úo do cliente',NULL,'localizacao','ativo','alto');
/*!40000 ALTER TABLE `regras_risco` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `transacoes`
--

DROP TABLE IF EXISTS `transacoes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `transacoes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_conta` int NOT NULL,
  `data_hora` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valor` decimal(10,2) NOT NULL,
  `tipo_transacao` enum('pix','cartao_debito','cartao_credito','boleto','dinheiro') NOT NULL,
  `canal` enum('online','fisico') NOT NULL,
  `localizacao` varchar(60) DEFAULT NULL,
  `status_transacao` enum('pendente','concluida','rejeitada','estornada') NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_transacoes_conta` (`id_conta`),
  KEY `idx_transacoes_id_conta` (`id_conta`),
  CONSTRAINT `transacoes_ibfk_1` FOREIGN KEY (`id_conta`) REFERENCES `contas` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `transacoes`
--

LOCK TABLES `transacoes` WRITE;
/*!40000 ALTER TABLE `transacoes` DISABLE KEYS */;
INSERT INTO `transacoes` VALUES (1,1,'2026-01-12 10:16:34',250.00,'pix','online','SP','concluida'),(2,1,'2026-01-12 10:16:34',4200.00,'cartao_debito','fisico','SP','concluida'),(3,2,'2026-01-12 10:16:34',120.00,'pix','online','RJ','concluida'),(4,3,'2026-01-12 10:16:34',8900.00,'cartao_credito','online','SP','pendente'),(5,3,'2026-01-12 10:16:34',300.00,'boleto','online',NULL,'concluida'),(6,4,'2026-01-12 10:16:34',25000.00,'boleto','online','SP','concluida'),(7,5,'2026-01-12 10:16:34',3800.00,'pix','online','MG','pendente'),(8,5,'2026-01-12 10:16:34',150.00,'pix','online','MG','concluida'),(9,6,'2026-01-12 10:16:34',900.00,'cartao_debito','fisico','SP','rejeitada'),(10,7,'2026-01-12 10:16:34',45000.00,'boleto','online','SP','concluida'),(11,8,'2026-01-12 10:16:34',200.00,'pix','online',NULL,'concluida'),(12,8,'2026-01-12 10:16:34',180.00,'pix','online',NULL,'concluida'),(13,9,'2026-01-12 10:16:34',5200.00,'cartao_credito','online','RJ','estornada'),(14,10,'2026-01-12 10:16:34',600.00,'pix','online','PR','concluida'),(16,5,'2026-02-23 09:34:02',8000.00,'cartao_debito','online','SP','concluida');
/*!40000 ALTER TABLE `transacoes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary view structure for view `vw_alertas_abertos`
--

DROP TABLE IF EXISTS `vw_alertas_abertos`;
/*!50001 DROP VIEW IF EXISTS `vw_alertas_abertos`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `vw_alertas_abertos` AS SELECT 
 1 AS `id_alerta`,
 1 AS `id_transacao`,
 1 AS `tipo_alerta`,
 1 AS `score_risco`,
 1 AS `valor`,
 1 AS `tipo_transacao`,
 1 AS `canal`,
 1 AS `localizacao`,
 1 AS `data_hora`*/;
SET character_set_client = @saved_cs_client;

--
-- Temporary view structure for view `vw_efetividade_regras`
--

DROP TABLE IF EXISTS `vw_efetividade_regras`;
/*!50001 DROP VIEW IF EXISTS `vw_efetividade_regras`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `vw_efetividade_regras` AS SELECT 
 1 AS `tipo_regra`,
 1 AS `qtd_ocorrencias`*/;
SET character_set_client = @saved_cs_client;

--
-- Temporary view structure for view `vw_perfil_cliente`
--

DROP TABLE IF EXISTS `vw_perfil_cliente`;
/*!50001 DROP VIEW IF EXISTS `vw_perfil_cliente`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `vw_perfil_cliente` AS SELECT 
 1 AS `id_cliente`,
 1 AS `tipo_pessoa`,
 1 AS `score_credito`,
 1 AS `renda`,
 1 AS `status_cliente`,
 1 AS `id_conta`,
 1 AS `tipo_conta`,
 1 AS `limite_credito`*/;
SET character_set_client = @saved_cs_client;

--
-- Temporary view structure for view `vw_ranking_risco_clientes`
--

DROP TABLE IF EXISTS `vw_ranking_risco_clientes`;
/*!50001 DROP VIEW IF EXISTS `vw_ranking_risco_clientes`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `vw_ranking_risco_clientes` AS SELECT 
 1 AS `id_cliente`,
 1 AS `tipo_pessoa`,
 1 AS `qtd_alertas`,
 1 AS `score_medio_risco`*/;
SET character_set_client = @saved_cs_client;

--
-- Temporary view structure for view `vw_transacoes_risco`
--

DROP TABLE IF EXISTS `vw_transacoes_risco`;
/*!50001 DROP VIEW IF EXISTS `vw_transacoes_risco`*/;
SET @saved_cs_client     = @@character_set_client;
/*!50503 SET character_set_client = utf8mb4 */;
/*!50001 CREATE VIEW `vw_transacoes_risco` AS SELECT 
 1 AS `id_transacao`,
 1 AS `id_conta`,
 1 AS `valor`,
 1 AS `tipo_transacao`,
 1 AS `canal`,
 1 AS `localizacao`,
 1 AS `data_hora`,
 1 AS `tipo_alerta`,
 1 AS `score_risco`,
 1 AS `status_alerta`*/;
SET character_set_client = @saved_cs_client;

--
-- Dumping routines for database 'fraude_projeto'
--
/*!50003 DROP PROCEDURE IF EXISTS `alterarStatusCliente` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `alterarStatusCliente`(IN p_id INT, IN p_status_cliente VARCHAR(20))
BEGIN
	UPDATE clientes
    SET status_cliente = p_status_cliente
    WHERE id = p_id;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `confirmarFraude` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `confirmarFraude`(
	IN p_id_investigacao INT
)
BEGIN
	DECLARE v_id_alerta INT;
	DECLARE v_id_transacao INT;
	DECLARE v_id_conta INT;
	DECLARE v_id_cliente INT;

	-- pega alerta
	SELECT id_alerta INTO v_id_alerta
	FROM investigacoes
	WHERE id = p_id_investigacao;

	-- pega transação
	SELECT id_transacao INTO v_id_transacao
	FROM alerta_fraude
	WHERE id = v_id_alerta;

	-- pega conta
	SELECT id_conta INTO v_id_conta
	FROM transacoes
	WHERE id = v_id_transacao;

	-- pega cliente
	SELECT id_cliente INTO v_id_cliente
	FROM contas
	WHERE id = v_id_conta;

	-- atualiza investigação
	UPDATE investigacoes
	SET resultado = 'fraude',
	    data_fechamento = NOW()
	WHERE id = p_id_investigacao;

	-- atualiza alerta
	UPDATE alerta_fraude
	SET status_alerta = 'confirmado'
	WHERE id = v_id_alerta;

	-- bloqueia cliente
	UPDATE clientes
	SET status_cliente = 'bloqueado'
	WHERE id = v_id_cliente;

	-- reduz limite da conta
	UPDATE contas
	SET limite_credito = limite_credito * 0.5
	WHERE id = v_id_conta;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `deletarTransacao` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `deletarTransacao`(IN p_id INT)
BEGIN
	DELETE FROM transacoes
    WHERE id = p_id;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `inserirAlertaFraude` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserirAlertaFraude`(
	IN p_id_transacao INT,
	IN p_tipo_alerta ENUM('valor_atipico', 'frequencia_elevada', 'horario_atipico', 'localizacao_incomum', 'dispositivo_nao_cadastrado', 'multiplas_tentativas', 'regra_combinada'),
    IN p_score_risco DECIMAL(5,2),
    IN p_status_alerta ENUM('aberto', 'investigado', 'confirmado'))
BEGIN
	INSERT INTO alerta_fraude(id_transacao, tipo_alerta, score_risco, status_alerta)  VALUES
	(p_id_transacao, p_tipo_alerta, p_score_risco, p_status_alerta);
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `inserirTransacao` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `inserirTransacao`(IN p_id_conta INT, IN p_valor DECIMAL(10,2),
    IN p_tipo_transacao ENUM('pix', 'cartao_debito', 'cartao_credito', 'boleto', 'dinheiro'), IN p_canal ENUM('online', 'fisico'),
    IN p_localizacao VARCHAR(60), IN p_status_transacao ENUM('pendente', 'concluida', 'rejeitada', 'estornada'))
BEGIN
	INSERT INTO transacoes (id_conta, valor, tipo_transacao, canal, localizacao, status_transacao) VALUES
	(p_id_conta, p_valor, p_tipo_transacao, p_canal, p_localizacao, p_status_transacao);
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `listarTransacoesSuspeitas` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `listarTransacoesSuspeitas`(IN p_condicao VARCHAR(20))
BEGIN
	SELECT *
    FROM transacoes t
    JOIN alerta_fraude al
		ON t.id = al.id_transacao
    WHERE status_alerta = p_condicao;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `reduzirLimite` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `reduzirLimite`(IN p_id INT, IN p_percentual_reducao DECIMAL(10,2))
BEGIN
	UPDATE contas
    SET limite_credito = limite_credito - (limite_credito*p_percentual_reducao/100)
    WHERE id = p_id;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `registrarAlertaInvestigacao` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `registrarAlertaInvestigacao`(
	IN p_id_transacao INT,
	IN p_tipo_alerta ENUM(
		'valor_atipico','frequencia_elevada','horario_atipico',
		'localizacao_incomum','dispositivo_nao_cadastrado',
		'multiplas_tentativas','regra_combinada'
	),
	IN p_score_risco DECIMAL(5,2),
	IN p_status_alerta ENUM('aberto','investigado','confirmado'),
    IN p_analista VARCHAR(60)    
)
BEGIN

	DECLARE erro BOOLEAN DEFAULT FALSE;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET erro = TRUE;
    
	START TRANSACTION;

	-- Cria Alerta de fraude
	INSERT INTO alerta_fraude (id_transacao, tipo_alerta, score_risco, status_alerta) 
							  VALUES(p_id_transacao, p_tipo_alerta, p_score_risco, p_status_alerta);
							  
	-- Cria investigação
	INSERT INTO investigacoes (id_alerta, analista_responsavel)
							  VALUES(LAST_INSERT_ID(), p_analista);
                              
	-- Lógica de decisão
    IF erro THEN
		ROLLBACK;
	ELSE
		COMMIT;
	END IF;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `registrarInvestigacao` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `registrarInvestigacao`(
	IN p_id_alerta INT,
	IN p_analista VARCHAR(60)
)
BEGIN
	INSERT INTO investigacoes (id_alerta, analista_responsavel)
	VALUES (p_id_alerta, p_analista);

	UPDATE alerta_fraude
	SET status_alerta = 'investigado'
	WHERE id = p_id_alerta;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Final view structure for view `vw_alertas_abertos`
--

/*!50001 DROP VIEW IF EXISTS `vw_alertas_abertos`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_alertas_abertos` AS select `af`.`id` AS `id_alerta`,`af`.`id_transacao` AS `id_transacao`,`af`.`tipo_alerta` AS `tipo_alerta`,`af`.`score_risco` AS `score_risco`,`t`.`valor` AS `valor`,`t`.`tipo_transacao` AS `tipo_transacao`,`t`.`canal` AS `canal`,`t`.`localizacao` AS `localizacao`,`t`.`data_hora` AS `data_hora` from (`alerta_fraude` `af` join `transacoes` `t` on((`af`.`id_transacao` = `t`.`id`))) where (`af`.`status_alerta` = 'aberto') */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `vw_efetividade_regras`
--

/*!50001 DROP VIEW IF EXISTS `vw_efetividade_regras`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_efetividade_regras` AS select `r`.`tipo_regra` AS `tipo_regra`,count(`ir`.`id_regra`) AS `qtd_ocorrencias` from ((`regras_risco` `r` join `investigacoes_regras` `ir` on((`r`.`id` = `ir`.`id_regra`))) join `investigacoes` `i` on((`ir`.`id_investigacao` = `i`.`id`))) where (`i`.`resultado` = 'fraude') group by `r`.`tipo_regra` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `vw_perfil_cliente`
--

/*!50001 DROP VIEW IF EXISTS `vw_perfil_cliente`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_perfil_cliente` AS select `cl`.`id` AS `id_cliente`,`cl`.`tipo_pessoa` AS `tipo_pessoa`,`cl`.`score_credito` AS `score_credito`,`cl`.`renda` AS `renda`,`cl`.`status_cliente` AS `status_cliente`,`ct`.`id` AS `id_conta`,`ct`.`tipo_conta` AS `tipo_conta`,`ct`.`limite_credito` AS `limite_credito` from (`clientes` `cl` left join `contas` `ct` on((`cl`.`id` = `ct`.`id_cliente`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `vw_ranking_risco_clientes`
--

/*!50001 DROP VIEW IF EXISTS `vw_ranking_risco_clientes`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_ranking_risco_clientes` AS select `cl`.`id` AS `id_cliente`,`cl`.`tipo_pessoa` AS `tipo_pessoa`,count(`af`.`id`) AS `qtd_alertas`,round(avg(`af`.`score_risco`),2) AS `score_medio_risco` from (((`clientes` `cl` join `contas` `ct` on((`cl`.`id` = `ct`.`id_cliente`))) join `transacoes` `t` on((`ct`.`id` = `t`.`id_conta`))) join `alerta_fraude` `af` on((`t`.`id` = `af`.`id_transacao`))) group by `cl`.`id`,`cl`.`tipo_pessoa` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `vw_transacoes_risco`
--

/*!50001 DROP VIEW IF EXISTS `vw_transacoes_risco`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_0900_ai_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `vw_transacoes_risco` AS select `t`.`id` AS `id_transacao`,`t`.`id_conta` AS `id_conta`,`t`.`valor` AS `valor`,`t`.`tipo_transacao` AS `tipo_transacao`,`t`.`canal` AS `canal`,`t`.`localizacao` AS `localizacao`,`t`.`data_hora` AS `data_hora`,`af`.`tipo_alerta` AS `tipo_alerta`,`af`.`score_risco` AS `score_risco`,`af`.`status_alerta` AS `status_alerta` from (`transacoes` `t` left join `alerta_fraude` `af` on((`t`.`id` = `af`.`id_transacao`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-03-26 10:02:00