-- One-off migration: creates the tables backing the Telegram bot's new
-- parent-linking flow (teacher_reminder_bot, sibling repo). A parent links
-- by their child's IIN rather than needing a pre-existing `users` row +
-- `students.parent_id` set by an admin, so this is deliberately decoupled
-- from `users`/`parent_id` — a Telegram "parent" here may never have a
-- `users` row at all. Supports multiple children per Telegram account
-- (each additional child is just another telegram_parent_students row).
--
-- Requires user_telegram to already exist (see create_user_telegram_table.sql)
-- only in the sense that both are part of the same bot; no FK between them.

CREATE TABLE telegram_parents (
    telegram_id                 BIGINT NOT NULL PRIMARY KEY,
    notification_period_days    TINYINT UNSIGNED NOT NULL DEFAULT 14,
    created_at                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE telegram_parent_students (
    id            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    telegram_id   BIGINT NOT NULL,
    student_id    INT NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_telegram_student (telegram_id, student_id),
    FOREIGN KEY (telegram_id) REFERENCES telegram_parents(telegram_id),
    FOREIGN KEY (student_id) REFERENCES students(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
