models.py

from datetime import datetime
import secrets
import json
import uuid
from typing import Optional

from flask_login import UserMixin
from werkzeug.security import generate_password_hash, check_password_hash

from extensions import db
from utils.query_scoping import UserScopedMixin
from utils.database_types import JSONField, JSONFieldWithDefault
from utils.encrypted_field import EncryptedField
from utils.uuid_utils import generate_uuid
from utils.datetime_utils import utc_now


class User(db.Model, UserMixin):
    """User accounts for authentication and data isolation."""
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    account_uuid = db.Column(db.String(32), unique=True, nullable=False, index=True)  # UUID4 hex (no hyphens)
    username = db.Column(db.String(80), unique=True, nullable=False, index=True)
    email = db.Column(db.String(120), unique=True, nullable=True, index=True)  # Optional for now
    password_hash = db.Column(db.String(255), nullable=False)
    display_name = db.Column(db.String(100), nullable=True)  # Defaults to username on registration

    # User preferences (migrated from session)
    theme = db.Column(db.String(10), nullable=False, default='dark')
    font_family = db.Column(db.String(20), nullable=False, default='poppins')  # poppins, inter, roboto, system, merriweather, opensans
    font_weight = db.Column(db.Integer, nullable=False, default=300)  # 100-900 (100=thin, 300=light, 400=regular, 600=semibold, 700=bold, 900=black)

    # Button customization (slider-based)
    btn_border_radius = db.Column(db.Integer, nullable=False, default=8)  # 0-50px (0=sharp, 8=rounded, 50=pill)
    btn_shadow_depth = db.Column(db.Integer, nullable=False, default=3)  # 0-10 (0=flat, 3=moderate, 10=dramatic)
    btn_hover_lift = db.Column(db.Integer, nullable=False, default=2)  # 0-10px vertical movement on hover
    btn_hover_grow = db.Column(db.Integer, nullable=False, default=0)  # 0-10 (maps to 0-10% scale increase)
    btn_animation_speed = db.Column(db.Integer, nullable=False, default=300)  # 100-500ms transition speed

    # Badge customization (slider-based)
    badge_border_radius = db.Column(db.Integer, nullable=False, default=6)  # 0-50px (0=square, 6=rounded, 50=pill)
    badge_padding_scale = db.Column(db.Integer, nullable=False, default=10)  # 0-20 (padding multiplier)
    badge_font_size = db.Column(db.Integer, nullable=False, default=12)  # 8-16px
    badge_font_weight = db.Column(db.Integer, nullable=False, default=500)  # 100-900

    # Card customization (slider-based, global for all cards)
    card_border_radius = db.Column(db.Integer, nullable=False, default=8)  # 0-24px border radius
    card_shadow_depth = db.Column(db.Integer, nullable=False, default=2)  # 0-10 shadow intensity
    card_border_thickness = db.Column(db.Integer, nullable=False, default=1)  # 0-4px border thickness
    card_hover_lift = db.Column(db.Integer, nullable=False, default=0)  # 0-10px vertical lift on hover

    # Event customization (slider-based)
    event_border_radius = db.Column(db.Integer, nullable=False, default=8)  # 0-24px border radius
    event_shadow_depth = db.Column(db.Integer, nullable=False, default=2)  # 0-10 shadow intensity
    event_border_thickness = db.Column(db.Integer, nullable=False, default=1)  # 0-4px border thickness
    event_hover_lift = db.Column(db.Integer, nullable=False, default=0)  # 0-10px vertical lift on hover

    # Page transition animation
    page_transition_animation = db.Column(db.Boolean, nullable=False, default=False)  # Slide-up animation on page load

    # Click counter gamification
    banked_clicks = db.Column(db.Integer, nullable=False, default=0)

    # Account management
    is_active = db.Column(db.Boolean, nullable=False, default=True)
    is_admin = db.Column(db.Boolean, nullable=False, default=False)

    # Notification preferences (JSON) - controls what notifications user receives
    notification_preferences = db.Column(db.JSON, nullable=False, default=lambda: {
        'friend_requests': True,
        'friend_accepted': True,
        'group_invites': True,
        'group_activity': True,
        'share_received': True,
        'pulse_updates': True,
        'chat_messages': True,
        'notification_sound': True
    })

    # Privacy settings
    profile_visibility = db.Column(db.String(20), nullable=False, default='everyone')  # everyone, friends, nobody
    activity_visibility = db.Column(db.String(20), nullable=False, default='friends')  # everyone, friends, nobody
    discoverable = db.Column(db.Boolean, nullable=False, default=True)  # Appear in user search
    show_online_status = db.Column(db.Boolean, nullable=False, default=True)  # Show online/last seen

    # Timestamps
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    updated_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now, onupdate=utc_now)
    last_login = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    events = db.relationship("Event", foreign_keys="[Event.user_id]", backref="owner", lazy=True, cascade="all,delete")
    calendar_sources = db.relationship("CalendarSource", backref="owner", lazy=True, cascade="all,delete")
    series_templates = db.relationship("SeriesTemplate", backref="owner", lazy=True, cascade="all,delete")
    custom_categories = db.relationship("Category", backref="owner", lazy=True, cascade="all,delete")
    account_history = db.relationship("AccountHistory", backref="user", lazy=True, cascade="all,delete", foreign_keys="[AccountHistory.user_id]")

    @staticmethod
    def generate_account_uuid() -> str:
        """Generate a unique account UUID (UUID4 hex without hyphens)."""
        return uuid.uuid4().hex

    def set_password(self, password: str):
        """Hash and set the user's password."""
        self.password_hash = generate_password_hash(password)

    def check_password(self, password: str) -> bool:
        """Verify the user's password."""
        return check_password_hash(self.password_hash, password)

    def __repr__(self) -> str:
        return f"<User {self.id} {self.username} uuid={self.account_uuid[:8]}...>"


class InviteCode(db.Model):
    """Invite codes for user registration."""
    __tablename__ = "invite_codes"

    id = db.Column(db.Integer, primary_key=True)
    code = db.Column(db.String(32), unique=True, nullable=False, index=True)
    created_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)  # None = system generated
    used_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)

    is_used = db.Column(db.Boolean, nullable=False, default=False)
    max_uses = db.Column(db.Integer, nullable=False, default=1)
    uses_count = db.Column(db.Integer, nullable=False, default=0)

    expires_at = db.Column(db.DateTime(timezone=True), nullable=True)
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    used_at = db.Column(db.DateTime(timezone=True), nullable=True)

    @staticmethod
    def generate_code(length: int = 16) -> str:
        """Generate a random invite code."""
        return secrets.token_urlsafe(length)[:length].upper()

    def __repr__(self) -> str:
        return f"<InviteCode {self.code} used={self.is_used}>"


class AccountHistory(db.Model):
    """Tracks changes to user accounts (username and display_name changes)."""
    __tablename__ = "account_history"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False, index=True)
    change_type = db.Column(db.String(20), nullable=False)  # 'username' or 'display_name'
    old_value = db.Column(db.String(100), nullable=True)
    new_value = db.Column(db.String(100), nullable=False)
    changed_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)

    # Admin tracking (for future admin tools)
    changed_by_admin = db.Column(db.Boolean, nullable=False, default=False)
    admin_user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=True)
    reason = db.Column(db.Text, nullable=True)

    # Performance indexes
    __table_args__ = (
        db.Index('idx_account_history_user_id', 'user_id'),
        db.Index('idx_account_history_changed_at', 'changed_at'),
        db.Index('idx_account_history_type', 'change_type'),
    )

    def __repr__(self) -> str:
        return f"<AccountHistory user={self.user_id} type={self.change_type} {self.old_value}->{self.new_value}>"


class TimestampMixin:
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    updated_at = db.Column(
        db.DateTime, nullable=False, default=utc_now, onupdate=utc_now
    )


class Category(db.Model, TimestampMixin):
    """Category definitions - both system (built-in) and user-created custom categories."""
    __tablename__ = "categories"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=True, index=True)  # NULL = system category

    # Category identification
    key = db.Column(db.String(50), nullable=False)  # 'event', 'meeting', 'movie_release', etc.
    display_label = db.Column(db.String(100), nullable=False)  # "Event", "Meeting", "Movie Release"

    # Visual properties
    color = db.Column(db.String(7), nullable=False, default="#3b82f6")  # Hex color
    icon = db.Column(db.String(10), nullable=True)  # Emoji or icon identifier

    # Category type
    is_system = db.Column(db.Boolean, nullable=False, default=False)  # True for built-in categories

    # Ensure unique category keys per user (system categories have user_id=NULL)
    __table_args__ = (
        db.Index('idx_category_user_key', 'user_id', 'key'),
        db.UniqueConstraint('user_id', 'key', name='uq_user_category_key'),
    )

    def __repr__(self) -> str:
        return f"<Category {self.id} {self.key} system={self.is_system}>"


# REMOVED (Roadmap #12): Checklist, ChecklistItem models
# App pivoted to events-only - no more checklists/tasks


