-- ============================================================
-- 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`;

-- ─── Users (admin account) ───────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`              VARCHAR(255)    NOT NULL,
    `email`             VARCHAR(255)    NOT NULL UNIQUE,
    `email_verified_at` TIMESTAMP       NULL DEFAULT NULL,
    `password`          VARCHAR(255)    NOT NULL,
    `remember_token`    VARCHAR(100)    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;

-- ─── Cache ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `cache` (
    `key`        VARCHAR(255) NOT NULL,
    `value`      MEDIUMTEXT   NOT NULL,
    `expiration` INT          NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache_locks` (
    `key`        VARCHAR(255) NOT NULL,
    `owner`      VARCHAR(255) NOT NULL,
    `expiration` INT          NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── 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 Admin Account ──────────────────────────────────────
-- Password: Admin@1234  (bcrypt hash — change before production!)
INSERT INTO `users` (`name`, `email`, `password`, `created_at`, `updated_at`) VALUES
(
  'Admin',
  'admin@esp32.local',
  '$2y$12$kq1V1y6v9F7rLblIK/UjeOoYBJzUkbkQpBbRmIPxE/HA3VEe32YDa',
  NOW(),
  NOW()
);

-- ─── 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()
);