manageData.php

<?php

/*
 *
 * aql - Active Query Listing
 *
 * Copyright (C) 2018 Kevin Benton - kbcmdba [at] gmail [dot] com
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along
 * with this program; if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 *
 */

namespace com\kbcmdba\aql ;

session_start() ;

require( 'vendor/autoload.php' ) ;
require( 'utility.php' ) ;

use com\kbcmdba\aql\Libs\Config ;
use com\kbcmdba\aql\Libs\DBConnection ;
use com\kbcmdba\aql\Libs\Exceptions\ConfigurationException ;
use com\kbcmdba\aql\Libs\Exceptions\DaoException;
use com\kbcmdba\aql\Libs\LDAP ;
use com\kbcmdba\aql\Libs\Tools ;
use com\kbcmdba\aql\Libs\WebPage ;

// ///////////////////////////////////////////////////////////////////////////

function checkIs1or0( $value, $errmsg, &$errors ) {
    if ( ( $value !== "1" ) && ( $value !== "0" ) ) {
        $errors .= $errmsg . " needs to be 1 or 0 (yes or no) (got $value)<br />\n";
    }
}

// ///////////////////////////////////////////////////////////////////////////
  
function checkIsNumeric( $value, $errmsg, &$errors ) {
    if ( ! Tools::isNumeric( $value ) ) {
        $errors .= $errmsg . " (got $value)<br />\n";
    }
}

// ///////////////////////////////////////////////////////////////////////////

/**
 * Get ENUM values from a column definition in the database
 * @param mysqli $dbh Database connection handle
 * @param string $table Table name
 * @param string $column Column name
 * @return array Array of ENUM values
 */
function getEnumValues( $dbh, $table, $column ) {
    $values = [] ;
    // SHOW COLUMNS doesn't support prepared statement parameter binding,
    // so we escape the identifiers directly (they come from our code, not user input)
    $safeTable = $dbh->real_escape_string( $table ) ;
    $safeColumn = $dbh->real_escape_string( $column ) ;
    $sql = "SHOW COLUMNS FROM `$safeTable` LIKE '$safeColumn'" ;
    $result = $dbh->query( $sql ) ;
    if ( $result && ( $row = $result->fetch_assoc() ) ) {
        // Type looks like: enum('MySQL','MariaDB','InnoDBCluster',...)
        $type = $row['Type'] ;
        if ( preg_match( "/^enum\('(.*)'\)$/i", $type, $matches ) ) {
            $values = explode( "','", $matches[1] ) ;
        }
    }
    return $values ;
}

// ///////////////////////////////////////////////////////////////////////////

/**
 * Verify that AQL can connect to a host and has required permissions
 * @param string $hostname Host to connect to
 * @param int $port Port number
 * @return array ['success' => bool, 'errors' => string[], 'warnings' => string[]]
 */
function verifyHostPermissions( $hostname, $port ) {
    $result = [
        'success' => true,
        'errors' => [],
        'warnings' => []
    ] ;

    try {
        // Step 1: DNS resolution check
        $ip = @gethostbyname( $hostname ) ;
        if ( $ip === $hostname && ! filter_var( $hostname, FILTER_VALIDATE_IP ) ) {
            $result['success'] = false ;
            $result['errors'][] = "DNS resolution failed: Cannot resolve hostname '$hostname'" ;
            return $result ;
        }

        // Step 2: TCP port reachability check (5 second timeout)
        $socket = @fsockopen( $hostname, $port, $errno, $errstr, 5 ) ;
        if ( ! $socket ) {
            $result['success'] = false ;
            $errorDetail = '' ;
            switch ( $errno ) {
                case 110: // Connection timed out
                case 10060: // Windows timeout
                    $errorDetail = "Connection timed out - host may be down or firewall is blocking port $port" ;
                    break ;
                case 111: // Connection refused
                case 10061: // Windows connection refused
                    $errorDetail = "Connection refused - MySQL may not be running on port $port" ;
                    break ;
                case 113: // No route to host
                case 10065: // Windows no route
                    $errorDetail = "No route to host - network path to '$hostname' is unavailable" ;
                    break ;
                default:
                    $errorDetail = "Cannot reach port $port: $errstr (error $errno)" ;
            }
            $result['errors'][] = "Network check failed: $errorDetail" ;
            return $result ;
        }
        fclose( $socket ) ;

        // Step 3: MySQL connection and privilege checks
        $config = new Config() ;
        $dbUser = $config->getDbUser() ;
        $dbPass = $config->getDbPass() ;

        // Try to connect
        $mysqli = @new \mysqli( $hostname, $dbUser, $dbPass, '', $port ) ;
        if ( $mysqli->connect_error ) {
            $result['success'] = false ;
            $result['errors'][] = "MySQL connection failed: " . $mysqli->connect_error ;
            return $result ;
        }

        // Check PROCESS privilege (required for SHOW PROCESSLIST)
        $processResult = @$mysqli->query( "SHOW PROCESSLIST" ) ;
        if ( ! $processResult ) {
            $result['success'] = false ;
            $result['errors'][] = "PROCESS privilege missing - cannot view queries. Grant with: GRANT PROCESS ON *.* TO '$dbUser'@'%';" ;
        } else {
            $processResult->close() ;
        }

        // Check REPLICATION CLIENT privilege (optional, for replica status)
        $replResult = @$mysqli->query( "SHOW SLAVE STATUS" ) ;
        if ( ! $replResult ) {
            // Try MySQL 8.0.22+ syntax
            $replResult = @$mysqli->query( "SHOW REPLICA STATUS" ) ;
        }
        if ( ! $replResult ) {
            $result['warnings'][] = "REPLICATION CLIENT privilege missing - replica status unavailable. Grant with: GRANT REPLICATION CLIENT ON *.* TO '$dbUser'@'%';" ;
        } else {
            $replResult->close() ;
        }

        $mysqli->close() ;

    } catch ( \Exception $e ) {
        $result['success'] = false ;
        $result['errors'][] = "Error: " . $e->getMessage() ;
    }

    return $result ;
}

// ///////////////////////////////////////////////////////////////////////////

function doLoginOrDie( $page ) {
    // Preserve query string parameters through login
    $dataParam = Tools::param( 'data' ) ;
    $dataHidden = '' ;
    if ( ! Tools::isNullOrEmptyString( $dataParam ) ) {
        $dataHidden = '<input type="hidden" name="data" value="' . htmlspecialchars( $dataParam, ENT_QUOTES, 'UTF-8' ) . '" />' ;
    }
    $loginPage = <<<HTML
<h2>Login</h2>
<div>
    <form method="post" action="manageData.php">
        $dataHidden
        <label for="user"><b>Username</b></label>
        <input type="text" name="user" required="required" />
        <label for="password"><b>Password</b></label>
        <input type="password" name="password" required="required" />
        <input type="submit" name="submit" value="submit" />
    </form>
</div>

HTML;
    if ( ! Tools::isNullOrEmptyString( Tools::param( 'logout' ) ) ) {
        session_unset() ;
        session_destroy() ;
        $page->setBody( $loginPage );
        $page->displayPage() ;
        die() ;
    }
    if ( ! isset( $_SESSION[ 'AuthUser' ] ) || ( ! isset( $_SESSION[ 'AuthCanAccess' ] ) ) ) {
        if  ( !Tools::isNullOrEmptyString( Tools::post( 'user', null, 1 ) )
        && !Tools::isNullOrEmptyString( Tools::post( 'password', null, 1 ) )
            ) {
            if ( LDAP::authenticate( Tools::post( 'user', null, 1 ), Tools::post( 'password', null, 1 ) ) ) {
                return ; // User was successfully logged in.
            }
            echo "Login failed: Incorrect user name, password, or access.<br />" ;
        }
        $page->setBody( $loginPage );
        $page->displayPage() ;
        die() ;
    }
} // END OF doLoginOrDie

