#!/usr/bin/env php
<?php
/**
 * The Strangecode Codebase - a general application development framework for PHP
 * For details visit the project site: <http://trac.strangecode.com/codebase/>
 * Copyright 2001-2012 Strangecode, LLC
 *
 * This file is part of The Strangecode Codebase.
 *
 * The Strangecode Codebase 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 3 of the License, or (at your option)
 * any later version.
 *
 * The Strangecode Codebase 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
 * The Strangecode Codebase. If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * sql.cli.php
 */

if ($_SERVER['argc'] > 1 && isset($_SERVER['argv'][1]) && '' != $_SERVER['argv'][1] && is_dir($_SERVER['argv'][1])) {
    // First arg is path to current site. Realpath removes trailing /s
    define('COMMON_BASE', realpath($_SERVER['argv'][1]));
} else {
    die("Error: First argument must be the directory path to an existing site (ex: /home/sc/www.strangecode.com).\n");
}

include_once dirname(__FILE__) . '/../_config.inc.php';

$op = null;
$valid_ops = array('sortorder', 'insert', 'update', 'delete', 'search', 'filter');

// Test arguments.
if (isset($_SERVER['argv'][2])) {
    // Second arg is db table.
    $db_tbl = $_SERVER['argv'][2];
} else {
    die(sprintf("Usage: %s site_directory db_table [operation]\nValid operations include: %s\n", basename($_SERVER['argv'][0]), join(', ', $valid_ops)));
}

// Test for operation.
if (isset($_SERVER['argv'][3])) {
    // Optional third arg is op.
    $op = $_SERVER['argv'][3];
    // Make sure op is valid.
    if (!in_array($op, $valid_ops)) {
        die(basename($_SERVER['argv'][0]) . " Warning: Operation '$op' is not something I know how to do Please select one of: " . join(", ", $valid_ops) . "\n");
    }
}

// Get DB tables.
$qid = $db->query("SHOW TABLES");
while (list($row) = mysql_fetch_row($qid)) {
    $tables[] = $row;
}

// Make sure requested table is in database.
if (!in_array($db_tbl, $tables)) {
    die(sprintf("%s Warning: %s does not exist in database %s. Please select one of: \n\n%s\n\n", basename($_SERVER['argv'][0]), $db_tbl, $app->getParam('db_name'), join("\n", $tables)));
}

// Make sure op is valid.
if (isset($op) && !in_array($op, $valid_ops)) {
    die(basename($_SERVER['argv'][0]) . " Warning: Operation '$op' is not something I know how to do Please select one of: " . join(", ", $valid_ops) . "\n");
}

// Get DB table column info.
$qid = $db->query("DESCRIBE " . $db->escapeString($db_tbl));
while ($row = mysql_fetch_row($qid)) {
    $cols[] = $row;
}

$sort_columns = '';
$primary_key = '__///__';

// Loop through columns
if (is_array($cols) && !empty($cols)) {
    foreach ($cols as $col) {

        // Human readable.
        $field = $col[0];
        $type = preg_replace('/^(\w+).*$/', '\\1', $col[1]);
        $is_primary_key = ('PRI' == $col[3]);

        $sort_columns .= "\$so->setColumn('$field', '$field ASC', '$field DESC');\n";

        if ($is_primary_key) {
            // This is the primary key. Deal with separately.
            $primary_key = $field;
        } else if ('set' == $type) {
            // Set types usually need to be converted to comma-delimited lists.
            $c[$field] = "'\" . escapedList(array_keys(\$frm['$field'])) . \"'";
        } else if ('featured' == $field || 'publish' == $field || preg_match("/enum\('true'\)/", $col[1])) {
            // Toggle types.
            $c[$field] = "'\" . isset(\$frm['$field']) . \"'";
        } else if ('added_by_user_id' == $field || 'modified_by_user_id' == $field) {
            // Expects a user_id.
            $c[$field] = "'\" . \$db->escapeString(\$auth->get('user_id')) . \"'";
        } else if ('added_datetime' == $field || 'modified_datetime' == $field) {
            // DB record insertion datetime.
            $c[$field] = "NOW()";
        } else if (preg_match('/date_|_date/', $field)) {
            // This is a date field. Convert to SQL date.
            $c[$field] = "'\" . \$db->escapeString(strToSQLDate(\$frm['$field'])) . \"'";
        } else {
            // Default. Just insert data.
            $c[$field] = "'\" . \$db->escapeString(\$frm['$field']) . \"'";
        }
    }

} else {
    die(basename($_SERVER['argv'][0]) . " Warning: $db_tbl does not have any columns.\n");
}


