-- One-off migration: creates the nps_responses table, used by the parent-facing
-- NPS survey (parent dashboard) and surfaced on the Analytics page.
--
-- IMPORTANT: this file contains Cyrillic text — force the client to
-- utf8mb4, otherwise the mysql CLI's default charset (often latin1) will
-- double-encode it into mojibake:
--
--   docker compose exec -T db mysql --default-character-set=utf8mb4 \
--       -u"$(grep '^DB_USER=' .env | cut -d '=' -f2-)" \
--       -p"$(grep '^DB_PASS=' .env | cut -d '=' -f2-)" \
--       "$(grep '^DB_NAME=' .env | cut -d '=' -f2-)" \
--       < scripts/create_nps_responses_table.sql

CREATE TABLE nps_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    parent_user_id BIGINT UNSIGNED NOT NULL,
    score TINYINT NOT NULL,
    comment VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (parent_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