// ///////////////////////////////////////////////////////////////////////////

$page = new WebPage( 'AQL: Manage Data' ) ;
$page->setTop( "<h2>AQL: Manage Data</h2>\n"
             .  "<a href=\"./manageData.php?logout=logout\">Log out of Manage Data</a>\n"
             .  "<p />\n"
             ) ;
doLoginOrDie( $page ) ;
switch ( Tools::param( 'data' ) ) {
    case 'Hosts':
        // Create DB connection early so we can fetch ENUM values for validation
        $dbc = new DBConnection();
        $dbh = $dbc->getConnection();
        $dbh->set_charset('utf8');

        // Fetch valid db_type values from ENUM definition in DDL
        $validDbTypes = getEnumValues( $dbh, 'host', 'db_type' ) ;
        if ( empty( $validDbTypes ) ) {
            // Fallback if ENUM fetch fails
            $validDbTypes = [ 'MySQL' ] ;
        }

        // validate input
        $body = '' ;
        $links = '' ;
        $errors = '' ;
        $action = Tools::param( 'action' ) ;
        $hostId = Tools::param( 'hostId' ) ;
        $hostName = Tools::param( 'hostName' ) ;
        $portNumber = Tools::param( 'portNumber') ;
        $description = Tools::param( 'description' ) ;
        $shouldMonitor = Tools::param( 'shouldMonitor' ) ;
        $shouldBackup = Tools::param( 'shouldBackup' ) ;
        $shouldSchemaspy = Tools::param( 'shouldSchemaspy' ) ;
        $revenueImpacting = Tools::param('revenueImpacting' ) ;
        $decommissioned = Tools::param( 'decommissioned' ) ;
        $alertCritSecs = Tools::param( 'alertCritSecs' ) ;
        $alertWarnSecs = Tools::param( 'alertWarnSecs' ) ;
        $alertInfoSecs = Tools::param( 'alertInfoSecs' ) ;
        $alertLowSecs = Tools::param( 'alertLowSecs' ) ;
        $dbType = Tools::param( 'dbType' ) ;

        if (  ( ( 'Update' === $action ) || ( 'Delete' === $action ) )
             && ! Tools::isNumeric( $hostId )
           ) {
            $errors .= "Invalid ID\n" ;
        }
        if ( 'Delete' !== $action ) {
            if ( Tools::isNullOrEmptyString( $hostName ) ) {
                $errors .= "Host Name cannot be empty.<br />\n" ;
            }  
            checkIsNumeric( $portNumber, "Invalid Port Number.\n", $errors ) ;
            checkIs1or0( $shouldMonitor, "Should Monitor", $errors ) ;
            checkIs1or0( $shouldBackup, "Should Backup", $errors ) ;
            checkIs1or0( $shouldSchemaspy, "Should Schemaspy", $errors ) ;
            checkIs1or0( $revenueImpacting, "Revenue Impacting", $errors ) ;
            checkIs1or0( $decommissioned, "Decommissioned", $errors ) ;
            checkIsNumeric( $alertCritSecs, "Alert Seconds: Critical", $errors ) ;
            checkIsNumeric( $alertWarnSecs, "Alert Seconds: Warning", $errors ) ;
            checkIsNumeric( $alertInfoSecs, "Alert Seconds: Info", $errors ) ;
            checkIsNumeric( $alertLowSecs, "Alert Seconds: Low", $errors ) ;
            if ( ! in_array( $dbType, $validDbTypes ) ) {
                $errors .= "Invalid Database Type.<br />\n" ;
            }
        }

        if ( ( '' != $errors ) && ( '' != $action ) ) {
            $page->setBody( $links . $body . $errors ) ;
            $page->displayPage() ;
            exit( 0 ) ;
        }

        // react accordingly
        switch ( $action ) {
            case '':
                // Nothing to see here.
                break;
            case 'Add':
                $body .= 'Add - ' ;
                // Verify host permissions before adding
                $permCheck = verifyHostPermissions( $hostName, $portNumber ) ;
                if ( ! $permCheck['success'] ) {
                    $config = new Config() ;
                    $dbUser = $config->getDbUser() ;
                    $body .= "<span class='msg-error'>Failed - Permission verification failed:</span><br />\n" ;
                    foreach ( $permCheck['errors'] as $err ) {
                        $body .= "<span class='msg-error'>&bull; " . htmlspecialchars( $err ) . "</span><br />\n" ;
                    }
                    $body .= "<p>Run these commands on <strong>" . htmlspecialchars( $hostName ) . ":" . htmlspecialchars( $portNumber ) . "</strong> to fix:</p>\n" ;
                    $body .= "<div class='code-box'>\n" ;
                    $body .= "<pre>" ;
                    $body .= "-- Create user if it doesn't exist\n" ;
                    $body .= "CREATE USER IF NOT EXISTS '" . htmlspecialchars( $dbUser ) . "'@'%' IDENTIFIED BY 'YourPasswordHere';\n\n" ;
                    $body .= "-- Grant required privileges\n" ;
                    $body .= "GRANT PROCESS ON *.* TO '" . htmlspecialchars( $dbUser ) . "'@'%';\n" ;
                    $body .= "GRANT REPLICATION CLIENT ON *.* TO '" . htmlspecialchars( $dbUser ) . "'@'%';\n\n" ;
                    $body .= "-- Apply changes\n" ;
                    $body .= "FLUSH PRIVILEGES;" ;
                    $body .= "</pre>\n" ;
                    $body .= "</div>\n" ;
                    break ;
                }
                // Show warnings but proceed with add
                if ( ! empty( $permCheck['warnings'] ) ) {
                    $body .= "<span class='msg-warning'>Warnings:</span><br />\n" ;
                    foreach ( $permCheck['warnings'] as $warn ) {
                        $body .= "<span class='msg-warning'>&bull; " . htmlspecialchars( $warn ) . "</span><br />\n" ;
                    }
                }
                $sql = 'INSERT INTO host SET hostname = ?, port_number = ?'
                     . ', description = ?, should_monitor = ?, should_backup = ?'
                     . ', should_schemaspy = ?, revenue_impacting = ?, decommissioned = ?'
                     . ', alert_crit_secs = ?, alert_warn_secs = ?'
                     . ', alert_info_secs = ?, alert_low_secs = ?, db_type = ?'
                     . ', created = NOW(), updated = NOW(), last_audited = NOW()'
                     ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'sisiiiiiiiiiis'
                                 , $hostName, $portNumber, $description, $shouldMonitor
                                 , $shouldBackup, $shouldSchemaspy, $revenueImpacting, $decommissioned
                                 , $alertCritSecs, $alertWarnSecs, $alertInfoSecs, $alertLowSecs, $dbType
                                 ) ;
                $body .= ( $stmt->execute() ) ? "<span class='msg-success'>Success.</span><br />\n" : "Failed.<br />\n" ;
            break ;
            case 'Update':
                $body .= 'Update - ' ;
                $sql = 'UPDATE host SET hostname = ?, port_number = ?'
                     . ', description = ?, should_monitor = ?'
                     . ', should_backup = ?, should_schemaspy = ?'
                     . ', revenue_impacting = ?, decommissioned = ?'
                     . ', alert_crit_secs = ?, alert_warn_secs = ?'
                     . ', alert_info_secs = ?, alert_low_secs = ?, db_type = ?'
                     . ', updated = NOW(), last_audited = NOW()'
                     . ' WHERE host_id = ?'
                     ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'sisiiiiiiiiisi'
                                 , $hostName, $portNumber, $description, $shouldMonitor
                                 , $shouldBackup, $shouldSchemaspy, $revenueImpacting, $decommissioned
                                 , $alertCritSecs, $alertWarnSecs, $alertInfoSecs, $alertLowSecs, $dbType
                                 , $hostId
                                 ) ;
                $body .= ( $stmt->execute() ) ? "Success.<br />\n" : "Failed.<br />\n" ;
                break ;
          case 'Delete':
                $body .= 'Delete - ' ;
                $sql = 'DELETE FROM host WHERE host_id = ?' ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'i', $hostId ) ;
                $body .= ( $stmt->execute() ) ? "Success.<br />\n" : "Failed.<br />\n" ;
                break ;
          default:
                $page->setBody( $links, 'Huh?' ) ;
                $page->displayPage() ;
                exit( 0 ) ;
        }

        $allHostsQuery = <<<SQL