class SeriesTemplate(db.Model, TimestampMixin):
    """Importable series template - stores metadata about series collections."""
    __tablename__ = "series_templates"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=True, index=True)  # None = public/system template
    name = db.Column(db.String(200), nullable=False)  # "US Holidays 2025", "Weekly Cleaning"
    description = db.Column(db.Text, nullable=True)
    author = db.Column(db.String(200), nullable=True)
    category = db.Column(db.String(50), nullable=True)  # "holidays", "routines", "shopping", etc.

    # Sharing settings
    is_public = db.Column(db.Boolean, nullable=False, default=False)  # If True, all users can see/import
    is_system = db.Column(db.Boolean, nullable=False, default=False)  # System-provided templates (holidays, etc.)

    # Track if this was imported and from where
    is_imported = db.Column(db.Boolean, nullable=False, default=False)
    import_source = db.Column(db.String(500), nullable=True)  # filename, URL, or "built-in"
    imported_at = db.Column(db.DateTime(timezone=True), nullable=True)

    def __repr__(self) -> str:
        return f"<SeriesTemplate {self.id} {self.title}>"


class RecurringEvent(db.Model, TimestampMixin, UserScopedMixin):
    """
    RRULE-based recurring event pattern.

    Stores event metadata directly (NO template reference) because events are simple:
    title + category + time fields. On-demand instance computation generates virtual
    Event objects that behave like database records but aren't persisted.

    Uses RFC5545 RRULE format - fully compatible with OAuth calendar APIs.
    """
    __tablename__ = "recurring_events"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    entry_uuid = db.Column(db.String(32), unique=True, nullable=False, index=True, default=lambda: uuid.uuid4().hex)

    # Dual-ownership model: Entry is owned by EITHER a user OR a pulse (not both)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=True, index=True)  # User-owned
    pulse_id = db.Column(db.String(36), db.ForeignKey("pulses.id", ondelete='CASCADE'), nullable=True, index=True)  # Pulse-owned (CASCADE: delete when Pulse deleted)
    created_by_user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='SET NULL'), nullable=True)

    # Event metadata (stored directly)
    title = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text, nullable=True)
    category = db.Column(db.String(20), nullable=False, default='event')
    time_type = db.Column(db.String(20), nullable=False, default='all_day')
    start_time = db.Column(db.Time, nullable=True)
    end_time = db.Column(db.Time, nullable=True)

    # RFC5545 RRULE string OR holiday rule name
    rrule = db.Column(db.Text, nullable=True)  # NULL if using holiday_name
    holiday_name = db.Column(db.String(100), nullable=True)  # e.g., "Easter Sunday", "Thanksgiving"
    start_date = db.Column(db.Date, nullable=True)  # When recurrence begins

    # Pulse tracking (Multi-Pulse Reference Architecture v2)
    # REMOVED: pulse_id and pulse_item_id (replaced by pulse_items backref)
    # Access Pulses via: entry.pulse_items -> List[PulseItem]
    subscription_id = db.Column(db.String(36), db.ForeignKey('pulse_subscriptions.id'), nullable=True)
    is_pulse_entry = db.Column(db.Boolean, nullable=False, default=False)  # True if this is a Pulse master copy
    is_hidden_by_pulse = db.Column(db.Boolean, nullable=False, default=False)  # True if hidden to avoid duplicates

    # Link to source template if imported
    source_template_id = db.Column(db.String(36), db.ForeignKey("series_templates.id"), nullable=True)

    # Relationships
    event_instances = db.relationship("Event", backref="recurring_event", lazy=True, cascade="all,delete")
    exceptions = db.relationship("EventException", backref="recurring_event", lazy=True, cascade="all,delete")

    # Performance indexes
    __table_args__ = (
        db.Index('idx_recurring_event_user', 'user_id'),
        db.Index('idx_recurring_event_pulse', 'pulse_id'),  # Pulse-owned entries
        # Constraint: exactly ONE owner (user OR pulse, not both)
        db.CheckConstraint(
            '(user_id IS NOT NULL AND pulse_id IS NULL) OR (user_id IS NULL AND pulse_id IS NOT NULL)',
            name='check_recurring_event_single_owner'
        ),
    )

    def get_rrule_object(self):
        """Parse rrule string into dateutil.rrule object."""
        if not self.rrule:
            return None

        from dateutil.rrule import rrulestr
        from datetime import datetime

        dtstart = self.start_date or datetime(2025, 1, 1).date()
        rrule_text = f"DTSTART:{dtstart.strftime('%Y%m%d')}\nRRULE:{self.rrule}"
        return rrulestr(rrule_text)

    def compute_instances(self, start_date, end_date):
        """Compute occurrence dates within range."""
        rrule_obj = self.get_rrule_object()
        if not rrule_obj:
            return []

        from datetime import datetime

        start_dt = datetime.combine(start_date, datetime.min.time())
        end_dt = datetime.combine(end_date, datetime.max.time())

        instances = []
        for dt in rrule_obj:
            if dt.date() > end_date:
                break
            if dt.date() >= start_date:
                instances.append(dt.date())

        return instances

    def get_human_readable_pattern(self) -> str:
        """Convert RRULE to human-readable description."""
        if not self.rrule:
            return "No recurrence pattern"

        parts = dict(p.split('=') for p in self.rrule.split(';'))
        freq = parts.get('FREQ', '').upper()

        if freq == 'DAILY':
            interval = int(parts.get('INTERVAL', 1))
            return "Every day" if interval == 1 else f"Every {interval} days"
        elif freq == 'WEEKLY':
            byday = parts.get('BYDAY', '')
            if byday:
                day_map = {'MO': 'Monday', 'TU': 'Tuesday', 'WE': 'Wednesday',
                           'TH': 'Thursday', 'FR': 'Friday', 'SA': 'Saturday', 'SU': 'Sunday'}
                days = [day_map.get(d, d) for d in byday.split(',')]
                return f"Every {' and '.join(days)}"
            return "Every week"
        elif freq == 'MONTHLY':
            bymonthday = parts.get('BYMONTHDAY', '')
            if bymonthday:
                suffix = 'th' if bymonthday not in ['1','21','31'] else ('st' if bymonthday == '1' else ('nd' if bymonthday == '2' else 'rd'))
                return f"{bymonthday}{suffix} of every month"
            return "Monthly"
        elif freq == 'YEARLY':
            bymonth = parts.get('BYMONTH', '')
            bymonthday = parts.get('BYMONTHDAY', '')
            if bymonth and bymonthday:
                month_names = ['', 'January', 'February', 'March', 'April', 'May', 'June',
                               'July', 'August', 'September', 'October', 'November', 'December']
                month_name = month_names[int(bymonth)] if int(bymonth) <= 12 else bymonth
                return f"{month_name} {bymonthday}"
            return "Yearly"

        return self.rrule

    @property
    def owner_type(self):
        """Returns 'pulse' if pulse-owned, 'user' if user-owned."""
        return 'pulse' if self.pulse_id else 'user'

    @property
    def owner_id(self):
        """Returns the ID of the owner (pulse_id or user_id)."""
        return self.pulse_id if self.pulse_id else self.user_id

    @property
    def time_descriptor(self) -> Optional[str]:
        """Human-readable time display (e.g., '3:00 PM' or '3:00 PM - 5:00 PM')."""
        if self.time_type == 'all_day' or not self.start_time:
            return None

        def format_time(t):
            """Format time as 12-hour with AM/PM."""
            return t.strftime('%I:%M %p').lstrip('0')

        if self.end_time and self.end_time != self.start_time:
            return f"{format_time(self.start_time)} - {format_time(self.end_time)}"
        return format_time(self.start_time)

    def __repr__(self) -> str:
        return f"<RecurringEvent {self.id} {self.title} rrule={self.rrule}>"


# REMOVED (Roadmap #12): RecurringChecklist model
# App pivoted to events-only - recurring checklists removed


# Exception models for recurring events

class EventException(db.Model, TimestampMixin):
    """Tracks exceptions to recurring event patterns (deleted or modified instances)."""
    __tablename__ = "event_exceptions"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    recurring_event_id = db.Column(db.String(36), db.ForeignKey("recurring_events.id", ondelete='CASCADE'), nullable=False)
    exception_date = db.Column(db.Date, nullable=False)
    exception_type = db.Column(db.String(20), nullable=False)  # 'deleted', 'modified'

    # If modified, store reference to the modified event instance
    modified_event_id = db.Column(db.String(36), db.ForeignKey("events.id", ondelete='CASCADE'), nullable=True)

    # Performance indexes
    __table_args__ = (
        db.Index('idx_event_exception_recurring', 'recurring_event_id', 'exception_date'),
    )

    def __repr__(self) -> str:
        return f"<EventException {self.id} {self.exception_type} on {self.exception_date}>"


# REMOVED (Roadmap #12): ChecklistException model - Events-Only Pivot

# REMOVED: SeriesException model (2025-10-17)
# Was replaced by EventException (for events) and ChecklistException (for checklists)
# ChecklistException also removed in Roadmap #12


