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

class PeriodicInvoices extends \WP_List_Table
{
    const CRONJOB_NAME = 'kirilkirkovwipdf_periodic_invoices_cron';

    const TABLE_NAME = 'kirilkirkov_wp_periodic_invoices';
    const TABLE_NAME_HISTORY = 'kirilkirkov_wp_periodic_invoices_history';

    public $table_name;
    public $table_name_history;

    // values used for reissue_type
    public $reissue_types = [
        'weekly',
    ];

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

    /**
     * Constructor
     * 
     * @param bool $exclude_parent - if true exclude parent constructor, because of API requests for eg.
     */
    public function __construct($exclude_parent = false)
    {
        if($exclude_parent === false) {
            parent::__construct();
        }
        $this->init();
    }

    /**
     * On plugin activation create tables
     */
    public static function create_tables()
    {
        $i = new self();
        $i->init();

        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS ".esc_sql($i->table_name)." (
            `id` mediumint(9) NOT NULL AUTO_INCREMENT,
            `invoice_id` int(10) UNSIGNED NOT NULL,

            `reissue_type` varchar(100) DEFAULT NULL,
            `reissue_custom_type` TEXT DEFAULT NULL,
            `reissue_data` varchar(100) DEFAULT NULL,
            `last_reissue_date` varchar(100) DEFAULT NULL,
            `next_reissue_date` varchar(100) 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);

        // HISTORY OF REISSUED INVOICES
        $sql = "CREATE TABLE IF NOT EXISTS ".esc_sql($i->table_name_history)." (
            `id` mediumint(9) NOT NULL AUTO_INCREMENT,
            `invoice_id` int(10) UNSIGNED NOT NULL,
            `periodic_invoice_id` int(10) UNSIGNED 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);
    }

    /**
     * Page for list periodic invoices
     */
    public function list_page()
    {
        $table = $this;
        $Config = Config::class;
        
        require plugin_dir_path( __FILE__ ) . '../../Views/PeriodicInvoices/List.php';
    }

    public function edit_page()
    {
        // show msg in frontend if bool
        $successEdit = null;
        $id = isset($_GET['edit']) ? (int)$_GET['edit'] : 0;

        // if submit form
        if(
            isset( $_SERVER['REQUEST_METHOD'] ) &&
            $_SERVER['REQUEST_METHOD'] === 'POST' &&
            isset($_POST['periodic_invoices_edit_nonce']) && wp_verify_nonce( sanitize_text_field(wp_unslash($_POST['periodic_invoices_edit_nonce'])), 'periodic_invoices_edit_nonce' )) {
            
            $resultUpdate = $this->updateOptions($_POST, $id);
            $successEdit = $resultUpdate === false ? false : true;
        }

        // get item data
        $item = $this->get_item($id);
        if($item === false) {
            require plugin_dir_path( __FILE__ ) . '../../Views/404.php';
            return;
        }

        $Config = Config::class;
        $reissue_types = $this->reissue_types;
        $history = $this->get_list_of_history($id);
        
        require plugin_dir_path( __FILE__ ) . '../../Views/PeriodicInvoices/Edit.php';
    }

    private function get_list_of_history($period_inv_id)
    {
        global $wpdb;
        $period_inv_id = (int)$period_inv_id;
        if($period_inv_id < 1) {
            return false;
        }

        $invoicesClass = new Invoices();
        $invoicesClass->init();
        $invoicesTableName = $invoicesClass->table_name;

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with hardcoded tables names – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $items = $wpdb->get_results("SELECT " . esc_sql($this->table_name_history) . ".*, " . esc_sql($invoicesTableName) . ".invoice_number
        FROM " . esc_sql($this->table_name_history) . " 
        INNER JOIN " . esc_sql($this->table_name) . " ON " . esc_sql($this->table_name) . ".id = " . esc_sql($this->table_name_history) . ".periodic_invoice_id
        INNER JOIN " . esc_sql($invoicesTableName) . " ON " . esc_sql($this->table_name_history) . ".invoice_id = " . esc_sql($invoicesTableName) . ".id
        WHERE periodic_invoice_id = " . esc_sql($period_inv_id), ARRAY_A); 
        if(!$items) {
            return false;
        }

        return $items;
    }

    private function get_item($id)
    {
        global $wpdb;
        $id = (int)$id;
        if($id < 1) {
            return false;
        }

        $invoicesClass = new Invoices();
        $invoicesClass->init();
        $invoicesTableName = $invoicesClass->table_name;

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_row() with hardcoded tables names – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $item = $wpdb->get_row("SELECT " . esc_sql($this->table_name) . ".*, " . esc_sql($invoicesTableName) . ".invoice_number, " . esc_sql($invoicesTableName) . ".invoice_date
        FROM " . esc_sql($this->table_name) . " 
        INNER JOIN " . esc_sql($invoicesTableName) . " ON " . esc_sql($this->table_name) . ".invoice_id = " . esc_sql($invoicesTableName) . ".id
        WHERE " . esc_sql($this->table_name) . ".id = " . esc_sql($id) . " 
        LIMIT 1", ARRAY_A);
        if(!$item) {
            return false;
        }

        return $item;
    }

    private function updateOptions($post, $updateId)
    {
        global $wpdb;

        $item = $this->get_item($updateId);
        if($item === false) {
            return;
        }

        $dt = new \DateTime();
        $updated_at = $dt->format('Y-m-d H:i:s');

        // We use $wpdb->query() with a prepared statement – the recommended way to perform UPDATE operations on custom tables. Do not cache UPDATE operations.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE ". esc_sql($this->table_name)." 
                SET reissue_type = %s, next_reissue_date = %s, updated_at = %s 
                WHERE id = %d LIMIT 1",
                mb_substr(trim($post[Config::INPUTS_PREFIX.'reissue_type']), 0, 255),
                self::get_next_reissue_date($post[Config::INPUTS_PREFIX.'reissue_type'], $item['invoice_date']),
                $updated_at,
                (int)$updateId
            )
        );
    }

    public static function get_next_reissue_date($reissue_type, $invoice_date)
    {
        $date = \DateTime::createFromFormat(Config::get_dates_format_type(), $invoice_date);
        if(!$date) {
            return null;
        }

        switch($reissue_type) {
            case 'weekly':
                $date->modify('+1 week');
                break;
            default:
                return null;
        }

        return $date->format(Config::get_dates_format_type());
    }

    /**
     * Extended methods for WP_List_Table
     */
    public function prepare_items()
    {
        $columns = $this->get_columns();
        $hidden = array();
        $sortable = array(
            'id' => array('id', false),
            'invoice_number' => array('invoice_number', false),
            'created_at' => array('created_at', true),
        );
        $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_periodic_invoices_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
		]);

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

    public function get_columns()
    {
        $columns = array(
          'cb' => '<input type="checkbox" />',
          'id' => esc_html__('ID', 'kirilkirkov-pdf-invoice-manager'),
          'invoice_number' => esc_html__('Reissue Invoice', 'kirilkirkov-pdf-invoice-manager'),
          'reissue_type' => esc_html__('Reissue Type', 'kirilkirkov-pdf-invoice-manager'),
        //   'reissue_data' => esc_html__('Reissue Data', 'kirilkirkov-pdf-invoice-manager'), // next release
          'last_reissue_date' => esc_html__('Last Reissue Date', 'kirilkirkov-pdf-invoice-manager'),
          'next_reissue_date' => esc_html__('Next Reissue Date', 'kirilkirkov-pdf-invoice-manager'),
          'created_at' => esc_html__('Created At', 'kirilkirkov-pdf-invoice-manager'),
        );
        return $columns;
    }

    private function record_count()
    {
		global $wpdb;

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_var() with hardcoded table name – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_var("SELECT COUNT(id) FROM " . esc_sql($this->table_name));
	}

    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['periodic_invoices_list_nonce']) || !wp_verify_nonce( sanitize_text_field(wp_unslash($_POST['periodic_invoices_list_nonce'])), 'periodic_invoices_list_nonce' )) {
                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 );
                }
            }
		}
	}

    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 search.
         */

        // Get orderby and order values. Do not need nonce verification because these are just used for sorting the data.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $orderby = $orderby_raw = ( isset( $_GET['orderby'] ) ) ? esc_sql( sanitize_text_field( wp_unslash($_GET['orderby'] ) ) ) : 'id';
        
        // Get order value. Do not need nonce verification because these are just used for sorting the data.
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $order = ( isset( $_GET['order'] ) ) ? esc_sql( sanitize_text_field( wp_unslash($_GET['order'] ) ) ) : 'ASC';
        
        $orderby = $this->sanitize_orderby( $orderby, $order );
        $order = $this->sanitize_order_type( $order, $orderby_raw );
        $offset = ( $page_number - 1 ) * $per_page;

        $invoicesClass = new Invoices();
        $invoicesClass->init();
        $invoicesTableName = $invoicesClass->table_name;

        // We need a fresh result directly from the database (not cached). Using $wpdb->get_results() with prepared statement and hardcoded tables names – the standard and safe approach for this operation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results( $wpdb->prepare("SELECT " . esc_sql($this->table_name).".id, " . esc_sql($this->table_name).".invoice_id, " . esc_sql($this->table_name).".reissue_type, " . esc_sql($invoicesTableName).".invoice_number,
        " . esc_sql($this->table_name).".reissue_data, " . esc_sql($this->table_name).".last_reissue_date, " . esc_sql($this->table_name).".next_reissue_date, " . esc_sql($this->table_name).".created_at FROM " . esc_sql($this->table_name). " INNER JOIN 
        " . esc_sql($invoicesTableName). " ON " . esc_sql($this->table_name). ".invoice_id = " . esc_sql($invoicesTableName). ".id ORDER BY " . esc_sql($orderby) . " " . esc_sql($order) . " LIMIT %d OFFSET %d", $per_page, $offset), ARRAY_A );
    }

    /**
     * Screen options invoices list page
     */
    public function screen_option_invoices()
    {
        global $kirilkirkovwipdf_periodic_invoices_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_periodic_invoices_list_per_page) {
            return;
        }

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

    /**
	 * 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 = [
            'id',
            'invoice_number',
            'created_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.
     * @param string $order_by - If is client type because the relation return empty
	 *
	 * @return string
	 */
	protected function sanitize_order_type( $order_type, $order_by )
    {
        if(trim(strtolower($order_by)) === 'client') {
            return '';
        }

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

    public function column_default( $item, $column_name )
    {
        return $item[$column_name];
    }

    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' ) );
        
        // history
        /**
         * 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_history, array( 'periodic_invoice_id' => $id ), array( '%d' ) );
	}

    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_PERIODIC_INVOICES_EDIT_PAGE_SLUG. '&edit=' . $item['id'] );
        $output = sprintf(
            '<div class="mobile-periodic-inv-name-table-preview"><span class="kk-inv-label-desktop">%d</span></label></div>', 
            $item['id']
        );
 
        // 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_reissue_type( $item )
    {
        return esc_html( sanitize_text_field(ucfirst($item['reissue_type'])) );
    }

    public function column_invoice_number( $item )
    {
        $edit_link = admin_url( 'admin.php?page=' . Config::SUB_MENU_CREATE_INVOICE_PAGE_SLUG. '&edit=' . $item['invoice_id'] );
        return '<a href="' . esc_url( $edit_link ) . '">' . $item['invoice_number'] . '</a>';
    }

    /**
     * Cronjobs for periodic invoices
     */
    public static function addCronjob()
    {
        add_action( self::CRONJOB_NAME, function () {
            
            $i = new self();
            $i->init();
            
            global $wpdb;

            $dt = new \DateTime();
            $today = $dt->format(Config::get_dates_format_type());
    
            // 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. Config::get_dates_format_type() returns hardcoded string.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $needsToReissueToday = $wpdb->get_results( "SELECT * FROM ".esc_sql($i->table_name)." WHERE next_reissue_date = '" . esc_sql($today) . "'", ARRAY_A ); 
            if(!count($needsToReissueToday)) {
                return;
            }
    
            foreach($needsToReissueToday as $invoiceToReissue) {
                $newInvoiceId = $i->reIssueInvoice($invoiceToReissue['invoice_id']);
                if($newInvoiceId === false) {
                    continue;
                }
    
                $dt = new \DateTime();
                $updated_at = $dt->format('Y-m-d H:i:s');

                //  Do not cache UPDATE operations. Using $wpdb->query() with a prepared statement – the recommended way to perform UPDATE operations on custom tables.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE ".esc_sql($i->table_name)." 
                        SET last_reissue_date = %s, next_reissue_date = %s, updated_at = %s 
                        WHERE id = %d LIMIT 1",
                        $today,
                        self::get_next_reissue_date($invoiceToReissue['reissue_type'], $today),
                        $updated_at,
                        (int)$invoiceToReissue['id']
                    )
                );

                // Do not cache INSERT operations. Using $wpdb->insert() – the recommended way to perform INSERT operations on custom tables.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->insert($i->table_name_history, array(
                    'invoice_id' => $newInvoiceId,
                    'periodic_invoice_id' => $invoiceToReissue['id'],
                ));
            }
            
        });

        add_action('init', function () {
            if ( ! wp_next_scheduled(self::CRONJOB_NAME) ) {
                wp_schedule_event( time(), 'daily', self::CRONJOB_NAME );
            }
        });
    }

    public static function removeCronjob()
    {
        $timestamp = wp_next_scheduled( self::CRONJOB_NAME );
        if ( $timestamp ) {
            wp_unschedule_event( $timestamp, self::CRONJOB_NAME );
        }
    }

    private function reIssueInvoice($invoice_id)
    {
        // copy invoice with clients and items
        $invoicesClass = new Invoices();
        $invoicesClass->init();
        $invoice = $invoicesClass::get_invoice($invoice_id);
        
        if($invoice === false) {
            return false;
        }

        $origin_invoice_id = (int)$invoice['id'];
        
        // clear manadory fields
        $invoice['invoice_date'] = gmdate(Config::get_dates_format_type());
        $invoice['date_tax_event'] = gmdate(Config::get_dates_format_type());
        $invoice['invoice_number'] = $invoicesClass::get_next_inv_number($invoice['invoice_type']);
        $invoice['paid'] = 0;
        $invoice['status'] = 0;

        // remove not needed
        unset($invoice['id']);
        unset($invoice['created_at']);
        unset($invoice['updated_at']);
        unset($invoice['client']['id']);
        unset($invoice['save_periodic_inv']);
        unset($invoice['send_at']);

        // add additional fields to be added
        $invoice['client_from_list'] = $invoice['client']['client_from_list'];

        // add prefixes to simulate post form
        $newArray = array();
        foreach($invoice as $key => &$value) {
            if(strtolower($key) == 'id') {
                $newArray[$key] = $value;
                $value = null;
                continue;
            }

            $newArray[Config::INPUTS_PREFIX . $key] = $value;
            $value = null;
        }

        // create new invoice
        $new_invoice_id = CreateInvoice::create_invoice($newArray);
        if($new_invoice_id === false) {
            return false;
        }

        // create adata
        $copy_adata = [];
        $a_data = InvoicesAData::get_adata($origin_invoice_id);
        if($a_data) {
            $copy_adata = [ // !! do not include "stripe_price_id". !!
                'private_remarks' => $a_data['private_remarks'],
                'amount_for_stripe' => $a_data['amount_for_stripe'],
                'currency_for_stripe' => $a_data['currency_for_stripe'],
            ];
        }
        
        InvoicesAData::set_adata($copy_adata, $new_invoice_id['id']);

        return $new_invoice_id['id'];
    }
}