<?php
/**
 * SMS Subscriber Model
 *
 * Manages the wp_af_sms_subscribers table for storing user phone numbers
 * and per-notification-type opt-in preferences.
 *
 * @package AuctionForge
 * @since 3.6.0.18
 */

namespace auctionforge\inc\model;

use auctionforge\inc\InstanceDB;

class SMSSubscriber
{
    const TABLE_NAME = 'af_sms_subscribers';

    /**
     * Create the SMS subscribers table if it doesn't exist.
     */
    public static function createTable()
    {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE_NAME;
        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS $table (
            id INT AUTO_INCREMENT PRIMARY KEY,
            bidspirit_user_email VARCHAR(255) NOT NULL,
            phone_number VARCHAR(20) NOT NULL,
            country_code VARCHAR(5) DEFAULT '1',
            sms_opt_in TINYINT(1) DEFAULT 1,
            opt_in_auction_published TINYINT(1) DEFAULT 1,
            opt_in_auction_starts_soon TINYINT(1) DEFAULT 1,
            opt_in_item_live_soon TINYINT(1) DEFAULT 1,
            opt_in_outbid TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_email (bidspirit_user_email)
        ) $charset;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * Get all opted-in subscribers for a specific notification type.
     *
     * @param string $notificationType One of: auction_published, auction_starts_soon, item_live_soon, outbid
     * @return array Array of objects with phone_number, country_code, bidspirit_user_email
     */
    public static function getOptedInPhones($notificationType)
    {
        $db = InstanceDB::getInstanceDB();
        $table = $db->prefix . self::TABLE_NAME;

        $column = self::getOptInColumn($notificationType);
        if (!$column) {
            return [];
        }

        $sql = $db->prepare(
            "SELECT phone_number, country_code, bidspirit_user_email 
             FROM $table 
             WHERE sms_opt_in = 1 AND $column = 1 AND phone_number != ''",
        );

        return $db->get_results($sql);
    }

    /**
     * Resolve phone numbers from an array of BidSpirit user emails.
     * Only returns subscribers who are opted in for the given notification type.
     *
     * @param array  $emails            Array of BidSpirit user email addresses
     * @param string $notificationType  Notification type for opt-in check
     * @return array Array of objects with phone_number, country_code, bidspirit_user_email
     */
    public static function resolvePhonesByEmails($emails, $notificationType)
    {
        if (empty($emails)) {
            return [];
        }

        $db = InstanceDB::getInstanceDB();
        $table = $db->prefix . self::TABLE_NAME;

        $column = self::getOptInColumn($notificationType);
        if (!$column) {
            return [];
        }

        // Sanitize emails
        $sanitized = array_map(function($email) {
            return sanitize_email($email);
        }, $emails);
        $sanitized = array_filter($sanitized);

        if (empty($sanitized)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($sanitized), '%s'));
        $sql = $db->prepare(
            "SELECT phone_number, country_code, bidspirit_user_email 
             FROM $table 
             WHERE sms_opt_in = 1 AND $column = 1 AND phone_number != ''
             AND bidspirit_user_email IN ($placeholders)",
            ...$sanitized
        );

        return $db->get_results($sql);
    }

    /**
     * Upsert a subscriber record (insert or update).
     *
     * @param string $email       BidSpirit user email
     * @param string $phone       Phone number (digits only)
     * @param string $countryCode Country calling code (e.g. "1" for US)
     * @param array  $optIns      Associative array of opt-in preferences
     * @return bool
     */
    public static function upsert($email, $phone, $countryCode = '1', $optIns = [])
    {
        $db = InstanceDB::getInstanceDB();
        $table = $db->prefix . self::TABLE_NAME;

        // Sanitize
        $email = sanitize_email($email);
        $phone = preg_replace('/[^0-9]/', '', $phone);
        $countryCode = preg_replace('/[^0-9]/', '', $countryCode);

        if (empty($email) || empty($phone)) {
            return false;
        }

        // Check if record exists
        $existing = $db->get_row($db->prepare(
            "SELECT id FROM $table WHERE bidspirit_user_email = %s",
            $email
        ));

        $data = [
            'bidspirit_user_email'      => $email,
            'phone_number'              => $phone,
            'country_code'              => $countryCode ?: '1',
            'sms_opt_in'                => isset($optIns['sms_opt_in']) ? intval($optIns['sms_opt_in']) : 1,
            'opt_in_auction_published'  => isset($optIns['auction_published']) ? intval($optIns['auction_published']) : 1,
            'opt_in_auction_starts_soon'=> isset($optIns['auction_starts_soon']) ? intval($optIns['auction_starts_soon']) : 1,
            'opt_in_item_live_soon'     => isset($optIns['item_live_soon']) ? intval($optIns['item_live_soon']) : 1,
            'opt_in_outbid'             => isset($optIns['outbid']) ? intval($optIns['outbid']) : 1,
        ];

        if ($existing) {
            unset($data['bidspirit_user_email']); // Don't update the key
            return $db->update($table, $data, ['id' => $existing->id]) !== false;
        } else {
            return $db->insert($table, $data) !== false;
        }
    }

    /**
     * Get a subscriber by BidSpirit email.
     *
     * @param string $email
     * @return object|null
     */
    public static function getByEmail($email)
    {
        $db = InstanceDB::getInstanceDB();
        $table = $db->prefix . self::TABLE_NAME;

        return $db->get_row($db->prepare(
            "SELECT * FROM $table WHERE bidspirit_user_email = %s",
            sanitize_email($email)
        ));
    }

    /**
     * Delete a subscriber by email (full opt-out).
     *
     * @param string $email
     * @return bool
     */
    public static function deleteByEmail($email)
    {
        $db = InstanceDB::getInstanceDB();
        $table = $db->prefix . self::TABLE_NAME;

        return $db->delete($table, ['bidspirit_user_email' => sanitize_email($email)]) !== false;
    }

    /**
     * Get total subscriber count.
     *
     * @return int
     */
    public static function getCount()
    {
        $db = InstanceDB::getInstanceDB();
        $table = $db->prefix . self::TABLE_NAME;

        return (int) $db->get_var("SELECT COUNT(*) FROM $table WHERE sms_opt_in = 1 AND phone_number != ''");
    }

    /**
     * Map notification type string to database column name.
     *
     * @param string $type
     * @return string|null
     */
    private static function getOptInColumn($type)
    {
        $map = [
            'auction_published'    => 'opt_in_auction_published',
            'auction_starts_soon'  => 'opt_in_auction_starts_soon',
            'item_live_soon'       => 'opt_in_item_live_soon',
            'outbid'               => 'opt_in_outbid',
        ];
        return $map[$type] ?? null;
    }
}
