<?php
/**
 * Base Model class for database operations.
 *
 * @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_Base_Model
 *
 * Abstract base class providing common CRUD operations
 * for all database models.
 *
 * @since 1.0.0
 */
abstract class BoostBot_Base_Model {

    /**
     * WordPress database object.
     *
     * @var wpdb
     */
    protected $wpdb;

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

    /**
     * Primary key column name.
     *
     * @var string
     */
    protected $primary_key = 'id';

    /**
     * Fillable columns (allowed for mass assignment).
     *
     * @var array
     */
    protected $fillable = array();

    /**
     * Column types for proper escaping.
     *
     * @var array
     */
    protected $column_types = array();

    /**
     * Cached table columns for the current model table.
     *
     * @var array|null
     */
    protected $table_columns = null;

    /**
     * Constructor.
     */
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
    }

    /**
     * Get a single record by ID.
     *
     * @param int $id The record ID.
     * @return object|null The record or null if not found.
     */
    public function get( $id ) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        return $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT * FROM {$this->table_name} WHERE {$this->primary_key} = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $id
            )
        );
    }

    /**
     * Get all records with optional filtering.
     *
     * @param array $args Optional. Query arguments.
     * @return array Array of records.
     */
    public function get_all( $args = array() ) {
        $defaults = array(
            'where'    => array(),
            'orderby'  => $this->primary_key,
            'order'    => 'DESC',
            'limit'    => 50,
            'offset'   => 0,
        );

        $args = wp_parse_args( $args, $defaults );

        // Build query
        $sql = "SELECT * FROM {$this->table_name}"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Add WHERE clause
        $where_parts = array();
        $values      = array();

        foreach ( $args['where'] as $column => $value ) {
            if ( ! in_array( $column, $this->fillable, true ) && $column !== $this->primary_key ) {
                continue;
            }

            if ( 'language' === $column ) {
                $value = class_exists( 'BoostBot_Language' ) ? BoostBot_Language::sanitize( $value ) : $value;
                if ( '' === $value || null === $value ) {
                    continue;
                }
            }

            $format = $this->get_column_format( $column );
            $where_parts[] = "{$column} = {$format}";
            $values[]      = $value;
        }

        if ( ! empty( $where_parts ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql .= ' WHERE ' . implode( ' AND ', $where_parts );
        }

        // Add ORDER BY. Validate the column against an allow-list (fillable
        // columns + primary key) before interpolation to prevent SQL injection
        // via a caller-supplied orderby; fall back to the primary key otherwise.
        $allowed_orderby = array_merge( $this->fillable, array( $this->primary_key ) );
        $orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : $this->primary_key;
        $allowed_order   = array( 'ASC', 'DESC' );
        $order           = in_array( strtoupper( $args['order'] ), $allowed_order, true ) ? strtoupper( $args['order'] ) : 'DESC';
        $sql            .= " ORDER BY {$orderby} {$order}"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Add LIMIT and OFFSET
        $sql    .= ' LIMIT %d OFFSET %d';
        $values[] = absint( $args['limit'] );
        $values[] = absint( $args['offset'] );

        // Execute query
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        return $this->wpdb->get_results(
            $this->wpdb->prepare( $sql, $values ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        );
    }

    /**
     * Create a new record.
     *
     * @param array $data The data to insert.
     * @return int|false The new record ID or false on failure.
     */
    public function create( $data ) {
        // Sanitize data
        $data = $this->sanitize( $data );

        // Validate data
        $validation = $this->validate( $data );
        if ( is_wp_error( $validation ) ) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG && class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::error( 'Validation failed on create', array(
                    'errors' => $validation->get_error_messages(),
                ) );
            }
            return false;
        }

        // Language validation for Localizable models (strict for admin entities)
        if ( method_exists( $this, 'validate_language_on_write' ) && isset( $data['language'] ) ) {
            $lang_check = $this->validate_language_on_write( $data );
            if ( is_wp_error( $lang_check ) ) {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::warning( 'Language validation failed on create', array(
                        'table' => $this->table_name,
                        'language' => $data['language'],
                        'error' => $lang_check->get_error_message(),
                    ) );
                }
                return false;
            }
        }

        // Filter to fillable columns only
        $data = $this->filter_fillable( $data );

        // Build format array
        $formats = array();
        foreach ( array_keys( $data ) as $column ) {
            $formats[] = $this->get_column_format( $column );
        }

        // Insert
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $this->wpdb->insert( $this->table_name, $data, $formats );

        if ( false === $result ) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG && class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::error( 'Database insert failed', array(
                    'table' => $this->table_name,
                    'error' => $this->wpdb->last_error,
                ) );
            }
            return false;
        }

        return $this->wpdb->insert_id;
    }

    /**
     * Update an existing record.
     *
     * @param int   $id   The record ID.
     * @param array $data The data to update.
     * @return bool True on success, false on failure.
     */
    public function update( $id, $data ) {
        // Sanitize data
        $data = $this->sanitize( $data );

        // Validate data
        $validation = $this->validate( $data, $id );
        if ( is_wp_error( $validation ) ) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG && class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::error( 'Validation failed on update', array(
                    'id'     => $id,
                    'errors' => $validation->get_error_messages(),
                ) );
            }
            return false;
        }

        // Language validation for Localizable models (strict for admin entities)
        if ( method_exists( $this, 'validate_language_on_write' ) && isset( $data['language'] ) ) {
            $lang_check = $this->validate_language_on_write( $data );
            if ( is_wp_error( $lang_check ) ) {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::warning( 'Language validation failed on update', array(
                        'table' => $this->table_name,
                        'id' => $id,
                        'language' => $data['language'],
                        'error' => $lang_check->get_error_message(),
                    ) );
                }
                return false;
            }
        }

        // Filter to fillable columns only
        $data = $this->filter_fillable( $data );

        // Build format array
        $formats = array();
        foreach ( array_keys( $data ) as $column ) {
            $formats[] = $this->get_column_format( $column );
        }

        // Update
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $this->wpdb->update(
            $this->table_name,
            $data,
            array( $this->primary_key => $id ),
            $formats,
            array( '%d' )
        );

        return false !== $result;
    }

    /**
     * Delete a record.
     *
     * @param int $id The record ID.
     * @return bool True on success, false on failure.
     */
    public function delete( $id ) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $this->wpdb->delete(
            $this->table_name,
            array( $this->primary_key => $id ),
            array( '%d' )
        );

        return false !== $result;
    }

    /**
     * Count records with optional filtering.
     *
     * @param array $where Optional. WHERE conditions.
     * @return int The count.
     */
    public function count( $where = array() ) {
        $sql    = "SELECT COUNT(*) FROM {$this->table_name}"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $values = array();

        // Add WHERE clause
        $where_parts = array();
        foreach ( $where as $column => $value ) {
            if ( ! in_array( $column, $this->fillable, true ) && $column !== $this->primary_key ) {
                continue;
            }

            if ( 'language' === $column ) {
                $value = class_exists( 'BoostBot_Language' ) ? BoostBot_Language::sanitize( $value ) : $value;
                if ( '' === $value || null === $value ) {
                    continue;
                }
            }

            $format        = $this->get_column_format( $column );
            $where_parts[] = "{$column} = {$format}";
            $values[]      = $value;
        }

        if ( ! empty( $where_parts ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $sql .= ' WHERE ' . implode( ' AND ', $where_parts );
        }

        // Execute query
        if ( empty( $values ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            return (int) $this->wpdb->get_var( $sql );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        return (int) $this->wpdb->get_var(
            $this->wpdb->prepare( $sql, $values ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        );
    }

    /**
     * Check if a record exists.
     *
     * @param int $id The record ID.
     * @return bool
     */
    public function exists( $id ) {
        return null !== $this->get( $id );
    }

    /**
     * Get the format string for a column.
     *
     * @param string $column The column name.
     * @return string The format string (%s, %d, or %f).
     */
    protected function get_column_format( $column ) {
        if ( isset( $this->column_types[ $column ] ) ) {
            switch ( $this->column_types[ $column ] ) {
                case 'int':
                case 'integer':
                case 'bigint':
                case 'tinyint':
                    return '%d';
                case 'float':
                case 'decimal':
                case 'double':
                    return '%f';
                default:
                    return '%s';
            }
        }

        // Default to string
        return '%s';
    }

    /**
     * Filter data to only include fillable columns.
     *
     * @param array $data The data to filter.
     * @return array The filtered data.
     */
    protected function filter_fillable( $data ) {
        $filtered = array_intersect_key( $data, array_flip( $this->fillable ) );

        // Defensive guard: in some installs, code may include newer fillable fields
        // before DB schema migrations/columns are present. Drop unknown columns to
        // prevent generic SQL failures on update/create.
        $table_columns = $this->get_table_columns();
        if ( ! empty( $table_columns ) ) {
            $filtered_by_schema = array_intersect_key( $filtered, array_flip( $table_columns ) );

            if ( count( $filtered_by_schema ) !== count( $filtered ) && class_exists( 'BoostBot_Logger' ) ) {
                $dropped = array_values( array_diff( array_keys( $filtered ), array_keys( $filtered_by_schema ) ) );
                if ( ! empty( $dropped ) ) {
                    BoostBot_Logger::debug( 'Dropped unknown model fields not present in DB schema', array(
                        'table'          => $this->table_name,
                        'dropped_fields' => $dropped,
                    ) );
                }
            }

            return $filtered_by_schema;
        }

        return $filtered;
    }

    /**
     * Get table columns for this model (cached per request).
     *
     * @return array
     */
    protected function get_table_columns() {
        if ( is_array( $this->table_columns ) ) {
            return $this->table_columns;
        }

        if ( empty( $this->table_name ) ) {
            $this->table_columns = array();
            return $this->table_columns;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $this->wpdb->get_results( "SHOW COLUMNS FROM {$this->table_name}", ARRAY_A );
        if ( ! is_array( $rows ) ) {
            $this->table_columns = array();
            return $this->table_columns;
        }

        $columns = array();
        foreach ( $rows as $row ) {
            if ( ! empty( $row['Field'] ) ) {
                $columns[] = (string) $row['Field'];
            }
        }

        $this->table_columns = $columns;
        return $this->table_columns;
    }

    /**
     * Validate data before insert/update.
     *
     * Override in child classes for custom validation.
     *
     * @param array    $data The data to validate.
     * @param int|null $id   Optional. The record ID for updates.
     * @return true|WP_Error True if valid, WP_Error otherwise.
     */
    abstract public function validate( $data, $id = null );

    /**
     * Sanitize data before insert/update.
     *
     * Override in child classes for custom sanitization.
     *
     * @param array $data The data to sanitize.
     * @return array The sanitized data.
     */
    abstract public function sanitize( $data );

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    protected static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}







