-- ============================================================
--  LIS Portal — MySQL Schema + Seed Data
--  Import via cPanel → phpMyAdmin → Import tab
--  Tested on MySQL 5.7+ / MariaDB 10.3+
-- ============================================================

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

-- ── migrations ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `migrations` (
  `id`        int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `migration` varchar(255) NOT NULL,
  `batch`     int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
  ('2025_01_01_000001_create_devices_table', 1),
  ('2025_01_01_000002_create_device_schedules_table', 1),
  ('2025_01_01_000003_create_send_logs_table', 1),
  ('2025_01_01_000004_create_sessions_table', 1),
  ('2025_01_01_000020_add_coordinates_to_devices', 1);

-- ── devices ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `devices` (
  `id`             bigint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name`           varchar(100)    NOT NULL,
  `ip_address`     varchar(45)     NOT NULL,
  `port`           int             NOT NULL DEFAULT 9527,
  `width_px`       int             NOT NULL DEFAULT 104,
  `height_px`      int             NOT NULL DEFAULT 208,
  `location`       varchar(200)    NULL,
  `latitude`       decimal(10,6)   NULL,
  `longitude`      decimal(10,6)   NULL,
  `highway`        varchar(100)    NULL,
  `chainage`       varchar(20)     NULL,
  `notes`          text            NULL,
  `is_active`      tinyint(1)      NOT NULL DEFAULT 1,
  `last_status`    varchar(20)     NULL,
  `last_sent_mode` varchar(10)     NULL,
  `last_sent_at`   datetime        NULL,
  `last_seen_at`   datetime        NULL,
  `created_at`     datetime        NULL,
  `updated_at`     datetime        NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `devices`
  (`name`,`ip_address`,`port`,`width_px`,`height_px`,`location`,`latitude`,`longitude`,`highway`,`chainage`,`is_active`,`created_at`,`updated_at`)
VALUES
  ('LIS KM8.1 NB','121.120.39.73', 9527,104,208,'KM8.1 Northbound, USJ–Seafield Toll Plaza',3.048400,101.575600,'LDP Northbound','KM8.1',1,NOW(),NOW()),
  ('LIS KM6.1 NB','121.120.194.67',9527,104,208,'KM6.1 Northbound, USJ–Seafield Toll Plaza',3.039600,101.589100,'LDP Northbound','KM6.1',1,NOW(),NOW());

-- ── device_schedules ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `device_schedules` (
  `id`                    bigint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `device_id`             bigint UNSIGNED NOT NULL,
  `weekday_morning_open`  time NULL DEFAULT '06:00:00',
  `weekday_morning_close` time NULL DEFAULT '10:00:00',
  `weekday_evening_open`  time NULL DEFAULT '16:00:00',
  `weekday_evening_close` time NULL DEFAULT '20:00:00',
  `weekend_open`          time NULL DEFAULT '10:00:00',
  `weekend_close`         time NULL DEFAULT '22:00:00',
  `is_active`             tinyint(1) NOT NULL DEFAULT 1,
  `created_at`            datetime NULL,
  `updated_at`            datetime NULL,
  CONSTRAINT `fk_ds_device`
    FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed schedules for both devices (Isnin–Jumaat + Weekend)
INSERT INTO `device_schedules`
  (`device_id`,`weekday_morning_open`,`weekday_morning_close`,`weekday_evening_open`,`weekday_evening_close`,`weekend_open`,`weekend_close`,`is_active`,`created_at`,`updated_at`)
SELECT `id`,'06:00:00','10:00:00','16:00:00','20:00:00','10:00:00','22:00:00',1,NOW(),NOW()
FROM `devices`;

-- ── send_logs ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `send_logs` (
  `id`           bigint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `device_id`    bigint UNSIGNED NULL,
  `device_name`  varchar(100)    NULL,
  `mode`         varchar(10)     NOT NULL,
  `status`       varchar(10)     NOT NULL DEFAULT 'ok',
  `message`      varchar(500)    NULL,
  `duration_ms`  int             NULL,
  `triggered_by` varchar(20)     NOT NULL DEFAULT 'manual',
  `created_at`   datetime        NULL,
  `updated_at`   datetime        NULL,
  CONSTRAINT `fk_sl_device`
    FOREIGN KEY (`device_id`) REFERENCES `devices`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── sessions (Laravel file sessions — table not used but created) ──
CREATE TABLE IF NOT EXISTS `sessions` (
  `id`           varchar(255)    NOT NULL PRIMARY KEY,
  `user_id`      bigint UNSIGNED NULL,
  `ip_address`   varchar(45)     NULL,
  `user_agent`   text            NULL,
  `payload`      longtext        NOT NULL,
  `last_activity` int            NOT NULL,
  INDEX `sessions_user_id_index` (`user_id`),
  INDEX `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;

-- ============================================================
-- Done. Visit  http://yourdomain.com/install.php  to finish.
-- ============================================================
