from flask_sqlalchemy import SQLAlchemy
from flask_login import UserMixin
from datetime import datetime
from slugify import slugify
import json
import uuid

db = SQLAlchemy()


# ============================================================================
# HELPER: Available languages list (single source of truth)
# ============================================================================

AVAILABLE_LANGUAGES = [
    # Latin script (alphabetical by native name)
    {'code': 'id', 'name': 'Bahasa Indonesia'},
    {'code': 'cs', 'name': 'Čeština'},
    {'code': 'da', 'name': 'Dansk'},
    {'code': 'de', 'name': 'Deutsch'},
    {'code': 'en', 'name': 'English'},
    {'code': 'es', 'name': 'Español'},
    {'code': 'fr', 'name': 'Français'},
    {'code': 'hr', 'name': 'Hrvatski'},
    {'code': 'it', 'name': 'Italiano'},
    {'code': 'hu', 'name': 'Magyar'},
    {'code': 'nl', 'name': 'Nederlands'},
    {'code': 'nb', 'name': 'Norsk bokmål'},
    {'code': 'pl', 'name': 'Polski'},
    {'code': 'pt', 'name': 'Português'},
    {'code': 'ro', 'name': 'Română'},
    {'code': 'sk', 'name': 'Slovenčina'},
    {'code': 'fi', 'name': 'Suomi'},
    {'code': 'sv', 'name': 'Svenska'},
    {'code': 'vi', 'name': 'Tiếng Việt'},
    {'code': 'tr', 'name': 'Türkçe'},
    # Cyrillic (alphabetical)
    {'code': 'bg', 'name': 'Български'},
    {'code': 'ru', 'name': 'Русский'},
    {'code': 'uk', 'name': 'Українська'},
    # Greek
    {'code': 'el', 'name': 'Ελληνικά'},
    # Other scripts
    {'code': 'hi', 'name': 'हिन्दी'},
    {'code': 'th', 'name': 'ไทย'},
    {'code': 'zh', 'name': '中文'},
    {'code': 'ja', 'name': '日本語'},
    {'code': 'ko', 'name': '한국어'},
    {'code': 'he', 'name': 'עברית'},
    {'code': 'ar', 'name': 'العربية'},
]

LANG_CODE_TO_NAME = {lang['code']: lang['name'] for lang in AVAILABLE_LANGUAGES}
LANG_NAME_TO_CODE = {lang['name']: lang['code'] for lang in AVAILABLE_LANGUAGES}


def generate_translation_group():
    """Generate a unique translation group ID"""
    return str(uuid.uuid4())


class User(UserMixin, db.Model):
    """User model for authentication and author profiles"""
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(200), nullable=False)
    is_admin = db.Column(db.Boolean, default=True)  # Kept for backward compatibility
    
    # Role system: owner (site creator), admin (full access), editor (content only)
    role = db.Column(db.String(20), default='editor')
    
    # Author profile (for public display & SEO / Schema.org)
    display_name = db.Column(db.String(200))  # Shown on posts instead of username
    bio = db.Column(db.Text)  # Author biography
    avatar_url = db.Column(db.String(500))  # Profile picture URL
    
    # AEO — Author entity links (for Schema.org sameAs, improves AI citation)
    linkedin_url = db.Column(db.String(500))
    website_url = db.Column(db.String(500))
    github_url = db.Column(db.String(500))
    
    # Account status
    is_active_account = db.Column(db.Boolean, default=True)  # Can be deactivated without deletion
    invite_token = db.Column(db.String(100), unique=True)  # For email invitations
    invite_expires = db.Column(db.DateTime)  # Invitation expiry
    
    # Password reset
    reset_token = db.Column(db.String(100), unique=True)  # Token sent via email
    reset_token_expires = db.Column(db.DateTime)  # Token expiry (1 hour)
    
    # Admin interface language preference (e.g. 'en', 'de', 'ru')
    admin_language = db.Column(db.String(10), default='en')
    
    # Admin interface theme: 'light', 'dark', 'system'
    admin_theme = db.Column(db.String(10), default='system')
    
    # Session invalidation: incremented on password change to force re-login
    session_version = db.Column(db.Integer, default=0)
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    posts = db.relationship('Post', backref='author', lazy=True, cascade='all, delete-orphan')
    pages = db.relationship('Page', backref='author', lazy=True, cascade='all, delete-orphan')
    images = db.relationship('Image', backref='uploader', lazy=True, cascade='all, delete-orphan')
    
    def get_display_name(self):
        """Return display_name if set, otherwise username"""
        return self.display_name or self.username
    
    def is_owner(self):
        return self.role == 'owner'
    
    def is_admin_or_owner(self):
        return self.role in ('owner', 'admin')
    
    def can_access_settings(self):
        """Only owner and admin can access settings, users, email, tools"""
        return self.role in ('owner', 'admin')
    
    def can_edit_content(self):
        """All roles can edit content"""
        return True
    
    def can_manage_users(self):
        """Only owner and admin can manage users"""
        return self.role in ('owner', 'admin')


