USE asterisk_db;

show tables;
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 USE asterisk_db;
NULL
);


CREATE TABLE extensions_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    context VARCHAR(50) NOT NULL,
    exten VARCHAR(50) NOT NULL UNIQUE,
    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);


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

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

-- Add AOR (Address of Record) for 1002
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');

-- Add Authentication for 1002
INSERT INTO ps_auths (id, auth_type, username, password) 
VALUES ('1003', 'userpass', '1003', '12345');

-- Add AOR (Address of Record) for 1002
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');

SELECT * FROM extensions_table;

-- DROP TABLE `extensions_table`;

SELECT * FROM extensions_table WHERE context='default';


SHOW TABLES;
select * from ps_endpoints;
select * from ps_auths;
select * from ps_aors;
select * from extensions_table;


ALTER TABLE ps_endpoints DROP COLUMN mailboxes;
ALTER TABLE ps_endpoints DROP COLUMN mohsuggest;

ALTER TABLE ps_endpoints ADD mohsuggest VARCHAR(40);
ALTER TABLE ps_endpoints ADD mailboxes VARCHAR(255);
UPDATE ps_endpoints SET mohsuggest = 'default' WHERE id IN ('1001', '1002', '1003');

UPDATE ps_aors SET max_contacts = 1 WHERE id = '1001';

show tables;



-- voice mail usertable ---

CREATE TABLE voicemail_users (
  context VARCHAR(20),     -- Which group or environment (usually 'default')
  mailbox VARCHAR(20),     -- The voicemail box number (usually same as extension)
  password VARCHAR(20),    -- PIN to access the voicemail
  fullname VARCHAR(50),    -- User’s name
  email VARCHAR(100)       -- Where to send the voicemail recording
);

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

select * from voicemail_users;

-- table end --



INSERT INTO extensions_table (context, exten, priority, app, appdata) VALUES
('default', '1001', 30, 'VoiceMail', '1001@default,u'),
('default', '1001', 40, 'Hangup', ''),
('default', '1002', 30, 'VoiceMail', '1002@default,u'),
('default', '1002', 40, 'Hangup', ''),
('default', '1003', 30, 'VoiceMail', '1003@default,u'),
('default', '1003', 40, 'Hangup', '');

select * from extensions_table; 

SHOW INDEXES FROM extensions_table;

ALTER TABLE extensions_table DROP INDEX exten;


ALTER TABLE extensions_table DROP PRIMARY KEY;

ALTER TABLE extensions_table ADD PRIMARY KEY (context, exten, priority);

ALTER TABLE extensions_table ADD UNIQUE INDEX unique_exten (context, exten, priority);

SELECT * FROM extensions_table WHERE exten IN ('1001', '1002', '1003') ORDER BY exten, priority;


INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES ('default', '555', 2, 'Playback', 'vm-theperson');

delete from extensions_table where appdata = 'vm-theperson';
select * from voicemail_users;
select * from extensions_table;

SHOW TABLES;
SELECT * FROM extensions_table WHERE context = 'default' ORDER BY exten, priority;

DESCRIBE extensions_table;
SELECT * FROM extensions_table WHERE context = 'default' AND exten = '1003';


SHOW INDEXES FROM extensions_table;





UPDATE extensions_table
SET appdata = CONCAT(SUBSTRING_INDEX(appdata, ',', 1), ',su')
WHERE app = 'VoiceMail' AND priority = 2;


select * from extensions_table;

delete from extensions_table where exten ='9999';

UPDATE extensions_table
SET appdata = CONCAT(SUBSTRING_INDEX(appdata, '@', 1), '@default,s')
WHERE app = 'VoiceMail';
SELECT * FROM voicemail_users WHERE mailbox = '1002';
show tables;
select * from voicemail_users;



-- changes in dialplan ---

INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES
('default', '1002', 6, 'GotoIf', '${DIALSTATUS}=BUSY?default,1002,30'),
('default', '1002', 7, 'GotoIf', '${DIALSTATUS}=CHANUNAVAIL?default,1002,30'),
('default', '1002', 8, 'GotoIf', '${DIALSTATUS}=NOANSWER?default,1002,30');

INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES
('default', '1003', 6, 'GotoIf', '${DIALSTATUS}=BUSY?default,1003,30'),
('default', '1003', 7, 'GotoIf', '${DIALSTATUS}=CHANUNAVAIL?default,1003,30'),
('default', '1003', 8, 'GotoIf', '${DIALSTATUS}=NOANSWER?default,1003,30');

select * from extensions_table;


UPDATE extensions_table
SET appdata = '${DIALSTATUS}=BUSY?default,1002,30'
WHERE exten = '1002' AND priority = 6;


UPDATE extensions_table
SET appdata = '${DIALSTATUS}=CHANUNAVAIL?default,1002,30'
WHERE exten = '1002' AND priority = 7;

UPDATE extensions_table
SET appdata = '${DIALSTATUS}=NOANSWER?default,1002,30'
WHERE exten = '1002' AND priority = 8;

UPDATE extensions_table
SET appdata = '${DIALSTATUS}=BUSY?default,1003,30'
WHERE exten = '1003' AND priority = 6;

UPDATE extensions_table
SET appdata = '${DIALSTATUS}=CHANUNAVAIL?default,1003,30'
WHERE exten = '1003' AND priority = 7;

UPDATE extensions_table
SET appdata = '${DIALSTATUS}=NOANSWER?default,1003,30'
WHERE exten = '1003' AND priority = 8;


INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES ('default', '1002', 3, 'Goto', 'default,1002,6');

INSERT INTO extensions_table (context, exten, priority, app, appdata)
VALUES ('default', '1003', 3, 'Goto', 'default,1003,6');


-- change end ---------------


desc extensions_table;
select * from extensions_table;


-- id|context|exten|priority|app        |appdata                                  |
-- --+-------+-----+--------+-----------+-----------------------------------------+
--  1|default|1001 |       2|Dial       |PJSIP/1001,20,m(default)                 |
--  2|default|1002 |       2|Dial       |PJSIP/1002,20,m(default)                 |
--  3|default|1003 |       2|Dial       |PJSIP/1003,20,m(default)                 |
--  4|default|1001 |       1|MixMonitor |1001-${UNIQUEID}.wav,b                   |
--  5|default|1002 |       1|MixMonitor |1002-${UNIQUEID}.wav,b                   |
--  6|default|1003 |       1|MixMonitor |1003-${UNIQUEID}.wav,b                   |
--  8|default|1001 |       3|GotoIf     |${DIALSTATUS}=BUSY?default,1002,1        |
--  9|default|1001 |       4|GotoIf     |${DIALSTATUS}=CHANUNAVAIL?default,1002,1 |
-- 10|default|1001 |       5|GotoIf     |${DIALSTATUS}=NOANSWER?default,1002,1    |
-- 11|default|1001 |      20|MusicOnHold|default                                  |
-- 12|default|1002 |      20|MusicOnHold|default                                  |
-- 13|default|1003 |      20|MusicOnHold|default                                  |
-- 14|default|1001 |      30|VoiceMail  |1001@default,u                           |
-- 15|default|1001 |      40|Hangup     |                                         |
-- 16|default|1002 |      30|VoiceMail  |1002@default,u                           |
-- 17|default|1002 |      40|Hangup     |                                         |
-- 18|default|1003 |      30|VoiceMail  |1003@default,u                           |
-- 19|default|1003 |      40|Hangup     |                                         |
-- 20|default|1002 |       6|GotoIf     |${DIALSTATUS}=BUSY?default,1002,30       |
-- 21|default|1002 |       7|GotoIf     |${DIALSTATUS}=CHANUNAVAIL?default,1002,30|
-- 22|default|1002 |       8|GotoIf     |${DIALSTATUS}=NOANSWER?default,1002,30   |
-- 23|default|1003 |       6|GotoIf     |${DIALSTATUS}=BUSY?default,1003,30       |
-- 24|default|1003 |       7|GotoIf     |${DIALSTATUS}=CHANUNAVAIL?default,1003,30|
-- 25|default|1003 |       8|GotoIf     |${DIALSTATUS}=NOANSWER?default,1003,30   |
-- 26|default|1002 |       3|Goto       |default,1002,6                           |
-- 27|default|1003 |       3|Goto       |default,1003,6                           |