-- =============================================================================
-- SmartLife Monitor - MySQL Database Schema
-- =============================================================================
-- استخدم هذا الملف لإنشاء قاعدة البيانات في phpMyAdmin
-- =============================================================================

-- إنشاء قاعدة البيانات
CREATE DATABASE IF NOT EXISTS smartlife_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE smartlife_db;

-- =============================================================================
-- جدول الأجهزة (Devices)
-- =============================================================================
CREATE TABLE devices (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    tenant_id VARCHAR(36) NOT NULL,
    
    lifesmart_device_id VARCHAR(255) NOT NULL,
    lifesmart_hub_id VARCHAR(255),
    
    name VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255),
    device_type VARCHAR(100),
    device_model VARCHAR(100),
    location VARCHAR(255),
    location_ar VARCHAR(255),
    icon VARCHAR(100),
    
    is_online BOOLEAN DEFAULT FALSE,
    last_seen TIMESTAMP NULL,
    
    device_metadata JSON,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY unique_device_per_tenant (tenant_id, lifesmart_device_id),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_device_type (device_type),
    INDEX idx_is_online (is_online)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- جدول قراءات الأجهزة (Device Readings)
-- =============================================================================
CREATE TABLE device_readings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(36) NOT NULL,
    
    reading_type VARCHAR(50) NOT NULL,
    value DECIMAL(10, 2) NOT NULL,
    unit VARCHAR(20),
    
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    INDEX idx_device_id (device_id),
    INDEX idx_reading_type (reading_type),
    INDEX idx_recorded_at (recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- جدول قواعد التنبيهات (Alert Rules)
-- =============================================================================
CREATE TABLE alert_rules (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    tenant_id VARCHAR(36) NOT NULL,
    device_id VARCHAR(36),
    
    name VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255),
    
    condition_type ENUM('greater_than', 'less_than', 'equals', 'between', 'outside', 'changed', 'offline') NOT NULL,
    reading_type VARCHAR(50) NOT NULL,
    
    threshold_value DECIMAL(10, 2),
    threshold_min DECIMAL(10, 2),
    threshold_max DECIMAL(10, 2),
    
    severity ENUM('info', 'warning', 'critical') DEFAULT 'warning',
    notification_channels JSON,
    
    cooldown_minutes INT DEFAULT 15,
    is_active BOOLEAN DEFAULT TRUE,
    last_triggered TIMESTAMP NULL,
    
    created_by VARCHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_device_id (device_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- جدول سجل التنبيهات (Alert Logs)
-- =============================================================================
CREATE TABLE alert_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    alert_rule_id VARCHAR(36),
    device_id VARCHAR(36) NOT NULL,
    tenant_id VARCHAR(36) NOT NULL,
    
    title VARCHAR(255) NOT NULL,
    message TEXT,
    severity ENUM('info', 'warning', 'critical') DEFAULT 'info',
    
    reading_value DECIMAL(10, 2),
    
    is_read BOOLEAN DEFAULT FALSE,
    is_resolved BOOLEAN DEFAULT FALSE,
    resolved_at TIMESTAMP NULL,
    
    triggered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (alert_rule_id) REFERENCES alert_rules(id) ON DELETE SET NULL,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_device_id (device_id),
    INDEX idx_is_read (is_read),
    INDEX idx_is_resolved (is_resolved),
    INDEX idx_triggered_at (triggered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- جدول سجل النشاطات (Activity Logs)
-- =============================================================================
CREATE TABLE activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(36),
    tenant_id VARCHAR(36),
    
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50),
    resource_id VARCHAR(36),
    
    details JSON,
    ip_address VARCHAR(45),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- تم إنشاء قاعدة البيانات بنجاح!
-- =============================================================================