class Post(db.Model):
    """Post model with full SEO support and translation_group for multilingual linking"""
    __tablename__ = 'posts'
    
    # Basic fields
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(500), nullable=False)
    slug = db.Column(db.String(500), unique=True, nullable=False)
    content = db.Column(db.Text, nullable=False)
    excerpt = db.Column(db.Text)  # Short summary
    
    # Language & Translations
    language = db.Column(db.String(5), default='en')  # ISO code
    translation_group = db.Column(db.String(36), index=True)  # UUID linking all translations together
    
    # Status
    status = db.Column(db.String(20), default='draft')  # draft, published, scheduled
    
    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    published_at = db.Column(db.DateTime)
    
    # Author
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # SEO Meta Tags
    meta_title = db.Column(db.String(500))
    meta_description = db.Column(db.String(500))
    meta_keywords = db.Column(db.String(500))
    
    # Open Graph (Facebook, LinkedIn)
    og_title = db.Column(db.String(500))
    og_description = db.Column(db.String(500))
    og_image = db.Column(db.String(500))
    og_type = db.Column(db.String(50), default='article')
    
    # Twitter Card
    twitter_card = db.Column(db.String(50), default='summary_large_image')
    twitter_title = db.Column(db.String(500))
    twitter_description = db.Column(db.String(500))
    twitter_image = db.Column(db.String(500))
    
    # Schema.org
    schema_type = db.Column(db.String(50), default='BlogPosting')
    schema_author = db.Column(db.String(200))
    schema_publisher = db.Column(db.String(200))
    
    # Featured Image
    featured_image = db.Column(db.String(500))
    featured_image_alt = db.Column(db.String(500))
    featured_image_caption = db.Column(db.Text)
    
    # Categorization
    canonical_url = db.Column(db.String(500))
    tags = db.Column(db.Text)  # JSON array: ["AI", "CMS", "SEO"]
    category = db.Column(db.String(100))
    
    # AI settings
    ai_generated = db.Column(db.Boolean, default=False)
    ai_prompt = db.Column(db.Text)  # Save prompt for regeneration
    
    # FAQ — JSON array of Q&A pairs for AEO (FAQPage schema)
    # [{"question": "What is X?", "answer": "X is..."}, ...]
    faq_json = db.Column(db.Text)
    
    # Display options (visible to humans on public page; always in meta tags)
    show_author = db.Column(db.Boolean, default=False)
    show_date = db.Column(db.Boolean, default=False)
    
    # Unique constraint: one language per translation group
    __table_args__ = (
        db.UniqueConstraint('translation_group', 'language', name='uq_post_translation_group_language'),
    )
    
    def __init__(self, **kwargs):
        super(Post, self).__init__(**kwargs)
        if not self.slug and self.title:
            self.slug = self.generate_unique_slug(self.title)
        # Auto-generate translation_group if not provided
        if not self.translation_group:
            self.translation_group = generate_translation_group()
    
    def generate_unique_slug(self, title):
        """Generate unique slug from title"""
        base_slug = slugify(title)
        if not base_slug:
            base_slug = 'post'
        slug = base_slug
        counter = 1
        while Post.query.filter_by(slug=slug).first():
            slug = f"{base_slug}-{counter}"
            counter += 1
        return slug
    
    def get_faq_list(self):
        """Get FAQ as list of dicts."""
        if self.faq_json:
            try:
                return json.loads(self.faq_json)
            except (ValueError, TypeError):
                return []
        return []
    
    def set_faq_list(self, faq_list):
        """Set FAQ from list of dicts."""
        self.faq_json = json.dumps(faq_list, ensure_ascii=False)
    
    def get_tags_list(self):
        """Get tags as list"""
        if self.tags:
            try:
                return json.loads(self.tags)
            except (ValueError, TypeError):
                return []
        return []
    
    def set_tags_list(self, tags_list):
        """Set tags from list"""
        self.tags = json.dumps(tags_list, ensure_ascii=False)
    
    def get_translations(self):
        """Get all translations in the same group (excluding self)"""
        if not self.translation_group:
            return []
        return Post.query.filter(
            Post.translation_group == self.translation_group,
            Post.id != self.id
        ).all()
    
    def get_all_language_versions(self):
        """Get ALL language versions including self (for hreflang)"""
        if not self.translation_group:
            return [self]
        return Post.query.filter(
            Post.translation_group == self.translation_group
        ).order_by(Post.language).all()
    
    def get_translation(self, lang_code):
        """Get a specific translation by language code"""
        if self.language == lang_code:
            return self
        if not self.translation_group:
            return None
        return Post.query.filter_by(
            translation_group=self.translation_group,
            language=lang_code
        ).first()
    
    def get_available_languages(self):
        """Get list of languages that already have translations"""
        versions = self.get_all_language_versions()
        return [v.language for v in versions]
    
    def get_missing_languages(self):
        """Get languages that don't have a translation yet"""
        existing = set(self.get_available_languages())
        return [lang for lang in AVAILABLE_LANGUAGES if lang['code'] not in existing]
    
    def get_language_name(self):
        """Get human-readable language name"""
        return LANG_CODE_TO_NAME.get(self.language, self.language)


class Page(db.Model):
    """Pages with full SEO support and translation_group for multilingual linking"""
    __tablename__ = 'pages'
    
    # Basic fields
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(500), nullable=False)
    slug = db.Column(db.String(500), unique=True, nullable=False)
    content = db.Column(db.Text, nullable=False)
    excerpt = db.Column(db.Text)  # Short summary
    template = db.Column(db.String(100), default='default')  # Which template to use
    
    # Page Builder
    blocks = db.Column(db.Text)           # JSON — page builder blocks structure
    use_builder = db.Column(db.Boolean, default=False)  # True = render from blocks
    
    # Language & Translations
    language = db.Column(db.String(5), default='en')  # ISO code
    translation_group = db.Column(db.String(36), index=True)  # UUID linking all translations together
    
    # Status
    status = db.Column(db.String(20), default='published')  # draft, published
    
    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    published_at = db.Column(db.DateTime)
    
    # Author
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # SEO Meta Tags
    meta_title = db.Column(db.String(500))
    meta_description = db.Column(db.String(500))
    meta_keywords = db.Column(db.String(500))
    
    # Open Graph (Facebook, LinkedIn)
    og_title = db.Column(db.String(500))
    og_description = db.Column(db.String(500))
    og_image = db.Column(db.String(500))
    og_type = db.Column(db.String(50), default='website')
    
    # Twitter Card
    twitter_card = db.Column(db.String(50), default='summary_large_image')
    twitter_title = db.Column(db.String(500))
    twitter_description = db.Column(db.String(500))
    twitter_image = db.Column(db.String(500))
    
    # Schema.org
    schema_type = db.Column(db.String(50), default='WebPage')
    schema_author = db.Column(db.String(200))
    schema_publisher = db.Column(db.String(200))
    
    # Featured Image
    featured_image = db.Column(db.String(500))
    featured_image_alt = db.Column(db.String(500))
    featured_image_caption = db.Column(db.Text)
    
    # Categorization
    canonical_url = db.Column(db.String(500))
    tags = db.Column(db.Text)  # JSON array: ["legal", "privacy"]
    category = db.Column(db.String(100))
    
    # AI settings
    ai_generated = db.Column(db.Boolean, default=False)
    ai_prompt = db.Column(db.Text)  # Save prompt for regeneration
    
    # FAQ — JSON array of Q&A pairs for AEO (FAQPage schema)
    # [{"question": "What is X?", "answer": "X is..."}, ...]
    faq_json = db.Column(db.Text)
    
    # Display options (visible to humans on public page; always in meta tags)
    show_author = db.Column(db.Boolean, default=False)
    show_date = db.Column(db.Boolean, default=False)
    
    # Unique constraint: one language per translation group
    __table_args__ = (
        db.UniqueConstraint('translation_group', 'language', name='uq_page_translation_group_language'),
    )
    
    def __init__(self, **kwargs):
        super(Page, self).__init__(**kwargs)
        if not self.slug and self.title:
            self.slug = self.generate_unique_slug(self.title)
        # Auto-generate translation_group if not provided
        if not self.translation_group:
            self.translation_group = generate_translation_group()
    
    def generate_unique_slug(self, title):
        """Generate unique slug from title"""
        base_slug = slugify(title)
        if not base_slug:
            base_slug = 'page'
        slug = base_slug
        counter = 1
        while Page.query.filter_by(slug=slug).first():
            slug = f"{base_slug}-{counter}"
            counter += 1
        return slug
    
    def get_faq_list(self):
        """Get FAQ as list of dicts."""
        if self.faq_json:
            try:
                return json.loads(self.faq_json)
            except (ValueError, TypeError):
                return []
        return []
    
    def set_faq_list(self, faq_list):
        """Set FAQ from list of dicts."""
        self.faq_json = json.dumps(faq_list, ensure_ascii=False)
    
    def get_tags_list(self):
        """Get tags as list"""
        if self.tags:
            try:
                return json.loads(self.tags)
            except (ValueError, TypeError):
                return []
        return []
    
    def set_tags_list(self, tags_list):
        """Set tags from list"""
        self.tags = json.dumps(tags_list, ensure_ascii=False)
    
    def get_all_faq_items(self):
        """Get FAQ items from ALL sources: faq_json + accordion blocks in Page Builder.
        Returns unified list of {"question": ..., "answer": ...} dicts for FAQPage schema.
        """
        import re
        items = list(self.get_faq_list())
        
        # Extract FAQ from accordion blocks in Page Builder
        if self.use_builder and self.blocks:
            try:
                data = json.loads(self.blocks) if isinstance(self.blocks, str) else self.blocks
                blocks_list = data.get('blocks', []) if isinstance(data, dict) else data
                for block in blocks_list:
                    if block.get('type') == 'accordion':
                        for item in block.get('data', {}).get('items', []):
                            title = item.get('title', '').strip()
                            content = item.get('content', '').strip()
                            if title and content:
                                clean = re.sub(r'<[^>]+>', '', content).strip()
                                items.append({'question': title, 'answer': clean})
            except (json.JSONDecodeError, TypeError, AttributeError):
                pass
        
        return items
    
    def get_translations(self):
        """Get all translations in the same group (excluding self)"""
        if not self.translation_group:
            return []
        return Page.query.filter(
            Page.translation_group == self.translation_group,
            Page.id != self.id
        ).all()
    
    def get_all_language_versions(self):
        """Get ALL language versions including self (for hreflang)"""
        if not self.translation_group:
            return [self]
        return Page.query.filter(
            Page.translation_group == self.translation_group
        ).order_by(Page.language).all()
    
    def get_translation(self, lang_code):
        """Get a specific translation by language code"""
        if self.language == lang_code:
            return self
        if not self.translation_group:
            return None
        return Page.query.filter_by(
            translation_group=self.translation_group,
            language=lang_code
        ).first()
    
    def get_available_languages(self):
        """Get list of languages that already have translations"""
        versions = self.get_all_language_versions()
        return [v.language for v in versions]
    
    def get_missing_languages(self):
        """Get languages that don't have a translation yet"""
        existing = set(self.get_available_languages())
        return [lang for lang in AVAILABLE_LANGUAGES if lang['code'] not in existing]
    
    def get_language_name(self):
        """Get human-readable language name"""
        return LANG_CODE_TO_NAME.get(self.language, self.language)


