--
-- Host: 127.0.0.1    Database: avaliacao_link
-- ------------------------------------------------------


--
-- Table structure for table `question_options`
--

CREATE TABLE `question_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL,
  `option_text` varchar(255) NOT NULL,
  `display_order` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_options_question` (`question_id`),
  CONSTRAINT `fk_options_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `question_options`
--


--
-- Table structure for table `questionnaire_units`
--

CREATE TABLE `questionnaire_units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `questionnaire_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `responded` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `fk_qu_questionnaire` (`questionnaire_id`),
  KEY `fk_qu_unit` (`unit_id`),
  CONSTRAINT `fk_qu_questionnaire` FOREIGN KEY (`questionnaire_id`) REFERENCES `questionnaires` (`id`),
  CONSTRAINT `fk_qu_unit` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `questionnaire_units`
--

INSERT INTO `questionnaire_units` VALUES (1,2,3,'2025-10-25 03:22:00','2025-10-27 03:22:00',1),(2,2,1,'2025-10-25 00:00:00','2025-10-31 23:59:00',1),(3,2,2,'2025-10-25 00:00:00','2025-10-31 23:59:00',1),(4,2,3,'2025-10-25 00:00:00','2025-10-31 23:59:00',0),(5,2,1,'2025-10-25 00:00:00','2025-10-27 23:59:00',0),(6,2,2,'2025-10-25 00:00:00','2025-10-27 23:59:00',0),(7,2,5,'2025-10-25 00:00:00','2025-10-27 23:59:00',1),(8,2,4,'2025-10-25 00:00:00','2025-10-27 23:59:00',1),(9,2,3,'2025-10-25 00:00:00','2025-10-27 23:59:00',0),(15,2,1,'2025-10-25 22:00:00','2025-11-01 23:59:00',0),(16,2,7,'2025-10-25 22:00:00','2025-11-01 23:59:00',0),(17,2,6,'2025-10-25 22:00:00','2025-11-01 23:59:00',0),(18,2,2,'2025-10-25 22:00:00','2025-11-01 23:59:00',0),(19,2,5,'2025-10-25 22:00:00','2025-11-01 23:59:00',0),(20,2,4,'2025-10-25 22:00:00','2025-11-01 23:59:00',0),(21,2,3,'2025-10-25 22:00:00','2025-11-01 23:59:00',0);

--
-- Table structure for table `questionnaires`
--

CREATE TABLE `questionnaires` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `questionnaires`
--

INSERT INTO `questionnaires` VALUES (2,'Pesquisa Link Dedicado - Telemedicina','Esta pesquisa avalia a qualidade do link dedicado utilizado para telemedicina em sua unidade. As respostas variam de 0 a 5, onde 0 significa \"muito insatisfeito\" e 5 significa \"muito satisfeito\". Após responder, a unidade não poderá enviar nova resposta.','2025-10-25 02:44:48',1);

--
-- Table structure for table `questions`
--

CREATE TABLE `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `section_id` int(11) NOT NULL,
  `text` varchar(500) NOT NULL,
  `display_order` int(11) NOT NULL,
  `question_type` enum('scale5','multiple_choice','text_short','text_long','nps','audit','result') NOT NULL DEFAULT 'scale5',
  PRIMARY KEY (`id`),
  KEY `fk_questions_section` (`section_id`),
  CONSTRAINT `fk_questions_section` FOREIGN KEY (`section_id`) REFERENCES `sections` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` VALUES (10,4,'A internet funciona bem durante os atendimentos, sem cair ou travar.',1,'scale5'),(16,4,'A imagem e o som das chamadas são bons, permitindo que todos se entendam com facilidade.',2,'scale5'),(19,4,'A conexão de internet é suficiente para atender todos os pacientes que usam o serviço ao mesmo tempo.',3,'scale5'),(21,5,'A qualidade da internet ajuda os profissionais a fazer os atendimentos de forma tranquila.',1,'scale5'),(22,5,'A boa conexão deixa o atendimento mais rápido e melhora o contato com os pacientes.',2,'scale5'),(23,5,'De modo geral, a internet dedicada tem funcionado bem e atende o que a unidade precisa para o dia a dia.',3,'scale5'),(42,10,'Com base na nota que você deu, conte brevemente o motivo da sua avaliação. Você pode dizer o que funcionou bem, o que pode melhorar ou dar algum exemplo do seu dia a dia.',1,'text_long');

--
-- Table structure for table `responses`
--

CREATE TABLE `responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `answer_int` int(11) DEFAULT NULL,
  `option_id` int(11) DEFAULT NULL,
  `answer_text` text DEFAULT NULL,
  `comment` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_responses_survey` (`survey_id`),
  KEY `fk_responses_question` (`question_id`),
  KEY `fk_responses_option` (`option_id`),
  CONSTRAINT `fk_responses_option` FOREIGN KEY (`option_id`) REFERENCES `question_options` (`id`),
  CONSTRAINT `fk_responses_question` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`),
  CONSTRAINT `fk_responses_survey` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `responses`
