<?php
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin tables; table names are internal constants not user input,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- Custom plugin tables; all table names are internal constants never from user input
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * ACQUIS_P3_DB
 *
 * Phase 3 database layer form builder, analytics, scoring, automation.
 * All write methods use prepared statements and sanitized input.
 */
class ACQUIS_P3_DB {

    // ── Table name helpers ─────────────────────────────────────────────────
    public static function forms_table()          { global $wpdb; return $wpdb->prefix . 'acquis_forms'; }
    public static function form_fields_table()    { global $wpdb; return $wpdb->prefix . 'acquis_form_fields'; }
    public static function submissions_table()    { global $wpdb; return $wpdb->prefix . 'acquis_submissions'; }
    public static function sub_values_table()     { global $wpdb; return $wpdb->prefix . 'acquis_submission_values'; }
    public static function analytics_table()      { global $wpdb; return $wpdb->prefix . 'acquis_analytics'; }

    // ── Pro table name helpers (referenced by Pro code via this shared class) ───
    public static function lead_scores_table()    { global $wpdb; return $wpdb->prefix . 'acquis_lead_scores'; }
    public static function scoring_rules_table()  { global $wpdb; return $wpdb->prefix . 'acquis_scoring_rules'; }
    public static function automation_rules_table(){ global $wpdb; return $wpdb->prefix . 'acquis_automation_rules'; }
    public static function automation_log_table() { global $wpdb; return $wpdb->prefix . 'acquis_automation_log'; }

