CREATE DATABASE IF NOT EXISTS iot_agri
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE iot_agri;

DROP TABLE IF EXISTS alerts;
DROP TABLE IF EXISTS sensor_data;
DROP TABLE IF EXISTS batches;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS nodes;
DROP TABLE IF EXISTS admin_users;

CREATE TABLE nodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    node_code VARCHAR(50) NOT NULL UNIQUE,
    node_name VARCHAR(100) NOT NULL,
    location VARCHAR(255),
    status VARCHAR(20) DEFAULT 'offline',
    last_seen DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sensor_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    node_code VARCHAR(50) NOT NULL,
    temperature FLOAT NULL,
    humidity FLOAT NULL,
    soil_moisture FLOAT NULL,
    light FLOAT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_node_time (node_code, created_at)
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_code VARCHAR(50) NOT NULL UNIQUE,
    product_id INT NULL,
    node_code VARCHAR(50) NOT NULL,
    origin VARCHAR(255),
    planting_date DATE NULL,
    harvest_date DATE NULL,
    producer_name VARCHAR(150),
    description TEXT,
    is_expired TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

CREATE TABLE alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    node_code VARCHAR(50),
    alert_type VARCHAR(100),
    value FLOAT NULL,
    threshold_value FLOAT NULL,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO nodes (node_code, node_name, location, status)
VALUES
('NODE01', 'Node cảm biến 1', 'Khu trồng 1', 'offline'),
('NODE02', 'Node cảm biến 2', 'Khu trồng 2', 'offline'),
('NODE03', 'Node cảm biến 3', 'Khu trồng 3', 'offline');

INSERT INTO products (product_name, description)
VALUES
('Rau cải xanh', 'Sản phẩm rau cải xanh trồng trong khu vực giám sát IoT'),
('Cà chua', 'Sản phẩm cà chua được theo dõi điều kiện môi trường');

INSERT INTO batches (batch_code, product_id, node_code, origin, planting_date, harvest_date, producer_name, description)
VALUES
('LOT001', 1, 'NODE01', 'Nhà kính A', '2026-05-01', '2026-05-30', 'Vườn IoT Demo', 'Lô rau cải xanh được giám sát bằng hệ thống IoT'),
('LOT002', 2, 'NODE02', 'Nhà kính B', '2026-05-05', '2026-06-01', 'Vườn IoT Demo', 'Lô cà chua được theo dõi nhiệt độ, độ ẩm và độ ẩm đất');