function saveConfig ($varName, $varValue, &$modx, &$output) {
    $sql = "delete from Config where varName = ?";
    $delStmt = $modx->prepare($sql);
    $result = $delStmt->execute(array($varName));
    if ($result) {
        $sql = "insert into Config (varName, varValue) values (?, ?)";
        $insStmt = $modx->prepare($sql);
        $result = $insStmt->execute(array($varName, $varValue));
        if ($result) {
            $output .= "Delete from &amp; insert to Config succeeded. (" . $varName . "=" . $varValue . ")<br>" . PHP_EOL;
        } else {
            $msg = $selFullProject->errorInfo();
            $output .= "Delete from Config succeeded but insert failed. (" . $varName . "=" . $varValue . ") " . $msg[2] . "<br>" . PHP_EOL;
        }
    } else {
        $msg = $selFullProject->errorInfo();
        $output .= "Delete from Config failed. (" . $varName . "=" . $varValue . ") " . $msg[2] . "<br>" . PHP_EOL;
    }
}

function deleteLoad (&$modx, &$output, &$tables, &$colNames, $exportDirPath) {
    $colValues = array (
        "Config" => "(?, ?)",
        "Titles" => "(?)",
        "SpecificStyles" => "(?)",
        "DownloadSites" => "(?, ?, ?)",
        "ProjectTypes" => "(?)",
        "Styles" => "(?, ?, ?)",
        "Artists" => "(?, ?, ?)",
        "SectionComments" => "(?, ?, ?, ?, ?)",
        "Projects" => "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
        "Downloads" => "(?, ?, ?, ?, ?)"
    );

    foreach ($tables as $table) {
        $sql = "delete from " . $table;
        $stmt = $modx->prepare ($sql);
        $output .= "Deleting from " . $table . "<br>" . PHP_EOL;
        $result = $stmt->execute();
    }

    /* Reverse the load order from the deletion order */
    $tables = array_reverse ($tables);

    foreach ($tables as $table) {
        $sql = "insert into " . $table . " (" . $colNames[$table] . ") values " . $colValues[$table];
        $stmt = $modx->prepare ($sql);
        $output .= "Loading " . $table . "<br>" . PHP_EOL;

        $fnum = fopen ($exportDirPath . $table, "r");
        while ($line = fgets($fnum, 4000)) {
            $line = trim ($line); /* Remove white space including CR if files came from Excel */
            $cols = explode ('|,|', $line); /* Split the line into individual columns */
            $cols[0] = str_replace ("|", "", $cols[0]); /* Remove | from first column */
            $cols[count($cols) - 1] = str_replace ("|", "", $cols[count($cols) - 1]); /* Remove | from last column */
            if (!$stmt->execute($cols)) {
                $msg = $stmt->errorInfo();
                $output .= "Insert to " . $table . " failed. " . $msg[2] . "<br>" . PHP_EOL;
                $output .= "colNames=" . $colNames[$table] . "<br>" . PHP_EOL;
                foreach ($cols as $col) {
                    $output .= $col . "<br>" . PHP_EOL;
                }
            }
        }
        fclose ($fnum);
    }
    return $output;
}

