<?php
namespace EasyChat\Model;

use EasyChat\Library\AppClock;

class OutboundContact extends Base {

    public function create(array $data): int {
        $clock = new AppClock($this->registry);
        $phone = (string)($data['phone'] ?? '');
        $client_request_id = trim((string)($data['client_request_id'] ?? ''));
        $payload_hash = strtolower(trim((string)($data['payload_hash'] ?? '')));
        $client_request_sql = $client_request_id !== ''
            ? "'" . $this->db->escape($client_request_id) . "'"
            : 'NULL';
        $this->db->query(
            "INSERT INTO `{$this->prefix}outbound_contact_requests`
             SET `public_id` = '" . $this->db->escape($this->publicId()) . "',
                 `client_request_id` = {$client_request_sql},
                 `payload_hash` = '" . $this->db->escape(preg_match('/^[a-f0-9]{64}$/D', $payload_hash) ? $payload_hash : '') . "',
                 `channel` = '" . $this->db->escape((string)($data['channel'] ?? 'max')) . "',
                 `status` = '" . $this->db->escape((string)($data['status'] ?? 'new')) . "',
                 `phone_e164` = '" . $this->db->escape($phone) . "',
                 `phone_hash` = '" . $this->db->escape($this->phoneHash($phone)) . "',
                 `customer_name` = '" . $this->db->escape((string)($data['customer_name'] ?? '')) . "',
                 `reason` = '" . $this->db->escape((string)($data['reason'] ?? 'manual')) . "',
                 `message_text` = '" . $this->db->escape((string)($data['message_text'] ?? '')) . "',
                 `manager_id` = " . (int)($data['manager_id'] ?? 0) . ",
                 `chat_id` = " . ((int)($data['chat_id'] ?? 0) > 0 ? (int)$data['chat_id'] : 'NULL') . ",
                 `messenger_contact_id` = " . ((int)($data['messenger_contact_id'] ?? 0) > 0 ? (int)$data['messenger_contact_id'] : 'NULL') . ",
                 `message_id` = " . ((int)($data['message_id'] ?? 0) > 0 ? (int)$data['message_id'] : 'NULL') . ",
                 `delivery_id` = " . ((int)($data['delivery_id'] ?? 0) > 0 ? (int)$data['delivery_id'] : 'NULL') . ",
                 `app_session_id` = " . ((int)($data['app_session_id'] ?? 0) > 0 ? (int)$data['app_session_id'] : 'NULL') . ",
                 `last_error` = '" . $this->db->escape((string)($data['last_error'] ?? '')) . "',
                 `date_added` = '" . $this->db->escape($clock->nowSql()) . "',
                 `date_modified` = '" . $this->db->escape($clock->nowSql()) . "'"
        );
        return (int)$this->db->getLastId();
    }

    public function update(int $request_id, array $data): void {
        $parts = [];
        foreach ($data as $key => $value) {
            if (in_array($key, ['chat_id', 'messenger_contact_id', 'message_id', 'delivery_id', 'app_session_id', 'attempt_count'], true)) {
                $parts[] = "`" . $this->db->escape($key) . "` = " . ((int)$value > 0 ? (int)$value : 'NULL');
                continue;
            }
            $parts[] = "`" . $this->db->escape($key) . "` = '" . $this->db->escape((string)$value) . "'";
        }
        if (!$parts) {
            return;
        }
        $parts[] = "`date_modified` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "'";
        $this->db->query("UPDATE `{$this->prefix}outbound_contact_requests` SET " . implode(', ', $parts) . " WHERE `request_id` = " . (int)$request_id);
    }

