45 lines
2.5 KiB
SQL
45 lines
2.5 KiB
SQL
ALTER TABLE asterisk_call_links ADD COLUMN telephony_status TEXT NOT NULL DEFAULT 'ringing';
|
|
ALTER TABLE asterisk_call_links ADD COLUMN claimed_by_user TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN claimed_at TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN operator_extension TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN channel_name TEXT;
|
|
ALTER TABLE asterisk_call_links ADD COLUMN connected_at TEXT;
|
|
|
|
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 INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
action_id TEXT NOT NULL UNIQUE,
|
|
call_id TEXT NOT NULL,
|
|
interaction_id TEXT,
|
|
action_type TEXT NOT NULL,
|
|
actor_user TEXT NOT NULL,
|
|
actor_role TEXT NOT NULL,
|
|
request_json TEXT NOT NULL DEFAULT '{}',
|
|
result_status TEXT NOT NULL,
|
|
ami_action_id TEXT,
|
|
error TEXT,
|
|
created_at TEXT 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);
|