function getSchemaFromConstants () {
$schema = <<<DDL
CREATE TABLE Artists
(
 artist               VARCHAR(256) NOT NULL,
 artistAnchor         VARCHAR(256) NOT NULL,
 artistSort           VARCHAR(256) NOT NULL
);

ALTER TABLE Artists 
ADD CONSTRAINT XPKArtist PRIMARY KEY (artist);

CREATE TABLE Config
(
 varName              VARCHAR(256) NOT NULL,
 varValue             VARCHAR(256) NULL
);

ALTER TABLE Config 
ADD CONSTRAINT XPKConfig PRIMARY KEY (varName);

CREATE TABLE Downloads
(
 artist               VARCHAR(256) NOT NULL,
 project              VARCHAR(256) NOT NULL,
 DLSize               INTEGER NOT NULL,
 DLURL                VARCHAR(256) NOT NULL,
 projectType          VARCHAR(32) NOT NULL
);

ALTER TABLE Downloads 
ADD CONSTRAINT XPKProjectVersions PRIMARY KEY (artist,project,projectType,DLURL);

CREATE TABLE DownloadSites
(
 urlIdentifier        VARCHAR(256) NOT NULL,
 generationRule       VARCHAR(4096) NULL,
 numDLs               SMALLINT NOT NULL
);

ALTER TABLE DownloadSites 
ADD CONSTRAINT XPKDownloadSource PRIMARY KEY (urlIdentifier,numDLs);

CREATE TABLE Projects
(
 artist               VARCHAR(256) NOT NULL,
 project              VARCHAR(256) NOT NULL,
 numTracks            SMALLINT NOT NULL,
 dateAdded            DATETIME NOT NULL,
 previewURL           VARCHAR(256) NOT NULL,
 wimpyURL             VARCHAR(256) NOT NULL,
 moreInfoTID          VARCHAR(6) NULL,
 discussFID           VARCHAR(6) NULL,
 showFID              boolean NULL,
 projectType          VARCHAR(32) NOT NULL,
 urlIdentifier        VARCHAR(256) NULL,
 numDLs               SMALLINT NULL,
 active               boolean NOT NULL,
 generalStyle         VARCHAR(256) NULL,
 explicitLyrics       boolean NULL,
 specificStyle        VARCHAR(256) NULL,
 title                VARCHAR(230) NULL,
 isNew                Boolean NULL
);

ALTER TABLE Projects 
ADD CONSTRAINT XPKProject PRIMARY KEY (artist,project,projectType);

CREATE TABLE ProjectTypes
(
 projectType          VARCHAR(32) NOT NULL
);

ALTER TABLE ProjectTypes 
ADD CONSTRAINT XPKProjectType PRIMARY KEY (projectType);

CREATE TABLE SectionComments 
(
 artist               VARCHAR(256) NOT NULL,
 generalStyle         VARCHAR(256) NOT NULL,
 comment              VARCHAR(4096) NULL,
 specificStyle        VARCHAR(256) NOT NULL,
 title                VARCHAR(230) NOT NULL
);

ALTER TABLE SectionComments 
ADD CONSTRAINT XPKSectionComments PRIMARY KEY (artist,generalStyle,specificStyle,title);

CREATE TABLE SpecificStyles
(
 specificStyle        VARCHAR(256) NOT NULL
);

ALTER TABLE SpecificStyles 
ADD CONSTRAINT XPKSpecificStyles PRIMARY KEY (specificStyle);

CREATE TABLE Styles
(
 generalStyle         VARCHAR(256) NOT NULL,
 styleAnchor          VARCHAR(256) NULL,
 sequence             SMALLINT NULL
);

ALTER TABLE Styles 
ADD CONSTRAINT XPKStyle PRIMARY KEY (generalStyle);

CREATE TABLE Titles
(
 title                VARCHAR(230) NOT NULL
);

ALTER TABLE Titles 
ADD CONSTRAINT XPKTitles PRIMARY KEY (title);

ALTER TABLE Downloads 
ADD CONSTRAINT R_3 FOREIGN KEY (artist, project, projectType) REFERENCES Projects (artist, project, projectType);

ALTER TABLE Projects 
ADD CONSTRAINT R_2 FOREIGN KEY (artist) REFERENCES Artists (artist);

ALTER TABLE Projects 
ADD CONSTRAINT R_16 FOREIGN KEY (projectType) REFERENCES ProjectTypes (projectType);

ALTER TABLE Projects 
ADD CONSTRAINT R_25 FOREIGN KEY (urlIdentifier, numDLs) REFERENCES DownloadSites (urlIdentifier, numDLs);

ALTER TABLE Projects 
ADD CONSTRAINT R_26 FOREIGN KEY (generalStyle) REFERENCES Styles (generalStyle);

ALTER TABLE Projects 
ADD CONSTRAINT R_35 FOREIGN KEY (specificStyle) REFERENCES SpecificStyles (specificStyle);

ALTER TABLE Projects 
ADD CONSTRAINT R_37 FOREIGN KEY (title) REFERENCES Titles (title);

ALTER TABLE SectionComments 
ADD CONSTRAINT R_29 FOREIGN KEY (artist) REFERENCES Artists (artist);

ALTER TABLE SectionComments 
ADD CONSTRAINT R_30 FOREIGN KEY (generalStyle) REFERENCES Styles (generalStyle);

ALTER TABLE SectionComments 
ADD CONSTRAINT R_39 FOREIGN KEY (specificStyle) REFERENCES SpecificStyles (specificStyle);

ALTER TABLE SectionComments 
ADD CONSTRAINT R_40 FOREIGN KEY (title) REFERENCES Titles (title);

DDL;
    return $schema;
}

