<?php

namespace KirilKirkov\WpInvoices;

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

/**
 * Additional Data for Invoices
 * more information about invoices
 */
class InvoicesAData
{
    const TABLE_NAME = 'kirilkirkov_wp_invoices_adata';

    public $table_name;

    public function init()
    {
        global $wpdb;
        
        $this->table_name = $wpdb->prefix . self::TABLE_NAME;
        // do not add another functions here. Its called in many places.
    }

    public function __construct()
    {
        $this->init();
        // its not good to add another functions here. Its called in many places.
    }

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

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

        // main invoices table
        $sql = "CREATE TABLE IF NOT EXISTS ".esc_sql($i->table_name)." (
            `id` mediumint(9) NOT NULL AUTO_INCREMENT,
            `invoice_id` mediumint(9) NULL DEFAULT NULL,
            `private_remarks` text,
            `slug` varchar(255) NOT NULL,
            `stripe_price_id` varchar(255) NULL DEFAULT NULL,
            `amount_for_stripe` varchar(255) NULL DEFAULT NULL,
            `currency_for_stripe` varchar(10) NULL DEFAULT NULL,
            `created_at` datetime NOT NULL,
            `updated_at` datetime NOT NULL,
            UNIQUE KEY id (id),
            UNIQUE KEY slug (slug)
        ) $charset_collate;";
        dbDelta($sql);
    }

    public static function delete_adata($invoice_id)
    {
        global $wpdb;
        $i = new self();
        $i->init(); // set tables names

        /**
         * 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(
            $i->table_name,
            array('invoice_id' => $invoice_id)
        );
    }

    public static function set_adata($post, $invoice_id)
    {
        // insert to table
        global $wpdb;
        $i = new self();
        $i->init(); // set tables names

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

        $slug = substr(md5($invoice_id . $created_at), 0, 8) . '-' . $invoice_id;

        // No cache needed for insert. Standard insert.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->insert(
            $i->table_name,
            array_merge(array(
                'invoice_id' => $invoice_id,
                'slug' => $slug,
                'created_at' => $created_at,
                'updated_at' => $created_at
            ), self::get_sql_array($post))
        );
    }
    
    public static function update_adata($post, $invoice_id)
    {
        // check if exists
        $adata = self::get_adata($invoice_id);
        if (!$adata) {
            self::set_adata($post, $invoice_id);
            return;
        }

        // update table
        global $wpdb;
        $i = new self();
        $i->init(); // set tables names

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

        // Stripe. Remove old price id if has update on "amount_for_stripe" or "currency_for_stripe"
        if (
            isset($post['amount_for_stripe']) && 
            isset($post['currency_for_stripe']) && 
            trim($adata['stripe_price_id']) != '' && 
            trim($adata['amount_for_stripe']) != '' &&
            trim($adata['currency_for_stripe']) != '' &&
            (
                trim($adata['amount_for_stripe']) != trim($post['amount_for_stripe']) || 
                trim($adata['currency_for_stripe']) != trim($post['currency_for_stripe'])
            )
        ) {
            // No cache needed for update. Standard update.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update(
                $i->table_name,
                array(
                    'stripe_price_id' => '',
                    'updated_at' => $updated_at,
                ),
                array('invoice_id' => $invoice_id)
            );
        }

        // No cache needed for update. Standard update.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->update(
            $i->table_name,
            array_merge(array(
                'updated_at' => $updated_at
            ), self::get_sql_array($post)),
            array('invoice_id' => $invoice_id)
        );
    }

    /**
     * Get SQL array for insert or update
     */
    private static function get_sql_array($post)
    {
        return array(
            'private_remarks' => isset($post['private_remarks']) ? $post['private_remarks'] : '',
            'amount_for_stripe' => isset($post['amount_for_stripe']) ? trim($post['amount_for_stripe']) : '',
            'currency_for_stripe' => isset($post['currency_for_stripe']) ? trim($post['currency_for_stripe']) : ''
        );
    }

    public static function get_adata($invoice_id)
    {
        global $wpdb;
        $i = new self();
        $i->init(); // set tables names

        // 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($wpdb->prepare("SELECT * FROM ".esc_sql($i->table_name)." WHERE invoice_id = %d LIMIT 1", $invoice_id), ARRAY_A);

        if (count($results) > 0) {
            return $results[0];
        }

        return false;
    }
}