-- Enhanced Audit and Version Control System
USE risk_assessment_db;

-- Assessment Versions Table
CREATE TABLE IF NOT EXISTS assessment_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL,
    version_number INT NOT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    version_type VARCHAR(50) DEFAULT 'auto' COMMENT 'auto, manual, scheduled',
    version_notes TEXT NULL COMMENT 'User provided notes',
    snapshot_data JSON NOT NULL COMMENT 'Complete assessment state',
    status VARCHAR(50) NULL,
    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_version (assessment_id, version_number),
    INDEX idx_assessment (assessment_id),
    INDEX idx_created_by (created_by),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User Activity Tracking
CREATE TABLE IF NOT EXISTS user_activity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    activity_type VARCHAR(50) NOT NULL COMMENT 'page_view, search, export, download, etc',
    activity_description TEXT NULL,
    page_url VARCHAR(500) NULL,
    duration_seconds INT NULL COMMENT 'Time spent on page/activity',
    metadata JSON NULL COMMENT 'Additional activity data',
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (activity_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Change Requests (for approval workflows)
CREATE TABLE IF NOT EXISTS change_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    requested_by INT NOT NULL,
    requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_by INT NULL,
    approved_at DATETIME NULL,
    rejected_by INT NULL,
    rejected_at DATETIME NULL,
    status VARCHAR(50) DEFAULT 'pending' COMMENT 'pending, approved, rejected',
    change_type VARCHAR(50) NOT NULL COMMENT 'update, delete',
    old_data JSON NULL,
    new_data JSON NULL,
    reason TEXT NULL,
    approval_notes TEXT NULL,
    FOREIGN KEY (requested_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (rejected_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_status (status),
    INDEX idx_requested_by (requested_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data Retention Policies
CREATE TABLE IF NOT EXISTS retention_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    retention_days INT NOT NULL DEFAULT 365,
    archive_after_days INT NULL,
    auto_delete TINYINT(1) DEFAULT 0,
    policy_description TEXT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_entity (entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default retention policies
INSERT INTO retention_policies (entity_type, retention_days, archive_after_days, policy_description) VALUES
('audit_log', 730, 365, 'Audit logs retained for 2 years, archived after 1 year'),
('assessment_versions', 1095, 730, 'Assessment versions retained for 3 years'),
('user_activity', 365, 180, 'User activity logs retained for 1 year'),
('assessments', 1825, 1095, 'Assessments retained for 5 years (regulatory requirement)');

-- Triggers to auto-create versions on major changes
DELIMITER //

CREATE TRIGGER after_assessment_update
AFTER UPDATE ON assessments
FOR EACH ROW
BEGIN
    -- Only create version if status changed or significant fields changed
    IF OLD.status != NEW.status OR 
       OLD.title != NEW.title OR
       OLD.location_id != NEW.location_id THEN
        
        INSERT INTO assessment_versions (
            assessment_id, 
            version_number, 
            created_by,
            version_type,
            version_notes,
            snapshot_data,
            status
        )
        SELECT 
            NEW.id,
            COALESCE(MAX(version_number), 0) + 1,
            NEW.updated_by,
            'auto',
            CONCAT('Auto-saved: Status changed from ', OLD.status, ' to ', NEW.status),
            JSON_OBJECT(
                'id', OLD.id,
                'title', OLD.title,
                'assessment_number', OLD.assessment_number,
                'location_id', OLD.location_id,
                'visit_date', OLD.visit_date,
                'status', OLD.status,
                'overall_risk_level', OLD.overall_risk_level,
                'updated_at', OLD.updated_at
            ),
            NEW.status
        FROM assessment_versions
        WHERE assessment_id = NEW.id;
    END IF;
END//

DELIMITER ;

SELECT 'Audit and version control schema created successfully!' as Status;