class MenuItem(db.Model):
    """Menu items for header, footer, sidebar"""
    __tablename__ = 'menu_items'
    
    id = db.Column(db.Integer, primary_key=True)
    menu_location = db.Column(db.String(50), nullable=False)  # header, footer, sidebar
    
    # Link details
    label = db.Column(db.String(200), nullable=False)
    url = db.Column(db.String(500))
    post_id = db.Column(db.Integer, db.ForeignKey('posts.id'))
    page_id = db.Column(db.Integer, db.ForeignKey('pages.id'))
    page_type = db.Column(db.String(50))  # post, page, custom, external
    
    # Language (for multilingual menus)
    language = db.Column(db.String(5), default='en')
    
    # Hierarchy
    parent_id = db.Column(db.Integer, db.ForeignKey('menu_items.id'))
    order = db.Column(db.Integer, default=0)
    
    # Settings
    is_active = db.Column(db.Boolean, default=True)
    target = db.Column(db.String(20), default='_self')  # _self, _blank
    css_class = db.Column(db.String(100))
    
    # Multilingual custom labels: JSON dict {"en": "About", "de": "Über uns"}
    labels_json = db.Column(db.Text)  # JSON dict of language-specific labels
    
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    children = db.relationship('MenuItem', backref=db.backref('parent', remote_side=[id]), lazy=True)
    post = db.relationship('Post', backref='menu_items', foreign_keys=[post_id])
    page = db.relationship('Page', backref='menu_items', foreign_keys=[page_id])