    // ── Schema install ──────────────────────────────────────────────────────
    public static function create_tables() {
        // Tables are created by ACQUIS_Activator::create_tables() on plugin activation.
        // This method is kept for backward compatibility.
        ACQUIS_Activator::create_tables();
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  FORMS
    // ══════════════════════════════════════════════════════════════════════════
    public static function create_form( $title, $description = '', $steps = 1, $settings = array() ) {
        global $wpdb;
        $wpdb->insert( self::forms_table(), array(
            'title'       => sanitize_text_field( $title ),
            'description' => sanitize_textarea_field( $description ),
            'steps'       => max( 1, absint( $steps ) ),
            'settings'    => wp_json_encode( $settings ),
            'status'      => 'active',
        ), array( '%s', '%s', '%d', '%s', '%s' ) );
        return $wpdb->insert_id ?: false;
    }

    public static function update_form( $id, $data ) {
        global $wpdb;
        $id = absint( $id );
        if ( ! $id ) return false;

        $clean = array();
        if ( isset( $data['title'] ) )       $clean['title']       = sanitize_text_field( $data['title'] );
        if ( isset( $data['description'] ) ) $clean['description'] = sanitize_textarea_field( $data['description'] );
        if ( isset( $data['steps'] ) )       $clean['steps']       = max( 1, absint( $data['steps'] ) );
        if ( isset( $data['status'] ) )      $clean['status']      = in_array( $data['status'], array('active','draft','archived'), true ) ? $data['status'] : 'active';

        // Settings are merged on top of any existing settings rather than
        // replacing the whole blob, so a partial save (e.g. saving just one
        // field from the builder canvas) never wipes out previously saved
        // form-level settings such as the email template override.
        if ( isset( $data['settings'] ) && is_array( $data['settings'] ) ) {
            $existing = array();
            $current  = $wpdb->get_var( $wpdb->prepare( "SELECT settings FROM %i WHERE id=%d", self::forms_table(), $id ) );
            if ( $current ) {
                $decoded = json_decode( $current, true );
                if ( is_array( $decoded ) ) $existing = $decoded;
            }
            $clean['settings'] = wp_json_encode( array_merge( $existing, $data['settings'] ) );
        }

        if ( empty( $clean ) ) return false;

        $clean['updated_at'] = current_time( 'mysql' );

        $result = $wpdb->update( self::forms_table(), $clean, array( 'id' => $id ) );

        // $wpdb->update() returns the number of rows changed, or FALSE on
        // DB error, or 0 if the values were identical to what's already
        // stored. Treat "0 rows changed because nothing was different" as
        // success so the admin UI doesn't report a false failure.
        return false !== $result;
    }

    public static function get_form( $id ) {
        global $wpdb;
        $form = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id=%d", self::forms_table(), absint($id) ) );
        if ( $form ) {
            $form->settings = json_decode( $form->settings ?: '{}', true );
            $form->fields   = self::get_form_fields( $form->id );
        }
        return $form;
    }

    public static function get_forms( $status = '' ) {
        global $wpdb;
        if ( $status ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; $status via %s
            return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE status = %s ORDER BY created_at DESC', self::forms_table(), $status ) );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; no user input
        return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC', self::forms_table() ) );
    }

    public static function delete_form( $id ) {
        global $wpdb;
        $wpdb->delete( self::form_fields_table(), array( 'form_id' => absint($id) ) );
        $wpdb->delete( self::forms_table(), array( 'id' => absint($id) ) );
    }

    public static function duplicate_form( $id ) {
        $original = self::get_form( $id );
        if ( ! $original ) return false;
        $new_id = self::create_form( $original->title . ' (Copy)', $original->description, $original->steps, $original->settings );
        if ( ! $new_id ) return false;
        foreach ( $original->fields as $field ) {
            self::save_field( $new_id, (array) $field );
        }
        return $new_id;
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  FORM FIELDS
    // ══════════════════════════════════════════════════════════════════════════
    public static function get_field( $field_id ) {
        global $wpdb;
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM %i WHERE id=%d",
            self::form_fields_table(), absint( $field_id )
        ) );
        if ( ! $row ) return null;
        $row->options    = json_decode( $row->options    ?: '[]', true );
        $row->validation = json_decode( $row->validation ?: '{}', true );
        $row->conditions = json_decode( $row->conditions ?: '{}', true );
        $row->settings   = json_decode( $row->settings   ?: '{}', true );
        return $row;
    }

    public static function save_field( $form_id, $field ) {
        global $wpdb;
        $data = array(
            'form_id'     => absint( $form_id ),
            'field_key'   => sanitize_key( $field['field_key'] ?? 'field_' . uniqid() ),
            'field_type'  => sanitize_text_field( $field['field_type'] ?? 'text' ),
            'label'       => sanitize_text_field( $field['label'] ?? '' ),
            'placeholder' => sanitize_text_field( $field['placeholder'] ?? '' ),
            'help_text'   => sanitize_text_field( $field['help_text'] ?? '' ),
            'options'     => wp_json_encode( $field['options'] ?? array() ),
            'step'        => max( 1, absint( $field['step'] ?? 1 ) ),
            'sort_order'  => absint( $field['sort_order'] ?? 0 ),
            'required'    => absint( $field['required'] ?? 0 ),
            'validation'  => wp_json_encode( $field['validation'] ?? array() ),
            'conditions'  => wp_json_encode( $field['conditions'] ?? array() ),
            'settings'    => wp_json_encode( $field['settings'] ?? array() ),
        );
        $fmt = array( '%d','%s','%s','%s','%s','%s','%s','%d','%d','%d','%s','%s','%s' );
        if ( ! empty( $field['id'] ) ) {
            $wpdb->update( self::form_fields_table(), $data, array( 'id' => absint($field['id']) ), $fmt, array('%d') );
            return absint( $field['id'] );
        }
        $wpdb->insert( self::form_fields_table(), $data, $fmt );
        return $wpdb->insert_id ?: false;
    }

    public static function get_form_fields( $form_id ) {
        global $wpdb;
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i WHERE form_id=%d ORDER BY step ASC, sort_order ASC",
            self::form_fields_table(), absint($form_id)
        ) );
        foreach ( $rows as $r ) {
            $r->options    = json_decode( $r->options    ?: '[]', true );
            $r->validation = json_decode( $r->validation ?: '{}', true );
            $r->conditions = json_decode( $r->conditions ?: '{}', true );
            $r->settings   = json_decode( $r->settings   ?: '{}', true );
        }
        return $rows;
    }

    public static function delete_field( $field_id ) {
        global $wpdb;
        $wpdb->delete( self::form_fields_table(), array( 'id' => absint($field_id) ) );
    }

    public static function reorder_fields( $form_id, $ordered_ids ) {
        global $wpdb;
        foreach ( $ordered_ids as $order => $field_id ) {
            $wpdb->update( self::form_fields_table(), array('sort_order'=>absint($order)), array('id'=>absint($field_id),'form_id'=>absint($form_id)), array('%d'), array('%d','%d') );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  SUBMISSIONS
    // ══════════════════════════════════════════════════════════════════════════
    public static function create_submission( $form_id, $lead_id, $file_id, $meta = array() ) {
        global $wpdb;
        $wpdb->insert( self::submissions_table(), array(
            'form_id'      => absint($form_id),
            'lead_id'      => absint($lead_id),
            'file_id'      => absint($file_id),
            'source_url'   => sanitize_url( $meta['source_url']   ?? '' ),
            'source_post'  => absint( $meta['source_post']  ?? 0 ),
            'referrer'     => sanitize_url( $meta['referrer']     ?? '' ),
            'utm_source'   => sanitize_text_field( $meta['utm_source']   ?? '' ),
            'utm_medium'   => sanitize_text_field( $meta['utm_medium']   ?? '' ),
            'utm_campaign' => sanitize_text_field( $meta['utm_campaign'] ?? '' ),
            'ip_address'   => sanitize_text_field( $meta['ip_address']   ?? '' ),
        ), array('%d','%d','%d','%s','%d','%s','%s','%s','%s','%s') );
        return $wpdb->insert_id ?: false;
    }

    public static function save_submission_values( $submission_id, $values ) {
        global $wpdb;
        foreach ( $values as $field_key => $data ) {
            $wpdb->insert( self::sub_values_table(), array(
                'submission_id' => absint($submission_id),
                'field_key'     => sanitize_key($field_key),
                'field_label'   => sanitize_text_field( $data['label'] ?? $field_key ),
                'value'         => sanitize_textarea_field( $data['value'] ?? '' ),
            ), array('%d','%s','%s','%s') );
        }
    }

    public static function get_submissions( $per_page = 25, $page = 1, $filters = array(), $orderby = 'created_at', $order = 'DESC', $search = '' ) {
        global $wpdb;
        $offset = ($page-1) * $per_page;

        $allowed_orderby = array('id','created_at','full_name','email','total_score');
        $orderby = in_array($orderby, $allowed_orderby, true) ? $orderby : 'created_at';
        $order   = $order === 'ASC' ? 'ASC' : 'DESC';

        // acquis_lead_scores is a Pro-only table (created by Pro's activator) — Lite must
        // not join it when running standalone, or every submissions query fails outright.
        $pro_active = defined( 'ACQUIS_PRO_VERSION' );

        // Map aliases
        $ob_col = $orderby === 'full_name' ? 'l.full_name' :
                  ($orderby === 'email' ? 'l.email' :
                  ($orderby === 'total_score' ? ($pro_active ? 'ls.total_score' : 's.created_at') :
                  ($orderby === 'id' ? 's.id' : 's.created_at')));

        $where = ' WHERE 1=1';
        if ( !empty($filters['id']) )       $where .= $wpdb->prepare(' AND s.id=%d',       absint($filters['id']));
        if ( isset($filters['form_id']) && '' !== $filters['form_id'] ) $where .= $wpdb->prepare(' AND s.form_id=%d', absint($filters['form_id']));
        if ( !empty($filters['file_id']) )  $where .= $wpdb->prepare(' AND s.file_id=%d',  absint($filters['file_id']));
        if ( !empty($filters['date_from'])) $where .= $wpdb->prepare(' AND s.created_at>=%s', sanitize_text_field($filters['date_from']));
        if ( !empty($filters['date_to']))   $where .= $wpdb->prepare(' AND s.created_at<=%s', sanitize_text_field($filters['date_to']));
        if ( !empty($search) ) {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where .= $wpdb->prepare(' AND (l.full_name LIKE %s OR l.email LIKE %s)', $like, $like);
        }

        $lt  = ACQUIS_Database::leads_table();
        $st  = self::submissions_table();
        $ft  = self::forms_table();

        $score_select = $pro_active ? 'ls.total_score,' : '0 AS total_score,';
        $score_join   = '';
        if ( $pro_active ) {
            $lsc        = self::lead_scores_table();
            $score_join = "LEFT JOIN {$lsc} ls ON ls.lead_id=s.lead_id";
        }

        return $wpdb->get_results( $wpdb->prepare(
            "SELECT s.*, l.full_name, l.email, p.post_title AS file_name, {$score_select} f.title AS form_title
             FROM {$st} s
             LEFT JOIN {$lt} l ON l.id=s.lead_id
             LEFT JOIN {$wpdb->posts} p ON p.ID=s.file_id
             {$score_join}
             LEFT JOIN {$ft} f ON f.id=s.form_id
             {$where} ORDER BY {$ob_col} {$order} LIMIT %d OFFSET %d",
            $per_page, $offset
        ));
    }

    public static function count_submissions( $filters = array(), $search = '' ) {
        global $wpdb;
        $lt = ACQUIS_Database::leads_table();
        $st = self::submissions_table();
        $where = ' WHERE 1=1';
        if ( isset($filters['form_id']) && '' !== $filters['form_id'] ) $where .= $wpdb->prepare(' AND s.form_id=%d', absint($filters['form_id']));
        if ( !empty($search) ) {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where .= $wpdb->prepare(' AND (l.full_name LIKE %s OR l.email LIKE %s)', $like, $like);
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table names via %i; $where parts built with prepare()
        $sql = "SELECT COUNT(*) FROM " . $wpdb->prepare( '%i', self::submissions_table() ) . " s LEFT JOIN " . $wpdb->prepare( '%i', ACQUIS_Database::leads_table() ) . " l ON l.id=s.lead_id {$where}";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- see above: table names use prepare('%i'); $where uses prepare() for all user values
        return (int) $wpdb->get_var( $sql );
    }

    /**
     * Return submissions grouped by email (one row per lead/email).
     * Each row includes: the lead's latest name/email, their score, a
     * comma-separated list of file names they engaged, the count of
     * submissions, and the most recent submission date.
     * All values passed to SQL go through $wpdb->prepare() with %i/%s/%d/%f.
     *
     * @param  int    $per_page  Rows per page.
     * @param  int    $page      1-based page number.
     * @param  string $search    Name/email search string (already untrusted).
     * @param  string $orderby   Whitelisted column key.
     * @param  string $order     ASC or DESC.
     * @return array
     */
    public static function get_submissions_grouped( $per_page = 25, $page = 1, $search = '', $orderby = 'last_seen', $order = 'DESC', $form_id = '' ) {
        global $wpdb;

        $offset = ( absint( $page ) - 1 ) * absint( $per_page );

        // Whitelist ORDER BY to prevent SQL injection
        $allowed_ob = array( 'last_seen', 'full_name', 'email', 'submission_count', 'total_score' );
        $orderby    = in_array( $orderby, $allowed_ob, true ) ? $orderby : 'last_seen';
        $order      = 'ASC' === strtoupper( $order ) ? 'ASC' : 'DESC';

        $lt = ACQUIS_Database::leads_table();
        $st = self::submissions_table();

        // acquis_lead_scores is a Pro-only table (created by Pro's activator) — Lite must
        // not join it when running standalone, or every submissions query fails outright.
        $pro_active = defined( 'ACQUIS_PRO_VERSION' );

        // Build WHERE — user search uses esc_like() + %s placeholder
        $where_parts = array( '1=1' );
        $where_args  = array();
        if ( '' !== $search ) {
            $like          = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
            $where_parts[] = '( l.full_name LIKE %s OR l.email LIKE %s )';
            $where_args[]  = $like;
            $where_args[]  = $like;
        }
        if ( '' !== $form_id && is_numeric( $form_id ) ) {
            $where_parts[] = 's.form_id = %d';
            $where_args[]  = absint( $form_id );
        }
        $where_sql = implode( ' AND ', $where_parts );

        if ( $pro_active ) {
            $lsc            = self::lead_scores_table();
            $score_select   = 'COALESCE( ls.total_score, 0 ) AS total_score,';
            $score_join     = 'LEFT  JOIN %i ls ON ls.lead_id = l.id';
            $group_by_extra = ', ls.total_score';
            $table_args     = array( $lt, $st, $lsc );
        } else {
            $score_select   = '0 AS total_score,';
            $score_join     = '';
            $group_by_extra = '';
            $table_args     = array( $lt, $st );
        }

        // GROUP BY l.id to get one row per lead.
        // GROUP_CONCAT aggregates every distinct file title across all that
        // lead's submissions — no user input enters the SQL string itself.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $lt/$st/$lsc from $wpdb->prefix + hardcoded string; $where_sql built entirely from prepare() and trusted literals; $orderby whitelisted; $order whitelisted
        $sql = $wpdb->prepare(
            "SELECT
                l.id            AS lead_id,
                l.full_name,
                l.email,
                l.created_at    AS first_seen,
                MAX( s.created_at ) AS last_seen,
                COUNT( s.id )   AS submission_count,
                {$score_select}
                GROUP_CONCAT(
                    DISTINCT COALESCE( p.post_title, CONCAT( '#', s.file_id ) )
                    ORDER BY s.created_at ASC
                    SEPARATOR ', '
                ) AS files_engaged,
                COUNT( DISTINCT s.file_id ) AS file_count
             FROM %i l
             INNER JOIN %i s ON s.lead_id = l.id
             LEFT  JOIN {$wpdb->posts} p ON p.ID = s.file_id AND p.post_status = 'publish'
             {$score_join}
             WHERE {$where_sql}
             GROUP BY l.id, l.full_name, l.email, l.created_at{$group_by_extra}
             ORDER BY {$orderby} {$order}
             LIMIT %d OFFSET %d",
            ...$table_args,
            ...$where_args,
            ...array( absint( $per_page ), absint( $offset ) )
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- built entirely above with prepare()
        return $wpdb->get_results( $sql );
    }

    /**
     * Count distinct leads who have at least one submission. Used for pagination
     * on the grouped submissions view.
     *
     * @param  string $search  Name/email search string.
     * @return int
     */
    public static function count_submissions_grouped( $search = '', $form_id = '' ) {
        global $wpdb;

        $lt = ACQUIS_Database::leads_table();
        $st = self::submissions_table();

        $where_parts = array( '1=1' );
        $where_args  = array();
        if ( '' !== $search ) {
            $like          = '%' . $wpdb->esc_like( sanitize_text_field( $search ) ) . '%';
            $where_parts[] = '( l.full_name LIKE %s OR l.email LIKE %s )';
            $where_args[]  = $like;
            $where_args[]  = $like;
        }
        if ( '' !== $form_id && is_numeric( $form_id ) ) {
            $where_parts[] = 's.form_id = %d';
            $where_args[]  = absint( $form_id );
        }
        $where_sql = implode( ' AND ', $where_parts );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $lt/$st from $wpdb->prefix + hardcoded string; $where_sql built with prepare()
        $sql = $wpdb->prepare(
            "SELECT COUNT( DISTINCT l.id )
             FROM %i l
             INNER JOIN %i s ON s.lead_id = l.id
             WHERE {$where_sql}",
            $lt, $st,
            ...$where_args
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- built above with prepare()
        return (int) $wpdb->get_var( $sql );
    }

    /**
     * Get all individual submissions for a single lead (email-grouped detail view).
     * Returns every submission row with file title, form title, and date.
     *
     * @param  int $lead_id
     * @return array
     */
    public static function get_submissions_for_lead( $lead_id ) {
        global $wpdb;

        $st = self::submissions_table();
        $ft = self::forms_table();

        return $wpdb->get_results( $wpdb->prepare(
            "SELECT s.*, p.post_title AS file_name, f.title AS form_title
             FROM %i s
             LEFT JOIN {$wpdb->posts} p ON p.ID = s.file_id
             LEFT JOIN %i f ON f.id = s.form_id
             WHERE s.lead_id = %d
             ORDER BY s.created_at DESC",
            $st, $ft, absint( $lead_id )
        ) );
    }

    /**
     * Get the latest submission field values for a given lead.
     * Used to show up-to-date contact info in the grouped detail view.
     *
     * @param  int $lead_id
     * @return array  Associative: field_key => latest value
     */
    public static function get_latest_field_values_for_lead( $lead_id ) {
        global $wpdb;

        $st  = self::submissions_table();
        $svt = self::sub_values_table();

        // For each field_key, take the value from the most recent submission.
        // The subquery finds the latest submission_id for each field_key for
        // this lead, then the outer query fetches those rows.
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT sv.field_key, sv.field_label, sv.value, sv.submission_id
             FROM %i sv
             INNER JOIN (
                 SELECT MAX( sv2.id ) AS max_id
                 FROM %i sv2
                 INNER JOIN %i s2 ON s2.id = sv2.submission_id
                 WHERE s2.lead_id = %d
                 GROUP BY sv2.field_key
             ) latest ON latest.max_id = sv.id
             ORDER BY sv.id ASC",
            $svt, $svt, $st, absint( $lead_id )
        ) );

        $out = array();
        foreach ( $rows as $row ) {
            $out[ $row->field_key ] = $row;
        }
        return $out;
    }

    public static function get_submission_values( $submission_id ) {
        global $wpdb;
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i WHERE submission_id=%d ORDER BY id ASC",
            self::sub_values_table(), absint($submission_id)
        ));
    }

    /**
     * All files a lead has engaged with (submitted a form to access),
     * ordered most recent first. Used in the Submission Detail view.
     *
     * @param  int $lead_id
     * @return array
     */
    public static function get_files_for_lead( $lead_id ) {
        global $wpdb;
        $st = self::submissions_table();
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT s.id AS submission_id, s.file_id, s.created_at, p.post_title AS file_name
             FROM {$st} s
             LEFT JOIN {$wpdb->posts} p ON p.ID = s.file_id
             WHERE s.lead_id = %d AND s.file_id > 0
             GROUP BY s.file_id
             ORDER BY s.created_at DESC",
            absint( $lead_id )
        ) );
    }

    /**
     * Find the most recent submission for a given lead + file pair.
     * Used by the download controller to attribute a download event back
     * to the form (and source page) that generated the original lead.
     *
     * @param  int $lead_id
     * @param  int $file_id
     * @return object|null
     */
    public static function get_submission_for_lead_and_file( $lead_id, $file_id ) {
        global $wpdb;
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM %i WHERE lead_id=%d AND file_id=%d ORDER BY id DESC LIMIT 1",
            self::submissions_table(), absint( $lead_id ), absint( $file_id )
        ) );
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  ANALYTICS
    // ══════════════════════════════════════════════════════════════════════════
    public static function track_event( $event_type, $data = array() ) {
        global $wpdb;
        $valid = array('form_view','form_start','form_submit','download','email_open','email_click');
        if ( !in_array($event_type, $valid, true) ) return;
        $wpdb->insert( self::analytics_table(), array(
            'event_type'  => $event_type,
            'form_id'     => absint($data['form_id']     ?? 0),
            'file_id'     => absint($data['file_id']     ?? 0),
            'lead_id'     => absint($data['lead_id']     ?? 0),
            'source_post' => absint($data['source_post'] ?? 0),
            'source_url'  => sanitize_url($data['source_url']   ?? ''),
            'utm_source'  => sanitize_text_field($data['utm_source']   ?? ''),
            'utm_medium'  => sanitize_text_field($data['utm_medium']   ?? ''),
            'utm_campaign'=> sanitize_text_field($data['utm_campaign'] ?? ''),
            'ip_address'  => sanitize_text_field($data['ip']           ?? ''),
        ), array('%s','%d','%d','%d','%d','%s','%s','%s','%s','%s'));
    }

    public static function get_analytics_summary( $days = 30 ) {
        global $wpdb;
        $since = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
        $at    = self::analytics_table();
        $st    = self::submissions_table();
        $lt    = ACQUIS_Database::leads_table();

        return array(
            'total_leads'       => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $lt ) ),
            'total_downloads'   => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE event_type = %s AND created_at >= %s', self::analytics_table(), 'download',    $since ) ),
            'total_views'       => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE event_type = %s AND created_at >= %s', self::analytics_table(), 'form_view',   $since ) ),
            'total_submissions' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s',                     self::submissions_table(),                $since ) ),
            'emails_sent'       => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', ACQUIS_Database::email_logs_table(), 'sent' ) ),
            'conversion_rate'   => self::calc_conversion_rate( $since ),
            'top_files'         => self::get_top_files( 5, $since ),
            'lead_sources'      => self::get_lead_sources( 10, $since ),
            'daily_leads'       => self::get_daily_counts( 'form_submit', $days ),
            'daily_downloads'   => self::get_daily_counts( 'download', $days ),
        );
    }

    private static function calc_conversion_rate( $since ) {
        global $wpdb;
        $views = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE event_type = %s AND created_at >= %s', self::analytics_table(), 'form_view',   $since ) );
        $subs  = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE event_type = %s AND created_at >= %s', self::analytics_table(), 'form_submit', $since ) );
        return $views > 0 ? round( ( $subs / $views ) * 100, 1 ) : 0;
    }

    /**
     * Return total download counts for every file that has at least one
     * tracked download, keyed by file_id. Used on the Upload Files page.
     *
     * @return array<int,int> file_id => download_count
     */
    public static function get_download_counts_by_file() {
        global $wpdb;
        $rows = $wpdb->get_results(
            "SELECT file_id, COUNT(*) AS downloads FROM " . self::analytics_table() . "
             WHERE event_type='download' AND file_id>0
             GROUP BY file_id"
        );
        $out = array();
        foreach ( $rows as $r ) {
            $out[ absint( $r->file_id ) ] = absint( $r->downloads );
        }
        return $out;
    }

    /**
     * Total download count for a single file.
     */
    public static function get_download_count_for_file( $file_id ) {
        global $wpdb;
        return (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE event_type='download' AND file_id=%d",
            self::analytics_table(), absint( $file_id )
        ) );
    }

    private static function get_top_files($limit, $since) {
        global $wpdb;
        return $wpdb->get_results($wpdb->prepare(
            "SELECT a.file_id, p.post_title AS file_name, COUNT(*) AS downloads
             FROM %i a LEFT JOIN {$wpdb->posts} p ON p.ID=a.file_id
             WHERE a.event_type='download' AND a.created_at>=%s AND a.file_id>0
             GROUP BY a.file_id ORDER BY downloads DESC LIMIT %d",
            self::analytics_table(), $since, $limit
        ));
    }

    private static function get_lead_sources($limit, $since) {
        global $wpdb;
        return $wpdb->get_results($wpdb->prepare(
            "SELECT source_post, COUNT(*) AS count FROM %i
             WHERE event_type='form_submit' AND created_at>=%s AND source_post>0
             GROUP BY source_post ORDER BY count DESC LIMIT %d",
            self::analytics_table(), $since, $limit
        ));
    }

    public static function get_daily_counts($event_type, $days) {
        global $wpdb;
        $since = gmdate('Y-m-d', strtotime("-{$days} days"));
        return $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(created_at) AS day, COUNT(*) AS count
             FROM %i WHERE event_type=%s AND created_at>=%s
             GROUP BY DATE(created_at) ORDER BY day ASC",
            self::analytics_table(), $event_type, $since
        ));
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  LEAD SCORING
    // ══════════════════════════════════════════════════════════════════════════
    public static function save_scoring_rule( $data ) {
        global $wpdb;
        $clean = array(
            'name'       => sanitize_text_field($data['name'] ?? ''),
            'field_key'  => sanitize_key($data['field_key'] ?? ''),
            'operator'   => sanitize_text_field($data['operator'] ?? 'equals'),
            'value'      => sanitize_text_field($data['value'] ?? ''),
            'points'     => (int)($data['points'] ?? 0),
            'active'     => absint($data['active'] ?? 1),
            'sort_order' => absint($data['sort_order'] ?? 0),
        );
        if ( !empty($data['id']) ) {
            $wpdb->update(self::scoring_rules_table(), $clean, array('id'=>absint($data['id'])), array('%s','%s','%s','%s','%d','%d','%d'), array('%d'));
            return absint($data['id']);
        }
        $wpdb->insert(self::scoring_rules_table(), $clean, array('%s','%s','%s','%s','%d','%d','%d'));
        return $wpdb->insert_id ?: false;
    }

    public static function get_scoring_rules( $active_only = false ) {
        global $wpdb;
        if ( $active_only ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; active filtered by integer literal
            return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE active = 1 ORDER BY sort_order ASC', self::scoring_rules_table() ) );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; no user input
        return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY sort_order ASC', self::scoring_rules_table() ) );
    }

    public static function delete_scoring_rule($id) {
        global $wpdb;
        $wpdb->delete(self::scoring_rules_table(), array('id'=>absint($id)));
    }

    public static function upsert_lead_score( $lead_id, $total, $breakdown ) {
        global $wpdb;
        $existing = $wpdb->get_var($wpdb->prepare("SELECT id FROM %i WHERE lead_id=%d", self::lead_scores_table(), absint($lead_id)));
        if ($existing) {
            $wpdb->update(self::lead_scores_table(), array('total_score'=>(int)$total,'breakdown'=>wp_json_encode($breakdown)), array('lead_id'=>absint($lead_id)), array('%d','%s'), array('%d'));
        } else {
            $wpdb->insert(self::lead_scores_table(), array('lead_id'=>absint($lead_id),'total_score'=>(int)$total,'breakdown'=>wp_json_encode($breakdown)), array('%d','%d','%s'));
        }
    }

    public static function get_lead_score( $lead_id ) {
        global $wpdb;
        // acquis_lead_scores is a Pro-only table (created by Pro's activator) — never
        // query it when Lite is running standalone, or callers like the export engine
        // (which invoke this unconditionally) hit a "table doesn't exist" DB error.
        if ( ! defined( 'ACQUIS_PRO_VERSION' ) ) return null;
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE lead_id=%d", self::lead_scores_table(), absint($lead_id)));
        if ($row) $row->breakdown = json_decode($row->breakdown ?: '[]', true);
        return $row;
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  AUTOMATION RULES
    // ══════════════════════════════════════════════════════════════════════════
    public static function save_automation_rule( $data ) {
        global $wpdb;
        $clean = array(
            'name'         => sanitize_text_field($data['name'] ?? 'New Rule'),
            'trigger_type' => sanitize_text_field($data['trigger_type'] ?? 'form_submit'),
            'conditions'   => wp_json_encode($data['conditions'] ?? array()),
            'actions'      => wp_json_encode($data['actions'] ?? array()),
            'active'       => absint($data['active'] ?? 1),
            'run_once'     => absint($data['run_once'] ?? 0),
            'sort_order'   => absint($data['sort_order'] ?? 0),
        );
        if ( !empty($data['id']) ) {
            $wpdb->update(self::automation_rules_table(), $clean, array('id'=>absint($data['id'])), array('%s','%s','%s','%s','%d','%d','%d'), array('%d'));
            return absint($data['id']);
        }
        $wpdb->insert(self::automation_rules_table(), $clean, array('%s','%s','%s','%s','%d','%d','%d'));
        return $wpdb->insert_id ?: false;
    }

    public static function get_automation_rules( $trigger_type = '' ) {
        global $wpdb;
        if ( $trigger_type ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; trigger_type via %s
            $rows = $wpdb->get_results( $wpdb->prepare(
                'SELECT * FROM %i WHERE trigger_type = %s AND active = 1 ORDER BY sort_order ASC',
                self::automation_rules_table(),
                $trigger_type
            ) );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; no user input
            $rows = $wpdb->get_results( $wpdb->prepare(
                'SELECT * FROM %i WHERE active = 1 ORDER BY sort_order ASC',
                self::automation_rules_table()
            ) );
        }
        foreach ( $rows as $r ) {
            $r->conditions = json_decode( $r->conditions ?: '[]', true );
            $r->actions    = json_decode( $r->actions    ?: '[]', true );
        }
        return $rows;
    }

    /**
     * Paginated automation rule listing for the admin "Active Rules"
     * display deliberately a separate method from get_automation_rules()
     * above, which the automation engine and time-based scheduler also
     * call to fetch EVERY active rule when actually running automations;
     * pagination must never apply there, since that would silently skip
     * executing some rules. This method also intentionally includes
     * inactive (paused) rules, so an admin can see and manage them too  
     * the engine-facing method only ever needs active=1 rules.
     *
     * @param  int $per_page
     * @param  int $page
     * @return array<int,object>
     */
    public static function get_automation_rules_paginated( $per_page = 20, $page = 1 ) {
        global $wpdb;
        $offset = max( 0, ( max( 1, $page ) - 1 ) * $per_page );
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM %i ORDER BY sort_order ASC, id DESC LIMIT %d OFFSET %d",
            self::automation_rules_table(), absint( $per_page ), $offset
        ) );
        foreach ( $rows as $r ) {
            $r->conditions = json_decode( $r->conditions ?: '[]', true );
            $r->actions    = json_decode( $r->actions    ?: '[]', true );
        }
        return $rows;
    }

    public static function count_automation_rules() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is an internal constant
        return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i", self::automation_rules_table() ) );
    }

    public static function delete_automation_rule($id) {
        global $wpdb;
        $wpdb->delete(self::automation_rules_table(), array('id'=>absint($id)));
    }

    public static function log_automation( $rule_id, $lead_id, $action_type, $result ) {
        global $wpdb;
        $wpdb->insert(self::automation_log_table(), array(
            'rule_id'     => absint($rule_id),
            'lead_id'     => absint($lead_id),
            'action_type' => sanitize_text_field($action_type),
            'result'      => sanitize_textarea_field($result),
        ), array('%d','%d','%s','%s'));
    }

    // ══════════════════════════════════════════════════════════════════════════
    //  EXPORT
    // ══════════════════════════════════════════════════════════════════════════
    public static function get_leads_for_export( $filters = array() ) {
        global $wpdb;
        $lt = ACQUIS_Database::leads_table();
        $st = self::submissions_table();

        // acquis_lead_scores is a Pro-only table (created by Pro's activator) — Lite must
        // not join it when running standalone, or the export query fails outright.
        $pro_active   = defined( 'ACQUIS_PRO_VERSION' );
        $score_select = $pro_active ? 'ls.total_score,' : '0 AS total_score,';
        $score_join   = '';
        if ( $pro_active ) {
            $ls         = self::lead_scores_table();
            $score_join = "LEFT JOIN $ls ls ON ls.lead_id=l.id";
        }

        $where = ' WHERE 1=1';
        if ( !empty($filters['date_from'])) $where .= $wpdb->prepare(' AND l.created_at>=%s', $filters['date_from']);
        if ( !empty($filters['date_to']))   $where .= $wpdb->prepare(' AND l.created_at<=%s', $filters['date_to']);
        if ( !empty($filters['form_id']))   $where .= $wpdb->prepare(' AND s.form_id=%d', absint($filters['form_id']));
        return $wpdb->get_results(
            "SELECT l.*, s.form_id, s.utm_source, s.utm_medium, s.utm_campaign, s.source_url,
                    {$score_select} p.post_title AS file_name
             FROM $lt l
             LEFT JOIN $st s ON s.lead_id=l.id
             {$score_join}
             LEFT JOIN {$wpdb->posts} p ON p.ID=l.file_id
             $where ORDER BY l.created_at DESC"
        );
    }
}