SELECT host_id
     , hostname
     , port_number
     , description
     , should_monitor
     , should_backup
     , should_schemaspy
     , revenue_impacting
     , decommissioned
     , alert_crit_secs
     , alert_warn_secs
     , alert_info_secs
     , alert_low_secs
     , db_type
  FROM aql_db.host
 ORDER BY decommissioned DESC, hostname ASC, port_number ASC
 
SQL;
        // Generate db_type dropdown options dynamically from ENUM values
        $dbTypeOptions = '' ;
        foreach ( $validDbTypes as $type ) {
            $selected = ( $type === 'MySQL' ) ? ' selected="selected"' : '' ;
            $dbTypeOptions .= '<option value="' . htmlspecialchars( $type, ENT_QUOTES, 'UTF-8' ) . '"' . $selected . '>'
                           . htmlspecialchars( $type, ENT_QUOTES, 'UTF-8' ) . '</option>' ;
        }

        // Form at the top for easier access
        $body .= <<<HTML
<form id="AddUpdateDeleteHostForm" action="manageData.php">
  <input type="hidden" name="data" value="Hosts">
  <table id="AddUpdateDeleteHostTable" border=1 cellspacing=0 cellpadding=2>
    <caption>Host Form</caption>
    <tr><th colspan="2">ID</th><td><input type="number" id="hostId" name="hostId" readonly="readonly" value="" size=5 /></td></tr>
    <tr><th colspan="2">Host Name</th><td><input type="text" id="hostName" name="hostName" size="32" value="" /></td></tr>
    <tr><th colspan="2">Port Number</th><td><input type="number" id="portNumber" name="portNumber" size="5" value="3306" /></td></tr>
    <tr><th colspan="2">Description</th><td><textarea id="description" name="description" rows="4" cols="60"></textarea></td></tr>
    <tr><th colspan="2">DB Type</th><td><select id="dbType" name="dbType">$dbTypeOptions</select></td></tr>
    <tr><th colspan="2">Should Monitor</th><td><select id="shouldMonitor" name="shouldMonitor"><option value="1" selected="selected">Yes</option><option value="0">No</option></select></td></tr>
    <tr><th colspan="2">Should Backup</th><td><select id="shouldBackup" name="shouldBackup"><option value="1" selected="selected">Yes</option><option value="0">No</option></select></td></tr>
    <tr><th colspan="2">Should Schemaspy</th><td><select id="shouldSchemaspy" name="shouldSchemaspy"><option value="1">Yes</option><option value="0" selected="selected">No</option></select></td></tr>
    <tr><th colspan="2">Revenue Impacting</th><td><select id="revenueImpacting" name="revenueImpacting"><option value="1" selected="selected">Yes</option><option value="0">No</option></select></td></tr>
    <tr><th colspan="2">Decommissioned</th><td><select id="decommissioned" name="decommissioned"><option value="1">Yes</option><option value="0" selected="selected">No</option></select></td></tr>
    <tr><th rowspan="4">Alert Seconds</th><th>Critical</th><td><input type="number" id="alertCritSecs" name="alertCritSecs" size="3" value="" /></td></tr>
    <tr><th>Warning</th><td><input type="number" id="alertWarnSecs" name="alertWarnSecs" size="3" value="" /></td></tr>
    <tr><th>Info</th><td><input type="number" id="alertInfoSecs" name="alertInfoSecs" size="3" value="" /></td></tr>
    <tr><th>Low</th><td><input type="number" id="alertLowSecs" name="alertLowSecs" size="3" value="" /></td></tr>
    </tr>
  </table>
  <input type="submit" name="action" value="Add"> &nbsp;
  <input type="submit" name="action" value="Update"> &nbsp;
  <input type="submit" name="action" value="Delete"> &nbsp;
</form>

<script>
// Default ports for each DB type
var defaultPorts = {
    'MySQL': 3306,
    'MariaDB': 3306,
    'MS-SQL': 1433,
    'Redis': 6379,
    'RLEC': 6379,
    'InnoDBCluster': 3306,
    'InnoDBRouter': 6446
};

// Update port when DB type changes (only if port is still a default value)
document.getElementById('dbType').addEventListener('change', function() {
    var portField = document.getElementById('portNumber');
    var currentPort = parseInt(portField.value, 10);
    var isDefaultPort = Object.values(defaultPorts).indexOf(currentPort) !== -1 || portField.value === '';

    if (isDefaultPort && defaultPorts[this.value]) {
        portField.value = defaultPorts[this.value];
    }
});
</script>

<p></p>

<table id="hostEdit" border=1 cellspacing=0 cellpadding=2 class="tablesorter aql-listing">
  <thead>
    <tr>
      <th rowspan="2">Actions</th>
      <th rowspan="2">Host ID</th>
      <th rowspan="2">Host Name</th>
      <th rowspan="2">Port</th>
      <th rowspan="2">Description</th>
      <th rowspan="2">Should Monitor</th>
      <th rowspan="2">Should Backup</th>
      <th rowspan="2">Should Schemaspy</th>
      <th rowspan="2">Revenue Impacting</th>
      <th rowspan="2">Decommissioned</th>
      <th colspan="4">Alert Seconds</th>
      <th rowspan="2">DB Type</th>
    </tr>
    <tr>
      <th>Critical</th>
      <th>Warn</th>
      <th>Info</th>
      <th>Low</th>
    </tr>
  </thead>
  <tbody>

