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

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

-- =============================================================================
-- جدول العملاء (Tenants)
-- =============================================================================
CREATE TABLE tenants (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255),
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(50),
    
    -- LifeSmart API Credentials
    lifesmart_app_key VARCHAR(255),
    lifesmart_app_token VARCHAR(255),
    lifesmart_user_id VARCHAR(255),
    lifesmart_region VARCHAR(50) DEFAULT 'global',
    
    -- Subscription
    subscription_plan VARCHAR(50) DEFAULT 'basic',
    max_devices INT DEFAULT 10,
    max_users INT DEFAULT 5,
    
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- جدول المستخدمين (Users)
-- =============================================================================
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    tenant_id VARCHAR(36),
    
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    full_name_ar VARCHAR(255),
    phone VARCHAR(50),
    avatar_url TEXT,
    
    role ENUM('super_admin', 'tenant_admin', 'tenant_user') DEFAULT 'tenant_user',
    
    language VARCHAR(10) DEFAULT 'ar',
    timezone VARCHAR(50) DEFAULT 'Asia/Riyadh',
    
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    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,
    INDEX idx_email (email),
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- جدول الأجهزة (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;

-- =============================================================================
-- جدول جلسات المستخدمين (User Sessions)
-- =============================================================================
CREATE TABLE user_sessions (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    user_id VARCHAR(36) NOT NULL,
    
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255),
    
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_token_hash (token_hash),
    INDEX idx_expires_at (expires_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;

-- =============================================================================
-- إنشاء مستخدم Super Admin افتراضي
-- =============================================================================
-- كلمة المرور: admin123
-- يجب تغييرها فورًا بعد أول تسجيل دخول!

INSERT INTO users (id, email, password_hash, full_name, full_name_ar, role, language, is_active)
VALUES (
    UUID(),
    'admin@smartlife.local',
    '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYIxF6O86PS',
    'System Administrator',
    'مدير النظام',
    'super_admin',
    'ar',
    TRUE
);

-- =============================================================================
-- Views للإحصائيات
-- =============================================================================

-- عرض إحصائيات الأجهزة لكل عميل
CREATE VIEW tenant_device_stats AS
SELECT 
    t.id AS tenant_id,
    t.name AS tenant_name,
    COUNT(d.id) AS total_devices,
    SUM(CASE WHEN d.is_online = TRUE THEN 1 ELSE 0 END) AS online_devices,
    SUM(CASE WHEN d.is_online = FALSE THEN 1 ELSE 0 END) AS offline_devices
FROM tenants t
LEFT JOIN devices d ON t.id = d.tenant_id
GROUP BY t.id, t.name;

-- عرض إحصائيات التنبيهات لكل عميل
CREATE VIEW tenant_alert_stats AS
SELECT 
    t.id AS tenant_id,
    t.name AS tenant_name,
    COUNT(al.id) AS total_alerts,
    SUM(CASE WHEN al.is_resolved = FALSE THEN 1 ELSE 0 END) AS active_alerts,
    SUM(CASE WHEN al.severity = 'critical' AND al.is_resolved = FALSE THEN 1 ELSE 0 END) AS critical_alerts
FROM tenants t
LEFT JOIN alert_logs al ON t.id = al.tenant_id
WHERE al.triggered_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY t.id, t.name;

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