Data Architecture

// Story 1.16.2: Company with meaningful ID (name)
// ADR-002: Logo storage via Generic File Upload Service
interface Company {
  name: string;  // Same as id - kept for backwards compatibility
  displayName: string;
  swissUID?: string;  // Swiss business ID (CHE-XXX.XXX.XXX)
  website?: string;
  industry?: string;
  description?: string;
  logoUrl?: string;      // CloudFront CDN URL (from logos.cloudfront_url)
  logoS3Key?: string;    // S3 key reference
  logoFileId?: string;   // References logos.upload_id (ADR-002)
  isVerified: boolean;
  createdAt: Date;
  updatedAt: Date;
  createdBy: string; // User ID who created this company
  // Note: employee relationships managed by User Service via User.companyId
  // Note: Partnership status managed by Partner Coordination Service via Partnership.companyId
  // Note: Logo storage uses Generic File Upload Service (ADR-002)
}

// ADR-002: Generic Logo entity (supports all entity types)
interface Logo {
  uploadId: string;              // Public identifier for tracking
  s3Key: string;                 // Current S3 key (temp or final)
  cloudFrontUrl?: string;        // CDN URL for access
  fileExtension: string;         // png, jpg, jpeg, svg
  fileSize: number;              // Size in bytes
  mimeType: string;              // image/png, etc.
  checksum?: string;             // SHA-256 for integrity
  status: LogoStatus;            // State machine
  associatedEntityType?: AssociatedEntityType;
  associatedEntityId?: string;   // Entity's identifier
  createdAt: Date;
  updatedAt: Date;
  expiresAt?: Date;              // For automatic cleanup
}

enum LogoStatus {
  PENDING = 'PENDING',           // Upload initiated, file may not exist in S3 yet
  CONFIRMED = 'CONFIRMED',       // File successfully uploaded to S3 and verified
  ASSOCIATED = 'ASSOCIATED'      // Linked to entity, file in final location
}

enum AssociatedEntityType {
  COMPANY = 'COMPANY',
  USER = 'USER',
  EVENT = 'EVENT',
  PARTNER = 'PARTNER',
  SPEAKER = 'SPEAKER',
  ATTENDEE = 'ATTENDEE'
}

// Partner is company-centric (per ADR-004)
interface Partner {
  id: string;
  companyId: string;                       // Primary relationship (FK to Company)
  partnershipLevel: PartnershipTier;
  partnershipStartDate: Date;
  partnershipEndDate?: Date;
  isActive: boolean;
  contacts: PartnerContact[];              // References User entities
  topicVotes: TopicVote[];
  topicSuggestions: TopicSuggestion[];
  meetingAttendance: PartnerMeetingAttendance[];
  benefits: PartnershipBenefits;
  createdAt: Date;
  updatedAt: Date;
}

// PartnerContact references User (per ADR-004)
interface PartnerContact {
  id: string;
  partnerId: string;                       // FK to Partner
  userId: string;                          // FK to User.id (NOT duplicating user fields)
  contactRole: ContactRole;                // Primary, Billing, Technical
  isPrimary: boolean;

  // API responses include User fields by joining:
  // username: string;                     // From User (API identifier)
  // email: string;                        // From User
  // firstName: string;                    // From User
  // lastName: string;                     // From User
  // profilePictureUrl: string;            // From User
}

enum ContactRole {
  PRIMARY = 'primary',
  BILLING = 'billing',
  TECHNICAL = 'technical',
  MARKETING = 'marketing'
}

interface PartnerAnalytics {
  totalEmployeeAttendanceAllEvents: number;
  averageAttendancePerEvent: number;
  contentEngagementScore: number;
  brandExposureMetrics: BrandExposure;
  roiCalculations: ROIMetrics;
  topicInfluenceScore: number;
}

enum PartnershipTier {
  BRONZE = 'bronze',
  SILVER = 'silver',
  GOLD = 'gold',
  PLATINUM = 'platinum',
  STRATEGIC = 'strategic'
}

// Story 1.16.2: User with meaningful ID (username)
// ADR-002: Profile picture via Generic File Upload Service
// ADR-004: User is single source of truth for email, name, bio, photo
interface User {
  id: string;  // Story 1.16.2: username (e.g., "john.doe"), not UUID
  cognitoUserId: string;
  email: string;
  firstName: string;
  lastName: string;
  bio?: string;  // ADR-004: Single source of truth (used by Speaker, Attendee, etc.)
  companyId?: string;  // Story 1.16.2: company name (e.g., "GoogleZH"), not UUID
  roles: Role[];
  preferences: UserPreferences;
  settings: UserSettings;
  profilePictureUrl?: string;     // CloudFront URL (from logos.cloudfront_url, ADR-002)
  profilePictureS3Key?: string;   // S3 key reference
  profilePictureFileId?: string;  // References logos.upload_id (ADR-002)
  isActive: boolean;
  createdAt: Date;
  updatedAt: Date;
  lastLoginAt?: Date;
}

interface UserPreferences {
  theme: 'light' | 'dark' | 'auto';
  language: 'de' | 'en' | 'fr' | 'it';
  emailNotifications: boolean;
  inAppNotifications: boolean;
  pushNotifications: boolean;
  notificationFrequency: 'immediate' | 'daily_digest' | 'weekly_digest';
  quietHoursStart?: string; // HH:mm format
  quietHoursEnd?: string;   // HH:mm format
}

interface UserSettings {
  profileVisibility: 'public' | 'members_only' | 'private';
  showEmail: boolean;
  showCompany: boolean;
  showActivityHistory: boolean;
  allowMessaging: boolean;
  allowCalendarSync: boolean;
  timezone: string;
  twoFactorEnabled: boolean;
}

enum Role {
  ORGANIZER = 'ORGANIZER',
  SPEAKER = 'SPEAKER',
  PARTNER = 'PARTNER',
  ATTENDEE = 'ATTENDEE'
}

interface ActivityHistory {
  id: string;
  userId: string;
  activityType: string; // event_registered, session_attended, topic_voted, etc.
  entityType: string;   // event, session, topic, etc.
  entityId: string;
  description: string;
  metadata: Record<string, any>;
  timestamp: Date;
}

interface Session {
  id: string;
  eventId: string;
  title: string;
  description: string;
  sessionType: SessionType;
  sessionUsers: SessionUser[]; // Multiple speakers (references User, not Speaker)
  startTime: Date;
  endTime: Date;
  room?: string;
  capacity?: number;
  materials: SessionMaterials;
  tags: string[];
  prerequisites?: string[];
  targetAudience: ExperienceLevel[];
  language: string;
  createdAt: Date;
  updatedAt: Date;
}

// ADR-004 pattern: Junction entity references User via userId
interface SessionUser {
  id: string;
  userId: string;          // FK to User.id (ADR-004 reference pattern)
  speakerRole: SpeakerRole;
  presentationTitle?: string; // Speaker-specific title if different
  isConfirmed: boolean;
  invitedAt: Date;
  confirmedAt?: Date;
  declinedAt?: Date;
  declineReason?: string;

