<?php
/**
 * Site Manager — CRUD operations for connected sites.
 */

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

class WPUHM_Site_Manager {

    /**
     * Get the table name.
     */
    private static function table() {
        global $wpdb;
        return $wpdb->prefix . 'userhub_sites';
    }

    /**
     * Get all connected sites.
     */
    public static function get_all() {
        global $wpdb;
        return $wpdb->get_results(
            "SELECT * FROM " . self::table() . " ORDER BY site_name ASC",
            ARRAY_A
        );
    }

    /**
     * Get a single site by ID.
     */
    public static function get( $id ) {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM " . self::table() . " WHERE id = %d", $id ),
            ARRAY_A
        );
    }

    /**
     * Get sites by IDs.
     */
    public static function get_by_ids( $ids ) {
        global $wpdb;
        if ( empty( $ids ) ) {
            return array();
        }
        $ids = array_map( 'absint', $ids );
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM " . self::table() . " WHERE id IN ({$placeholders}) ORDER BY site_name ASC",
                ...$ids
            ),
            ARRAY_A
        );
    }

    /**
     * Add a new site.
     */
    public static function add( $site_url, $api_key, $site_name = '' ) {
        global $wpdb;

        // Normalize URL
        $site_url = trailingslashit( esc_url_raw( $site_url ) );

        // Enforce maximum length bounds matching the DB schema
        if ( strlen( $site_url ) > 500 ) {
            return new WP_Error( 'invalid_url_length', __( 'Site URL must be 500 characters or less.', 'absoluit-user-hub' ) );
        }
        if ( strlen( $api_key ) > 255 ) {
            return new WP_Error( 'invalid_key_length', __( 'API Key must be 255 characters or less.', 'absoluit-user-hub' ) );
        }
        if ( strlen( $site_name ) > 255 ) {
            return new WP_Error( 'invalid_name_length', __( 'Site Name must be 255 characters or less.', 'absoluit-user-hub' ) );
        }

        // Check for duplicate
        $existing = $wpdb->get_var(
            $wpdb->prepare( "SELECT id FROM " . self::table() . " WHERE site_url = %s", $site_url )
        );

        if ( $existing ) {
            return new WP_Error( 'duplicate', __( 'This site is already connected.', 'absoluit-user-hub' ) );
        }

        $result = $wpdb->insert(
            self::table(),
            array(
                'site_url'  => $site_url,
                'api_key'   => sanitize_text_field( $api_key ),
                'site_name' => ! empty( $site_name ) ? sanitize_text_field( $site_name ) : $site_url,
                'status'    => 'unknown',
            ),
            array( '%s', '%s', '%s', '%s' )
        );

        if ( false === $result ) {
            return new WP_Error( 'db_error', __( 'Failed to add site to database.', 'absoluit-user-hub' ) );
        }

        return $wpdb->insert_id;
    }

    /**
     * Update a site.
     */
    public static function update( $id, $data ) {
        global $wpdb;

        // Enforce length limits on updated fields
        if ( isset( $data['site_url'] ) && strlen( $data['site_url'] ) > 500 ) {
            return false;
        }
        if ( isset( $data['api_key'] ) && strlen( $data['api_key'] ) > 255 ) {
            return false;
        }
        if ( isset( $data['site_name'] ) && strlen( $data['site_name'] ) > 255 ) {
            return false;
        }

        $allowed = array( 'site_name', 'site_url', 'api_key', 'status', 'last_ping', 'wp_version', 'php_version', 'user_count' );
        $update  = array();
        $format  = array();

        foreach ( $allowed as $field ) {
            if ( isset( $data[ $field ] ) ) {
                $update[ $field ] = $data[ $field ];
                $format[]         = ( in_array( $field, array( 'user_count' ), true ) ) ? '%d' : '%s';
            }
        }

        if ( empty( $update ) ) {
            return false;
        }

        return $wpdb->update( self::table(), $update, array( 'id' => absint( $id ) ), $format, array( '%d' ) );
    }

    /**
     * Remove a site.
     */
    public static function remove( $id ) {
        global $wpdb;
        return $wpdb->delete( self::table(), array( 'id' => absint( $id ) ), array( '%d' ) );
    }

    /**
     * Test connection to a site (ping).
     */
    public static function test_connection( $id ) {
        $site = self::get( $id );
        if ( ! $site ) {
            return new WP_Error( 'not_found', __( 'Site not found.', 'absoluit-user-hub' ) );
        }

        $result = WPUHM_API_Client::get( $site['site_url'], $site['api_key'], 'ping' );

        if ( $result['success'] ) {
            $data = $result['data'];
            self::update( $id, array(
                'status'      => 'online',
                'last_ping'   => current_time( 'mysql' ),
                'site_name'   => isset( $data['label'] ) ? $data['label'] : $site['site_name'],
                'wp_version'  => isset( $data['wp'] ) ? $data['wp'] : '',
                'php_version' => isset( $data['php'] ) ? $data['php'] : '',
                'user_count'  => isset( $data['users']['total_users'] ) ? $data['users']['total_users'] : 0,
            ) );
        } else {
            self::update( $id, array(
                'status'    => 'offline',
                'last_ping' => current_time( 'mysql' ),
            ) );
        }

        return $result;
    }

    /**
     * Get site counts.
     */
    public static function get_counts() {
        global $wpdb;
        $table = self::table();

        return array(
            'total'   => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ),
            'online'  => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", 'online' ) ),
            'offline' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", 'offline' ) ),
        );
    }
}