class Event(db.Model, TimestampMixin, UserScopedMixin):
    """Calendar event entry - the core entity in Pulsefeed (Roadmap #12)."""
    __tablename__ = "events"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    entry_uuid = db.Column(db.String(32), unique=True, nullable=False, index=True, default=lambda: uuid.uuid4().hex)

    # Dual-ownership model: Entry is owned by EITHER a user OR a pulse (not both)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=True, index=True)  # User-owned
    pulse_id = db.Column(db.String(36), db.ForeignKey("pulses.id", ondelete='CASCADE'), nullable=True, index=True)  # Pulse-owned (CASCADE: delete when Pulse deleted)

    created_by_user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='SET NULL'), nullable=True)
    title = db.Column(db.String(255), nullable=False)
    description = db.Column(db.Text, nullable=True)

    # Time fields - ALWAYS populated (required for calendar entries)
    start_date = db.Column(db.Date, nullable=False)  # REQUIRED
    start_time = db.Column(db.Time, nullable=True)
    end_date = db.Column(db.Date, nullable=True)
    end_time = db.Column(db.Time, nullable=True)
    time_type = db.Column(db.String(20), nullable=False, default='all_day')

    # Category (event, birthday, holiday, etc.)
    category = db.Column(db.String(20), nullable=False, default="event")

    # Recurring pattern links (if this is part of a recurring pattern)
    recurring_event_id = db.Column(db.String(36), db.ForeignKey("recurring_events.id", ondelete='CASCADE'), nullable=True)

    # Group attribution (Roadmap #8) - where this entry came from
    source_group_id = db.Column(db.String(36), db.ForeignKey("groups.id", ondelete='SET NULL'), nullable=True)

    # Pulse tracking (Multi-Pulse Reference Architecture v2)
    # REMOVED: pulse_id and pulse_item_id (replaced by pulse_items backref)
    # Access Pulses via: entry.pulse_items -> List[PulseItem]
    subscription_id = db.Column(db.String(36), db.ForeignKey('pulse_subscriptions.id'), nullable=True)
    is_pulse_entry = db.Column(db.Boolean, nullable=False, default=False)  # True if this is a Pulse master copy
    is_hidden_by_pulse = db.Column(db.Boolean, nullable=False, default=False)  # True if hidden to avoid duplicates

    # Calendar import tracking
    import_source_id = db.Column(db.String(36), db.ForeignKey("calendar_sources.id", ondelete='CASCADE'), nullable=True)
    is_imported = db.Column(db.Boolean, nullable=False, default=False)

    # Local annotations for imported events (Roadmap #15)
    # These fields allow curators to add notes/overrides while preserving original iCal data
    local_notes = db.Column(db.Text, nullable=True)  # Curator annotations
    local_category_override = db.Column(db.String(20), nullable=True)  # Override imported category

    # Comment count (denormalized for performance)
    comment_count = db.Column(db.Integer, nullable=False, default=0)

    # Relationships
    imported_event_data = db.relationship("ImportedEvent", backref="event", uselist=False, cascade="all,delete")
    import_source = db.relationship("CalendarSource", backref="events", foreign_keys=[import_source_id])
    # REMOVED: pulse relationship (use pulse_items backref instead)

    # Performance indexes (REDUCED from original - only essential)
    __table_args__ = (
        db.Index('idx_event_user_date', 'user_id', 'start_date'),  # Most common query
        db.Index('idx_event_recurring', 'recurring_event_id'),  # Recurring event lookups
        db.Index('idx_event_import_source', 'import_source_id'),  # Import sync
        db.Index('idx_event_pulse', 'pulse_id'),  # Pulse-owned entries
        # Constraint: exactly ONE owner (user OR pulse, not both)
        db.CheckConstraint(
            '(user_id IS NOT NULL AND pulse_id IS NULL) OR (user_id IS NULL AND pulse_id IS NOT NULL)',
            name='check_event_single_owner'
        ),
    )

    @property
    def owner_type(self):
        """Returns 'pulse' if pulse-owned, 'user' if user-owned."""
        return 'pulse' if self.pulse_id else 'user'

    @property
    def owner_id(self):
        """Returns the ID of the owner (pulse_id or user_id)."""
        return self.pulse_id if self.pulse_id else self.user_id

    @property
    def time_descriptor(self) -> Optional[str]:
        """Human-readable time display (e.g., '3:00 PM' or '3:00 PM - 5:00 PM')."""
        if self.time_type == 'all_day' or not self.start_time:
            return None

        def format_time(t):
            """Format time as 12-hour with AM/PM."""
            return t.strftime('%I:%M %p').lstrip('0')

        if self.end_time and self.end_time != self.start_time:
            return f"{format_time(self.start_time)} - {format_time(self.end_time)}"
        return format_time(self.start_time)

    def __repr__(self) -> str:
        return f"<Event {self.id} {self.title}>"


# REMOVED (Roadmap #12): Task, TaskCompletion, UserTaskCompletion, CollaborativeTaskCompletion models - Events-Only Pivot


class EventComment(db.Model):
    """User comments on Events with threading and voting support."""
    __tablename__ = "event_comments"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    event_id = db.Column(db.String(36), db.ForeignKey('events.id', ondelete='CASCADE'), nullable=False)
    parent_id = db.Column(db.String(36), db.ForeignKey('event_comments.id', ondelete='CASCADE'), nullable=True)
    comment_text = db.Column(db.Text, nullable=False)
    vote_score = db.Column(db.Integer, nullable=False, default=1)  # Start at 1 (author's implicit upvote)
    is_deleted = db.Column(db.Boolean, nullable=False, default=False)  # Soft delete for threaded comments
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    edited_at = db.Column(db.DateTime(timezone=True), nullable=True)
    edit_history = db.Column(db.JSON, nullable=True)  # List of {text, edited_at} for admin view

    # Relationships
    user = db.relationship('User', backref='event_comments', foreign_keys=[user_id])
    event = db.relationship('Event', backref='comments', foreign_keys=[event_id])
    parent = db.relationship('EventComment', remote_side=[id], backref='replies', foreign_keys=[parent_id])

    __table_args__ = (
        db.Index('idx_event_comment_event', 'event_id', 'created_at'),
        db.Index('idx_event_comment_user', 'user_id'),
        db.Index('idx_event_comment_parent', 'parent_id'),
    )

    @property
    def has_visible_content(self) -> bool:
        """
        Check if this comment or any descendant is visible (not deleted).

        Used to hide fully-deleted comment chains from the UI.
        Returns True if this comment should be shown.
        """
        # Fallback to True if is_deleted doesn't exist (pre-migration comments)
        if not getattr(self, 'is_deleted', False):
            return True
        # Deleted - check if any reply has visible content
        replies = getattr(self, 'replies', []) or []
        return any(reply.has_visible_content for reply in replies)

    def __repr__(self) -> str:
        return f"<EventComment {self.id} user={self.user_id} event={self.event_id}>"


class CommentVote(db.Model):
    """
    Polymorphic votes on comments (both PulseComment and EventComment).

    vote_value: +1 for upvote, -1 for downvote
    """
    __tablename__ = "comment_votes"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    comment_type = db.Column(db.String(20), nullable=False)  # 'pulse' or 'event'
    comment_id = db.Column(db.String(36), nullable=False)
    vote_value = db.Column(db.Integer, nullable=False)  # +1 or -1
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)

    # Relationships
    user = db.relationship('User', backref='comment_votes', foreign_keys=[user_id])

    __table_args__ = (
        db.UniqueConstraint('user_id', 'comment_type', 'comment_id', name='unique_comment_vote'),
        db.Index('idx_comment_vote_comment', 'comment_type', 'comment_id'),
        db.Index('idx_comment_vote_user', 'user_id'),
        db.CheckConstraint('vote_value IN (-1, 1)', name='check_vote_value'),
    )

    def __repr__(self) -> str:
        return f"<CommentVote user={self.user_id} {self.comment_type}:{self.comment_id} vote={self.vote_value}>"


class CommentReport(db.Model):
    """
    Reports on comments for admin review.

    Polymorphic: works for both PulseComment and EventComment.
    """
    __tablename__ = "comment_reports"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    reporter_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    comment_type = db.Column(db.String(20), nullable=False)  # 'pulse' or 'event'
    comment_id = db.Column(db.String(36), nullable=False)

    # Report details
    reason = db.Column(db.String(50), nullable=False)  # spam, harassment, misinformation, off_topic, other
    details = db.Column(db.Text, nullable=True)  # Optional additional details

    # Status tracking
    status = db.Column(db.String(20), nullable=False, default='pending')  # pending, reviewed, actioned, dismissed
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    reviewed_at = db.Column(db.DateTime(timezone=True), nullable=True)
    reviewed_by_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    admin_notes = db.Column(db.Text, nullable=True)

    # Relationships
    reporter = db.relationship('User', backref='submitted_reports', foreign_keys=[reporter_id])
    reviewed_by = db.relationship('User', backref='reviewed_reports', foreign_keys=[reviewed_by_id])

    __table_args__ = (
        db.Index('idx_comment_report_status', 'status', 'created_at'),
        db.Index('idx_comment_report_comment', 'comment_type', 'comment_id'),
        db.Index('idx_comment_report_reporter', 'reporter_id'),
    )

    def __repr__(self) -> str:
        return f"<CommentReport {self.id} {self.comment_type}:{self.comment_id} reason={self.reason} status={self.status}>"


