// SPDX-FileCopyrightText: 2021 Andrea Chirulescu <andrea.chirulescu@gmail.com>
// SPDX-FileCopyrightText: 2021 Harald Eilertsen <haraldei@anduin.net>
//
// SPDX-License-Identifier: AGPL-3.0-or-later

<?php
/*
 * I kinda overloaded this snippet. Added comments for each function. But this
 * is used in the giglog admin page, which should only be available to admin
 * users. After the file is being uploaded into the concertlists folder, its
 * content is written into wpg_files. Then the content is split into lines and
 * each line is transformed intoa  concert
 */

/* this checks th wpg_files table to see if any file is uploaded but hasn't
 * gone through the processing process - aka fetching each line and
 * transforming it into a concert line
 */
function giglogadmin_getunprocessed()
{
    global $wpdb;

    $content = '<br /><h3> UNPROCESSED ROWS</h3><table class="concertstable">';
    $content .= '<tr class="concertsheaderrow"><th>Filerow</th><th>FILENAME</th><th>DATE</TH><th>UploadedContent</th>';
    $query   = 'SELECT rowid,filename,filedate,rowcontent from wpg_files where processed="N"';
    $results = $wpdb->get_results($query);
    foreach ($results AS $row) {
        $content .= '<tr>';
        $content .= '<td>' . $row->rowid . '</td>';
        $content .= '<td>' . $row->filename . '</td>';
        $content .= '<td>' . $row->filedate . '</td>';
        $content .= '<td>' . $row->rowcontent . '</td>';
        $content .= '</tr>';
    }
    $content .= '</table>';
    return $content;
}

/* function that goes through each line of the unprocessed file. Each line is
 * checked against the concerts table. if it exists - concert and date and
 * venue - it does nothing with it. If it doesn't exist, it checks if band or
 * venue exists. If they don't, they get created, if they do, their ID from
 * their table is fetchd and used in concerts table
 */
function giglogadmin_insertconcerts()
{
    global $wpdb;
    $concertlist = '<p>Inserted the following:</p>';
    $newconcert= [];
    $query1   = 'SELECT id,rowid,filename,filedate,rowcontent from wpg_files where processed="N"';
    $cresults = $wpdb->get_results($query1);
    foreach ($cresults AS $row) {
        $rowfileid   = $row->id;
        $resultArray = explode("\t", $row->rowcontent);
        $band        = $resultArray[0];
        $venue       = $resultArray[1];
        $condate     = date('Y-m-d', strtotime($resultArray[2]));
        $ticketlink  = $resultArray[3];
        $eventlink   = $resultArray[4];
        //first item in the row should be band $resultArray[0]; second should be venue $resultArray[1]; third should be concert date $resultArray[2];
        //fourth item is ticketlink $resultArray[3];  fifth item is eventlink $resultArray[4];

        //processing band
        $bandsql = 'SELECT id FROM wpg_bands WHERE upper(wpgband_name)="' . $band . '"';
        $results = $wpdb->get_results($bandsql);
        if ($results)
            $newconcert[0] = $results[0]->id;
        else {
            $wpdb->insert('wpg_bands', array(
                'id' => '',
                'wpgband_name' => $band
            ));
            echo ($wpdb->last_error);
            $newconcert[0] = $wpdb->insert_id;
        }
        //done processing band

        //processing venue
        if (is_numeric($venue))
            $newconcert[1] = $venue;
        else {
            $venuesql = 'SELECT id FROM wpg_venues WHERE upper(wpgvenue_name)="' . $venue . '"';
            $results  = $wpdb->get_results($venuesql);
            if ($results)
                $newconcert[1] = $results[0]->id;
            else {
                $wpdb->insert('wpg_venues', array(
                    'id' => '',
                    'wpgvenue_name' => $venue
                ));
                echo ($wpdb->last_error);
                $newconcert[1] = $wpdb->insert_id;
            }
        }
        //done processing venue

        //not sure how to check dates, hopefully manual verification of files will take care of it

        //check if concert already exists and return ID if it does.  Not checking by date, to be considered
        $csql = 'SELECT id from wpg_concerts where band = ' . $newconcert[0] . ' and venue = ' . $newconcert[1] . ' and wpgconcert_date ="' . $condate . '"';

        $cresults = $wpdb->get_results($csql);
        if ($cresults) {
            $usql = 'UPDATE wpg_files SET processed="D", wpgc_id = ' . $cresults[0]->id . ' WHERE id = ' . $rowfileid;

            $uresults = $wpdb->get_results($usql);
            $concertlist .= 'DUPLICATE ROW detected BAND ' . $band . ' with band ID ' . $newconcert[0];
            $concertlist .= ', VENUE ' . $venue . ' with venue ID ' . $newconcert[1];
            $concertlist .= ', CONCERTDATE ' . $condate;
            $concertlist .= ' <br />';
        } else {
            $wpdb->insert('wpg_concerts', array(
                'id' => '',
                'band' => $newconcert[0],
                'venue' => $newconcert[1],
                'wpgconcert_date' => $condate,
                'wpgconcert_tickets' => $ticketlink,
                'wpgconcert_event' => $eventlink
            ));
            echo ($wpdb->last_error);
            $newconcertid = $wpdb->insert_id;

            $usql = 'UPDATE wpg_files SET processed="Y", wpgc_id = ' . $newconcertid . ' WHERE id = ' . $rowfileid;

            $uresults = $wpdb->get_results($usql);
            $concertlist .= 'BAND ' . $band . ' with band ID ' . $newconcert[0];
            $concertlist .= ', VENUE ' . $venue . ' with venue ID ' . $newconcert[1];
            $concertlist .= ', CONCERTDATE ' . $condate . ', Ticket LINK ' . $ticketlink . ', event LINK' . $eventlink;
            $concertlist .= ' <br />';

        }

        //end check if concert exists


        //remember to add the concert ID when displaying


    } //end looping through unprocessed rows

    return $concertlist;
}

function giglogadmin_display_unprocessed() {
    $output = giglogadmin_getunprocessed();

    $output .= '<form method="POST" action=""><input type="submit" name="ProcessConcerts" value="ProcessConcerts"/></form>';


    if (isset($_POST['ProcessConcerts'])) {
        $output .= giglogadmin_insertconcerts();

        //$url2 = $_SERVER['REQUEST_URI'];
        //header("Refresh: 5; URL=$url2"); //reload page
    } //end if button for process concerts is pressed

    return $output;
}