class Image(db.Model):
    """Media library for images with optimized variants for PageSpeed"""
    __tablename__ = 'images'
    
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(500), nullable=False)
    original_filename = db.Column(db.String(500))
    url = db.Column(db.String(500), nullable=False)  # internal long path for disk lookup
    
    # Clean public URL slug, without extension (e.g. 'sunset' → /sunset.jpg, /sunset-800w.webp, …)
    # Unique globally — cross-checked against File, Post and Page slugs.
    download_slug = db.Column(db.String(255), unique=True, index=True)
    
    # Required accessibility fields
    alt_text = db.Column(db.String(500), nullable=False)  # MANDATORY!
    title = db.Column(db.String(500))
    caption = db.Column(db.Text)
    description = db.Column(db.Text)
    
    # Technical info (original image dimensions)
    width = db.Column(db.Integer)
    height = db.Column(db.Integer)
    file_size = db.Column(db.Integer)  # in bytes
    mime_type = db.Column(db.String(50))
    
    # --- NEW: Optimized variants (WebP, AVIF, multiple sizes) ---
    # JSON structure:
    # {
    #   "original": {"url": "/static/uploads/photo.jpg", "width": 3000, "height": 2000, "size": 524288},
    #   "webp": {
    #     "400": {"url": "/static/uploads/photo_400w.webp", "width": 400, "height": 267, "size": 12340},
    #     "800": {"url": "/static/uploads/photo_800w.webp", "width": 800, "height": 533, "size": 34560},
    #     "1200": {"url": "/static/uploads/photo_1200w.webp", "width": 1200, "height": 800, "size": 67890},
    #     "1920": {"url": "/static/uploads/photo_1920w.webp", "width": 1920, "height": 1280, "size": 123456}
    #   },
    #   "avif": {
    #     "400": {"url": "/static/uploads/photo_400w.avif", "width": 400, "height": 267, "size": 8900},
    #     ...
    #   }
    # }
    variants = db.Column(db.Text)  # JSON — all generated sizes and formats
    
    # Dominant color for placeholder (prevents CLS before image loads)
    # Hex color like "#3a7bc8" — extracted from image on upload
    dominant_color = db.Column(db.String(7))
    
    # Timestamps
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # ---- Helper methods ----
    #
    # PUBLIC URL MODEL (2026 rewrite):
    #
    # Every public URL emitted by this model is a CLEAN SHORT URL derived from
    # `download_slug`. Long `/static/uploads/...` paths never leak into HTML,
    # srcset, og:image, schema.org, RSS or llms.txt. Physical files stay on disk
    # under their timestamped names; the route handler in app.py maps short URLs
    # back to disk files using this model's `variants` JSON.
    #
    # URL schemes produced:
    #   /{slug}.{ext}            → original                 (e.g. /sunset.jpg)
    #   /{slug}-{width}w.{fmt}   → responsive variant       (e.g. /sunset-800w.webp)
    #   /{slug}-og.{fmt}         → OG social-sharing crop   (e.g. /sunset-og.jpg)
    
    def get_variants(self):
        """Get variants dict from JSON"""
        if self.variants:
            try:
                return json.loads(self.variants)
            except (ValueError, TypeError):
                return {}
        return {}
    
    def set_variants(self, variants_dict):
        """Save variants dict as JSON"""
        self.variants = json.dumps(variants_dict)
    
    @property
    def original_extension(self):
        """Extension of the original file (jpg, png, gif, svg, webp, avif)."""
        if self.filename:
            parts = self.filename.rsplit('.', 1)
            if len(parts) == 2:
                return parts[1].lower()
        return 'jpg'
    
    @property
    def public_url(self):
        """Canonical short public URL for this image — used everywhere in
        HTML, admin 'Copy URL' buttons, og:image, schema.org, RSS, llms.txt,
        and TinyMCE insertion. Format: /{slug}.{ext}.
        """
        if self.download_slug:
            return f'/{self.download_slug}.{self.original_extension}'
        # Fallback only for legacy records without a slug (shouldn't happen
        # in a clean install — `admin_media_upload` always assigns one).
        return self.url
    
    def _mime_from_ext(self, ext=None):
        """Map a file extension to its MIME type."""
        ext = (ext or self.original_extension).lower()
        return {
            'png':  'image/png',
            'jpg':  'image/jpeg',
            'jpeg': 'image/jpeg',
            'webp': 'image/webp',
            'avif': 'image/avif',
            'gif':  'image/gif',
            'svg':  'image/svg+xml',
        }.get(ext, 'image/jpeg')
    
    def get_srcset(self, fmt='webp'):
        """Build srcset with SHORT URLs for the given variant format.
        Returns e.g.: '/sunset-400w.webp 400w, /sunset-800w.webp 800w, …'
        """
        variants = self.get_variants()
        fmt_variants = variants.get(fmt, {})
        if not fmt_variants or not self.download_slug:
            return ''
        parts = []
        for width_key in sorted(fmt_variants.keys(), key=int):
            parts.append(f"/{self.download_slug}-{width_key}w.{fmt} {width_key}w")
        return ', '.join(parts)
    
    def get_smallest_url(self, fmt='webp'):
        """Short URL of the smallest variant (for thumbnails, library grid)."""
        variants = self.get_variants()
        fmt_variants = variants.get(fmt, {})
        if not fmt_variants or not self.download_slug:
            return self.public_url
        smallest_key = min(fmt_variants.keys(), key=int)
        return f'/{self.download_slug}-{smallest_key}w.{fmt}'
    
    def get_largest_url(self, fmt='webp'):
        """Short URL of the largest variant (for featured image, full view)."""
        variants = self.get_variants()
        fmt_variants = variants.get(fmt, {})
        if not fmt_variants or not self.download_slug:
            return self.public_url
        largest_key = max(fmt_variants.keys(), key=int)
        return f'/{self.download_slug}-{largest_key}w.{fmt}'
    
    def get_og_url(self, fmt='jpg'):
        """Short URL of OG-cropped (1200×630) variant in the requested format.
        Falls back to any available OG format, then to the base public URL.
        """
        variants = self.get_variants()
        og_variants = variants.get('og', {})
        if not og_variants or not self.download_slug:
            return self.public_url
        if fmt in og_variants:
            return f'/{self.download_slug}-og.{fmt}'
        for fallback_fmt in ('jpg', 'webp', 'avif'):
            if fallback_fmt in og_variants:
                return f'/{self.download_slug}-og.{fallback_fmt}'
        return self.public_url
    
    def get_og_meta(self):
        """Return (short_url, mime_type) of the best available OG variant.
        
        Used by templates for <meta property="og:image"> + matching
        <meta property="og:image:type">. Priority: webp → avif → jpg → original.
        WebP first because it preserves transparency for RGBA originals at
        the smallest byte size; AVIF second; JPG third for legacy social
        scrapers; original last as a final fallback.
        """
        variants = self.get_variants()
        og_variants = variants.get('og', {})
        
        if self.download_slug and og_variants:
            for fmt, mime in (('webp', 'image/webp'),
                              ('avif', 'image/avif'),
                              ('jpg',  'image/jpeg')):
                if fmt in og_variants and og_variants[fmt].get('url'):
                    return (f'/{self.download_slug}-og.{fmt}', mime)
        
        # Fallback: base public URL with mime from its extension.
        return (self.public_url, self._mime_from_ext())
    
    def get_all_public_urls(self):
        """Every short URL that can reference this image anywhere on the site.
        
        Used by `_cleanup_image_references()` in app.py when an image is
        deleted, so that stale references in post/page content, builder
        blocks, menus and newsletters get scrubbed regardless of which
        specific variant URL the editor embedded.
        """
        if not self.download_slug:
            return []
        urls = [self.public_url]
        variants = self.get_variants()
        for fmt in ('webp', 'avif'):
            for width_key in variants.get(fmt, {}).keys():
                urls.append(f'/{self.download_slug}-{width_key}w.{fmt}')
        for fmt_key in variants.get('og', {}).keys():
            urls.append(f'/{self.download_slug}-og.{fmt_key}')
        return urls
    
    def get_all_file_paths(self, upload_folder):
        """Get list of ALL physical file paths (original + all variants + OG crops).
        Used for cleanup when deleting an image.
        """
        import os
        paths = []
        # Original file
        original_path = os.path.join(upload_folder, self.filename)
        paths.append(original_path)
        # All variants (webp, avif responsive sizes)
        variants = self.get_variants()
        for fmt in ['webp', 'avif']:
            fmt_variants = variants.get(fmt, {})
            for width_key, v in fmt_variants.items():
                variant_filename = v['url'].rsplit('/', 1)[-1]
                paths.append(os.path.join(upload_folder, variant_filename))
        # OG crop variants
        og_variants = variants.get('og', {})
        for fmt_key, v in og_variants.items():
            og_filename = v['url'].rsplit('/', 1)[-1]
            paths.append(os.path.join(upload_folder, og_filename))
        return paths


# ============================================================================
# FILES — Document manager (PDF, DOC, XLS, ZIP, etc.)
# ============================================================================

