-- --------------------------------------------------------
-- Servidor:                     127.0.0.1
-- Versão do servidor:           10.6.7-MariaDB - mariadb.org binary distribution
-- OS do Servidor:               Win64
-- HeidiSQL Versão:              12.5.0.6677
-- --------------------------------------------------------



-- Copiando estrutura do banco de dados para forms
CREATE DATABASE IF NOT EXISTS `forms` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `forms`;

-- Copiando estrutura para tabela forms.form
CREATE TABLE IF NOT EXISTS `form` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `share` tinyint(4) DEFAULT NULL,
  `posted` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_form_users` (`author_id`),
  CONSTRAINT `FK_form_users` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.form: ~2 rows (aproximadamente)
INSERT INTO `form` (`id`, `name`, `description`, `starts`, `ends`, `author_id`, `share`, `posted`) VALUES
	(34, 'teste', '01', '2020-01-01 00:00:00', '2025-01-01 00:00:00', 19, 1, 1),
	(35, 'teste', '01', '2020-01-01 00:00:00', '2025-01-01 00:00:00', 1, 1, 1);

-- Copiando estrutura para tabela forms.form_options
CREATE TABLE IF NOT EXISTS `form_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) DEFAULT NULL,
  `option_text` varchar(255) DEFAULT NULL,
  `option_true` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_form_options_form_questions` (`question_id`),
  CONSTRAINT `FK_form_options_form_questions` FOREIGN KEY (`question_id`) REFERENCES `form_questions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.form_options: ~14 rows (aproximadamente)
INSERT INTO `form_options` (`id`, `question_id`, `option_text`, `option_true`) VALUES
	(65, 75, 'um', 0),
	(66, 75, 'dois', 0),
	(67, 75, 'tres', 0),
	(68, 76, 'um', 0),
	(69, 76, '2', 0),
	(70, 76, '3', 0),
	(71, 76, '4', 0),
	(72, 77, 'vdd', 1),
	(73, 77, '1', 0),
	(74, 78, 'Sim', 0),
	(75, 78, 'Não', 0),
	(76, 79, 'Paçoca', 0),
	(77, 79, 'Rolha', 0),
	(78, 79, 'Gibi', 0);

-- Copiando estrutura para tabela forms.form_questions
CREATE TABLE IF NOT EXISTS `form_questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) DEFAULT NULL,
  `question_type` varchar(50) DEFAULT NULL,
  `question_text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `form_id_question_type_question_text` (`form_id`,`question_type`,`question_text`),
  KEY `FK_form_questions_form` (`form_id`),
  CONSTRAINT `FK_form_questions_form` FOREIGN KEY (`form_id`) REFERENCES `form` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.form_questions: ~6 rows (aproximadamente)
INSERT INTO `form_questions` (`id`, `form_id`, `question_type`, `question_text`) VALUES
	(75, 34, 'radio', 'escolha'),
	(76, 34, 'single', 'teste'),
	(74, 34, 'text', 'teste'),
	(77, 34, 'truefalse', 'sss'),
	(79, 35, 'radio', 'Quais você prefere?'),
	(78, 35, 'single', 'Você gosta de paçoca?');

-- Copiando estrutura para tabela forms.form_share
CREATE TABLE IF NOT EXISTS `form_share` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) DEFAULT NULL,
  `external_user_id` int(11) DEFAULT NULL,
  `access_hash` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_form_share_form` (`form_id`),
  KEY `FK_form_share_users_share` (`external_user_id`),
  CONSTRAINT `FK_form_share_form` FOREIGN KEY (`form_id`) REFERENCES `form` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK_form_share_users_share` FOREIGN KEY (`external_user_id`) REFERENCES `users_share` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.form_share: ~1 rows (aproximadamente)
INSERT INTO `form_share` (`id`, `form_id`, `external_user_id`, `access_hash`) VALUES
	(2, 34, 1, '555');

-- Copiando estrutura para tabela forms.response
CREATE TABLE IF NOT EXISTS `response` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `external_user_id` int(11) DEFAULT NULL,
  `response_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_response_form` (`form_id`),
  KEY `FK_response_users` (`user_id`),
  CONSTRAINT `FK_response_form` FOREIGN KEY (`form_id`) REFERENCES `form` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK_response_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.response: ~5 rows (aproximadamente)
INSERT INTO `response` (`id`, `form_id`, `user_id`, `external_user_id`, `response_date`) VALUES
	(44, 34, NULL, 1, '2024-08-02 16:15:00'),
	(45, 34, NULL, 1, '2024-08-02 16:15:37'),
	(46, 35, 1, NULL, '2024-08-03 01:06:36'),
	(47, 35, 1, NULL, '2024-08-03 01:06:44'),
	(48, 34, NULL, 1, '2024-08-03 02:01:37');

-- Copiando estrutura para tabela forms.response_answers
CREATE TABLE IF NOT EXISTS `response_answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `response_id` int(11) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  `option_id` int(11) DEFAULT NULL,
  `answer_text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_response_answers_response` (`response_id`),
  KEY `FK_response_answers_form_questions` (`question_id`),
  KEY `FK_response_answers_form_options` (`option_id`),
  CONSTRAINT `FK_response_answers_form_options` FOREIGN KEY (`option_id`) REFERENCES `form_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_response_answers_form_questions` FOREIGN KEY (`question_id`) REFERENCES `form_questions` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_response_answers_response` FOREIGN KEY (`response_id`) REFERENCES `response` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.response_answers: ~13 rows (aproximadamente)
INSERT INTO `response_answers` (`id`, `response_id`, `question_id`, `option_id`, `answer_text`) VALUES
	(93, 44, 74, NULL, 'teste'),
	(94, 44, 75, 65, NULL),
	(95, 44, 76, 68, NULL),
	(96, 44, 77, 72, NULL),
	(97, 45, 74, NULL, 'teste'),
	(98, 45, 76, 68, NULL),
	(99, 45, 76, 71, NULL),
	(100, 45, 77, 72, NULL),
	(101, 46, 78, 74, NULL),
	(102, 46, 79, 77, NULL),
	(103, 47, 78, 75, NULL),
	(104, 47, 79, 78, NULL),
	(105, 48, 74, NULL, 'teste');

-- Copiando estrutura para tabela forms.users_groups
CREATE TABLE IF NOT EXISTS `users_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `read` tinyint(4) DEFAULT NULL,
  `create` tinyint(4) DEFAULT NULL,
  `edit` tinyint(4) DEFAULT NULL,
  `delete` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.users_groups: ~2 rows (aproximadamente)
INSERT INTO `users_groups` (`id`, `name`, `read`, `create`, `edit`, `delete`) VALUES
	(1, 'teste', NULL, NULL, NULL, NULL),
	(2, '222', NULL, NULL, NULL, NULL);

-- Copiando estrutura para tabela forms.users_share
CREATE TABLE IF NOT EXISTS `users_share` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `surname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- Copiando dados para a tabela forms.users_share: ~1 rows (aproximadamente)
INSERT INTO `users_share` (`id`, `username`, `name`, `surname`) VALUES
	(1, '12345612310', 'teste', NULL);

