<?php

namespace KirilKirkov\WpInvoices;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

if(!class_exists('WP_List_Table')) {
    require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}

/**
 * Items
 */
class Items extends \WP_List_Table
{
    const TABLE_NAME = 'kirilkirkov_wp_items';

    public $table_name;
    private $creators = [];

    public function __construct($has_parent = true)
    {
        if($has_parent === true) {
            parent::__construct();
        }

        $this->init();
    }

    private function init()
    {
        global $wpdb;
        $this->table_name = $wpdb->prefix . self::TABLE_NAME;
    }

    /**
     * Main page table
     */
    public function items_page()
    {
        $table = new Items();
        $Config = Config::class;
        
        require plugin_dir_path( __FILE__ ) . '../../Views/Items/Items.php';
    }

    /**
     * Called from plugin activation hook
     */
    public static function create_tables()
    {
        $i = new self(false);
        $i->init(); // set tables names

        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

        $sql = "CREATE TABLE IF NOT EXISTS ".$i->table_name." (
            `id` mediumint(9) NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL,
            `price` varchar(100) NOT NULL,
            `available_quantity` varchar(100) NOT NULL DEFAULT '0',
            `created_from` int(11) NOT NULL,
            `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY id (id)
        ) $charset_collate;";
        dbDelta($sql);
    }

    public function get_columns()
    {
        $columns = array(
          'cb' => '<input type="checkbox" />',
          'id' => esc_html__('ID', 'kirilkirkov-pdf-invoice-manager'),
          'name' => esc_html__('Name', 'kirilkirkov-pdf-invoice-manager'),
          'price' => esc_html__('Price', 'kirilkirkov-pdf-invoice-manager'),
          'available_quantity' => esc_html__('Available Quantity', 'kirilkirkov-pdf-invoice-manager'),
          'created_from' => esc_html__('Created From', 'kirilkirkov-pdf-invoice-manager'),
          'created_at' => esc_html__('Created At', 'kirilkirkov-pdf-invoice-manager'),
          'updated_at' => esc_html__('Updated At', 'kirilkirkov-pdf-invoice-manager')
        );
        return $columns;
    }
    
    /**
     * Screen options items page
     */
    public function screen_option_items()
    {
        global $kirilkirkovwipdf_items_list_per_page;
 
        $screen = get_current_screen();
    
        // get out of here if we are not on our settings page
        if(!is_object($screen) || $screen->id != $kirilkirkovwipdf_items_list_per_page) {
            return;
        }

        add_screen_option( 'per_page', array(
            'label'   => esc_html__('Items per page', 'kirilkirkov-pdf-invoice-manager'),
            'default' => 10,
            'option'  => 'kirilkirkovwipdf_items_list_per_page'
        ) );
    }

    public function prepare_items()
    {
        $columns = $this->get_columns();
        $hidden = array();
        $sortable = array(
            'id' => array('id', false),
            'name' => array('name', false),
            'price' => array('price', false),
            'available_quantity' => array('available_quantity', false),
            'created_from' => array('created_from', false),
            'created_at' => array('created_at', false),
            'updated_at' => array('updated_at', false),
        );
        $this->_column_headers = array($columns, $hidden, $sortable);

        /** Process bulk action */
		$this->process_bulk_action();

        // fetch data and set pagination
        $per_page  = $this->get_items_per_page( 'kirilkirkovwipdf_items_list_per_page', 10);

		$current_page = $this->get_pagenum();
		$total_items  = $this->record_count();
        $this->set_pagination_args([
			'total_items' => $total_items, // WE have to calculate the total number of items
			'per_page'  => $per_page, // WE have to determine how many items to show on a page,
            'total_pages' => ceil( $total_items / $per_page ) // use ceil to round up
		]);

        $table_data = $this->fetch_table_data($per_page, $current_page);
        $this->items = $table_data;
    }

    public function process_bulk_action()
    {
		// If the delete bulk action is triggered
		if ( ( isset( $_POST['action'] ) && $_POST['action'] == 'delete' )
		     || ( isset( $_POST['action2'] ) && $_POST['action2'] == 'delete' )) {

            if(!isset($_POST['_nonce_items_list_page']) || !wp_verify_nonce( sanitize_text_field(wp_unslash($_POST['_nonce_items_list_page'])), 'items_list_page' )) {
                wp_die( esc_html__('Invalid nonce items.', 'kirilkirkov-pdf-invoice-manager') );
            }
			$delete_ids = isset($_POST['bulk_delete']) ? array_map( 'absint',wp_unslash($_POST['bulk_delete'])) : [];
            if(is_array($delete_ids) && count($delete_ids)) {
                foreach ( $delete_ids as $id ) {
                    $this->delete_record( $id );
                }
            }
		}
	}

    /**
	 * Delete record.
	 *
	 * @param int $id - ID
	 */
	public function delete_record($id)
    {
		global $wpdb;
        $id = (int)$id;
        if($id < 1) {
            return;
        }

        /**
         * Delete a record from the custom table.
         * We use $wpdb->delete() – the recommended way to perform DELETE operations on custom tables.
         * Caching is not applicable for deletion operations.
         */
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete($this->table_name, array( 'id' => $id ), array( '%d' ) );
	}

    /**
	 * Returns the count of records in the database.
	 *
	 * @return null|string
	 */
	private function record_count()
    {
		global $wpdb;

        $q = "SELECT COUNT(id) FROM " . esc_sql($this->table_name);
        $where = $this->get_query_where();
        if(count($where)) {
            $q .= ' WHERE '.join(' AND ', $where);
        }

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_var() with prepare() – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_var($q); 
	}

    private function fetch_table_data($per_page = 5, $page_number = 1)
    {
        global $wpdb;

        // No nonce verification needed here – only reading safe _GET params for orderby and order.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $orderby = ( isset( $_GET['orderby'] ) ) ? esc_sql( sanitize_text_field( wp_unslash($_GET['orderby']) ) ) : 'id';
        $orderby = $this->sanitize_orderby( $orderby );
        
        // No nonce verification needed here – only reading safe _GET params for orderby and order.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $order = ( isset( $_GET['order'] ) ) ? esc_sql( sanitize_text_field( wp_unslash($_GET['order']) ) ) : 'ASC';
        $order = $this->sanitize_order_type( $order );

        $offset = ( $page_number - 1 ) * $per_page;

        $user_query_str = "SELECT id, name, price, available_quantity,  
        created_from, created_at, updated_at FROM " . esc_sql($this->table_name);

        $where = $this->get_query_where();
        if(count($where)) {
            $user_query_str .= ' WHERE '.join(' AND ', $where);
        }

        // All the data from $user_query_str is prepared in $this->get_query_where(). $this->table_name is hardcoded string.
        //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $user_query = $wpdb->prepare($user_query_str . " ORDER BY " . esc_sql($orderby) . " " . esc_sql($order) . " LIMIT %d OFFSET %d", $per_page, $offset);  

        // query output_type will be an associative array with ARRAY_A.
        // All the data from $this->get_query_where() is prepared.
        //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $query_results = $wpdb->get_results( $user_query, ARRAY_A );
        
        // return result array to prepare_items.
        return $query_results;
    }

    private function get_query_where()
    {
        global $wpdb;

        // No nonce verification needed here – only reading a safe _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $search = ( isset( $_REQUEST['s'] ) ) ? sanitize_text_field(wp_unslash($_REQUEST['s'])) : false;

        $where = [];
        if($search && trim($search) != '') {
            $where[] = $wpdb->prepare( ' (name LIKE %s) ', "%$search%");
        }
        
        return $where;
    }

    /**
	 * Heavily restricts the possible columns by which a user can order by the table
	 *
	 * @param string $orderby The column by which we want to order.
	 *
	 * @return string
	 */
	protected function sanitize_orderby( $orderby ) {
		$valid_column_names = [
            'created_from',
            'created_at',
            'updated_at',
		];

		if ( in_array( strtolower($orderby), $valid_column_names, true ) ) {
			return strtolower($orderby);
		}

		return 'created_at';
	}

    /**
	 * Heavily restricts the possible type by which a user can order
	 *
	 * @param string $order_type The type by which we want to order asc/desc.
	 *
	 * @return string
	 */
	protected function sanitize_order_type( $order_type ) {
		$valid_types = [
			'ASC',
			'DESC',
		];

		if ( in_array( strtoupper($order_type), $valid_types, true ) ) {
			return strtoupper($order_type);
		}

		return 'ASC';
	}

    // WordPress looks for methods called column_{key_name}, e.g. function column_booktitle
    // this is default
    public function column_default( $item, $column_name )
    {
        return $item[$column_name];
    }

    public function get_bulk_actions()
    {
        $actions = array(
          'delete' => esc_html__('Delete', 'kirilkirkov-pdf-invoice-manager')
        );
        return $actions;
    }

    public function column_created_from($item)
    {
        // prevent double call
        if(isset($this->creators[$item['created_from']])) {
            return $this->creators[$item['created_from']];
        }
        $user = get_user_by('id', $item['created_from']);
        if($user === false) {
            return '-';
        }
        $this->creators[$user->ID] = $user->user_login;
        return $user->user_login;
    }

    public function column_cb($item)
    {
        return sprintf(
            '<input type="checkbox" name="bulk_delete[]" value="%d" />', 
            $item['id']
        );
    }

    public function column_id($item)
    {
        $edit_link = admin_url( 'admin.php?page=' . Config::SUB_MENU_CREATE_ITEM_PAGE_SLUG. '&edit=' . $item['id'] . '&nonce=' . wp_create_nonce( 'edit_item_' . $item['id'] ) );
        $output = sprintf(
            '<div class="mobile-client-name-table-preview"><span class="kk-inv-label-desktop">%d</span><span class="kk-inv-label-mobile">%s</span></label></div>', 
            $item['id'], $item['name']
        );
 
        // Get actions.
        $actions = array(
            'edit'   => '<a href="' . esc_url( $edit_link ) . '">' . esc_html__( 'Edit', 'kirilkirkov-pdf-invoice-manager' ) . '</a>',
        );
 
        $row_actions = array();
        foreach ( $actions as $action => $link ) {
            $row_actions[] = '<span class="' . esc_attr( sanitize_text_field($action) ) . '">' . $link . '</span>';
        }
 
        $output .= '<div class="row-actions">' . implode( ' | ', $row_actions ) . '</div>';
 
        return $output;
    }

    /**
     * @return mixed - boolean or array
     */
    public static function get_items($search = '', $limit = 20)
    {
        global $wpdb;
        $w = new Items(false);

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded table name and prepare – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->get_results( $wpdb->prepare( 'SELECT id, name, price, available_quantity   
        FROM ' . esc_sql($w->table_name) . 
        ' WHERE name LIKE %s LIMIT %d', '%' . $search . '%', $limit), OBJECT );
        if(!count($result)) {
            return [];
        }

        return $result;
    }

    /**
     * @return mixed - boolean or array
     */
    public static function get_item($id, $mapped = false, $all_fields = false)
    {
        if((int)$id < 1) {
            return false;
        }
        $id = (int)$id;
        
        global $wpdb;
        $w = new Items(false);

        $fields = 'id, name, price, available_quantity';
        if($all_fields === true) {
            $fields = 'id, name, price, available_quantity, created_from, created_at, updated_at';
        }

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded table name and prepare – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->get_results( $wpdb->prepare( 'SELECT ' . esc_sql($fields) . '
        FROM ' . esc_sql($w->table_name) . ' 
        WHERE id = %d 
        LIMIT 1', 
        $id), OBJECT );
        if(!count($result)) {
            return false;
        }
        
        if($mapped === true) {
            return self::mapTaskWithPrefix($result[0]);
        }

        return $result[0];
    }

    private static function mapTaskWithPrefix($category)
    {
        $prefixed = [];
        foreach($category as $k => $v) {
            if($k === 'id') {
                $prefixed['id'] = $v;
                continue;
            }
            $prefixed[Config::INPUTS_PREFIX . $k] = $v;
        }
        
        return $prefixed;
    }

    /**
     * Decrease available quantity of items when create invoice
     * 
     * @param array $post - submited post data
     */
    public static function decrease_quantity($post)
    {
        // stop if disabled
        $disabled_items_update_quantity_on_create = get_option(Config::INPUTS_PREFIX.'disabled_items_update_quantity_on_create');
        if((int)$disabled_items_update_quantity_on_create > 0) {
            return;
        }

        if(isset($post[Config::INPUTS_PREFIX.'items']) 
         && is_array($post[Config::INPUTS_PREFIX.'items'])
         && count($post[Config::INPUTS_PREFIX.'items'])) {
            $items = $post[Config::INPUTS_PREFIX.'items'];

            if(isset($items['item_from_list']) && count($items['item_from_list']) > 0) {
                
                global $wpdb;
                $w = new Items(false);
                
                foreach($items['item_from_list'] as $k => $item_id) {
                    $quantity = isset($items['items_quantities']) && isset($items['items_quantities'][$k]) && !is_array($items['items_quantities'][$k]) ? (float)$items['items_quantities'][$k] : 0;
                    if($quantity > 0 && (int)$item_id > 0) {

                        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded table name and prepare – the standard and safe approach for this operation.
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $wpdb->get_results( $wpdb->prepare( 'UPDATE ' . esc_sql($w->table_name) . ' 
                        SET available_quantity = available_quantity - %s WHERE id = %d 
                        LIMIT 1', 
                        $item_id,
                        $quantity
                        ) 
                        , OBJECT );
                        
                    }
                }
            }
        }
    }

    /**
     * Update available quantity of items when update invoice
     * 
     * @param array $post - submited post data
     */
    public static function update_quantity($post, $old_items)
    {
        // stop if disabled
        $disabled_items_update_quantity_on_edit = get_option(Config::INPUTS_PREFIX.'disabled_items_update_quantity_on_edit');
        if((int)$disabled_items_update_quantity_on_edit > 0) {
            return;
        }

        global $wpdb;
        $w = new Items(false);

        // get all inserted items
        $old = [];
        if(count($old_items)) {
            foreach($old_items as $oi) {
                if((int)$oi->item_from_list < 1) {
                    continue;
                }
                if(!isset($old[(int)$oi->item_from_list])) {
                    $old[(int)$oi->item_from_list] = (float)$oi->quantity;
                } else {
                    $old[(int)$oi->item_from_list] += (float)$oi->quantity;
                }
            }
        }

        // get new submitted items
        $new = [];
        if(isset($post[Config::INPUTS_PREFIX.'items']) 
         && is_array($post[Config::INPUTS_PREFIX.'items'])
         && count($post[Config::INPUTS_PREFIX.'items'])) {
            $items = $post[Config::INPUTS_PREFIX.'items'];

            if(isset($items['item_from_list']) && count($items['item_from_list']) > 0) {
                foreach($items['item_from_list'] as $k => $item_id) {
                    if((int)$item_id < 1) {
                        continue;
                    }
                    $quantity = $items['items_quantities'][$k];
                    if(!isset($new[(int)$item_id])) {
                        $new[(int)$item_id] = (float)$quantity;
                    } else {
                        $new[(int)$item_id] += (float)$quantity;
                    }
                }
            }
        }

        // compare to the new ones and increase/decrease if has difference
        if(count($old)) {
            foreach($old as $item_id => $quantity) {
                $new_q = null;

                // increase
                if(isset($new[$item_id]) && $new[$item_id] < $quantity) {
                    $new_q = (float)($quantity - $new[$item_id]);
                    $operator = '+';
                }
                // decrease
                if(isset($new[$item_id]) && $new[$item_id] > $quantity) {
                    $new_q = (float)($new[$item_id] - $quantity);
                    $operator = '-';
                }
                // if is deleted
                if(!isset($new[$item_id])) {
                    $new_q = (float)$quantity;
                    $operator = '+';
                }

                if($new_q !== null) {
                    // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded table name and prepare – the standard and safe approach for this operation.
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $wpdb->get_results( $wpdb->prepare( 'UPDATE ' . esc_sql($w->table_name) . ' 
                    SET available_quantity = available_quantity '.esc_sql($operator).' %s WHERE id = %d 
                    LIMIT 1', 
                    $new_q,
                    $item_id)
                    , OBJECT );
                }
            }
        }

        // compare to the old ones and decrease if has new items
        if(count($new)) {
            foreach($new as $item_id => $quantity) {
                // if is new item
                if(!isset($old[$item_id])) {
                    // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded table name – the standard and safe approach for this operation.
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $wpdb->get_results( $wpdb->prepare( 'UPDATE ' . esc_sql($w->table_name) . ' 
                    SET available_quantity = available_quantity - %s WHERE id = %d 
                    LIMIT 1',
                    $quantity,
                    $item_id
                    ) 
                    , OBJECT );
                }
            }
        }
    }

    /**
     * Get all items
     * 
     * @param bool $mapped - map with prefixes
     */
    public static function get_all_items($mapped = false)
    {        
        global $wpdb;
        $w = new Items(false);

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded table name – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results( 'SELECT id FROM ' . esc_sql($w->table_name), ARRAY_A ); 
        if(!count($results)) {
            return false;
        }
        
        $items = array();
        foreach($results as $result) {
            $items[] = self::get_item($result['id'], $mapped, true);
        }

        return $items;
    }

    /**
     * Rewrite default search_box function
     */
    public function search_box( $text, $input_id )
    {
        // No nonce verification needed here – only reading a _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( empty( $_REQUEST['s'] ) && ! $this->has_items() ) {
			return;
		}

		$input_id = $input_id . '-search-input';

        // No nonce verification needed here – only reading a  _REQUEST param for orderby.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['orderby'] ) ) {
            // No nonce verification needed here – only reading a  _REQUEST param for orderby.
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended
			echo '<input type="hidden" name="orderby" value="' . esc_attr( sanitize_text_field(wp_unslash($_REQUEST['orderby'] ) ) ) . '" />';
		}
        // No nonce verification needed here – only reading a  _REQUEST param for orderby type.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['order'] ) ) {
            // No nonce verification needed here – only reading a  _REQUEST param for order type.
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended
			echo '<input type="hidden" name="order" value="' . esc_attr( sanitize_text_field(wp_unslash($_REQUEST['order'] ) ) ) . '" />';
		}
        // No nonce verification needed here – only reading a _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['post_mime_type'] ) ) {
            // No nonce verification needed here – only reading a _REQUEST param for search.
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended
			echo '<input type="hidden" name="post_mime_type" value="' . esc_attr( sanitize_text_field(wp_unslash($_REQUEST['post_mime_type'] ) ) ) . '" />';
		}
        // No nonce verification needed here – only reading a _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['detached'] ) ) {
            // No nonce verification needed here – only reading a _REQUEST param for search.
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended
			echo '<input type="hidden" name="detached" value="' . esc_attr( sanitize_text_field(wp_unslash($_REQUEST['detached'] ) ) ) . '" />';
		}
		?>
            <p class="search-box">
                <label class="screen-reader-text" for="<?php echo esc_attr( sanitize_text_field($input_id)); ?>"><?php echo esc_html(sanitize_text_field($text)); ?>:</label>
                <input placeholder="<?php echo esc_attr__('Item name', 'kirilkirkov-pdf-invoice-manager') ?>" type="search" id="<?php echo esc_attr( sanitize_text_field($input_id)); ?>" name="s" value="<?php _admin_search_query(); ?>" />
                    <?php submit_button( $text, '', '', false, array( 'id' => 'search-submit' ) ); ?>
            </p>
		<?php
	}
}