HTML;
        try {
            $result = $dbh->query( $allHostsQuery );
            if ( ! $result ) {
                throw new \ErrorException( "Query failed: $allHostsQuery\n Error: " . $dbh->error );
            }
            while ( $row = $result->fetch_row() ) {
                // Use json_encode for safe JavaScript string escaping, then htmlspecialchars for HTML attribute context
                $jsHostname = htmlspecialchars( json_encode( $row[1] ), ENT_QUOTES, 'UTF-8' ) ;
                $jsDescription = htmlspecialchars( json_encode( $row[3] ), ENT_QUOTES, 'UTF-8' ) ;
                $jsDbType = htmlspecialchars( json_encode( $row[13] ), ENT_QUOTES, 'UTF-8' ) ;
                $body .= "      <tr>"
                      .  "<td class=\"text-center\">"
                      .  "<button type=\"button\" onclick=\"fillHostForm("
                      .  intval( $row[0] ) . ", "
                      .  $jsHostname . ", "
                      .  intval( $row[2] ) . ", "
                      .  $jsDescription . ", "
                      .  intval( $row[4] ) . ", "
                      .  intval( $row[5] ) . ", "
                      .  intval( $row[6] ) . ", "
                      .  intval( $row[7] ) . ", "
                      .  intval( $row[8] ) . ", "
                      .  intval( $row[9] ) . ", "
                      .  intval( $row[10] ) . ", "
                      .  intval( $row[11] ) . ", "
                      .  intval( $row[12] ) . ", "
                      .  $jsDbType
                      .  "); return false;\">Fill Host Form</button>"
                      .  "</td>"
                      ;
                // Columns 4-8 are boolean flags that get color coding
                $boolCols = [ 4, 5, 6, 7, 8 ] ;
                for ( $i = 0 ; $i < 14 ; $i++ ) {
                    if ( in_array( $i, $boolCols ) ) {
                        $class = ( $row[$i] == 1 ) ? 'bool-yes' : 'bool-no' ;
                        $body .= "<td class=\"$class\">" . htmlspecialchars( $row[$i] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>" ;
                    } else {
                        $body .= "<td>" . htmlspecialchars( $row[$i] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>" ;
                    }
                }
                $body .= "</tr>\n" ;
            }
            $body .= <<<HTML
  </tbody>
</table>

HTML;
            $page->setBody( $links . $body ) ;
        }
        catch (DaoException $e) {
            $page->appendBody( "<pre>Error interacting with the database\n\n" . $e->getMessage() . "\n</pre>\n" ) ;
        }
        $page->displayPage() ;

        break ;
    case 'Groups':
        $body = $links = $errors = '' ;
        $action = Tools::param( 'action' ) ;
        $groupId = Tools::param( 'groupId' ) ;
        $groupTag = Tools::param( 'groupTag' ) ;
        $shortDesc = Tools::param( 'shortDescription' ) ;
        $fullDesc = Tools::param( 'fullDescription' ) ;
        $groupSelection = Tools::params( 'groupSelect' ) ;

        if (  ( ( 'Update' === $action ) || ( 'Delete' === $action ) )
            && ! Tools::isNumeric( $groupId )
        ) {
            $errors .= "Invalid ID\n" ;
        }
        if ( ( 'Add' === $action ) || ( 'Update' === $action ) ) {
            if ( Tools::isNullOrEmptyString( $groupTag ) ) {
                $errors .= "Tag cannot be empty.<br />\n" ;
            }
            if ( strlen( $groupTag ) > 16 )  {
                $errors .= "Group Tag is too long.<br />\n" ;
            }
            if ( ! is_array( $groupSelection ) ) {
                // Someone is fiddling with the input.
                echo "Huh?\n" ; exit( 0 ) ;
            }
            $countIsValid = 0 ;
            // Check each of the $groupSelection array members
            foreach ( $groupSelection as $value ) {
                if ( ! Tools::isNumeric( $value ) ) {
                    // Someone is fiddling with the input.
                    echo "Huh?\n" ; exit( 1 ) ;
                }
                $countIsValid = 1 ;
            }
            if ( 0 === $countIsValid ) {
                $errors .= "Must specify at least one host in group.<br />\n" ;
            }
        }

        if ( $errors !== '' ) {
            $page->setBody( $links . $errors ) ;
            $page->displayPage() ;
            exit( 0 );
        }

        $dbc = new DBConnection() ;
        $dbh = $dbc->getConnection() ;
        $dbh->set_charset('utf8') ;
        // Handle group change
        switch ( $action ) {
            case '':
                // Nothing to see here.
                break ;
            case 'Add':
                $body .= 'Add - ' ;
                $sql = 'INSERT INTO host_group SET tag = ?'
                     . ', short_description = ?'
                     . ', full_description = ?'
                     . ', created = CURRENT_TIMESTAMP()'
                     . ', updated = CURRENT_TIMESTAMP()'
                     ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'sss', $groupTag, $shortDesc, $fullDesc ) ;
                $body .= ( $stmt->execute() ) ? "Success.<br />\n" : "Failed.<br />\n" ;
                // get new $groupId
                $groupId = $dbh->insert_id ;
                break ;
            case 'Update':
                $body .= 'Update - ' ;
                $sql = 'UPDATE host_group SET tag = ?'
                     . ', short_description = ?'
                     . ', full_description = ?'
                     . ', updated = CURRENT_TIMESTAMP()'
                     . ' WHERE host_group_id = ?'
                    ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'sssi', $groupTag, $shortDesc, $fullDesc, $groupId ) ;
                $body .= ( $stmt->execute() ) ? "Success.<br />\n" : "Failed.<br />\n" ;
                break ;
            case 'Delete':
                $body .= 'Delete - ' ;
                $sql = 'DELETE FROM host_group_map WHERE host_group_id = ?' ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'i', $groupId ) ;
                if ( ! $stmt->execute() ) {
                    echo "Conflicting update?<br />\n" ;
                    exit( 1 ) ;
                }
                $sql = 'DELETE FROM host_group WHERE host_group_id = ?' ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'i', $groupId ) ;
                $body .= ( $stmt->execute() ) ? "Success.<br />\n" : "Failed.<br />\n" ;
                break ;
            default:
                $page->setBody( $links . 'Huh?' ) ;
                $page->displayPage() ;
                exit( 0 );
        }

        if ( 'Update' === $action ) {
            // Delete group members not in the selected list
            if ( !empty( $groupSelection ) ) {
                $placeholders = implode( ',', array_fill( 0, count( $groupSelection ), '?' ) ) ;
                $sql = "DELETE FROM host_group_map WHERE host_group_id = ? AND host_id NOT IN ($placeholders)" ;
                $stmt = $dbh->prepare( $sql ) ;
                $types = 'i' . str_repeat( 'i', count( $groupSelection ) ) ;
                $params = array_merge( [ $groupId ], $groupSelection ) ;
                $stmt->bind_param( $types, ...$params ) ;
                if ( ! $stmt->execute() ) {
                    echo "Conflicting update?<br />\n" ;
                    exit( 1 ) ;
                }
            } else {
                // No hosts selected - delete all mappings for this group
                $sql = "DELETE FROM host_group_map WHERE host_group_id = ?" ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'i', $groupId ) ;
                if ( ! $stmt->execute() ) {
                    echo "Conflicting update?<br />\n" ;
                    exit( 1 ) ;
                }
            }
        }
        if ( ( 'Add' === $action ) || ( 'Update' === $action ) ) {
            // Add group members
            $sql = "INSERT IGNORE INTO host_group_map"
                 . " SET host_group_id = ?, host_id = ?, created = NOW(), updated = NOW(), last_audited = NOW()"
                 ;
            $stmt = $dbh->prepare( $sql ) ;
            foreach ( $groupSelection as $hostId ) {
                $stmt->bind_param( "ii", $groupId, $hostId ) ;
                $stmt->execute() ;
            }
        }

        $groupQuery = <<<SQL
SELECT hg.host_group_id
     , hg.tag
     , hg.short_description
     , hg.full_description
     , COUNT( hgm.host_id ) as host_cnt
     , GROUP_CONCAT( DISTINCT hgm.host_id ) as host_list
  FROM host_group AS hg
  LEFT
  JOIN host_group_map AS hgm USING ( host_group_id )
 GROUP BY hg.host_group_id 
 ORDER BY hg.tag

SQL;
        $hostsQuery = <<<SQL
SELECT host_id, CONCAT( hostname, ':', port_number )
  FROM aql_db.host
 WHERE decommissioned = 0
 ORDER BY hostname, port_number