class File(db.Model):
    """File manager for documents — separate from Media Library (images).
    
    Supports: PDF, DOC/DOCX, XLS/XLSX, ZIP, RAR, TXT, CSV.
    No image processing, no variants, no alt_text — just file storage with metadata.
    Files stored in /static/uploads/files/
    """
    __tablename__ = 'files'
    
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(500), nullable=False)
    original_filename = db.Column(db.String(500))
    url = db.Column(db.String(500), nullable=False)
    
    # Clean public URL slug (e.g. 'report.pdf', unique)
    # Accessed as yoursite.com/report.pdf
    download_slug = db.Column(db.String(500), unique=True, index=True)
    
    # User-facing metadata
    title = db.Column(db.String(500))
    description = db.Column(db.Text)
    
    # Technical info
    file_size = db.Column(db.Integer)  # in bytes
    mime_type = db.Column(db.String(100))
    
    # Timestamps & ownership
    uploaded_at = db.Column(db.DateTime, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # ---- Helper methods ----
    
    def get_extension(self):
        """Get file extension (lowercase, without dot)"""
        if self.original_filename:
            parts = self.original_filename.rsplit('.', 1)
            if len(parts) > 1:
                return parts[1].lower()
        if self.filename:
            parts = self.filename.rsplit('.', 1)
            if len(parts) > 1:
                return parts[1].lower()
        return ''
    
    def get_icon(self):
        """Return emoji icon based on file type"""
        ext = self.get_extension()
        icons = {
            'pdf': '📕',
            'doc': '📘', 'docx': '📘',
            'xls': '📗', 'xlsx': '📗',
            'zip': '📦', 'rar': '📦',
            'txt': '📄',
            'csv': '📊',
        }
        return icons.get(ext, '📄')
    
    def get_type_label(self):
        """Return human-readable type label"""
        ext = self.get_extension()
        labels = {
            'pdf': 'PDF',
            'doc': 'DOC', 'docx': 'DOCX',
            'xls': 'XLS', 'xlsx': 'XLSX',
            'zip': 'ZIP', 'rar': 'RAR',
            'txt': 'TXT',
            'csv': 'CSV',
        }
        return labels.get(ext, ext.upper())
    
    def human_size(self):
        """Return human-readable file size"""
        if not self.file_size:
            return '—'
        if self.file_size < 1024:
            return f'{self.file_size} B'
        elif self.file_size < 1024 * 1024:
            return f'{self.file_size / 1024:.0f} KB'
        else:
            return f'{self.file_size / (1024 * 1024):.1f} MB'


# ============================================================================
# FORMS — Smart Form Builder with submissions tracking
# ============================================================================

class Form(db.Model):
    """Form builder — stores form structure as JSON.
    
    Supports: contact forms, surveys, polls, subscription forms, custom forms.
    Fields are stored as JSON for maximum flexibility and AI-agent compatibility.
    
    JSON structure for `fields`:
    [
        {
            "id": "f1a2b3",
            "type": "text|email|phone|number|textarea|select|radio|checkbox|date|url|file|hidden|rating|consent",
            "name": "field_name",
            "label": "Field Label",
            "placeholder": "Placeholder text...",
            "required": true,
            "options": ["Option 1", "Option 2"],
            "max_rating": 5,
            "consent_text": "I agree to...",
            "validation": {"min": 0, "max": 100},
            "width": "full|half",
            "help_text": "Additional help text"
        }
    ]
    
    JSON structure for `settings`:
    {
        "submit_label": "Send Message",
        "success_message": "Thank you! We'll get back to you soon.",
        "redirect_url": "",
        "notify_email": "admin@example.com",
        "notify_on_submit": true,
        "store_submissions": true,
        "anti_spam": "honeypot",
        "limit_per_ip": 0,
        "confirmation_email": false,
        "double_opt_in": false
    }
    """
    __tablename__ = 'forms'
    
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(500), nullable=False)
    slug = db.Column(db.String(500), unique=True, nullable=False)
    description = db.Column(db.Text)  # Shown above the form on the public site
    
    # Language & Translations (same pattern as Post/Page)
    language = db.Column(db.String(5), default='en')  # ISO code
    translation_group = db.Column(db.String(36), index=True)  # UUID linking all translations together
    
    # Form type (for templates and filtering)
    form_type = db.Column(db.String(50), default='custom')  # contact, survey, subscription, custom
    
    # Fields and settings stored as JSON
    fields = db.Column(db.Text, nullable=False, default='[]')       # JSON array of field definitions
    settings = db.Column(db.Text, nullable=False, default='{}')     # JSON settings object
    
    # Status
    is_active = db.Column(db.Boolean, default=True)
    
    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Author
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # Relationships
    submissions = db.relationship('FormSubmission', backref='form', lazy='dynamic', cascade='all, delete-orphan')
    
    # Unique constraint: one language per translation group
    __table_args__ = (
        db.UniqueConstraint('translation_group', 'language', name='uq_form_translation_group_language'),
    )
    
    def __init__(self, **kwargs):
        super(Form, self).__init__(**kwargs)
        if not self.slug and self.title:
            self.slug = self.generate_unique_slug(self.title)
        # Auto-generate translation_group if not provided
        if not self.translation_group:
            self.translation_group = generate_translation_group()
    
    def generate_unique_slug(self, title):
        """Generate unique slug from title"""
        base_slug = slugify(title)
        if not base_slug:
            base_slug = 'form'
        slug = base_slug
        counter = 1
        while Form.query.filter_by(slug=slug).first():
            slug = f"{base_slug}-{counter}"
            counter += 1
        return slug
    
    def get_fields(self):
        """Get fields as list"""
        try:
            return json.loads(self.fields) if self.fields else []
        except (json.JSONDecodeError, TypeError):
            return []
    
    def set_fields(self, fields_list):
        """Set fields from list"""
        self.fields = json.dumps(fields_list, ensure_ascii=False)
    
    def get_settings(self):
        """Get settings as dict"""
        defaults = {
            'submit_label': 'Submit',
            'success_message': 'Thank you! Your response has been recorded.',
            'redirect_url': '',
            'notify_email': '',
            'notify_on_submit': True,
            'store_submissions': True,
            'anti_spam': 'honeypot',
            'limit_per_ip': 0,
            'confirmation_email': True,
            'double_opt_in': True,
        }
        try:
            saved = json.loads(self.settings) if self.settings else {}
        except (json.JSONDecodeError, TypeError):
            saved = {}
        defaults.update(saved)
        return defaults
    
    def set_settings(self, settings_dict):
        """Set settings from dict"""
        self.settings = json.dumps(settings_dict, ensure_ascii=False)
    
    def get_submission_count(self):
        """Get total number of submissions"""
        return self.submissions.count()
    
    def get_new_submission_count(self):
        """Get number of unread submissions"""
        return self.submissions.filter_by(status='new').count()
    
    # --- Translation methods (same pattern as Post/Page) ---
    
    def get_translations(self):
        """Get all translations in the same group (excluding self)"""
        if not self.translation_group:
            return []
        return Form.query.filter(
            Form.translation_group == self.translation_group,
            Form.id != self.id
        ).all()
    
    def get_all_language_versions(self):
        """Get ALL language versions including self"""
        if not self.translation_group:
            return [self]
        return Form.query.filter(
            Form.translation_group == self.translation_group
        ).order_by(Form.language).all()
    
    def get_translation(self, lang_code):
        """Get a specific translation by language code"""
        if self.language == lang_code:
            return self
        if not self.translation_group:
            return None
        return Form.query.filter_by(
            translation_group=self.translation_group,
            language=lang_code
        ).first()
    
    def get_available_languages(self):
        """Get list of languages that already have translations"""
        versions = self.get_all_language_versions()
        return [v.language for v in versions]
    
    def get_missing_languages(self):
        """Get languages that don't have a translation yet"""
        existing = set(self.get_available_languages())
        return [lang for lang in AVAILABLE_LANGUAGES if lang['code'] not in existing]
    
    def get_language_name(self):
        """Get human-readable language name"""
        return LANG_CODE_TO_NAME.get(self.language, self.language)


