<?php
/**
 * Database handler class for the Activity Lens plugin.
 *
 * Manages interactions with a separate database for storing activity logs, user data,
 * and session information, ensuring minimal performance impact on the main WordPress
 * database. Utilizes WordPress's wpdb class for external database connections and
 * handles table creation, data insertion, updates, and retrieval.
 *
 * Key features:
 * - Establishes a secure connection to an external database.
 * - Creates and manages custom tables (`wpal_users`, `wpal_sessions`, `wpal_activity`).
 * - Inserts and updates records for users, sessions, and activities.
 * - Retrieves activity data for admin interface display.
 * - Exports activity logs as CSV files.
 * - Includes debug-friendly error logging and robust error handling.
 *
 * @package Activity_Lens
 * @since   1.0.0
 */

if (!defined('ABSPATH')) {
    exit;
}

class WPAL_DB {

    /**
     * Prefix for custom database table names.
     *
     * Used to namespace the plugin’s tables (e.g., `wpal_users`, `wpal_sessions`, `wpal_activity`)
     * to avoid conflicts with other database tables.
     *
     * @since 1.0.0
     * @var   string
     */
    private $prefix = 'wpal_';

    /**
     * Name of the users table.
     *
     * Stores the fully qualified name of the users table (e.g., `wpal_users`) for user profile data.
     *
     * @since 1.0.0
     * @var   string
     */
    private $users;

    /**
     * Name of the sessions table.
     *
     * Stores the fully qualified name of the sessions table (e.g., `wpal_sessions`) for session data.
     *
     * @since 1.0.0
     * @var   string
     */
    private $sessions;

    /**
     * Name of the activity table.
     *
     * Stores the fully qualified name of the activity table (e.g., `wpal_activity`) for activity logs.
     *
     * @since 1.0.0
     * @var   string
     */
    private $activity;

    /**
     * Instance of the wpdb class for database operations.
     *
     * Manages the connection to the external database and executes queries for table creation,
     * data insertion, updates, and retrieval.
     *
     * @since 1.0.0
     * @var   wpdb
     */
    public $wpaldb;


    /**
     * Constructor to initialize database table names and connection.
     *
     * Sets up table names with the configured prefix and establishes a connection to the
     * external database using provided credentials.
     *
     * @since 1.0.0
     * @param string $uname Database username.
     * @param string $pass  Database password.
     * @param string $db    Database name.
     * @param string $host  Database host.
     */
    public function __construct( $uname, $pass, $db, $host ) {
        $this->users    = $this->prefix . 'users';
        $this->sessions = $this->prefix . 'sessions';
        $this->activity = $this->prefix . 'activity';

        $this->wpal_DBConnect( $uname, $pass, $db, $host );
    }

    /**
     * Establishes a connection to the external database.
     *
     * Uses WordPress’s wpdb class to connect to the external database and displays an admin
     * notice if the connection fails.
     *
     * @since 1.0.0
     * @param string $uname Database username.
     * @param string $pass  Database password.
     * @param string $db    Database name.
     * @param string $host  Database host.
     */
    protected function wpal_DBConnect( $uname, $pass, $db, $host ) {
        $this->wpaldb = new wpdb( $uname, $pass, $db, $host );

        $allowed_screens = [
            'toplevel_page_wpal-settings',
            'wpal_page_wpal-db-settings',
            'wpal_page_wpal-export-data',
        ];
       
       if ( ! empty( $this->wpaldb->error ) ) {
            $this->wpaldb = null;
            add_action( 'current_screen', function( $screen ) use($allowed_screens) {
                if ( isset( $screen->id ) && in_array( $screen->id, $allowed_screens, true )) {
                    add_action( 'admin_notices', function() {
                        ?>
                        <div class="notice notice-error is-dismissible">
                            <p><?php esc_html_e( 'Activity Lens: Database is not connected. Please check your database credentials.', 'activity-lens' ); ?></p>
                        </div>
                        <?php
                    } );

                }
            } );
        } else {
            add_action( 'current_screen', function( $screen ) use($allowed_screens) {
                if ( isset( $screen->id ) && in_array( $screen->id, $allowed_screens, true ) ) {
                    add_action( 'admin_notices', function() {
                        ?>
                        <div class="notice notice-success is-dismissible">
                            <p><?php esc_html_e( 'Activity Lens: Database is connected.', 'activity-lens' ); ?></p>
                        </div>
                        <?php
                    } );

                }
            } );
        }
    }