SQL;
        try {
            // Build host select dropdown first (needed for form)
            $hostResult = $dbh->query( $hostsQuery ) ;
            if ( ! $hostResult ) {
                throw new \ErrorException( "Query failed: $hostsQuery\n Error: " . $dbh->error ) ;
            }
            $groupSelect = "<select name=\"groupSelect[]\" id=\"groupSelect\" size=\"25\" multiple=\"multiple\">\n" ;
            while ( $row = $hostResult->fetch_row() ) {
                $groupSelect .= "  <option value=\"" . intval( $row[0] ) . "\">"
                             . htmlspecialchars( $row[1], ENT_QUOTES, 'UTF-8' ) . "</option>\n" ;
            }
            $groupSelect .= "</select>\n" ;

            // Form at the top for easier access
            $body .= <<<HTML
<form method="get" action="manageData.php">
  <input type="hidden" name="data" value="Groups">
  <table id="Form" border=1 cellspacing=0 cellpadding=2>
    <caption>Group Form</caption>
    <tr><th>Group ID</th><td><input type="number" id="groupId" name="groupId" readonly="readonly" size=5 /></td></tr>
    <tr><th>Group Tag (16)</th><td><input type="text" id="groupTag" name="groupTag" size="16" maxlength="16" /></td></tr>
    <tr><th>Description</th><td><input type="text" id="shortDescription" name="shortDescription" size="80" maxlength="255" /></td></tr>
    <tr><th>Full Description</th><td><textarea id="fullDescription" name="fullDescription" rows="4" cols="80" maxlength="65535"></textarea></td></tr>
    <tr><th>Members</th><td>$groupSelect</td></tr>
  </table>
  <input type="submit" name="action" value="Add"> &nbsp; &nbsp;
  <input type="submit" name="action" value="Update"> &nbsp; &nbsp;
  <input type="submit" name="action" value="Delete"> &nbsp; &nbsp;
  <br clear="all" />
</form>

<p></p>

<table id="groupEdit" border=1 cellspacing=0 cellpadding=2 class="tablesorter aql-listing">
  <thead>
    <tr>
      <th>Actions</th>
      <th>Group ID</th>
      <th>Group Tag</th>
      <th>Description</th>
      <th>Full Description</th>
      <th>Member Count</th>
    </tr>
  </thead>
  <tbody>

HTML;
            $groupResult = $dbh->query( $groupQuery ) ;
            if ( ! $groupResult ) {
                throw new \ErrorException( "Query failed: $groupQuery\n Error: " . $dbh->error ) ;
            }
            while ( $row = $groupResult->fetch_row() ) {
                // Use json_encode for safe JavaScript string escaping, then htmlspecialchars for HTML attribute context
                $jsTag = htmlspecialchars( json_encode( $row[1] ), ENT_QUOTES, 'UTF-8' ) ;
                $jsShortDesc = htmlspecialchars( json_encode( $row[2] ), ENT_QUOTES, 'UTF-8' ) ;
                $jsFullDesc = htmlspecialchars( json_encode( $row[3] ), ENT_QUOTES, 'UTF-8' ) ;
                // host_list is comma-separated integers from GROUP_CONCAT, sanitize to be safe
                $hostList = $row[5] ? preg_replace( '/[^0-9,]/', '', $row[5] ) : '' ;
                $body .= "      <tr>"
                      .  "<td><button type=\"button\""
                      .  " onclick=\"fillGroupForm(" . intval( $row[0] ) . ", $jsTag, $jsShortDesc, $jsFullDesc, [$hostList]); return false;\""
                      .  ">Fill In Form</button></td>"
                      ;
                for ( $i = 0 ; $i < 5 ; $i++ ) {
                    $body .= "<td>" . htmlspecialchars( $row[$i] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>" ;
                }
                $body .= "</tr>\n" ;
            }
            $body .= <<<HTML
  </tbody>
</table>

HTML;
            $page->setBody( $links . $body ) ;
        }
        catch (DaoException $e) {
        $page->appendBody( "<pre>Error interacting with the database\n\n" . $e->getMessage() . "\n</pre>\n" ) ;
        }
        $page->displayPage() ;

        break ;
    case 'MaintenanceWindows':
        $dbc = new DBConnection();
        $dbh = $dbc->getConnection();
        $dbh->set_charset('utf8');

        $body = $links = $errors = '' ;
        $action = Tools::param( 'action' ) ;
        $windowId = Tools::param( 'windowId' ) ;
        $windowType = Tools::param( 'windowType' ) ;
        $scheduleType = Tools::param( 'scheduleType' ) ;
        $targetType = Tools::param( 'targetType' ) ;  // 'host' or 'group'
        $targetId = Tools::param( 'targetId' ) ;
        $daysOfWeek = Tools::params( 'daysOfWeek' ) ;  // array
        $dayOfMonth = Tools::param( 'dayOfMonth' ) ;
        $monthOfYear = Tools::param( 'monthOfYear' ) ;
        $periodDays = Tools::param( 'periodDays' ) ;
        $periodStartDate = Tools::param( 'periodStartDate' ) ;
        $startTime = Tools::param( 'startTime' ) ;
        $endTime = Tools::param( 'endTime' ) ;
        $timezone = Tools::param( 'timezone' ) ;
        $silenceUntil = Tools::param( 'silenceUntil' ) ;
        $description = Tools::param( 'description' ) ;

        // Validation
        if ( ( ( 'Update' === $action ) || ( 'Delete' === $action ) )
             && ! Tools::isNumeric( $windowId )
           ) {
            $errors .= "Invalid Window ID<br />\n" ;
        }
        if ( 'Delete' !== $action && '' !== $action ) {
            if ( ! in_array( $windowType, [ 'scheduled', 'adhoc' ] ) ) {
                $errors .= "Window Type must be 'scheduled' or 'adhoc'<br />\n" ;
            }
            if ( ! in_array( $targetType, [ 'host', 'group' ] ) ) {
                $errors .= "Target Type must be 'host' or 'group'<br />\n" ;
            }
            if ( ! Tools::isNumeric( $targetId ) || $targetId <= 0 ) {
                $errors .= "Please select a valid Host or Group<br />\n" ;
            }
            if ( 'scheduled' === $windowType ) {
                // Validate schedule type
                if ( ! in_array( $scheduleType, [ 'weekly', 'monthly', 'quarterly', 'annually', 'periodic' ] ) ) {
                    $errors .= "Schedule Type must be one of: weekly, monthly, quarterly, annually, periodic<br />\n" ;
                }
                // Schedule-type-specific validation
                if ( 'weekly' === $scheduleType ) {
                    if ( empty( $daysOfWeek ) ) {
                        $errors .= "Please select at least one day of the week<br />\n" ;
                    }
                }
                if ( in_array( $scheduleType, [ 'monthly', 'quarterly', 'annually' ] ) ) {
                    if ( ! Tools::isNumeric( $dayOfMonth ) || $dayOfMonth < 1 || $dayOfMonth > 32 ) {
                        $errors .= "Day of Month must be between 1 and 32<br />\n" ;
                    }
                }
                if ( in_array( $scheduleType, [ 'quarterly', 'annually' ] ) ) {
                    $maxMonth = ( 'quarterly' === $scheduleType ) ? 3 : 12 ;
                    if ( ! Tools::isNumeric( $monthOfYear ) || $monthOfYear < 1 || $monthOfYear > $maxMonth ) {
                        $errors .= "Month must be between 1 and $maxMonth<br />\n" ;
                    }
                }
                if ( 'periodic' === $scheduleType ) {
                    if ( ! Tools::isNumeric( $periodDays ) || $periodDays < 1 ) {
                        $errors .= "Period Days must be at least 1<br />\n" ;
                    }
                    if ( empty( $periodStartDate ) ) {
                        $errors .= "Period Start Date is required for periodic schedules<br />\n" ;
                    }
                }
                // All scheduled types need start/end time
                if ( empty( $startTime ) || empty( $endTime ) ) {
                    $errors .= "Start Time and End Time are required for scheduled windows<br />\n" ;
                }
            }
            if ( 'adhoc' === $windowType ) {
                if ( empty( $silenceUntil ) ) {
                    $errors .= "Silence Until is required for ad-hoc windows<br />\n" ;
                }
            }
        }

        if ( ( '' != $errors ) && ( '' != $action ) ) {
            $page->setBody( $links . $body . $errors ) ;
            $page->displayPage() ;
            exit( 0 ) ;
        }

        // Process action
        switch ( $action ) {
            case '':
                break;
            case 'Add':
                $body .= 'Add - ' ;
                // Convert days array to SET string
                $daysSet = is_array( $daysOfWeek ) ? implode( ',', $daysOfWeek ) : '' ;
                // Prepare values (use null for empty)
                $scheduleTypeVal = ( 'scheduled' === $windowType ) ? $scheduleType : null ;
                $daysSetVal = empty( $daysSet ) ? null : $daysSet ;
                $dayOfMonthVal = empty( $dayOfMonth ) ? null : intval( $dayOfMonth ) ;
                $monthOfYearVal = empty( $monthOfYear ) ? null : intval( $monthOfYear ) ;
                $periodDaysVal = empty( $periodDays ) ? null : intval( $periodDays ) ;
                $periodStartDateVal = empty( $periodStartDate ) ? null : $periodStartDate ;
                $startTimeVal = empty( $startTime ) ? null : $startTime ;
                $endTimeVal = empty( $endTime ) ? null : $endTime ;
                $silenceUntilVal = empty( $silenceUntil ) ? null : $silenceUntil ;
                $createdBy = $_SESSION['AuthUser'] ?? '' ;
                $sql = 'INSERT INTO maintenance_window SET window_type = ?, schedule_type = ?, days_of_week = ?, day_of_month = ?, month_of_year = ?, period_days = ?, period_start_date = ?, start_time = ?, end_time = ?, timezone = ?, silence_until = ?, description = ?, created_by = ?' ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'sssiiiissssss', $windowType, $scheduleTypeVal, $daysSetVal, $dayOfMonthVal, $monthOfYearVal, $periodDaysVal, $periodStartDateVal, $startTimeVal, $endTimeVal, $timezone, $silenceUntilVal, $description, $createdBy ) ;
                if ( $stmt->execute() ) {
                    $newWindowId = $dbh->insert_id ;
                    // Add to mapping table
                    if ( 'host' === $targetType ) {
                        $mapSql = 'INSERT INTO maintenance_window_host_map (window_id, host_id) VALUES (?, ?)' ;
                    } else {
                        $mapSql = 'INSERT INTO maintenance_window_host_group_map (window_id, host_group_id) VALUES (?, ?)' ;
                    }
                    $mapStmt = $dbh->prepare( $mapSql ) ;
                    $mapStmt->bind_param( 'ii', $newWindowId, $targetId ) ;
                    $mapStmt->execute() ;
                    $body .= "Added window ID $newWindowId<br />\n" ;
                } else {
                    $body .= "Error: " . $dbh->error . "<br />\n" ;
                }
                break;
            case 'Update':
                $body .= 'Update - ' ;
                // Convert days array to SET string
                $daysSet = is_array( $daysOfWeek ) ? implode( ',', $daysOfWeek ) : '' ;
                // Prepare values (use null for empty)
                $scheduleTypeVal = ( 'scheduled' === $windowType ) ? $scheduleType : null ;
                $daysSetVal = empty( $daysSet ) ? null : $daysSet ;
                $dayOfMonthVal = empty( $dayOfMonth ) ? null : intval( $dayOfMonth ) ;
                $monthOfYearVal = empty( $monthOfYear ) ? null : intval( $monthOfYear ) ;
                $periodDaysVal = empty( $periodDays ) ? null : intval( $periodDays ) ;
                $periodStartDateVal = empty( $periodStartDate ) ? null : $periodStartDate ;
                $startTimeVal = empty( $startTime ) ? null : $startTime ;
                $endTimeVal = empty( $endTime ) ? null : $endTime ;
                $silenceUntilVal = empty( $silenceUntil ) ? null : $silenceUntil ;
                $sql = 'UPDATE maintenance_window SET window_type = ?, schedule_type = ?, days_of_week = ?, day_of_month = ?, month_of_year = ?, period_days = ?, period_start_date = ?, start_time = ?, end_time = ?, timezone = ?, silence_until = ?, description = ? WHERE window_id = ?' ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'sssiiissssssi', $windowType, $scheduleTypeVal, $daysSetVal, $dayOfMonthVal, $monthOfYearVal, $periodDaysVal, $periodStartDateVal, $startTimeVal, $endTimeVal, $timezone, $silenceUntilVal, $description, $windowId ) ;
                if ( $stmt->execute() ) {
                    // Update mapping - remove old, add new
                    $dbh->query( "DELETE FROM maintenance_window_host_map WHERE window_id = $windowId" ) ;
                    $dbh->query( "DELETE FROM maintenance_window_host_group_map WHERE window_id = $windowId" ) ;
                    if ( 'host' === $targetType ) {
                        $mapSql = 'INSERT INTO maintenance_window_host_map (window_id, host_id) VALUES (?, ?)' ;
                    } else {
                        $mapSql = 'INSERT INTO maintenance_window_host_group_map (window_id, host_group_id) VALUES (?, ?)' ;
                    }
                    $mapStmt = $dbh->prepare( $mapSql ) ;
                    $mapStmt->bind_param( 'ii', $windowId, $targetId ) ;
                    $mapStmt->execute() ;
                    $body .= "Updated window ID $windowId<br />\n" ;
                } else {
                    $body .= "Error: " . $dbh->error . "<br />\n" ;
                }
                break;
            case 'Delete':
                $body .= 'Delete - ' ;
                // Foreign keys with CASCADE will handle mapping table cleanup
                $sql = 'DELETE FROM maintenance_window WHERE window_id = ?' ;
                $stmt = $dbh->prepare( $sql ) ;
                $stmt->bind_param( 'i', $windowId ) ;
                if ( $stmt->execute() ) {
                    $body .= "Deleted window ID $windowId<br />\n" ;
                } else {
                    $body .= "Error: " . $dbh->error . "<br />\n" ;
                }
                break;
        }

        // Fetch hosts and groups for dropdowns
        $hostsResult = $dbh->query( 'SELECT host_id, CONCAT(hostname, ":", port_number) AS display FROM host ORDER BY hostname, port_number' ) ;
        $hostOptions = '<option value="">-- Select Host --</option>' ;
        while ( $row = $hostsResult->fetch_assoc() ) {
            $hostOptions .= '<option value="' . $row['host_id'] . '">' . htmlspecialchars( $row['display'], ENT_QUOTES, 'UTF-8' ) . '</option>' ;
        }

        $groupsResult = $dbh->query( 'SELECT host_group_id, tag FROM host_group ORDER BY tag' ) ;
        $groupOptions = '<option value="">-- Select Group --</option>' ;
        while ( $row = $groupsResult->fetch_assoc() ) {
            $groupOptions .= '<option value="' . $row['host_group_id'] . '">' . htmlspecialchars( $row['tag'], ENT_QUOTES, 'UTF-8' ) . '</option>' ;
        }

        // Form
        $body .= <<<HTML
