-- ESP32 Universal IoT System - Final Optimized Database
-- 6 Ana Tablo + JSON Alanları + Views + Triggers

-- ==================== TEMEL TABLOLAR ====================

-- 1. Kullanıcılar (Tüm kullanıcı bilgileri ve ayarlar)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user', 'viewer') DEFAULT 'user',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    
    -- Kişisel bilgiler
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    phone VARCHAR(20) NULL,
    avatar_url VARCHAR(255) NULL,
    
    -- Sistem bilgileri
    last_login TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- JSON alanları (esnek veriler)
    preferences JSON NULL,                    -- Kullanıcı ayarları
    fcm_tokens JSON NULL,                     -- FCM token'ları array
    api_keys JSON NULL,                        -- API anahtarları array
    notification_settings JSON NULL,          -- Bildirim ayarları
    
    INDEX idx_users_email (email),
    INDEX idx_users_username (username),
    INDEX idx_users_role (role),
    INDEX idx_users_status (status)
);

-- 2. Cihazlar (Tüm cihaz bilgileri ve konfigürasyon)
CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL,
    owner_id INT NOT NULL,
    
    -- Donanım bilgileri
    esp_model ENUM('ESP32', 'ESP32-S2', 'ESP32-S3') NOT NULL,
    firmware_version VARCHAR(50) NULL,
    mac_address VARCHAR(17) NULL UNIQUE,
    
    -- Durum bilgileri
    status ENUM('online', 'offline', 'error', 'maintenance') DEFAULT 'offline',
    current_mode ENUM('SETUP', 'PROVISION', 'ONLINE', 'MOTOR_CONTROL', 'PROTECTION') DEFAULT 'SETUP',
    last_seen TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    
    -- Konfigürasyon (JSON alanları)
    config JSON NULL,                           -- Tüm cihaz ayarları
    sensor_config JSON NULL,                    -- Sensör konfigürasyonu
    motor_config JSON NULL,                     -- Motor ayarları
    protection_config JSON NULL,                -- Koruma ayarları
    
    -- Konum ve meta veriler
    location JSON NULL,                         -- GPS veya konum bilgileri
    tags JSON NULL,                             -- Etiketler
    metadata JSON NULL,                         -- Ek meta veriler
    
    -- Zaman damgaları
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_devices_device_id (device_id),
    INDEX idx_devices_owner (owner_id),
    INDEX idx_devices_status (status),
    INDEX idx_devices_model (esp_model),
    INDEX idx_devices_last_seen (last_seen)
);

-- 3. Sensör Verileri (Zaman serisi verisi)
CREATE TABLE IF NOT EXISTS sensor_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    sensor_code VARCHAR(50) NOT NULL,
    sensor_type VARCHAR(50) NOT NULL,
    value DECIMAL(15,4) NOT NULL,
    unit VARCHAR(20) NULL,
    
    -- Kalite ve meta veriler
    quality ENUM('good', 'fair', 'poor', 'error') DEFAULT 'good',
    metadata JSON NULL,                         -- Ek sensör meta verileri
    
    -- Zaman damgası
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    INDEX idx_sensor_data_device_time (device_id, timestamp),
    INDEX idx_sensor_data_sensor_time (sensor_code, timestamp),
    INDEX idx_sensor_data_timestamp (timestamp)
);

-- 4. Cihaz Komutları ve Durumları
CREATE TABLE IF NOT EXISTS device_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    event_type ENUM('command', 'status', 'alert', 'mode_change', 'role_change', 'heartbeat', 'sensor_data') NOT NULL,
    
    -- Event detayları
    event_data JSON NOT NULL,                    -- Tüm event verileri
    
    -- Kaynak bilgileri
    source ENUM('api', 'mobile', 'web', 'device', 'system') NOT NULL,
    user_id INT NULL,                           -- Kimin gönderdiği
    
    -- Durum bilgileri
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    error_message TEXT NULL,
    
    -- Zaman damgaları
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_device_events_device_time (device_id, created_at),
    INDEX idx_device_events_type (event_type),
    INDEX idx_device_events_status (status),
    INDEX idx_events_source (source)
);

-- 5. Kullanıcı-Cihaz İlişkileri
CREATE TABLE IF NOT EXISTS device_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    user_id INT NOT NULL,
    permission_type ENUM('owner', 'controller', 'viewer') NOT NULL,
    
    -- İzin detayları
    permissions JSON NULL,                      -- Detaylı izinler
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    
    -- Durum
    is_active BOOLEAN DEFAULT TRUE,
    last_access TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_device_user_permission (device_id, user_id),
    INDEX idx_device_permissions_device (device_id),
    INDEX idx_device_permissions_user (user_id),
    INDEX idx_device_permissions_type (permission_type)
);