function getSchemaFromFile () {
    /* Get the whole DDL file into $file. */
    $schema = file_get_contents (MODX_CORE_PATH . 'components/ms-mtk-gen/create.ddl');
    /* Replace carriage returns, if any, with blanks */
    $schema = str_replace (chr(13), " ", $schema);
    return $schema;
}

function create (&$modx, &$output) {
    $schema = getSchemaFromConstants ();
    /* $schema = getSchemaFromFile (); */

    /* Break $schema into individual DDL statements. */
    $ddlStmts = explode (";", $schema);

    /* Execute the statements. */
    $output .= "Creating database structure<br>";
    foreach ($ddlStmts as $ddlStmt) {
        trim($ddlStmt);
        if ($ddlStmt != "") {
            $sqlStmt = $modx->prepare($ddlStmt);
            if ($sqlStmt->execute()) {
                $output .= $ddlStmt . " succeeded.<br>";
            } else {
                $msg = $sqlStmt->errorInfo();
                $output .= $ddlStmt . ' <span class="red">failed. ' . $msg[2] . '</span><br>';
            }
        }
    }
    return $output;
}

function drop (&$modx, &$output, &$tables) {
/* Drop all of the tables */
    $output .= "Dropping tables<br>";
    foreach ($tables as $table) { /* Iterate thru the table names */
        $sql = "drop table " . $table; /* Form the SQL statement */
        $stmt = $modx->prepare ($sql);
        if ($stmt->execute()) {
            $output .= $sql . " succeeded<br>";
        } else {
            $msg = $stmt->errorInfo ();
            $output .= $sql . " failed. " . $msg[2] . "<br>";
        }
    }
}

function export (&$modx, &$output, &$tables, &$colNames) {
/* Export all of the tables into the same format the installation package uses ("|" enclosed, comma separated) */
    foreach ($tables as $table) { /* Iterate thru the table names */
        $sql = "select " . $colNames[$table] . " from " . $table; /* Form the SQL statement */
        $stmt = $modx->prepare ($sql);
        if ($stmt->execute()) {
            $stmt->setFetchMode(PDO::FETCH_NUM); /* Columns come out in the order of the select. */
            $lines = "";
            while ($row = $stmt->fetch()) {
                $lines .= "|" . $row[0] . "|"; /* The first, and sometimes only, column has no comma */
                for ($idx=1; $idx<count($row); $idx++) {
                    $lines .= ",|" . $row[$idx] . "|"; /* The second and greater columns need a comma before them */
                }
                $lines .= PHP_EOL; /* Terminate the line */
            }
            $result = file_put_contents ($_POST["exportDirPath"] . $table, $lines);
            if ($result) {
                $output .= $table . " exported<br>";
            } else {
                $output .= $table . " NOT exported - probable issue with file system permissions<br>";
            }
        } else {
            $msg = $stmt->errorInfo ();
            $output .= "Select from " . $table . " failed. " . $msg[2] . "<br>";
        }
    }
}

function deleteOne (&$modx, &$output) {
/* Delete a row from the DownloadSites table. */
    $sql = "delete from DownloadSites where urlIdentifier = ? and numDls = ?";
    $stmt = $modx->prepare ($sql);
    $result = $stmt->execute(array ($_POST["delUrlIdentifier"], $_POST["delNumDls"]));
    if ($result) {
        $output .= "Delete from DownloadSites succeeded.<br>" . PHP_EOL;
    } else {
        $msg = $stmt->errorInfo ();
        $output .= "Delete from DownloadSites failed. " . $msg[2] . "<br>" . PHP_EOL;
    }
}

function uaConfig (&$modx, &$output) {
    saveConfig ("previewHttpPath", $_POST["previewHttpPath"], $modx, $output);
    saveConfig ("previewFileSysPath", $_POST["previewFileSysPath"], $modx, $output);
    saveConfig ("multitrackHttpPath", $_POST["multitrackHttpPath"], $modx, $output);
    saveConfig ("multitrackFileSysPath", $_POST["multitrackFileSysPath"], $modx, $output);
}