<form id="MaintenanceWindowForm" action="manageData.php">
  <input type="hidden" name="data" value="MaintenanceWindows">
  <table id="MaintenanceWindowTable" border=1 cellspacing=0 cellpadding=2>
    <caption>Maintenance Window Form</caption>
    <tr><th>Window ID</th><td><input type="number" id="windowId" name="windowId" readonly="readonly" value="" size=5 /></td></tr>
    <tr><th>Window Type <a onclick="alert('Scheduled: Recurring window that repeats on a schedule.\\n\\nAd-hoc: One-time silencing until a specific date/time (e.g., while working an issue).'); return false;" class="help-cursor">?</a></th><td>
      <select id="windowType" name="windowType" onchange="toggleWindowTypeFields()">
        <option value="scheduled">Scheduled (Recurring)</option>
        <option value="adhoc">Ad-hoc (One-time)</option>
      </select>
    </td></tr>
    <tr id="scheduleTypeRow"><th>Schedule Type <a onclick="alert('Weekly: Repeats on selected days each week.\\n\\nMonthly: Repeats on a specific day each month (e.g., 1st, 15th, or last day).\\n\\nQuarterly: Repeats on a specific day of a specific month each quarter.\\n\\nAnnually: Repeats on a specific date each year.\\n\\nPeriodic: Repeats every N days from a start date.'); return false;" class="help-cursor">?</a></th><td>
      <select id="scheduleType" name="scheduleType" onchange="toggleScheduleTypeFields()">
        <option value="weekly">Weekly</option>
        <option value="monthly">Monthly</option>
        <option value="quarterly">Quarterly</option>
        <option value="annually">Annually</option>
        <option value="periodic">Periodic (Every N Days)</option>
      </select>
    </td></tr>
    <tr><th>Target Type</th><td>
      <select id="targetType" name="targetType" onchange="toggleTargetFields()">
        <option value="host">Host</option>
        <option value="group">Group</option>
      </select>
    </td></tr>
    <tr id="hostRow"><th>Host</th><td>
      <select id="targetHost" name="targetId">$hostOptions</select>
    </td></tr>
    <tr id="groupRow" style="display:none;"><th>Group</th><td>
      <select id="targetGroup">$groupOptions</select>
    </td></tr>
    <tr id="daysRow"><th>Days of Week</th><td>
      <label><input type="checkbox" name="daysOfWeek[]" value="Sun"> Sun</label>
      <label><input type="checkbox" name="daysOfWeek[]" value="Mon"> Mon</label>
      <label><input type="checkbox" name="daysOfWeek[]" value="Tue"> Tue</label>
      <label><input type="checkbox" name="daysOfWeek[]" value="Wed"> Wed</label>
      <label><input type="checkbox" name="daysOfWeek[]" value="Thu"> Thu</label>
      <label><input type="checkbox" name="daysOfWeek[]" value="Fri"> Fri</label>
      <label><input type="checkbox" name="daysOfWeek[]" value="Sat"> Sat</label>
    </td></tr>
    <tr id="dayOfMonthRow" style="display:none;"><th>Day of Month <a onclick="alert('Day of the month (1-31).\\n\\nUse 32 for \\'last day of month\\' (handles Feb, 30-day months, etc.).'); return false;" class="help-cursor">?</a></th><td>
      <input type="number" id="dayOfMonth" name="dayOfMonth" min="1" max="32" size="4" />
      <span class="msg-muted">(1-31, or 32 for last day)</span>
    </td></tr>
    <tr id="monthOfYearRow" style="display:none;"><th>Month <a onclick="alert('For quarterly: which month of each quarter (1=first month, 2=second, 3=third).\\n\\nFor annually: which month of the year (1=Jan, 12=Dec).'); return false;" class="help-cursor">?</a></th><td>
      <select id="monthOfYear" name="monthOfYear">
        <option value="">-- Select --</option>
        <option value="1">January (or Q1/Q2/Q3/Q4 month 1)</option>
        <option value="2">February (or Q1/Q2/Q3/Q4 month 2)</option>
        <option value="3">March (or Q1/Q2/Q3/Q4 month 3)</option>
        <option value="4">April</option>
        <option value="5">May</option>
        <option value="6">June</option>
        <option value="7">July</option>
        <option value="8">August</option>
        <option value="9">September</option>
        <option value="10">October</option>
        <option value="11">November</option>
        <option value="12">December</option>
      </select>
    </td></tr>
    <tr id="periodDaysRow" style="display:none;"><th>Every N Days <a onclick="alert('Number of days between occurrences.\\n\\nExample: 7 = weekly, 14 = bi-weekly, 30 = roughly monthly.'); return false;" class="help-cursor">?</a></th><td>
      <input type="number" id="periodDays" name="periodDays" min="1" max="365" size="4" />
      <span class="msg-muted">(e.g., 7, 14, 30)</span>
    </td></tr>
    <tr id="periodStartDateRow" style="display:none;"><th>Period Start Date <a onclick="alert('Anchor date for periodic schedule calculation.\\n\\nThe window will occur on this date and every N days thereafter.'); return false;" class="help-cursor">?</a></th><td>
      <input type="date" id="periodStartDate" name="periodStartDate" />
    </td></tr>
    <tr id="startTimeRow"><th>Start Time <a onclick="alert('Start and End times define the daily window.\\n\\nOvernight spans are supported: e.g., Start 22:00, End 08:00 means 10PM to 8AM the next day.'); return false;" class="help-cursor">?</a></th><td><input type="time" id="startTime" name="startTime" /></td></tr>
    <tr id="endTimeRow"><th>End Time</th><td><input type="time" id="endTime" name="endTime" /></td></tr>
    <tr id="timezoneRow"><th>Timezone <a onclick="alert('PHP timezone identifier for scheduled windows.\\n\\nExamples:\\n• America/Chicago (Central)\\n• America/Denver (Mountain)\\n• America/New_York (Eastern)\\n• America/Los_Angeles (Pacific)\\n• UTC'); return false;" class="help-cursor">?</a></th><td><input type="text" id="timezone" name="timezone" value="America/Chicago" size="30" /></td></tr>
    <tr id="silenceUntilRow" style="display:none;"><th>Silence Until <a onclick="alert('For ad-hoc windows only.\\n\\nSpecify when silencing should end. Alerts will be suppressed until this date/time.'); return false;" class="help-cursor">?</a></th><td><input type="datetime-local" id="silenceUntil" name="silenceUntil" step="60" /></td></tr>
    <tr><th>Description</th><td><input type="text" id="mwDescription" name="description" size="60" /></td></tr>
  </table>
  <input type="submit" name="action" value="Add"> &nbsp;
  <input type="submit" name="action" value="Update"> &nbsp;
  <input type="submit" name="action" value="Delete"> &nbsp;