-- 6. Sistem Logları (Tüm sistem olayları)
CREATE TABLE IF NOT EXISTS system_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    log_level ENUM('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL') NOT NULL,
    category ENUM('auth', 'device', 'api', 'system', 'security', 'performance') NOT NULL,
    
    -- Log detayları
    message TEXT NOT NULL,
    context JSON NULL,                           -- Bağlam bilgileri
    
    -- Kaynak bilgileri
    user_id INT NULL,
    device_id INT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    
    -- Zaman damgası
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE SET NULL,
    INDEX idx_system_logs_level_time (log_level, created_at),
    INDEX idx_system_logs_category_time (category, created_at),
    INDEX idx_system_logs_user (user_id),
    INDEX idx_system_logs_device (device_id)
);

-- ==================== TRIGGER'LER ====================

-- Cihaz olaylarını otomatik log'la
DELIMITER //
CREATE TRIGGER IF NOT EXISTS log_device_events
AFTER INSERT ON devices
FOR EACH ROW
BEGIN
    INSERT INTO system_logs (log_level, category, message, context, device_id)
    VALUES ('INFO', 'device', 
           CONCAT('Device created: ', NEW.name, ' (', NEW.device_id, ')'),
           JSON_OBJECT('action', 'create', 'device_id', NEW.device_id, 'name', NEW.name),
           NEW.id);
END//
DELIMITER ;

-- Kullanıcı olaylarını log'la
DELIMITER //
CREATE TRIGGER IF NOT EXISTS log_user_events
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.status <> OLD.status THEN
        INSERT INTO system_logs (log_level, category, message, context, user_id)
        VALUES ('INFO', 'auth', 
               CONCAT('User status changed: ', NEW.username, ' from ', OLD.status, ' to ', NEW.status),
               JSON_OBJECT('action', 'status_change', 'old_status', OLD.status, 'new_status', NEW.status),
               NEW.id);
    END IF;
END//
DELIMITER ;

-- ==================== SİSTEM AYARLARI ====================

CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL,
    setting_type ENUM('string', 'integer', 'float', 'boolean', 'json') DEFAULT 'string',
    category VARCHAR(50) DEFAULT 'general',
    description TEXT NULL,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_system_settings_key (setting_key),
    INDEX idx_system_settings_category (category)
);

-- Varsayılan ayarlar
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, category, is_public) VALUES
('max_devices_per_user', '10', 'integer', 'Kullanıcı başına maksimum cihaz sayısı', 'limits', FALSE),
('default_sensor_interval', '900000', 'integer', 'Varsayılan sensör okuma aralığı (ms)', 'sensors', FALSE),
('data_retention_days', '90', 'integer', 'Veri saklama süresi (gün)', 'system', FALSE),
('enable_real_time', 'true', 'boolean', 'Gerçek zamanlı veri aktif', 'features', FALSE),
('websocket_port', '8080', 'integer', 'WebSocket sunucu portu', 'network', FALSE),
('fcm_server_key', 'YOUR_FCM_SERVER_KEY', 'string', 'FCM sunucu anahtarı', 'notifications', FALSE),
('max_api_requests_per_minute', '1000', 'integer', 'API istek limiti (dakika)', 'api', FALSE),
('enable_device_auto_claim', 'true', 'boolean', 'Otomatik cihaz kayıt aktif', 'devices', FALSE),
('motor_protection_timeout', '3000', 'integer', 'Motor koruma zaman aşımı (ms)', 'motor', FALSE),
('notification_batch_size', '100', 'integer', 'Bildirim batch boyutu', 'notifications', FALSE)
ON DUPLICATE KEY UPDATE 
setting_value = VALUES(setting_value),
updated_at = CURRENT_TIMESTAMP;

-- ==================== VERİ TEMİZLİĞİ ====================

-- Eski verileri temizleme (sistem ayarlarına göre)
-- Bu procedure'ı düzenli aralıklarla çalıştırabilir
DELIMITER //
CREATE PROCEDURE cleanup_old_data()
BEGIN
    DECLARE retention_days INT DEFAULT 90;
    
    -- Ayarlardan saklama süresini al
    SELECT CAST(setting_value AS UNSIGNED) INTO retention_days 
    FROM system_settings 
    WHERE setting_key = 'data_retention_days';
    
    -- Eski sensör verilerini temizle
    DELETE FROM sensor_data 
    WHERE timestamp < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    -- Eski logları temizle
    DELETE FROM system_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    -- Eski olayları temizle
    DELETE FROM device_events 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    SELECT CONCAT('Cleanup completed. Retention days: ', retention_days) AS message;
END//
DELIMITER ;

-- ==================== SONUÇ ====================

-- Optimize edilmiş veritabanı:
-- 6 ana tablo
-- JSON alanları ile esnek veri depolama
-- View'lar ile kolay sorgular
-- Trigger'lar ile otomasyon
-- Sistem ayarları ile konfigürasyon
-- Performans optimizasyonları

-- Toplam tablo sayısı: 6 (ana) + 3 (view) = 9
-- Eskiden: 21 tablo
-- Tasarruf: 57% azalma

-- Bu dosyayı doğrudan MySQL'de çalıştırabilir
-- mysql -u root -p dmapesp < dmapesp.sql

-- VIEW'ler için ayrıca çalıştırın:
-- mysql -u root -p dmapesp < views.sql
