<?php
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,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;

class ACQUIS_Database {

    // ── Table Helpers ──────────────────────────────────────────────────────
    public static function leads_table()            { global $wpdb; return $wpdb->prefix . 'acquis_leads'; }
    public static function tokens_table()           { global $wpdb; return $wpdb->prefix . 'acquis_tokens'; }
    public static function email_logs_table()       { global $wpdb; return $wpdb->prefix . 'acquis_email_logs'; }
    public static function integration_logs_table() { global $wpdb; return $wpdb->prefix . 'acquis_integration_logs'; }

    // ── Leads ──────────────────────────────────────────────────────────────
    public static function insert_lead( $full_name, $email, $file_id ) {
        global $wpdb;
        $result = $wpdb->insert(
            self::leads_table(),
            array(
                'full_name'  => sanitize_text_field( $full_name ),
                'email'      => sanitize_email( $email ),
                'file_id'    => absint( $file_id ),
                'ip_address' => self::get_client_ip(),
                // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- isset() check only; sanitized via sanitize_text_field()/wp_unslash() below
                'user_agent' => isset( $_SERVER['HTTP_USER_AGENT'] )
                                    ? sanitize_text_field( substr( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ), 0, 500 ) )
                                    : '',
            ),
            array( '%s', '%s', '%d', '%s', '%s' )
        );
        return $result ? $wpdb->insert_id : false;
    }

    public static function get_lead( $lead_id ) {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM %i WHERE id = %d", self::leads_table(), absint( $lead_id ) )
        );
    }

    /**
     * Most recent lead row matching a given email, or null if this
     * email has never been captured before. Used wherever a flow needs
     * to attach to an existing lead rather than always creating a new
     * one for the same person (e.g. a repeat buyer in the Payments
     * module) a generically useful lookup, not specific to any one
     * module.
     */
    public static function get_lead_by_email( $email ) {
        global $wpdb;
        $email = sanitize_email( $email );
        if ( ! is_email( $email ) ) {
            return null;
        }
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM %i WHERE email = %s ORDER BY id DESC LIMIT 1",
            self::leads_table(), $email
        ) );
    }

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

        $allowed = array('id','full_name','email','file_id','created_at');
        $orderby = in_array($orderby, $allowed, true) ? $orderby : 'created_at';
        $order   = $order === 'ASC' ? 'ASC' : 'DESC';
        $ob_col  = 'l.' . $orderby;

        $where = ' WHERE 1=1';
        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 = self::leads_table();
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT l.*, p.post_title AS file_name
             FROM {$lt} l
             LEFT JOIN {$wpdb->posts} p ON p.ID = l.file_id
             {$where}
             ORDER BY {$ob_col} {$order}
             LIMIT %d OFFSET %d",
            $per_page, $offset
        ) );
    }

    public static function count_leads( $search = '' ) {
        global $wpdb;
        $lt = self::leads_table();
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $lt is an internal constant
        if ( !empty($search) ) {
            $like = '%' . $wpdb->esc_like($search) . '%';
            return (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM {$lt} WHERE full_name LIKE %s OR email LIKE %s",
                $like, $like
            ) );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- count_leads called without search; table name passed via %i placeholder below
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::leads_table() ) );
    }

    // ── Tokens ─────────────────────────────────────────────────────────────
    public static function create_token( $lead_id, $file_id ) {
        global $wpdb;
        $token      = bin2hex( random_bytes( 32 ) );
        $expiry_min = absint( get_option( 'acquis_link_expiry_minutes', 30 ) );
        $expires_at = gmdate( 'Y-m-d H:i:s', time() + ( $expiry_min * 60 ) );
        $result = $wpdb->insert(
            self::tokens_table(),
            array( 'token' => $token, 'lead_id' => absint( $lead_id ), 'file_id' => absint( $file_id ), 'expires_at' => $expires_at ),
            array( '%s', '%d', '%d', '%s' )
        );
        return $result ? $token : false;
    }

    public static function validate_token( $token ) {
        global $wpdb;
        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE token = %s AND used = 0 AND expires_at > UTC_TIMESTAMP()",
                self::tokens_table(), sanitize_text_field( $token )
            )
        );
    }

    public static function mark_token_used( $token ) {
        global $wpdb;
        $wpdb->update( self::tokens_table(), array( 'used' => 1 ), array( 'token' => sanitize_text_field( $token ) ), array( '%d' ), array( '%s' ) );
    }

    public static function purge_expired_tokens() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; no user input in query
        $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE expires_at < UTC_TIMESTAMP() OR used = 1', self::tokens_table() ) );
    }

    // ── Email Logs ─────────────────────────────────────────────────────────
    public static function log_email( $lead_id, $email, $subject, $status = 'queued', $provider = 'wp_mail', $error_msg = '' ) {
        global $wpdb;
        $result = $wpdb->insert(
            self::email_logs_table(),
            array(
                'lead_id'   => absint( $lead_id ),
                'email'     => sanitize_email( $email ),
                'subject'   => sanitize_text_field( $subject ),
                'status'    => in_array( $status, array( 'sent', 'failed', 'queued' ), true ) ? $status : 'queued',
                'provider'  => sanitize_text_field( $provider ),
                'error_msg' => sanitize_textarea_field( $error_msg ),
            ),
            array( '%d', '%s', '%s', '%s', '%s', '%s' )
        );
        return $result ? $wpdb->insert_id : false;
    }

    public static function update_email_log_status( $log_id, $status, $error_msg = '' ) {
        global $wpdb;
        $wpdb->update(
            self::email_logs_table(),
            array( 'status' => $status, 'error_msg' => sanitize_textarea_field( $error_msg ) ),
            array( 'id' => absint( $log_id ) ),
            array( '%s', '%s' ),
            array( '%d' )
        );
    }

    public static function get_email_logs( $per_page = 25, $page = 1 ) {
        global $wpdb;
        $offset = ( $page - 1 ) * $per_page;
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT el.*, l.full_name FROM %i el
                 LEFT JOIN %i l ON l.id = el.lead_id
                 ORDER BY el.created_at DESC LIMIT %d OFFSET %d",
                self::email_logs_table(), self::leads_table(), $per_page, $offset
            )
        );
    }

    public static function count_email_logs( $status = '' ) {
        global $wpdb;
        if ( $status ) {
            return (int) $wpdb->get_var(
                $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = %s", self::email_logs_table(), $status )
            );
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name via %i; no user input
        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::email_logs_table() ) );
    }

    // ── Integration Logs ───────────────────────────────────────────────────
    public static function log_integration( $lead_id, $integration, $status, $response = '' ) {
        global $wpdb;
        $wpdb->insert(
            self::integration_logs_table(),
            array(
                'lead_id'     => absint( $lead_id ),
                'integration' => sanitize_text_field( $integration ),
                'status'      => in_array( $status, array( 'success', 'failed', 'skipped' ), true ) ? $status : 'skipped',
                'response'    => sanitize_textarea_field( $response ),
            ),
            array( '%d', '%s', '%s', '%s' )
        );
    }

    /**
     * The most recent email log entry sent to a given address, or null.
     * Used by the Orders admin page to show whether a purchase receipt
     * was actually sent (and whether it succeeded) directly alongside
     * each order, rather than requiring direct database access to
     * answer "was an email even attempted for this purchase".
     */
    public static function get_latest_email_log_for( $email ) {
        global $wpdb;
        $email = sanitize_email( $email );
        if ( ! is_email( $email ) ) {
            return null;
        }
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM %i WHERE email = %s ORDER BY id DESC LIMIT 1",
            self::email_logs_table(), $email
        ) );
    }

    // ── Helpers ────────────────────────────────────────────────────────────
    private static function get_client_ip() {
        // For analytics/logging only — NOT used for security gating or rate limiting.
        // Proxy headers are acceptable here as IP accuracy is best-effort for analytics.
        $keys = array( 'HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'REMOTE_ADDR' );
        foreach ( $keys as $key ) {
            // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash,WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- IP address is validated via filter_var
            if ( ! empty( $_SERVER[ $key ] ) ) {
                $ip = trim( explode( ',', sanitize_text_field( wp_unslash( $_SERVER[ $key ] ) ) )[0] );
                if ( filter_var( $ip, FILTER_VALIDATE_IP ) ) return $ip;
            }
        }
        return '0.0.0.0';
    }
}