const { pool } = require('./src/config/database')

async function setupSecurityTables() {
  console.log('🔧 Setting up security tables...')
  
  try {
    // Create failed_login_attempts table
    console.log('Creating failed_login_attempts table...')
    await pool.execute(`
      CREATE TABLE IF NOT EXISTS failed_login_attempts (
        id int(11) NOT NULL AUTO_INCREMENT,
        login_id varchar(100) DEFAULT NULL,
        organization_id int(11) DEFAULT NULL,
        ip_address varchar(45) DEFAULT NULL,
        user_agent text DEFAULT NULL,
        attempted_at timestamp NULL DEFAULT current_timestamp(),
        reason varchar(255) DEFAULT 'Invalid credentials',
        PRIMARY KEY (id),
        KEY idx_login_id (login_id),
        KEY idx_organization_id (organization_id),
        KEY idx_attempted_at (attempted_at),
        KEY idx_ip_address (ip_address),
        CONSTRAINT failed_login_attempts_ibfk_1 FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 
      COMMENT='Tracks failed login attempts for security monitoring'
    `)
    console.log('✅ failed_login_attempts table created successfully')

    // Create password_change_history table
    console.log('Creating password_change_history table...')
    await pool.execute(`
      CREATE TABLE IF NOT EXISTS password_change_history (
        id int(11) NOT NULL AUTO_INCREMENT,
        user_id int(11) NOT NULL,
        changed_by int(11) NOT NULL,
        organization_id int(11) NOT NULL DEFAULT 1,
        ip_address varchar(45) DEFAULT NULL,
        user_agent text DEFAULT NULL,
        changed_at timestamp NULL DEFAULT current_timestamp(),
        change_type enum('self','admin_reset','forced') DEFAULT 'self' COMMENT 'Type of password change',
        PRIMARY KEY (id),
        KEY idx_user_id (user_id),
        KEY idx_changed_by (changed_by),
        KEY idx_organization_id (organization_id),
        KEY idx_changed_at (changed_at),
        CONSTRAINT password_change_history_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
        CONSTRAINT password_change_history_ibfk_2 FOREIGN KEY (changed_by) REFERENCES users (id) ON DELETE CASCADE,
        CONSTRAINT password_change_history_ibfk_3 FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 
      COMMENT='Tracks password change history for security auditing'
    `)
    console.log('✅ password_change_history table created successfully')

    // Create additional indexes for better performance
    console.log('Creating performance indexes...')
    await pool.execute(`
      CREATE INDEX IF NOT EXISTS idx_failed_login_attempts_24h 
      ON failed_login_attempts(attempted_at DESC)
    `).catch(() => {
      // Index might already exist, ignore error
      console.log('  Index idx_failed_login_attempts_24h already exists or created')
    })

    await pool.execute(`
      CREATE INDEX IF NOT EXISTS idx_password_change_history_7d 
      ON password_change_history(changed_at DESC)
    `).catch(() => {
      // Index might already exist, ignore error
      console.log('  Index idx_password_change_history_7d already exists or created')
    })
    console.log('✅ Performance indexes created successfully')

    console.log('\n✅ Security tables setup completed successfully!')
    console.log('\nYou can now use the following API endpoints:')
    console.log('  - GET /api/security/failed-logins?hours=24')
    console.log('  - GET /api/security/password-changes?days=7')
    console.log('  - GET /api/security/overview')
    
  } catch (error) {
    console.error('❌ Error setting up security tables:', error.message)
    throw error
  } finally {
    await pool.end()
  }
}

// Run the setup
setupSecurityTables()
  .then(() => {
    console.log('\n🎉 Setup completed!')
    process.exit(0)
  })
  .catch((error) => {
    console.error('\n❌ Setup failed:', error)
    process.exit(1)
  })

