-- backend/schema.sql
-- Smart Home Automation Database
-- Timezone: India/Kolkata
-- Create database first, then tables.

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

USE smart_home;

-- Store current live state of each relay
CREATE TABLE IF NOT EXISTS device_status (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    relay_num TINYINT UNSIGNED NOT NULL UNIQUE,
    relay_name VARCHAR(100) NOT NULL DEFAULT '',
    current_state TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=ON, 0=OFF',
    source ENUM('system','web','physical_switch','cron','device_poll') DEFAULT 'system',
    last_updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_relay_num (relay_num),
    INDEX idx_current_state (current_state)
) ENGINE=InnoDB;

-- Store automation schedules
CREATE TABLE IF NOT EXISTS schedules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    relay_num TINYINT UNSIGNED NOT NULL,
    on_time DATETIME NOT NULL,
    off_time DATETIME NOT NULL,
    is_executed_on TINYINT(1) NOT NULL DEFAULT 0,
    is_executed_off TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('pending','running','completed','cancelled') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_relay_num (relay_num),
    INDEX idx_on_time (on_time),
    INDEX idx_off_time (off_time),
    INDEX idx_status (status),
    CONSTRAINT chk_relay_num CHECK (relay_num BETWEEN 1 AND 3),
    CONSTRAINT chk_state_flags CHECK (is_executed_on IN (0,1) AND is_executed_off IN (0,1))
) ENGINE=InnoDB;

-- Optional: event log for debugging web, cron, device and physical switch actions
CREATE TABLE IF NOT EXISTS event_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    relay_num TINYINT UNSIGNED NULL,
    event_type VARCHAR(50) NOT NULL,
    old_state TINYINT(1) NULL,
    new_state TINYINT(1) NULL,
    message VARCHAR(255) NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_relay_num (relay_num),
    INDEX idx_event_type (event_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Default relay rows. NC wiring means default ON is safer for fail-safe setup.
INSERT INTO device_status (relay_num, relay_name, current_state, source)
VALUES
(1, 'Appliance 1', 1, 'system'),
(2, 'Appliance 2', 1, 'system'),
(3, 'Appliance 3', 1, 'system')
ON DUPLICATE KEY UPDATE relay_num = VALUES(relay_num);