<?php

namespace WPDeveloper\BetterDocsPro\Core;

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

use WPDeveloper\BetterDocs\Core\Roles;
use WPDeveloper\BetterDocs\Core\Settings;
use WPDeveloper\BetterDocs\Utils\Database;
use WPDeveloper\BetterDocs\Dependencies\DI\Container;

class Install {
    /**
     * Container
     * @var Container
     */
    public $container;
    /**
     * Database
     * @var Database
     */
    public $database;

    public function __construct() {
        register_activation_hook( BETTERDOCS_PRO_FILE, [$this, 'activate'] );
        register_deactivation_hook( BETTERDOCS_PRO_FILE, [$this, 'deactivate'] );

        add_action( 'init', [$this, 'init'], -999 );
        add_action( 'init', [$this, 'check_db_updates'], 1 );
        add_action( 'init', [$this, 'check_version'], 5 );
        // After the new tables exist (priority 1), seed them once from the
        // legacy Free analytics table so history from older versions survives.
        add_action( 'init', [$this, 'maybe_backfill_legacy_analytics'], 6 );
        // Expand legacy per-day reaction counts into per-item Feedback Inbox rows
        // so the inbox shows history collected before the update.
        add_action( 'init', [$this, 'maybe_backfill_legacy_feedback'], 7 );
        // Drop backfill rows that duplicate pipeline rows for the same (post, day)
        // on installs that ran the old key-collision-based backfill (QA #15).
        add_action( 'init', [$this, 'maybe_repair_duplicate_daily_rows'], 8 );
    }

    public function init() {
        if( ! class_exists( '\WPDeveloper\BetterDocs\Plugin' ) ) {
            return;
        }

        $this->container = betterdocs()->container;
        $this->database  = $this->container->get( Database::class );

        if ( $this->database->get_transient( 'betterdocs_pro_activated' ) ) {
            // Create DB Tables if not created.
            $this->check_db_updates();

            // Set admin roles
            $this->container->get( Roles::class )->setup( true );

            // Save default settings.
            // $this->container->get( Settings::class )->save_default_settings();

            $this->database->delete_transient( 'betterdocs_pro_activated' );
        }
    }

    public function activate() {
        // Flush all the existing rewrite rules
        set_transient( 'betterdocs_flush_rewrite_rules', true );

        // This flag will re-run some checks after activation of the plugin.
        set_transient( 'betterdocs_pro_activated', true );
    }

    public function deactivate() {
        // Flush all the existing rewrite rules
        set_transient( 'betterdocs_flush_rewrite_rules', true );

        // Remove roles
        if( $this->container !== null ) {
            $this->container->get( Roles::class )->setup( true );
        }

        // Clear the related-docs retention cron so we don't leak orphaned hooks.
        $next = wp_next_scheduled( 'betterdocs_related_docs_cleanup' );
        if ( $next ) {
            wp_unschedule_event( $next, 'betterdocs_related_docs_cleanup' );
        }
    }

    public function check_version() {
        $betterdocs_pro_version  = get_option( 'betterdocs_pro_version', '2.2.7' );
        $betterdocs_code_version = betterdocs_pro()->version;
        $requires_update         = version_compare( $betterdocs_pro_version, $betterdocs_code_version, '<' );

        if ( $requires_update && did_action( 'betterdocs_loaded' ) >= 1 ) {
            // Re-check if any db setup needed
            $this->check_db_updates();

            // Re-check if any migration is needed.
            $this->container->get( Migration::class )->init( str_replace( '.', '', $betterdocs_code_version ) );

            $this->update_version();
        }
    }

    /**
     * Update BetterDocs Pro version to current.
     */
    private function update_version() {
        update_option( 'betterdocs_pro_version', betterdocs_pro()->version );
    }

    public function check_db_updates() {
        global $wpdb;

        $_db_version      = get_option( 'betterdocs_pro_db_version', '1.0' );
        $_db_code_version = betterdocs_pro()->db_version;
        $requires_update  = version_compare( $_db_version, $_db_code_version, '<' );

        if ( ! $requires_update ) {
            return;
        }

        // Create/update database tables
        $this->create_tables();

        // Update DB Version
        update_option( 'betterdocs_pro_db_version', $_db_code_version );
    }