  // API responses include User fields by joining:
  // username: string;        // From User
  // firstName: string;       // From User
  // lastName: string;        // From User
  // company: string;         // From User.companyId
  // profilePictureUrl: string; // From User
}

enum SessionType {
  KEYNOTE = 'keynote',
  PRESENTATION = 'presentation',
  WORKSHOP = 'workshop',
  PANEL_DISCUSSION = 'panel_discussion',
  NETWORKING = 'networking',
  BREAK = 'break',
  LUNCH = 'lunch'
}

enum SpeakerRole {
  PRIMARY_SPEAKER = 'primary_speaker',
  CO_SPEAKER = 'co_speaker',
  MODERATOR = 'moderator',
  PANELIST = 'panelist'
}

// Story 1.16.2: Event with meaningful ID (eventCode)
interface Event {
  id: string;  // Story 1.16.2: eventCode (e.g., "BATbern56"), not UUID
  eventNumber: number;
  title: string;
  description: string;
  eventDate: Date;
  registrationDeadline: Date;
  eventType: EventType;
  slotConfiguration: EventSlotConfiguration;
  venue: Venue;
  workflowState: EventWorkflowState;  // V17 migration: removed legacy 'status' field
  organizerId: string;  // Story 1.16.2: organizer username (e.g., "john.doe"), not UUID
  capacity: number;
  currentAttendeeCount: number;
  topics: Topic[];
  sessions: Session[]; // Sessions contain speaker relationships
  slots: EventSlot[];
  overflowManagement: OverflowManagement;
  qualityReview: EventQualityReview;
  createdAt: Date;
  updatedAt: Date;
  publishedAt?: Date;
  metadata: EventMetadata;
}

// EventStatus enum removed in V17 migration (2025-12-15)
// Replaced by EventWorkflowState (9-state workflow, see 06a-workflow-state-machines.md)

enum EventType {
  FULL_DAY = 'full_day',        // 6-8 slots
  AFTERNOON = 'afternoon',      // 6-8 slots
  EVENING = 'evening'           // 3-4 slots
}

// Epic 5: 9-state Event Workflow (Story 5.1a)
// Introduced in V12 migration, became the sole state field in V17 migration
// See 06a-workflow-state-machines.md for complete workflow documentation
enum EventWorkflowState {
  CREATED = 'CREATED',
  TOPIC_SELECTION = 'TOPIC_SELECTION',
  SPEAKER_BRAINSTORMING = 'SPEAKER_BRAINSTORMING',
  SPEAKER_OUTREACH = 'SPEAKER_OUTREACH',
  SPEAKER_CONFIRMATION = 'SPEAKER_CONFIRMATION',
  CONTENT_COLLECTION = 'CONTENT_COLLECTION',
  QUALITY_REVIEW = 'QUALITY_REVIEW',
  THRESHOLD_CHECK = 'THRESHOLD_CHECK',
  OVERFLOW_MANAGEMENT = 'OVERFLOW_MANAGEMENT',
  SLOT_ASSIGNMENT = 'SLOT_ASSIGNMENT',
  AGENDA_PUBLISHED = 'AGENDA_PUBLISHED',
  AGENDA_FINALIZED = 'AGENDA_FINALIZED',
  NEWSLETTER_SENT = 'NEWSLETTER_SENT',
  EVENT_READY = 'EVENT_READY',
  PARTNER_MEETING_COMPLETE = 'PARTNER_MEETING_COMPLETE',
  ARCHIVED = 'ARCHIVED'
}

interface EventSlotConfiguration {
  eventType: EventType;
  minSlots: number;
  maxSlots: number;
  slotDuration: number; // minutes
  theoreticalSlotsAM: boolean; // theoretical presentations in morning
  breakSlots: number;
  lunchSlots: number;
  // Story 15.2 — apéro knobs (added to the shared template AND the per-event override).
  // afternoon/evening templates default aperitifSlots=1 @ 90 min, position=end; full_day=0.
  aperitifSlots: number; // 0 = off, >=1 = on
  aperitifDuration: number; // minutes
  aperitifPosition: 'start' | 'end'; // after moderation-start | after moderation-end (default)
}

// Story 15.2 — per-event copy-on-edit override of the event-type template.
// One row per event (table `event_agenda_config`, FK event_id → events.id). Created only
// when an organizer first edits the event type in slot assignment; the shared `event_types`
// template is never mutated. Resolution (AgendaConfigResolver): per-event override if a row
// exists, else the shared template. Mirrors every EventSlotConfiguration knob + the apéro knobs.
interface EventAgendaConfig extends EventSlotConfiguration {
  eventId: string;
}

interface EventSlot {
  id: string;
  eventId: string;
  slotNumber: number;
  startTime: Date;
  endTime: Date;
  slotType: SlotType;
  assignedSessionId?: string;
  assignedSpeakerId?: string;
  isFlexible: boolean;
  technicalRequirements: TechnicalRequirement[];
}

enum SlotType {
  THEORETICAL = 'theoretical',
  PRACTICAL = 'practical',
  KEYNOTE = 'keynote',
  WORKSHOP = 'workshop',
  BREAK = 'break',
  LUNCH = 'lunch',
  NETWORKING = 'networking'
}

interface OverflowManagement {
  eventId: string;
  overflowSpeakers: OverflowSpeaker[];
  selectionVotes: SpeakerSelectionVote[];
  isVotingComplete: boolean;
  votingDeadline?: Date;
}

interface OverflowSpeaker {
  speakerId: string;
  sessionId: string;
  votes: number;
  topicFitScore: number;
  isSelected: boolean;
  declinedAt?: Date;
  declineReason?: string;
}

interface SpeakerSelectionVote {
  organizerId: string;
  speakerId: string;
  vote: VoteType;
  reason?: string;
  votedAt: Date;
}

enum VoteType {
  APPROVE = 'approve',
  REJECT = 'reject',
  ABSTAIN = 'abstain'
}

interface EventQualityReview {
  eventId: string;
  moderatorId: string;
  overallStatus: QualityReviewStatus;
  sessionReviews: ContentQualityReview[];
  reviewDeadline: Date;
  completedAt?: Date;
}

interface Topic {
  id: string;
  title: string;
  description: string;
  category: TopicCategory;
  createdDate: Date;
  lastUsedDate?: Date;
  usageCount: number;
  usageHistory: TopicUsageRecord[];
  stalenessScore: number; // 0-100, higher = safer to reuse
  calculatedWaitPeriod?: number; // in months
  partnerInfluenceScore?: number;
  isActive: boolean;
}

interface TopicUsageRecord {
  eventId: string;
  usedDate: Date;
  attendeeCount: number;
  feedbackScore?: number;
}

