-- Users table
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  slack_user_id VARCHAR(255) UNIQUE NOT NULL,
  slack_team_id VARCHAR(255) NOT NULL,
  slack_access_token TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Meeting sessions
CREATE TABLE IF NOT EXISTS meeting_sessions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  active_count INTEGER DEFAULT 0,
  started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_heartbeat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  source VARCHAR(50) DEFAULT 'companion',
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Status history
CREATE TABLE IF NOT EXISTS status_history (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  previous_status_text VARCHAR(500),
  previous_status_emoji VARCHAR(50),
  previous_status_expiration TIMESTAMP,
  meeting_status_text VARCHAR(500),
  meeting_status_emoji VARCHAR(50),
  restored_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_meeting_sessions_user_active ON meeting_sessions(user_id, active_count) WHERE active_count > 0;
CREATE INDEX idx_meeting_sessions_heartbeat ON meeting_sessions(last_heartbeat);
CREATE INDEX idx_status_history_user ON status_history(user_id);
