<?php

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

class Niwa_AI_Admin_Autonomy_DB_Intelligence {
    const CACHE_TRANSIENT = 'niwa_ai_admin_db_intelligence_v1';

    private $workspace;

    public function __construct( $workspace = null ) {
        $this->workspace = $workspace;
    }

    public function register_hooks() {
        add_action( 'activated_plugin', array( $this, 'flush_cache' ) );
        add_action( 'deactivated_plugin', array( $this, 'flush_cache' ) );
        add_action( 'switch_theme', array( $this, 'flush_cache' ) );
        add_action( 'upgrader_process_complete', array( $this, 'flush_cache' ) );
    }

    public function flush_cache() {
        delete_transient( self::CACHE_TRANSIENT );
    }

    public function get_table_index( $plugin_facts = array(), $force = false ) {
        $snapshot = $this->get_snapshot( $force );
        $tables   = isset( $snapshot['tables'] ) && is_array( $snapshot['tables'] ) ? $snapshot['tables'] : array();

        return $this->attach_owner_hints( $tables, $plugin_facts );
    }

    public function get_table_detail( $table_name, $plugin_facts = array(), $sample_limit = 3 ) {
        $table_name = trim( (string) $table_name );
        $tables     = $this->get_table_index( $plugin_facts );

        foreach ( $tables as $table ) {
            if ( isset( $table['name'] ) && $table_name === (string) $table['name'] ) {
                $table['sample_rows'] = $this->get_sample_rows( $table_name, max( 1, min( 5, intval( $sample_limit ) ) ) );
                return $table;
            }
        }

        return array();
    }

    public function get_snapshot( $force = false ) {
        if ( ! $force ) {
            $cached = get_transient( self::CACHE_TRANSIENT );
            if ( is_array( $cached ) && ! empty( $cached ) ) {
                return $cached;
            }
        }

        global $wpdb;

        $tables = $wpdb->get_col( 'SHOW TABLES' );
        if ( ! is_array( $tables ) ) {
            $tables = array();
        }

        $rows = array();
        foreach ( $tables as $table_name ) {
            $rows[] = $this->build_table_fact( (string) $table_name );
        }

        $snapshot = array(
            'generated_at' => time(),
            'table_count'  => count( $rows ),
            'tables'       => $rows,
        );

        set_transient( self::CACHE_TRANSIENT, $snapshot, 15 * MINUTE_IN_SECONDS );
        if ( $this->workspace && method_exists( $this->workspace, 'write_json_artifact' ) ) {
            $this->workspace->write_json_artifact( 'db-intelligence-latest.json', $snapshot, 'snapshots' );
        }

        return $snapshot;
    }

    /**
     * Verify table name exists in the database (whitelist check).
     *
     * @param string $table_name Table name to validate.
     * @return bool True if the table exists.
     */
    private function is_known_table( $table_name ) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $tables = $wpdb->get_col( 'SHOW TABLES' );

