PSTN / SIP Trunk
                          |
                          v
                   +--------------+
                   |  Asterisk    |
                   |  DID Routing |
                   +--------------+
                          |
              DID matched -> Route to extension 999888777
                          |
                          v
                 +------------------+
                 | doppia_router.agi|
                 |   (Perl AGI)    |
                 +------------------+
                    |           |
          +--------+           +--------+
          v                             v
   Query doppia_calls            Query did_company_map
   (repeat detection)            (company name lookup)
          |                             |
          v                             v
   Set is_repeat=Y/N            Set CALLERID(name)
          |
          v
   +------+------+
   |             |
   v             v
 REPEAT       FIRST-TIME
   |             |
   v             v
 "doppia"     "normal"
 ingroup      ingroup
   |             |
   v             v
 Experienced   General
 agents        agents

# Check that the required Perl modules are available
perl -MDBI -e 'print "DBI version: $DBI::VERSION\n"'
perl -MDBD::mysql -e 'print "DBD::mysql OK\n"'

# If missing, install them:
# RHEL/CentOS:
yum install perl-DBI perl-DBD-MySQL

# Debian/Ubuntu:
apt-get install libdbi-perl libdbd-mysql-perl

# openSUSE (ViciBox):
zypper install perl-DBI perl-DBD-mysql