echo isset($op) ? '' : "\n\n\n";

// Insert SQL.
if (!isset($op) || 'insert' == $op) {
$insert_skip_columns = array('modified_datetime', 'modified_by_user_id');
$insert_c = array();
foreach ($c as $k=>$v) {
    if (in_array($k, $insert_skip_columns)) {
        continue;
    }
    $insert_c[$k] = $v;
}
$db_keys = join("`,\n            `", array_keys($insert_c));
$db_vals = join(",\n            ", $insert_c);
echo <<<E_O_F
    // Insert record data.
    \$db->query("
        INSERT INTO $db_tbl (
            `$db_keys`
        ) VALUES (
            $db_vals
        )
    ");
E_O_F;
}

echo isset($op) ? '' : "\n\n\n";

// Update SQL.
if (!isset($op) || 'update' == $op) {
$update_skip_columns = array('added_datetime', 'added_by_user_id');
$comma = '';
$key_eq_val = '';
foreach ($c as $k=>$v) {
    if (in_array($k, $update_skip_columns)) {
        continue;
    }
    $key_eq_val .= $comma . "\n            `$k` = $v";
    $comma = ',';
}
echo <<<E_O_F
    // Update record data.
    \$db->query("
        UPDATE $db_tbl SET$key_eq_val
        WHERE `$primary_key` = '" . \$db->escapeString(\$frm['$primary_key']) . "'
    ");
E_O_F;
}

echo isset($op) ? '' : "\n\n\n";

// Delete SQL.
if (!isset($op) || 'delete' == $op) {
$where_clause = '';
$delete_skip_columns = array();
$delim = 'WHERE';
if (!empty($primary_key)) {
    $where_clause = "            $delim `$primary_key` = '\" . \$db->escapeString(\$frm['$primary_key']) . \"'\n";
    $delim = 'AND';
}
foreach ($c as $k=>$ignore) {
    if (in_array($k, $delete_skip_columns)) {
        continue;
    }
    $where_clause .= "            $delim `$k` = '\" . \$db->escapeString(\$frm['$k']) . \"'\n";
    $delim = 'AND';
}
echo <<<E_O_F
        // Delete record data.
        \$db->query("
            DELETE FROM $db_tbl
$where_clause        ");
E_O_F;
}

echo isset($op) ? '' : "\n\n\n";

// SortOrder methods SQL.
if (!isset($op) || 'sortorder' == $op) {
    echo "// Instantiate a sorting object with the default sort and order. Add SQL for each column.\n";
    echo "\$so = new SortOrder('$db_tbl.$primary_key', 'DESC');\n";
    echo "///\$so->asc_widget = sprintf('<i class=\"fa fa-arrow-up fa-fw\" title=\"%s\"></i>', _(\"Ascending\"));\n";
    echo "///\$so->desc_widget = sprintf('<i class=\"fa fa-arrow-down fa-fw\" title=\"%s\"></i>', _(\"Descending\"));\n";
    echo "\$so->setColumn('$db_tbl.$primary_key', '$db_tbl.$primary_key ASC', '$db_tbl.$primary_key DESC');\n";
    foreach ($c as $k=>$v) {
        echo "\$so->setColumn('$db_tbl.$k', '$db_tbl.$k ASC', '$db_tbl.$k DESC');\n";
    }
}

echo isset($op) ? '' : "\n\n\n";

// Search SQL
if (!isset($op) || 'search' == $op) {
$search_skip_columns = array('added_datetime', 'added_by_user_id', 'modified_datetime', 'modified_by_user_id', 'publish', 'featured');
$search_columns = $db_tbl . '.' . join(" LIKE '%\" . \$db->escapeString(\$qry_words[\$i]) . \"%'\n                    OR $db_tbl.", array_diff(array_keys($c), $search_skip_columns));
echo <<<E_O_F
            \$where_clause .= (empty(\$where_clause) ? 'WHERE' : 'AND') . "
                (
                    $search_columns LIKE '%" . \$db->escapeString(\$qry_words[\$i]) . "%'
                )
            ";
E_O_F;
}


echo isset($op) ? '' : "\n\n\n";