enum TopicCategory {
  TECHNICAL = 'technical',
  MANAGEMENT = 'management',
  SOFT_SKILLS = 'soft_skills',
  INDUSTRY_TRENDS = 'industry_trends',
  TOOLS_PLATFORMS = 'tools_platforms'
}

interface SpeakerSlotPreferences {
  speakerId: string;
  eventId: string;
  preferredTimeSlots: PreferredTimeSlot[];
  technicalRequirements: TechnicalRequirement[];
  cannotPresentAfter?: string; // time constraint (e.g., "16:00")
  ownLaptopRequired: boolean;
  specialEquipmentNeeds?: string;
  accessibilityRequirements?: string;
  submittedAt: Date;
  lastUpdatedAt: Date;
}

interface PreferredTimeSlot {
  startTime: string; // time format "09:00"
  endTime: string;   // time format "10:30"
  preference: PreferenceLevel;
}

enum PreferenceLevel {
  STRONGLY_PREFERRED = 'strongly_preferred',
  PREFERRED = 'preferred',
  ACCEPTABLE = 'acceptable',
  NOT_PREFERRED = 'not_preferred',
  UNAVAILABLE = 'unavailable'
}

interface TechnicalRequirement {
  id: string;
  requirement: string;
  isRequired: boolean;
  description?: string;
}

interface SlotAssignment {
  slotId: string;
  assignedAt: Date;
  assignedBy: string; // organizer ID
  automaticallyAssigned: boolean;
  manualOverride: boolean;
  conflictResolution?: string;
}

interface ContentQualityReview {
  id: string;
  sessionId: string;
  speakerId: string;
  reviewerId: string; // moderator ID
  abstractReview: AbstractReview;
  materialReview: MaterialReview;
  status: QualityReviewStatus;
  submittedAt: Date;
  reviewedAt?: Date;
  feedback?: string;
  revisionRequested: boolean;
  revisionDeadline?: Date;
}

interface AbstractReview {
  content: string;
  characterCount: number;
  hasLessonsLearned: boolean;
  hasProductPromotion: boolean;
  meetsStandards: boolean;
  issues: string[];
}

interface MaterialReview {
  bioReview: BiographyReview;
  photoReview: PhotoReview;
  additionalMaterials: MaterialItem[];
}

interface BiographyReview {
  content: string;
  characterCount: number;
  isAppropriate: boolean;
  issues: string[];
}

interface PhotoReview {
  photoUrl: string;
  isAppropriate: boolean;
  meetsTechnicalStandards: boolean;
  issues: string[];
}

interface MaterialItem {
  fileName: string;
  fileType: string;
  fileSize: number;
  uploadedAt: Date;
  isApproved: boolean;
  issues: string[];
}

enum QualityReviewStatus {
  PENDING = 'pending',
  IN_REVIEW = 'in_review',
  APPROVED = 'approved',
  REQUIRES_CHANGES = 'requires_changes',
  REJECTED = 'rejected',
  REVISION_SUBMITTED = 'revision_submitted'
}

interface ContentMetadata {
  fileId: string; // UUID
  s3Bucket: string; // Bucket name
  s3Key: string; // Full S3 object key
  originalFilename: string;
  fileSizeBytes: number;
  mimeType: string;
  checksum: string; // SHA-256 hash
  contentType: ContentType;
  uploadStatus: UploadStatus;
  uploadedBy: string; // User ID
  uploadedAt?: Date;
  cloudFrontUrl?: string; // CDN URL if published (cdn.batbern.ch or cdn.staging.batbern.ch)
  metadata: Record<string, string>; // Custom metadata
  createdAt: Date;
  updatedAt: Date;
}

enum ContentType {
  PRESENTATION = 'presentation',        // Session presentations
  SPEAKER_CV = 'speaker_cv',           // Speaker CVs
  EVENT_PHOTO = 'event_photo',         // Event photography
  ARCHIVE_MATERIAL = 'archive_material' // Historical content
  // NOTE: LOGO and SPEAKER_PHOTO removed - use logos table (ADR-002)
}

enum UploadStatus {
  PENDING = 'pending',
  UPLOADING = 'uploading',
  COMPLETED = 'completed',
  FAILED = 'failed',
  VIRUS_DETECTED = 'virus_detected',
  DELETED = 'deleted'
}

interface SessionMaterials {
  sessionId: string;
  presentationFiles: PresentationFile[];
  supplementaryMaterials: SupplementaryMaterial[];
  recordingUrl?: string;
  photosGallery?: string[]; // Array of ContentMetadata fileIds
  materialsPublishedAt?: Date;
}

interface PresentationFile {
  fileId: string; // References ContentMetadata
  title: string;
  description?: string;
  isPrimary: boolean;
  uploadedBy: string; // Speaker ID
  uploadedAt: Date;
  downloadCount: number;
  fileUrl: string; // CloudFront CDN URL (cdn.batbern.ch or cdn.staging.batbern.ch)
  lastAccessedAt?: Date;
}

interface SupplementaryMaterial {
  fileId: string; // References ContentMetadata
  title: string;
  description?: string;
  materialType: 'code_sample' | 'slides' | 'handout' | 'resource_link' | 'demo_video';
  uploadedBy: string;
  uploadedAt: Date;
  downloadCount: number;
}

interface StorageQuota {
  userId: string;
  userRole: UserRole;
  quotaLimitBytes: number; // -1 for unlimited (organizers)
  currentUsageBytes: number;
  fileCount: number;
  lastUpdated: Date;
  quotaWarningIssued: boolean;
  quotaExceededAt?: Date;
}

interface StorageUsageLog {
  id: string;
  userId: string;
  fileId: string;
  action: 'upload' | 'delete';
  fileSizeBytes: number;
  timestamp: Date;
  newTotalUsageBytes: number;
}

interface StorageQuotaInfo {
  quotaLimitBytes: number;
  currentUsageBytes: number;
  fileCount: number;
  percentageUsed: number;
  warningThresholdPercentage: number; // 80%
  availableBytes: number;
}

interface PresignedUploadUrl {
  uploadUrl: string;
  fileId: string;
  expiresIn: number; // seconds
  requiredHeaders: Record<string, string>;
}

interface PresignedDownloadUrl {
  downloadUrl: string;
  filename: string;
  fileSizeBytes: number;
  mimeType: string;
  expiresIn: number; // seconds
}

interface FileUploadRequest {
  filename: string;
  contentType: ContentType;
  fileSizeBytes: number;
  mimeType: string;
}

interface FileUploadConfirmation {
  fileId: string;
  checksum: string; // SHA-256 hash
}