class FormSubmission(db.Model):
    """Individual form submission — stores response data as JSON.
    
    Email confirmation (double opt-in):
        email_confirmed = None  → form doesn't use double opt-in
        email_confirmed = False → confirmation email sent, waiting for click
        email_confirmed = True  → email confirmed by visitor (confirmed_at set)
    
    JSON structure for `data`:
    {
        "name": "John Doe",
        "email": "john@example.com",
        "message": "Hello...",
        "_meta": {
            "form_title": "Contact Form",
            "submitted_at": "2026-02-25T12:00:00",
            "user_agent": "Mozilla/5.0..."
        }
    }
    """
    __tablename__ = 'form_submissions'
    
    id = db.Column(db.Integer, primary_key=True)
    form_id = db.Column(db.Integer, db.ForeignKey('forms.id'), nullable=False, index=True)
    
    # Submission data as JSON
    data = db.Column(db.Text, nullable=False, default='{}')
    
    # Status for admin workflow
    status = db.Column(db.String(20), default='new')  # new, read, archived, spam
    
    # Email confirmation (double opt-in)
    # None = form doesn't use double opt-in (not applicable)
    # False = confirmation email sent, waiting for click
    # True = email confirmed by visitor
    email_confirmed = db.Column(db.Boolean, nullable=True, default=None)
    confirmation_token = db.Column(db.String(100), nullable=True, index=True)
    confirmed_at = db.Column(db.DateTime, nullable=True)
    
    # Metadata
    ip_address = db.Column(db.String(45))
    user_agent = db.Column(db.String(500))
    referrer = db.Column(db.String(500))
    
    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow, index=True)
    
    def get_data(self):
        """Get submission data as dict"""
        try:
            return json.loads(self.data) if self.data else {}
        except (json.JSONDecodeError, TypeError):
            return {}
    
    def set_data(self, data_dict):
        """Set submission data from dict"""
        self.data = json.dumps(data_dict, ensure_ascii=False)
    
    def get_field_value(self, field_name):
        """Get a specific field value"""
        return self.get_data().get(field_name, '')
    
    def generate_confirmation_token(self):
        """Generate a unique token for double opt-in email confirmation"""
        import secrets
        self.confirmation_token = secrets.token_urlsafe(32)
        self.email_confirmed = False
        return self.confirmation_token
    
    def confirm_email(self):
        """Mark email as confirmed (double opt-in completed)"""
        self.email_confirmed = True
        self.confirmed_at = datetime.utcnow()
        # Keep confirmation_token so repeat clicks show "already confirmed" instead of 404


# ============================================================================
# NEWSLETTER — Email campaigns to form subscribers
# ============================================================================

class Newsletter(db.Model):
    """Newsletter campaign — stores email content and send status.
    
    Targets subscribers from ANY form that has an email field.
    Filters by form language to send in the correct language.
    """
    __tablename__ = 'newsletters'
    
    id = db.Column(db.Integer, primary_key=True)
    subject = db.Column(db.String(500), nullable=False)
    content = db.Column(db.Text, nullable=False, default='')  # HTML content (TinyMCE)
    
    # Language & Translations (same pattern as Post/Page)
    language = db.Column(db.String(5), default='en')  # ISO code — language of the campaign content
    translation_group = db.Column(db.String(36), index=True)  # UUID linking translations together
    
    # Target audience
    target_languages = db.Column(db.Text, default='[]')  # JSON array of language codes, e.g. ["en", "de"]
    target_form_ids = db.Column(db.Text, default='[]')    # JSON array of form IDs (empty = all forms)
    excluded_emails = db.Column(db.Text, default='[]')    # JSON array of emails manually excluded
    imported_emails = db.Column(db.Text, default='[]')    # JSON array of {"email": "...", "name": "..."} imported from CSV
    confirmed_only = db.Column(db.Boolean, default=True)  # Only send to confirmed subscribers (skip pending)
    
    # Status: draft, sending, sent, failed
    status = db.Column(db.String(20), default='draft')
    
    # Stats
    recipients_count = db.Column(db.Integer, default=0)   # Total emails sent
    failed_count = db.Column(db.Integer, default=0)       # Failed deliveries
    
    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    sent_at = db.Column(db.DateTime)  # When sending completed
    
    # Author
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    
    # Unique constraint: one language per translation group
    __table_args__ = (
        db.UniqueConstraint('translation_group', 'language', name='uq_newsletter_translation_group_language'),
    )
    
    def __init__(self, **kwargs):
        super(Newsletter, self).__init__(**kwargs)
        # Auto-generate translation_group if not provided
        if not self.translation_group:
            self.translation_group = generate_translation_group()
    
    # ── Translation helpers (same pattern as Post/Page) ──
    
    def get_translations(self):
        """Get all translations in the same group (excluding self)"""
        if not self.translation_group:
            return []
        return Newsletter.query.filter(
            Newsletter.translation_group == self.translation_group,
            Newsletter.id != self.id
        ).all()
    
    def get_all_language_versions(self):
        """Get ALL language versions including self"""
        if not self.translation_group:
            return [self]
        return Newsletter.query.filter(
            Newsletter.translation_group == self.translation_group
        ).order_by(Newsletter.language).all()
    
    def get_translation(self, lang_code):
        """Get a specific translation by language code"""
        if self.language == lang_code:
            return self
        if not self.translation_group:
            return None
        return Newsletter.query.filter_by(
            translation_group=self.translation_group,
            language=lang_code
        ).first()
    
    def get_available_languages(self):
        """Get list of languages that already have translations"""
        versions = self.get_all_language_versions()
        return [v.language for v in versions]
    
    def get_missing_languages(self):
        """Get languages that don't have a translation yet"""
        existing = set(self.get_available_languages())
        return [lang for lang in AVAILABLE_LANGUAGES if lang['code'] not in existing]
    
    def get_language_name(self):
        """Get human-readable language name"""
        return LANG_CODE_TO_NAME.get(self.language, self.language)
    
    # ── Target audience helpers ──
    
    def get_target_languages(self):
        """Get target languages as list"""
        try:
            return json.loads(self.target_languages) if self.target_languages else []
        except (json.JSONDecodeError, TypeError):
            return []
    
    def set_target_languages(self, langs_list):
        """Set target languages from list"""
        self.target_languages = json.dumps(langs_list)
    
    def get_target_form_ids(self):
        """Get target form IDs as list"""
        try:
            return json.loads(self.target_form_ids) if self.target_form_ids else []
        except (json.JSONDecodeError, TypeError):
            return []
    
    def set_target_form_ids(self, ids_list):
        """Set target form IDs from list"""
        self.target_form_ids = json.dumps(ids_list)
    
    def get_excluded_emails(self):
        """Get excluded emails as list"""
        try:
            return json.loads(self.excluded_emails) if self.excluded_emails else []
        except (json.JSONDecodeError, TypeError):
            return []
    
    def set_excluded_emails(self, emails_list):
        """Set excluded emails from list"""
        self.excluded_emails = json.dumps(emails_list)
    
    def get_imported_emails(self):
        """Get imported emails as list of dicts [{email, name}, ...]"""
        try:
            return json.loads(self.imported_emails) if self.imported_emails else []
        except (json.JSONDecodeError, TypeError):
            return []
    
    def set_imported_emails(self, imports_list):
        """Set imported emails from list of dicts"""
        self.imported_emails = json.dumps(imports_list, ensure_ascii=False)