class CalendarSource(db.Model, TimestampMixin, UserScopedMixin):
    """External calendar connection (iCal URL) for importing events."""
    __tablename__ = "calendar_sources"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False, index=True)
    name = db.Column(db.String(200), nullable=False)  # "Work Calendar", "Family Events"
    provider = db.Column(db.String(50), nullable=True)  # 'google', 'outlook', 'apple', 'other'
    ical_url = db.Column(db.Text, nullable=False)  # The webcal/https URL

    # Sync configuration
    sync_enabled = db.Column(db.Boolean, nullable=False, default=True)
    last_sync = db.Column(db.DateTime(timezone=True), nullable=True)
    sync_interval_minutes = db.Column(db.Integer, nullable=False, default=60)

    # Import settings
    default_category = db.Column(db.String(20), nullable=False, default='event')
    color = db.Column(db.String(7), nullable=True)  # Optional color for all events from this source

    # Pulse integration (Roadmap #15) - iCal sources feed into Pulses
    pulse_id = db.Column(db.String(36), db.ForeignKey("pulses.id", ondelete='SET NULL'), nullable=True, index=True)

    # Relationships
    imported_events = db.relationship("ImportedEvent", backref="source", lazy=True, cascade="all,delete")
    pulse = db.relationship("Pulse", backref="calendar_sources", lazy=True)

    def __repr__(self) -> str:
        return f"<CalendarSource {self.id} {self.name}>"


class ImportedEvent(db.Model, TimestampMixin):
    """Links external calendar events to Event objects."""
    __tablename__ = "imported_events"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    source_id = db.Column(db.String(36), db.ForeignKey("calendar_sources.id", ondelete='CASCADE'), nullable=False)

    # External event tracking
    external_uid = db.Column(db.String(500), nullable=False)  # UID from iCal
    last_modified = db.Column(db.DateTime(timezone=True), nullable=True)  # LAST-MODIFIED from iCal

    # Link to our Event object
    event_id = db.Column(db.String(36), db.ForeignKey("events.id", ondelete='CASCADE'), nullable=False, unique=True)

    # Sync status
    sync_status = db.Column(db.String(20), nullable=False, default='synced')
        # 'synced' = up to date
        # 'updated' = changed on external calendar, needs update
        # 'deleted' = removed from external calendar
        # 'modified_locally' = user edited, don't overwrite

    # Store original data for debugging
    raw_data = db.Column(db.Text, nullable=True)  # JSON blob of iCal data

    # Unique: one external event per source
    __table_args__ = (
        db.UniqueConstraint('source_id', 'external_uid', name='uq_source_uid'),
        db.Index('idx_imported_source_id', 'source_id'),
        db.Index('idx_imported_event_id', 'event_id'),
    )

    def __repr__(self) -> str:
        return f"<ImportedEvent {self.id} {self.external_uid}>"


# ===== SOCIAL SYSTEM MODELS =====

class UserProfile(db.Model, TimestampMixin):
    """Extended user profile information with privacy settings."""
    __tablename__ = "user_profiles"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), unique=True, nullable=False)

    # Profile info
    bio = db.Column(db.Text, nullable=True)
    avatar_url = db.Column(db.String(500), nullable=True)
    location = db.Column(db.String(100), nullable=True)
    timezone = db.Column(db.String(50), nullable=True)

    # Privacy settings (private by default)
    profile_visibility = db.Column(db.String(20), nullable=False, default='private')  # private, friends, public
    show_email = db.Column(db.Boolean, nullable=False, default=False)
    allow_friend_requests = db.Column(db.Boolean, nullable=False, default=True)

    # Cached stats for performance
    friend_count = db.Column(db.Integer, nullable=False, default=0)
    shared_count = db.Column(db.Integer, nullable=False, default=0)

    # Relationship back to user
    user = db.relationship("User", backref=db.backref("profile", uselist=False), foreign_keys=[user_id])

    def __repr__(self) -> str:
        return f"<UserProfile user={self.user_id} visibility={self.profile_visibility}>"


class Friendship(db.Model):
    """Friend connections between users."""
    __tablename__ = "friendships"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False)
    friend_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False)
    status = db.Column(db.String(20), nullable=False, default='pending')  # pending, accepted, blocked
    initiated_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    accepted_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    user = db.relationship("User", foreign_keys=[user_id], backref="friendships_as_user")
    friend = db.relationship("User", foreign_keys=[friend_id], backref="friendships_as_friend")
    initiator = db.relationship("User", foreign_keys=[initiated_by])

    # Indexes and constraints
    __table_args__ = (
        db.UniqueConstraint('user_id', 'friend_id', name='unique_friendship'),
        db.Index('idx_friendship_user', 'user_id'),
        db.Index('idx_friendship_friend', 'friend_id'),
        db.Index('idx_friendship_status', 'status'),
        # Friend list query optimization (Phase 2.1)
        db.Index('idx_friendship_user_status_date', 'user_id', 'status', 'accepted_at'),
    )

    def __repr__(self) -> str:
        return f"<Friendship {self.user_id}<->{self.friend_id} status={self.status}>"


# REMOVED: SharedContent model (Roadmap #10 Phase 3) - Pulses are THE sharing method
# Originally for friend-to-friend one-time copy sharing
# Table 'shared_content' can be safely dropped in future migration

# REMOVED: MarketplaceItem model (unused - zero implementation, no routes)
# Originally intended for public marketplace feature, but replaced by Pulse system
# Table 'marketplace_items' can be safely dropped in future migration


class ChatRoom(db.Model):
    """Chat rooms between users (1-on-1 direct messaging)."""
    __tablename__ = "chat_rooms"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    room_type = db.Column(db.String(20), nullable=False, default='direct')  # direct, group (future)
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    last_message_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    members = db.relationship("ChatMember", backref="room", lazy=True, cascade="all,delete")
    messages = db.relationship("ChatMessage", backref="room", lazy=True, cascade="all,delete")

    def __repr__(self) -> str:
        return f"<ChatRoom {self.id} {self.room_type}>"


class ChatMember(db.Model):
    """Users who are members of a chat room."""
    __tablename__ = "chat_members"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    room_id = db.Column(db.String(36), db.ForeignKey("chat_rooms.id", ondelete='CASCADE'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False)

    # Track read status
    last_read_at = db.Column(db.DateTime(timezone=True), nullable=True)
    unread_count = db.Column(db.Integer, nullable=False, default=0)
    joined_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)

    # Relationships
    user = db.relationship("User", foreign_keys=[user_id], backref="chat_memberships")

    # Constraints
    __table_args__ = (
        db.UniqueConstraint('room_id', 'user_id', name='unique_room_member'),
        db.Index('idx_chat_member_user', 'user_id'),
        db.Index('idx_chat_member_room', 'room_id'),
    )

    def __repr__(self) -> str:
        return f"<ChatMember room={self.room_id} user={self.user_id}>"


class ChatMessage(db.Model):
    """Individual chat messages within a room."""
    __tablename__ = "chat_messages"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    room_id = db.Column(db.String(36), db.ForeignKey("chat_rooms.id", ondelete='CASCADE'), nullable=False)
    sender_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False)

    message = db.Column(db.Text, nullable=False)
    message_type = db.Column(db.String(20), nullable=False, default='text')  # text, system, share_link

    sent_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    edited_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    sender = db.relationship("User", foreign_keys=[sender_id])

    # Indexes
    __table_args__ = (
        db.Index('idx_message_room_time', 'room_id', 'sent_at'),
    )

    def __repr__(self) -> str:
        return f"<ChatMessage {self.id} room={self.room_id} from={self.sender_id}>"


class Notification(db.Model):
    """User notifications for social interactions."""
    __tablename__ = "notifications"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False)

    notification_type = db.Column(db.String(50), nullable=False)
    # Types: friend_request, friend_accepted, share_received, chat_message, marketplace_import

    title = db.Column(db.String(200), nullable=False)
    message = db.Column(db.Text, nullable=True)
    link = db.Column(db.String(500), nullable=True)  # Where to go when clicked

    # Related entities
    related_user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=True)
    related_object_type = db.Column(db.String(50), nullable=True)
    related_object_id = db.Column(db.Integer, nullable=True)

    # Status
    is_read = db.Column(db.Boolean, nullable=False, default=False)
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    read_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    user = db.relationship("User", foreign_keys=[user_id], backref="notifications")
    related_user = db.relationship("User", foreign_keys=[related_user_id])

    # Indexes
    __table_args__ = (
        db.Index('idx_notification_user_read', 'user_id', 'is_read', 'created_at'),
    )

    def __repr__(self) -> str:
        return f"<Notification {self.id} type={self.notification_type} user={self.user_id}>"


# ===== OAUTH CALENDAR INTEGRATION =====