-- Content metadata table (session content only - NOT profile images)
CREATE TABLE content_metadata (
    file_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    s3_bucket VARCHAR(255) NOT NULL,
    s3_key VARCHAR(1000) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_size_bytes BIGINT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    checksum VARCHAR(64), -- SHA-256 hash
    content_type VARCHAR(50) NOT NULL CHECK (content_type IN (
        'presentation',      -- Session presentations
        'speaker_cv',        -- Speaker CVs
        'event_photo',       -- Event photography
        'archive_material'   -- Historical content
        -- NOTE: 'logo' and 'speaker_photo' removed - use logos table (ADR-002)
    )),
    upload_status VARCHAR(20) NOT NULL CHECK (upload_status IN (
        'pending', 'uploading', 'completed', 'failed', 'virus_detected', 'deleted'
    )),
    uploaded_by UUID NOT NULL REFERENCES users(id),
    uploaded_at TIMESTAMP,
    cloudfront_url VARCHAR(500),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT unique_s3_key UNIQUE (s3_bucket, s3_key)
);

CREATE INDEX idx_content_metadata_uploaded_by ON content_metadata(uploaded_by);
CREATE INDEX idx_content_metadata_content_type ON content_metadata(content_type);
CREATE INDEX idx_content_metadata_upload_status ON content_metadata(upload_status);
CREATE INDEX idx_content_metadata_created_at ON content_metadata(created_at DESC);

-- Storage quota table
CREATE TABLE storage_quota (
    user_id UUID PRIMARY KEY REFERENCES users(id),
    user_role VARCHAR(50) NOT NULL,
    quota_limit_bytes BIGINT NOT NULL, -- -1 for unlimited
    current_usage_bytes BIGINT NOT NULL DEFAULT 0,
    file_count INTEGER NOT NULL DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    quota_warning_issued BOOLEAN NOT NULL DEFAULT false,
    quota_exceeded_at TIMESTAMP
);

CREATE INDEX idx_storage_quota_usage ON storage_quota(current_usage_bytes DESC);
CREATE INDEX idx_storage_quota_warning ON storage_quota(quota_warning_issued) WHERE quota_warning_issued = true;

-- Storage usage log table for audit trail
CREATE TABLE storage_usage_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    file_id UUID REFERENCES content_metadata(file_id),
    action VARCHAR(10) NOT NULL CHECK (action IN ('upload', 'delete')),
    file_size_bytes BIGINT NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    new_total_usage_bytes BIGINT NOT NULL
);

CREATE INDEX idx_storage_usage_log_user_id ON storage_usage_log(user_id, timestamp DESC);
CREATE INDEX idx_storage_usage_log_timestamp ON storage_usage_log(timestamp DESC);

-- Presentation files table (links content to sessions)
CREATE TABLE presentation_files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    file_id UUID NOT NULL REFERENCES content_metadata(file_id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    is_primary BOOLEAN NOT NULL DEFAULT false,
    uploaded_by UUID NOT NULL REFERENCES users(id),
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    download_count INTEGER NOT NULL DEFAULT 0,
    last_accessed_at TIMESTAMP,

    CONSTRAINT unique_session_primary_file UNIQUE (session_id, is_primary) WHERE is_primary = true
);

CREATE INDEX idx_presentation_files_session_id ON presentation_files(session_id);
CREATE INDEX idx_presentation_files_file_id ON presentation_files(file_id);
CREATE INDEX idx_presentation_files_download_count ON presentation_files(download_count DESC);

