<?php
/**
 * EPPRO_DB
 *
 * Manages all custom database tables for Essential Pro Pack.
 *
 * Tables:
 *   {prefix}eppro_form_entries  — stores every form submission
 *
 * Usage:
 *   EPPRO_DB::install();          // create / upgrade tables
 *   EPPRO_DB::table('form_entries'); // returns full table name
 *
 * @package AdMoreWidgetsSuite
 * @since   1.0.0
 */

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

// This class is the data layer for the plugin's own {prefix}eppro_form_entries
// table, which has no core API. Every query is prepared; reads are cached with
// wp_cache_* and invalidated on each write (see flush_list_cache()). The DROP in
// uninstall() only runs from uninstall.php.
// phpcs:disable WordPress.DB.DirectDatabaseQuery

final class EPPRO_DB {

    /** DB schema version stored in wp_options. */
    const DB_VERSION     = '1.2.0';
    const DB_VERSION_KEY = 'eppro_db_version';

    // -------------------------------------------------------------------------
    // Public API
    // -------------------------------------------------------------------------

    /**
     * Create or upgrade all plugin tables.
     * Safe to call multiple times — dbDelta handles no-ops.
     */
    public static function install(): void {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset = $wpdb->get_charset_collate();

        // -- eppro_form_entries -------------------------------------------------
        $table = self::table( 'form_entries' );
        $sql   = "CREATE TABLE {$table} (
            id            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
            form_id       VARCHAR(100)     NOT NULL DEFAULT '',
            form_name     VARCHAR(200)     NOT NULL DEFAULT '',
            page_id       BIGINT UNSIGNED  NOT NULL DEFAULT 0,
            page_url      VARCHAR(500)     NOT NULL DEFAULT '',
            fields        LONGTEXT         NOT NULL,
            ip_address    VARCHAR(45)      NOT NULL DEFAULT '',
            user_agent    VARCHAR(500)     NOT NULL DEFAULT '',
            status        VARCHAR(20)      NOT NULL DEFAULT 'unread',
            submitted_at  DATETIME         NOT NULL,
            PRIMARY KEY  (id),
            KEY form_id  (form_id(50)),
            KEY status   (status),
            KEY submitted_at (submitted_at)
        ) {$charset};";

        dbDelta( $sql );