class Settings(db.Model):
    """Site settings - key-value storage"""
    __tablename__ = 'settings'
    
    key = db.Column(db.String(200), primary_key=True)
    value = db.Column(db.Text)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    @staticmethod
    def get(key, default=None):
        """Get setting value"""
        setting = Settings.query.get(key)
        return setting.value if setting else default
    
    @staticmethod
    def set(key, value):
        """Set setting value"""
        setting = Settings.query.get(key)
        if setting:
            setting.value = value
            setting.updated_at = datetime.utcnow()
        else:
            setting = Settings(key=key, value=value)
            db.session.add(setting)
        db.session.commit()


class Redirect(db.Model):
    """Automatic 301 redirects — created when a post/page slug changes.
    
    Prevents broken links and preserves SEO rankings.
    If a slug is changed multiple times (A → B → C), the chain is flattened:
    both A → C and B → C will exist, so there's never a redirect chain.
    """
    __tablename__ = 'redirects'
    
    id = db.Column(db.Integer, primary_key=True)
    old_path = db.Column(db.String(500), unique=True, nullable=False, index=True)  # e.g. "old-slug"
    new_path = db.Column(db.String(500), nullable=False)  # e.g. "new-slug"
    content_type = db.Column(db.String(20))  # "post" or "page"
    content_id = db.Column(db.Integer)  # ID of the post/page (for reference)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    @staticmethod
    def create_redirect(old_slug, new_slug, content_type, content_id):
        """Create a redirect and flatten any existing chains.
        
        If old redirects already point to old_slug, update them
        to point directly to new_slug (no chain: A→B→C becomes A→C, B→C).
        """
        # Don't create a redirect to itself
        if old_slug == new_slug:
            return
        
        # If new_slug already has a redirect entry as old_path, remove it
        # (the new slug is now a real page, not a redirect target)
        existing_as_old = Redirect.query.filter_by(old_path=new_slug).first()
        if existing_as_old:
            db.session.delete(existing_as_old)
        
        # Flatten chains: update any existing redirects that point to old_slug
        Redirect.query.filter_by(new_path=old_slug).update({'new_path': new_slug})
        
        # Create or update the redirect for old_slug
        redirect_entry = Redirect.query.filter_by(old_path=old_slug).first()
        if redirect_entry:
            redirect_entry.new_path = new_slug
            redirect_entry.content_type = content_type
            redirect_entry.content_id = content_id
            redirect_entry.created_at = datetime.utcnow()
        else:
            redirect_entry = Redirect(
                old_path=old_slug,
                new_path=new_slug,
                content_type=content_type,
                content_id=content_id
            )
            db.session.add(redirect_entry)
        
        # Don't commit here — let the caller commit with the rest of the transaction


class AuditLog(db.Model):
    """Audit trail — records all significant actions in the admin panel.
    
    Used for security monitoring, debugging, and accountability.
    """
    __tablename__ = 'audit_log'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    username = db.Column(db.String(80))  # Stored separately in case user is deleted
    action = db.Column(db.String(50), nullable=False)  # login, create_post, edit_page, delete_post, etc.
    target_type = db.Column(db.String(30))  # post, page, user, settings, media
    target_id = db.Column(db.Integer)  # ID of the affected object
    details = db.Column(db.Text)  # Human-readable description
    ip_address = db.Column(db.String(45))  # IPv4 or IPv6
    created_at = db.Column(db.DateTime, default=datetime.utcnow, index=True)
    
    # Relationship (optional — user may have been deleted)
    user = db.relationship('User', backref=db.backref('audit_logs', lazy='dynamic'))
    
    @staticmethod
    def log(action, target_type=None, target_id=None, details=None, user=None, ip=None):
        """Create an audit log entry.
        
        Args:
            action: Short action name (login, create_post, edit_page, delete_user, etc.)
            target_type: What was affected (post, page, user, settings, media)
            target_id: ID of the affected object
            details: Human-readable description
            user: User object (or None for anonymous actions like failed login)
            ip: IP address string
        """
        entry = AuditLog(
            user_id=user.id if user else None,
            username=user.get_display_name() if user else None,
            action=action,
            target_type=target_type,
            target_id=target_id,
            details=details,
            ip_address=ip
        )
        db.session.add(entry)
        # Don't commit — let the caller commit with the rest of the transaction


class LoginAttempt(db.Model):
    """Tracks failed login attempts for brute-force protection.
    
    After 5 failed attempts within 15 minutes from the same IP,
    the IP is blocked for 15 minutes.
    """
    __tablename__ = 'login_attempts'
    
    id = db.Column(db.Integer, primary_key=True)
    ip_address = db.Column(db.String(45), nullable=False, index=True)
    username = db.Column(db.String(80))  # What username was tried
    success = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    @staticmethod
    def is_blocked(ip, max_attempts=5, window_minutes=15):
        """Check if an IP is currently blocked due to too many failed attempts."""
        from datetime import timedelta
        cutoff = datetime.utcnow() - timedelta(minutes=window_minutes)
        
        failed_count = LoginAttempt.query.filter(
            LoginAttempt.ip_address == ip,
            LoginAttempt.success == False,
            LoginAttempt.created_at >= cutoff
        ).count()
        
        return failed_count >= max_attempts
    
    @staticmethod
    def record(ip, username, success):
        """Record a login attempt."""
        entry = LoginAttempt(
            ip_address=ip,
            username=username,
            success=success
        )
        db.session.add(entry)
        db.session.commit()
    
    @staticmethod
    def clear_old(days=7):
        """Remove login attempts older than N days (housekeeping)."""
        from datetime import timedelta
        cutoff = datetime.utcnow() - timedelta(days=days)
        LoginAttempt.query.filter(LoginAttempt.created_at < cutoff).delete()
        db.session.commit()