</form>

<script>
function toggleWindowTypeFields() {
    var windowType = document.getElementById('windowType').value;
    var isScheduled = (windowType === 'scheduled');

    // Show/hide schedule type row
    document.getElementById('scheduleTypeRow').style.display = isScheduled ? '' : 'none';

    // Common scheduled rows (always shown for scheduled windows regardless of schedule type)
    var commonScheduledRows = ['startTimeRow', 'endTimeRow', 'timezoneRow'];
    commonScheduledRows.forEach(function(id) {
        document.getElementById(id).style.display = isScheduled ? '' : 'none';
    });

    // Ad-hoc rows
    document.getElementById('silenceUntilRow').style.display = isScheduled ? 'none' : '';

    // Update schedule-type-specific fields
    if (isScheduled) {
        toggleScheduleTypeFields();
    } else {
        // Hide all schedule-type-specific fields when ad-hoc
        ['daysRow', 'dayOfMonthRow', 'monthOfYearRow', 'periodDaysRow', 'periodStartDateRow'].forEach(function(id) {
            document.getElementById(id).style.display = 'none';
        });
    }
}

function toggleScheduleTypeFields() {
    var scheduleType = document.getElementById('scheduleType').value;

    // Days of week: only for weekly
    document.getElementById('daysRow').style.display = (scheduleType === 'weekly') ? '' : 'none';

    // Day of month: for monthly, quarterly, annually
    var needsDayOfMonth = ['monthly', 'quarterly', 'annually'].includes(scheduleType);
    document.getElementById('dayOfMonthRow').style.display = needsDayOfMonth ? '' : 'none';

    // Month of year: for quarterly (1-3) and annually (1-12)
    var needsMonth = ['quarterly', 'annually'].includes(scheduleType);
    document.getElementById('monthOfYearRow').style.display = needsMonth ? '' : 'none';

    // Period fields: only for periodic
    var isPeriodic = (scheduleType === 'periodic');
    document.getElementById('periodDaysRow').style.display = isPeriodic ? '' : 'none';
    document.getElementById('periodStartDateRow').style.display = isPeriodic ? '' : 'none';
}