    /**
     * Create database tables for BetterDocs Pro features
     */
    public function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        // Real-time Related Docs Panel - User Journey Tracking Table
        $_user_journeys_table = $wpdb->prefix . 'betterdocs_user_journeys';
        $user_journeys_table = "CREATE TABLE $_user_journeys_table (
            id bigint NOT NULL AUTO_INCREMENT,
            session_id varchar(255) NOT NULL,
            user_id bigint DEFAULT NULL,
            article_id bigint DEFAULT NULL,
            event_type varchar(50) NOT NULL,
            event_data longtext NOT NULL,
            user_agent text,
            ip_address varchar(45),
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY session_id (session_id),
            KEY article_id (article_id),
            KEY event_type (event_type),
            KEY created_at (created_at),
            KEY user_id (user_id)
        ) {$charset_collate};";

        // Real-time Related Docs Panel - AI Suggestions Performance Table
        $_related_suggestions_table = $wpdb->prefix . 'betterdocs_related_suggestions';
        $related_suggestions_table = "CREATE TABLE $_related_suggestions_table (
            id bigint NOT NULL AUTO_INCREMENT,
            article_id bigint NOT NULL,
            suggested_article_id bigint NOT NULL,
            suggestion_type varchar(50) NOT NULL,
            relevance_score decimal(5,2) DEFAULT 0.00,
            click_count int DEFAULT 0,
            impression_count int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY  unique_suggestion (article_id, suggested_article_id, suggestion_type),
            KEY article_id (article_id),
            KEY suggested_article_id (suggested_article_id),
            KEY suggestion_type (suggestion_type),
            KEY relevance_score (relevance_score)
        ) {$charset_collate};";

        // ---- Advanced Analytics v1.0 tables ----

        // Raw event stream (write-only hot path, ~30-day retention).
        $_analytics_events_table = $wpdb->prefix . 'betterdocs_analytics_events';
        $analytics_events_table  = "CREATE TABLE $_analytics_events_table (
            id bigint NOT NULL AUTO_INCREMENT,
            event_type varchar(32) NOT NULL,
            object_id bigint DEFAULT 0 NOT NULL,
            session_hash char(64) DEFAULT NULL,
            kb_id bigint DEFAULT 0 NOT NULL,
            lang varchar(12) DEFAULT NULL,
            payload longtext NOT NULL,
            ip_hash char(64) DEFAULT NULL,
            created_at datetime NOT NULL,
            processed tinyint(1) DEFAULT 0 NOT NULL,
            PRIMARY KEY  (id),
            KEY processed_created (processed, created_at),
            KEY event_type (event_type),
            KEY object_id (object_id)
        ) {$charset_collate};";

        // Per-doc per-day rollup (superset of the Free betterdocs_analytics table).
        $_analytics_daily_table = $wpdb->prefix . 'betterdocs_analytics_daily';
        $analytics_daily_table  = "CREATE TABLE $_analytics_daily_table (
            id bigint NOT NULL AUTO_INCREMENT,
            post_id bigint DEFAULT 0 NOT NULL,
            kb_id bigint DEFAULT 0 NOT NULL,
            lang varchar(12) DEFAULT '' NOT NULL,
            views bigint DEFAULT 0 NOT NULL,
            unique_views bigint DEFAULT 0 NOT NULL,
            happy bigint DEFAULT 0 NOT NULL,
            sad bigint DEFAULT 0 NOT NULL,
            normal bigint DEFAULT 0 NOT NULL,
            avg_scroll_depth decimal(5,2) DEFAULT 0.00 NOT NULL,
            reading_completions bigint DEFAULT 0 NOT NULL,
            stat_date date DEFAULT '0000-00-00' NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY  uniq_post_day (post_id, kb_id, lang, stat_date),
            KEY stat_date (stat_date),
            KEY post_id (post_id)
        ) {$charset_collate};";

        // Search rollup (zero-result, CTR, result-count dimensions).
        $_analytics_search_table = $wpdb->prefix . 'betterdocs_analytics_search';
        $analytics_search_table  = "CREATE TABLE $_analytics_search_table (
            id bigint NOT NULL AUTO_INCREMENT,
            keyword varchar(191) NOT NULL,
            keyword_hash char(32) NOT NULL,
            search_count bigint DEFAULT 0 NOT NULL,
            results_count_avg decimal(8,2) DEFAULT 0.00 NOT NULL,
            zero_result_count bigint DEFAULT 0 NOT NULL,
            click_through_count bigint DEFAULT 0 NOT NULL,
            kb_id bigint DEFAULT 0 NOT NULL,
            lang varchar(12) DEFAULT '' NOT NULL,
            stat_date date DEFAULT '0000-00-00' NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY  uniq_kw_day (keyword_hash, kb_id, lang, stat_date),
            KEY stat_date (stat_date),
            KEY zero_result_count (zero_result_count)
        ) {$charset_collate};";

        // Unified feedback stream for the Inbox (per-event status, bulk actions).
        $_analytics_feedback_table = $wpdb->prefix . 'betterdocs_analytics_feedback';
        $analytics_feedback_table  = "CREATE TABLE $_analytics_feedback_table (
            id bigint NOT NULL AUTO_INCREMENT,
            post_id bigint DEFAULT 0 NOT NULL,
            feeling varchar(16) NOT NULL,
            comment text DEFAULT NULL,
            session_hash char(64) DEFAULT NULL,
            status varchar(16) DEFAULT 'new' NOT NULL,
            kb_id bigint DEFAULT 0 NOT NULL,
            lang varchar(12) DEFAULT '' NOT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY post_id (post_id),
            KEY status (status),
            KEY created_at (created_at)
        ) {$charset_collate};";

        // Link health scan results.
        $_analytics_links_table = $wpdb->prefix . 'betterdocs_analytics_links';
        $analytics_links_table  = "CREATE TABLE $_analytics_links_table (
            id bigint NOT NULL AUTO_INCREMENT,
            post_id bigint DEFAULT 0 NOT NULL,
            url text NOT NULL,
            url_hash char(32) NOT NULL,
            http_status smallint DEFAULT 0 NOT NULL,
            link_type varchar(16) DEFAULT '' NOT NULL,
            is_broken tinyint(1) DEFAULT 0 NOT NULL,
            last_checked datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY  uniq_post_url (post_id, url_hash),
            KEY is_broken (is_broken),
            KEY last_checked (last_checked)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta(
            $user_journeys_table .
            $related_suggestions_table .
            $analytics_events_table .
            $analytics_daily_table .
            $analytics_search_table .
            $analytics_feedback_table .
            $analytics_links_table
        );
    }

    /**
     * One-time backfill of the legacy {prefix}betterdocs_analytics rows into the
     * Advanced-Analytics {prefix}betterdocs_analytics_daily rollup.
     *
     * The new dashboard reads views/article metrics from betterdocs_analytics_daily,
     * which only the forward-looking event pipeline populates. Without this copy,
     * a site that collected analytics on an older (master) version would see an
     * empty Article Performance / Views dashboard after updating. Runs exactly
     * once (guarded by an option) and uses INSERT IGNORE so any (post, day) the
     * new pipeline has already written is left untouched — no double counting.
     */
    public function maybe_backfill_legacy_analytics() {
        if ( get_option( 'betterdocs_analytics_legacy_backfilled' ) ) {
            return;
        }

        global $wpdb;

        $legacy = $wpdb->prefix . 'betterdocs_analytics';
        $daily  = $wpdb->prefix . 'betterdocs_analytics_daily';

        // If either table is missing there is nothing to migrate — mark done.
        $has_legacy = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $legacy ) );
        $has_daily  = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $daily ) );
        if ( ! $has_legacy || ! $has_daily ) {
            update_option( 'betterdocs_analytics_legacy_backfilled', 1, false );
            return;
        }

        // Map legacy columns onto the daily rollup (kb_id/lang default to 0/'').
        // Scroll-depth and reading-completion did not exist in the legacy table.
        // Dedupe on (post_id, stat_date) — NOT the table's 4-column unique key:
        // pipeline rows carry kb_id/lang values, so INSERT IGNORE alone can never
        // collide with them and the same day's views would land twice (QA #15).
        $wpdb->query(
            "INSERT INTO {$daily}
                ( post_id, kb_id, lang, views, unique_views, happy, sad, normal, avg_scroll_depth, reading_completions, stat_date )
             SELECT l.post_id, 0, '', SUM( l.impressions ), SUM( l.unique_visit ), SUM( l.happy ), SUM( l.sad ), SUM( l.normal ), 0.00, 0, l.created_at
             FROM {$legacy} l
             WHERE l.post_id > 0 AND l.created_at <> '0000-00-00'
               AND NOT EXISTS (
                   SELECT 1 FROM {$daily} d
                   WHERE d.post_id = l.post_id AND d.stat_date = l.created_at
               )
             GROUP BY l.post_id, l.created_at"
        );

        update_option( 'betterdocs_analytics_legacy_backfilled', 1, false );
    }

    /**
     * One-shot repair for installs where the original backfill double-counted:
     * its INSERT IGNORE only deduped on the full (post, kb, lang, date) key, so a
     * kb_id=0/lang='' backfill row could coexist with the pipeline's kb/lang row
     * for the same (post, day) — the same visits summed twice in every report.
     * Where such a pipeline twin exists, the backfill row is redundant (Free's
     * tracker and the event pipeline recorded the same views) — drop it.
     */
    public function maybe_repair_duplicate_daily_rows() {
        if ( get_option( 'betterdocs_analytics_daily_deduped' ) ) {
            return;
        }

        global $wpdb;

        $daily = $wpdb->prefix . 'betterdocs_analytics_daily';
        if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $daily ) ) ) {
            update_option( 'betterdocs_analytics_daily_deduped', 1, false );
            return;
        }

        $wpdb->query(
            "DELETE b FROM {$daily} b
             JOIN {$daily} p
               ON p.post_id = b.post_id AND p.stat_date = b.stat_date AND p.id <> b.id
             WHERE b.kb_id = 0 AND b.lang = ''
               AND ( p.kb_id <> 0 OR p.lang <> '' )"
        );

        update_option( 'betterdocs_analytics_daily_deduped', 1, false );
    }

    /**
     * One-time expansion of legacy per-day reaction COUNTS
     * ({prefix}betterdocs_analytics happy/sad/normal) into per-item
     * {prefix}betterdocs_analytics_feedback rows, so the Feedback Inbox shows the
     * reaction history collected before the Advanced-Analytics update (which only
     * captures per-item rows going forward).
     *
     * Legacy data is per-day aggregate counts, so each count of N is fanned out
     * into N synthetic rows. Those rows carry no comment, status = 'resolved'
     * (already-triaged history, so they don't inflate the "New" badge) and
     * created_at = that day at 12:00:00. There is no natural UNIQUE key to dedupe
     * on, so this MUST stay strictly one-shot — guarded by its own option.
     */
    public function maybe_backfill_legacy_feedback() {
        if ( get_option( 'betterdocs_feedback_legacy_backfilled' ) ) {
            return;
        }

        global $wpdb;

        $legacy   = $wpdb->prefix . 'betterdocs_analytics';
        $feedback = $wpdb->prefix . 'betterdocs_analytics_feedback';

        $has_legacy   = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $legacy ) );
        $has_feedback = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $feedback ) );
        if ( ! $has_legacy || ! $has_feedback ) {
            update_option( 'betterdocs_feedback_legacy_backfilled', 1, false );
            return;
        }

        // A 0..99 numbers table lets a COUNT of N fan out into N rows in pure SQL.
        // Per-doc-per-day reaction counts above 99 are clipped (acceptable for a
        // one-time legacy snapshot).
        $tally   = '(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4'
                 . ' UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) ';
        $numbers = "(SELECT ( a.n + b.n * 10 ) AS seq FROM {$tally} a, {$tally} b) num";

        foreach ( [ 'happy', 'sad', 'normal' ] as $feeling ) {
            // $feeling is a hardcoded allowlisted column name — safe to interpolate.
            $wpdb->query(
                $wpdb->prepare(
                    "INSERT INTO {$feedback}
                        ( post_id, feeling, comment, session_hash, status, kb_id, lang, created_at )
                     SELECT l.post_id, %s, NULL, NULL, 'resolved', 0, '', CONCAT( l.created_at, ' 12:00:00' )
                     FROM {$legacy} l
                     JOIN {$numbers} ON num.seq < l.{$feeling}
                     WHERE l.post_id > 0 AND l.created_at <> '0000-00-00' AND l.{$feeling} > 0",
                    $feeling
                )
            );
        }

        update_option( 'betterdocs_feedback_legacy_backfilled', 1, false );
    }
}