class OAuthCalendarConnection(db.Model, TimestampMixin):
    """OAuth provider connections for calendar synchronization (Google, Microsoft, Apple)."""
    __tablename__ = "oauth_calendar_connections"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id", ondelete='CASCADE'), nullable=False, index=True)

    # Provider information
    provider = db.Column(db.String(20), nullable=False)  # 'google', 'microsoft', 'apple'
    provider_account_id = db.Column(db.String(255), nullable=False)  # External account ID
    provider_email = db.Column(db.String(255), nullable=True)  # Email used for OAuth

    # OAuth tokens (encrypted at rest for security)
    access_token = db.Column(EncryptedField, nullable=False)
    refresh_token = db.Column(EncryptedField, nullable=True)
    token_expires_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Calendar list from provider (cached) - auto-serializes to/from JSON
    calendar_list = db.Column(JSONFieldWithDefault(default_factory=list))

    # Sync configuration
    sync_enabled = db.Column(db.Boolean, nullable=False, default=True)
    last_sync_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    user = db.relationship("User", foreign_keys=[user_id], backref="oauth_calendar_connections")
    mappings = db.relationship("OAuthCalendarMapping", backref="connection", lazy=True, cascade="all,delete")

    # Constraints
    __table_args__ = (
        db.UniqueConstraint('user_id', 'provider', 'provider_account_id', name='unique_oauth_connection'),
        db.Index('idx_oauth_conn_user', 'user_id'),
        db.Index('idx_oauth_conn_provider', 'provider'),
    )

    def is_token_expired(self) -> bool:
        """Check if access token is expired."""
        if not self.token_expires_at:
            return False
        return self.token_expires_at < utc_now()

    def __repr__(self) -> str:
        return f"<OAuthCalendarConnection {self.id} user={self.user_id} provider={self.provider}>"


class OAuthCalendarMapping(db.Model, TimestampMixin):
    """Maps external OAuth calendar to our internal system."""
    __tablename__ = "oauth_calendar_mappings"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    connection_id = db.Column(db.String(36), db.ForeignKey("oauth_calendar_connections.id"), nullable=False)

    # External calendar info
    provider_calendar_id = db.Column(db.String(255), nullable=False)  # Calendar ID from provider
    provider_calendar_name = db.Column(db.String(255), nullable=True)  # Calendar name from provider

    # Optional mapping to our group (for syncing to a specific group)
    our_group_id = db.Column(db.String(36), db.ForeignKey("groups.id"), nullable=True)

    # Sync configuration
    sync_direction = db.Column(db.String(20), nullable=False, default='read_only')  # read_only, write_only, bidirectional
    sync_enabled = db.Column(db.Boolean, nullable=False, default=True)

    # Visual
    color = db.Column(db.String(7), nullable=True)  # Calendar color from provider

    # Relationships
    our_group = db.relationship("Group", foreign_keys=[our_group_id])
    synced_events = db.relationship("OAuthEventSync", backref="mapping", lazy=True, cascade="all,delete")

    # Constraints
    __table_args__ = (
        db.UniqueConstraint('connection_id', 'provider_calendar_id', name='unique_calendar_mapping'),
        db.Index('idx_oauth_mapping_connection', 'connection_id'),
        db.Index('idx_oauth_mapping_our_group', 'our_group_id'),
    )

    def __repr__(self) -> str:
        return f"<OAuthCalendarMapping {self.id} provider_cal={self.provider_calendar_id}>"


class OAuthEventSync(db.Model, TimestampMixin):
    """Tracks synced events between OAuth providers and our system."""
    __tablename__ = "oauth_event_syncs"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    mapping_id = db.Column(db.String(36), db.ForeignKey("oauth_calendar_mappings.id"), nullable=False)

    # External event tracking
    provider_event_id = db.Column(db.String(255), nullable=False)  # Event ID from provider

    # Our internal event
    our_event_id = db.Column(db.String(36), db.ForeignKey("events.id", ondelete='CASCADE'), nullable=True)

    # Sync status
    last_synced_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    sync_hash = db.Column(db.String(64), nullable=True)  # Hash of event data to detect changes

    # Relationships
    our_event = db.relationship("Event", foreign_keys=[our_event_id])

    # Constraints
    __table_args__ = (
        db.UniqueConstraint('mapping_id', 'provider_event_id', name='unique_event_sync'),
        db.Index('idx_oauth_sync_mapping', 'mapping_id'),
        db.Index('idx_oauth_sync_event', 'our_event_id'),
        db.Index('idx_oauth_sync_provider_event', 'provider_event_id'),
    )

    def __repr__(self) -> str:
        return f"<OAuthEventSync {self.id} provider_event={self.provider_event_id} event={self.our_event_id}>"


# ===== PULSE SYSTEM (CONTENT SUBSCRIPTIONS) =====