        return is_array( $tables ) && in_array( $table_name, $tables, true );
    }

    private function build_table_fact( $table_name ) {
        global $wpdb;

        if ( ! $this->is_known_table( $table_name ) ) {
            return array( 'name' => $table_name, 'error' => 'Table not found in database.' );
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names cannot use prepare(); validated via is_known_table() whitelist.
        $columns = $wpdb->get_results( 'DESCRIBE `' . esc_sql( $table_name ) . '`', ARRAY_A );
        if ( ! is_array( $columns ) ) {
            $columns = array();
        }

        $status = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS LIKE %s', $table_name ), ARRAY_A );
        if ( ! is_array( $status ) ) {
            $status = array();
        }

        $column_names = array();
        $column_defs  = array();
        foreach ( $columns as $column ) {
            if ( empty( $column['Field'] ) ) {
                continue;
            }
            $column_names[] = (string) $column['Field'];
            $column_defs[]  = array(
                'name'    => (string) $column['Field'],
                'type'    => isset( $column['Type'] ) ? (string) $column['Type'] : '',
                'null'    => isset( $column['Null'] ) ? (string) $column['Null'] : '',
                'key'     => isset( $column['Key'] ) ? (string) $column['Key'] : '',
                'default' => isset( $column['Default'] ) ? (string) $column['Default'] : '',
            );
        }

        $row_count = isset( $status['Rows'] ) ? intval( $status['Rows'] ) : 0;
        $metrics_signals = array_values(
            array_intersect(
                array( 'created_at', 'updated_at', 'event_type', 'order_total', 'discount_amount', 'used_at', 'session_id', 'user_session', 'visits', 'pageviews', 'revenue' ),
                $column_names
            )
        );

        return array(
            'name'           => $table_name,
            'engine'         => isset( $status['Engine'] ) ? (string) $status['Engine'] : '',
            'rows'           => $row_count,
            'columns'        => $column_defs,
            'column_names'   => $column_names,
            'sample_rows'    => $this->get_sample_rows( $table_name, 2 ),
            'metrics_signals' => $metrics_signals,
            'fingerprint'    => md5( wp_json_encode( array( $column_defs, $row_count ) ) ),
            'owner_hints'    => array(),
        );
    }

    private function get_sample_rows( $table_name, $limit ) {
        global $wpdb;

        if ( ! $this->is_known_table( $table_name ) ) {
            return array();
        }

        $limit = max( 1, min( 5, intval( $limit ) ) );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names cannot use prepare(); validated via is_known_table() whitelist.
        $rows  = $wpdb->get_results( 'SELECT * FROM `' . esc_sql( $table_name ) . '` LIMIT ' . $limit, ARRAY_A );
        if ( ! is_array( $rows ) ) {
            return array();
        }

        $samples = array();
        foreach ( $rows as $row ) {
            $sample = array();
            foreach ( (array) $row as $key => $value ) {
                if ( is_null( $value ) ) {
                    $sample[ $key ] = null;
                    continue;
                }
                $text = (string) $value;
                if ( mb_strlen( $text ) > 180 ) {
                    $text = rtrim( mb_substr( $text, 0, 177 ) ) . '...';
                }
                $sample[ $key ] = $text;
            }
            $samples[] = $sample;
        }

        return $samples;
    }

    private function attach_owner_hints( $tables, $plugin_facts ) {
        global $wpdb;

        $plugin_terms = $this->build_plugin_terms( $plugin_facts );

        foreach ( $tables as &$table ) {
            $table_name = isset( $table['name'] ) ? strtolower( (string) $table['name'] ) : '';
            $trimmed    = preg_replace( '/^' . preg_quote( strtolower( (string) $wpdb->prefix ), '/' ) . '/', '', $table_name );
            $hints      = array();

            foreach ( $plugin_terms as $plugin ) {
                $score = 0;
                foreach ( $plugin['terms'] as $term ) {
                    if ( '' === $term ) {
                        continue;
                    }
                    if ( false !== strpos( $trimmed, $term ) ) {
                        $score += 40;
                    } elseif ( false !== strpos( $table_name, $term ) ) {
                        $score += 25;
                    }
                }

                if ( $score <= 0 ) {
                    continue;
                }

                $hints[] = array(
                    'slug'       => $plugin['slug'],
                    'name'       => $plugin['name'],
                    'confidence' => min( 100, $score ),
                );
            }

            usort(
                $hints,
                function ( $a, $b ) {
                    return intval( $b['confidence'] ) <=> intval( $a['confidence'] );
                }
            );

            $table['owner_hints'] = array_slice( $hints, 0, 3 );
        }
        unset( $table );

        return $tables;
    }

    private function build_plugin_terms( $plugin_facts ) {
        $terms = array();

        foreach ( (array) $plugin_facts as $plugin ) {
            $slug = isset( $plugin['slug'] ) ? sanitize_title( (string) $plugin['slug'] ) : '';
            $name = isset( $plugin['name'] ) ? (string) $plugin['name'] : $slug;
            if ( '' === $slug ) {
                continue;
            }

            $candidates = array(
                $slug,
                str_replace( '-', '_', $slug ),
                str_replace( '_', '-', $slug ),
            );

            if ( ! empty( $plugin['scan']['option_keys'] ) && is_array( $plugin['scan']['option_keys'] ) ) {
                foreach ( array_slice( $plugin['scan']['option_keys'], 0, 8 ) as $option_key ) {
                    $parts = preg_split( '/[_-]/', strtolower( (string) $option_key ) );
                    if ( ! empty( $parts[0] ) ) {
                        $candidates[] = $parts[0];
                    }
                    if ( ! empty( $parts[1] ) ) {
                        $candidates[] = $parts[0] . '_' . $parts[1];
                    }
                }
            }

            $terms[] = array(
                'slug'  => $slug,
                'name'  => $name,
                'terms' => array_values( array_unique( array_filter( array_map( 'strtolower', $candidates ) ) ) ),
            );
        }

        return $terms;
    }
}