-- Supplementary materials table
CREATE TABLE supplementary_materials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    file_id UUID NOT NULL REFERENCES content_metadata(file_id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    material_type VARCHAR(50) NOT NULL CHECK (material_type IN (
        'code_sample', 'slides', 'handout', 'resource_link', 'demo_video'
    )),
    uploaded_by UUID NOT NULL REFERENCES users(id),
    uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    download_count INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX idx_supplementary_materials_session_id ON supplementary_materials(session_id);
CREATE INDEX idx_supplementary_materials_file_id ON supplementary_materials(file_id);

-- Trigger to update storage quota on file upload
CREATE OR REPLACE FUNCTION update_quota_on_upload()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.upload_status = 'completed' AND
       (OLD.upload_status IS NULL OR OLD.upload_status != 'completed') THEN

        UPDATE storage_quota
        SET current_usage_bytes = current_usage_bytes + NEW.file_size_bytes,
            file_count = file_count + 1,
            last_updated = CURRENT_TIMESTAMP
        WHERE user_id = NEW.uploaded_by;

        -- Check if quota exceeded
        UPDATE storage_quota
        SET quota_exceeded_at = CURRENT_TIMESTAMP
        WHERE user_id = NEW.uploaded_by
            AND quota_limit_bytes > 0
            AND current_usage_bytes > quota_limit_bytes
            AND quota_exceeded_at IS NULL;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_quota_on_upload
    AFTER INSERT OR UPDATE ON content_metadata
    FOR EACH ROW
    EXECUTE FUNCTION update_quota_on_upload();

-- Trigger to update storage quota on file deletion
CREATE OR REPLACE FUNCTION update_quota_on_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.upload_status = 'deleted' AND OLD.upload_status = 'completed' THEN
        UPDATE storage_quota
        SET current_usage_bytes = GREATEST(0, current_usage_bytes - NEW.file_size_bytes),
            file_count = GREATEST(0, file_count - 1),
            last_updated = CURRENT_TIMESTAMP,
            quota_warning_issued = false
        WHERE user_id = NEW.uploaded_by;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_quota_on_delete
    AFTER UPDATE ON content_metadata
    FOR EACH ROW
    WHEN (NEW.upload_status = 'deleted')
    EXECUTE FUNCTION update_quota_on_delete();

-- Trigger to increment download count
CREATE OR REPLACE FUNCTION increment_download_count()
RETURNS TRIGGER AS $$
BEGIN
    NEW.download_count = OLD.download_count + 1;
    NEW.last_accessed_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

// Attendee entity references User (per ADR-004)
interface Attendee {
  id: string;                              // UUID (internal)
  userId: string;                          // UUID FK to User.id (internal)

  // API responses include User fields by joining:
  // username: string;                     // From User (API identifier)
  // email: string;                        // From User
  // firstName: string;                    // From User
  // lastName: string;                     // From User
  // companyId?: string;                   // From User (optional)

  // Attendee-specific fields:
  eventRegistrations: EventRegistration[]; // Many-to-many with events
  engagementHistory: AttendeeEngagement;   // Content engagement tracking
  contentPreferences: ContentPreferences;  // Different from User.preferences
  newsletterSubscription: boolean;
  gdprConsent: GDPRConsent;
  createdAt: Date;
  updatedAt: Date;
}

interface EventRegistration {
  id: string;                          // UUID
  eventId: string;                     // FK to Event

  // Authenticated registration (existing pattern)
  attendeeId?: string;                 // UUID FK to Attendee (nullable for anonymous registrations)

  // Anonymous registration (new pattern - ADR-010)
  anonymousEmail?: string;             // Email for anonymous registration
  anonymousFirstName?: string;         // First name
  anonymousLastName?: string;          // Last name
  anonymousCompany?: string;           // Company
  anonymousRole?: string;              // Job title/role

  // Account linking (when anonymous user creates account)
  claimedByUserId?: string;            // User who claimed this registration
  claimedAt?: Date;                    // When registration was claimed

  // Registration metadata
  confirmationCode: string;            // Format: BAT-YYYY-NNNNNN (e.g., BAT-2025-000123)
  registrationDate: Date;
  status: RegistrationStatus;          // 'registered' | 'waitlisted' | 'confirmed' | 'cancelled' | 'attended'
  specialRequests?: string;
  attendanceConfirmed?: boolean;
  actualAttendance: boolean;
  sessionPreferences?: string[];       // Event-level registration, sessions are preferences only

  // Communication preferences
  newsletterSubscribed?: boolean;
  eventReminders?: boolean;
}

interface GDPRConsent {
  consentGiven: boolean;
  consentDate: Date;
  ipAddress: string;
  consentVersion: string;
}

-- user_profiles table (cognito_user_id is NULL for anonymous users)
INSERT INTO user_profiles (
    id, username, email, first_name, last_name, company_id, cognito_user_id
) VALUES (
    gen_random_uuid(),
    'john.doe',              -- Generated username
    'john@example.com',
    'John',
    'Doe',
    'AcmeCorp',              -- FK to companies
    NULL                     -- NULL = anonymous user (ADR-007)
);

-- registrations reference user by username (ADR-004: no duplicated user fields)
INSERT INTO registrations (
    id, event_code, attendee_username, registration_code, status
) VALUES (
    gen_random_uuid(),
    'BATbern25',             -- eventCode per ADR-003
    'john.doe',              -- References user_profiles.username (cross-service)
    'BAT-2025-000123',
    'confirmed'
);

-- Simply update the existing user_profile with the Cognito ID
UPDATE user_profiles SET
    cognito_user_id = 'cognito-sub-12345',
    claimed_at = NOW()
WHERE email = 'john@example.com'
  AND cognito_user_id IS NULL;

-- All registrations are ALREADY linked via attendee_username!
-- No migration, no copying, no clearing required.

-- Unique email prevents duplicate anonymous users
CREATE UNIQUE INDEX idx_user_profiles_email ON user_profiles(email);

-- Unique (event, attendee) prevents duplicate registrations
CREATE UNIQUE INDEX idx_registrations_event_attendee
    ON registrations(event_code, attendee_username);

interface EmailTemplate {
  id: string;
  templateType: TemplateType;
  language: 'de' | 'en';
  subject: string;
  htmlBody: string;
  textBody: string;
  variables: string[]; // e.g., ['speakerName', 'eventTitle', 'deadline']
  version: number;
  isActive: boolean;
  createdBy: string;
  createdAt: Date;
  updatedAt: Date;
}

enum TemplateType {
  SPEAKER_INVITATION = 'speaker_invitation',
  DEADLINE_REMINDER_48H = 'deadline_reminder_48h',
  DEADLINE_REMINDER_24H = 'deadline_reminder_24h',
  DEADLINE_CRITICAL = 'deadline_critical',
  MATERIAL_RECEIVED = 'material_received_confirmation',
  EVENT_PUBLISHED = 'event_published',
  NEWSLETTER_PROGRESSIVE = 'newsletter_progressive',
  NEWSLETTER_FINAL = 'newsletter_final',
  AGENDA_UPDATE = 'agenda_update'
}

interface NotificationPreferences {
  id: string;
  userId: string;
  channel: NotificationChannel;
  notificationType: string;
  isEnabled: boolean;
  frequency: FrequencyType;
  quietHoursStart?: string; // HH:MM format
  quietHoursEnd?: string; // HH:MM format
  updatedAt: Date;
}

enum NotificationChannel {
  EMAIL = 'email',
  IN_APP = 'in_app',
  PUSH = 'push',
  SMS = 'sms'
}

enum FrequencyType {
  IMMEDIATE = 'immediate',
  DAILY_DIGEST = 'daily_digest',
  WEEKLY_DIGEST = 'weekly_digest'
}

interface EscalationRule {
  id: string;
  eventId: string;
  ruleType: string; // e.g., 'speaker_deadline', 'content_review'
  tier1HoursBefore: number; // e.g., 48 hours
  tier2HoursBefore: number; // e.g., 24 hours
  tier3HoursBefore: number; // e.g., 0 hours (deadline)
  escalationThreshold?: number; // Minutes after deadline to escalate
  backupOrganizerIds: string[];
  isActive: boolean;
  createdAt: Date;
  updatedAt: Date;
}

interface NotificationLog {
  id: string;
  userId: string;
  notificationType: string;
  channel: NotificationChannel;
  templateId?: string;
  subject: string;
  sentAt: Date;
  deliveryStatus: DeliveryStatus;
  sesMessageId?: string;
  errorMessage?: string;
  metadata: Record<string, any>;
}

enum DeliveryStatus {
  SENT = 'sent',
  DELIVERED = 'delivered',
  BOUNCED = 'bounced',
  FAILED = 'failed',
  COMPLAINED = 'complained'
}

-- Events table (aggregate root)
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_number INTEGER UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    event_date TIMESTAMP WITH TIME ZONE NOT NULL,
    registration_deadline TIMESTAMP WITH TIME ZONE NOT NULL,
    venue_name VARCHAR(255) NOT NULL,
    venue_address TEXT NOT NULL,
    venue_capacity INTEGER NOT NULL,
    status VARCHAR(50) NOT NULL CHECK (status IN (
        'planning', 'topic_defined', 'speakers_invited', 'agenda_draft',
        'published', 'registration_open', 'registration_closed',
        'in_progress', 'completed', 'archived'
    )),
    organizer_id UUID NOT NULL,
    current_attendee_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    published_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}'
);

-- Sessions table (V2, updated in V21 for placeholder session support)
-- IMPORTANT: Sessions ARE the slot model for slot assignment workflow
-- Placeholder pattern: session created when speaker accepts (with title, but null times)
-- Timing assigned later: organizer sets start_time/end_time/room during slot assignment
-- See also: speaker_pool.session_id FK (V20) links speakers to sessions
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    session_type VARCHAR(50) CHECK (session_type IN (  -- V21: Nullable for placeholder sessions
        'keynote', 'presentation', 'workshop', 'panel_discussion',
        'networking', 'break', 'lunch'
    )),
    start_time TIMESTAMP WITH TIME ZONE,  -- V21: Nullable - assigned during slot assignment
    end_time TIMESTAMP WITH TIME ZONE,    -- V21: Nullable - assigned during slot assignment
    room VARCHAR(100),
    capacity INTEGER,
    language VARCHAR(10) DEFAULT 'de',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Session-User junction table (many-to-many speakers).
