-- Create database if not exists
CREATE DATABASE IF NOT EXISTS iot_management;
USE iot_management;

-- Devices table
CREATE TABLE IF NOT EXISTS devices (
    device_id VARCHAR(50) PRIMARY KEY,
    device_name VARCHAR(100) NOT NULL,
    device_type VARCHAR(50),
    gateway_id VARCHAR(50),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Gateways table
CREATE TABLE IF NOT EXISTS gateways (
    gateway_id VARCHAR(50) PRIMARY KEY,
    gateway_name VARCHAR(100) NOT NULL,
    location VARCHAR(255),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Gateway uptime tracking
CREATE TABLE IF NOT EXISTS gateway_uptime (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    gateway_id VARCHAR(50),
    status ENUM('up', 'down') NOT NULL,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (gateway_id) REFERENCES gateways(gateway_id)
);

-- Add some sample data
INSERT INTO gateways (gateway_id, gateway_name, location) VALUES
('gateway-001', 'Main Building Gateway', 'Main Building Floor 1'),
('gateway-002', 'Warehouse Gateway', 'Warehouse A'),
('gateway-003', 'Office Gateway', 'Office Building Floor 2');

INSERT INTO gateway_uptime (gateway_id, status, start_time) VALUES
('gateway-001', 'up', NOW()),
('gateway-002', 'down', NOW()),
('gateway-003', 'up', NOW());
