CREATE TABLE IF NOT EXISTS briefscore_clients (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS briefscore_accounts (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS briefscore_creatives (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS briefscore_questions (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    position TINYINT NOT NULL,
    label    TEXT NOT NULL,
    type     ENUM('text','score') NOT NULL DEFAULT 'text'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS briefscore_questionnaires (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    token        VARCHAR(64) NOT NULL UNIQUE,
    status       ENUM('pending','submitted') DEFAULT 'pending',
    sent_to      VARCHAR(255),
    sent_at      DATETIME,
    submitted_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS briefscore_responses (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    questionnaire_id INT NOT NULL,
    client_id        INT,
    account_id       INT,
    FOREIGN KEY (questionnaire_id) REFERENCES briefscore_questionnaires(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id)        REFERENCES briefscore_clients(id)        ON DELETE SET NULL,
    FOREIGN KEY (account_id)       REFERENCES briefscore_accounts(id)       ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS briefscore_response_answers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    response_id INT NOT NULL,
    question_id INT NOT NULL,
    answer      TEXT,
    FOREIGN KEY (response_id) REFERENCES briefscore_responses(id)  ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES briefscore_questions(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO briefscore_questions (position, label, type) VALUES
(1, 'Como avalias a clareza do briefing recebido?', 'score'),
(2, 'A informação fornecida foi suficiente para iniciares o trabalho criativo?', 'score'),
(3, 'O timing para a entrega foi adequado?', 'score'),
(4, 'Tens sugestões para melhorar futuras passagens de briefing?', 'text');