def init_default_settings():
    """Initialize default settings on first run"""
    defaults = {
        # Site Identity
        'site_name': 'AliothPress',
        'site_tagline': 'Create once, publish everywhere',
        'site_description': 'Multilingual AI-powered content management system',
        'site_logo': '',
        
        # Header
        'header_layout': 'logo-left',
        
        # Footer
        'footer_text': '© 2026 AliothPress. All rights reserved.',
        'footer_layout': '3-column',
        'show_social_icons': 'true',
        
        # Theme/Colors
        'theme_primary_color': '#2563eb',
        'theme_secondary_color': '#7c3aed',
        'theme_font_heading': 'Inter, system-ui, sans-serif',
        'theme_font_body': 'Inter, system-ui, sans-serif',
        'site_custom_css': '',
        
        # Social Links
        'social_facebook': '',
        'social_twitter': '',
        'social_linkedin': '',
        'social_github': '',
        'social_youtube': '',
        'social_instagram': '',
        
        # Google Services
        'google_analytics_id': '',
        'google_search_console': '',
        'google_tag_manager': '',
        'google_adsense': '',
        
        # SEO
        'default_og_image': '/static/images/default-og-image.jpg',
        'robots_txt': 'User-agent: *\nAllow: /\n\nUser-agent: GPTBot\nAllow: /\n\nUser-agent: Google-Extended\nAllow: /\n\nUser-agent: ClaudeBot\nAllow: /\n\nUser-agent: Amazonbot\nAllow: /',
        
        # Social Meta
        'facebook_app_id': '',
        'twitter_username': '',
        
        # Legal (empty by default, will be filled by page content)
        'privacy_policy_url': '/privacy-policy',
        'legal_notice_url': '/legal-notice',
        'accessibility_url': '/accessibility',
        
        # Language settings
        'default_language': 'en',
        'active_languages': 'en',
        'show_language_switcher': 'true',
        
        # AEO / Entity Graph
        'org_sameas_wikipedia': '',
        'org_sameas_wikidata': '',
        'org_sameas_crunchbase': '',
        'org_legal_name': '',
        'org_founding_year': '',
    }
    
    for key, value in defaults.items():
        if not Settings.query.get(key):
            Settings.set(key, value)


def create_default_pages(admin_user_id, language='en'):
    """Create default legal pages in the specified language.
    
    Content is loaded from static/i18n/default_pages.json.
    Falls back to English if the requested language is not available.
    Slug is auto-generated from the title (no hardcoded slugs).
    After creation, updates Settings with the actual page URLs.
    """
    import os
    
    # Load page content from JSON
    pages_file = os.path.join(
        os.path.dirname(os.path.abspath(__file__)),
        'static', 'i18n', 'default_pages.json'
    )
    
    page_templates = None
    if os.path.exists(pages_file):
        try:
            with open(pages_file, 'r', encoding='utf-8') as f:
                all_pages = json.load(f)
            # Try requested language, fallback to English
            page_templates = all_pages.get(language) or all_pages.get('en')
        except (json.JSONDecodeError, IOError):
            pass
    
    # Hardcoded English fallback — used only if default_pages.json is missing/broken
    if not page_templates:
        page_templates = [
            {
                'key': 'privacy_policy',
                'title': 'Privacy Policy',
                'content': '<h1>Privacy Policy</h1>\n<p>This Privacy Policy explains how we collect, use, and protect your personal information.</p>\n<p><em>Last updated: [Date]</em></p>',
            },
            {
                'key': 'legal_notice',
                'title': 'Legal Notice',
                'content': '<h1>Legal Notice (Impressum)</h1>\n<p>[Your Name / Company Name]<br>[Address]<br>[Country]</p>\n<p><em>Last updated: [Date]</em></p>',
            },
            {
                'key': 'accessibility',
                'title': 'Accessibility',
                'content': '<h1>Accessibility Statement</h1>\n<p>We are committed to ensuring digital accessibility for people with disabilities.</p>\n<p><em>Last updated: [Date]</em></p>',
            },
        ]
    
    # Legacy slugs from pre-i18n installations (hardcoded English slugs)
    # Used to detect existing default pages on databases created before i18n update
    legacy_slugs = {
        'privacy_policy': 'privacy-policy',
        'legal_notice': 'legal-notice',
        'accessibility': 'accessibility',
    }
    
    # Settings key → page key mapping for URL updates
    settings_url_map = {
        'privacy_policy': 'privacy_policy_url',
        'legal_notice': 'legal_notice_url',
        'accessibility': 'accessibility_url',
    }
    
    for tpl in page_templates:
        page_key = tpl.get('key', '')
        title = tpl['title']
        
        # Check if this default page already exists:
        # 1. By new translation_group prefix (post-i18n installations)
        # 2. By legacy English slug (pre-i18n installations)
        existing = Page.query.filter(
            Page.translation_group.like(f'default_{page_key}%'),
            Page.language == language
        ).first()
        
        if not existing and page_key in legacy_slugs:
            existing = Page.query.filter_by(slug=legacy_slugs[page_key]).first()
        
        if existing:
            continue
        
        # Let Page.__init__ auto-generate slug from title
        page = Page(
            title=title,
            content=tpl['content'],
            template='default',
            status='published',
            user_id=admin_user_id,
            language=language,
            translation_group=f'default_{page_key}',
        )
        db.session.add(page)
        db.session.flush()  # Get the slug before commit
        
        # Update Settings URL to point to the actual auto-generated slug
        if page_key in settings_url_map:
            Settings.set(settings_url_map[page_key], f'/{page.slug}')
    
    db.session.commit()


def create_default_menu_items():
    """Create default menu structure"""
    # Only create if no menu items exist
    if MenuItem.query.count() > 0:
        return
    
    # Footer menu — find default legal pages by their translation_group prefix
    pages = Page.query.filter(
        Page.translation_group.in_(['default_privacy_policy', 'default_legal_notice', 'default_accessibility'])
    ).all()
    
    for idx, page in enumerate(pages):
        menu_item = MenuItem(
            menu_location='footer',
            label=page.title,
            url=f'/{page.slug}',
            page_id=page.id,
            page_type='page',
            language=page.language,
            order=idx,
            is_active=True
        )
        db.session.add(menu_item)
    
    db.session.commit()
