cezenGIT/MySQL_conf_pbx/test1/three_users.sql

USE asterisk_db;

CREATE TABLE ps_endpoints (
  id VARCHAR(40) NOT NULL PRIMARY KEY,
  transport VARCHAR(40) NULL,
  aors VARCHAR(200) NULL UNIQUE,
  auth VARCHAR(40) NULL UNIQUE,
  context VARCHAR(40) NULL,
  disallow VARCHAR(200) NULL,
  allow VARCHAR(200) NULL,
  direct_media VARCHAR(40) NULL,
  connected_line_method VARCHAR(40) NULL,
  callerid VARCHAR(40) NULL,
  dtmf_mode VARCHAR(40) NULL
);

CREATE TABLE ps_auths (
  id VARCHAR(40) NOT NULL PRIMARY KEY,
  auth_type VARCHAR(40) NOT NULL,
  username VARCHAR(40) NULL UNIQUE,
  password VARCHAR(40) NULL,
  md5_cred VARCHAR(40) NULL,
  realm VARCHAR(40) NULL
);

CREATE TABLE ps_aors (
  id VARCHAR(40) NOT NULL PRIMARY KEY,
  max_contacts INTEGER NULL
);


CREATE TABLE extensions_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    context VARCHAR(50) NOT NULL,
    exten VARCHAR(50) NOT NULL,
    priority INT NOT NULL,
    app VARCHAR(50) NOT NULL,
    appdata VARCHAR(100)
);

ALTER TABLE extensions_table MODIFY appdata VARCHAR(200);


INSERT INTO ps_endpoints (id, transport, aors, auth, context, disallow, allow) 
VALUES ('1001', 'transport-udp', '1001', '1001', 'default', 'all', 'ulaw,alaw');

INSERT INTO ps_auths (id, auth_type, username, password) 
VALUES ('1001', 'userpass', '1001', '12345');

INSERT INTO ps_aors (id, max_contacts) 
VALUES ('1001', 1);



INSERT INTO ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) 
VALUES ('1002', 'transport-udp', '1002', '1002', 'default', 'all', 'ulaw,alaw', 'no');

INSERT INTO ps_auths (id, auth_type, username, password) 
VALUES ('1002', 'userpass', '1002', '12345');

INSERT INTO ps_aors (id, max_contacts) 
VALUES ('1002', 2);


INSERT INTO ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) 
VALUES ('1003', 'transport-udp', '1003', '1003', 'default', 'all', 'ulaw,alaw', 'no');

INSERT INTO ps_auths (id, auth_type, username, password) 
VALUES ('1003', 'userpass', '1003', '12345');

INSERT INTO ps_aors (id, max_contacts) 
VALUES ('1003', 2);


INSERT INTO extensions_table (context, exten, priority, app, appdata) 
VALUES 
('default', '1001', 1, 'Dial', 'PJSIP/1001,20'),
('default', '1002', 1, 'Dial', 'PJSIP/1002,20'),
('default', '1003', 1, 'Dial', 'PJSIP/1003,20');

/*INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES 
('default', '1001', 1, 'MixMonitor', '1001-${UNIQUEID}.wav,b'),
('default', '1001', 2, 'Dial', 'PJSIP/1001,20'),

('default', '1002', 1, 'MixMonitor', '1002-${UNIQUEID}.wav,b'),
('default', '1002', 2, 'Dial', 'PJSIP/1002,20'),

('default', '1003', 1, 'MixMonitor', '1003-${UNIQUEID}.wav,b'),
('default', '1003', 2, 'Dial', 'PJSIP/1003,20');*/


UPDATE extensions_table SET appdata = CONCAT(exten, '-${UNIQUEID}.wav,b') WHERE app = 'MixMonitor';

-- call forwarding for 1001
-- Call Forwarding if Busy
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES 
('default', '1001', 3, 'GotoIf', '${DIALSTATUS}=BUSY?default,1002,1');


-- Call Forwarding if Unavailable
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES 
('default', '1001', 4, 'GotoIf', '${DIALSTATUS}=CHANUNAVAIL?default,1002,1');

-- Call Forwarding if No Answer
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES 
('default', '1001', 5, 'GotoIf', '${DIALSTATUS}=NOANSWER?default,1002,1');

-- 

-- music on hold
USE asterisk_db;

-- CREATE TABLE moh_classes (
--     name VARCHAR(40) PRIMARY KEY,
--     mode VARCHAR(20) NOT NULL,
--     directory VARCHAR(255),
--     application VARCHAR(255),
--     digit INTEGER DEFAULT NULL,
--     sort VARCHAR(10) DEFAULT 'random'
-- );
-- 
-- INSERT INTO moh_classes (name, mode, directory, sort) 
-- VALUES 
-- ('default', 'files', '/etc/asterisk/moh', 'random');
-- 
-- SELECT * FROM moh_classes;
-- 
-- DROP TABLE `moh_classes`;

INSERT INTO extensions_table (context, exten, priority, app, appdata) 
VALUES 
('default', '1001', 20, 'MusicOnHold', 'default'),
('default', '1002', 20, 'MusicOnHold', 'default'),
('default', '1003', 20, 'MusicOnHold', 'default');

UPDATE extensions_table 
SET appdata = CONCAT(appdata, ',m(default)')
WHERE app = 'Dial';


-- music on hold end


-- DND

CREATE TABLE IF NOT EXISTS dnd_status (
    extension VARCHAR(10) PRIMARY KEY,
    status ENUM('ON', 'OFF') NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Enable DND (*78)
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES 
('default', '*78', 1, 'Set', 'RESULT=${ODBC_SQL(${CALLERID(num)}, ON)}'),
-- ('default', '*78', 2, 'Playback', 'dnd-activated'),
('default', '*78', 3, 'Hangup', '');

-- Disable DND (*79)
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES 
('default', '*79', 1, 'Set', 'RESULT=${ODBC_SQL(${CALLERID(num)}, OFF)}'),
-- ('default', '*79', 2, 'Playback', 'dnd-deactivated'),
('default', '*79', 3, 'Hangup', '');

-- Enable DND (*78)
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES ('default', '*78', 1, 'System', 'mysql -uasterisk_user -p12345 -h127.0.0.1 -P3308 -e "INSERT INTO asterisk_db.dnd_status (extension, status) VALUES (\'${CALLERID(num)}\', \'ON\') ON DUPLICATE KEY UPDATE status=\'ON\', updated_at=NOW();"'),
       ('default', '*78', 2, 'Playback', 'dnd-activated'),
       ('default', '*78', 3, 'Hangup', NULL);

-- Disable DND (*79)
INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES ('default', '*79', 1, 'System', 'mysql -uasterisk_user -p12345 -h127.0.0.1 -P3308 -e "INSERT INTO asterisk_db.dnd_status (extension, status) VALUES (\'${CALLERID(num)}\', \'OFF\') ON DUPLICATE KEY UPDATE status=\'OFF\', updated_at=NOW();"'),
       ('default', '*79', 2, 'Playback', 'dnd-deactivated'),
       ('default', '*79', 3, 'Hangup', NULL);


SELECT * FROM `extensions_table`;

DELETE FROM `extensions_table` WHERE exten = '*78' OR exten = '*79';

-- end of DND