class Pulse(db.Model, TimestampMixin):
    """Curated collection of templates that users can subscribe to."""
    __tablename__ = "pulses"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    creator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    # POLYMORPHIC OWNERSHIP (Roadmap #8 Phase 1) - Pulses can be owned by users OR groups
    owner_type = db.Column(db.String(20), nullable=False, default='user')  # 'user' or 'group'
    owner_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=True)
    owner_group_id = db.Column(db.String(36), db.ForeignKey('groups.id', ondelete='CASCADE'), nullable=True)
    created_by_user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)  # Track creator for group-owned Pulses

    # System Pulse flag (auto-generated reference data like US holidays)
    # System Pulses are owned by an admin user but marked with is_system=True
    is_system = db.Column(db.Boolean, nullable=False, default=False)
    system_key = db.Column(db.String(100), nullable=True, unique=True, index=True)  # Unique identifier for system Pulses (e.g., 'us_holidays_2025')

    # Source type (Roadmap #15) - how this Pulse was created
    # 'user' = manually created by user, 'ical' = created from iCal import, 'system' = system-provided
    source_type = db.Column(db.String(20), nullable=False, default='user')

    # Pulse details
    title = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text, nullable=True)
    category = db.Column(db.String(50), nullable=True)  # fitness, productivity, sports, holidays

    # REMOVED (Roadmap #12): completion_mode - was for checklist completion modes

    # Privacy control (who can subscribe)
    # Values: 'invite_only' (not searchable, join by invite), 'public' (searchable, request to join)
    visibility = db.Column(db.String(20), nullable=False, default='invite_only')

    # Publishing status
    is_published = db.Column(db.Boolean, nullable=False, default=False)
    published_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Stats
    subscriber_count = db.Column(db.Integer, nullable=False, default=0)
    view_count = db.Column(db.Integer, nullable=False, default=0)

    # Social engagement (counters only, UI in Roadmap #4)
    like_count = db.Column(db.Integer, nullable=False, default=0)
    comment_count = db.Column(db.Integer, nullable=False, default=0)

    # Content metadata
    cover_image_hash = db.Column(db.String(64), nullable=True)  # SHA256 hash for content-addressed storage (Phase 2.2)
    cover_image_alt_text = db.Column(db.String(200), nullable=True)  # Accessibility (Phase 2.2)
    external_link = db.Column(db.String(500), nullable=True)  # Link to external site (e.g., show website, Google search)

    # Versioning for updates
    version = db.Column(db.Integer, nullable=False, default=1)
    last_updated_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)

    # Future monetization
    is_premium = db.Column(db.Boolean, nullable=False, default=False)
    price = db.Column(db.Numeric(10, 2), nullable=True)  # $4.99, etc.

    # DEPRECATED: Replaced by PulseSubscription.role='collaborator' (will be dropped in migration)
    # Collaborative editing now uses role-based permissions via PulseSubscription
    collaborator_ids = db.Column(JSONField, nullable=True)

    # Future: Ownership transfer
    transfer_token = db.Column(db.String(32), nullable=True, unique=True)  # One-time transfer token

    # Trending score (pre-computed hourly for performance)
    # Formula: (likes + 2*subscribers + 0.5*comments + 0.1*net_votes) / (hours + 2)^1.5
    trending_score = db.Column(db.Float, nullable=False, default=0.0, index=True)
    trending_score_updated_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    creator = db.relationship('User', backref='created_pulses', foreign_keys=[creator_id])
    owner_user = db.relationship('User', foreign_keys=[owner_user_id], backref='owned_pulses')
    owner_group = db.relationship('Group', foreign_keys=[owner_group_id], backref='group_owned_pulses')
    items = db.relationship('PulseItem', backref='pulse', cascade='all,delete')
    subscriptions = db.relationship('PulseSubscription', backref='pulse', cascade='all,delete')

    # Performance indexes
    __table_args__ = (
        db.Index('idx_pulse_creator', 'creator_id'),
        db.Index('idx_pulse_category', 'category'),
        db.Index('idx_pulse_visibility', 'visibility'),
        db.Index('idx_pulse_published', 'is_published'),
        db.Index('idx_pulse_owner_user', 'owner_user_id'),
        db.Index('idx_pulse_owner_group', 'owner_group_id'),
        # Constraint: exactly ONE owner (user OR group)
        db.CheckConstraint(
            '(owner_user_id IS NOT NULL AND owner_group_id IS NULL) OR (owner_user_id IS NULL AND owner_group_id IS NOT NULL)',
            name='check_pulse_single_owner'
        ),
    )

    def __repr__(self) -> str:
        owner_str = f"group={self.owner_group_id}" if self.owner_type == 'group' else f"user={self.owner_user_id}"
        return f"<Pulse {self.id} {self.title} owned_by={owner_str}>"

    @property
    def owner(self):
        """Get the actual owner (User or Group instance)."""
        if self.owner_type == 'user':
            from models import User
            return User.query.get(self.owner_user_id)
        else:
            return Group.query.get(self.owner_group_id)

    def can_user_edit(self, user_id: int) -> bool:
        """
        Check if user can edit this Pulse.

        For user-owned Pulses: user must be the owner
        For group-owned Pulses: user must have manage_content permission in group

        UPDATED Roadmap #9 Phase 1: Now recognizes co-owner role.
        """
        if self.owner_type == 'user':
            return self.owner_user_id == user_id
        else:
            # Check group membership and permissions (5-tier system)
            membership = GroupMembership.query.filter_by(
                user_id=user_id,
                group_id=self.owner_group_id
            ).first()
            return membership and (
                membership.role in ['owner', 'co-owner', 'admin'] or
                membership.permissions.get('manage_content', False)
            )

    @property
    def cover_image_url_resolved(self) -> Optional[str]:
        """
        Get cover image URL from content-addressed storage.

        Returns URL from ImageService if cover_image_hash is set.

        Phase 2.2: Cloud-ready image storage
        """
        if self.cover_image_hash:
            from utils.image_service import ImageService
            return ImageService.get_url(self.cover_image_hash, size='cover')
        return None

    @property
    def cover_thumbnail_url(self) -> Optional[str]:
        """Get thumbnail URL for discovery page grid (200x200)."""
        if self.cover_image_hash:
            from utils.image_service import ImageService
            return ImageService.get_url(self.cover_image_hash, size='thumbnail')
        return None

    def create_version_snapshot(self, user_id: int, change_summary: str = None) -> 'PulseVersion':
        """
        Create a version snapshot of current Pulse state (Phase 3.1).

        Args:
            user_id: ID of user creating the snapshot
            change_summary: Optional human-readable description of changes

        Returns:
            PulseVersion object (not yet committed)
        """
        # Serialize Pulse items to JSON (Events-Only, Roadmap #12)
        items_data = []
        for item in sorted(self.items, key=lambda x: x.order_index):
            item_data = {'order_index': item.order_index}

            if item.event:
                item_data.update({
                    'type': 'event',
                    'id': item.event.id,
                    'title': item.event.title,
                    'category': item.event.category,
                    'start_date': item.event.start_date.isoformat() if item.event.start_date else None,
                    'start_time': item.event.start_time.isoformat() if item.event.start_time else None,
                    'end_time': item.event.end_time.isoformat() if item.event.end_time else None
                })
            elif item.recurring_event:
                item_data.update({
                    'type': 'recurring_event',
                    'id': item.recurring_event.id,
                    'title': item.recurring_event.title,
                    'category': item.recurring_event.category,
                    'rrule': item.recurring_event.rrule,
                    'start_time': item.recurring_event.start_time.isoformat() if item.recurring_event.start_time else None,
                    'end_time': item.recurring_event.end_time.isoformat() if item.recurring_event.end_time else None
                })

            items_data.append(item_data)

        # Create snapshot
        snapshot = {
            'title': self.title,
            'description': self.description,
            'visibility': self.visibility,
            'category': self.category,
            'external_link': self.external_link,
            'cover_image_hash': self.cover_image_hash,
            'cover_image_alt_text': self.cover_image_alt_text,
            'items': items_data
        }

        # Increment version
        self.version += 1
        self.last_updated_at = utc_now()

        from models import PulseVersion
        version = PulseVersion(
            pulse_id=self.id,
            created_by_user_id=user_id,
            version_number=self.version,
            change_summary=change_summary,
            snapshot_data=snapshot
        )

        return version

    def restore_from_snapshot(self, version_id: int) -> dict:
        """
        Restore Pulse to a previous version (Phase 3.1).

        Args:
            version_id: ID of PulseVersion to restore from

        Returns:
            dict with keys:
                - success: bool
                - message: str
                - changes: dict (keys: name, description, items_added, items_removed)
        """
        from models import PulseVersion
        version = PulseVersion.query.filter_by(pulse_id=self.id, id=version_id).first()
        if not version:
            return {'success': False, 'message': 'Version not found'}

        snapshot = version.snapshot_data
        changes = {}

        # Restore metadata
        if snapshot.get('title') != self.title:
            changes['title'] = {'old': self.title, 'new': snapshot.get('title')}
            self.title = snapshot.get('title')
        if snapshot.get('description') != self.description:
            changes['description'] = {'old': self.description, 'new': snapshot.get('description')}
            self.description = snapshot.get('description')
        if snapshot.get('visibility') != self.visibility:
            changes['visibility'] = {'old': self.visibility, 'new': snapshot.get('visibility')}
            self.visibility = snapshot.get('visibility')
        if snapshot.get('category') != self.category:
            changes['category'] = {'old': self.category, 'new': snapshot.get('category')}
            self.category = snapshot.get('category')
        if snapshot.get('external_link') != self.external_link:
            changes['external_link'] = {'old': self.external_link, 'new': snapshot.get('external_link')}
            self.external_link = snapshot.get('external_link')

        # Note: Item restoration is complex (requires matching/creating entries)
        # For now, we only restore metadata. Full item restoration could be added later.
        changes['items_note'] = 'Item restoration not yet implemented - only metadata restored'

        return {
            'success': True,
            'message': f'Restored Pulse to version {version.version_number}',
            'changes': changes
        }


class PulseItem(db.Model):
    """Links events to a Pulse collection (Events-Only, Roadmap #12)."""
    __tablename__ = "pulse_items"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)

    # What type of event is in this Pulse
    # Values: 'recurring_event', 'event' (Roadmap #12: checklist types removed)
    item_type = db.Column(db.String(20), nullable=False)
    # REMOVED (Roadmap #12): checklist_id, recurring_checklist_id - Events-Only Pivot
    recurring_event_id = db.Column(db.String(36), db.ForeignKey('recurring_events.id', ondelete='CASCADE'), nullable=True)
    event_id = db.Column(db.String(36), db.ForeignKey('events.id', ondelete='CASCADE'), nullable=True)

    # Display order in Pulse
    order_index = db.Column(db.Integer, nullable=False, default=0)

    # Relationships
    recurring_event = db.relationship('RecurringEvent', foreign_keys=[recurring_event_id], backref='pulse_items')
    event = db.relationship('Event', foreign_keys=[event_id], backref='pulse_items')

    # Check constraint: Exactly one of the event IDs is set (Roadmap #12: simplified to events only)
    __table_args__ = (
        db.CheckConstraint(
            '(recurring_event_id IS NOT NULL AND event_id IS NULL) OR '
            '(recurring_event_id IS NULL AND event_id IS NOT NULL)',
            name='check_pulse_item_type'
        ),
        # Single-Pulse Simplification (Phase 2, Roadmap #10)
        # Each entry can only belong to ONE Pulse (enforced by unique constraints)
        db.UniqueConstraint('event_id', name='uq_pulse_event'),
        db.UniqueConstraint('recurring_event_id', name='uq_pulse_recurring_event'),
        db.Index('idx_pulse_item_pulse', 'pulse_id'),
        db.Index('idx_pulse_items_entry_lookup', 'event_id', 'recurring_event_id'),
    )

    def __repr__(self) -> str:
        return f"<PulseItem {self.id} pulse={self.pulse_id} type={self.item_type}>"


class PulseSubscription(db.Model, TimestampMixin):
    """Tracks user subscriptions to Pulses."""
    __tablename__ = "pulse_subscriptions"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)

    # Role-based permissions (Three-tier model)
    role = db.Column(
        db.String(20),
        nullable=False,
        default='participant'
    )
    # Values: 'creator', 'collaborator', 'participant'

    # Membership status (for join request workflow)
    status = db.Column(
        db.String(20),
        nullable=False,
        default='active'
    )
    # Values: 'active', 'pending', 'denied'

    # Invitation tracking
    invited_by_user_id = db.Column(
        db.Integer,
        db.ForeignKey('users.id'),
        nullable=True
    )
    # Who invited/approved this member (NULL if creator or self-requested)

    # Join request timestamps
    requested_at = db.Column(db.DateTime(timezone=True), nullable=True)
    # When user requested to join (if status='pending')

    approved_at = db.Column(db.DateTime(timezone=True), nullable=True)
    # When request was approved (if status='active' and was pending)

    # Subscription status
    subscribed_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    is_active = db.Column(db.Boolean, nullable=False, default=True)

    # Subscription type (reference vs copy architecture)
    subscription_type = db.Column(
        db.String(20),
        nullable=False,
        default='reference'  # Default for new subscriptions
    )
    # Values: 'reference' (Controlled Pulses) or 'copy' (Isolated Pulses)

    # Update behavior
    auto_update = db.Column(db.Boolean, nullable=False, default=False)  # Opt-in to creator updates
    last_synced_version = db.Column(db.Integer, nullable=False, default=1)

    # Customization tracking (fork-on-edit)
    is_customized = db.Column(db.Boolean, nullable=False, default=False)

    # Relationships
    user = db.relationship('User', backref='pulse_subscriptions', foreign_keys=[user_id])
    invited_by = db.relationship(
        'User',
        foreign_keys=[invited_by_user_id],
        backref='pulse_invitations_sent'
    )

    # Constraints
    __table_args__ = (
        db.UniqueConstraint('user_id', 'pulse_id', name='unique_pulse_subscription'),
        db.Index('idx_subscription_user', 'user_id'),
        db.Index('idx_subscription_pulse', 'pulse_id'),
        db.Index('idx_subscription_role', 'pulse_id', 'role'),
        db.Index('idx_subscription_status', 'pulse_id', 'status'),
        db.CheckConstraint(
            "role IN ('creator', 'collaborator', 'participant')",
            name='check_subscription_role'
        ),
        db.CheckConstraint(
            "status IN ('active', 'pending', 'denied')",
            name='check_subscription_status'
        ),
    )

    def __repr__(self) -> str:
        return f"<PulseSubscription user={self.user_id} pulse={self.pulse_id} active={self.is_active}>"


