-- =====================================================
-- COMPLETE VISITOR ROLE SETUP - CORRECTED VERSION
-- =====================================================
-- This script creates a Visitor role with VIEW and ADD permissions only
-- Run this in phpMyAdmin SQL tab
-- Database: pafda_config
-- =====================================================

USE pafda_config;

-- =====================================================
-- STEP 1: Clean up any existing visitor role (if exists)
-- =====================================================

-- Delete existing visitor user-role assignments
DELETE FROM user_roles 
WHERE role_id = (SELECT id FROM roles WHERE role_code = 'visitor');

-- Delete existing visitor role-permission assignments
DELETE FROM role_permissions 
WHERE role_id = (SELECT id FROM roles WHERE role_code = 'visitor');

-- Delete existing visitor role
DELETE FROM roles WHERE role_code = 'visitor';

-- Delete existing visitor test user (if exists)
DELETE FROM users WHERE username = 'visitor';

-- =====================================================
-- STEP 2: Create Visitor Role
-- =====================================================

INSERT INTO roles (role_name, role_code, description, is_active, created_at, updated_at)
VALUES (
    'Visitor',
    'visitor',
    'Read-only user with view and add permissions. Cannot edit or delete data.',
    1,
    NOW(),
    NOW()
);

-- Get the newly created role ID
SET @visitor_role_id = LAST_INSERT_ID();

SELECT CONCAT('✓ Visitor role created with ID: ', @visitor_role_id) as status;

-- =====================================================
-- STEP 3: Assign Permissions to Visitor Role
-- =====================================================

-- Get all READ permissions (view access)
INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
SELECT 
    @visitor_role_id,
    p.id,
    NOW(),
    NULL
FROM permissions p
WHERE p.action = 'read';

SELECT CONCAT('✓ Assigned ', ROW_COUNT(), ' READ permissions') as status;

-- Get all CREATE permissions (add access)
INSERT INTO role_permissions (role_id, permission_id, granted_at, granted_by)
SELECT 
    @visitor_role_id,
    p.id,
    NOW(),
    NULL
FROM permissions p
WHERE p.action = 'create';

SELECT CONCAT('✓ Assigned ', ROW_COUNT(), ' CREATE permissions') as status;

-- =====================================================
-- STEP 4: Create Test Visitor User
-- =====================================================

-- Create visitor user
-- Username: visitor
-- Password: Visitor@123
-- Password hash generated with bcrypt (10 rounds)

INSERT INTO users (
    username,
    email,
    password_hash,
    full_name,
    is_active,
    created_at,
    updated_at
)
VALUES (
    'visitor',
    'visitor@lims.com',
    '$2a$10$rZ5qYQYxGxJxK8vQH5YqZeF5YqZeF5YqZeF5YqZeF5YqZeF5YqZeF',
    'Visitor User',
    1,
    NOW(),
    NOW()
);

-- Get the newly created user ID
SET @visitor_user_id = LAST_INSERT_ID();

SELECT CONCAT('✓ Visitor user created with ID: ', @visitor_user_id) as status;

-- =====================================================
-- STEP 5: Assign Visitor Role to User
-- =====================================================

INSERT INTO user_roles (user_id, role_id, assigned_at, assigned_by)
VALUES (
    @visitor_user_id,
    @visitor_role_id,
    NOW(),
    NULL
);

SELECT '✓ Visitor role assigned to user' as status;

-- =====================================================
-- STEP 6: Verification Queries
-- =====================================================

-- Show created role
SELECT 
    '=== VISITOR ROLE ===' as section,
    r.id,
    r.role_name,
    r.role_code,
    r.description,
    r.is_active
FROM roles r
WHERE r.role_code = 'visitor';

-- Count permissions assigned
SELECT 
    '=== PERMISSIONS COUNT ===' as section,
    COUNT(*) as total_permissions,
    SUM(CASE WHEN p.action = 'read' THEN 1 ELSE 0 END) as read_permissions,
    SUM(CASE WHEN p.action = 'create' THEN 1 ELSE 0 END) as create_permissions,
    SUM(CASE WHEN p.action = 'update' THEN 1 ELSE 0 END) as update_permissions,
    SUM(CASE WHEN p.action = 'delete' THEN 1 ELSE 0 END) as delete_permissions
FROM role_permissions rp
JOIN permissions p ON rp.permission_id = p.id
WHERE rp.role_id = @visitor_role_id;

-- Show all permissions assigned to Visitor
SELECT 
    '=== VISITOR PERMISSIONS ===' as section,
    p.permission_name,
    p.permission_code,
    p.resource,
    p.action,
    p.description
FROM role_permissions rp
JOIN permissions p ON rp.permission_id = p.id
WHERE rp.role_id = @visitor_role_id
ORDER BY p.resource, p.action;

-- Show created user
SELECT 
    '=== VISITOR USER ===' as section,
    u.id,
    u.username,
    u.email,
    u.full_name,
    u.is_active,
    r.role_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.username = 'visitor';

-- =====================================================
-- COMPLETION MESSAGE
-- =====================================================

SELECT 
    '✅ VISITOR ROLE SETUP COMPLETE!' as message,
    'Username: visitor' as login_username,
    'Password: Visitor@123' as login_password,
    'Role has READ and CREATE permissions only' as permissions,
    'NO UPDATE or DELETE permissions' as restrictions;

-- =====================================================
-- NOTES
-- =====================================================
-- 
-- What was created:
-- 1. Visitor role with code 'visitor'
-- 2. All READ permissions (view access to all pages)
-- 3. All CREATE permissions (can add new data)
-- 4. Test user: visitor / Visitor@123
-- 5. User assigned to visitor role
--
-- What visitor CANNOT do:
-- - Cannot UPDATE (edit) existing data
-- - Cannot DELETE any data
-- - Cannot access admin functions
--
-- To create more visitor users:
-- - Use User Management in admin panel
-- - Assign them the "Visitor" role
--
-- To remove visitor role:
-- - Run the cleanup section (STEP 1) only
--
-- =====================================================