-- ADR-009 / Story 11.E.8: PRIMARY_SPEAKER row provisioned at the CONTACTED → READY
-- transition by SpeakerWorkflowService.runReadyHook (idempotent). is_confirmed flipped
-- at INVITED → ACCEPTED by runAcceptedHook. Username is the cross-service reference
-- per ADR-003 (re-keyed from user_id to username in V26). presentation_title was
-- dropped in V102 (Story 11.E.8) — sessions.title is canonical.
CREATE TABLE session_users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    username VARCHAR(100) NOT NULL, -- public identifier per ADR-003
    speaker_role VARCHAR(50) NOT NULL CHECK (speaker_role IN (
        'primary_speaker', 'co_speaker', 'moderator', 'panelist'
    )),
    is_confirmed BOOLEAN DEFAULT FALSE,
    invited_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    confirmed_at TIMESTAMP WITH TIME ZONE,
    declined_at TIMESTAMP WITH TIME ZONE,
    decline_reason TEXT,
    -- speaker name cache fields populated on assignment for full-text search (V38)
    speaker_first_name VARCHAR(100),
    speaker_last_name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(session_id, username)
);

-- Versioned audit log of SPEAKER submissions for a session (renamed from
-- speaker_content_submissions in V99 — Story 11.E.8 §2.7). Keyed by
-- (session_id, submission_version). Inserted only by ContentSubmissionService.submit()
-- — organizer session-edit modal updates sessions.title directly and does NOT write
-- a history row (Story 11.E.8 §2.9 — history reflects what the speaker submitted,
-- not all edits). The latest row mirrors sessions.title / sessions.description at the
-- moment of submission; reviewer feedback on a rejected version stays on that row for
-- the audit trail. submitted_by_username records the actor (speaker on the portal or
-- organizer-on-behalf). Reviewer feedback is written here on rejection; approval is
-- recorded by the speaker_status_history transition to QUALITY_REVIEWED.
CREATE TABLE session_content_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    abstract TEXT NOT NULL,
    abstract_char_count INTEGER NOT NULL,
    submission_version INTEGER NOT NULL DEFAULT 1,
    submitted_by_username VARCHAR(100) NOT NULL,
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    reviewer_feedback TEXT,         -- non-null iff this version was rejected
    reviewed_at TIMESTAMP WITH TIME ZONE,
    reviewed_by VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(session_id, submission_version)
);

-- Topics table with usage tracking
CREATE TABLE topics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title VARCHAR(500) NOT NULL,
    description TEXT,
    category VARCHAR(50) NOT NULL CHECK (category IN (
        'technical', 'management', 'soft_skills',
        'industry_trends', 'tools_platforms'
    )),
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_used_date TIMESTAMP WITH TIME ZONE,
    usage_count INTEGER DEFAULT 0,
    staleness_score NUMERIC(5,2) DEFAULT 100.00,
    calculated_wait_period INTEGER, -- in months
    partner_influence_score NUMERIC(5,2),
    is_active BOOLEAN DEFAULT TRUE,
    title_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', title)) STORED,
    description_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(description, ''))) STORED,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Topic usage history
CREATE TABLE topic_usage_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    topic_id UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
    used_date TIMESTAMP WITH TIME ZONE NOT NULL,
    attendee_count INTEGER,
    feedback_score NUMERIC(3,2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_events_status ON events(status);
CREATE INDEX idx_events_event_date ON events(event_date);
CREATE INDEX idx_sessions_event_id ON sessions(event_id);
CREATE INDEX idx_session_users_session_id ON session_users(session_id);
CREATE INDEX idx_session_users_username ON session_users(username);
CREATE INDEX idx_session_content_history_session ON session_content_history(session_id);
CREATE INDEX idx_session_content_history_submitted_by ON session_content_history(submitted_by_username);
CREATE INDEX idx_session_users_confirmed ON session_users(is_confirmed);
CREATE INDEX idx_topics_title_vector ON topics USING GIN(title_vector);
CREATE INDEX idx_topics_description_vector ON topics USING GIN(description_vector);
CREATE INDEX idx_topics_last_used ON topics(last_used_date);
CREATE INDEX idx_topics_staleness ON topics(staleness_score);
CREATE INDEX idx_topics_active ON topics(is_active);
CREATE INDEX idx_topic_usage_history_topic_id ON topic_usage_history(topic_id);
CREATE INDEX idx_topic_usage_history_used_date ON topic_usage_history(used_date DESC);

-- Companies table
CREATE TABLE companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    swiss_uid VARCHAR(20),
    website VARCHAR(500),
    industry VARCHAR(100),
    description TEXT,
    logo_url VARCHAR(1000),      -- CloudFront URL (populated after logo association)
    logo_s3_key VARCHAR(500),     -- S3 key reference
    logo_file_id VARCHAR(255),    -- References logos.upload_id (ADR-002)
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Note: User-company relationships are managed in User Management Service
-- Query employee count: SELECT COUNT(*) FROM users WHERE company_id = '{companyId}'
-- Note: Partnership status managed in Partner Coordination Service via Partnership.companyId
-- Note: Logo storage uses Generic File Upload Service (ADR-002)

-- Generic Logos Table (ADR-002: Generic File Upload Service)
-- Supports all entity types: COMPANY, USER, EVENT, PARTNER, etc.
CREATE TABLE logos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    upload_id VARCHAR(100) UNIQUE NOT NULL,  -- Public identifier for tracking
    s3_key VARCHAR(500) NOT NULL,             -- Current S3 key (temp or final)
    cloudfront_url VARCHAR(1000),             -- CDN URL for access
    file_extension VARCHAR(10) NOT NULL,      -- png, jpg, jpeg, svg
    file_size BIGINT NOT NULL,                -- Size in bytes
    mime_type VARCHAR(100) NOT NULL,          -- image/png, etc.
    checksum VARCHAR(100),                    -- SHA-256 for integrity

    -- State machine (ADR-002 three-phase upload pattern)
    status VARCHAR(20) NOT NULL CHECK (status IN (
        'PENDING',      -- Upload initiated, file may not exist in S3 yet
        'CONFIRMED',    -- File successfully uploaded to S3 and verified
        'ASSOCIATED'    -- Linked to entity, file in final location
    )),

    -- Entity association (populated when ASSOCIATED)
    associated_entity_type VARCHAR(50) CHECK (associated_entity_type IN (
        'COMPANY', 'USER', 'EVENT', 'PARTNER', 'SPEAKER', 'ATTENDEE'
    )),
    associated_entity_id VARCHAR(255),        -- Entity's identifier (name or username)

    -- Lifecycle timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,      -- For automatic cleanup

    -- Constraints
    CONSTRAINT valid_association CHECK (
        (status = 'ASSOCIATED' AND associated_entity_type IS NOT NULL AND associated_entity_id IS NOT NULL) OR
        (status != 'ASSOCIATED')
    )
);

-- Indexes
CREATE INDEX idx_companies_name ON companies(name);
CREATE INDEX idx_companies_swiss_uid ON companies(swiss_uid);
CREATE INDEX idx_companies_is_verified ON companies(is_verified);
CREATE INDEX idx_companies_logo_file_id ON companies(logo_file_id);

