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

async function addManagementPermissions() {
  let connection;
  
  try {
    // Create database connection
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'navy_cms',
      port: process.env.DB_PORT || 3306
    });

    console.log('✅ Connected to database');

    // Read and execute SQL file
    const fs = require('fs');
    const path = require('path');
    const sqlFile = path.join(__dirname, 'add_management_permissions.sql');
    const sql = fs.readFileSync(sqlFile, 'utf8');

    // Extract INSERT statement (before SELECT)
    const insertMatch = sql.match(/INSERT IGNORE INTO.*?;/s);
    if (insertMatch) {
      const insertStatement = insertMatch[0];
      console.log('Executing INSERT statement...');
      await connection.execute(insertStatement);
      console.log('✅ INSERT statement executed');
    } else {
      console.log('⚠️ No INSERT statement found in SQL file');
    }

    // Verify permissions were added
    const [rows] = await connection.execute(`
      SELECT 
        p.id,
        p.name,
        p.description,
        p.resource,
        p.action,
        p.is_active,
        COUNT(rp.role_id) as assigned_to_roles
      FROM permissions p
      LEFT JOIN role_permissions rp ON p.id = rp.permission_id
      WHERE p.resource = 'management'
      GROUP BY p.id, p.name, p.description, p.resource, p.action, p.is_active
      ORDER BY p.action, p.id
    `);

    console.log('\n✅ Management Permissions Added Successfully!');
    console.log(`\n📊 Total Permissions Added: ${rows.length}`);
    console.log('\n📋 Permissions List:');
    rows.forEach((row, index) => {
      console.log(`${index + 1}. ${row.name} - ${row.description} (Assigned to ${row.assigned_to_roles} role(s))`);
    });

  } catch (error) {
    console.error('❌ Error adding management permissions:', error);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n✅ Database connection closed');
    }
  }
}

// Run the script
addManagementPermissions();

