<?php declare(strict_types=1);

/**
 * {@inheritDoc}
 *
 * @license http://www.gnu.org/licenses/gpl-3.0.txt GPLv3
 */
class InstrumentImporter extends DataImporter
{
    protected \Database $DB;

    /**
     * This value should be updated if the CandID class in LORIS is updated.
     */
    const CANDID_LENGTH = 6;
    /**
     * This value should be updated if the source project has a PSCID of
     * a custom length (the LORIS default at this time is 6).
     * This value is used to extract a PSCID from a CommentID based on an index.
     */
    const PSCID_LENGTH = 6;

    /* An array of CommentIDs that exist in the database. */
    protected $existingCommentIDs = [];

    /**
     * {@inheritDoc}
     *
     * @param SplFileInfo $mappingFile A path to the CSV mapping file.
     * @param SplFileInfo $dataFile    A path to the CSV data file.
     * @param Database    $DB          LORIS Database connection
     *
     * @return void
     */
    public function __construct(
        SplFileInfo $mappingFile,
        SplFileInfo $dataFile,
        \Database $DB
    ) {
        $this->DB = $DB;
        // The name of the table must be the first value in the filename preceding
        // an underscore. e.g, "$instrumentName_dataExtract_output.csv".
        $this->table = substr(
            $dataFile->getFilename(),
            0,
            strpos($dataFile->getFilename(), '_')
        );

        // Access list of existing CommentIDs
        $this->existingCommentIDs = $DB->pselectCol(
            'SELECT CommentID from flag',
            []
        );

        // Create a mapping between PSCIDs and new PSCIDs to new CandIDs.
        parent::__construct($mappingFile, $dataFile);
    }

    /**
     * {@inheritDoc}
     *
     * @param string[] $row CSV data to be converted to an SQL statement.
     *
     * @return void
     */
    function buildSQLQuery(array $row): void
    {
        $data = $row;

        // Use the old PSCID to get the new CandID.
        $newCandID = $this->newCandID($this->newPSCID($data['PSCID']));

        // Retrieve the CommentID for this candidate/session/instrument using
        // the new CandID and the visit label.
        $query = 'SELECT f.CommentID
            FROM flag f
            INNER JOIN session s ON s.ID = f.SessionID
            INNER JOIN candidate c ON c.CandID = s.CandID
            JOIN test_names tn ON tn.ID = f.TestID
            WHERE c.CandID = :newCandID 
            AND s.Visit_label = :visitLabel 
            AND tn.test_name = :table
            AND f.CommentID NOT LIKE "DDE%"';

        $newCommentID = $this->DB->pselectOne(
            $query,
            [
                'newCandID'  => $newCandID,
                'visitLabel' => $data['Visit_label'],
                'table'      => $this->table,
            ]
        );

        // Validate
        if (empty($newCommentID)) {
            $msg = "No CommentID could be found for candidate `%s`, " .
                "visit label `%s`, and instrument `%s`";
            echo sprintf($msg, $newCandID, $data['Visit_label'], $this->table)
                . PHP_EOL;
            return;
        }

        // Update the flag table
        $command['table']    = 'flag';
        $command['data']     = [
            'Data_entry'     => $data['Data_entry'],
            'Administration' => $data['Administration'],
        ];
        $command['where']    = ['CommentID' => $newCommentID];
        $this->UPDATEQueue[] = $command;
        unset($command);
        // Unset all values that don't belong in the instrument table
        unset(
            $data['PSCID'],
            $data['Visit_label'],
            $data['Data_entry'],
            $data['Administration']
        );

        // Update the instrument table
        $command['table']    = $this->table;
        $command['data']     = $data;
        $command['where']    = ['CommentID' => $newCommentID];
        $this->UPDATEQueue[] = $command;
    }
}
