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