-- PostgreSQL Database Schema for Discord-style Chat Application
-- Created for Chat App MVP with Socket.IO, Session Authentication
-- Target: 10-30 users, 500ms response time requirement

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Users table - Core user information with session-based authentication
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL CHECK (LENGTH(email) >= 5 AND email ~ '^[^@]+@[^@]+\.[^@]+$'),
  password_hash VARCHAR(255) NOT NULL CHECK (LENGTH(password_hash) >= 60), -- bcrypt hash minimum
  display_name VARCHAR(100) NOT NULL CHECK (LENGTH(TRIM(display_name)) >= 1 AND LENGTH(display_name) <= 100),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Channels table - Chat channels/rooms for organized communication  
CREATE TABLE channels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(50) UNIQUE NOT NULL CHECK (LENGTH(TRIM(name)) >= 2 AND LENGTH(name) <= 50 AND name ~ '^[a-zA-Z0-9_-]+$'),
  description TEXT CHECK (description IS NULL OR LENGTH(description) <= 500),
  created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Messages table - Individual chat messages with full history
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  channel_id UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL CHECK (LENGTH(TRIM(content)) >= 1 AND LENGTH(content) <= 2000),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Channel Memberships table - User-channel participation tracking
CREATE TABLE channel_memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  channel_id UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  joined_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(channel_id, user_id) -- Prevent duplicate memberships
);

-- User Sessions table - Session-based authentication storage
CREATE TABLE user_sessions (
  session_id VARCHAR(128) PRIMARY KEY CHECK (LENGTH(session_id) >= 32),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at TIMESTAMP NOT NULL CHECK (expires_at > NOW()),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Performance Optimization Indexes
-- Message history retrieval (most frequent operation)
CREATE INDEX idx_messages_channel_created ON messages(channel_id, created_at);
CREATE INDEX idx_messages_user ON messages(user_id);

-- Channel membership lookups (frequent for access control)
CREATE INDEX idx_channel_memberships_user ON channel_memberships(user_id);
CREATE INDEX idx_channel_memberships_channel ON channel_memberships(channel_id);

-- Session management performance
CREATE INDEX idx_user_sessions_expires ON user_sessions(expires_at);

-- Channel management
CREATE INDEX idx_channels_created_by ON channels(created_by);
CREATE INDEX idx_channels_name ON channels(name); -- For name uniqueness checks

-- User lookup optimization
CREATE INDEX idx_users_email ON users(email);

-- Comments for key design decisions
COMMENT ON TABLE users IS 'Core user authentication with session-based auth (ADR-0002)';
COMMENT ON TABLE channels IS 'Discord-style channels for organized chat';
COMMENT ON TABLE messages IS 'Full message history with 500ms retrieval target';
COMMENT ON TABLE user_sessions IS 'Session storage for stateful authentication';

COMMENT ON INDEX idx_messages_channel_created IS 'Critical for message history pagination';
COMMENT ON INDEX idx_user_sessions_expires IS 'Session cleanup and validation performance';