44 lines
2.8 KiB
SQL
44 lines
2.8 KiB
SQL
CREATE TABLE caller_analysis_calls (
|
|
id CHAR(64) NOT NULL PRIMARY KEY, payload JSON NULL, updated_at DATETIME(3) NOT NULL
|
|
);
|
|
CREATE TABLE caller_analysis_events (
|
|
id CHAR(64) NOT NULL PRIMARY KEY, call_key CHAR(64) NOT NULL, occurred_at DATETIME(3) NOT NULL,
|
|
payload JSON NOT NULL, created_at DATETIME(3) NOT NULL,
|
|
INDEX caller_events_call (call_key, occurred_at), INDEX caller_events_retention (created_at)
|
|
);
|
|
CREATE TABLE caller_analysis_states (
|
|
id CHAR(64) NOT NULL PRIMARY KEY, call_key CHAR(64) NOT NULL, view VARCHAR(16) NOT NULL,
|
|
started_at DATETIME(3) NOT NULL, customer_id VARCHAR(32) NOT NULL, customer_gateway_id VARCHAR(32) NOT NULL,
|
|
vendor_id VARCHAR(32) NOT NULL, vendor_gateway_id VARCHAR(32) NOT NULL, caller VARCHAR(64) NOT NULL,
|
|
callee VARCHAR(64) NOT NULL, city VARCHAR(12) NOT NULL, carrier VARCHAR(20) NOT NULL,
|
|
counts JSON NOT NULL, payload JSON NOT NULL, updated_at DATETIME(3) NOT NULL,
|
|
INDEX caller_states_window (view, started_at), INDEX caller_states_customer (customer_id, view, started_at),
|
|
INDEX caller_states_number (view, caller, started_at), INDEX caller_states_call (call_key)
|
|
);
|
|
CREATE TABLE caller_analysis_minute_buckets (
|
|
id CHAR(64) NOT NULL PRIMARY KEY, started_at DATETIME(3) NOT NULL, view VARCHAR(16) NOT NULL,
|
|
customer_id VARCHAR(32) NOT NULL, customer_gateway_id VARCHAR(32) NOT NULL,
|
|
vendor_id VARCHAR(32) NOT NULL, vendor_gateway_id VARCHAR(32) NOT NULL, caller VARCHAR(64) NOT NULL,
|
|
city VARCHAR(12) NOT NULL, carrier VARCHAR(20) NOT NULL, counts JSON NOT NULL,
|
|
INDEX caller_buckets_window (view, started_at), INDEX caller_buckets_customer (customer_id, view, started_at),
|
|
INDEX caller_buckets_number (view, caller, started_at)
|
|
);
|
|
CREATE TABLE caller_analysis_access (
|
|
user_id VARCHAR(32) NOT NULL, customer_id VARCHAR(32) NOT NULL, PRIMARY KEY(user_id, customer_id)
|
|
);
|
|
CREATE TABLE caller_analysis_health (
|
|
id VARCHAR(64) NOT NULL PRIMARY KEY, payload JSON NOT NULL, updated_at DATETIME(3) NOT NULL
|
|
);
|
|
CREATE TABLE caller_analysis_alerts (
|
|
id CHAR(64) NOT NULL PRIMARY KEY, customer_id VARCHAR(32) NOT NULL, view VARCHAR(16) NOT NULL,
|
|
caller VARCHAR(64) NOT NULL, status VARCHAR(20) NOT NULL, payload JSON NOT NULL,
|
|
updated_at DATETIME(3) NOT NULL, INDEX caller_alerts_customer (customer_id, status, updated_at)
|
|
);
|
|
INSERT INTO permissions (id, module, action, description, created_at, updated_at)
|
|
VALUES ('caller_analytics.view', 'caller_analytics', 'view', '查看授权客户的主叫分析', NOW(3), NOW(3)),
|
|
('caller_analytics.view_all', 'caller_analytics', 'view_all', '查看全部客户的主叫分析', NOW(3), NOW(3));
|
|
INSERT INTO role_permissions (role_id, permission_id, created_at)
|
|
SELECT id, 'caller_analytics.view', NOW(3) FROM roles WHERE id='ROLE_SUPER_ADMIN';
|
|
INSERT INTO role_permissions (role_id, permission_id, created_at)
|
|
SELECT id, 'caller_analytics.view_all', NOW(3) FROM roles WHERE id='ROLE_SUPER_ADMIN';
|