function toggleTargetFields() {
    var targetType = document.getElementById('targetType').value;
    document.getElementById('hostRow').style.display = (targetType === 'host') ? '' : 'none';
    document.getElementById('groupRow').style.display = (targetType === 'group') ? '' : 'none';
    // Sync the hidden targetId field
    if (targetType === 'host') {
        document.getElementById('targetHost').name = 'targetId';
        document.getElementById('targetGroup').name = '';
    } else {
        document.getElementById('targetHost').name = '';
        document.getElementById('targetGroup').name = 'targetId';
    }
}

// Initialize on page load
document.addEventListener('DOMContentLoaded', function() {
    toggleWindowTypeFields();
    toggleTargetFields();

    // Handle preselection from URL parameters (e.g., from index.php quick link)
    var urlParams = new URLSearchParams(window.location.search);
    var preselectType = urlParams.get('preselect');
    var preselectId = urlParams.get('preselectId');

    if (preselectType && preselectId) {
        // Set target type
        document.getElementById('targetType').value = preselectType;
        toggleTargetFields();

        // Set the target ID in the appropriate select
        if (preselectType === 'host') {
            document.getElementById('targetHost').value = preselectId;
        } else if (preselectType === 'group') {
            document.getElementById('targetGroup').value = preselectId;
        }

        // Default to ad-hoc for quick silencing from index.php
        document.getElementById('windowType').value = 'adhoc';
        toggleWindowTypeFields();

        // Set default silence time (1 hour from now)
        var now = new Date();
        now.setHours(now.getHours() + 1);
        var isoStr = now.toISOString().slice(0, 16);
        document.getElementById('silenceUntil').value = isoStr;
    }
});
</script>

<p></p>

<table id="maintenanceWindowEdit" border=1 cellspacing=0 cellpadding=2 class="tablesorter aql-listing">
  <thead>
    <tr>
      <th>Actions</th>
      <th>ID</th>
      <th>Type</th>
      <th>Schedule</th>
      <th>Target</th>
      <th>Schedule Details</th>
      <th>Start</th>
      <th>End</th>
      <th>Timezone</th>
      <th>Silence Until</th>
      <th>Description</th>
      <th>Created By</th>
      <th>Created</th>
    </tr>
  </thead>
  <tbody>

HTML;

        // Query existing windows with their targets
        $windowsQuery = <<<SQL
SELECT mw.window_id
     , mw.window_type
     , mw.schedule_type
     , CASE
         WHEN mwhm.host_id IS NOT NULL THEN 'host'
         WHEN mwgm.host_group_id IS NOT NULL THEN 'group'
         ELSE 'none'
       END AS target_type
     , COALESCE(mwhm.host_id, mwgm.host_group_id) AS target_id
     , CASE
         WHEN mwhm.host_id IS NOT NULL THEN CONCAT(h.hostname, ':', h.port_number)
         WHEN mwgm.host_group_id IS NOT NULL THEN hg.tag
         ELSE '(none)'
       END AS target_display
     , mw.days_of_week
     , mw.day_of_month
     , mw.month_of_year
     , mw.period_days
     , mw.period_start_date
     , mw.start_time
     , mw.end_time
     , mw.timezone
     , mw.silence_until
     , mw.description
     , mw.created_by
     , mw.created
  FROM maintenance_window mw
  LEFT JOIN maintenance_window_host_map mwhm ON mwhm.window_id = mw.window_id
  LEFT JOIN host h ON h.host_id = mwhm.host_id
  LEFT JOIN maintenance_window_host_group_map mwgm ON mwgm.window_id = mw.window_id
  LEFT JOIN host_group hg ON hg.host_group_id = mwgm.host_group_id
 ORDER BY mw.window_id DESC
SQL;

        try {
            $result = $dbh->query( $windowsQuery ) ;
            if ( ! $result ) {
                throw new \ErrorException( "Query failed: $windowsQuery\n Error: " . $dbh->error ) ;
            }
            while ( $row = $result->fetch_assoc() ) {
                $jsDescription = htmlspecialchars( json_encode( $row['description'] ?? '' ), ENT_QUOTES, 'UTF-8' ) ;
                $jsDays = htmlspecialchars( json_encode( $row['days_of_week'] ?? '' ), ENT_QUOTES, 'UTF-8' ) ;
                $jsTimezone = htmlspecialchars( json_encode( $row['timezone'] ?? 'America/Chicago' ), ENT_QUOTES, 'UTF-8' ) ;

                // Build schedule details display based on schedule type
                $scheduleType = $row['schedule_type'] ?? 'weekly' ;
                $scheduleDetails = '' ;
                switch ( $scheduleType ) {
                    case 'weekly':
                        $scheduleDetails = htmlspecialchars( $row['days_of_week'] ?? '', ENT_QUOTES, 'UTF-8' ) ;
                        break ;
                    case 'monthly':
                        $dom = intval( $row['day_of_month'] ?? 0 ) ;
                        $scheduleDetails = $dom === 32 ? 'Last day' : "Day $dom" ;
                        break ;
                    case 'quarterly':
                        $dom = intval( $row['day_of_month'] ?? 0 ) ;
                        $moy = intval( $row['month_of_year'] ?? 0 ) ;
                        $domStr = $dom === 32 ? 'Last' : $dom ;
                        $scheduleDetails = "Day $domStr, Month $moy of quarter" ;
                        break ;
                    case 'annually':
                        $dom = intval( $row['day_of_month'] ?? 0 ) ;
                        $moy = intval( $row['month_of_year'] ?? 0 ) ;
                        $domStr = $dom === 32 ? 'Last' : $dom ;
                        $months = [ '', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' ] ;
                        $scheduleDetails = ( $months[$moy] ?? "Month $moy" ) . " $domStr" ;
                        break ;
                    case 'periodic':
                        $pd = intval( $row['period_days'] ?? 0 ) ;
                        $psd = $row['period_start_date'] ?? '' ;
                        $scheduleDetails = "Every {$pd} days from $psd" ;
                        break ;
                }

                $body .= "<tr>"
                      .  "<td class=\"text-center\">"
                      .  "<button type=\"button\" onclick=\"fillMaintenanceWindowForm("
                      .  intval( $row['window_id'] ) . ", "
                      .  "'" . $row['window_type'] . "', "
                      .  "'" . ( $row['schedule_type'] ?? 'weekly' ) . "', "
                      .  "'" . $row['target_type'] . "', "
                      .  intval( $row['target_id'] ?? 0 ) . ", "
                      .  $jsDays . ", "
                      .  intval( $row['day_of_month'] ?? 0 ) . ", "
                      .  intval( $row['month_of_year'] ?? 0 ) . ", "
                      .  intval( $row['period_days'] ?? 0 ) . ", "
                      .  "'" . ( $row['period_start_date'] ?? '' ) . "', "
                      .  "'" . ( $row['start_time'] ?? '' ) . "', "
                      .  "'" . ( $row['end_time'] ?? '' ) . "', "
                      .  $jsTimezone . ", "
                      .  "'" . ( $row['silence_until'] ?? '' ) . "', "
                      .  $jsDescription
                      .  "); return false;\">Edit</button>"
                      .  "</td>"
                      .  "<td>" . intval( $row['window_id'] ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['window_type'], ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $scheduleType, ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['target_display'], ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . $scheduleDetails . "</td>"
                      .  "<td>" . htmlspecialchars( $row['start_time'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['end_time'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['timezone'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['silence_until'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['description'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['created_by'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "<td>" . htmlspecialchars( $row['created'] ?? '', ENT_QUOTES, 'UTF-8' ) . "</td>"
                      .  "</tr>\n" ;
            }
            $body .= <<<HTML
  </tbody>
</table>

HTML;
            $page->setBody( $links . $body ) ;
        }
        catch ( \Exception $e ) {
            $page->appendBody( "<pre>Error: " . $e->getMessage() . "</pre>\n" ) ;
        }
        $page->displayPage() ;

        break ;
    default:
        $page->setBody( '' ) ;
        $page->displayPage() ;
        break ;
}