-- Logos table indexes (ADR-002)
CREATE UNIQUE INDEX idx_logos_upload_id ON logos(upload_id);
CREATE INDEX idx_logos_status ON logos(status);
CREATE INDEX idx_logos_status_expires ON logos(status, expires_at) WHERE status != 'ASSOCIATED';
CREATE INDEX idx_logos_entity_association ON logos(associated_entity_type, associated_entity_id) WHERE status = 'ASSOCIATED';

-- ADR-002: Three-Phase Upload Pattern
-- Phase 1: POST /logos/presigned-url → Creates logo with status=PENDING, temp S3 key
-- Phase 2: Confirm upload → Updates status=CONFIRMED
-- Phase 3: Entity creation → Copies to final S3 location, status=ASSOCIATED, associates with entity
--
-- Example S3 key progression:
--   PENDING/CONFIRMED: logos/temp/{uploadId}/logo-{fileId}.png
--   ASSOCIATED:        logos/2025/companies/GoogleZH/logo-{fileId}.png
--
-- Cleanup: Scheduled job deletes PENDING > 24h, CONFIRMED > 7 days

-- Partners table (ADR-003: stores companyName, NOT company_id UUID)
CREATE TABLE partners (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    company_name VARCHAR(12) NOT NULL UNIQUE, -- ADR-003: Meaningful ID, NO UUID FK
    partnership_level VARCHAR(50) NOT NULL CHECK (partnership_level IN (
        'bronze', 'silver', 'gold', 'platinum', 'strategic'
    )),
    partnership_start_date DATE NOT NULL,
    partnership_end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Partner contacts (ADR-003: stores username, NOT user_id UUID)
CREATE TABLE partner_contacts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE CASCADE,
    username VARCHAR(100) NOT NULL, -- ADR-003: Meaningful ID, NO UUID FK to users
    contact_role VARCHAR(50) NOT NULL CHECK (contact_role IN (
        'primary', 'billing', 'technical', 'marketing'
    )),
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(partner_id, username)
);

-- Topic voting
CREATE TABLE topic_votes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    topic_id UUID NOT NULL, -- References topic in Event Management Service
    partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE CASCADE,
    vote_weight INTEGER DEFAULT 1, -- Based on partnership_level
    vote_value INTEGER NOT NULL CHECK (vote_value BETWEEN 1 AND 5),
    voted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(topic_id, partner_id)
);

-- Partner topic suggestions
CREATE TABLE topic_suggestions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE CASCADE,
    suggested_topic VARCHAR(500) NOT NULL,
    description TEXT,
    business_justification TEXT,
    suggested_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    status VARCHAR(50) NOT NULL CHECK (status IN (
        'submitted', 'under_review', 'accepted', 'rejected', 'implemented'
    )) DEFAULT 'submitted',
    reviewed_at TIMESTAMP WITH TIME ZONE,
    reviewed_by UUID -- References organizer
);

-- Partner meetings
CREATE TABLE partner_meetings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    meeting_type VARCHAR(50) NOT NULL CHECK (meeting_type IN ('spring', 'autumn', 'ad_hoc')),
    scheduled_date DATE NOT NULL,
    location VARCHAR(255),
    agenda TEXT,
    materials_generated BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Partner meeting attendance
CREATE TABLE partner_meeting_attendance (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    meeting_id UUID NOT NULL REFERENCES partner_meetings(id) ON DELETE CASCADE,
    partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE CASCADE,
    rsvp_status VARCHAR(50) NOT NULL CHECK (rsvp_status IN (
        'invited', 'accepted', 'declined', 'tentative', 'attended'
    )) DEFAULT 'invited',
    rsvp_at TIMESTAMP WITH TIME ZONE,
    attended BOOLEAN DEFAULT FALSE,
    UNIQUE(meeting_id, partner_id)
);

-- Indexes
CREATE INDEX idx_partners_company_name ON partners(company_name); -- Changed from company_id
CREATE INDEX idx_partners_active ON partners(is_active);
CREATE INDEX idx_partners_partnership_level ON partners(partnership_level);
CREATE INDEX idx_partner_contacts_partner_id ON partner_contacts(partner_id);
CREATE INDEX idx_partner_contacts_username ON partner_contacts(username);
CREATE INDEX idx_topic_votes_partner_id ON topic_votes(partner_id);
CREATE INDEX idx_topic_votes_topic_id ON topic_votes(topic_id);
CREATE INDEX idx_topic_suggestions_partner_id ON topic_suggestions(partner_id);
CREATE INDEX idx_topic_suggestions_status ON topic_suggestions(status);
CREATE INDEX idx_partner_meetings_date ON partner_meetings(scheduled_date);
CREATE INDEX idx_partner_meeting_attendance_meeting_id ON partner_meeting_attendance(meeting_id);
CREATE INDEX idx_partner_meeting_attendance_partner_id ON partner_meeting_attendance(partner_id);

-- Attendees table (ADR-004: References User, no duplicated fields)
CREATE TABLE attendees (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL UNIQUE,  -- FK to users.id (one-to-one)

    -- Domain-specific fields only (NO email, name, company, position)
    newsletter_subscription BOOLEAN DEFAULT FALSE,
    content_preferences JSONB DEFAULT '{}', -- Topics of interest (different from User.preferences)

    -- GDPR consent tracking
    gdpr_consent_given BOOLEAN DEFAULT FALSE,
    gdpr_consent_date TIMESTAMP WITH TIME ZONE,
    gdpr_consent_ip VARCHAR(45), -- IPv4 or IPv6
    gdpr_consent_version VARCHAR(20),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Foreign key constraint with cascade delete
    CONSTRAINT fk_attendee_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

-- Indexes
CREATE UNIQUE INDEX idx_attendees_user_id ON attendees(user_id);
CREATE INDEX idx_attendees_newsletter ON attendees(newsletter_subscription) WHERE newsletter_subscription = true;

-- Event registrations
-- ADR-004: References user by attendee_username (no duplicated user fields)
-- ADR-007: Unified user_profiles handles both anonymous and authenticated users
CREATE TABLE registrations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_code VARCHAR(20) NOT NULL,  -- References event (ADR-003 meaningful ID)

    -- Cross-service reference to user_profiles.username (ADR-004)
    -- User details (email, name, company) fetched via UserManagementClient API
    attendee_username VARCHAR(100) NOT NULL,

    -- Registration metadata
    registration_code VARCHAR(20) NOT NULL UNIQUE, -- Format: BAT-YYYY-NNNNNN
    registration_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    status VARCHAR(50) NOT NULL CHECK (status IN (
        'registered', 'waitlisted', 'confirmed', 'cancelled', 'attended'
    )) DEFAULT 'registered',
    special_requests TEXT,
    attendance_confirmed BOOLEAN DEFAULT FALSE,
    actual_attendance BOOLEAN DEFAULT FALSE,

    -- Communication preferences
    newsletter_subscribed BOOLEAN DEFAULT FALSE,
    event_reminders BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Prevent duplicate registrations
    UNIQUE(event_code, attendee_username)
);

