-- ============================================================
-- ESP32 Smart Dashboard — Reference SQL
-- Generated for MySQL 8.x / MariaDB
-- ============================================================

CREATE DATABASE IF NOT EXISTS `esp32_dashboard`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `esp32_dashboard`;

-- ─── Jobs ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `jobs` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue`        VARCHAR(255)    NOT NULL,
    `payload`      LONGTEXT        NOT NULL,
    `attempts`     TINYINT UNSIGNED NOT NULL,
    `reserved_at`  INT UNSIGNED    NULL DEFAULT NULL,
    `available_at` INT UNSIGNED    NOT NULL,
    `created_at`   INT UNSIGNED    NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `job_batches` (
    `id`             VARCHAR(255) NOT NULL,
    `name`           VARCHAR(255) NOT NULL,
    `total_jobs`     INT          NOT NULL,
    `pending_jobs`   INT          NOT NULL,
    `failed_jobs`    INT          NOT NULL,
    `failed_job_ids` LONGTEXT     NOT NULL,
    `options`        MEDIUMTEXT   NULL DEFAULT NULL,
    `cancelled_at`   INT          NULL DEFAULT NULL,
    `created_at`     INT          NOT NULL,
    `finished_at`    INT          NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid`       VARCHAR(255)    NOT NULL UNIQUE,
    `connection` TEXT            NOT NULL,
    `queue`      TEXT            NOT NULL,
    `payload`    LONGTEXT        NOT NULL,
    `exception`  LONGTEXT        NOT NULL,
    `failed_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Devices ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `devices` (
    `id`               BIGINT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`             VARCHAR(100)       NOT NULL DEFAULT 'ESP32 Device',
    `goal`             VARCHAR(255)       NULL DEFAULT NULL,
    `weather`          VARCHAR(50)        NULL DEFAULT NULL,
    `quote`            VARCHAR(255)       NULL DEFAULT NULL,
    `brightness`       TINYINT UNSIGNED   NOT NULL DEFAULT 80,
    `animation`        VARCHAR(50)        NOT NULL DEFAULT 'happy',
    `refresh`          SMALLINT UNSIGNED  NOT NULL DEFAULT 30,
    `current_ip`       VARCHAR(45)        NULL DEFAULT NULL,
    `wifi_strength`    TINYINT            NULL DEFAULT NULL COMMENT 'dBm value',
    `firmware_version` VARCHAR(20)        NULL DEFAULT NULL,
    `last_seen`        TIMESTAMP          NULL DEFAULT NULL,
    `created_at`       TIMESTAMP          NULL DEFAULT NULL,
    `updated_at`       TIMESTAMP          NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Seed Default Device ─────────────────────────────────────
INSERT INTO `devices` (`name`, `goal`, `weather`, `quote`, `brightness`, `animation`, `refresh`, `firmware_version`, `created_at`, `updated_at`) VALUES
(
  'Ebad',
  'Finish Laravel',
  '37°C',
  'Keep Learning',
  80,
  'happy',
  30,
  'v1.0.0',
  NOW(),
  NOW()
);
