-- =====================================================
-- IoT Devices Management System - Database Schema
-- =====================================================

-- Table: iot_devices
CREATE TABLE IF NOT EXISTS `iot_devices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) UNIQUE NOT NULL,
  `device_name` VARCHAR(100) NOT NULL,
  `device_type` ENUM('esp8266', 'esp32', 'arduino', 'raspberry_pi', 'other') DEFAULT 'esp8266',
  `description` TEXT,
  `location` VARCHAR(100),
  `pin_config` JSON,
  `is_online` BOOLEAN DEFAULT FALSE,
  `is_active` BOOLEAN DEFAULT TRUE,
  `last_seen` DATETIME,
  `ip_address` VARCHAR(45),
  `firmware_version` VARCHAR(20),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_device_id` (`device_id`),
  INDEX `idx_is_online` (`is_online`),
  INDEX `idx_device_type` (`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: iot_commands
CREATE TABLE IF NOT EXISTS `iot_commands` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `payload` JSON,
  `priority` TINYINT DEFAULT 5,
  `status` ENUM('pending', 'sent', 'executed', 'failed', 'cancelled') DEFAULT 'pending',
  `error_message` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `sent_at` DATETIME,
  `executed_at` DATETIME,
  `created_by` VARCHAR(50),
  FOREIGN KEY (`device_id`) REFERENCES `iot_devices`(`device_id`) ON DELETE CASCADE,
  INDEX `idx_device_status` (`device_id`, `status`),
  INDEX `idx_priority` (`priority`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: iot_logs
CREATE TABLE IF NOT EXISTS `iot_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) NOT NULL,
  `log_type` ENUM('info', 'warning', 'error', 'command', 'status') DEFAULT 'info',
  `message` TEXT NOT NULL,
  `data` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`device_id`) REFERENCES `iot_devices`(`device_id`) ON DELETE CASCADE,
  INDEX `idx_device_log` (`device_id`, `log_type`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: iot_sensors_data
CREATE TABLE IF NOT EXISTS `iot_sensors_data` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) NOT NULL,
  `sensor_type` VARCHAR(50) NOT NULL,
  `value` DECIMAL(10, 2),
  `unit` VARCHAR(20),
  `raw_data` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`device_id`) REFERENCES `iot_devices`(`device_id`) ON DELETE CASCADE,
  INDEX `idx_device_sensor` (`device_id`, `sensor_type`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: iot_schedules
CREATE TABLE IF NOT EXISTS `iot_schedules` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` VARCHAR(50) NOT NULL,
  `schedule_name` VARCHAR(100) NOT NULL,
  `action` VARCHAR(50) NOT NULL,
  `payload` JSON,
  `cron_expression` VARCHAR(100),
  `is_active` BOOLEAN DEFAULT TRUE,
  `last_run` DATETIME,
  `next_run` DATETIME,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`device_id`) REFERENCES `iot_devices`(`device_id`) ON DELETE CASCADE,
  INDEX `idx_device_schedule` (`device_id`, `is_active`),
  INDEX `idx_next_run` (`next_run`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data
INSERT INTO `iot_devices` (`device_id`, `device_name`, `device_type`, `description`, `location`, `pin_config`) VALUES
('ESP01', 'Living Room Light', 'esp8266', 'Main living room smart light controller', 'Living Room', '{"relay_pin": 5, "led_pin": 2}'),
('ESP02', 'Bedroom Fan', 'esp8266', 'Bedroom ceiling fan controller', 'Bedroom', '{"relay_pin": 4, "led_pin": 2}');