-- Content engagement tracking
CREATE TABLE content_engagement (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    attendee_username VARCHAR(100) NOT NULL, -- Cross-service reference
    content_type VARCHAR(50) NOT NULL, -- 'session', 'document', 'video', etc.
    content_id UUID NOT NULL,
    engagement_type VARCHAR(50) NOT NULL, -- 'view', 'download', 'rating', etc.
    engagement_value DECIMAL(5,2), -- rating score or duration
    engaged_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_registrations_event_code ON registrations(event_code);
CREATE INDEX idx_registrations_attendee_username ON registrations(attendee_username);
CREATE INDEX idx_registrations_status ON registrations(status);
CREATE INDEX idx_registrations_registration_code ON registrations(registration_code); -- For public lookup

CREATE INDEX idx_content_engagement_attendee ON content_engagement(attendee_username);
CREATE INDEX idx_content_engagement_content ON content_engagement(content_type, content_id);

-- Example query: Get registration with user data (cross-service enrichment)
-- 1. Query registrations table for event registrations
-- 2. Call UserManagementClient.getUsersByUsernames(attendeeUsernames)
-- 3. Join/merge results in application layer
-- See docs/guides/microservices-http-clients.md for HTTP client patterns

-- User Roles with History
CREATE TABLE user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    role VARCHAR(50) NOT NULL CHECK (role IN ('ORGANIZER', 'SPEAKER', 'PARTNER', 'ATTENDEE')),
    event_id UUID REFERENCES events(id), -- Optional: role scoped to specific event
    granted_by UUID NOT NULL, -- References users
    granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP,
    revoked_by UUID, -- References users
    reason TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT unique_active_user_role UNIQUE (user_id, role, event_id) WHERE is_active = true,
    CONSTRAINT fk_granted_by FOREIGN KEY (granted_by) REFERENCES users(id),
    CONSTRAINT fk_revoked_by FOREIGN KEY (revoked_by) REFERENCES users(id)
);

CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_active ON user_roles(is_active, user_id);
CREATE INDEX idx_user_roles_event ON user_roles(event_id) WHERE event_id IS NOT NULL;

-- Role Change Requests (for approval workflows)
CREATE TABLE role_change_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    current_role VARCHAR(50) NOT NULL,
    requested_role VARCHAR(50) NOT NULL,
    requested_by UUID NOT NULL, -- Organizer initiating change
    requires_approval_from UUID, -- User who must approve (for organizer demotions)
    reason TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'EXPIRED')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),

    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_requested_by FOREIGN KEY (requested_by) REFERENCES users(id),
    CONSTRAINT fk_approval_from FOREIGN KEY (requires_approval_from) REFERENCES users(id)
);

CREATE INDEX idx_role_requests_user ON role_change_requests(user_id);
CREATE INDEX idx_role_requests_status ON role_change_requests(status, expires_at);
CREATE INDEX idx_role_requests_approver ON role_change_requests(requires_approval_from) WHERE requires_approval_from IS NOT NULL;

-- Role Change Approvals
CREATE TABLE role_change_approvals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role_change_request_id UUID NOT NULL,
    approved_by UUID NOT NULL, -- User providing approval
    approved BOOLEAN NOT NULL,
    comments TEXT,
    approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_role_change_request FOREIGN KEY (role_change_request_id) REFERENCES role_change_requests(id),
    CONSTRAINT fk_approved_by FOREIGN KEY (approved_by) REFERENCES users(id),
    CONSTRAINT unique_approval_per_request UNIQUE (role_change_request_id, approved_by)
);

CREATE INDEX idx_approvals_request ON role_change_approvals(role_change_request_id);

-- Notifications table (Event Management Service - V25 migration)
-- ADR-003 compliant: Uses meaningful IDs (recipient_username, event_code)
-- Hybrid storage: Email audit trail (create rows), in-app queries (dynamic)
CREATE TABLE IF NOT EXISTS notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- ADR-003: Meaningful IDs (NOT foreign keys)
    recipient_username VARCHAR(100) NOT NULL,
    event_code VARCHAR(50),  -- Nullable (non-event notifications)

    -- Notification details
    notification_type VARCHAR(50) NOT NULL,
    channel VARCHAR(20) NOT NULL,
    priority VARCHAR(20) DEFAULT 'NORMAL',
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,

    -- Delivery tracking
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    sent_at TIMESTAMP,
    read_at TIMESTAMP,
    failed_at TIMESTAMP,
    failure_reason TEXT,

    -- Metadata
    metadata JSONB,

    -- Timestamps
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_notifications_recipient ON notifications(recipient_username);
CREATE INDEX idx_notifications_event ON notifications(event_code) WHERE event_code IS NOT NULL;
CREATE INDEX idx_notifications_status ON notifications(status);
CREATE INDEX idx_notifications_created ON notifications(created_at DESC);
CREATE INDEX idx_notifications_recipient_created ON notifications(recipient_username, created_at DESC);
CREATE INDEX idx_notifications_recipient_status ON notifications(recipient_username, status);

-- Comment for documentation
COMMENT ON TABLE notifications IS 'Notification delivery tracking (email/SMS audit trail). In-app notifications queried dynamically. Follows Task System pattern (Story 5.5). ADR-003 compliant (meaningful IDs).';

-- Function to enforce minimum 2 organizers rule
CREATE OR REPLACE FUNCTION enforce_minimum_organizers()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.role = 'ORGANIZER' AND NEW.is_active = false THEN
        IF (SELECT COUNT(*) FROM user_roles
            WHERE role = 'ORGANIZER'
            AND is_active = true
            AND (event_id = NEW.event_id OR (event_id IS NULL AND NEW.event_id IS NULL))
            AND id != NEW.id) < 2 THEN
            RAISE EXCEPTION 'Cannot demote organizer: minimum 2 organizers required';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_minimum_organizers
    BEFORE UPDATE ON user_roles
    FOR EACH ROW
    WHEN (OLD.is_active = true AND NEW.is_active = false)
    EXECUTE FUNCTION enforce_minimum_organizers();

// Shared domain events for data consistency
interface DomainEvent {
  id: string;
  aggregateId: string;
  aggregateType: string;
  eventType: string;
  eventVersion: number;
  occurredAt: Date;
  data: Record<string, any>;
}

// Example events
interface CompanyCreatedEvent extends DomainEvent {
  eventType: 'CompanyCreated';
  data: {
    companyId: string;
    name: string;
    swissUID?: string;
    isVerified: boolean;
  };
}

interface SpeakerInvitedEvent extends DomainEvent {
  eventType: 'SpeakerInvited';
  data: {
    speakerId: string;
    sessionId: string;
    eventId: string;
    invitedAt: Date;
  };
}