-- Schema MySQL para a aplicação de Pesquisas
-- Execute dentro do banco já criado (USE avaliacao_link;)

-- Tabela de usuários do painel
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de unidades (quem responde a pesquisa)
CREATE TABLE IF NOT EXISTS units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  responded TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Questionários (configuráveis)
CREATE TABLE IF NOT EXISTS questionnaires (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL,
  active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seções da pesquisa (vinculadas a um questionário)
CREATE TABLE IF NOT EXISTS sections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  questionnaire_id INT NULL,
  title VARCHAR(255) NOT NULL,
  objective TEXT NULL,
  display_order INT NOT NULL,
  CONSTRAINT fk_sections_questionnaire FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Perguntas vinculadas às seções
CREATE TABLE IF NOT EXISTS questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  section_id INT NOT NULL,
  text VARCHAR(500) NOT NULL,
  display_order INT NOT NULL,
  question_type ENUM('scale5','multiple_choice','text_short','text_long','nps','audit','result') NOT NULL DEFAULT 'scale5',
  CONSTRAINT fk_questions_section FOREIGN KEY (section_id) REFERENCES sections(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Opções para perguntas de múltipla escolha/auditoria
CREATE TABLE IF NOT EXISTS question_options (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question_id INT NOT NULL,
  option_text VARCHAR(255) NOT NULL,
  display_order INT NOT NULL,
  CONSTRAINT fk_options_question FOREIGN KEY (question_id) REFERENCES questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vinculação questionário x unidade, com janela de resposta
CREATE TABLE IF NOT EXISTS questionnaire_units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  questionnaire_id INT NOT NULL,
  unit_id INT NOT NULL,
  start_date DATETIME NOT NULL,
  end_date DATETIME NOT NULL,
  responded TINYINT(1) DEFAULT 0,
  CONSTRAINT fk_qu_questionnaire FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id),
  CONSTRAINT fk_qu_unit FOREIGN KEY (unit_id) REFERENCES units(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pesquisas enviadas por unidade (uma submissão)
CREATE TABLE IF NOT EXISTS surveys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  questionnaire_id INT NOT NULL,
  unit_id INT NOT NULL,
  submitted_at DATETIME NOT NULL,
  CONSTRAINT fk_surveys_questionnaire FOREIGN KEY (questionnaire_id) REFERENCES questionnaires(id),
  CONSTRAINT fk_surveys_unit FOREIGN KEY (unit_id) REFERENCES units(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Respostas das perguntas (suporta tipos variados) + justificativa
CREATE TABLE IF NOT EXISTS responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  survey_id INT NOT NULL,
  question_id INT NOT NULL,
  -- valores por tipo
  answer_int INT NULL,          -- scale5, nps
  option_id INT NULL,           -- multiple_choice, audit
  answer_text TEXT NULL,        -- texto curto/long
  comment TEXT NULL,            -- justificativa livre
  -- compatibilidade com versão anterior (mantemos score)
  score INT NULL,
  CONSTRAINT fk_responses_survey FOREIGN KEY (survey_id) REFERENCES surveys(id),
  CONSTRAINT fk_responses_question FOREIGN KEY (question_id) REFERENCES questions(id),
  CONSTRAINT fk_responses_option FOREIGN KEY (option_id) REFERENCES question_options(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;