    public function getById(int $request_id): array {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}outbound_contact_requests` WHERE `request_id` = " . (int)$request_id . " LIMIT 1");
        return $query->row ?: [];
    }

    public function getByMessageId(int $message_id): array {
        if ($message_id <= 0) {
            return [];
        }
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}outbound_contact_requests`
             WHERE `message_id` = " . (int)$message_id . "
             ORDER BY `request_id` ASC
             LIMIT 1"
        );
        return $query->row ?: [];
    }

    public function getByClientRequestId(int $manager_id, string $client_request_id): array {
        if ($manager_id <= 0 || $client_request_id === '') return [];
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}outbound_contact_requests`
             WHERE `manager_id` = " . (int)$manager_id . "
               AND `client_request_id` = '" . $this->db->escape($client_request_id) . "'
             LIMIT 1"
        );
        return $query->row ?: [];
    }

    



    public function reserveRateLimited(array $data): array {
        $channel = strtolower(trim((string)($data['channel'] ?? '')));
        $phone = (string)($data['phone'] ?? '');
        $manager_id = (int)($data['manager_id'] ?? 0);
        $client_request_id = trim((string)($data['client_request_id'] ?? ''));
        $payload_hash = strtolower(trim((string)($data['payload_hash'] ?? '')));
        if (!in_array($channel, ['max', 'sms'], true) || $phone === '' || $manager_id <= 0) {
            return ['success' => false, 'code' => 'outbound_request_invalid'];
        }

        $lock_name = '';
        $acquired = false;
        try {
            $database_query = $this->db->query('SELECT DATABASE() AS `database_name`');
            $database_name = (string)($database_query->row['database_name'] ?? '');
            if ($database_name === '') {
                return ['success' => false, 'code' => 'outbound_rate_storage_failed', 'retryable' => 1];
            }
            $lock_name = 'easychat_outbound_' . substr(hash(
                'sha256',
                $database_name . "\0" . $this->prefix . "\0" . $channel
            ), 0, 32);
            $lock = $this->db->query(
                "SELECT GET_LOCK('" . $this->db->escape($lock_name) . "', 3) AS acquired"
            );
            $acquired = (int)($lock->row['acquired'] ?? 0) === 1;
            if (!$acquired) {
                return ['success' => false, 'code' => 'outbound_rate_busy', 'retryable' => 1];
            }

            $existing = $this->getByClientRequestId($manager_id, $client_request_id);
            if ($existing) {
                if ($payload_hash !== '' && !hash_equals((string)$existing['payload_hash'], $payload_hash)) {
                    return ['success' => false, 'code' => 'client_request_id_reused'];
                }
                return ['success' => true, 'request_id' => (int)$existing['request_id'], 'reused' => 1];
            }

             
             
            if ($channel === 'sms' && preg_match('/^[a-f0-9]{64}$/D', $payload_hash)) {
                $active = $this->findActiveSmsByPayloadHash($payload_hash);
                if ($active) {
                    return [
                        'success' => true,
                        'request_id' => (int)$active['request_id'],
                        'message_id' => (int)$active['message_id'],
                        'active_duplicate' => 1,
                    ];
                }
            }

            if ($this->countRecent($channel, '', $manager_id, 60) >= 5) {
                return ['success' => false, 'code' => 'outbound_manager_rate_limited'];
            }
            if ($this->countRecent($channel, '', 0, 3600) >= 30) {
                return ['success' => false, 'code' => 'outbound_channel_rate_limited'];
            }
            if ($this->countRecent($channel, $phone, 0, 86400) >= 3) {
                return ['success' => false, 'code' => 'outbound_phone_rate_limited'];
            }

            return [
                'success' => true,
                'request_id' => $this->create($data + ['status' => 'checking']),
                'created' => 1,
            ];
        } catch (\Throwable $e) {
            return ['success' => false, 'code' => 'outbound_rate_storage_failed', 'retryable' => 1];
        } finally {
            if ($acquired) {
                try {
                    $this->db->query("SELECT RELEASE_LOCK('" . $this->db->escape($lock_name) . "')");
                } catch (\Throwable $ignored) {
                }
            }
        }
    }

    public function updateByMessageId(int $message_id, array $data): void {
        $parts = [];
        foreach ($data as $key => $value) {
            if (in_array($key, ['chat_id', 'messenger_contact_id', 'message_id', 'delivery_id', 'app_session_id', 'attempt_count'], true)) {
                $parts[] = "`" . $this->db->escape($key) . "` = " . ((int)$value > 0 ? (int)$value : 'NULL');
                continue;
            }
            $parts[] = "`" . $this->db->escape($key) . "` = '" . $this->db->escape((string)$value) . "'";
        }
        if (!$parts || $message_id <= 0) {
            return;
        }
        $parts[] = "`date_modified` = '" . $this->db->escape((new AppClock($this->registry))->nowSql()) . "'";
        $this->db->query("UPDATE `{$this->prefix}outbound_contact_requests` SET " . implode(', ', $parts) . " WHERE `message_id` = " . (int)$message_id);
    }

     
    public function updateSmsStatusByMessageId(
        int $message_id,
        string $status,
        string $last_error = '',
        bool $confirmed_delivered = false
    ): void {
        if ($message_id <= 0) return;
        $status = in_array($status, ['pending', 'sent', 'failed'], true) ? $status : 'pending';
        $status_e = $this->db->escape($status);
        $error_e = $this->db->escape((string)preg_replace('/[^a-z0-9._-]/i', '_', substr($last_error, 0, 64)));
        $allow_delivery_repair = $confirmed_delivered && $status === 'sent' ? 1 : 0;
        $preserve = "((`status` = 'failed' AND {$allow_delivery_repair} = 0) OR (`status` = 'sent' AND '{$status_e}' = 'pending'))";
        $this->db->query(
            "UPDATE `{$this->prefix}outbound_contact_requests`
             SET `last_error` = IF({$preserve}, `last_error`, '{$error_e}'),
                 `status` = IF({$preserve}, `status`, '{$status_e}'),
                 `date_modified` = NOW()
             WHERE `message_id` = " . (int)$message_id
        );
    }

    public function countRecent(string $channel, string $phone, int $manager_id, int $seconds): int {
        $seconds = max(60, min(86400, $seconds));
        $where = "`channel` = '" . $this->db->escape($channel) . "'
                  AND `status` <> 'rate_compensated'
                  AND `date_added` >= DATE_SUB(NOW(), INTERVAL " . $seconds . " SECOND)";
        if ($phone !== '') {
            $where .= " AND `phone_hash` = '" . $this->db->escape($this->phoneHash($phone)) . "'";
        }
        if ($manager_id > 0) {
            $where .= " AND `manager_id` = " . (int)$manager_id;
        }
        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}outbound_contact_requests` WHERE {$where}");
        return (int)($query->row['total'] ?? 0);
    }

    




    public function compensateMaxChatTypeAttempt(int $request_id, int $manager_id): array {
        if ($request_id <= 0 || $manager_id <= 0) {
            return ['success' => false, 'code' => 'outbound_compensation_invalid'];
        }

        $this->db->query(
            "UPDATE `{$this->prefix}outbound_contact_requests`
             SET `status` = 'rate_compensated',
                 `reason` = 'admin_max_type_recovery:" . (int)$manager_id . "',
                 `date_modified` = NOW()
             WHERE `request_id` = " . (int)$request_id . "
               AND `channel` = 'max'
               AND `status` = 'failed'
               AND (
                    `last_error` = 'max_chat_type_pending'
                    OR (
                        `last_error` = 'reply_not_allowed'
                        AND `messenger_contact_id` IS NOT NULL
                        AND EXISTS (
                            SELECT 1
                            FROM `{$this->prefix}messenger_contacts` mc
                            WHERE mc.`contact_id` = `{$this->prefix}outbound_contact_requests`.`messenger_contact_id`
                              AND mc.`provider` = 'max'
                              AND JSON_VALID(mc.`metadata_json`) = 1
                              AND JSON_UNQUOTE(JSON_EXTRACT(mc.`metadata_json`, '$.source')) = 'outbound_first_contact'
                              AND COALESCE(JSON_UNQUOTE(JSON_EXTRACT(mc.`metadata_json`, '$.chat_type')), '') NOT IN ('DIALOG','CHAT','CHANNEL')
                              AND COALESCE(JSON_UNQUOTE(JSON_EXTRACT(mc.`metadata_json`, '$.max_chat_type')), '') NOT IN ('DIALOG','CHAT','CHANNEL')
                        )
                    )
               )
             LIMIT 1"
        );
        $affected = (int)$this->db->countAffected();

        return $affected === 1
            ? ['success' => true, 'request_id' => $request_id]
            : ['success' => false, 'code' => 'outbound_compensation_not_allowed'];
    }

    private function findActiveSmsByPayloadHash(string $payload_hash): array {
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}outbound_contact_requests`
             WHERE `channel` = 'sms'
               AND `payload_hash` = '" . $this->db->escape($payload_hash) . "'
               AND `message_id` IS NOT NULL
               AND `message_id` > 0
               AND `status` IN ('checking','sending','sent_waiting','pending')
               AND `date_added` >= DATE_SUB(NOW(), INTERVAL 3 HOUR)
             ORDER BY `request_id` DESC
             LIMIT 1"
        );
        return $query->row ?: [];
    }

    private function publicId(): string {
        return 'oc_' . bin2hex(random_bytes(12));
    }

    private function phoneHash(string $phone): string {
        return hash('sha256', preg_replace('/\D+/', '', $phone));
    }
}
