<?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');
}

/**
 * Clients table
 */
class Clients extends \WP_List_Table
{
    const TABLE_NAME = 'kirilkirkov_wp_invoices_clients';

    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;
    }

    /**
     * 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 ".esc_sql($i->table_name)." (
            `id` mediumint(9) NOT NULL AUTO_INCREMENT,
            `client` varchar(255) NOT NULL,
            `is_individual` tinyint(1) NOT NULL DEFAULT '0',
            `identification_number` varchar(255) DEFAULT NULL,
            `has_vat_registration` tinyint(1) NOT NULL DEFAULT '0',
            `vat_number` varchar(255) DEFAULT NULL,
            `accountable_person` varchar(255) DEFAULT NULL,
            `city` varchar(255) DEFAULT NULL,
            `address` varchar(300) DEFAULT NULL,
            `country` varchar(255) DEFAULT NULL,
            `beneficiary` varchar(255) DEFAULT NULL,
            `created_from` int(11) NOT NULL,
            `zip_code` varchar(200) DEFAULT 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);

        /**
         * /// end
         */
    }

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

    /**
     * Screen options clients page
     */
    public function screen_option_clients()
    {
        global $kirilkirkovwipdf_clients_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_clients_list_per_page) {
            return;
        }

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

    public function get_columns()
    {
        $columns = array(
          'cb' => '<input type="checkbox" />',
          'id' => esc_html__('ID', 'kirilkirkov-pdf-invoice-manager'),
          'client' => esc_html__('Client', 'kirilkirkov-pdf-invoice-manager'),
          'created_from' => esc_html__('Created From', 'kirilkirkov-pdf-invoice-manager'),
          'count_invoices' => esc_html__('Count Invoices', 'kirilkirkov-pdf-invoice-manager'),
          'action' => esc_html__('Action', '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;
    }
      
    public function prepare_items()
    {
        $columns = $this->get_columns();
        $hidden = array();
        $sortable = array(
            'id' => array('id', false),
            'client' => array('client', false),
            'created_from' => array('created_from', false),
            'count_invoices' => array('count_invoices', false),
            'create_invoice' => array('create_invoice', 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_clients_list_per_page', 10);

		$current_page = $this->get_pagenum();
		$total_items  = $this->record_count();
        $this->set_pagination_args([
			'total_items' => $total_items,
			'per_page'  => $per_page,
            'total_pages' => ceil( $total_items / $per_page )
		]);

        $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_clients_list_page']) || !wp_verify_nonce( sanitize_text_field(wp_unslash($_POST['_nonce_clients_list_page'])), 'clients_list_page' )) {
                wp_die( esc_html__('Invalid nonce', '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' ) );
	}

    private function get_query_where()
    {
        global $wpdb;

        // No nonce verification needed here – only reading a safe GET 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( ' (client LIKE %s) ', "%$search%");
        }
        
        return $where;
    }

    /**
	 * 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);
        }

        // All the data from get_query_where is already prepared, escaped and safe
        // 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 a safe GET param for 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 a safe GET param for 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 = "SELECT id, client, identification_number, is_individual, 
        created_from, created_at, updated_at FROM " . esc_sql($this->table_name);

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

        // $orderby and $order are sanitized with ->sanitize_orderby and ->sanitize_order_type
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $user_query .= $wpdb->prepare( " ORDER BY " . esc_sql($orderby) . " " . esc_sql($order) . " LIMIT %d OFFSET %d", $per_page, $offset);


        // All the data from get_query_where is already prepared and safe. No need cache here
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results( $user_query, ARRAY_A  );
    }

    /**
	 * 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';
	}

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

    // 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];
    }

    /**
     * Columns modify below
     */
    public function column_created_from($item)
    {
        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_CLIENT_PAGE_SLUG. '&edit=' . $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['client']
        );
 
        // 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;
    }

    public function column_action($item)
    {
        $create_invoice_link = admin_url( 'admin.php?page=' . Config::SUB_MENU_CREATE_INVOICE_PAGE_SLUG. '&kkwp_create_inv_for_client=' . $item['id'] );
        $output = sprintf(
            '<a class="button kkwi-button kkwi-button-small" href="%s">%s</a>', 
            esc_url( $create_invoice_link ),
            esc_html__( 'Create Invoice', 'kirilkirkov-pdf-invoice-manager' )
        );
        return $output;
    }

    public function column_count_invoices($item)
    {
        global $wpdb;
        $invoices_table = $wpdb->prefix . Invoices::TABLE_CLIENTS_NAME;

        // 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.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(id) FROM " . esc_sql($invoices_table) . " WHERE client_from_list = %d", 
            $item['id']
        ) );

        return (int)$count;
    }

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

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() and ->prepare() with hardcoded table name – 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, client, is_individual, 
        identification_number, has_vat_registration, vat_number, accountable_person,
        city, address, country, beneficiary, zip_code, created_from, created_at, updated_at
        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($article)
    {
        $prefixed = [];
        foreach($article as $k => $v) {
            if($k === 'id') {
                $prefixed['id'] = $v;
                continue;
            }
            $prefixed[Config::INPUTS_PREFIX . $k] = $v;
        }
        
        return $prefixed;
    }

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

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() and ->prepare() with hardcoded table name – 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, client, is_individual, 
        identification_number, has_vat_registration, vat_number, accountable_person,
        city, address, country, beneficiary, zip_code, created_from, created_at, updated_at
        FROM " . esc_sql($w->table_name) . " WHERE client LIKE %s OR identification_number LIKE %s LIMIT %d", 
        '%' . $search . '%',
        '%' . $search . '%',
        $limit
        ), OBJECT );
        if(!count($result)) {
            return array();
        }

        return $result;
    }

     /**
     * Get all clients
     * 
     * @param bool $mapped - map with prefixes
     */
    public static function get_all_clients($mapped = false)
    {        
        global $wpdb;
        $w = new Clients(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;
        }
        
        $clients = array();
        foreach($results as $result) {
            $clients[] = self::get_client($result['id'], $mapped);
        }

        return $clients;
    }

    /**
     * Rewrite default search_box function
     */
    public function search_box( $text, $input_id )
    {
        // No nonce verification needed here – only reading a safe _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 safe _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['orderby'] ) ) {
            // No nonce verification needed here – only reading a safe _REQUEST param for search.
            // 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 safe _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['order'] ) ) {
            // No nonce verification needed here – only reading a safe _REQUEST param for search.
            // 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 safe _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['post_mime_type'] ) ) {
            // No nonce verification needed here – only reading a safe _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 safe _REQUEST param for search.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
		if ( ! empty( $_REQUEST['detached'] ) ) {
            // No nonce verification needed here – only reading a safe _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__('Client 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
	}
}