<?php

/**
 * Suppress "error - 0 - No summary was found for this file" on phpdoc generation
 *
 * @package WPDataAccess\Simple_Form
 */
namespace WPDataAccess\Simple_Form;

use WPDataAccess\Connection\WPDADB;
use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Exist;
use WPDataAccess\Data_Dictionary\WPDA_List_Columns;
use WPDataAccess\Plugin_Table_Models\WPDA_Table_Settings_Model;
use WPDataAccess\Utilities\WPDA_Message_Box;
use WPDataAccess\WPDA;
/**
 * Class WPDA_Simple_Form_Data
 *
 * WPDA_Simple_Form_Data is responsible for data management. It queries the database, adds new records to tables
 * and updates table data. Simple validations are performed based on information retrieved from the data dictionary.
 *
 * @author  Peter Schulz
 * @since   1.0.0
 */
class WPDA_Simple_Form_Data {
    /**
     * Database schema name
     *
     * @var string
     */
    protected $schema_name;

    /**
     * Database table name
     *
     * @var string
     */
    protected $table_name;

    /**
     * Reference to calling form
     *
     * @var WPDA_Simple_Form
     */
    protected $calling_form;

    /**
     * Reference to column list
     *
     * @var WPDA_List_Columns
     */
    protected $wpda_list_columns;

    /**
     * Default success message
     *
     * Is set in the constructor to support internationalization.
     *
     * @var string
     */
    protected $wpda_success_msg;

    /**
     * Default failure message
     *
     * Is set in the constructor to support internationalization.
     *
     * @var string
     */
    protected $wpda_failure_msg;

    /**
     * Handle to data dictionary object
     *
     * @var WPDA_Dictionary_Exist
     */
    protected $wpda_data_dictionary;

    /**
     * WPDA_Simple_Form_Data constructor
     *
     * Check if table exists and access is granted.
     *
     * @param string            $schema_name Database schema name.
     * @param string            $table_name Database table name.
     * @param WPDA_List_Columns $wpda_list_columns Reference to column array.
     * @param WPDA_Simple_Form  $calling_form Reference to calling form.
     * @param string            $wpda_success_msg Message shown on success.
     * @param string            $wpda_failure_msg Message shown on failure.
     *
     * @since   1.0.0
     */
    public function __construct(
        $schema_name,
        $table_name,
        &$wpda_list_columns,
        &$calling_form,
        $wpda_success_msg,
        $wpda_failure_msg
    ) {
        $this->schema_name = $schema_name;
        $this->table_name = $table_name;
        // Table must exist and user must be authorized.
        $this->wpda_data_dictionary = new WPDA_Dictionary_Exist($this->schema_name, $this->table_name);
        if ( !$this->wpda_data_dictionary->table_exists() ) {
            wp_die( __( 'ERROR: Invalid table name or not authorized' ) );
        }
        $this->calling_form = $calling_form;
        $this->wpda_list_columns = $wpda_list_columns;
        $this->wpda_success_msg = $wpda_success_msg;
        $this->wpda_failure_msg = $wpda_failure_msg;
    }

    /**
     * Create new record
     *
     * Nothing to do!
     *
     * @return null
     * @since   1.0.0
     */
    public function new_row() {
        return null;
    }

