<?php

namespace StoreSuit;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Messages are returned as JSON by Admin::ajax() and shown with textContent; HTML output escapes them where printed.
/** Rebuildable per-order reporting facts; WooCommerce remains the source of truth. */
final class DashboardFacts {
    private const SCHEMA = 'storesuit_dashboard_facts_schema';

    private const READY = 'storesuit_dashboard_facts_ready';

    private const CURSOR = 'storesuit_dashboard_facts_cursor';

    private const BATCH = 120;

    /**
     * '1' once the table has the exchanged column (persona fixes REP-1): the part of an order's refunds that an exchange
     * passed on to its replacement order as credit, not money paid back. Added to the table of schema 1 in place (the schema
     * option keeps '1', which other screens check); the facts are then read again (sync()), so the dashboard's period has it.
     * Until it is there, rows are written without it and the exchanged figures are 0, so a failed ALTER never breaks a write.
     */
    private const COLUMNS = 'storesuit_dashboard_facts_columns';

    private static ?bool $exchanged_exists = null;

    public static function table() : string {
        global $wpdb;
        return $wpdb->prefix . 'storesuit_dashboard_facts';
    }

    /** Whether the facts table has the exchanged column (REP-1). */
    public static function has_exchanged() : bool {
        if ( get_option( self::COLUMNS ) !== '1' ) {
            self::$exchanged_exists = null;
            return false;
        }
        if ( self::$exchanged_exists === null ) {
            // Found within the last ten minutes, in a persistent object cache only (as OrderIndex::VERIFIED): a page view
            // no longer reads the table's columns each time.
            $kept = wp_using_ext_object_cache();
            if ( $kept && wp_cache_get( 'storesuit_facts_columns', 'storesuit' ) === self::table() ) {
                return self::$exchanged_exists = true;
            }
            global $wpdb;
            self::$exchanged_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', self::table(), 'exchanged' ) ) === 'exchanged';
            if ( !self::$exchanged_exists ) {
                delete_option( self::COLUMNS );
                wp_cache_delete( 'storesuit_facts_columns', 'storesuit' );
            } elseif ( $kept ) {
                wp_cache_set(
                    'storesuit_facts_columns',
                    self::table(),
                    'storesuit',
                    10 * MINUTE_IN_SECONDS
                );
            }
        }
        return self::$exchanged_exists;
    }