        update_option( self::DB_VERSION_KEY, self::DB_VERSION );
    }

    /**
     * Drop all plugin tables and options.
     * Called only from uninstall.php.
     */
    public static function uninstall(): void {
        global $wpdb;

        $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( 'form_entries' ) ) );

        delete_option( self::DB_VERSION_KEY );
        delete_option( 'eppro_settings' );
        delete_option( 'eppro_settings_version' );
        delete_option( 'eppro_customizer_settings' );

        $wpdb->query(
            $wpdb->prepare(
                'DELETE FROM %i WHERE option_name LIKE %s OR option_name LIKE %s',
                $wpdb->options,
                $wpdb->esc_like( '_transient_eppro_' ) . '%',
                $wpdb->esc_like( '_transient_timeout_eppro_' ) . '%'
            )
        );

        wp_cache_flush();
    }

    /**
     * Run any necessary DB upgrades when the plugin version changes.
     */
    public static function maybe_upgrade(): void {
        $installed = get_option( self::DB_VERSION_KEY, '0.0.0' );
        if ( version_compare( $installed, self::DB_VERSION, '<' ) ) {
            self::install();
        }
    }

    /**
     * Returns the full table name for a given short name.
     *
     * @param  string $name e.g. 'form_entries'
     * @return string       e.g. 'wp_eppro_form_entries'
     */
    /** Invalidate cached entry lists after any insert / update / delete. */
    private static function flush_list_cache(): void {
        wp_cache_set_last_changed( 'eppro_entries' );
    }

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

    // -------------------------------------------------------------------------
    // Form Entries CRUD
    // -------------------------------------------------------------------------

    /**
     * Insert a new form entry.
     *
     * @param  array $data {
     *     @type string $form_id
     *     @type string $form_name
     *     @type int    $page_id
     *     @type string $page_url
     *     @type array  $fields       key-label => sanitized-value
     *     @type string $ip_address
     *     @type string $user_agent
     * }
     * @return int|false  Inserted row ID, or false on failure.
     */
    public static function insert_entry( array $data ) {
        global $wpdb;

        $row = [
            'form_id'      => sanitize_key( $data['form_id']   ?? '' ),
            'form_name'    => sanitize_text_field( $data['form_name'] ?? '' ),
            'page_id'      => absint( $data['page_id']          ?? 0 ),
            'page_url'     => esc_url_raw( $data['page_url']    ?? '' ),
            'fields'       => wp_json_encode( $data['fields']   ?? [] ),
            'ip_address'   => sanitize_text_field( $data['ip_address']  ?? '' ),
            'user_agent'   => sanitize_text_field( substr( $data['user_agent'] ?? '', 0, 500 ) ),
            'status'       => 'unread',
            'submitted_at' => current_time( 'mysql' ),
        ];

        $result = $wpdb->insert( self::table( 'form_entries' ), $row );

        wp_cache_delete( 'eppro_form_list', 'eppro' );
        self::flush_list_cache();

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

        return (int) $wpdb->insert_id;
    }

    /**
     * Get paginated entries, optionally filtered by form_id or status.
     *
     * @param  array $args {
     *     @type string $form_id   Filter by form ID (optional).
     *     @type string $status    'unread'|'read'|'starred'|'' for all.
     *     @type int    $per_page  Default 20.
     *     @type int    $paged     Default 1.
     *     @type string $orderby   Default 'submitted_at'.
     *     @type string $order     'DESC'|'ASC'. Default 'DESC'.
     * }
     * @return array { entries: array, total: int }
     */
    public static function get_entries( array $args = [] ): array {
        global $wpdb;

        $defaults = [
            'form_id'  => '',
            'status'   => '',
            'per_page' => 20,
            'paged'    => 1,
            'orderby'  => 'submitted_at',
            'order'    => 'DESC',
        ];
        $args = wp_parse_args( $args, $defaults );

        $table = self::table( 'form_entries' );

        $allowed_orderby = [ 'id', 'form_name', 'status', 'submitted_at' ];
        $orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'submitted_at';
        $order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

        $per_page = max( 1, min( 100, (int) $args['per_page'] ) );
        $offset   = ( max( 1, (int) $args['paged'] ) - 1 ) * $per_page;

        $form_id = ! empty( $args['form_id'] ) ? sanitize_key( $args['form_id'] ) : '';
        $status  = ! empty( $args['status'] ) ? sanitize_key( $args['status'] ) : '';

        // Keyed on the last write, so new, updated or deleted entries show up at once
        // (matters with a persistent object cache such as Redis or Memcached).
        $cache_key = 'eppro_entries_' . md5( wp_json_encode( [ $form_id, $status, $orderby, $order, $per_page, $offset, wp_cache_get_last_changed( 'eppro_entries' ) ] ) );
        $cached    = wp_cache_get( $cache_key, 'eppro' );
        if ( is_array( $cached ) ) {
            return $cached;
        }

        // Build both queries with prepare() inlined directly into the $wpdb
        // call. The optional form_id/status filters are expressed as
        // "( %s = '' OR column = %s )" so the SQL string stays fully literal —
        // every value and identifier goes through a placeholder ( %s / %i / %d )
        // and an empty filter value makes its clause a no-op.
        $total = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE ( %s = '' OR form_id = %s ) AND ( %s = '' OR status = %s )",
                $table,
                $form_id,
                $form_id,
                $status,
                $status
            )
        );

        // ORDER BY direction cannot be a placeholder, so branch on the
        // already-whitelisted $order value into two fully literal queries,
        // each with prepare() inlined into get_results().
        if ( 'ASC' === $order ) {
            $entries = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM %i WHERE ( %s = '' OR form_id = %s ) AND ( %s = '' OR status = %s ) ORDER BY %i ASC LIMIT %d OFFSET %d",
                    $table,
                    $form_id,
                    $form_id,
                    $status,
                    $status,
                    $orderby,
                    $per_page,
                    $offset
                ),
                ARRAY_A
            );
        } else {
            $entries = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM %i WHERE ( %s = '' OR form_id = %s ) AND ( %s = '' OR status = %s ) ORDER BY %i DESC LIMIT %d OFFSET %d",
                    $table,
                    $form_id,
                    $form_id,
                    $status,
                    $status,
                    $orderby,
                    $per_page,
                    $offset
                ),
                ARRAY_A
            );
        }

        $result = [
            'entries' => $entries ?: [],
            'total'   => $total,
        ];

        wp_cache_set( $cache_key, $result, 'eppro' );
        return $result;
    }

    /**
     * Get a single entry by ID.
     */
    public static function get_entry( int $id ): ?array {
        global $wpdb;

        $cache_key = 'eppro_entry_' . $id;
        $cached    = wp_cache_get( $cache_key, 'eppro' );
        if ( is_array( $cached ) ) {
            return $cached;
        }

        $row = $wpdb->get_row(
            $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table( 'form_entries' ), $id ),
            ARRAY_A
        );

        $entry = $row ?: null;
        if ( $entry ) {
            wp_cache_set( $cache_key, $entry, 'eppro' );
        }
        return $entry;
    }

    /**
     * Update the status of an entry.
     *
     * @param int    $id     Entry ID.
     * @param string $status 'read'|'unread'|'starred'|'trash'.
     */
    public static function update_entry_status( int $id, string $status ): bool {
        global $wpdb;
        $allowed = [ 'read', 'unread', 'starred', 'trash' ];
        if ( ! in_array( $status, $allowed, true ) ) return false;

        $result = (bool) $wpdb->update(
            self::table( 'form_entries' ),
            [ 'status' => $status ],
            [ 'id'     => $id ],
            [ '%s' ],
            [ '%d' ]
        );

        wp_cache_delete( 'eppro_entry_' . $id, 'eppro' );
        wp_cache_delete( 'eppro_form_list', 'eppro' );
        self::flush_list_cache();
        return $result;
    }

    /**
     * Delete an entry permanently.
     */
    public static function delete_entry( int $id ): bool {
        global $wpdb;

        $result = (bool) $wpdb->delete(
            self::table( 'form_entries' ),
            [ 'id' => $id ],
            [ '%d' ]
        );

        wp_cache_delete( 'eppro_entry_' . $id, 'eppro' );
        wp_cache_delete( 'eppro_form_list', 'eppro' );
        self::flush_list_cache();
        return $result;
    }

    /**
     * Delete all entries for a given form_id.
     */
    public static function delete_form_entries( string $form_id ): bool {
        global $wpdb;

        $result = (bool) $wpdb->delete(
            self::table( 'form_entries' ),
            [ 'form_id' => sanitize_key( $form_id ) ],
            [ '%s' ]
        );

        wp_cache_delete( 'eppro_form_list', 'eppro' );
        self::flush_list_cache();
        return $result;
    }

    /**
     * Get all distinct form IDs + names (for filter dropdowns).
     *
     * @return array[] Each item has 'form_id' and 'form_name'.
     */
    public static function get_form_list(): array {
        $cache_key = 'eppro_form_list';
        $cached    = wp_cache_get( $cache_key, 'eppro' );
        if ( is_array( $cached ) ) {
            return $cached;
        }

        global $wpdb;
        $results = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT DISTINCT form_id, form_name FROM %i ORDER BY form_name ASC',
                self::table( 'form_entries' )
            ),
            ARRAY_A
        ) ?: [];

        wp_cache_set( $cache_key, $results, 'eppro' );
        return $results;
    }
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery