<?php
/**
 * Request Model — CRUD over wpnest_rfw_requests + writes to wpnest_rfw_status_log.
 *
 * @package WPNest_Returns_For_WooCommerce
 */

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

class WPNest_RFW_Request_Model {

    const STATUS_PENDING  = 'pending';
    const STATUS_ACCEPTED = 'accepted';
    const STATUS_REJECTED = 'rejected';
    const STATUS_REFUNDED = 'refunded';

    public static function get_statuses() {
        return array(
            self::STATUS_PENDING  => __( 'Pending', 'wpnest-returns-for-woocommerce' ),
            self::STATUS_ACCEPTED => __( 'Accepted', 'wpnest-returns-for-woocommerce' ),
            self::STATUS_REJECTED => __( 'Rejected', 'wpnest-returns-for-woocommerce' ),
            self::STATUS_REFUNDED => __( 'Refunded', 'wpnest-returns-for-woocommerce' ),
        );
    }

    /**
     * Insert a new return request row.
     *
     * @param array $data {
     *     order_id (int, required), customer_id (int|null), order_key (string),
     *     reason (string), has_returned (bool), is_digital (bool), ip_hash (string)
     * }
     * @return int|false Inserted row ID or false on error.
     */
    public static function create( array $data ) {
        global $wpdb;

        $now    = current_time( 'mysql', true );
        $row    = array(
            'order_id'     => isset( $data['order_id'] ) ? (int) $data['order_id'] : 0,
            'customer_id'  => isset( $data['customer_id'] ) ? (int) $data['customer_id'] : null,
            'order_key'    => isset( $data['order_key'] ) ? substr( (string) $data['order_key'], 0, 64 ) : '',
            'status'       => self::STATUS_PENDING,
            'reason'       => isset( $data['reason'] ) ? (string) $data['reason'] : null,
            'has_returned' => ! empty( $data['has_returned'] ) ? 1 : 0,
            'is_digital'   => ! empty( $data['is_digital'] ) ? 1 : 0,
            'ip_hash'      => isset( $data['ip_hash'] ) ? substr( (string) $data['ip_hash'], 0, 64 ) : '',
            'created_at'   => $now,
            'updated_at'   => $now,
        );

        if ( $row['order_id'] <= 0 ) {
            return false;
        }

        $formats = array( '%d', '%d', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert.
        $inserted = $wpdb->insert( wpnest_rfw_requests_table(), $row, $formats );
        if ( false === $inserted ) {
            return false;
        }

        $request_id = (int) $wpdb->insert_id;
        self::write_log( $request_id, null, self::STATUS_PENDING, 0, '' );

        return $request_id;
    }

    /**
     * Fetch single request by ID.
     *
     * @param int $request_id
     * @return object|null
     */
    public static function get( $request_id ) {
        global $wpdb;
        $request_id = (int) $request_id;
        if ( $request_id <= 0 ) {
            return null;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table read.
        return $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE id = %d LIMIT 1',
                wpnest_rfw_requests_table(),
                $request_id
            )
        );
    }