    public static function install() : void {
        $schema = get_option( self::SCHEMA ) === '1';
        self::$exchanged_exists = null;
        // A migration/restore may have changed the physical table within this request.
        if ( $schema && (!wp_using_ext_object_cache() && get_option( self::COLUMNS ) === '1' && !Store::upkeep_request() || self::has_exchanged()) ) {
            return;
        }
        // A shopper's page view with no persistent object cache reads no columns, as OrderIndex::install().
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $table = self::table();
        $charset = $wpdb->get_charset_collate();
        dbDelta( "CREATE TABLE {$table} (\n            order_id bigint(20) unsigned NOT NULL,\n            created_at datetime NOT NULL,\n            currency varchar(8) NOT NULL,\n            status varchar(32) NOT NULL,\n            channel varchar(32) NOT NULL,\n            gross decimal(26,8) NOT NULL DEFAULT 0,\n            refunds decimal(26,8) NOT NULL DEFAULT 0,\n            exchanged decimal(26,8) NOT NULL DEFAULT 0,\n            items_json longtext NOT NULL,\n            PRIMARY KEY  (order_id),\n            KEY period (currency,created_at)\n        ) {$charset};" );
        if ( !$schema && $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'items_json' ) ) === 'items_json' ) {
            update_option( self::SCHEMA, '1', false );
            update_option( self::READY, '0', false );
            delete_option( self::CURSOR );
        }
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'exchanged' ) ) === 'exchanged' ) {
            // Autoloaded: install() reads it on every request (OrderIndex::hooks()).
            update_option( self::COLUMNS, '1', true );
            self::$exchanged_exists = true;
            // Rows written before the column say 0: the period's rows are read again, as on a first build.
            if ( $schema ) {
                update_option( self::READY, '0', false );
                delete_option( self::CURSOR );
            }
        }
    }

    /**
     * What exchanges on this order passed to their replacement orders (Returns: a return with status 'exchanged' keeps the
     * credit it moved as 'transferred'). That part of the refunds was never paid back; never more than the refunds.
     */
    public static function exchanged_of( \WC_Order $order, float $refunds ) : float {
        $sum = 0.0;
        foreach ( (array) (( $order->get_meta( '_storesuit_returns' ) ?: array() )) as $r ) {
            if ( is_array( $r ) && ($r['status'] ?? '') === 'exchanged' && is_numeric( $r['transferred'] ?? null ) ) {
                $sum += max( 0.0, (float) $r['transferred'] );
            }
        }
        return max( 0.0, min( $sum, $refunds ) );
    }

    /**
     * The exchanged part of the refunds of a period and of its previous period, over the same orders as
     * DashboardTotals::sales() (the period's facts, sales statuses only). 0 until the column exists.
     */
    public static function exchanged(
        string $prior,
        string $from,
        string $end,
        string $currency
    ) : array {
        $out = array(
            'current'  => 0.0,
            'previous' => 0.0,
        );
        if ( !self::has_exchanged() ) {
            return $out;
        }
        global $wpdb;
        foreach ( DashboardTotals::rows( $wpdb->get_results( $wpdb->prepare( 'SELECT CASE WHEN f.created_at<%s THEN 0 ELSE 1 END period,SUM(f.exchanged) exchanged FROM %i f INNER JOIN %i i ON i.order_id=f.order_id WHERE f.created_at BETWEEN %s AND %s AND f.currency=%s AND f.status NOT IN (' . implode( ',', array_fill( 0, count( SaleStatus::NOT_SALES ), '%s' ) ) . ') GROUP BY CASE WHEN f.created_at<%s THEN 0 ELSE 1 END', array_merge( array(
            $from . ' 00:00:00',
            self::table(),
            OrderIndex::table(),
            $prior . ' 00:00:00',
            $end . ' 23:59:59',
            $currency
        ), SaleStatus::NOT_SALES, array($from . ' 00:00:00') ) ), ARRAY_A ) ) as $row ) {
            $out[( (int) $row['period'] === 1 ? 'current' : 'previous' )] = (float) $row['exchanged'];
        }
        return $out;
    }

    private static function money( $amount ) : string {
        return sprintf( '%.8F', (float) $amount );
    }

    public static function changed( \WC_Order $order ) : bool {
        if ( get_option( self::SCHEMA ) !== '1' ) {
            return false;
        }
        if ( $order->get_type() !== 'shop_order' || in_array( $order->get_status(), array('trash', 'auto-draft'), true ) ) {
            self::remove( $order->get_id() );
            return true;
        }
        // A receiptless return is no sale: its value is a refund and its units come off the units sold, under a status of its
        // own (0.62.9). A cancelled one is no refund either. Receiptless returns are the paid build's.
        $return = false;
        $status = $order->get_status();
        $lines = array();
        foreach ( $order->get_items( 'line_item' ) as $item_id => $item ) {
            $product_id = ( $item->get_variation_id() ?: $item->get_product_id() );
            $lines[] = array(
                'id'   => (int) $product_id,
                'name' => (string) $item->get_name(),
                'qty'  => ( $return ? -(float) $item->get_quantity() : max( 0, (float) $item->get_quantity() - abs( $order->get_qty_refunded_for_item( $item_id ) ) ) ),
                'net'  => ( $return ? -(float) $item->get_total() : (float) $item->get_total() - abs( (float) $order->get_total_refunded_for_item( $item_id ) ) ),
            );
        }
        $json = wp_json_encode( $lines );
        if ( false === $json ) {
            update_option( self::READY, '0', false );
            return false;
        }
        $channel = Orders::channel( $order );
        global $wpdb;
        $refunds = (float) (( $return ? $order->get_total() : $order->get_total_refunded() ));
        $row = array(
            'order_id'   => $order->get_id(),
            'created_at' => ( $order->get_date_created() ? $order->get_date_created()->date( 'Y-m-d H:i:s' ) : gmdate( 'Y-m-d H:i:s' ) ),
            'currency'   => $order->get_currency(),
            'status'     => $status,
            'channel'    => $channel,
            'gross'      => self::money( ( $return ? 0 : $order->get_total() ) ),
            'refunds'    => self::money( $refunds ),
            'items_json' => $json,
        );
        $format = array(
            '%d',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s'
        );
        // An exchange's refund is credit for the replacement order, not money back (REP-1); a receiptless return has none.
        if ( self::has_exchanged() ) {
            $row['exchanged'] = self::money( ( $return ? 0 : self::exchanged_of( $order, $refunds ) ) );
            $format[] = '%s';
        }
        $written = $wpdb->replace( self::table(), $row, $format );
        if ( false === $written ) {
            update_option( self::READY, '0', false );
            return false;
        }
        return true;
    }

    public static function remove( int $order_id ) : void {
        if ( get_option( self::SCHEMA ) !== '1' ) {
            return;
        }
        global $wpdb;
        if ( false === $wpdb->delete( self::table(), array(
            'order_id' => $order_id,
        ), array('%d') ) ) {
            update_option( self::READY, '0', false );
        }
    }

    /**
     * A small, repeatable read-model backfill; no order or payment is changed. It fails closed: a read that did not run
     * (a killed or failed SELECT returns no rows and a NULL count) is an error, never "nothing left", so the facts are
     * marked ready only after counts that really ran (8.30 dashboard review I3).
     */
    public static function sync() : array {
        if ( get_option( self::SCHEMA ) !== '1' ) {
            throw new \RuntimeException(I18n::t( 'The reporting index could not be prepared. No sales records were changed.' ));
        }
        if ( get_option( self::READY ) === '1' ) {
            return array(
                'ready' => true,
            );
        }
        if ( get_option( 'storesuit_order_index_ready' ) !== '1' ) {
            throw new \RuntimeException(I18n::t( 'The order index is being prepared. Refresh in a moment.' ));
        }
        global $wpdb;
        $failed = static fn() => new \RuntimeException(I18n::t( 'The reporting index could not be prepared. No sales records were changed.' ));
        $cursor = absint( get_option( self::CURSOR, 0 ) );
        $index = OrderIndex::table();
        // The widest dashboard period is 90 days plus its previous 90-day comparison.
        $cutoff = ( new \DateTimeImmutable('today', wp_timezone()) )->modify( '-181 days' )->format( 'Y-m-d 00:00:00' );
        $ids = $wpdb->get_col( $wpdb->prepare(
            'SELECT order_id FROM %i WHERE created_at>=%s AND order_id>%d ORDER BY order_id ASC LIMIT %d',
            $index,
            $cutoff,
            $cursor,
            self::BATCH
        ) );
        if ( $wpdb->last_error !== '' || !is_array( $ids ) ) {
            throw $failed();
        }
        foreach ( $ids as $id ) {
            $order = wc_get_order( $id );
            if ( !$order instanceof \WC_Order || $order->get_status() === 'trash' ) {
                OrderIndex::remove( (int) $id );
            } elseif ( !self::changed( $order ) ) {
                throw $failed();
            }
            $cursor = max( $cursor, (int) $id );
        }
        if ( $ids ) {
            update_option( self::CURSOR, max( $cursor, absint( get_option( self::CURSOR, 0 ) ) ), false );
        }
        // A successful COUNT(*) is never NULL: NULL or an error means the count did not run.
        $total = $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at>=%s', $index, $cutoff ) );
        if ( $wpdb->last_error !== '' || $total === null ) {
            throw $failed();
        }
        $remaining = $wpdb->get_var( $wpdb->prepare(
            'SELECT COUNT(*) FROM %i WHERE created_at>=%s AND order_id>%d',
            $index,
            $cutoff,
            $cursor
        ) );
        if ( $wpdb->last_error !== '' || $remaining === null ) {
            throw $failed();
        }
        $total = (int) $total;
        $remaining = (int) $remaining;
        if ( $remaining === 0 ) {
            $missing = $wpdb->get_var( $wpdb->prepare(
                'SELECT COUNT(*) FROM %i i LEFT JOIN %i f ON f.order_id=i.order_id WHERE i.created_at>=%s AND f.order_id IS NULL',
                $index,
                self::table(),
                $cutoff
            ) );
            if ( $wpdb->last_error !== '' || $missing === null ) {
                throw $failed();
            }
            if ( (int) $missing ) {
                update_option( self::CURSOR, 0, false );
                return array(
                    'ready' => false,
                    'done'  => 0,
                    'total' => $total,
                );
            }
            update_option( self::READY, '1', false );
            delete_option( self::CURSOR );
            return array(
                'ready' => true,
            );
        }
        return array(
            'ready' => false,
            'done'  => max( 0, $total - $remaining ),
            'total' => $total,
        );
    }

}