    /**
     * Add records to database table
     *
     * @return bool TRUE = record successfully added to table
     * @since   1.0.0
     */
    public function add_row() {
        $column_values_to_be_inserted = null;
        foreach ( $this->wpda_list_columns->get_table_columns() as $column ) {
            if ( isset( $column['readonly'], $column['default'] ) ) {
                $this->calling_form->insert_column_default( $column['column_name'], WPDA::convert_default_value( $column['default'] ) );
            }
            if ( $column['column_name'] === $this->wpda_list_columns->get_auto_increment_column_name() ) {
                // Auto increment column is not added
            } else {
                if ( null === $this->calling_form->get_new_value( $column['column_name'] ) || '' === $this->calling_form->get_new_value( $column['column_name'] ) ) {
                    // Skip empty columns
                } elseif ( 'date' === WPDA::get_type( $column['data_type'] ) || 'time' === WPDA::get_type( $column['data_type'] ) ) {
                    // Convert date and time values
                    $column_values_to_be_inserted[$column['column_name']] = self::convert_datetime( $column['data_type'], $this->calling_form->get_new_value( $column['column_name'] ) );
                } else {
                    // Add value
                    $column_values_to_be_inserted[$column['column_name']] = $this->calling_form->get_new_value( $column['column_name'] );
                }
            }
        }
        $wpdadb = WPDADB::get_db_connection( $this->schema_name );
        if ( null === $wpdadb ) {
            wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name ) ) );
        }
        $result = $wpdadb->insert( $this->table_name, $column_values_to_be_inserted );
        // db call ok; no-cache ok.
        if ( 1 === $result ) {
            $msg = new WPDA_Message_Box(array(
                'message_text' => $this->wpda_success_msg,
            ));
            $msg->box();
            // If inserted record contains an auto_increment column: return value.
            if ( false !== $this->wpda_list_columns->get_auto_increment_column_name() ) {
                return $wpdadb->insert_id;
                // Return auto_increment value.
            } else {
                return true;
                // Return true = transaction succeeded.
            }
        } else {
            // An error occurred.
            $msg = new WPDA_Message_Box(array(
                'message_text'           => ( '' === $wpdadb->last_error ? $this->wpda_failure_msg : $this->wpda_failure_msg . ' [' . $wpdadb->last_error . ']' ),
                'message_type'           => 'error',
                'message_is_dismissible' => false,
            ));
            $msg->box();
            return false;
            // Return false = transaction failed.
        }
    }

    /**
     * Get record from database table
     *
     * @param int    $auto_increment_value Auto increment number (returned if provided by dbms).
     * @param string $wpda_err Error message to be shown on failure.
     *
     * @return mixed
     * @since   1.0.0
     */
    public function get_row( $auto_increment_value, $wpda_err ) {
        $settings_db = WPDA_Table_Settings_Model::query( $this->table_name, $this->schema_name );
        if ( isset( $settings_db[0]['wpda_table_settings'] ) ) {
            $settings_db_custom = json_decode( $settings_db[0]['wpda_table_settings'] );
            if ( isset( $settings_db_custom->table_settings->row_level_security ) && 'true' === $settings_db_custom->table_settings->row_level_security ) {
                // Check access
                $wp_nonce = ( isset( $_REQUEST['rownonce'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['rownonce'] ) ) : '' );
                // input var okay.
                $keys = '';
                foreach ( $this->wpda_list_columns->get_table_primary_key() as $key ) {
                    $keys .= "-{$key}-" . $this->calling_form->get_new_value( $key );
                }
                // Check action from list table
                if ( !wp_verify_nonce( $wp_nonce, "wpda-row-level-security-{$this->table_name}{$keys}" ) ) {
                    // Check action from data entry form (standard behaviour)
                    $wp_nonce = ( isset( $_REQUEST['_wpnonce'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['_wpnonce'] ) ) : '' );
                    // input var okay.
                    if ( !wp_verify_nonce( $wp_nonce, $this->calling_form->get_nonce_action() ) ) {
                        wp_die( __( 'ERROR: Not authorized', 'wp-data-access' ) );
                    }
                }
            }
        }
        $wpdadb = WPDADB::get_db_connection( $this->schema_name );
        if ( null === $wpdadb ) {
            wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name ) ) );
        }
        $table_columns = array();
        // Get all table columns.
        foreach ( $this->wpda_list_columns->get_table_columns() as $column ) {
            $table_columns[$column['column_name']] = $column['data_type'];
        }
        $where = '';
        // Compose where clause.
        $use_primary_key = true;
        if ( $auto_increment_value < 1 ) {
            foreach ( $this->wpda_list_columns->get_table_primary_key() as $pk_column ) {
                if ( '' === $this->calling_form->get_new_value( $pk_column ) ) {
                    $use_primary_key = false;
                }
            }
        }
        if ( isset( $_REQUEST['child_request'] ) && 'TRUE' === $_REQUEST['child_request'] && $auto_increment_value > -1 ) {
            // Special case:
            // - parent-child relation based on non-primary key parent column
            // - parent primary key is auto increment
            // - child primary key is auto increment
            // - insert record
            $use_primary_key = true;
        }
        if ( $use_primary_key ) {
            foreach ( $this->wpda_list_columns->get_table_primary_key() as $pk_column ) {
                $where_current = ( '' === $where ? ' where ' : ' and ' );
                if ( WPDA::get_type( $table_columns[$pk_column] ) === 'number' ) {
                    // Column data type is numeric:
                    // For numeric columns we need to omit quotes. All numeric values will be handled as float. MySQL
                    // will automatically convert them if necessary. Values supplied in a wrong format will be handed
                    // over to MySQL as is and might result in unpredictable results. For our simple form we rely on
                    // the user's judgement.
                    $where_current .= " `{$pk_column}` = %d";
                } else {
                    // Column data type is string:
                    // Quotes will be added to all non-numeric values. We might have issues with date and time fields.
                    // For our simple form we'll accept that limitation (at least for now).
                    $where_current .= " `{$pk_column}` = %s";
                }
                if ( $auto_increment_value > -1 && $pk_column === $this->wpda_list_columns->get_auto_increment_column_name() ) {
                    // For inserts with auto_increment columns use $wpdadb->insert_id.
                    $pkvalue = $auto_increment_value;
                } else {
                    if ( 0 === $wpda_err ) {
                        // No errors: use new value.
                        if ( $this->calling_form->get_new_value( $pk_column ) === '' ) {
                            wp_die( __( 'ERROR: Wrong arguments [missing primary key value]', 'wp-data-access' ) );
                        }
                        $pkvalue = $this->calling_form->get_new_value( $pk_column );
                    } else {
                        // There are errors: use old values (in case a key value was changed).
                        if ( $this->calling_form->get_old_value( $pk_column ) === '' ) {
                            wp_die( __( 'ERROR: Wrong arguments [missing primary key value]', 'wp-data-access' ) );
                        }
                        $pkvalue = $this->calling_form->get_old_value( $pk_column );
                    }
                }
                if ( WPDA::get_type( $table_columns[$pk_column] ) === 'date' || WPDA::get_type( $table_columns[$pk_column] ) === 'time' ) {
                    // Convert date and time values
                    $pkvalue = self::convert_datetime( $table_columns[$pk_column], $pkvalue, $pkvalue );
                }
                $where .= $wpdadb->prepare( $where_current, $pkvalue );
                // phpcs:ignore Standard.Category.SniffName.ErrorCode
            }
        } else {
            $alternative_key_found = false;
            // Check for alternative keys
            foreach ( $this->wpda_list_columns->get_table_alternative_keys() as $alternative_keys ) {
                foreach ( $alternative_keys as $alternative_key ) {
                    $alternative_keys_found = 0;
                    if ( $this->calling_form->get_new_value( $alternative_key ) !== '' ) {
                        $where_current = ( '' === $where ? ' where ' : ' and ' );
                        if ( WPDA::get_type( $table_columns[$alternative_key] ) === 'number' ) {
                            $where_current .= " `{$alternative_key}` = %f";
                        } else {
                            $where_current .= " `{$alternative_key}` = %s";
                        }
                        $pkvalue = $this->calling_form->get_new_value( $alternative_key );
                        $alternative_keys_found++;
                    }
                }
                if ( $alternative_keys_found === count( $alternative_keys ) ) {
                    //phpcs:ignore - 8.1 proof
                    $alternative_key_found = true;
                }
            }
            if ( !$alternative_key_found ) {
                wp_die( __( 'ERROR: Wrong arguments [missing key value]', 'wp-data-access' ) );
            }
            $where .= $wpdadb->prepare( $where_current, $pkvalue );
            // phpcs:ignore Standard.Category.SniffName.ErrorCode
        }
        if ( '' === $this->schema_name ) {
            $query = "\n\t\t\t\t\tselect *\n\t\t\t\t\tfrom `{$this->table_name}`\n\t\t\t\t\t{$where}\n\t\t\t\t";
        } else {
            $query = "\n\t\t\t\t\tselect *\n\t\t\t\t\tfrom `{$wpdadb->dbname}`.`{$this->table_name}`\n\t\t\t\t\t{$where}\n\t\t\t\t";
        }
        $result = $wpdadb->get_results( $query, 'ARRAY_A' );
        // phpcs:ignore Standard.Category.SniffName.ErrorCode
        if ( 1 === $wpdadb->num_rows ) {
            return $result;
        } else {
            wp_die( __( 'ERROR: Wrong arguments [no data found]', 'wp-data-access' ) );
        }
    }

    /**
     * Update current record (write changes to database)
     *
     * @since   1.0.0
     */
    public function set_row() {
        $wpdadb = WPDADB::get_db_connection( $this->schema_name );
        if ( null === $wpdadb ) {
            wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name ) ) );
        }
        $column_values_to_be_updated = null;
        foreach ( $this->wpda_list_columns->get_table_columns() as $column ) {
            if ( isset( $column['readonly'] ) ) {
                $this->calling_form->revert_column_value( $column['column_name'] );
                continue;
            }
            if ( $this->calling_form->get_old_value( $column['column_name'] ) !== $this->calling_form->get_new_value( $column['column_name'] ) ) {
                if ( 'number' === WPDA::get_type( $column['data_type'] ) && '' === $this->calling_form->get_new_value( $column['column_name'] ) ) {
                    // Convert empty numeric value to null
                    $column_values_to_be_updated[$column['column_name']] = null;
                } else {
                    if ( 'time' === WPDA::get_type( $column['data_type'] ) || 'date' === WPDA::get_type( $column['data_type'] ) ) {
                        // Convert date and time values
                        $column_values_to_be_updated[$column['column_name']] = self::convert_datetime( $column['data_type'], $this->calling_form->get_new_value( $column['column_name'] ) );
                    } else {
                        $new_value = $this->calling_form->get_new_value( $column['column_name'] );
                        if ( '' !== $new_value ) {
                            $column_values_to_be_updated[$column['column_name']] = $this->calling_form->get_new_value( $column['column_name'] );
                        } else {
                            if ( isset( $column['is_nullable'] ) && 'YES' === $column['is_nullable'] ) {
                                $column_values_to_be_updated[$column['column_name']] = null;
                            } else {
                                $column_values_to_be_updated[$column['column_name']] = $this->calling_form->get_new_value( $column['column_name'] );
                            }
                        }
                    }
                }
            }
        }
        if ( null === $column_values_to_be_updated ) {
            // Nothing to update.
            if ( $_REQUEST['wpda_message'] && '' !== $_REQUEST['wpda_message'] ) {
                // phpcs:ignore WordPress.Security.ValidatedSanitizedInput
                // Happens when inserting new row on parent child page
                $msgtxt = sanitize_text_field( wp_unslash( $_REQUEST['wpda_message'] ) );
                // input var okay.
            } else {
                $msgtxt = __( 'Nothing to save', 'wp-data-access' );
            }
            $msg = new WPDA_Message_Box(array(
                'message_text' => $msgtxt,
            ));
            $msg->box();
        } else {
            // Write changes to database.
            $where = array();
            foreach ( $this->wpda_list_columns->get_table_primary_key() as $pk_column ) {
                $action = $this->calling_form->get_form_action();
                $action2 = $this->calling_form->get_form_action2();
                if ( 'edit' === $action && 'save' === $action2 ) {
                    // Form was submitted after update: use old key value to build where clause.
                    if ( '' === $this->calling_form->get_old_value( $pk_column ) ) {
                        wp_die( __( 'ERROR: Wrong arguments [missing primary key value]', 'wp-data-access' ) );
                    }
                    $where[$pk_column] = $this->calling_form->get_old_value( $pk_column );
                    // Set primary keys value(s).
                    $data_type = $this->wpda_list_columns->get_column_data_type( $pk_column );
                    if ( 'time' === $data_type || 'date' === $data_type ) {
                        // Convert date and time values
                        $where[$pk_column] = self::convert_datetime( $data_type, $where[$pk_column] );
                    }
                } else {
                    // Form submitted a new record: use new value (no old value available).
                    if ( $this->calling_form->get_new_value( $pk_column ) === '' ) {
                        wp_die( __( 'ERROR: Wrong arguments [missing primary key value]', 'wp-data-access' ) );
                    }
                    $where[$pk_column] = $this->calling_form->get_new_value( $pk_column );
                    // Set primary keys value(s).
                }
            }
            // var_dump($column_values_to_be_updated);
            $result = $wpdadb->update( $this->table_name, $column_values_to_be_updated, $where );
            // db call ok; no-cache ok.
            if ( 1 === $result ) {
                // Since we are updating by key, result must be exactly 1 record.
                $msg = new WPDA_Message_Box(array(
                    'message_text' => $this->wpda_success_msg,
                ));
                $msg->box();
            } else {
                if ( 0 === $result && '' === $wpdadb->last_error ) {
                    $table_info = WPDA::get_table_values( $this->schema_name, $this->table_name );
                    if ( 1 === count( $table_info ) && 'connect' === strtolower( $table_info[0]['engine'] ) ) {
                        //phpcs:ignore - 8.1 proof
                        // Connect engine does not return number of rows updated
                        // Presuming update was successful when no error message was returned
                        $msg = new WPDA_Message_Box(array(
                            'message_text' => $this->wpda_success_msg,
                        ));
                        $msg->box();
                        return;
                    }
                    // Nothing to update.
                    $msg = new WPDA_Message_Box(array(
                        'message_text' => __( 'Nothing to save', 'wp-data-access' ),
                    ));
                    $msg->box();
                } else {
                    // An error occurred.
                    $msg = new WPDA_Message_Box(array(
                        'message_text'           => ( '' === $wpdadb->last_error ? $this->wpda_failure_msg : $this->wpda_failure_msg . ' [' . $wpdadb->last_error . ']' ),
                        'message_type'           => 'error',
                        'message_is_dismissible' => false,
                    ));
                    $msg->box();
                }
            }
        }
    }

    public static function convert_datetime( $column_type, $column_value, $error_value = null ) {
        if ( null === $column_value || '' === $column_value ) {
            return null;
        }
        switch ( $column_type ) {
            case 'time':
                $date_format = WPDA::get_option( WPDA::OPTION_PLUGIN_TIME_FORMAT );
                $db_format = WPDA::DB_TIME_FORMAT;
                break;
            case 'date':
                $date_format = WPDA::get_option( WPDA::OPTION_PLUGIN_DATE_FORMAT );
                $db_format = WPDA::DB_DATE_FORMAT;
                break;
            default:
                $date_format = WPDA::get_option( WPDA::OPTION_PLUGIN_DATE_FORMAT ) . ' ' . WPDA::get_option( WPDA::OPTION_PLUGIN_TIME_FORMAT );
                $db_format = WPDA::DB_DATETIME_FORMAT;
        }
        $convert_date = \DateTime::createFromFormat( $date_format, $column_value );
        if ( false !== $convert_date ) {
            $converted_value = $convert_date->format( $db_format );
        } else {
            $converted_value = $error_value;
        }
        return $converted_value;
    }

}
