#!/usr/bin/env php
<?php

/**
 * Observium
 *
 *   This file is part of Observium.
 *
 * @package    observium
 * @subpackage discovery
 * @author     Adam Armstrong <adama@memetic.org>
 * @copyright  (C) 2006-2014 Adam Armstrong
 *
 */

chdir(dirname(__FILE__));

include('includes/defaults.inc.php');
include('config.php');

$options = getopt('drqVa:e:p:s:');

// Debug Option
if (isset($options['d']))
{
  echo("DEBUG!\n");
  $debug = TRUE;
  ini_set('display_errors', 1);
  ini_set('display_startup_errors', 1);
  ini_set('log_errors', 1);
  ini_set('error_reporting', 1);
} else {
  $debug = FALSE;
  ini_set('display_startup_errors', 0);
  ini_set('log_errors', 0);
}

include('includes/definitions.inc.php');
include('includes/functions.php');
include('includes/discovery/functions.inc.php');

// Control what each option manages in the database
$maintenance = array(
  'e' => array(
    'tables' => array('eventlog'),
    'column' => 'timestamp',
    ),
  'p' => array(
    'tables' => array('devices_perftimes', 'perf_times'),
    'column' => 'from_unixtime(start)',
    ),
  's' => array(
    'tables' => array('syslog'),
    'column' => 'timestamp',
    ),
  );

$cli = TRUE;
$report_mode = FALSE;

// Version Option
if (isset($options['V']))
{
  print_message('Observium '. $config['version']);
  exit;
}

// Reporting Option
if (isset($options['r']))
{
  $report_mode = TRUE;

  // If there are no Interval options, show a general report on table sizes
  if (!isset($options['a']) && !isset($options['e']) && !isset($options['p']) && !isset($options['s']))
  {
    $query = 'SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH + INDEX_LENGTH AS TABLE_SIZE '.
      'FROM information_schema.TABLES '.
      'WHERE information_schema.TABLES.table_schema="observium" AND ';

    // Build Table WHERE clause
    $tbl_array = array();
    foreach ($maintenance as $key => $value)
    {
      foreach ($value['tables'] as $table)
      {
        $table_array[] = 'TABLE_NAME="'. $table .'"';
      }
    }
    $query .= join(' OR ', $table_array);

    foreach (dbFetch($query) as $table)
    {
      print_message('Table "%y'. $table['TABLE_NAME'] .'%n" has %y'. number_format($table['TABLE_ROWS'])
        .'%n records using %y'. number_format($table['TABLE_SIZE']/1024/1024, 2, '.', '')."%n Mb", 'color');
    }

    exit;
  }
}

if (!isset($options['a']) && !isset($options['e']) && !isset($options['p']) && !isset($options['s']))
{
  print_usage();
}

// All Tables Option
if (isset($options['a']))
{
  $options['e'] = $options['a'];
  $options['p'] = $options['a'];
  $options['s'] = $options['a'];
}

// Validate interval formats
validate_intervals($options);

// It's go time
foreach ($maintenance as $key => $value)
{
  if (isset($options[$key]))
  {
    if ($report_mode !== FALSE)
    {
      // Report Mode
      foreach ($value['tables'] as $tbl)
      {
        show_table_interval_report($tbl, $value['column'], $value['interval']);
      }
    } else {
      // Purge Mode
      foreach ($value['tables'] as $tbl)
      {
        $count = purge_table_records($tbl, $value['column'], $value['interval']);

        if ($count !== FALSE)
        {
          // Log
          $log = __FILE__ .' - '. date('c') . " purged $count records from table $tbl";
          if ($debug) { echo("$log\n"); }
          if ($count > 0) { logfile($log); }

          if (!isset($options['q'])) {
            print_message('Table "%y'. $tbl .'%n": purged %r'. number_format($count) .'%n records.', 'color');
          }
        } else {
            print_message('%rERROR:%n failed to delete records from table "%y'. $tbl .'%n".', 'color');
        }
      }
    }
  }
}


/*
 * Print command-line usage text
 */
function print_usage()
{
  print_message("USAGE:
 db-maintenance.php [-drV] [-a INTERVAL] [-e INTERVAL] [-p INTERVAL] [-s INTERVAL]

PURGING:
 -a INTERVAL      Purge entries from all log tables.
 -e INTERVAL      Purge entries from table 'eventlog'.
 -p INTERVAL      Purge entries from table 'perf_times'.
 -s INTERVAL      Purge entries from table 'syslog'.

REPORTING:
 -r               Reporting mode; no purging occurs.

GENERAL:
 -d               Enable debugging output.
 -q               Quiet mode.
 -V               Show version and exit.

INTERVAL FORMAT:
 The INTERVAL format is a single-unit expression of a time period.
 Use of '18m' as an interval is allowed, but not '1y6m'.

 Valid units:
   d = day
   w = week
   m = month
   y = year

EXAMPLES:
 db-maintenance.php -r
   Report on general database table usage.

 db-maintenance.php -r -a 1y
   Report on the number of records affected by a 1 year interval

 db-maintenance.php -a 1y
   Purge all log records over 1 year old

 db-maintenance.php -e 1y -p 6m -s 1y
   Purge eventlog and syslog records over 1 year old and perftimes records
   over 6 months old

%rInvalid arguments!%n", 'color');
  exit;
}

/*
 * Delete records from a table based on column and interval.
 */
function purge_table_records($table, $column, $interval)
{
  return dbDelete($table, "$column <= DATE(NOW() - INTERVAL $interval)");
}

/*
 * Helper function to print a count of records in a table affected by a given interval.
 */
function show_table_interval_report($table, $column, $interval)
{
  foreach (dbFetch("SELECT COUNT(*) AS count FROM $table WHERE $column <= DATE(NOW() - INTERVAL $interval)") as $result)
  {
    print_message('Table "%y'. $table .'%n" has %y'. number_format($result['count']) .'%n records affected by internal "%y'. $interval .'%n"', 'color');
  }
}

/*
 * Convert interval string to MySQL interval format
 */
function interval_to_mysql($interval)
{
  if (preg_match('/^(?P<num>\d+)(?P<unit>[dwmy])$/', $interval, $matches))
  {
    $conv = $matches['num'] .' '. str_replace(array('d', 'w', 'm', 'y'), array('DAY', 'WEEK', 'MONTH', 'YEAR'), $matches['unit']);
    return $conv;
  } else {
    print_usage();
  }
}

/*
 * Validate interval input from user and save mysql version of interval
 */
function validate_intervals($opts)
{
  global $maintenance;

  foreach ($maintenance as $key => $value)
  {
    if (isset($opts[$key]))
    {
      $maintenance[$key]['interval'] = interval_to_mysql($opts[$key]);
    }
  }
}