-- Devices
CREATE TABLE devices (
    id BINARY(16) NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    location VARCHAR(255) NOT NULL,
    status_id SMALLINT UNSIGNED NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE device_statuses (
    id SMALLINT UNSIGNED PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO device_statuses (id, name) VALUES
(1,'ok'),(2,'pending'),(3,'lost'),(4,'disabled');

CREATE TABLE data_types (
    id SMALLINT UNSIGNED PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
);
INSERT INTO data_types (id, name) VALUES (1,'bool'),(2,'float');

-- Sensors
CREATE TABLE sensors (
    id BINARY(16) NOT NULL PRIMARY KEY,
    device_id BINARY(16) NOT NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    data_type_id SMALLINT UNSIGNED NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (data_type_id) REFERENCES data_types(id)
);


-- Sensor readings
CREATE TABLE sensor_readings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sensor_id BINARY(16) NOT NULL,
    value DOUBLE NOT NULL,
    value_at DATETIME(6) NOT NULL,
    INDEX idx_sensor_time (sensor_id, value_at),
    FOREIGN KEY (sensor_id) REFERENCES sensors(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Actors (actuators)
CREATE TABLE actors (
    id BINARY(16) NOT NULL PRIMARY KEY,
    device_id BINARY(16) NOT NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    data_type_id SMALLINT UNSIGNED NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (data_type_id) REFERENCES data_types(id)
);

-- Users table for authentication
CREATE TABLE users (
    id BINARY(16) NOT NULL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create index on username for faster lookups
CREATE INDEX idx_username ON users(username);

-- Insert default admin user (password: password)
-- bcrypt hash of "password"
INSERT INTO users (id, username, password_hash, email)
VALUES (
    UUID_TO_BIN(UUID()),
    'admin',
    '$2a$10$y5R/aLl7Ah9GbdcR57f4ze0lX12suTJQvPyJwLE0RJXqWpg7lStAC',
    'admin@example.com'
);