<?php

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

class BSPP_Purchase_Repository {
    private $table;

    public function __construct() {
        global $wpdb;
        $this->table = $wpdb->prefix . 'bobasoftware_pp_purchases';
    }

    public function create_pending($user_id, $email, $type, $amount, $status = 'pending') {
        global $wpdb;

        $user_id = $user_id !== null ? (int) $user_id : null;
        $email = is_string($email) ? sanitize_email($email) : null;
        $type = sanitize_key($type);
        $amount = $amount !== null ? (float) $amount : null;
        $status = sanitize_key($status);

        if (empty($type) || $status === '') {
            return false;
        }

        $result = $wpdb->insert(
            $this->table,
            [
                'user_id' => $user_id,
                'email' => $email,
                'type' => $type,
                'amount' => $amount,
                'status' => $status,
                'stripe_session_id' => null,
            ]
        );

        if (!$result) {
            return false;
        }

        return (int) $wpdb->insert_id;
    }

    public function update_stripe_session_id($purchase_id, $stripe_session_id) {
        global $wpdb;

        $purchase_id = (int) $purchase_id;
        $stripe_session_id = sanitize_text_field($stripe_session_id);

        if ($purchase_id <= 0 || empty($stripe_session_id)) {
            return false;
        }

        return (bool) $wpdb->update(
            $this->table,
            ['stripe_session_id' => $stripe_session_id],
            ['id' => $purchase_id]
        );
    }

    public function update_status_by_session_id($stripe_session_id, $status) {
        global $wpdb;

        $stripe_session_id = sanitize_text_field($stripe_session_id);
        $status = sanitize_key($status);

        if (empty($stripe_session_id) || empty($status)) {
            return false;
        }

        return (bool) $wpdb->update(
            $this->table,
            ['status' => $status],
            ['stripe_session_id' => $stripe_session_id]
        );
    }

    /**
     * Attach WordPress user and normalized email after Stripe Checkout (webhook).
     */
    public function update_user_and_email_by_session_id($stripe_session_id, $user_id, $email = null) {
        global $wpdb;

        $stripe_session_id = sanitize_text_field($stripe_session_id);
        $user_id = (int) $user_id;

        if (empty($stripe_session_id) || $user_id <= 0) {
            return false;
        }

        $data = [
            'user_id' => $user_id,
        ];
        if ($email !== null && $email !== '') {
            $email = sanitize_email($email);
            if ($email !== '') {
                $data['email'] = $email;
            }
        }

        return (bool) $wpdb->update(
            $this->table,
            $data,
            ['stripe_session_id' => $stripe_session_id]
        );
    }

    public function get_by_id($purchase_id) {
        global $wpdb;
        $purchase_id = (int) $purchase_id;
        if ($purchase_id <= 0) {
            return null;
        }

        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->table} WHERE id = %d LIMIT 1", $purchase_id));
    }

    public function update_status_by_id($purchase_id, $status) {
        global $wpdb;
        $purchase_id = (int) $purchase_id;
        $status = sanitize_key((string) $status);
        if ($purchase_id <= 0 || $status === '') {
            return false;
        }

        return (bool) $wpdb->update($this->table, ['status' => $status], ['id' => $purchase_id]);
    }

    /**
     * Store Payment Intent id after Checkout completes (tip / per-article).
     */
    public function update_payment_intent_by_session_id($stripe_session_id, $stripe_payment_intent_id) {
        global $wpdb;
        $stripe_session_id = sanitize_text_field((string) $stripe_session_id);
        $stripe_payment_intent_id = sanitize_text_field((string) $stripe_payment_intent_id);
        if ($stripe_session_id === '' || $stripe_payment_intent_id === '' || strpos($stripe_payment_intent_id, 'pi_') !== 0) {
            return false;
        }

        return (bool) $wpdb->update(
            $this->table,
            ['stripe_payment_intent_id' => $stripe_payment_intent_id],
            ['stripe_session_id' => $stripe_session_id]
        );
    }

    /**
     * Store Stripe Invoice id for subscription Checkout rows (receipt matching).
     */
    /**
     * Whether this reader has any refunded subscription checkout (used to keep access revoked after Stripe webhooks).
     */
    public function user_has_refunded_subscription($user_id) {
        global $wpdb;

        $user_id = (int) $user_id;
        if ($user_id <= 0) {
            return false;
        }

        $count = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table}
                 WHERE user_id = %d
                 AND status = 'refunded'
                 AND type IN ('subscription', 'subscription_monthly', 'subscription_annual')",
                $user_id
            )
        );

        return ((int) $count) > 0;
    }

    public function update_invoice_by_session_id($stripe_session_id, $stripe_invoice_id) {
        global $wpdb;
        $stripe_session_id = sanitize_text_field((string) $stripe_session_id);
        $stripe_invoice_id = sanitize_text_field((string) $stripe_invoice_id);
        if ($stripe_session_id === '' || $stripe_invoice_id === '' || strpos($stripe_invoice_id, 'in_') !== 0) {
            return false;
        }

        return (bool) $wpdb->update(
            $this->table,
            ['stripe_invoice_id' => $stripe_invoice_id],
            ['stripe_session_id' => $stripe_session_id]
        );
    }
}

