-- ===============================
-- Schema for olymp_bot (MySQL 8+)
-- ===============================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET sql_notes = 0;

-- ---------- subjects ----------
-- Справочник предметов
CREATE TABLE IF NOT EXISTS subjects (
                                        id   SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                        code VARCHAR(64)  NOT NULL,        -- 'math','physics', ...
    name VARCHAR(255) NOT NULL,        -- "Математика", "Физика"
    PRIMARY KEY (id),
    UNIQUE KEY uq_subject_code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------- olympiads ----------
-- Олимпиады/события (одна олимпиада = один предмет)
CREATE TABLE IF NOT EXISTS olympiads (
                                         id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                         subject_id  SMALLINT UNSIGNED NOT NULL,
                                         text        VARCHAR(1024)  NOT NULL,   -- текст анонса/название (сюда же можно вставлять URL)
    starts_at   DATETIME       NOT NULL,   -- в UTC
    has_time    TINYINT(1)     NOT NULL DEFAULT 1,  -- 1: есть точное время; 0: только дата (не шлём за час)
    created_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_olympiads_subject_start (subject_id, starts_at),
    CONSTRAINT fk_olymp_subject
    FOREIGN KEY (subject_id) REFERENCES subjects(id)
                                                                  ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------- contacts ----------
-- Подписчики (пользователи бота)
CREATE TABLE IF NOT EXISTS contacts (
                                        id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                        telegram_id  BIGINT          NOT NULL,   -- chat_id
                                        username     VARCHAR(255) DEFAULT NULL,  -- без @
    created_at   DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_contact_telegram (telegram_id),
    KEY idx_contacts_username (username)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------- subscriptions ----------
-- Кто на что подписан (многие-к-одному: контакт -> предмет)
CREATE TABLE IF NOT EXISTS subscriptions (
                                             contact_id  BIGINT UNSIGNED   NOT NULL,
                                             subject_id  SMALLINT UNSIGNED NOT NULL,
                                             PRIMARY KEY (contact_id, subject_id),
    CONSTRAINT fk_sub_contact
    FOREIGN KEY (contact_id) REFERENCES contacts(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_sub_subject
    FOREIGN KEY (subject_id) REFERENCES subjects(id)
    ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------- deliveries ----------
-- Очередь/лог отправок (этапы: 7d, 2d, 1d, 1h)
CREATE TABLE IF NOT EXISTS deliveries (
                                          id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                          contact_id    BIGINT UNSIGNED NOT NULL,
                                          olympiad_id   BIGINT UNSIGNED NOT NULL,
                                          stage         ENUM('7d','2d','1d','1h') NOT NULL,
    scheduled_at  DATETIME        NOT NULL,   -- когда отправить (UTC)
    sent_at       DATETIME     DEFAULT NULL,  -- когда отправили
    status        ENUM('scheduled','sent','failed') NOT NULL DEFAULT 'scheduled',
    error_msg     VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_delivery_once (contact_id, olympiad_id, stage),
    KEY idx_deliveries_due (status, scheduled_at),
    CONSTRAINT fk_deliv_contact
    FOREIGN KEY (contact_id)  REFERENCES contacts(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_deliv_olymp
    FOREIGN KEY (olympiad_id) REFERENCES olympiads(id)
    ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET sql_notes = 1;

-- ---------- seed (необязательный) ----------
-- Базовые предметы: математика и физика
INSERT IGNORE INTO subjects (code, name) VALUES
  ('math','Математика'),
  ('physics','Физика');