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)
);



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



-- update the dial plan 
UPDATE extensions_table 
SET appdata = 'PJSIP/1001,20,g' 
WHERE exten = '1001' AND app = 'Dial';

-- g here ensures it captures the DIALSTATUS even after the call ends

ALTER TABLE `ps_endpoints` ADD mohsuggest VARCHAR(40);
ALTER TABLE `ps_endpoints` ADD mailboxes VARCHAR(255);

UPDATE ps_endpoints SET mohsuggest = 'default';
UPDATE ps_endpoints SET direct_media = 'no';
SELECT * FROM `ps_endpoints`;


SELECT * FROM extensions_table;

-- DROP TABLE `extensions_table`;

SELECT * FROM extensions_table WHERE context='default';


SHOW TABLES;

#

-- ------------ call conferencing start ----------------------------------------------

-- Static Conference Setup
INSERT INTO extensions_table (id, context, exten, priority, app, appdata) VALUES
(1, 'default', '100', 1, 'Answer', ''),
(2, 'default', '100', 2, 'ConfBridge', '100'),
(3, 'default', '100', 3, 'Hangup', '');

-- Pre-Bridge Subroutine
INSERT INTO extensions_table (id, context, exten, priority, app, appdata) VALUES
(91, 'prebridge', 's', 1, 'NoOp', '*** Pre-Bridge Subroutine ***'),
(92, 'prebridge', 's', 2, 'Set', 'SHARED(lastcaller)=${CALLERCHAN}'),
(93, 'prebridge', 's', 3, 'Set', 'SHARED(lastcallee)=${EXTEN}'),
(94, 'prebridge', 's', 4, 'Return', '');

-- Transfer to ConfBridge Logic
INSERT INTO extensions_table (id, context, exten, priority, app, appdata) VALUES
(95, 'transfer-to-conf', 's', 1, 'NoOp', '*** Transferring to ConfBridge ***'),
(96, 'transfer-to-conf', 's', 2, 'Set', 'CONFROOM=9000'),
(97, 'transfer-to-conf', 's', 3, 'Originate', 'Local/9100@default,,exten,default,9100,1'),
(98, 'transfer-to-conf', 's', 4, 'Originate', 'Local/9100@default,,exten,default,9100,1'),
(99, 'transfer-to-conf', 's', 5, 'Wait', '5'),
(100, 'transfer-to-conf', 's', 6, 'Return', '');

-- Add Third User Logic
INSERT INTO extensions_table (id, context, exten, priority, app, appdata) VALUES
(101, 'add-third-user', 's', 1, 'NoOp', '*** Adding 3rd User to Conference ***'),
(102, 'add-third-user', 's', 2, 'Read', 'THIRDPARTY,please-enter-extension,4'),
(103, 'add-third-user', 's', 3, 'NoOp', 'Inviting ${THIRDPARTY} to join conf'),
(104, 'add-third-user', 's', 4, 'Originate', 'PJSIP/${THIRDPARTY},,exten,default,9100,1'),
(105, 'add-third-user', 's', 5, 'Return', '');

-- ConfBridge Join Point (Room 9000)
INSERT INTO extensions_table (id, context, exten, priority, app, appdata) VALUES
(106, 'default', '9100', 1, 'NoOp', '*** Joining ConfBridge Room 9000 ***'),
(107, 'default', '9100', 2, 'ConfBridge', '9000'),
(108, 'default', '9100', 3, 'Hangup', '');


-- Dynamic Dialplan for All 1XXX Extensions (Voicemail) -- 
INSERT INTO extensions_table (id, context, exten, priority, app, appdata) VALUES
(135, 'default', '_1XXX', 1, 'NoOp', 'Calling dynamic extension ${EXTEN}'),
(136, 'default', '_1XXX', 2, 'Dial', 'PJSIP/${EXTEN},20'),
(137, 'default', '_1XXX', 3, 'VoiceMail', '${EXTEN}@default,u');



-- The above contains content for potential dynamic conferencing as well for future use --

--------- call conferencing end -------------------------------------------------------------


--------- queue members ---------------------------------

CREATE TABLE queue_members (
  id INT PRIMARY KEY,
  queue_id INT,
  interface VARCHAR(255),
  penalty INT DEFAULT 0,
  member_name VARCHAR(255),
  state_interface VARCHAR(255),
  queue_name VARCHAR(255),
  uniqueid VARCHAR(20)
);


INSERT INTO queue_members (id, queue_id, interface, penalty, member_name, state_interface, queue_name, uniqueid) VALUES
(1, 1, 'PJSIP/1001', 0, 'Agent 1001', 'Agent:1001', 'waiting-queue', '001'),
(2, 2, 'PJSIP/1002', 0, 'Agent 1002', 'Agent:1002', 'waiting-queue', '002'),
(3, 3, 'PJSIP/1003', 0, 'Agent 1003', 'Agent:1003', 'waiting-queue', '003');


------------ queue members end -----------------------

------------- queues ---------------------------------

CREATE TABLE queues (
  name VARCHAR(100) PRIMARY KEY,
  music_class VARCHAR(100),
  strategy VARCHAR(50),
  timeout INT,
  retry INT,
  wrapup_time INT,
  max_len INT,
  announce_frequency INT
);

INSERT INTO queues (name, music_class, strategy, timeout, retry, wrapup_time, max_len, announce_frequency) VALUES
('waiting-queue', 'default', 'ringall', 15, 5, 10, 0, 0);



-------------queues end ---------------------------------------

-------------voicemail users ----------------------------------

CREATE TABLE voicemail_users (
  context VARCHAR(50),
  mailbox VARCHAR(10) PRIMARY KEY,
  password VARCHAR(20),
  fullname VARCHAR(100),
  email VARCHAR(100)
);

INSERT INTO voicemail_users (context, mailbox, password, fullname, email) VALUES
('default', '1001', '1234', 'User One', 'djangod56@gmail.com'),
('default', '1002', '1234', 'User Two', 'djangod56@gmail.com'),
('default', '1003', '1234', 'User Three', 'djangod56@gmail.com');


--------------- voicemail users end ------------------------------------------------