    /**
     * Creates custom database tables for users, sessions, and activities.
     *
     * Defines schema for `wpal_users`, `wpal_sessions`, and `wpal_activity` tables with
     * appropriate columns, primary keys, and foreign key constraints. Respects database
     * charset and collation settings.
     *
     * @since 1.0.0
     * @return bool|WP_Error True on success, WP_Error on failure.
     */
    public function wpal_tables() {
        if ( empty( $this->wpaldb ) ) {
            return false;
        }

        $charset_collate = $this->wpaldb->get_charset_collate();

        // Create users table.
        $users_table_sql = "CREATE TABLE IF NOT EXISTS {$this->users} (
            user_id INT NOT NULL,
            user_name VARCHAR(100) NOT NULL,
            user_email VARCHAR(150) NOT NULL,
            user_role VARCHAR(50) NOT NULL,
            user_active TINYINT(1) NOT NULL DEFAULT 1,
            PRIMARY KEY (user_id)
        ) $charset_collate;";

        if ( false === $this->wpaldb->query( $users_table_sql ) ) {
            return new WP_Error( 'db_create_users_error', 'Failed to create users table.');
        }

        // Create sessions table.
        $sessions_table_sql = "CREATE TABLE IF NOT EXISTS {$this->sessions} (
            session_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            session_token VARCHAR(255) NOT NULL,
            user_ip VARCHAR(45) NOT NULL,
            user_agent VARCHAR(255) NOT NULL,
            user_id INT NOT NULL,
            PRIMARY KEY (session_token),
            FOREIGN KEY (user_id) REFERENCES {$this->users}(user_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) $charset_collate;";

        if ( false === $this->wpaldb->query( $sessions_table_sql ) ) {
            return new WP_Error( 'db_create_sessions_error', 'Failed to create sessions table.');
        }

        // Create activity table.
        $activity_table_sql = "CREATE TABLE IF NOT EXISTS {$this->activity} (
            activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            activity_id INT NOT NULL AUTO_INCREMENT,
            user_id INT NOT NULL,
            activity_type VARCHAR(100) NOT NULL,
            activity_details TEXT NOT NULL,
            activity_session VARCHAR(255),
            PRIMARY KEY (activity_id),
            FOREIGN KEY (user_id) REFERENCES {$this->users}(user_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) $charset_collate;";

        if ( false === $this->wpaldb->query( $activity_table_sql ) ) {
            return new WP_Error( 'db_create_activity_error', 'Failed to create activity table.');
        }

