-- ====================================================================
-- SMART FACILITY MANAGEMENT SYSTEM - SIMPLIFIED VERSION
-- Database Schema - Focus on Users, Companies, Devices
-- Version: 1.0 Simple
-- ====================================================================

-- Create database
CREATE DATABASE IF NOT EXISTS smart_facility_mgmt 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE smart_facility_mgmt;

-- Disable strict mode for easier development
SET SESSION sql_mode = '';

-- ====================================================================
-- 1. COMPANIES TABLE
-- ====================================================================
CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    company_code VARCHAR(20) UNIQUE NOT NULL,
    
    -- Contact info
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL,
    address VARCHAR(500) NULL,
    
    -- Logo and branding
    logo_path VARCHAR(500) NULL,
    
    -- Subscription
    max_devices INT DEFAULT 100,
    max_users INT DEFAULT 10,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_active (is_active)
);

-- ====================================================================
-- 2. USERS TABLE
-- ====================================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    
    -- Basic info
    username VARCHAR(50) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    
    -- Role and permissions
    user_role ENUM('admin', 'manager', 'technician', 'viewer') DEFAULT 'viewer',
    can_control_devices BOOLEAN DEFAULT FALSE,
    can_manage_users BOOLEAN DEFAULT FALSE,
    can_view_reports BOOLEAN DEFAULT TRUE,
    
    -- Contact
    phone VARCHAR(20) NULL,
    profile_image VARCHAR(500) NULL,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company (company_id),
    INDEX idx_active (is_active),
    INDEX idx_role (user_role)
);

-- ====================================================================
-- 3. SITES TABLE
-- ====================================================================
CREATE TABLE sites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    site_name VARCHAR(200) NOT NULL,
    address VARCHAR(500) NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company (company_id)
);

-- ====================================================================
-- 4. DEVICES TABLE
-- ====================================================================
CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    site_id INT NOT NULL,
    
    -- Device info
    device_name VARCHAR(200) NOT NULL,
    device_type ENUM('projector', 'server', 'hvac', 'lighting', 'camera', 'other') DEFAULT 'other',
    brand VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    serial_number VARCHAR(100) NULL,
    
    -- Network
    ip_address VARCHAR(45) NULL,
    port INT DEFAULT 80,
    
    -- Location
    location VARCHAR(200) NULL,
    
    -- Status
    status ENUM('online', 'offline', 'error', 'maintenance') DEFAULT 'offline',
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Additional
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    INDEX idx_company (company_id),
    INDEX idx_site (site_id),
    INDEX idx_status (status),
    INDEX idx_type (device_type)
);

-- ====================================================================
-- 5. SIMPLE ATTRIBUTES SYSTEM
-- ====================================================================
CREATE TABLE device_attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    attribute_name VARCHAR(100) NOT NULL,
    attribute_value VARCHAR(500) NULL,
    unit VARCHAR(20) NULL,
    alert_min DECIMAL(10,2) NULL,
    alert_max DECIMAL(10,2) NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    UNIQUE KEY unique_device_attr (device_id, attribute_name),
    INDEX idx_device (device_id)
);

-- ====================================================================
-- 6. ALERTS TABLE
-- ====================================================================
CREATE TABLE alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    alert_type VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    status ENUM('active', 'acknowledged', 'resolved') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at TIMESTAMP NULL,
    acknowledged_by INT NULL,
    
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    FOREIGN KEY (acknowledged_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_device (device_id),
    INDEX idx_status (status),
    INDEX idx_severity (severity)
);