function updateAdd (&$modx, &$output) {
/* Try to insert into DownloadSites. If it fails on duplicate key, do an update instead. */
    $numDLs = substr_count ($_POST["generationRule"], '$URL'); /* Count $URL in the rule to get the number of downloads it handles */
    if (($numDLs > 0) && ($numDLs < 10)) { /* Check for valid input. */
        $sql = "insert into DownloadSites (urlIdentifier, numDLs, generationRule) values (?, ?, ?)";
        $stmt = $modx->prepare($sql);
        $result = $stmt->execute(array ($_POST["urlIdentifier"], $numDLs, $_POST["generationRule"]));
        if ($result) {
            $output .= "Insert to DownloadSites succeeded<br>" . PHP_EOL;
        } else {
            $msg = $stmt->errorInfo();
            if ($msg[1] == 1062) { /* 1062 is duplicate key error so try yo update the row */
                $output .= "Insert to Downloads failed on duplicate key.<br>" . PHP_EOL;
                $sql = "update DownloadSites set generationRule = ? where urlIdentifier = ? and numDLs = ?";
                $stmt = $modx->prepare($sql);
                $result = $stmt->execute(array ($_POST["generationRule"], $_POST["urlIdentifier"], $numDLs));
                if ($result) {
                    $output .= "Update to DownloadSites succeeded.<br>" . PHP_EOL;
                } else {
                    $msg = $stmt->errorInfo();
                    $output .= "Update to DownloadSites failed. " . $msg[2] . "<br>" . PHP_EOL;
                }
            } else {
                /* The insert failed on something other than a duplicate key. */
                $output .= "Insert to Downloads failed. " . $msg[2] . "<br>" . PHP_EOL;
            }
        }
    } else {
        $output .= '< 1 or > 9 "$URL" strings in generation rule.<br>' . PHP_EOL;
    }
}

/* IMPORTANT - Setup $tables and $colNames here and pass them into the "export" and "deleteLoad"
   functions. They need to match exactly so the data will line up. $tables should be in the order
   needed for delete and drop (i.e. delete/drop tables with foreign keys first). The "deleteLoad" 
   function will reverse their order before loading. */
$tables = array ("SectionComments", "Downloads", "Projects", "Artists", "Styles", "ProjectTypes", "DownloadSites", "SpecificStyles", "Titles", "Config");
$colNames = array (
    "Config" => "varName, varValue",
    "Titles" => "title",
    "SpecificStyles" => "specificStyle",
    "DownloadSites" => "urlIdentifier, numDLs, generationRule",
    "ProjectTypes" => "projectType",
    "Styles" => "generalStyle, styleAnchor, sequence",
    "Artists" => "artist, artistSort, artistAnchor",
    "SectionComments" => "artist, generalStyle, specificStyle, title, comment",
    "Projects" => "artist, project, projectType, generalStyle, specificStyle, title, dateAdded, previewURL, wimpyURL, numTracks, explicitLyrics, " .
                  "moreInfoTID, discussFID, showFID, active, urlIdentifier, numDLs",
    "Downloads" => "artist, project, projectType, DLURL, DLSize"
);

/* Mainline logic */
if (!empty($_POST)) {
    $output = "";

    /* Determine which button was clicked */
    if (array_key_exists ("uaUrl", $_POST)) {
        updateAdd ($modx, $output);
    } elseif (array_key_exists ("delete", $_POST)) {
        deleteOne ($modx, $output);
    } elseif (array_key_exists ("uaConfig", $_POST)) {
        uaConfig ($modx, $output);
    } elseif (array_key_exists ("export", $_POST)) {
        if (substr($_POST["exportDirPath"], (strlen ($_POST["exportDirPath"]) - 1)) != "/") {
            $_POST["exportDirPath"] .= "/";
        } 
        saveConfig ("exportDirPath", $_POST["exportDirPath"], $modx, $output);
        export ($modx, $output, $tables, $colNames);
    } elseif (array_key_exists ("dropCreate", $_POST)) {
        drop ($modx, $output, $tables);
        create ($modx, $output);
    } elseif (array_key_exists ("deleteLoad", $_POST)) {
        deleteLoad ($modx, $output, $tables, $colNames, $_POST["exportDirPath"]);
    }

    /* This is common between the 3 "post" snippets. */
    $page = $modx->getObject('modResource', array("pagetitle" => "ms-mtk-UpdateStatus")); /* Get the page object for the UpdateStatus page */
    $page->setTVValue('updateStatus', $output); /* Set the updateStatus template var in the Update Status page to the output of this run */
    $resId = $modx->resource->get('id'); /* Get the ID of this page */
    $url = $modx->makeUrl($resId); /* Make a URL pointing to this page */
    $page->setTVValue('returnToResource', $url); /* Set the URL into the returnToResource template var so Update Status knows where to return to */
    $modx->cacheManager->refresh(); /* Should probably change this to only clear the resource cache */
    $modx->sendRedirect($modx->makeUrl($page->get('id'))); /* Redirect to the Update Status page */
}