<?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.3.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 );

        // -- eppro_popup_stats: one row per popup per day ------------------------
        $stats = self::table( 'popup_stats' );
        dbDelta( "CREATE TABLE {$stats} (
            popup_id     BIGINT UNSIGNED  NOT NULL,
            day          DATE             NOT NULL,
            views        INT UNSIGNED     NOT NULL DEFAULT 0,
            conversions  INT UNSIGNED     NOT NULL DEFAULT 0,
            closes       INT UNSIGNED     NOT NULL DEFAULT 0,
            PRIMARY KEY  (popup_id,day),
            KEY day (day)
        ) {$charset};" );

        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' ) ) );
        $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( 'popup_stats' ) ) );
        delete_option( 'eppro_integration_status' );
        delete_option( 'eppro_brand_applied' );

        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