        return true;
    }

    /**
     * Inserts a user record into the users table.
     *
     * Adds a new user with their ID, username, email, and role. Checks for duplicates
     * and sanitizes inputs for data integrity.
     *
     * @since 1.0.0
     * @param int    $user_id    User ID from WordPress.
     * @param string $user_name  Username of the user.
     * @param string $user_email Email address of the user.
     * @param string $user_role  Role of the user (e.g., 'administrator', 'subscriber').
     * @return bool|WP_Error True on success, WP_Error on failure or duplicate.
     */
    public function wpal_insertUser( $user_id, $user_name, $user_email, $user_role ) {
        if ( empty( $this->wpaldb ) ) {
            return false;
        }

        $data = [
            'user_id'    => $user_id,
            'user_name'  => sanitize_text_field( $user_name ),
            'user_email' => sanitize_email( $user_email ),
            'user_role'  => sanitize_text_field( $user_role ),
        ];
        $format = [ '%d', '%s', '%s', '%s' ];

        // Check if user already exists.
        $exists = $this->wpaldb->get_var(
            $this->wpaldb->prepare(
                "SELECT COUNT(*) FROM {$this->users} WHERE user_id = %d",
                $user_id
            )
        );

        if ( $exists > 0 ) {
            return new WP_Error( 'db_duplicate_user', 'User already exists.');
        }

        // Attempt insert.
        $inserted = $this->wpaldb->insert( $this->users, $data, $format );

        if ( false === $inserted ) {
            return new WP_Error( 'db_insert_error', 'Failed to insert user.');
        }

        return true;
    }

    /**
     * Updates a user’s active status in the users table.
     *
     * Modifies the `user_active` field for the specified user ID, typically to mark
     * users as inactive (0) or active (1).
     *
     * @since 1.0.0
     * @param int $user_id     User ID to update.
     * @param int $user_active New active status (0 for inactive, 1 for active).
     * @return bool|WP_Error True on success, WP_Error on failure.
     */
    public function wpal_updateUser( $user_id, $user_active ) {
        if ( empty( $this->wpaldb ) ) {
            return false;
        }

        $data  = [ 'user_active' => $user_active ];
        $where = [ 'user_id' => $user_id ];
        $updated = $this->wpaldb->update( $this->users, $data, $where, [ '%d' ], [ '%d' ] );

        if ( false === $updated ) {
            return new WP_Error( 'db_update_error', 'Failed to update user.');
        }

        return true;
    }

    /**
     * Inserts a session record into the sessions table.
     *
     * Adds a new session with the session token, user IP, user agent, and associated user ID.
     * Sanitizes inputs for data integrity.
     *
     * @since 1.0.0
     * @param string $session_token Unique session token.
     * @param string $user_ip      User’s IP address.
     * @param string $user_agent   User’s browser user-agent string.
     * @param int    $user_id      User ID associated with the session.
     * @return bool|WP_Error True on success, WP_Error on failure.
     */
    public function wpal_insertSession( $session_token, $user_ip, $user_agent, $user_id ) {
        if ( empty( $this->wpaldb ) ) {
            return false;
        }

        $data = [
            'session_token' => sanitize_text_field( $session_token ),
            'user_ip'       => sanitize_text_field( $user_ip ),
            'user_agent'    => sanitize_text_field( $user_agent ),
            'user_id'       => $user_id,
        ];
        $format = [ '%s', '%s', '%s', '%d' ];

        $inserted = $this->wpaldb->insert( $this->sessions, $data, $format );

        if ( false === $inserted ) {
            return new WP_Error( 'db_insert_error', 'Failed to insert session.');
        }

        return true;
    }

    /**
     * Inserts an activity record into the activity table.
     *
     * Adds a new activity with user ID, activity type, details, and session token.
     * Sanitizes inputs for data integrity.
     *
     * @since 1.0.0
     * @param int    $user_id          User ID associated with the activity.
     * @param string $activity_type    Type of activity (e.g., 'user', 'post').
     * @param string $activity_details Detailed description of the activity.
     * @param string $activity_session Session token linking to a session.
     * @return bool|WP_Error True on success, WP_Error on failure.
     */
    public function wpal_insertActivity( $user_id, $activity_type, $activity_details, $activity_session ) {
        if ( empty( $this->wpaldb ) ) {
            return false;
        }

        $log_count = $this->wpaldb->get_var(
            $this->wpaldb->prepare(
                "SELECT COUNT(*) FROM {$this->activity} WHERE %d = %d", 1, 1
            )
        );

        if ( $log_count >= 1000 ) {

            update_option( 'wpal_log_limit_notice', 1 );
            return;
        }

        $data = [
            'user_id'          => $user_id,
            'activity_type'    => sanitize_text_field( $activity_type ),
            'activity_details' => sanitize_textarea_field( $activity_details ),
            'activity_session' => sanitize_text_field( $activity_session ),
        ];
        $format = [ '%d', '%s', '%s', '%s' ];

        $inserted = $this->wpaldb->insert( $this->activity, $data, $format );

        if ( false === $inserted ) {
            return new WP_Error( 'db_insert_error', 'Failed to insert activity.');
        }

        return true;
    }

    /**
     * Displays the latest 100 activity records in an HTML table.
     *
     * Retrieves and displays the most recent 100 activity records, joining with the
     * `wpal_users` table to include user details. Outputs an HTML table in the admin interface.
     *
     * @since 1.0.0
     */
    public function wpal_displayActivityTable() {
        if ( empty( $this->wpaldb ) ) {
            return;
        }

        $users_table    = $this->users;
        $activity_table = $this->activity;

        $query = $this->wpaldb->prepare(
            "
            SELECT 
                a.activity_id,
                a.user_id,
                u.user_name AS user_name,
                u.user_role,
                a.activity_type,
                a.activity_details,
                a.activity_session,
                a.activity_time
            FROM {$activity_table} AS a
            LEFT JOIN {$users_table} AS u ON a.user_id = u.user_id
            ORDER BY a.activity_time DESC
            LIMIT %d
            ", 100
        );

        $results = $this->wpaldb->get_results( $query );

         $allowed_screens = [
            'toplevel_page_wpal-settings',
            'wpal_page_wpal-db-settings',
            'wpal_page_wpal-export-data',
        ];

        if ( empty( $results ) ) {
           add_action( 'current_screen', function( $screen ) use($allowed_screens) {
                if ( isset( $screen->id ) && in_array( $screen->id, $allowed_screens, true )) {
                    add_action( 'admin_notices', function() {
                        ?>
                        <div class="notice notice-info is-dismissible">
                            <p><?php esc_html_e( 'Activity Lens: No activity found in the record.', 'activity-lens' ); ?></p>
                        </div>
                        <?php
                    } );

                }
            } );
            return;
        }

        ?>
        <div class="wrap">
            <table class="widefat fixed striped">
                <thead>
                    <tr>
                        <th><?php esc_html_e( 'Activity Time', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'Activity ID', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'User ID', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'User Name', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'User Role', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'Activity Type', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'Activity Details', 'activity-lens' ); ?></th>
                        <th><?php esc_html_e( 'Activity Session', 'activity-lens' ); ?></th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ( $results as $row ) : ?>
                        <tr>
                            <td><?php echo esc_html( $row->activity_time ); ?></td>
                            <td><?php echo esc_html( $row->activity_id ); ?></td>
                            <td><?php echo esc_html( $row->user_id ); ?></td>
                            <td><?php echo esc_html( $row->user_name ); ?></td>
                            <td><?php echo esc_html( $row->user_role ); ?></td>
                            <td><?php echo esc_html( $row->activity_type ); ?></td>
                            <td><?php echo esc_html( $row->activity_details ); ?></td>
                            <td><?php echo esc_html( $row->activity_session ); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <?php
    }

    /**
     * Exports all activity records as a CSV file.
     *
     * Generates a CSV file with all activity records, joined with user details, for download.
     * Triggered by a GET parameter with nonce verification.
     *
     * @since 1.0.0
     */
    public function wpal_exportData() {
        if ( empty( $this->wpaldb ) ) {
            return;
        }

        $users_table    = $this->users;
        $activity_table = $this->activity;

        if (
            isset( $_GET['export_activity_csv'] ) &&
            '1' === $_GET['export_activity_csv'] &&
            isset( $_GET['_wpnonce'] ) &&
            wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ), 'export_activity_csv_nonce' )
        ) {
            $export_query = "
                SELECT 
                    a.activity_time,
                    a.activity_id,
                    a.user_id,
                    u.user_name,
                    u.user_role,
                    a.activity_type,
                    a.activity_details,
                    a.activity_session
                FROM {$activity_table} AS a
                LEFT JOIN {$users_table} AS u ON a.user_id = u.user_id
                ORDER BY a.activity_time DESC
                LIMIT 1000
            ";

            $export_results = $this->wpaldb->get_results( $export_query, ARRAY_A );

            if ( null === $export_results ) {
                ?>
                <div class="notice notice-error">
                    <p><?php esc_html_e( 'Activity Lens: Database error occurred while exporting data.', 'activity-lens' ); ?></p>
                </div>
                <?php
                return;
            }

            if (!empty($export_results)) {
                // Output CSV headers
                header('Content-Type: text/csv');
                header('Content-Disposition: attachment;filename=word_activity_log' . current_time('Y-m-d_H-i-s') . '.csv');

                $output = fopen('php://output', 'w');

                // Column Headers
                $csv = implode(',', array_keys($export_results[0])) . "\n";

                foreach ($export_results as $row) {
                    $csv .= implode(',', array_map('sanitize_text_field', $row)) . "\n";
                }

                echo esc_html($csv);
                exit;

            } else {
                ?>
                <div class="notice notice-info">
                    <p><?php esc_html_e( 'Activity Lens: No data to export.', 'activity-lens' ); ?></p>
                </div>
                <?php
            }
        }
    }
}
?>