45 lines
2.7 KiB
SQL
45 lines
2.7 KiB
SQL
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS telephony_status VARCHAR(32) NOT NULL DEFAULT 'ringing';
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS claimed_by_user VARCHAR(128);
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS claimed_at VARCHAR(64);
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS operator_extension VARCHAR(64);
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS channel_name VARCHAR(256);
|
|
ALTER TABLE asterisk_call_links ADD COLUMN IF NOT EXISTS connected_at VARCHAR(64);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_telephony_status ON asterisk_call_links(telephony_status);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_claimed_by_user ON asterisk_call_links(claimed_by_user);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_claimed_at ON asterisk_call_links(claimed_at);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_operator_extension ON asterisk_call_links(operator_extension);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_channel_name ON asterisk_call_links(channel_name);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_links_connected_at ON asterisk_call_links(connected_at);
|
|
|
|
UPDATE asterisk_call_links
|
|
SET telephony_status = CASE
|
|
WHEN status = 'ended' THEN 'ended'
|
|
ELSE 'ringing'
|
|
END
|
|
WHERE telephony_status IS NULL OR telephony_status = '';
|
|
|
|
CREATE TABLE IF NOT EXISTS asterisk_call_action_log (
|
|
id SERIAL PRIMARY KEY,
|
|
action_id VARCHAR(64) NOT NULL UNIQUE,
|
|
call_id VARCHAR(128) NOT NULL,
|
|
interaction_id VARCHAR(64),
|
|
action_type VARCHAR(64) NOT NULL,
|
|
actor_user VARCHAR(128) NOT NULL,
|
|
actor_role VARCHAR(32) NOT NULL,
|
|
request_json TEXT NOT NULL DEFAULT '{}',
|
|
result_status VARCHAR(32) NOT NULL,
|
|
ami_action_id VARCHAR(64),
|
|
error TEXT,
|
|
created_at VARCHAR(64) NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_action_id ON asterisk_call_action_log(action_id);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_call_id ON asterisk_call_action_log(call_id);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_interaction_id ON asterisk_call_action_log(interaction_id);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_action_type ON asterisk_call_action_log(action_type);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_actor_user ON asterisk_call_action_log(actor_user);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_actor_role ON asterisk_call_action_log(actor_role);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_result_status ON asterisk_call_action_log(result_status);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_ami_action_id ON asterisk_call_action_log(ami_action_id);
|
|
CREATE INDEX IF NOT EXISTS idx_asterisk_call_action_log_created_at ON asterisk_call_action_log(created_at);
|