-- Sensi Company Secure Portal Database Schema
-- MySQL/MariaDB Schema for AWS RDS
-- 
-- This schema supports the following PHP classes:
-- - User.php: User authentication and management
-- - FileManager.php: Secure file upload/download operations  
-- - AuditLogger.php: Security event logging
-- - Security.php: Password hashing and security utilities

-- Create database (run this separately if needed)
-- CREATE DATABASE sensi_portal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE sensi_portal;

-- Users table - Used by User.php class
-- Methods: login(), createUser(), getUserById(), updateUserRole(), getAllUsers()
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'staff') DEFAULT 'staff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_role (role)
);

-- Files table - Used by FileManager.php class
-- Methods: uploadFile(), getFileById(), getFiles(), downloadFile(), deleteFile()
-- Stores metadata for securely uploaded files with original and stored names
CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL UNIQUE,
    mime_type VARCHAR(100) NOT NULL,
    file_size INT NOT NULL,
    uploaded_by INT NOT NULL,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_upload_date (upload_date)
);

-- Audit log table - Used by AuditLogger.php class
-- Methods: log(), logLoginAttempt(), logFileUpload(), logFileDownload(), getLogs()
-- Tracks all security events and user actions for compliance
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_timestamp (timestamp),
    INDEX idx_ip_address (ip_address)
);

-- Insert default admin user (password: Admin123!)
-- This hash is generated by Security::hashPassword() method
-- User.php->createUser() method creates users with proper password hashing
INSERT INTO users (email, password_hash, role) VALUES 
('admin@sensicompany.com', '$argon2id$v=19$m=65536,t=4,p=3$YWJjZGVmZ2hpams$+8yQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ', 'admin');

-- Note: The above password hash is a placeholder. In production, generate it using:
-- Security::hashPassword('Admin123!') - supports both Argon2ID and bcrypt fallback

-- Create indexes for better performance
-- Optimizes FileManager::getFiles() queries by user and date
CREATE INDEX idx_files_composite ON files(uploaded_by, upload_date);
-- Optimizes AuditLogger::getLogs() queries by user and timestamp
CREATE INDEX idx_audit_composite ON audit_log(user_id, timestamp);

-- File statistics view - Can be used by admin dashboard
-- Provides summary data that could be accessed via FileManager or User classes
-- Shows total files, storage usage, and last activity per user
CREATE VIEW file_stats AS
SELECT 
    u.email,
    COUNT(f.id) as total_files,
    SUM(f.file_size) as total_size,
    MAX(f.upload_date) as last_upload
FROM users u
LEFT JOIN files f ON u.id = f.uploaded_by
GROUP BY u.id, u.email;

-- TABLE-TO-CLASS MAPPING SUMMARY:
-- users table ↔ User.php (authentication, user management)
-- files table ↔ FileManager.php (file operations, metadata storage)
-- audit_log table ↔ AuditLogger.php (security logging, compliance tracking)
-- All tables use Security.php for password hashing and security utilities
