

-- 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)
);

-- 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);

-- 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
