-- ============================================================================
-- AIMS - Admin Roles & Permissions System
-- Role-Based Access Control (RBAC) for Administrative Functions
-- ============================================================================

USE aims_db;

-- ============================================================================
-- ADMIN ROLES TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS admin_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    role_description TEXT,
    role_level INT DEFAULT 1 COMMENT '1-Low, 5-Medium, 10-Super Admin',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role_name (role_name),
    INDEX idx_role_level (role_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ADMIN PERMISSIONS TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS admin_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(50) UNIQUE NOT NULL,
    permission_name VARCHAR(100) NOT NULL,
    permission_description TEXT,
    permission_category ENUM('users', 'content', 'system', 'data', 'reports') DEFAULT 'system',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_permission_key (permission_key),
    INDEX idx_category (permission_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ROLE PERMISSIONS (Junction Table)
-- ============================================================================
CREATE TABLE IF NOT EXISTS role_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    granted_by INT DEFAULT NULL,
    FOREIGN KEY (role_id) REFERENCES admin_roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES admin_permissions(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_role_permission (role_id, permission_id),
    INDEX idx_role (role_id),
    INDEX idx_permission (permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- UPDATE USERS TABLE TO ADD ADMIN ROLE
-- ============================================================================
ALTER TABLE users 
    ADD COLUMN admin_role_id INT DEFAULT NULL AFTER user_type,
    ADD COLUMN last_activity TIMESTAMP NULL DEFAULT NULL AFTER last_login,
    ADD COLUMN ip_address VARCHAR(45) DEFAULT NULL AFTER last_activity,
    ADD FOREIGN KEY (admin_role_id) REFERENCES admin_roles(id) ON DELETE SET NULL;

CREATE INDEX idx_users_admin_role ON users(admin_role_id);
CREATE INDEX idx_users_last_activity ON users(last_activity);

-- ============================================================================
-- ADMIN ACTIVITY LOG TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS admin_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    action_type VARCHAR(100) NOT NULL,
    action_description TEXT,
    affected_table VARCHAR(50),
    affected_record_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_data JSON COMMENT 'Stores request parameters',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_admin (admin_id),
    INDEX idx_action (action_type),
    INDEX idx_created (created_at),
    INDEX idx_table_record (affected_table, affected_record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SYSTEM AUDIT LOG TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS system_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_type ENUM('login', 'logout', 'create', 'update', 'delete', 'approve', 'reject', 'export', 'import') NOT NULL,
    user_id INT DEFAULT NULL,
    entity_type VARCHAR(50) COMMENT 'Table or entity affected',
    entity_id INT DEFAULT NULL,
    old_data JSON COMMENT 'Previous state before change',
    new_data JSON COMMENT 'New state after change',
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_event (event_type),
    INDEX idx_user (user_id),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INSERT DEFAULT ADMIN ROLES
-- ============================================================================

-- Super Administrator (Full Access)
INSERT INTO admin_roles (role_name, role_description, role_level) VALUES
('Super Administrator', 'Full system access with all permissions', 10);

SET @super_admin_id = LAST_INSERT_ID();

-- Administrator (High Level Access)
INSERT INTO admin_roles (role_name, role_description, role_level) VALUES
('Administrator', 'High-level access for day-to-day administration', 8);

SET @admin_id = LAST_INSERT_ID();

-- Content Manager
INSERT INTO admin_roles (role_name, role_description, role_level) VALUES
('Content Manager', 'Manages agricultural tips, prices, and content', 5);

SET @content_mgr_id = LAST_INSERT_ID();

-- User Moderator
INSERT INTO admin_roles (role_name, role_description, role_level) VALUES
('User Moderator', 'Manages users, approvals, and reports', 5);

SET @user_mod_id = LAST_INSERT_ID();

-- Data Analyst
INSERT INTO admin_roles (role_name, role_description, role_level) VALUES
('Data Analyst', 'View-only access to analytics and reports', 3);

SET @analyst_id = LAST_INSERT_ID();

-- ============================================================================
-- INSERT ADMIN PERMISSIONS
-- ============================================================================

-- Dashboard & Analytics
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('view_dashboard', 'View Dashboard', 'Access to admin dashboard', 'system'),
('view_analytics', 'View Analytics', 'Access to analytics and statistics', 'reports'),
('view_logs', 'View System Logs', 'Access to system and activity logs', 'system');

-- User Management
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_users', 'Manage Users', 'Create, edit, view user accounts', 'users'),
('approve_users', 'Approve Users', 'Approve or reject user registrations', 'users'),
('delete_users', 'Delete Users', 'Permanently delete user accounts', 'users'),
('ban_users', 'Ban Users', 'Ban or suspend user accounts', 'users'),
('export_users', 'Export User Data', 'Export user data to files', 'users');

-- Location Management
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_locations', 'Manage Locations', 'CRUD operations on location hierarchy', 'data'),
('import_locations', 'Import Locations', 'Bulk import location data', 'data');

-- Product Management
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_products', 'Manage Products', 'Moderate and manage marketplace products', 'content'),
('delete_products', 'Delete Products', 'Remove products from marketplace', 'content');

-- Order Management
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('view_orders', 'View Orders', 'View all system orders', 'data'),
('manage_orders', 'Manage Orders', 'Update order statuses', 'data');

-- Market Prices
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_prices', 'Manage Market Prices', 'Add, edit, delete market prices', 'content'),
('import_prices', 'Import Prices', 'Bulk import market price data', 'content');

-- Content Management
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_content', 'Manage Content', 'Manage tips, resources, and content', 'content'),
('publish_content', 'Publish Content', 'Approve and publish content', 'content');

-- Reports & Moderation
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_reports', 'Manage Reports', 'Review and resolve user reports', 'users'),
('view_reports', 'View Reports', 'Access to user-submitted reports', 'users');

-- System Settings
INSERT INTO admin_permissions (permission_key, permission_name, permission_description, permission_category) VALUES
('manage_settings', 'Manage Settings', 'Configure system settings', 'system'),
('manage_roles', 'Manage Roles & Permissions', 'Configure admin roles and permissions', 'system'),
('full_access', 'Full System Access', 'Complete unrestricted access', 'system');

-- ============================================================================
-- ASSIGN PERMISSIONS TO ROLES
-- ============================================================================

-- Super Administrator: ALL PERMISSIONS
INSERT INTO role_permissions (role_id, permission_id)
SELECT @super_admin_id, id FROM admin_permissions;

-- Administrator: Most permissions except full access
INSERT INTO role_permissions (role_id, permission_id)
SELECT @admin_id, id FROM admin_permissions 
WHERE permission_key != 'full_access' AND permission_key != 'manage_roles';

-- Content Manager: Content-related permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT @content_mgr_id, id FROM admin_permissions 
WHERE permission_key IN (
    'view_dashboard',
    'manage_content',
    'publish_content',
    'manage_prices',
    'import_prices',
    'manage_products',
    'view_analytics'
);

-- User Moderator: User management permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT @user_mod_id, id FROM admin_permissions 
WHERE permission_key IN (
    'view_dashboard',
    'manage_users',
    'approve_users',
    'ban_users',
    'manage_reports',
    'view_reports',
    'view_logs'
);

-- Data Analyst: View-only permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT @analyst_id, id FROM admin_permissions 
WHERE permission_key IN (
    'view_dashboard',
    'view_analytics',
    'view_logs',
    'view_orders',
    'view_reports'
);

-- ============================================================================
-- UPDATE EXISTING ADMIN USER
-- ============================================================================

-- Assign Super Administrator role to existing admin
UPDATE users 
SET admin_role_id = @super_admin_id 
WHERE user_type IN ('admin', 'super_admin') 
AND admin_role_id IS NULL;

-- ============================================================================
-- STORED PROCEDURES FOR ADMIN FUNCTIONS
-- ============================================================================

-- Get user permissions
DELIMITER //
CREATE PROCEDURE GetUserPermissions(IN userId INT)
BEGIN
    SELECT 
        p.permission_key,
        p.permission_name,
        p.permission_description,
        p.permission_category
    FROM admin_permissions p
    JOIN role_permissions rp ON p.id = rp.permission_id
    JOIN admin_roles ar ON rp.role_id = ar.id
    JOIN users u ON u.admin_role_id = ar.id
    WHERE u.id = userId AND ar.is_active = 1
    ORDER BY p.permission_category, p.permission_name;
END //
DELIMITER ;

-- Log admin activity
DELIMITER //
CREATE PROCEDURE LogAdminActivity(
    IN p_admin_id INT,
    IN p_action_type VARCHAR(100),
    IN p_description TEXT,
    IN p_table VARCHAR(50),
    IN p_record_id INT,
    IN p_ip_address VARCHAR(45)
)
BEGIN
    INSERT INTO admin_activity_log 
        (admin_id, action_type, action_description, affected_table, affected_record_id, ip_address)
    VALUES 
        (p_admin_id, p_action_type, p_description, p_table, p_record_id, p_ip_address);
END //
DELIMITER ;

-- Get admin dashboard statistics
DELIMITER //
CREATE PROCEDURE GetAdminDashboardStats()
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM users WHERE is_active = 1) as total_active_users,
        (SELECT COUNT(*) FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as new_users_month,
        (SELECT COUNT(*) FROM user_approvals WHERE approval_status = 'pending') as pending_approvals,
        (SELECT COUNT(*) FROM products WHERE status = 'available') as active_products,
        (SELECT COUNT(*) FROM orders WHERE status = 'pending') as pending_orders,
        (SELECT COUNT(*) FROM reports WHERE status = 'pending') as pending_reports,
        (SELECT COUNT(DISTINCT admin_id) FROM admin_activity_log WHERE created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) as active_admins_today;
END //
DELIMITER ;

-- ============================================================================
-- VIEWS FOR ADMIN REPORTING
-- ============================================================================

-- View: Admin users with their roles
CREATE OR REPLACE VIEW v_admin_users AS
SELECT 
    u.id,
    u.full_name,
    u.phone_number,
    u.email,
    u.user_type,
    ar.role_name,
    ar.role_level,
    u.last_login,
    u.last_activity,
    u.is_active,
    u.created_at
FROM users u
LEFT JOIN admin_roles ar ON u.admin_role_id = ar.id
WHERE u.user_type IN ('admin', 'super_admin')
ORDER BY ar.role_level DESC, u.full_name;

-- View: Recent admin activities
CREATE OR REPLACE VIEW v_recent_admin_activities AS
SELECT 
    aal.*,
    u.full_name as admin_name,
    ar.role_name as admin_role
FROM admin_activity_log aal
JOIN users u ON aal.admin_id = u.id
LEFT JOIN admin_roles ar ON u.admin_role_id = ar.id
ORDER BY aal.created_at DESC
LIMIT 100;

-- ============================================================================
-- TRIGGERS FOR AUDIT LOGGING
-- ============================================================================

-- Log user deletions
DELIMITER //
CREATE TRIGGER audit_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO system_audit_log (event_type, user_id, entity_type, entity_id, old_data, ip_address)
    VALUES (
        'delete',
        @current_admin_id,
        'users',
        OLD.id,
        JSON_OBJECT(
            'full_name', OLD.full_name,
            'phone_number', OLD.phone_number,
            'user_type', OLD.user_type,
            'created_at', OLD.created_at
        ),
        @current_ip_address
    );
END //
DELIMITER ;

-- Log approval status changes
DELIMITER //
CREATE TRIGGER audit_approval_update
AFTER UPDATE ON user_approvals
FOR EACH ROW
BEGIN
    IF NEW.approval_status != OLD.approval_status THEN
        INSERT INTO system_audit_log (event_type, user_id, entity_type, entity_id, old_data, new_data, ip_address)
        VALUES (
            IF(NEW.approval_status = 'approved', 'approve', 'reject'),
            NEW.approved_by,
            'user_approvals',
            NEW.id,
            JSON_OBJECT('status', OLD.approval_status),
            JSON_OBJECT('status', NEW.approval_status, 'reason', NEW.rejection_reason),
            @current_ip_address
        );
    END IF;
END //
DELIMITER ;

-- ============================================================================
-- SAMPLE ADMIN ACTIVITY ENTRIES
-- ============================================================================

-- This will be populated automatically as admins perform actions

-- ============================================================================
-- VERIFICATION COMPLETE
-- ============================================================================

SELECT 'Admin Roles & Permissions system setup completed successfully!' as status;
SELECT COUNT(*) as total_roles FROM admin_roles;
SELECT COUNT(*) as total_permissions FROM admin_permissions;
SELECT COUNT(*) as total_role_permissions FROM role_permissions;