class PulseLike(db.Model):
    """User likes on Pulses."""
    __tablename__ = "pulse_likes"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)

    # Relationships
    user = db.relationship('User', backref='pulse_likes', foreign_keys=[user_id])

    __table_args__ = (
        db.UniqueConstraint('user_id', 'pulse_id', name='unique_pulse_like'),
        db.Index('idx_pulse_like_user', 'user_id'),
        db.Index('idx_pulse_like_pulse', 'pulse_id'),
    )

    def __repr__(self) -> str:
        return f"<PulseLike user={self.user_id} pulse={self.pulse_id}>"


class PulseComment(db.Model):
    """User comments on Pulses with threading and voting support."""
    __tablename__ = "pulse_comments"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)
    parent_id = db.Column(db.String(36), db.ForeignKey('pulse_comments.id', ondelete='CASCADE'), nullable=True)
    comment_text = db.Column(db.Text, nullable=False)
    vote_score = db.Column(db.Integer, nullable=False, default=1)  # Start at 1 (author's implicit upvote)
    is_deleted = db.Column(db.Boolean, nullable=False, default=False)  # Soft delete for threaded comments
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    edited_at = db.Column(db.DateTime(timezone=True), nullable=True)
    edit_history = db.Column(db.JSON, nullable=True)  # List of {text, edited_at} for admin view

    # Relationships
    user = db.relationship('User', backref='pulse_comments', foreign_keys=[user_id])
    parent = db.relationship('PulseComment', remote_side=[id], backref='replies', foreign_keys=[parent_id])

    __table_args__ = (
        db.Index('idx_pulse_comment_pulse', 'pulse_id', 'created_at'),
        db.Index('idx_pulse_comment_user', 'user_id'),
        db.Index('idx_pulse_comment_parent', 'parent_id'),
    )

    @property
    def has_visible_content(self) -> bool:
        """
        Check if this comment or any descendant is visible (not deleted).

        Used to hide fully-deleted comment chains from the UI.
        Returns True if this comment should be shown.
        """
        # Fallback to True if is_deleted doesn't exist (pre-migration comments)
        if not getattr(self, 'is_deleted', False):
            return True
        # Deleted - check if any reply has visible content
        replies = getattr(self, 'replies', []) or []
        return any(reply.has_visible_content for reply in replies)

    def __repr__(self) -> str:
        return f"<PulseComment {self.id} user={self.user_id} pulse={self.pulse_id}>"


class PulseShare(db.Model):
    """
    Tracks Pulse shares between users via chat.

    Used for:
    - Authorization: Validates share-based access to invite-only Pulses
    - Analytics: Track share conversion rates
    - Deduplication: Prevent duplicate shares to same user
    """
    __tablename__ = "pulse_shares"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)
    sender_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    recipient_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)

    # Optional personal note from sender
    note = db.Column(db.Text, nullable=True)

    # Reference to the chat message containing this share
    chat_message_id = db.Column(db.String(36), db.ForeignKey('chat_messages.id', ondelete='SET NULL'), nullable=True)

    # Status tracking
    status = db.Column(db.String(20), nullable=False, default='sent')
    # Values: 'sent', 'viewed', 'subscribed', 'declined'

    # Timestamps
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    viewed_at = db.Column(db.DateTime(timezone=True), nullable=True)
    responded_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Optional expiration for time-limited invitations
    expires_at = db.Column(db.DateTime(timezone=True), nullable=True)

    # Relationships
    pulse = db.relationship('Pulse', backref=db.backref('shares', passive_deletes=True))
    sender = db.relationship('User', foreign_keys=[sender_id], backref='pulse_shares_sent')
    recipient = db.relationship('User', foreign_keys=[recipient_id], backref='pulse_shares_received')
    chat_message = db.relationship('ChatMessage', foreign_keys=[chat_message_id])

    # Performance indexes and constraints
    __table_args__ = (
        db.Index('idx_pulse_share_recipient', 'recipient_id', 'status'),
        db.Index('idx_pulse_share_pulse', 'pulse_id'),
        db.Index('idx_pulse_share_sender', 'sender_id'),
        # Prevent duplicate shares (same sender, recipient, pulse)
        db.UniqueConstraint('pulse_id', 'sender_id', 'recipient_id', name='uq_pulse_share'),
        db.CheckConstraint(
            "status IN ('sent', 'viewed', 'subscribed', 'declined')",
            name='check_pulse_share_status'
        ),
    )

    def is_valid(self) -> bool:
        """Check if share is still valid for subscription."""
        if self.status in ['subscribed', 'declined']:
            return False
        if self.expires_at and self.expires_at < utc_now():
            return False
        return True

    def __repr__(self) -> str:
        return f"<PulseShare {self.id} pulse={self.pulse_id} {self.sender_id}->{self.recipient_id} status={self.status}>"


class PulseVersion(db.Model):
    """Version history snapshots for Pulses (Phase 3.1)."""
    __tablename__ = "pulse_versions"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)
    created_by_user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    # Version metadata
    version_number = db.Column(db.Integer, nullable=False)  # Incremental version (1, 2, 3...)
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    change_summary = db.Column(db.Text, nullable=True)  # Human-readable summary of changes

    # Snapshot data (full Pulse state at this version)
    snapshot_data = db.Column(JSONField, nullable=False)  # JSON blob with complete Pulse state
    # Format (Events-Only, Roadmap #12): {
    #   "name": "Pulse name",
    #   "description": "Pulse description",
    #   "visibility": "public",
    #   "category": "sports",
    #   "items": [
    #     {"type": "event", "id": 123, "title": "...", "start_date": "..."},
    #     {"type": "recurring_event", "id": 456, "title": "...", "rrule": "..."},
    #     ...
    #   ]
    # }

    # Relationships
    pulse = db.relationship('Pulse', backref=db.backref('versions', passive_deletes=True))
    created_by = db.relationship('User', foreign_keys=[created_by_user_id])

    __table_args__ = (
        db.Index('idx_pulse_version_pulse', 'pulse_id', 'version_number'),
        db.Index('idx_pulse_version_created', 'created_at'),
    )

    def __repr__(self) -> str:
        return f"<PulseVersion {self.id} pulse={self.pulse_id} v{self.version_number}>"


class PulseOwnershipTransfer(db.Model):
  """
  Audit trail for Pulse ownership transfers (Roadmap #9 Phase 3, simplified in #13).

  Tracks transfers between users and groups.
  """
  __tablename__ = "pulse_ownership_transfers"

  id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
  pulse_id = db.Column(db.String(36), db.ForeignKey('pulses.id', ondelete='CASCADE'), nullable=False)

  # FROM ownership
  from_owner_type = db.Column(db.String(20), nullable=False)  # 'user' or 'group'
  from_owner_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
  from_owner_group_id = db.Column(db.String(36), db.ForeignKey('groups.id', ondelete='SET NULL'), nullable=True)

  # TO ownership
  to_owner_type = db.Column(db.String(20), nullable=False)  # 'user' or 'group'
  to_owner_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
  to_owner_group_id = db.Column(db.String(36), db.ForeignKey('groups.id', ondelete='SET NULL'), nullable=True)

  # Transfer metadata
  transferred_by_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=False)
  transferred_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
  reason = db.Column(db.Text, nullable=True)  # Optional explanation for transfer

  # Relationships
  pulse = db.relationship('Pulse', backref=db.backref('ownership_transfers', passive_deletes=True))
  transferred_by = db.relationship('User', foreign_keys=[transferred_by_user_id])
  from_user = db.relationship('User', foreign_keys=[from_owner_user_id])
  from_group = db.relationship('Group', foreign_keys=[from_owner_group_id])
  to_user = db.relationship('User', foreign_keys=[to_owner_user_id])
  to_group = db.relationship('Group', foreign_keys=[to_owner_group_id])

  __table_args__ = (
    # FROM ownership: Exactly one of user_id or group_id must be set
    db.CheckConstraint(
      "(from_owner_type = 'user' AND from_owner_user_id IS NOT NULL AND from_owner_group_id IS NULL) OR "
      "(from_owner_type = 'group' AND from_owner_user_id IS NULL AND from_owner_group_id IS NOT NULL)",
      name='check_from_owner'
    ),
    # TO ownership: Exactly one of user_id or group_id must be set
    db.CheckConstraint(
      "(to_owner_type = 'user' AND to_owner_user_id IS NOT NULL AND to_owner_group_id IS NULL) OR "
      "(to_owner_type = 'group' AND to_owner_user_id IS NULL AND to_owner_group_id IS NOT NULL)",
      name='check_to_owner'
    ),
    db.Index('idx_ownership_transfer_pulse', 'pulse_id', 'transferred_at'),
    db.Index('idx_ownership_transfer_from', 'from_owner_type', 'from_owner_user_id', 'from_owner_group_id'),
    db.Index('idx_ownership_transfer_to', 'to_owner_type', 'to_owner_user_id', 'to_owner_group_id'),
  )

  def __repr__(self) -> str:
    return f"<PulseOwnershipTransfer {self.id} pulse={self.pulse_id} from={self.from_owner_type} to={self.to_owner_type}>"


