const mysql = require('mysql2/promise')
require('dotenv').config()

// Database configuration
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'cms_db',
  charset: 'utf8mb4'
}

async function insertSampleRoles() {
  let connection
  
  try {
    console.log('Connecting to database...')
    connection = await mysql.createConnection(dbConfig)
    console.log('Connected to database successfully!')
    
    // Additional sample roles for better testing
    const additionalRoles = [
      {
        name: 'Manager',
        description: 'Department manager with elevated privileges',
        level: 40
      },
      {
        name: 'Supervisor',
        description: 'Team supervisor with limited administrative access',
        level: 30
      },
      {
        name: 'Officer',
        description: 'Senior officer with specific departmental access',
        level: 25
      },
      {
        name: 'Staff',
        description: 'Regular staff member with basic access',
        level: 15
      },
      {
        name: 'Intern',
        description: 'Intern with very limited access',
        level: 5
      }
    ]
    
    console.log('Inserting additional sample roles...')
    
    for (const role of additionalRoles) {
      try {
        // Check if role already exists
        const [existing] = await connection.execute(
          'SELECT id FROM roles WHERE name = ?',
          [role.name]
        )
        
        if (existing.length > 0) {
          console.log(`Role "${role.name}" already exists, skipping...`)
          continue
        }
        
        // Insert role
        const [result] = await connection.execute(
          'INSERT INTO roles (name, description, level, is_active) VALUES (?, ?, ?, 1)',
          [role.name, role.description, role.level]
        )
        
        console.log(`✅ Inserted role: ${role.name} (ID: ${result.insertId})`)
        
        // Assign some basic permissions to each role based on level
        let permissionsToAssign = []
        
        if (role.level >= 40) { // Manager and above
          permissionsToAssign = [
            'user.read', 'user.update',
            'unit.read', 'department.read',
            'message.create', 'message.read', 'message.update',
            'conversation.create', 'conversation.read', 'conversation.update',
            'notification.read', 'notification.update',
            'feedback.read', 'feedback.update',
            'dashboard.view', 'dashboard.analytics'
          ]
        } else if (role.level >= 30) { // Supervisor and above
          permissionsToAssign = [
            'user.read',
            'unit.read', 'department.read',
            'message.create', 'message.read', 'message.update',
            'conversation.create', 'conversation.read',
            'notification.read', 'notification.update',
            'feedback.read',
            'dashboard.view'
          ]
        } else if (role.level >= 25) { // Officer and above
          permissionsToAssign = [
            'user.read',
            'message.create', 'message.read', 'message.update',
            'conversation.create', 'conversation.read',
            'notification.read',
            'feedback.create', 'feedback.read',
            'dashboard.view'
          ]
        } else if (role.level >= 15) { // Staff and above
          permissionsToAssign = [
            'message.create', 'message.read',
            'conversation.create', 'conversation.read',
            'notification.read',
            'feedback.create', 'feedback.read',
            'dashboard.view'
          ]
        } else { // Intern
          permissionsToAssign = [
            'message.read',
            'conversation.read',
            'notification.read',
            'feedback.create',
            'dashboard.view'
          ]
        }
        
        // Get permission IDs for the permissions to assign
        if (permissionsToAssign.length > 0) {
          const [permissions] = await connection.execute(
            `SELECT id FROM permissions WHERE name IN (${permissionsToAssign.map(() => '?').join(',')}) AND is_active = 1`,
            permissionsToAssign
          )
          
          // Insert role permissions
          for (const permission of permissions) {
            await connection.execute(
              'INSERT INTO role_permissions (role_id, permission_id) VALUES (?, ?)',
              [result.insertId, permission.id]
            )
          }
          
          console.log(`  📋 Assigned ${permissions.length} permissions to ${role.name}`)
        }
        
      } catch (error) {
        console.error(`❌ Error inserting role "${role.name}":`, error.message)
      }
    }
    
    console.log('\n✅ Sample roles insertion completed!')
    
    // Display summary
    const [roles] = await connection.execute(
      'SELECT r.*, COUNT(rp.permission_id) as permission_count FROM roles r LEFT JOIN role_permissions rp ON r.id = rp.role_id WHERE r.is_active = 1 GROUP BY r.id ORDER BY r.level DESC'
    )
    
    console.log('\n📊 Current roles in database:')
    console.log('┌─────┬─────────────┬─────────────────────────────────┬───────┬──────────────┐')
    console.log('│ ID  │ Name        │ Description                     │ Level │ Permissions  │')
    console.log('├─────┼─────────────┼─────────────────────────────────┼───────┼──────────────┤')
    
    roles.forEach(role => {
      const name = role.name.padEnd(11)
      const desc = role.description.substring(0, 31).padEnd(31)
      const level = role.level.toString().padStart(5)
      const permCount = role.permission_count.toString().padStart(12)
      console.log(`│ ${role.id.toString().padStart(3)} │ ${name} │ ${desc} │ ${level} │ ${permCount} │`)
    })
    
    console.log('└─────┴─────────────┴─────────────────────────────────┴───────┴──────────────┘')
    
  } catch (error) {
    console.error('❌ Database error:', error.message)
  } finally {
    if (connection) {
      await connection.end()
      console.log('Database connection closed.')
    }
  }
}

// Run the script
if (require.main === module) {
  insertSampleRoles()
    .then(() => {
      console.log('Script completed successfully!')
      process.exit(0)
    })
    .catch((error) => {
      console.error('Script failed:', error)
      process.exit(1)
    })
}

module.exports = { insertSampleRoles }



