CREATE DATABASE IF NOT EXISTS escolinha_diario
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE escolinha_diario;

CREATE TABLE IF NOT EXISTS students (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(140) NOT NULL,
    birth_date DATE NULL,
    email VARCHAR(180) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX students_active_name_index (active, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS actions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL,
    color VARCHAR(20) NOT NULL DEFAULT '#0f766e',
    icon VARCHAR(40) NOT NULL DEFAULT 'check',
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX actions_active_sort_index (active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    student_id BIGINT UNSIGNED NOT NULL,
    action_id BIGINT UNSIGNED NOT NULL,
    note TEXT NULL,
    occurred_at DATETIME NOT NULL,
    email_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    email_sent_at DATETIME NULL,
    email_error TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX student_logs_student_date_index (student_id, occurred_at),
    INDEX student_logs_action_date_index (action_id, occurred_at),
    CONSTRAINT student_logs_student_id_fk FOREIGN KEY (student_id) REFERENCES students(id),
    CONSTRAINT student_logs_action_id_fk FOREIGN KEY (action_id) REFERENCES actions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `key` VARCHAR(120) NOT NULL,
    `value` TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY settings_key_unique (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO actions (name, color, icon, sort_order)
SELECT 'Chegou', '#0f766e', 'log-in', 10
WHERE NOT EXISTS (SELECT 1 FROM actions WHERE name = 'Chegou');

INSERT INTO actions (name, color, icon, sort_order)
SELECT 'Dormiu', '#4f46e5', 'moon', 20
WHERE NOT EXISTS (SELECT 1 FROM actions WHERE name = 'Dormiu');

INSERT INTO actions (name, color, icon, sort_order)
SELECT 'Acordou', '#f59e0b', 'sun', 30
WHERE NOT EXISTS (SELECT 1 FROM actions WHERE name = 'Acordou');

INSERT INTO actions (name, color, icon, sort_order)
SELECT 'Almoçou', '#db2777', 'utensils', 40
WHERE NOT EXISTS (SELECT 1 FROM actions WHERE name = 'Almoçou');

INSERT INTO actions (name, color, icon, sort_order)
SELECT 'Saiu', '#dc2626', 'log-out', 50
WHERE NOT EXISTS (SELECT 1 FROM actions WHERE name = 'Saiu');