--

INSERT INTO `responses` VALUES (1,1,10,5,5,NULL,NULL,NULL),(2,1,16,4,4,NULL,NULL,NULL),(3,1,19,4,4,NULL,NULL,NULL),(4,1,21,5,5,NULL,NULL,NULL),(5,1,22,5,5,NULL,NULL,NULL),(6,1,23,4,4,NULL,NULL,NULL),(7,1,42,0,NULL,NULL,'teste',NULL),(8,2,10,3,3,NULL,NULL,NULL),(9,2,16,3,3,NULL,NULL,NULL),(10,2,19,3,3,NULL,NULL,NULL),(11,2,21,3,3,NULL,NULL,NULL),(12,2,22,3,3,NULL,NULL,NULL),(13,2,23,3,3,NULL,NULL,NULL),(14,2,42,0,NULL,NULL,'oioioi',NULL),(15,3,10,5,5,NULL,NULL,NULL),(16,3,16,3,3,NULL,NULL,NULL),(17,3,19,3,3,NULL,NULL,NULL),(18,3,21,3,3,NULL,NULL,NULL),(19,3,22,2,2,NULL,NULL,NULL),(20,3,23,2,2,NULL,NULL,NULL),(21,3,42,0,NULL,NULL,'teste teste',NULL),(22,4,10,1,1,NULL,NULL,NULL),(23,4,16,2,2,NULL,NULL,NULL),(24,4,19,2,2,NULL,NULL,NULL),(25,4,21,2,2,NULL,NULL,NULL),(26,4,22,3,3,NULL,NULL,NULL),(27,4,23,2,2,NULL,NULL,NULL),(28,4,42,0,NULL,NULL,'teste teste',NULL),(29,5,10,4,4,NULL,NULL,NULL),(30,5,16,1,1,NULL,NULL,NULL),(31,5,19,0,0,NULL,NULL,NULL),(32,5,21,5,5,NULL,NULL,NULL),(33,5,22,1,1,NULL,NULL,NULL),(34,5,23,3,3,NULL,NULL,NULL),(35,5,42,0,NULL,NULL,'teste teste teste',NULL);

--
-- Table structure for table `sections`
--

CREATE TABLE `sections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `objective` text DEFAULT NULL,
  `is_final` tinyint(1) NOT NULL DEFAULT 0,
  `display_order` int(11) NOT NULL,
  `questionnaire_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sections_questionnaire` (`questionnaire_id`),
  CONSTRAINT `fk_sections_questionnaire` FOREIGN KEY (`questionnaire_id`) REFERENCES `questionnaires` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sections`
--

INSERT INTO `sections` VALUES (4,'Conexão e Qualidade da Internet','Objetivo: Avaliar se a internet usada nas consultas funciona bem e permite comunicação clara.',0,1,2),(5,'Satisfação e Facilidade no Dia a Dia','Objetivo: Avalia como o uso da internet dedicada ajuda o trabalho e a experiência nas consultas.',0,2,2),(10,'Comentários Finais','',1,3,2);

--
-- Table structure for table `surveys`
--

CREATE TABLE `surveys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_id` int(11) NOT NULL,
  `submitted_at` datetime NOT NULL,
  `questionnaire_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_surveys_unit` (`unit_id`),
  KEY `fk_surveys_questionnaire` (`questionnaire_id`),
  CONSTRAINT `fk_surveys_questionnaire` FOREIGN KEY (`questionnaire_id`) REFERENCES `questionnaires` (`id`),
  CONSTRAINT `fk_surveys_unit` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `surveys`
--

INSERT INTO `surveys` VALUES (1,3,'2025-10-25 04:26:47',2),(2,1,'2025-10-25 05:23:22',2),(3,2,'2025-10-25 05:46:08',2),(4,4,'2025-10-25 06:54:00',2),(5,5,'2025-10-25 06:54:24',2);

--
-- Table structure for table `units`
--

CREATE TABLE `units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `responded` tinyint(1) DEFAULT 0,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `units`
--

INSERT INTO `units` VALUES (1,'Unidade Centro','centro@unidade.com',0,'2025-10-25 01:53:54'),(2,'Unidade Zona Sul','zonalink@unidade.com',0,'2025-10-25 01:53:54'),(3,'UPA Pirituba','upapirituba@teste.com.br',0,'2025-10-25 02:10:09'),(4,'UPA Lapa','upalapa@teste.com.br',0,'2025-10-25 05:36:01'),(5,'UPA Jaraguá','upajaragua@teste.com.br',0,'2025-10-25 06:30:15'),(6,'Unidade Zona Oeste','unidadezo@teste.com.br',0,'2025-10-25 22:09:11'),(7,'Unidade Zona Leste','unidadeleste@teste.com.br',0,'2025-10-25 22:12:25'),(8,'Unidade Lapa','unidadelapa@teste.com.br',0,'2025-10-25 22:19:00');


