<?php
/**
 * Bundle Model for BoostBot.
 *
 * Handles CRUD operations for AI-generated product bundles.
 *
 * @package BoostBot
 * @since   1.0.0
 */

// Prevent direct access.
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Bundle_Model
 *
 * Model for managing product bundles created by the Bundle Builder widget.
 *
 * @since 1.0.0
 */
class BoostBot_Bundle_Model extends BoostBot_Base_Model {

    use BoostBot_Localizable;

    /**
     * Table name without prefix.
     *
     * @var string
     */
    protected $table_name;

    /**
     * Fillable columns.
     *
     * @var array
     */
    protected $fillable = array(
        'conversation_id',
        'chatbot_id',
        'widget_id',
        'language',
        'customer_criteria',
        'products',
        'total_price',
        'original_price',
        'discount_amount',
        'discount_percent',
        'status',
        'converted_at',
    );

    /**
     * Column types for proper escaping.
     *
     * @var array
     */
    protected $column_types = array(
        'id'               => 'bigint',
        'chatbot_id'       => 'bigint',
        'widget_id'        => 'bigint',
        'total_price'      => 'decimal',
        'original_price'   => 'decimal',
        'discount_amount'  => 'decimal',
        'discount_percent' => 'decimal',
    );

    /**
     * Bundle status constants.
     */
    const STATUS_SUGGESTED  = 'suggested';
    const STATUS_ACCEPTED   = 'accepted';
    const STATUS_CUSTOMIZED = 'customized';
    const STATUS_REJECTED   = 'rejected';
    const STATUS_PURCHASED  = 'purchased';

    /**
     * Constructor.
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bundles' );
        parent::__construct();
    }

    /**
     * Create a new bundle.
     *
     * @since 1.0.0
     * @param array $data Bundle data.
     * @return int|false Bundle ID or false on failure.
     */
    public function create( $data ) {
        // Encode JSON fields.
        if ( isset( $data['customer_criteria'] ) && is_array( $data['customer_criteria'] ) ) {
            $data['customer_criteria'] = wp_json_encode( $data['customer_criteria'] );
        }

        if ( isset( $data['products'] ) && is_array( $data['products'] ) ) {
            $data['products'] = wp_json_encode( $data['products'] );
        }

        return parent::create( $data );
    }

    /**
     * Update a bundle.
     *
     * @since 1.0.0
     * @param int   $id   Bundle ID.
     * @param array $data Data to update.
     * @return bool True on success.
     */
    public function update( $id, $data ) {
        // Encode JSON fields if they are arrays.
        if ( isset( $data['customer_criteria'] ) && is_array( $data['customer_criteria'] ) ) {
            $data['customer_criteria'] = wp_json_encode( $data['customer_criteria'] );
        }

        if ( isset( $data['products'] ) && is_array( $data['products'] ) ) {
            $data['products'] = wp_json_encode( $data['products'] );
        }

        return parent::update( $id, $data );
    }

    /**
     * Get bundle with decoded products.
     *
     * @since 1.0.0
     * @param int $id Bundle ID.
     * @return object|null Bundle object with decoded JSON fields.
     */
    public function get_with_products( int $id ): ?object {
        $bundle = $this->get( $id );

        if ( $bundle ) {
            $bundle->products          = json_decode( $bundle->products, true ) ?? array();
            $bundle->customer_criteria = json_decode( $bundle->customer_criteria, true ) ?? array();
        }

        return $bundle;
    }

    /**
     * Update bundle status.
     *
     * @since 1.0.0
     * @param int    $id     Bundle ID.
     * @param string $status New status.
     * @return bool True on success.
     */
    public function update_status( int $id, string $status ): bool {
        $data = array( 'status' => $status );

        if ( self::STATUS_PURCHASED === $status ) {
            $data['converted_at'] = current_time( 'mysql' );
        }

        return $this->update( $id, $data );
    }