    /**
     * Latest request for an order (any status).
     *
     * @param int $order_id
     * @return object|null
     */
    public static function get_by_order( $order_id ) {
        global $wpdb;
        $order_id = (int) $order_id;
        if ( $order_id <= 0 ) {
            return null;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table read.
        return $wpdb->get_row(
            $wpdb->prepare(
                'SELECT * FROM %i WHERE order_id = %d ORDER BY id DESC LIMIT 1',
                wpnest_rfw_requests_table(),
                $order_id
            )
        );
    }

    /**
     * Updates status, writes status_log row, fires action.
     *
     * @return bool
     */
    public static function update_status( $request_id, $new_status, $changed_by = 0, $note = '' ) {
        global $wpdb;

        $request = self::get( $request_id );
        if ( ! $request ) {
            return false;
        }

        $old_status = (string) $request->status;
        $new_status = sanitize_key( $new_status );
        if ( ! isset( self::get_statuses()[ $new_status ] ) ) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table update.
        $updated = $wpdb->update(
            wpnest_rfw_requests_table(),
            array(
                'status'     => $new_status,
                'updated_at' => current_time( 'mysql', true ),
            ),
            array( 'id' => (int) $request_id ),
            array( '%s', '%s' ),
            array( '%d' )
        );

        if ( false === $updated ) {
            return false;
        }

        self::write_log( (int) $request_id, $old_status, $new_status, (int) $changed_by, (string) $note );

        do_action( 'wpnest_rfw_status_changed', (int) $request_id, $old_status, $new_status );

        return true;
    }

    /**
     * Updates an arbitrary column on a request row.
     *
     * @param int    $request_id
     * @param string $column
     * @param mixed  $value
     * @param string $format  '%s', '%d', '%f'
     * @return bool
     */
    public static function update_column( $request_id, $column, $value, $format = '%s' ) {
        global $wpdb;
        $request_id = (int) $request_id;
        $column     = sanitize_key( $column );
        $allowed    = array( 'pdf_path', 'pdf_token', 'attachment_path', 'bank_account', 'deadline_at' );
        if ( $request_id <= 0 || ! in_array( $column, $allowed, true ) ) {
            return false;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table update.
        $updated = $wpdb->update(
            wpnest_rfw_requests_table(),
            array(
                $column      => $value,
                'updated_at' => current_time( 'mysql', true ),
            ),
            array( 'id' => $request_id ),
            array( $format, '%s' ),
            array( '%d' )
        );
        return false !== $updated;
    }

    /**
     * Filtered, paginated list.
     *
     * The dynamic WHERE clause is assembled by build_where() from a whitelisted set
     * of column references plus `%s`/`%d` placeholders only — never user input.
     * All values are bound through `$wpdb->prepare()` via the $params array.
     *
     * @param array $args { status, date_from, date_to, order_id, per_page (20), page (1) }
     * @return array of objects
     */
    public static function get_list( array $args = array() ) {
        global $wpdb;
        list( $where_sql, $where_params ) = self::build_where( $args );

        $per_page = isset( $args['per_page'] ) ? max( 1, (int) $args['per_page'] ) : 20;
        $page     = isset( $args['page'] ) ? max( 1, (int) $args['page'] ) : 1;
        $offset   = ( $page - 1 ) * $per_page;

        $params = array_merge( array( wpnest_rfw_requests_table() ), $where_params, array( $per_page, $offset ) );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $sql = $wpdb->prepare(
            "SELECT * FROM %i WHERE {$where_sql} ORDER BY id DESC LIMIT %d OFFSET %d",
            $params
        );
        return $wpdb->get_results( $sql );
        // phpcs:enable
    }

    /**
     * Total count for the same filter set as get_list().
     */
    public static function get_total( array $args = array() ) {
        global $wpdb;
        list( $where_sql, $where_params ) = self::build_where( $args );
        $params = array_merge( array( wpnest_rfw_requests_table() ), $where_params );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $sql = $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE {$where_sql}",
            $params
        );
        return (int) $wpdb->get_var( $sql );
        // phpcs:enable
    }

    /**
     * Builds WHERE clause + ordered parameter list (excluding the leading table name).
     *
     * Returns SQL fragment that contains ONLY hardcoded column names and `%s`/`%d`
     * placeholders — never raw user input — so the caller can pass the fragment to
     * $wpdb->prepare() with confidence.
     *
     * @return array{0:string,1:array}
     */
    private static function build_where( array $args ) {
        $clauses = array( '1=1' );
        $params  = array();

        if ( ! empty( $args['status'] ) ) {
            $clauses[] = 'status = %s';
            $params[]  = sanitize_key( $args['status'] );
        }
        if ( ! empty( $args['date_from'] ) ) {
            $clauses[] = 'created_at >= %s';
            $params[]  = gmdate( 'Y-m-d 00:00:00', strtotime( (string) $args['date_from'] ) );
        }
        if ( ! empty( $args['date_to'] ) ) {
            $clauses[] = 'created_at <= %s';
            $params[]  = gmdate( 'Y-m-d 23:59:59', strtotime( (string) $args['date_to'] ) );
        }
        if ( ! empty( $args['order_id'] ) ) {
            $clauses[] = 'order_id = %d';
            $params[]  = (int) $args['order_id'];
        }

        return array( implode( ' AND ', $clauses ), $params );
    }

    /**
     * Hard-delete all rows from all tables — used only by uninstall.php.
     *
     * @return bool
     */
    public static function delete_all() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Uninstall cleanup.
        $a = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', wpnest_rfw_requests_table() ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Uninstall cleanup.
        $b = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', wpnest_rfw_status_log_table() ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Uninstall cleanup.
        $c = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', wpnest_rfw_request_items_table() ) );
        return false !== $a && false !== $b && false !== $c;
    }

    /**
     * Status log writer (private).
     */
    private static function write_log( $request_id, $old_status, $new_status, $changed_by, $note ) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table insert.
        $wpdb->insert(
            wpnest_rfw_status_log_table(),
            array(
                'request_id' => (int) $request_id,
                'old_status' => $old_status ? (string) $old_status : null,
                'new_status' => (string) $new_status,
                'changed_by' => $changed_by ? (int) $changed_by : null,
                'note'       => '' !== $note ? (string) $note : null,
                'changed_at' => current_time( 'mysql', true ),
            ),
            array( '%d', '%s', '%s', '%d', '%s', '%s' )
        );
    }
}
