<?php

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

/**
 * Permanently logs frontend customer conversations.
 *
 * Transient-based conversation storage expires after 12 hours.
 * This class provides a persistent record so the admin autonomy bot
 * and the shop owner can review complete customer exchanges by session.
 */
class Niwa_AI_Frontend_Question_Log {

    const TABLE_SLUG    = 'niwa_ai_frontend_questions';
    const MAX_ROWS      = Niwa_AI_Constants::QUESTION_LOG_MAX_ROWS;
    const CLEANUP_BATCH = Niwa_AI_Constants::QUESTION_LOG_CLEANUP;

    /* ----------------------------------------------------------
     *  Table management
     * -------------------------------------------------------- */

    public function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_SLUG;
    }

    public function maybe_ensure_table_schema() {
        global $wpdb;

        $table = $this->get_table_name();
        $exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );

        if ( $table !== $exists ) {
            $this->ensure_table_schema();
        }
    }

    public function ensure_table_schema() {
        global $wpdb;

        $table           = $this->get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

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

        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            session_id varchar(128) NOT NULL DEFAULT '',
            question text NOT NULL,
            answer_summary text NOT NULL,
            source varchar(32) NOT NULL DEFAULT 'frontend',
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY session_id (session_id),
            KEY created_at (created_at)
        ) {$charset_collate};";

        dbDelta( $sql );
    }

    /* ----------------------------------------------------------
     *  Write
     * -------------------------------------------------------- */

    /**
     * Log a single customer question and the bot's full answer.
     *
     * @param string $session_id  Frontend session identifier.
     * @param string $question    The customer's message.
     * @param string $answer      The bot's response.
     * @param string $source      Origin channel (default 'frontend').
     */
    public function log_question( $session_id, $question, $answer = '', $source = 'frontend' ) {
        global $wpdb;

        $question = trim( (string) $question );
        if ( '' === $question ) {
            return;
        }

        $this->maybe_ensure_table_schema();

        $wpdb->insert(
            $this->get_table_name(),
            array(
                'session_id'     => sanitize_text_field( substr( (string) $session_id, 0, 128 ) ),
                'question'       => sanitize_textarea_field( $question ),
                'answer_summary' => sanitize_textarea_field( trim( (string) $answer ) ),
                'source'         => sanitize_key( substr( (string) $source, 0, 32 ) ),
                'created_at'     => current_time( 'mysql', true ),
            ),
            array( '%s', '%s', '%s', '%s', '%s' )
        );

        // Periodic cleanup: keep last MAX_ROWS rows.
        $this->maybe_cleanup();
    }

    /* ----------------------------------------------------------
     *  Read helpers (used by admin autonomy tools / heartbeat)
     * -------------------------------------------------------- */

    /**
     * Return the last $limit questions, newest first.
     */
    public function get_recent( $limit = 100, $offset = 0 ) {
        global $wpdb;

        $table = $this->get_table_name();
        $limit  = max( 1, min( 500, intval( $limit ) ) );
        $offset = max( 0, intval( $offset ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT id, session_id, question, LEFT(answer_summary, 500) AS answer_summary, source, created_at
             FROM {$table}
             ORDER BY created_at DESC
             LIMIT %d OFFSET %d",
            $limit,
            $offset
        ), ARRAY_A );
    }

    /**
     * Return the last $limit questions within a given number of days.
     */
    public function get_recent_days( $days = 7, $limit = 200 ) {
        global $wpdb;

        $table = $this->get_table_name();
        $limit = max( 1, min( 500, intval( $limit ) ) );
        $since = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, intval( $days ) ) * DAY_IN_SECONDS ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT id, session_id, question, LEFT(answer_summary, 500) AS answer_summary, source, created_at
             FROM {$table}
             WHERE created_at >= %s
             ORDER BY created_at DESC
             LIMIT %d",
            $since,
            $limit
        ), ARRAY_A );
    }

    /**
     * Return total number of logged questions.
     */
    public function count_all() {
        global $wpdb;

        $table = $this->get_table_name();
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
    }

    /**
     * Simple summary: count per day for the last N days.
     */
    public function daily_counts( $days = 14 ) {
        global $wpdb;

        $table = $this->get_table_name();
        $since = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, intval( $days ) ) * DAY_IN_SECONDS ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT DATE(created_at) AS day, COUNT(*) AS cnt
             FROM {$table}
             WHERE created_at >= %s
             GROUP BY DATE(created_at)
             ORDER BY day DESC",
            $since
        ), ARRAY_A );
    }

    /**
     * Return frontend sessions with aggregate activity and a last-message preview.
     *
     * @param string $search Search session IDs or transcript text.
     * @param int    $limit  Sessions per page.
     * @param int    $offset Result offset.
     * @return array{items:array,total:int}
     */
    public function get_sessions( $search = '', $limit = 20, $offset = 0 ) {
        global $wpdb;

        $this->maybe_ensure_table_schema();

        $table  = $this->get_table_name();
        $search = trim( (string) $search );
        $limit  = max( 1, min( 100, intval( $limit ) ) );
        $offset = max( 0, intval( $offset ) );
        $where  = "WHERE session_id <> ''";
        $args   = array();

        if ( '' !== $search ) {
            $like    = '%' . $wpdb->esc_like( $search ) . '%';
            $where  .= " AND session_id IN (
                SELECT DISTINCT session_id
                FROM {$table}
                WHERE session_id LIKE %s OR question LIKE %s OR answer_summary LIKE %s
            )";
            $args    = array( $like, $like, $like );
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $count_sql = "SELECT COUNT(DISTINCT session_id) FROM {$table} {$where}";
        $total     = empty( $args )
            ? (int) $wpdb->get_var( $count_sql ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            : (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $args ) );

        $query_args   = array_merge( $args, array( $limit, $offset ) );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sessions_sql = "SELECT session_id, MAX(source) AS source,
                MIN(created_at) AS started_at, MAX(created_at) AS last_activity,
                COUNT(*) AS turn_count, MAX(id) AS last_id
            FROM {$table}
            {$where}
            GROUP BY session_id
            ORDER BY last_activity DESC, last_id DESC
            LIMIT %d OFFSET %d";
        $items = $wpdb->get_results( $wpdb->prepare( $sessions_sql, $query_args ), ARRAY_A );
        $items = is_array( $items ) ? $items : array();

        if ( empty( $items ) ) {
            return array( 'items' => array(), 'total' => $total );
        }

        $last_ids     = array_values( array_filter( array_map( 'absint', array_column( $items, 'last_id' ) ) ) );
        $placeholders = implode( ', ', array_fill( 0, count( $last_ids ), '%d' ) );
        $previews     = array();
        if ( '' !== $placeholders ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $preview_sql  = "SELECT id, question, LEFT(answer_summary, 240) AS answer_summary FROM {$table} WHERE id IN ({$placeholders})";
            $preview_rows = $wpdb->get_results( $wpdb->prepare( $preview_sql, $last_ids ), ARRAY_A );
            foreach ( (array) $preview_rows as $row ) {
                $question = trim( (string) ( $row['question'] ?? '' ) );
                $answer   = trim( (string) ( $row['answer_summary'] ?? '' ) );
                $previews[ (int) ( $row['id'] ?? 0 ) ] = array(
                    'question' => $question,
                    'answer'   => $answer,
                );
            }
        }

        foreach ( $items as &$item ) {
            $preview                  = $previews[ (int) ( $item['last_id'] ?? 0 ) ] ?? array();
            $item['question_preview'] = (string) ( $preview['question'] ?? '' );
            $item['answer_preview']   = (string) ( $preview['answer'] ?? '' );
            $item['preview']          = '' !== $item['answer_preview'] ? $item['answer_preview'] : $item['question_preview'];
        }
        unset( $item );

        return array( 'items' => $items, 'total' => $total );
    }

    /**
     * Return every recorded turn for one frontend session, oldest first.
     *
     * @param string $session_id Frontend session identifier.
     * @return array
     */
    public function get_session_transcript( $session_id ) {
        global $wpdb;

        $this->maybe_ensure_table_schema();
        $session_id = sanitize_text_field( substr( (string) $session_id, 0, 128 ) );
        if ( '' === $session_id ) {
            return array();
        }

        $table = $this->get_table_name();
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT id, session_id, question, answer_summary, source, created_at
            FROM {$table}
            WHERE session_id = %s
            ORDER BY created_at ASC, id ASC";

        $items = $wpdb->get_results( $wpdb->prepare( $sql, $session_id ), ARRAY_A );
        return is_array( $items ) ? $items : array();
    }

    /* ----------------------------------------------------------
     *  Maintenance
     * -------------------------------------------------------- */

    private function maybe_cleanup() {
        // Run cleanup roughly 1 in 50 inserts.
        if ( wp_rand( 1, Niwa_AI_Constants::QUESTION_LOG_CLEANUP_ODDS ) !== 1 ) {
            return;
        }

        global $wpdb;

        $table = $this->get_table_name();
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );

        if ( $total <= self::MAX_ROWS ) {
            return;
        }

        $delete_count = $total - self::MAX_ROWS + self::CLEANUP_BATCH;
        // Keep transcripts intact: evict complete oldest sessions instead of
        // cutting the first turns off conversations that remain visible.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $oldest = $wpdb->get_results(
            "SELECT session_id, COUNT(*) AS row_count
             FROM {$table}
             GROUP BY session_id
             ORDER BY MAX(created_at) ASC, MAX(id) ASC
             LIMIT 500",
            ARRAY_A
        );
        $session_ids = array();
        $rows         = 0;
        foreach ( (array) $oldest as $session ) {
            $session_id = (string) ( $session['session_id'] ?? '' );
            if ( '' === $session_id ) {
                continue;
            }
            $session_ids[] = $session_id;
            $rows          += max( 1, intval( $session['row_count'] ?? 0 ) );
            if ( $rows >= $delete_count ) {
                break;
            }
        }

        if ( empty( $session_ids ) ) {
            return;
        }

        $placeholders = implode( ', ', array_fill( 0, count( $session_ids ), '%s' ) );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE session_id IN ({$placeholders})", $session_ids ) );
    }
}