    /**
     * Get bundles by conversation ID.
     *
     * @since 1.0.0
     * @param string $conversation_id Conversation ID.
     * @return array Array of bundle objects.
     */
    public function get_by_conversation( string $conversation_id ): array {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $bundles = $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE conversation_id = %s ORDER BY created_at DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $conversation_id
            )
        );

        // Decode JSON fields.
        foreach ( $bundles as $bundle ) {
            $bundle->products          = json_decode( $bundle->products, true ) ?? array();
            $bundle->customer_criteria = json_decode( $bundle->customer_criteria, true ) ?? array();
        }

        return $bundles;
    }

    /**
     * Get bundles by chatbot ID.
     *
     * @since 1.0.0
     * @param int   $chatbot_id Chatbot ID.
     * @param int   $limit      Number of bundles to return.
     * @param int   $offset     Offset for pagination.
     * @return array Array of bundle objects.
     */
    public function get_by_chatbot( int $chatbot_id, int $limit = 50, int $offset = 0 ): array {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $bundles = $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE chatbot_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $chatbot_id,
                $limit,
                $offset
            )
        );

        // Decode JSON fields.
        foreach ( $bundles as $bundle ) {
            $bundle->products          = json_decode( $bundle->products, true ) ?? array();
            $bundle->customer_criteria = json_decode( $bundle->customer_criteria, true ) ?? array();
        }

        return $bundles;
    }

    /**
     * Get bundle analytics.
     *
     * @since 1.0.0
     * @param int $days Number of days to analyze.
     * @return array Analytics data.
     */
    public function get_analytics( int $days = 30 ): array {
        $since = gmdate( 'Y-m-d', strtotime( "-{$days} days" ) );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $this->wpdb->prepare(
                "SELECT 
                    COUNT(*) as total_bundles,
                    SUM(CASE WHEN status IN ('accepted', 'purchased') THEN 1 ELSE 0 END) as accepted,
                    SUM(CASE WHEN status = 'purchased' THEN 1 ELSE 0 END) as purchased,
                    AVG(total_price) as avg_bundle_value,
                    AVG(discount_percent) as avg_discount,
                    SUM(CASE WHEN status = 'purchased' THEN total_price ELSE 0 END) as total_revenue
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                FROM {$this->table_name}
                WHERE created_at >= %s",
                $since
            ),
            ARRAY_A
        );

        if ( ! $result ) {
            return array(
                'total_bundles'    => 0,
                'accepted'         => 0,
                'purchased'        => 0,
                'avg_bundle_value' => 0,
                'avg_discount'     => 0,
                'total_revenue'    => 0,
                'conversion_rate'  => 0,
            );
        }

        // Calculate conversion rate.
        $result['conversion_rate'] = 0;
        if ( (int) $result['total_bundles'] > 0 ) {
            $result['conversion_rate'] = round(
                ( (int) $result['purchased'] / (int) $result['total_bundles'] ) * 100,
                1
            );
        }

        return $result;
    }

    /**
     * Get recent bundles.
     *
     * @since 1.0.0
     * @param int    $limit  Number of bundles to return.
     * @param string $status Optional status filter.
     * @return array Array of bundle objects.
     */
    public function get_recent( int $limit = 10, string $status = '' ): array {
        $sql = "SELECT * FROM {$this->table_name}"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( ! empty( $status ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql .= $this->wpdb->prepare( ' WHERE status = %s', $status );
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $sql .= $this->wpdb->prepare( ' ORDER BY created_at DESC LIMIT %d', $limit );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $bundles = $this->wpdb->get_results( $sql );

        // Decode JSON fields.
        foreach ( $bundles as $bundle ) {
            $bundle->products          = json_decode( $bundle->products, true ) ?? array();
            $bundle->customer_criteria = json_decode( $bundle->customer_criteria, true ) ?? array();
        }

        return $bundles;
    }

    /**
     * Validate bundle data.
     *
     * @since 1.0.0
     * @param array    $data Bundle data.
     * @param int|null $id   Bundle ID for updates.
     * @return true|WP_Error True if valid.
     */
    public function validate( $data, $id = null ) {
        $errors = new WP_Error();

        // Products are required for new bundles.
        if ( null === $id && empty( $data['products'] ) ) {
            $errors->add( 'missing_products', __( 'Products are required', 'boostbot' ) );
        }

        // Total price is required for new bundles.
        if ( null === $id && ! isset( $data['total_price'] ) ) {
            $errors->add( 'missing_price', __( 'Total price is required', 'boostbot' ) );
        }

        // Validate status if provided.
        $valid_statuses = array(
            self::STATUS_SUGGESTED,
            self::STATUS_ACCEPTED,
            self::STATUS_CUSTOMIZED,
            self::STATUS_REJECTED,
            self::STATUS_PURCHASED,
        );

        if ( ! empty( $data['status'] ) && ! in_array( $data['status'], $valid_statuses, true ) ) {
            $errors->add( 'invalid_status', __( 'Invalid bundle status', 'boostbot' ) );
        }

        if ( $errors->has_errors() ) {
            return $errors;
        }

        return true;
    }

    /**
     * Sanitize bundle data.
     *
     * @since 1.0.0
     * @param array $data Bundle data.
     * @return array Sanitized data.
     */
    public function sanitize( $data ) {
        $sanitized = array();

        if ( isset( $data['conversation_id'] ) ) {
            $sanitized['conversation_id'] = sanitize_text_field( $data['conversation_id'] );
        }

        if ( isset( $data['chatbot_id'] ) ) {
            $sanitized['chatbot_id'] = absint( $data['chatbot_id'] );
        }

        if ( isset( $data['widget_id'] ) ) {
            $sanitized['widget_id'] = absint( $data['widget_id'] );
        }

        if ( isset( $data['language'] ) ) {
            $sanitized['language'] = sanitize_text_field( $data['language'] );
        }

        if ( isset( $data['customer_criteria'] ) ) {
            // Already JSON encoded or will be.
            $sanitized['customer_criteria'] = $data['customer_criteria'];
        }

        if ( isset( $data['products'] ) ) {
            // Already JSON encoded or will be.
            $sanitized['products'] = $data['products'];
        }

        if ( isset( $data['total_price'] ) ) {
            $sanitized['total_price'] = floatval( $data['total_price'] );
        }

        if ( isset( $data['original_price'] ) ) {
            $sanitized['original_price'] = floatval( $data['original_price'] );
        }

        if ( isset( $data['discount_amount'] ) ) {
            $sanitized['discount_amount'] = floatval( $data['discount_amount'] );
        }

        if ( isset( $data['discount_percent'] ) ) {
            $sanitized['discount_percent'] = floatval( $data['discount_percent'] );
        }

        if ( isset( $data['status'] ) ) {
            $sanitized['status'] = sanitize_text_field( $data['status'] );
        }

        if ( isset( $data['converted_at'] ) ) {
            $sanitized['converted_at'] = sanitize_text_field( $data['converted_at'] );
        }

        return $sanitized;
    }
}