CREATE TABLE IF NOT EXISTS doppia_calls (
    id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    phone_number  VARCHAR(32)     NOT NULL COMMENT 'Caller CLI (E.164 or national format)',
    did           VARCHAR(50)     NOT NULL COMMENT 'The DID that was called',
    last_call_time DATETIME       NOT NULL COMMENT 'Timestamp of most recent call',
    PRIMARY KEY (id),
    UNIQUE KEY idx_phone_did (phone_number, did),
    KEY idx_last_call_time (last_call_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  COMMENT='Tracks unique callers per DID for repeat detection';

CREATE TABLE IF NOT EXISTS did_company_map (
    did           VARCHAR(50)     NOT NULL,
    company_name  VARCHAR(100)    NOT NULL COMMENT 'Displayed as CALLERID(name) to agents',
    PRIMARY KEY (did)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  COMMENT='Maps DIDs to company names for agent display';

-- Example: map your DIDs to the brands they represent
INSERT INTO did_company_map (did, company_name) VALUES
    ('441234567890', 'Acme Plumbing UK'),
    ('441234567891', 'Acme Heating UK'),
    ('390212345678', 'Solar Italia'),
    ('33145678901',  'CleanTech France');

-- Bulk-load from CSV:
-- LOAD DATA LOCAL INFILE '/tmp/did_map.csv'
--   INTO TABLE did_company_map
--   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
--   LINES TERMINATED BY '\n'
--   (did, company_name);

CREATE TABLE IF NOT EXISTS doppia_blacklist (
    id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    phone_number  VARCHAR(32)     NOT NULL,
    did           VARCHAR(50)     NOT NULL DEFAULT '' COMMENT 'Empty = blocked on all DIDs',
    reason        VARCHAR(255)    DEFAULT NULL,
    date_added    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_phone (phone_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  COMMENT='Blacklisted callers - blocked before reaching agents';

-- Block a specific number on all DIDs
INSERT INTO doppia_blacklist (phone_number, reason)
VALUES ('+441234000000', 'Repeated abusive calls - blocked per manager request');

-- Block a number on a specific DID only
INSERT INTO doppia_blacklist (phone_number, did, reason)
VALUES ('+441234000001', '441234567890', 'Competitor probing this DID');

#!/usr/bin/perl
#
# doppia_router.agi - Repeat Caller Detection & Routing for ViciDial
#
# Called from Asterisk dialplan via AGI().
# Checks whether the caller (CLI + DID) has called within a configurable
# time window. Sets channel variables that the dialplan uses to route
# the call to the appropriate ViciDial inbound group.
#
# Arguments:
#   $ARGV[0] = Time window in seconds (default: 604800 = 7 days)
#   $ARGV[1] = Logical DID override (default: uses EXTEN)
#
# Channel variables set:
#   ${did}         - The resolved DID
#   ${is_repeat}   - "Y" if caller has called within window, "N" otherwise
#   ${is_blocked}  - "Y" if caller is blacklisted, "N" otherwise
#   ${company}     - Company name from did_company_map (if found)
#
# Usage in dialplan:
#   AGI(doppia_router.agi,604800,${EXTEN})
#

use strict;
use warnings;
use DBI;

$|++;    # Unbuffered output (required for AGI)

# ============================================================
# AGI Helper Functions
# ============================================================

my %AGI;

sub agi_verbose {
    my ($msg, $level) = @_;
    $level ||= 1;
    print "VERBOSE \"$msg\" $level\n";
}

sub agi_get_variable {
    my ($var) = @_;
    print "GET VARIABLE $var\n";
    my $response = <STDIN>;
    return '' unless defined $response;
    if ($response =~ /result=1\s*\((.*)\)/) {
        return $1;
    }
    return '';
}

sub agi_set_variable {
    my ($var, $val) = @_;
    print "SET VARIABLE $var $val\n";
    my $response = <STDIN>;    # consume the response
}

# ============================================================
# Read AGI Environment (Asterisk sends this on STDIN)
# ============================================================

while (<STDIN>) {
    chomp;
    last if $_ eq '';
    my ($k, $v) = split(/:\s*/, $_, 2);
    $AGI{$k} = $v if defined $k;
}

# ============================================================
# Parse Arguments
# ============================================================

my $TIME_WIN   = $ARGV[0] || 604800;     # Default: 7 days in seconds
my $DOPPIA_DID = $ARGV[1] || '';          # Logical DID for this rule

my $uniqueid   = $AGI{'agi_uniqueid'} || '';
my $chan        = $AGI{'agi_channel'}  || '';

# Retrieve caller and dialed number info from the channel
my $CLI   = agi_get_variable('CALLERID(num)');
my $EXTEN = agi_get_variable('EXTEN');
my $DNID  = agi_get_variable('DNID');

# If no explicit DID argument, use the dialed extension
$DOPPIA_DID ||= $EXTEN;

agi_verbose("DOPPIA: CLI=$CLI EXTEN=$EXTEN DNID=$DNID "
          . "TIME_WIN=$TIME_WIN DOPPIA_DID=$DOPPIA_DID", 1);

# ============================================================
# Read Database Config from ViciDial's astguiclient.conf
# ============================================================

my $conf_file = '/etc/astguiclient.conf';
my %dbconf;

if (open my $CFG, '<', $conf_file) {
    while (<$CFG>) {
        chomp;
        next if /^\s*#/;
        if (/^VARDB_server\s*=>\s*(.+)$/)   { $dbconf{server}   = $1; }
        if (/^VARDB_database\s*=>\s*(.+)$/) { $dbconf{database} = $1; }
        if (/^VARDB_user\s*=>\s*(.+)$/)     { $dbconf{user}     = $1; }
        if (/^VARDB_pass\s*=>\s*(.+)$/)     { $dbconf{pass}     = $1; }
        if (/^VARDB_port\s*=>\s*(.+)$/)     { $dbconf{port}     = $1; }
    }
    close $CFG;
} else {
    agi_verbose("DOPPIA ERROR: cannot open $conf_file: $!", 1);
}

# Defaults matching standard ViciDial install
$dbconf{server}   ||= 'localhost';
$dbconf{database} ||= 'asterisk';
$dbconf{user}     ||= 'cron';
$dbconf{pass}     ||= '';
$dbconf{port}     ||= 3306;

# ============================================================
# Initialize result variables
# ============================================================

my ($is_repeat, $is_blocked) = ('N', 'N');
my $RESOLVED_DID = $DOPPIA_DID;
my $company_name = '';

# ============================================================
# Connect to Database
# ============================================================

my $dbh;
eval {
    my $dsn = "DBI:mysql:database=$dbconf{database};"
            . "host=$dbconf{server};port=$dbconf{port}";
    $dbh = DBI->connect(
        $dsn,
        $dbconf{user},
        $dbconf{pass},
        {
            PrintError => 0,
            RaiseError => 0,
            AutoCommit => 1,
            mysql_connect_timeout => 3,    # Don't hang if DB is slow
        }
    );
};

if (!$dbh) {
    agi_verbose("DOPPIA ERROR: DB connect failed: "
              . ($DBI::errstr || 'unknown'), 1);
    goto RESULT;
}

# ============================================================
# Ensure tables exist (safe for first run; can remove later)
# ============================================================

eval {
    $dbh->do(q{
        CREATE TABLE IF NOT EXISTS doppia_calls (
            id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
            phone_number  VARCHAR(32)  NOT NULL,
            did           VARCHAR(50)  NOT NULL,
            last_call_time DATETIME    NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_phone_did (phone_number, did),
            KEY idx_last_call_time (last_call_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    });
};
if ($@) {
    agi_verbose("DOPPIA ERROR: table creation failed: $@", 1);
    goto RESULT;
}

# ============================================================
# Step 1: Check Blacklist
# ============================================================

eval {
    my $sth = $dbh->prepare(q{
        SELECT 1 FROM doppia_blacklist
        WHERE phone_number = ?
          AND (did = ? OR did = '' OR did IS NULL)
        LIMIT 1
    });
    if ($sth && $sth->execute($CLI, $DOPPIA_DID)) {
        my ($found) = $sth->fetchrow_array;
        $is_blocked = 'Y' if $found;
        $sth->finish;
    }
};
if ($is_blocked eq 'Y') {
    agi_verbose("DOPPIA BLOCKED: CLI=$CLI DID=$DOPPIA_DID", 1);
    goto RESULT;
}

# ============================================================
# Step 2: Check Repeat Caller (within time window)
# ============================================================

my $cutoff_epoch = time() - $TIME_WIN;

eval {
    my $sth = $dbh->prepare(q{
        SELECT last_call_time
        FROM doppia_calls
        WHERE phone_number = ? AND did = ?
          AND last_call_time >= FROM_UNIXTIME(?)
        ORDER BY last_call_time DESC
        LIMIT 1
    });
    if ($sth && $sth->execute($CLI, $DOPPIA_DID, $cutoff_epoch)) {
        my ($last_call_time) = $sth->fetchrow_array;
        if ($last_call_time) {
            $is_repeat = 'Y';
            agi_verbose("DOPPIA REPEAT: CLI=$CLI last_call=$last_call_time", 1);
        }
        $sth->finish;
    } else {
        agi_verbose("DOPPIA ERROR: SELECT failed: "
                  . ($dbh->errstr || 'unknown'), 1);
    }
};

# ============================================================
# Step 3: Upsert - Update existing record or insert new one
# ============================================================

eval {
    my $sth_upd = $dbh->prepare(q{
        UPDATE doppia_calls
           SET last_call_time = NOW()
         WHERE phone_number = ? AND did = ?
    });
    if ($sth_upd && $sth_upd->execute($CLI, $DOPPIA_DID)) {
        if ($sth_upd->rows == 0) {
            # No existing row - insert a new one
            my $sth_ins = $dbh->prepare(q{
                INSERT INTO doppia_calls (phone_number, did, last_call_time)
                VALUES (?, ?, NOW())
            });
            if (!$sth_ins || !$sth_ins->execute($CLI, $DOPPIA_DID)) {
                agi_verbose("DOPPIA ERROR: INSERT failed: "
                          . ($dbh->errstr || 'unknown'), 1);
            }
        }
    } else {
        agi_verbose("DOPPIA ERROR: UPDATE failed: "
                  . ($dbh->errstr || 'unknown'), 1);
    }
};

# ============================================================
# Step 4: Look up company name from did_company_map
# ============================================================

eval {
    my $sth = $dbh->prepare(q{
        SELECT company_name FROM did_company_map WHERE did = ? LIMIT 1
    });
    if ($sth && $sth->execute($DOPPIA_DID)) {
        ($company_name) = $sth->fetchrow_array;
        $company_name ||= '';
        $sth->finish;
    }
};

if ($company_name) {
    agi_verbose("DOPPIA COMPANY: $company_name for DID $DOPPIA_DID", 1);
}

# ============================================================
# Disconnect and set results
# ============================================================

$dbh->disconnect if $dbh;

RESULT:

agi_verbose("DOPPIA RESULT: did=$RESOLVED_DID is_repeat=$is_repeat "
          . "is_blocked=$is_blocked company=$company_name", 1);

# Set channel variables for the dialplan to consume
agi_set_variable('did',        $RESOLVED_DID);
agi_set_variable('is_repeat',  $is_repeat);
agi_set_variable('is_blocked', $is_blocked);

# Set caller ID name to the company name so agents see it on screen
if ($company_name) {
    agi_set_variable('CALLERID(name)', $company_name);
}

exit 0;

# Copy to the AGI directory
cp doppia_router.agi /var/lib/asterisk/agi-bin/doppia_router.agi

# Set ownership and permissions
chown asterisk:asterisk /var/lib/asterisk/agi-bin/doppia_router.agi
chmod 755 /var/lib/asterisk/agi-bin/doppia_router.agi

# Verify it parses without errors
perl -c /var/lib/asterisk/agi-bin/doppia_router.agi
# Expected output: doppia_router.agi syntax OK

; ================================================================
; DOPPIA REPEAT CALLER ROUTING
; All DIDs configured for repeat detection route here.
; In ViciDial DID admin: set DID Route = EXTEN, Extension = 999888777
; ================================================================

exten => 999888777,1,NoOp(DOPPIA ENTRY: CLI=${CALLERID(num)} EXTEN=${EXTEN})

; Answer the channel so we can run AGI
same => n,Answer()

; Optional: brief ring-back delay (2-5 seconds)
; This simulates normal business phone behavior so callers
; hear ringing rather than dead air while the AGI runs.
; Adjust the Ringing() + Wait() to taste.
same => n,Ringing()
same => n,Wait(${RAND(2,5)})

; Run the AGI script
; Arg 1: time window in seconds (604800 = 7 days)
; Arg 2: the DID (passed via ${EXTEN} or hardcoded)
same => n,AGI(doppia_router.agi,604800,${EXTEN})

; Read back the AGI decisions
same => n,NoOp(DOPPIA DECISION: DID=${did} REPEAT=${is_repeat} BLOCKED=${is_blocked})

; If blacklisted, send to blocked handler
same => n,GotoIf($["${is_blocked}"="Y"]?doppia-blocked,1)

; Route based on repeat status:
;   Repeat caller  -> "doppia" inbound group (experienced agents)
;   First-time     -> "normal" inbound group (general agents)
same => n,Set(target_ingroup=${IF($["${is_repeat}"="Y"]?doppia:normal)})
same => n,NoOp(DOPPIA TARGET INGROUP=${target_ingroup})

; Hand off to ViciDial's inbound engine
same => n,Set(__VICIDIAL_INBOUND=Y)
same => n,Set(__CALL_LOG_OVERRIDE=INBOUND)
same => n,AGI(agi-VDAD_ALL_inbound.agi,CIDLOOKUP-----LB-----${target_ingroup}-----${did}-----${CALLERID(num)}-----park----------999-----1)

; FALLBACK: if the ViciDial AGI exits unexpectedly, send to a ring group
; so the call is never silently dropped
same => n,NoOp(AGI FALLBACK: VDAD AGI exited abnormally)
same => n,Goto(default,your-fallback-extension,1)

; ----------------------------------------------------------------
; Blacklisted caller handler
; ----------------------------------------------------------------
exten => doppia-blocked,1,NoOp(DOPPIA BLOCKED: CLI=${CALLERID(num)} DID=${did})
same => n,Playback(ss-noservice)
same => n,Hangup(21)

# Reload the dialplan without restarting Asterisk
asterisk -rx "dialplan reload"

# Verify the extension loaded
asterisk -rx "dialplan show 999888777@default"

-- One at a time
INSERT INTO did_company_map (did, company_name)
VALUES ('441234567890', 'Acme Plumbing London');

-- Bulk insert
INSERT INTO did_company_map (did, company_name) VALUES
    ('441234567890', 'Acme Plumbing London'),
    ('441234567891', 'Acme Plumbing Manchester'),
    ('441234567892', 'Acme Heating UK'),
    ('390212345678', 'Solar Italia Milano'),
    ('390212345679', 'Solar Italia Roma'),
    ('33145678901',  'CleanTech Paris');

-- Generate INSERT statements from your existing DID configuration
SELECT CONCAT(
    'INSERT IGNORE INTO did_company_map (did, company_name) VALUES (''',
    did_pattern, ''', ''',
    did_description, ''');'
)
FROM vicidial_inbound_dids
WHERE did_route = 'EXTEN'
  AND extension = '999888777'
  AND did_description != '';

-- Check how many DIDs are mapped
SELECT COUNT(*) AS mapped_dids FROM did_company_map;

-- Find DIDs that are routed through doppia but NOT mapped to a company
SELECT d.did_pattern
FROM vicidial_inbound_dids d
LEFT JOIN did_company_map m ON d.did_pattern = m.did
WHERE d.extension = '999888777'
  AND m.did IS NULL;

-- Add a number to the blacklist (blocked on ALL DIDs)
INSERT INTO doppia_blacklist (phone_number, reason)
VALUES ('+441234000000', 'Abusive caller - 3 incidents logged');

-- Add a number blocked on a SPECIFIC DID only
INSERT INTO doppia_blacklist (phone_number, did, reason)
VALUES ('+441234000001', '441234567890', 'Competitor probing this campaign');

-- View the blacklist
SELECT phone_number, did, reason, date_added
FROM doppia_blacklist
ORDER BY date_added DESC;

-- Remove a number from the blacklist
DELETE FROM doppia_blacklist WHERE phone_number = '+441234000000';

# Test from the command line using the same credentials the AGI will use
# (read these from /etc/astguiclient.conf)
mysql -u <DBUSER> -p<DBPASS> <DBNAME> -e "SELECT 1 AS db_ok;"

# Simulate an AGI invocation
echo -e "agi_uniqueid: test123\nagi_channel: SIP/test\n\n" | \
    /var/lib/asterisk/agi-bin/doppia_router.agi 604800 441234567890

VERBOSE "DOPPIA: CLI= EXTEN= DNID= TIME_WIN=604800 DOPPIA_DID=441234567890" 1
GET VARIABLE CALLERID(num)
GET VARIABLE EXTEN
GET VARIABLE DNID
VERBOSE "DOPPIA RESULT: did=441234567890 is_repeat=N is_blocked=N company=" 1
SET VARIABLE did 441234567890
SET VARIABLE is_repeat N
SET VARIABLE is_blocked N

SELECT * FROM doppia_calls ORDER BY last_call_time DESC LIMIT 5;

asterisk -rx "core set verbose 5"
# Then in another terminal, or use:
asterisk -rvvvvv

DOPPIA ENTRY: CLI=441234000123 EXTEN=999888777
DOPPIA: CLI=441234000123 EXTEN=999888777 DNID=441234567890 TIME_WIN=604800 DOPPIA_DID=999888777
DOPPIA RESULT: did=999888777 is_repeat=N is_blocked=N company=Acme Plumbing London
DOPPIA TARGET INGROUP=normal

DOPPIA RESULT: did=999888777 is_repeat=Y is_blocked=N company=Acme Plumbing London
DOPPIA TARGET INGROUP=doppia

-- Add your test number to the blacklist
INSERT INTO doppia_blacklist (phone_number, reason)
VALUES ('+441234000123', 'Testing blacklist');

DOPPIA BLOCKED: CLI=+441234000123 DID=999888777

DELETE FROM doppia_blacklist WHERE phone_number = '+441234000123';

INSERT INTO did_company_map (did, company_name)
VALUES ('999888777', 'Test Company');

-- Repeat rate over the last 30 days
-- A caller is "repeat" if their record existed before today
SELECT
    DATE(cl.call_date) AS call_date,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN dc.last_call_time < DATE(cl.call_date) THEN 1 ELSE 0 END) AS repeat_calls,
    ROUND(
        SUM(CASE WHEN dc.last_call_time < DATE(cl.call_date) THEN 1 ELSE 0 END)
        / COUNT(*) * 100, 1
    ) AS repeat_pct
FROM vicidial_closer_log cl
LEFT JOIN doppia_calls dc
    ON cl.phone_number = dc.phone_number
WHERE cl.call_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(cl.call_date)
ORDER BY call_date;

SELECT
    COUNT(*) AS total_tracked,
    COUNT(CASE WHEN last_call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
        AS active_last_7d,
    COUNT(CASE WHEN last_call_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END)
        AS active_last_30d,
    MIN(last_call_time) AS oldest_record,
    MAX(last_call_time) AS newest_record
FROM doppia_calls;

SELECT
    dc.did,
    COALESCE(m.company_name, '(unmapped)') AS company,
    COUNT(*) AS unique_callers,
    COUNT(CASE WHEN dc.last_call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
        AS callers_last_7d
FROM doppia_calls dc
LEFT JOIN did_company_map m ON dc.did = m.did
GROUP BY dc.did, m.company_name
ORDER BY unique_callers DESC
LIMIT 20;

SELECT
    cl.phone_number,
    COUNT(*) AS call_count,
    COUNT(DISTINCT DATE(cl.call_date)) AS distinct_days,
    MIN(cl.call_date) AS first_call,
    MAX(cl.call_date) AS last_call
FROM vicidial_closer_log cl
WHERE cl.call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY cl.phone_number
HAVING call_count >= 3
ORDER BY call_count DESC
LIMIT 20;

SELECT
    campaign_id AS ingroup,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN status = 'SALE' THEN 1 ELSE 0 END) AS sales,
    ROUND(
        SUM(CASE WHEN status = 'SALE' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1
    ) AS conversion_pct,
    ROUND(AVG(length_in_sec), 0) AS avg_duration_sec,
    ROUND(AVG(queue_seconds), 0) AS avg_queue_sec
FROM vicidial_closer_log
WHERE call_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND campaign_id IN ('doppia', 'normal')
GROUP BY campaign_id;

-- Check table growth
SELECT
    table_name,
    table_rows AS approx_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN ('doppia_calls', 'did_company_map', 'doppia_blacklist');

-- Remove records not updated in 90 days
-- Run via cron weekly or monthly
DELETE FROM doppia_calls
WHERE last_call_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

# Purge stale doppia records every Sunday at 3 AM
0 3 * * 0 mysql -u <DBUSER> -p<DBPASS> <DBNAME> -e "DELETE FROM doppia_calls WHERE last_call_time < DATE_SUB(NOW(), INTERVAL 90 DAY);" >> /var/log/doppia_cleanup.log 2>&1

; Example: 14-day window
same => n,AGI(doppia_router.agi,1209600,${EXTEN})

; Sales DIDs: 7-day window
exten => 999888777,1,NoOp(DOPPIA SALES)
same => n,AGI(doppia_router.agi,604800,${EXTEN})
; ... rest of routing ...

; Support DIDs: 30-day window
exten => 999888778,1,NoOp(DOPPIA SUPPORT)
same => n,AGI(doppia_router.agi,2592000,${EXTEN})
; ... rest of routing ...

# Add after retrieving $CLI
if (!$CLI || $CLI =~ /^(0+|anonymous|unavailable|private|unknown)$/i) {
    agi_verbose("DOPPIA: Anonymous caller, treating as first-time", 1);
    $is_repeat = 'N';
    goto RESULT;
}

# Normalize CLI: strip leading +, 00, or national prefix
$CLI =~ s/^\+//;            # Remove leading +
$CLI =~ s/^00//;            # Remove international prefix 00
$CLI =~ s/^0(\d{9,})/$1/;  # Remove national leading 0 (adjust regex for your country)

DATE=$(date +%Y%m%d)
cp /etc/asterisk/extensions.conf /etc/asterisk/extensions.conf.bak.$DATE

# Check permissions
ls -la /var/lib/asterisk/agi-bin/doppia_router.agi
# Should show: -rwxr-xr-x asterisk asterisk

# Check Asterisk can find it
asterisk -rx "agi show" | grep doppia

# Check for Perl errors
perl -c /var/lib/asterisk/agi-bin/doppia_router.agi

# Test with the same credentials the AGI uses
grep VARDB /etc/astguiclient.conf
mysql -u <DBUSER> -p<DBPASS> <DBNAME> -e "SELECT 1;"

   SELECT * FROM doppia_calls WHERE phone_number LIKE '%YOUR_TEST_NUMBER%';