class UserActivity(db.Model):
    """Track user activity for admin monitoring and analytics."""
    __tablename__ = "user_activity"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    action_type = db.Column(db.String(50), nullable=False)  # login, create_event, edit_event, delete_pulse, etc.
    description = db.Column(db.Text, nullable=False)  # Human-readable description

    # Optional metadata
    ip_address = db.Column(db.String(45), nullable=True)  # IPv4 (15 chars) or IPv6 (45 chars)
    user_agent = db.Column(db.Text, nullable=True)

    # Related entities (nullable - not all actions have associated entities)
    entity_type = db.Column(db.String(50), nullable=True)  # event, recurring_event, pulse, group, etc.
    entity_id = db.Column(db.String(36), nullable=True)  # String to support both int IDs and UUIDs

    # Timestamps
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now, index=True)

    # Relationships
    user = db.relationship('User', backref='activities', foreign_keys=[user_id])

    __table_args__ = (
        db.Index('idx_activity_user_created', 'user_id', 'created_at'),
        db.Index('idx_activity_action', 'action_type'),
        db.Index('idx_activity_entity', 'entity_type', 'entity_id'),
    )

    def __repr__(self) -> str:
        return f"<UserActivity {self.id} user={self.user_id} action={self.action_type}>"


# ===== GROUPS SYSTEM (Roadmap #8) =====
# Unified collaboration model that absorbs SharedCalendar functionality


class Group(db.Model, TimestampMixin):
    """
    Flat community model (Roadmap #8 Phase 1, simplified in Roadmap #13).

    Groups are simple communities where:
    - Members join to discover and subscribe to Pulses
    - Groups own Pulses directly (no subgroups)
    - Pulses are OFFERED to members (not forced)

    Collaboration modes:
    - shared: Real-time editing (replaces SharedCalendar)
    - distributed: Template distribution (like Pulse subscriptions)
    - hybrid: Both modes available
    """
    __tablename__ = "groups"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    name = db.Column(db.String(100), nullable=False)
    slug = db.Column(db.String(50), unique=True, nullable=False, index=True)
    description = db.Column(db.Text, nullable=True)
    type = db.Column(db.String(20), nullable=False, default='social')  # social/professional/community/family

    # Privacy control
    visibility = db.Column(db.String(20), nullable=False, default='private')  # public/private/invite_only

    # COLLABORATION MODE (Replaces SharedCalendar)
    collaboration_mode = db.Column(db.String(20), nullable=False, default='shared')
    # 'shared': Real-time shared editing (replaces SharedCalendar)
    #           Members edit the SAME entries, changes visible to all immediately
    # 'distributed': Template distribution (like Pulse subscriptions)
    #                Members get COPIES they can customize
    # 'hybrid': Both modes available (group can share calendars AND distribute templates)

    # Visual identity
    avatar_url = db.Column(db.String(255), nullable=True)
    color_theme = db.Column(db.String(7), nullable=False, default='#6c757d')  # Hex color
    emoji_icon = db.Column(db.String(10), nullable=True)  # Optional emoji

    # Group settings
    features = db.Column(JSONField, nullable=True, default={'pulses': True})
    settings = db.Column(JSONField, nullable=True, default={})

    # Ownership (who created this group)
    creator_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    # Stats
    member_count = db.Column(db.Integer, nullable=False, default=0)
    active_pulse_count = db.Column(db.Integer, nullable=False, default=0)

    # Relationships
    memberships = db.relationship('GroupMembership', back_populates='group', cascade='all,delete-orphan')
    creator = db.relationship('User', foreign_keys=[creator_user_id])

    # Performance indexes
    __table_args__ = (
        db.Index('idx_group_creator', 'creator_user_id'),
        db.Index('idx_group_type', 'type'),
        db.Index('idx_group_visibility', 'visibility'),
        db.Index('idx_group_slug', 'slug'),
    )

    def __repr__(self) -> str:
        return f"<Group {self.id} {self.name} type={self.type} mode={self.collaboration_mode}>"


class GroupMembership(db.Model, TimestampMixin):
    """
    User membership in a group with roles and preferences (Roadmap #8, simplified Roadmap #13).

    4-Tier Permission System (Roadmap #13):
    - owner: Root admin with absolute control (delete group, transfer ownership)
    - co-owner: Trusted leadership (all admin powers + critical settings, can't delete group)
    - admin: Content & member management (create/edit Pulses, manage members)
    - member: Basic participation (view content, subscribe to Pulses)
    """
    __tablename__ = "group_memberships"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    group_id = db.Column(db.String(36), db.ForeignKey('groups.id', ondelete='CASCADE'), nullable=False)

    # 4-TIER ROLE SYSTEM (simplified in Roadmap #13)
    role = db.Column(db.String(20), nullable=False, default='member')  # owner/co-owner/admin/member

    # Granular permissions (for flexibility)
    permissions = db.Column(JSONField, nullable=True, default={})
    # Example permissions:
    # {
    #     'manage_members': False,      # Invite/remove members
    #     'manage_content': False,      # Edit group-owned content
    #     'create_pulses': False,       # Create group-owned Pulses
    #     'moderate_content': False,    # Hide/pin content
    # }

    # User preferences for this group
    priority_weight = db.Column(db.Integer, nullable=False, default=5)  # 1-10
    notification_level = db.Column(db.String(20), nullable=False, default='important')  # all/important/muted
    show_in_homepage = db.Column(db.Boolean, nullable=False, default=True)

    # Join metadata
    joined_at = db.Column(db.DateTime(timezone=True), nullable=False, default=utc_now)
    invited_by_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    join_method = db.Column(db.String(20), nullable=True)  # invited/requested/public_join

    # Relationships
    user = db.relationship('User', foreign_keys=[user_id], backref='group_memberships')
    group = db.relationship('Group', back_populates='memberships')
    invited_by = db.relationship('User', foreign_keys=[invited_by_user_id])

    # Performance indexes
    __table_args__ = (
        db.UniqueConstraint('user_id', 'group_id', name='uq_user_group'),
        db.Index('idx_membership_user', 'user_id'),
        db.Index('idx_membership_group', 'group_id'),
        db.Index('idx_membership_role', 'group_id', 'role'),
    )

    def __repr__(self) -> str:
        return f"<GroupMembership user={self.user_id} group={self.group_id} role={self.role}>"


class GroupInvite(db.Model, TimestampMixin):
    """
    Invite codes for joining groups (Roadmap #8 Phase 1).
    """
    __tablename__ = "group_invites"

    id = db.Column(db.String(36), primary_key=True, default=generate_uuid)
    group_id = db.Column(db.String(36), db.ForeignKey('groups.id', ondelete='CASCADE'), nullable=False)

    # Invite details
    code = db.Column(db.String(32), unique=True, nullable=False, index=True)
    email = db.Column(db.String(120), nullable=True)  # Optional: specific email

    # Metadata
    created_by_user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    expires_at = db.Column(db.DateTime(timezone=True), nullable=True)
    max_uses = db.Column(db.Integer, nullable=True)
    uses_count = db.Column(db.Integer, nullable=False, default=0)

    # Relationships
    group = db.relationship('Group', backref=db.backref('invites', cascade='all,delete-orphan'))
    created_by = db.relationship('User', foreign_keys=[created_by_user_id])

    # Performance indexes
    __table_args__ = (
        db.Index('idx_invite_group', 'group_id'),
        db.Index('idx_invite_code', 'code'),
        db.Index('idx_invite_creator', 'created_by_user_id'),
    )

    @staticmethod
    def generate_code(length: int = 16) -> str:
        """Generate a random invite code."""
        return secrets.token_urlsafe(length)[:length].upper()

    def __repr__(self) -> str:
        return f"<GroupInvite {self.code} group={self.group_id}>"