38 lines
1.3 KiB
MySQL
38 lines
1.3 KiB
MySQL
|
-- Migration 041: Create engagement tables for Microsoft Graph activity data
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS graph_users (
|
||
|
|
id VARCHAR(255) PRIMARY KEY, -- Azure AD object ID
|
||
|
|
display_name VARCHAR(255),
|
||
|
|
email VARCHAR(255) UNIQUE, -- matches resources.email
|
||
|
|
job_title VARCHAR(255),
|
||
|
|
department VARCHAR(255),
|
||
|
|
account_enabled BOOLEAN DEFAULT true,
|
||
|
|
last_activity_date DATE,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS engagement_snapshots (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_email VARCHAR(255) NOT NULL,
|
||
|
|
period_type VARCHAR(10) NOT NULL, -- 'D7', 'D30', 'D90'
|
||
|
|
period_end DATE NOT NULL,
|
||
|
|
-- Teams
|
||
|
|
teams_chat_messages INT DEFAULT 0,
|
||
|
|
teams_private_messages INT DEFAULT 0,
|
||
|
|
teams_calls INT DEFAULT 0,
|
||
|
|
teams_meetings_attended INT DEFAULT 0,
|
||
|
|
teams_meetings_organized INT DEFAULT 0,
|
||
|
|
-- Email
|
||
|
|
emails_sent INT DEFAULT 0,
|
||
|
|
emails_received INT DEFAULT 0,
|
||
|
|
emails_read INT DEFAULT 0,
|
||
|
|
-- Last activity
|
||
|
|
last_activity_date DATE,
|
||
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(user_email, period_type, period_end)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_engagement_snapshots_email ON engagement_snapshots(user_email);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_engagement_snapshots_period ON engagement_snapshots(period_type, period_end);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_graph_users_email ON graph_users(email);
|