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

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 || process.env.DB_PASS || '',
  database: process.env.DB_NAME || 'cms_db',
  multipleStatements: true,
  charset: 'utf8mb4'
};

async function addLocationPermissions() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Adding Contact Location Field Permissions');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    // Read SQL file
    const sqlFile = path.join(__dirname, 'add_contact_location_permissions.sql');
    const sql = fs.readFileSync(sqlFile, 'utf8');
    
    // Execute SQL
    const statements = sql.split(';').filter(s => s.trim().length > 0 && !s.trim().startsWith('--'));
    
    for (const statement of statements) {
      const trimmed = statement.trim();
      if (trimmed.length > 0 && !trimmed.startsWith('--') && !trimmed.startsWith('SELECT')) {
        try {
          await connection.query(trimmed);
          console.log('✅ Executed:', trimmed.substring(0, 60) + '...');
        } catch (error) {
          if (error.message.includes('Duplicate entry') || error.message.includes('already exists')) {
            console.log('⚠️  Permission already exists, skipping...');
          } else {
            console.error('❌ Error:', error.message);
          }
        }
      }
    }
    
    // Verify permissions
    const [permissions] = await connection.query(
      `SELECT id, name, description, resource, action, is_active 
       FROM permissions 
       WHERE resource = 'contact' AND action LIKE 'address.%' 
       ORDER BY id`
    );
    
    console.log('');
    console.log('========================================');
    console.log('✅ Permissions added successfully!');
    console.log('========================================');
    console.log('');
    console.log('Added permissions:');
    permissions.forEach(p => {
      console.log(`  - ${p.name} (ID: ${p.id})`);
    });
    console.log('');
    
  } catch (error) {
    console.error('');
    console.error('========================================');
    console.error('❌ ERROR: Failed to add permissions!');
    console.error('========================================');
    console.error('');
    console.error('Error details:');
    console.error(error.message);
    if (error.stack) {
      console.error('Stack:', error.stack);
    }
    console.error('');
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Database connection closed');
    }
  }
}

addLocationPermissions()
  .then(() => {
    console.log('Migration script completed successfully!');
    process.exit(0);
  })
  .catch((error) => {
    console.error('Fatal error:', error);
    process.exit(1);
  });

