-- encoding: utf8mb4
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS schema_migrations (
                                                 version     VARCHAR(50) PRIMARY KEY,
    applied_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contacts (
                                        id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                        telegram_id  BIGINT UNSIGNED NOT NULL,
                                        username     VARCHAR(32)     NULL,
    first_name   VARCHAR(255)    NULL,
    last_name    VARCHAR(255)    NULL,
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_contacts_telegram_id (telegram_id),
    UNIQUE KEY uq_contacts_username    (username)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS events (
                                      id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                      text         TEXT            NOT NULL,
                                      repeat_days  VARCHAR(32)     NULL,  -- хранится как строка/число в текстовом виде
    created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS event_times (
                                           id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                                           event_id   BIGINT UNSIGNED NOT NULL,
                                           remind_at  DATETIME        NOT NULL,  -- сравнение до минуты (код добавляет ':00' секунд)
                                           PRIMARY KEY (id),
    UNIQUE KEY uq_event_time (event_id, remind_at),
    KEY idx_remind_at (remind_at),
    CONSTRAINT fk_event_times_event
    FOREIGN KEY (event_id) REFERENCES events(id)
    ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS event_contacts (
                                              event_id   BIGINT UNSIGNED NOT NULL,
                                              contact_id BIGINT UNSIGNED NOT NULL,
                                              PRIMARY KEY (event_id, contact_id),
    KEY idx_event_contacts_contact (contact_id),
    CONSTRAINT fk_event_contacts_event
    FOREIGN KEY (event_id)   REFERENCES events(id)   ON DELETE CASCADE,
    CONSTRAINT fk_event_contacts_contact
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;