CREATE TABLES.sql

CREATE DATABASE ADBMS_Project;
USE ADBMS_Project;
CREATE TABLE USER (
    User_ID INT PRIMARY KEY AUTO_INCREMENT,
    Full_Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE TARIFF (
    Tariff_ID INT PRIMARY KEY AUTO_INCREMENT,
    Tariff_Name VARCHAR(50) NOT NULL,
    Price_per_kWh DECIMAL(10, 4) NOT NULL 
);
CREATE TABLE House (
    House_ID INT PRIMARY KEY AUTO_INCREMENT,
    Address VARCHAR(255) NOT NULL,
    Area_sqm DECIMAL(10, 2),
    User_ID INT NOT NULL,
    FOREIGN KEY (User_ID) REFERENCES USER(User_ID)
);
CREATE TABLE SMART_DEVICE (
    Device_ID INT PRIMARY KEY AUTO_INCREMENT,
    House_ID INT NOT NULL,
    Device_Name VARCHAR(100) NOT NULL,
    Type VARCHAR(50), 
    Model VARCHAR(50),
    MAC_Address VARCHAR(17) UNIQUE,
    Status ENUM('ON', 'OFF', 'STANDBY') NOT NULL DEFAULT 'OFF',
    FOREIGN KEY (House_ID) REFERENCES House(House_ID)
);
CREATE TABLE APPLIED_TARIFF (
    House_ID INT,
    Tariff_ID INT,
    Start_Date_Time DATETIME NOT NULL,
    End_Date_Time DATETIME, 
    PRIMARY KEY (House_ID, Tariff_ID, Start_Date_Time),
    FOREIGN KEY (House_ID) REFERENCES House(House_ID),
    FOREIGN KEY (Tariff_ID) REFERENCES TARIFF(Tariff_ID)
);
CREATE TABLE COMMAND_LOG (
    Command_Log_ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    User_ID INT NOT NULL,
    Device_ID INT NOT NULL,
    Command_Type ENUM('TURN_ON', 'TURN_OFF', 'SET_TEMP') NOT NULL,
    Execution_Time DATETIME NOT NULL,
    Execution_Status ENUM('SUCCESS', 'FAILED', 'PENDING') NOT NULL,
    FOREIGN KEY (User_ID) REFERENCES USER(User_ID),
    FOREIGN KEY (Device_ID) REFERENCES SMART_DEVICE(Device_ID)
);
CREATE TABLE CONSUMPTION_READING (
    Reading_ID BIGINT PRIMARY KEY AUTO_INCREMENT,
    Device_ID INT NOT NULL,
    Reading_Timestamp DATETIME NOT NULL,
    Consumption_kWh DECIMAL(10, 4) NOT NULL,
    Current_A DECIMAL(10, 4),
    UNIQUE (Device_ID, Reading_Timestamp), 
    FOREIGN KEY (Device_ID) REFERENCES SMART_DEVICE(Device_ID)
);
CREATE INDEX idx_device_time ON CONSUMPTION_READING (Device_ID, Reading_Timestamp);
CREATE INDEX idx_timestamp ON CONSUMPTION_READING (Reading_Timestamp);