-- ====================================================================
-- 7. USER ACTIVITY LOG
-- ====================================================================
CREATE TABLE user_activity (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

-- ====================================================================
-- 8. INSERT SAMPLE DATA
-- ====================================================================

-- Insert sample company
INSERT INTO companies (company_name, company_code, email, phone, is_active) VALUES 
('Tech Solutions Ltd', 'TECH001', 'admin@techsolutions.com', '+1-555-0123', TRUE);

SET @company_id = LAST_INSERT_ID();

-- Insert sample site
INSERT INTO sites (company_id, site_name, address, is_active) VALUES 
(@company_id, 'Main Office', '123 Tech Street, Tech City', TRUE);

SET @site_id = LAST_INSERT_ID();

-- Insert sample users
INSERT INTO users (company_id, username, first_name, last_name, email, password_hash, user_role, can_control_devices, can_manage_users) VALUES
(@company_id, 'admin', 'System', 'Administrator', 'admin@techsolutions.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', TRUE, TRUE),
(@company_id, 'manager', 'John', 'Smith', 'manager@techsolutions.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'manager', TRUE, FALSE),
(@company_id, 'tech1', 'Alice', 'Johnson', 'tech1@techsolutions.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'technician', TRUE, FALSE),
(@company_id, 'viewer1', 'Bob', 'Wilson', 'viewer@techsolutions.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'viewer', FALSE, FALSE);

-- Insert sample devices
INSERT INTO devices (company_id, site_id, device_name, device_type, brand, model, ip_address, location, status) VALUES
(@company_id, @site_id, 'Conference Room Projector', 'projector', 'Epson', 'EB-G7905U', '192.168.1.101', 'Conference Room A', 'online'),
(@company_id, @site_id, 'Main Web Server', 'server', 'Dell', 'PowerEdge R750', '192.168.1.100', 'Server Room', 'online'),
(@company_id, @site_id, 'HVAC Unit 1', 'hvac', 'Carrier', 'WeatherExpert', '192.168.1.102', 'Roof Top', 'online'),
(@company_id, @site_id, 'Reception Area Lighting', 'lighting', 'Philips', 'Smart LED', '192.168.1.103', 'Reception', 'offline'),
(@company_id, @site_id, 'Security Camera 1', 'camera', 'Hikvision', 'DS-2CD2385G1', '192.168.1.104', 'Main Entrance', 'online');

-- Insert sample device attributes
INSERT INTO device_attributes (device_id, attribute_name, attribute_value, unit, alert_min, alert_max) VALUES
(1, 'temperature', '45.5', '°C', 10.0, 60.0),
(1, 'lamp_hours', '1250', 'hours', NULL, 2000.0),
(2, 'cpu_usage', '35.2', '%', NULL, 90.0),
(2, 'memory_usage', '68.1', '%', NULL, 85.0),
(3, 'temperature', '22.0', '°C', 15.0, 30.0),
(3, 'humidity', '45.0', '%', 30.0, 70.0);

-- Insert sample alerts
INSERT INTO alerts (device_id, alert_type, message, severity, status) VALUES
(4, 'device_offline', 'Reception Area Lighting is offline', 'medium', 'active'),
(1, 'high_temperature', 'Conference Room Projector temperature is high', 'high', 'active');

-- ====================================================================
-- 9. USEFUL VIEWS
-- ====================================================================

-- Device summary view
CREATE VIEW device_summary AS
SELECT 
    d.id,
    d.device_name,
    d.device_type,
    d.status,
    d.ip_address,
    d.location,
    d.last_seen,
    s.site_name,
    c.company_name,
    COUNT(a.id) as active_alerts
FROM devices d
JOIN sites s ON d.site_id = s.id
JOIN companies c ON d.company_id = c.id
LEFT JOIN alerts a ON d.id = a.device_id AND a.status = 'active'
GROUP BY d.id;

-- Company stats view
CREATE VIEW company_stats AS
SELECT 
    c.id,
    c.company_name,
    COUNT(DISTINCT u.id) as total_users,
    COUNT(DISTINCT s.id) as total_sites,
    COUNT(DISTINCT d.id) as total_devices,
    COUNT(DISTINCT CASE WHEN d.status = 'online' THEN d.id END) as online_devices,
    COUNT(DISTINCT CASE WHEN d.status = 'offline' THEN d.id END) as offline_devices,
    COUNT(DISTINCT CASE WHEN a.status = 'active' THEN a.id END) as active_alerts
FROM companies c
LEFT JOIN users u ON c.id = u.company_id
LEFT JOIN sites s ON c.id = s.company_id
LEFT JOIN devices d ON c.id = d.company_id
LEFT JOIN alerts a ON d.id = a.device_id
GROUP BY c.id;

-- ====================================================================
-- SAMPLE DATA INFO
-- ====================================================================
SELECT 'Database created successfully!' as status;
SELECT 'Default login: admin / password' as login_info;
SELECT 'Company: Tech Solutions Ltd' as company_info;
SELECT CONCAT(COUNT(*), ' sample devices created') as devices_info FROM devices;
SELECT CONCAT(COUNT(*), ' sample users created') as users_info FROM users;