nordabiz/database.py

"""
Norda Biznes - Database Models
===============================

SQLAlchemy models for PostgreSQL database.

Models:
- User: User accounts with authentication
- Company: Company information
- Category, Service, Competency: Company classifications
- AIChatConversation, AIChatMessage: Chat history
- AIAPICostLog: API cost tracking
- CompanyDigitalMaturity: Digital maturity scores and benchmarking
- CompanyWebsiteAnalysis: Website analysis and SEO metrics
- MaturityAssessment: Historical tracking of maturity scores
- GBPAudit: Google Business Profile audit results
- ITAudit: IT infrastructure audit results
- ITCollaborationMatch: IT collaboration matches between companies
- AIUsageLog, AIUsageDaily, AIRateLimit: AI API usage monitoring
- Announcement: Ogłoszenia i aktualności dla członków

Author: Maciej Pienczyn, InPi sp. z o.o.
Created: 2025-11-23
Updated: 2026-01-11 (AI Usage Tracking)
"""

import os
import json
from datetime import datetime
from enum import IntEnum
from sqlalchemy import create_engine, Column, Integer, SmallInteger, String, Text, Boolean, DateTime, Float, ForeignKey, Table, Numeric, Date, Time, TypeDecorator, UniqueConstraint, Enum
from sqlalchemy.dialects.postgresql import ARRAY as PG_ARRAY, JSONB as PG_JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from flask_login import UserMixin


# ============================================================
# ROLE SYSTEM ENUMS
# ============================================================

class SystemRole(IntEnum):
    """
    System-wide user roles with hierarchical access levels.
    Higher value = more permissions.

    Role hierarchy:
    - UNAFFILIATED (10): Firma spoza Izby - tylko publiczne profile (bez kontaktów)
    - MEMBER (20): Członek Norda bez firmy - pełny dostęp do treści
    - EMPLOYEE (30): Pracownik firmy członkowskiej - może edytować dane firmy
    - MANAGER (40): Kadra zarządzająca - pełna kontrola firmy + zarządzanie użytkownikami
    - OFFICE_MANAGER (50): Kierownik biura Norda - panel admina (bez użytkowników)
    - ADMIN (100): Administrator portalu - zarządzanie treścią i użytkownikami
    - SUPERADMIN (200): Superadministrator - pełne prawa + audyty techniczne
    """
    UNAFFILIATED = 10   # Niezrzeszony (firma spoza Izby)
    MEMBER = 20         # Członek Norda bez firmy
    EMPLOYEE = 30       # Pracownik firmy członkowskiej
    MANAGER = 40        # Kadra zarządzająca firmy
    OFFICE_MANAGER = 50 # Kierownik biura Norda
    ADMIN = 100         # Administrator portalu
    SUPERADMIN = 200    # Superadministrator - pełne prawa + audyty

    @classmethod
    def choices(cls):
        """Return list of (value, label) tuples for forms."""
        labels = {
            cls.UNAFFILIATED: 'Niezrzeszony',
            cls.MEMBER: 'Członek',
            cls.EMPLOYEE: 'Pracownik',
            cls.MANAGER: 'Kadra Zarządzająca',
            cls.OFFICE_MANAGER: 'Kierownik Biura',
            cls.ADMIN: 'Administrator',
            cls.SUPERADMIN: 'Superadministrator',
        }
        return [(role.value, labels[role]) for role in cls]

    @classmethod
    def from_string(cls, value: str) -> 'SystemRole':
        """Convert string to SystemRole enum."""
        mapping = {
            'UNAFFILIATED': cls.UNAFFILIATED,
            'MEMBER': cls.MEMBER,
            'EMPLOYEE': cls.EMPLOYEE,
            'MANAGER': cls.MANAGER,
            'OFFICE_MANAGER': cls.OFFICE_MANAGER,
            'ADMIN': cls.ADMIN,
            'SUPERADMIN': cls.SUPERADMIN,
        }
        return mapping.get(value.upper(), cls.UNAFFILIATED)


class CompanyRole(IntEnum):
    """
    User's role within their assigned company.
    Determines what actions they can perform on company data.

    - NONE (0): Brak powiązania z firmą
    - VIEWER (10): Może przeglądać dashboard firmy
    - EMPLOYEE (20): Może edytować dane firmy (opis, usługi, kompetencje)
    - MANAGER (30): Pełna kontrola + zarządzanie użytkownikami firmy
    """
    NONE = 0        # Brak powiązania z firmą
    VIEWER = 10     # Może przeglądać dashboard firmy
    EMPLOYEE = 20   # Może edytować dane firmy
    MANAGER = 30    # Pełna kontrola + zarządzanie użytkownikami firmy

    @classmethod
    def choices(cls):
        """Return list of (value, label) tuples for forms."""
        labels = {
            cls.NONE: 'Brak',
            cls.VIEWER: 'Podgląd',
            cls.EMPLOYEE: 'Pracownik',
            cls.MANAGER: 'Zarządzający',
        }
        return [(role.value, labels[role]) for role in cls]

    @classmethod
    def from_string(cls, value: str) -> 'CompanyRole':
        """Convert string to CompanyRole enum."""
        mapping = {
            'NONE': cls.NONE,
            'VIEWER': cls.VIEWER,
            'EMPLOYEE': cls.EMPLOYEE,
            'MANAGER': cls.MANAGER,
        }
        return mapping.get(value.upper(), cls.NONE)

# Database configuration
# WARNING: The fallback DATABASE_URL uses a placeholder password.
# Production credentials MUST be set via the DATABASE_URL environment variable.
# NEVER commit real credentials to version control (CWE-798).
DATABASE_URL = os.getenv(
    'DATABASE_URL',
    'postgresql://nordabiz_app:CHANGE_ME@localhost:5432/nordabiz'
)

# Determine if we're using SQLite
IS_SQLITE = DATABASE_URL.startswith('sqlite')


def normalize_social_url(url: str, platform: str = None) -> str:
    """
    Normalize social media URLs to prevent duplicates.

    Handles:
    - www vs non-www (removes www.)
    - http vs https (forces https)
    - Trailing slashes (removes)
    - Platform-specific canonicalization

    Examples:
        normalize_social_url('http://www.facebook.com/inpipl/')
        -> 'https://facebook.com/inpipl'

        normalize_social_url('https://www.instagram.com/user/')
        -> 'https://instagram.com/user'
    """
    if not url:
        return url

    url = url.strip()

    # Force https
    if url.startswith('http://'):
        url = 'https://' + url[7:]
    elif not url.startswith('https://'):
        url = 'https://' + url

    # Remove www. prefix
    url = url.replace('https://www.', 'https://')

    # Remove trailing slash
    url = url.rstrip('/')

    # Platform-specific normalization
    if platform == 'facebook' or 'facebook.com' in url:
        # fb.com -> facebook.com
        url = url.replace('https://fb.com/', 'https://facebook.com/')
        url = url.replace('https://m.facebook.com/', 'https://facebook.com/')

    if platform == 'twitter' or 'twitter.com' in url or 'x.com' in url:
        # x.com -> twitter.com (or vice versa, pick one canonical)
        url = url.replace('https://x.com/', 'https://twitter.com/')

    if platform == 'linkedin' or 'linkedin.com' in url:
        # Remove locale prefix
        url = url.replace('/pl/', '/').replace('/en/', '/')

    return url


class StringArray(TypeDecorator):
    """
    Platform-agnostic array type.
    Uses PostgreSQL ARRAY for PostgreSQL, stores as JSON string for SQLite.
    """
    impl = Text
    cache_ok = True

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(PG_ARRAY(String))
        return dialect.type_descriptor(Text())

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        if dialect.name == 'postgresql':
            return value
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        if dialect.name == 'postgresql':
            return value
        if isinstance(value, list):
            return value
        return json.loads(value)


class JSONBType(TypeDecorator):
    """
    Platform-agnostic JSONB type.
    Uses PostgreSQL JSONB for PostgreSQL, stores as JSON string for SQLite.
    """
    impl = Text
    cache_ok = True

    def load_dialect_impl(self, dialect):
        if dialect.name == 'postgresql':
            return dialect.type_descriptor(PG_JSONB())
        return dialect.type_descriptor(Text())

    def process_bind_param(self, value, dialect):
        if value is None:
            return None
        if dialect.name == 'postgresql':
            return value
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return None
        if dialect.name == 'postgresql':
            return value
        if isinstance(value, dict):
            return value
        return json.loads(value)


# Aliases for backwards compatibility
ARRAY = StringArray
JSONB = JSONBType


# Create engine
engine = create_engine(DATABASE_URL, echo=False, pool_size=10, pool_pre_ping=True)
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()


# ============================================================
# USER MANAGEMENT
# ============================================================

class User(Base, UserMixin):
    """User accounts with role-based access control."""
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    name = Column(String(255))
    company_nip = Column(String(10))
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=True)
    company = relationship('Company', backref='users', lazy='joined')  # eager load to avoid DetachedInstanceError
    person_id = Column(Integer, ForeignKey('people.id'), nullable=True)
    person = relationship('Person', backref='users', lazy='joined')  # Link to Person (KRS data)
    phone = Column(String(50))

    # === ROLE SYSTEM (added 2026-02-01) ===
    # System-wide role determining overall access level
    role = Column(String(20), default='UNAFFILIATED', nullable=False)
    # Role within assigned company (if any)
    company_role = Column(String(20), default='NONE', nullable=False)

    # Status
    is_active = Column(Boolean, default=True)
    is_verified = Column(Boolean, default=False)
    is_admin = Column(Boolean, default=False)  # DEPRECATED: synced by set_role() for backward compat. Use has_role(SystemRole.ADMIN) instead. Will be removed in future migration.
    is_norda_member = Column(Boolean, default=False)
    is_rada_member = Column(Boolean, default=False)  # Member of Rada Izby (Board Council)
    chamber_role = Column(String(50))  # prezes, wiceprezes, czlonek_rady, komisja_rewizyjna, sad_kolezenski
    avatar_path = Column(String(500))  # Path to profile photo (relative to static/uploads/)

    # Account origin tracking
    created_by_id = Column(Integer, ForeignKey('users.id'), nullable=True)  # NULL = self-registration
    created_by = relationship('User', remote_side='User.id', foreign_keys='User.created_by_id', lazy='joined')

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    last_login = Column(DateTime)
    last_active_at = Column(DateTime)
    verified_at = Column(DateTime)

    # Engagement metrics
    login_count = Column(Integer, default=0)
    page_views_count = Column(Integer, default=0)

    # Verification token
    verification_token = Column(String(255))
    verification_token_expires = Column(DateTime)

    # Password reset token
    reset_token = Column(String(255))
    reset_token_expires = Column(DateTime)

    # Account lockout (brute force protection)
    failed_login_attempts = Column(Integer, default=0)
    locked_until = Column(DateTime, nullable=True)

    # Two-Factor Authentication (TOTP)
    totp_secret = Column(String(32), nullable=True)  # Base32 encoded secret
    totp_enabled = Column(Boolean, default=False)
    totp_backup_codes = Column(StringArray, nullable=True)  # Emergency backup codes

    # Privacy settings
    privacy_show_phone = Column(Boolean, default=True)  # If FALSE, phone hidden from other users
    privacy_show_email = Column(Boolean, default=True)  # If FALSE, email hidden from other users

    # Contact preferences
    contact_prefer_email = Column(Boolean, default=True)  # User prefers email contact
    contact_prefer_phone = Column(Boolean, default=True)  # User prefers phone contact
    contact_prefer_portal = Column(Boolean, default=True)  # User prefers portal messages
    contact_note = Column(Text, nullable=True)  # Additional note (e.g. best hours)

    # Email notification preferences (per event type)
    notify_email_messages = Column(Boolean, default=True)              # Prywatna wiadomość
    notify_email_classified_question = Column(Boolean, default=True)   # Pytanie pod moim ogłoszeniem B2B
    notify_email_classified_answer = Column(Boolean, default=True)     # Odpowiedź pod moim pytaniem B2B
    notify_email_classified_expiry = Column(Boolean, default=True)     # Moje ogłoszenie wygasa za 3 dni
    notify_email_forum_reply = Column(Boolean, default=False)          # Odpowiedź w moim wątku forum (default OFF — forum subs duplikuje)
    notify_email_forum_quote = Column(Boolean, default=True)           # Cytat mojego wpisu forum
    notify_email_announcements = Column(Boolean, default=False)        # Nowa aktualność Izby (broadcast)
    notify_email_board_meetings = Column(Boolean, default=False)       # Posiedzenia Rady (broadcast)
    notify_email_event_invites = Column(Boolean, default=True)         # Nowe wydarzenie w kalendarzu
    notify_email_event_reminders = Column(Boolean, default=True)       # Przypomnienie 24h przed wydarzeniem

    # Web Push notification preferences (per event type)
    notify_push_messages = Column(Boolean, default=True)              # Prywatna wiadomość
    notify_push_classified_interest = Column(Boolean, default=True)   # Zainteresowanie ogłoszeniem B2B
    notify_push_classified_question = Column(Boolean, default=True)   # Pytanie pod ogłoszeniem B2B
    notify_push_forum_reply = Column(Boolean, default=True)           # Odpowiedź w moim wątku forum
    notify_push_forum_quote = Column(Boolean, default=True)           # Cytat mojego wpisu forum
    notify_push_announcements = Column(Boolean, default=True)         # Nowa aktualność Izby
    notify_push_board_meetings = Column(Boolean, default=True)        # Posiedzenia Rady (utw./program/protokół)
    notify_push_event_invites = Column(Boolean, default=True)         # Zaproszenie na wydarzenie
    notify_push_event_reminders = Column(Boolean, default=True)       # Przypomnienie 24h przed wydarzeniem

    # Relationships
    conversations = relationship('AIChatConversation', back_populates='user', cascade='all, delete-orphan')
    forum_topics = relationship('ForumTopic', back_populates='author', cascade='all, delete-orphan', primaryjoin='User.id == ForumTopic.author_id')
    forum_replies = relationship('ForumReply', back_populates='author', cascade='all, delete-orphan', primaryjoin='User.id == ForumReply.author_id')
    forum_subscriptions = relationship('ForumTopicSubscription', back_populates='user', cascade='all, delete-orphan')
    push_subscriptions = relationship('PushSubscription', back_populates='user', cascade='all, delete-orphan')

    # === ROLE SYSTEM HELPER METHODS ===

    @property
    def chamber_role_label(self):
        labels = {
            'prezes': 'Prezes Izby',
            'wiceprezes': 'Wiceprezes Izby',
            'czlonek_rady': 'Członek Rady Izby',
            'komisja_rewizyjna': 'Komisja Rewizyjna',
            'sad_kolezenski': 'Sąd Koleżeński',
        }
        return labels.get(self.chamber_role)

    @property
    def system_role(self) -> SystemRole:
        """Get the user's SystemRole enum value."""
        return SystemRole.from_string(self.role or 'UNAFFILIATED')

    @property
    def company_role_enum(self) -> CompanyRole:
        """Get the user's CompanyRole enum value."""
        return CompanyRole.from_string(self.company_role or 'NONE')

    def has_role(self, required_role: SystemRole) -> bool:
        """
        Check if user has at least the required role level.

        Args:
            required_role: Minimum required SystemRole

        Returns:
            True if user's role >= required_role

        Example:
            if user.has_role(SystemRole.OFFICE_MANAGER):
                # User is Office Manager or Admin
        """
        return self.system_role >= required_role

    def can_view_contacts(self) -> bool:
        """
        Check if user can view contact information (email, phone) of other members.
        Requires at least MEMBER role.
        """
        return self.has_role(SystemRole.MEMBER)

    def can_access_forum(self) -> bool:
        """
        Check if user can read and write on the forum.
        Requires at least MEMBER role.
        """
        return self.has_role(SystemRole.MEMBER)

    def can_access_chat(self) -> bool:
        """
        Check if user can use NordaGPT chat with full features.
        UNAFFILIATED users get limited access.
        """
        return self.has_role(SystemRole.MEMBER)

    def get_companies(self, session=None):
        """
        Get all companies associated with this user.

        Returns:
            List of UserCompany objects (uses relationship if loaded, else queries).
        """
        if self.company_associations:
            return self.company_associations
        if session:
            return session.query(UserCompany).filter_by(user_id=self.id).all()
        return []

    def get_company_role(self, company_id: int, session=None) -> 'CompanyRole':
        """
        Get user's role for a specific company from user_companies table.

        Args:
            company_id: The company to check.
            session: SQLAlchemy session (optional, uses relationship if loaded).

        Returns:
            CompanyRole enum value, or CompanyRole.NONE if not associated.
        """
        # Check loaded relationships first
        for assoc in (self.company_associations or []):
            if assoc.company_id == company_id:
                return assoc.role_enum
        # Fallback to query
        if session:
            assoc = session.query(UserCompany).filter_by(
                user_id=self.id, company_id=company_id
            ).first()
            if assoc:
                return assoc.role_enum
        # Legacy fallback: if company_id matches primary, use company_role
        if self.company_id == company_id:
            return self.company_role_enum
        return CompanyRole.NONE

    def can_edit_company(self, company_id: int = None) -> bool:
        """
        Check if user can edit a company's profile.

        Args:
            company_id: Company to check. If None, checks user's primary company.

        Returns:
            True if user can edit the company.
        """
        # Admins and Office Managers can edit any company
        if self.has_role(SystemRole.OFFICE_MANAGER):
            return True

        target_company = company_id or self.company_id
        if not target_company:
            return False

        # Check role via user_companies (supports multi-company)
        role = self.get_company_role(target_company)
        return role >= CompanyRole.EMPLOYEE

    def can_manage_company(self, company_id: int = None) -> bool:
        """
        Check if user can manage a company (including user management).

        Args:
            company_id: Company to check. If None, checks user's primary company.

        Returns:
            True if user has full management rights.
        """
        # Admins can manage any company
        if self.has_role(SystemRole.ADMIN):
            return True

        target_company = company_id or self.company_id
        if not target_company:
            return False

        # Check role via user_companies (supports multi-company)
        role = self.get_company_role(target_company)
        return role >= CompanyRole.MANAGER

    def can_manage_users(self) -> bool:
        """
        Check if user can manage all portal users.
        Only ADMIN role has this permission.
        """
        return self.has_role(SystemRole.ADMIN)

    def can_access_admin_panel(self) -> bool:
        """
        Check if user can access the admin panel.
        OFFICE_MANAGER and ADMIN roles have access.
        """
        return self.has_role(SystemRole.OFFICE_MANAGER)

    def can_moderate_forum(self) -> bool:
        """
        Check if user can moderate forum content.
        OFFICE_MANAGER and ADMIN roles have this permission.
        """
        return self.has_role(SystemRole.OFFICE_MANAGER)

    def has_delegated_permission(self, permission: str, company_id: int = None, session=None) -> bool:
        """
        Check if user has a specific delegated permission for a company.

        This checks UserCompanyPermissions table for fine-grained permissions
        granted by a MANAGER.

        Args:
            permission: One of: 'edit_description', 'edit_services', 'edit_contacts',
                       'edit_social', 'manage_classifieds', 'post_forum', 'view_analytics'
            company_id: Company to check. If None, checks user's primary company.
            session: SQLAlchemy session (optional, uses relationship if available)

        Returns:
            True if user has this specific permission
        """
        target_company = company_id or self.company_id

        # Managers have all permissions by default
        role = self.get_company_role(target_company) if target_company else self.company_role_enum
        if role >= CompanyRole.MANAGER:
            return True

        # Check delegated permissions
        if self.company_permissions:
            for perm in self.company_permissions:
                if perm.company_id == target_company:
                    attr_name = f'can_{permission}'
                    return getattr(perm, attr_name, False)

        return False

    def can_edit_company_field(self, field_category: str, company_id: int = None) -> bool:
        """
        Check if user can edit a specific category of company fields.

        Args:
            field_category: 'description', 'services', 'contacts', or 'social'
            company_id: Company to check. If None, checks user's primary company.

        Returns:
            True if user can edit fields in this category
        """
        # Admins and Office Managers can edit everything
        if self.has_role(SystemRole.OFFICE_MANAGER):
            return True

        target_company = company_id or self.company_id
        if not target_company:
            return False

        # Check role via user_companies (supports multi-company)
        role = self.get_company_role(target_company)

        # Managers can edit everything in their company
        if role >= CompanyRole.MANAGER:
            return True

        # Employees need delegated permission
        if role >= CompanyRole.EMPLOYEE:
            return self.has_delegated_permission(f'edit_{field_category}', company_id=target_company)

        return False

    def set_role(self, new_role: SystemRole, sync_is_admin: bool = True):
        """
        Set the user's system role.

        Args:
            new_role: The new SystemRole to assign
            sync_is_admin: If True, also update is_admin field for backward compatibility
        """
        self.role = new_role.name
        if sync_is_admin:
            self.is_admin = (new_role >= SystemRole.ADMIN)

    def set_company_role(self, new_role: CompanyRole):
        """Set the user's company role."""
        self.company_role = new_role.name

    def __repr__(self):
        return f'<User {self.email} role={self.role}>'


class PushSubscription(Base):
    """Web Push subscription per user device (desktop, mobile browser, PWA iOS).

    Jeden user może mieć wiele subskrypcji — po jednej na każde urządzenie/przeglądarkę.
    Endpoint unique — powtórny subscribe z tej samej przeglądarki aktualizuje istniejący rekord.
    """
    __tablename__ = 'push_subscriptions'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True)
    endpoint = Column(Text, nullable=False, unique=True)
    p256dh = Column(String(255), nullable=False)
    auth = Column(String(255), nullable=False)
    user_agent = Column(String(500))
    created_at = Column(DateTime, default=datetime.now)
    last_used_at = Column(DateTime)

    user = relationship('User', back_populates='push_subscriptions')

    def __repr__(self):
        return f'<PushSubscription user={self.user_id} id={self.id}>'


class UserCompanyPermissions(Base):
    """
    Delegated permissions for company employees.

    Allows MANAGER to grant specific permissions to EMPLOYEE users,
    enabling fine-grained control over what each employee can do.

    Example:
        - Jan Kowalski (EMPLOYEE) gets permission to edit social media
        - Anna Nowak (EMPLOYEE) gets permission to manage B2B classifieds
    """
    __tablename__ = 'user_company_permissions'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)

    # Content editing permissions
    can_edit_description = Column(Boolean, default=True)  # Opis firmy, historia, wartości
    can_edit_services = Column(Boolean, default=True)     # Usługi, kompetencje, technologie
    can_edit_contacts = Column(Boolean, default=False)    # Email, telefon, adres
    can_edit_social = Column(Boolean, default=False)      # Social media, strona www

    # Feature permissions
    can_manage_classifieds = Column(Boolean, default=True)  # B2B ogłoszenia w imieniu firmy
    can_post_forum = Column(Boolean, default=True)          # Posty na forum w imieniu firmy
    can_view_analytics = Column(Boolean, default=False)     # Statystyki firmy, wyświetlenia

    # Granted by (for audit trail)
    granted_by_id = Column(Integer, ForeignKey('users.id'), nullable=True)
    granted_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    user = relationship('User', foreign_keys=[user_id], backref='company_permissions')
    company = relationship('Company', backref='user_permissions')
    granted_by = relationship('User', foreign_keys=[granted_by_id])

    __table_args__ = (
        UniqueConstraint('user_id', 'company_id', name='uq_user_company_permissions'),
    )

    def __repr__(self):
        return f'<UserCompanyPermissions user={self.user_id} company={self.company_id}>'

    @classmethod
    def get_or_create(cls, session, user_id: int, company_id: int) -> 'UserCompanyPermissions':
        """Get existing permissions or create default ones.

        MANAGER+ roles get full permissions automatically.
        EMPLOYEE gets restricted defaults (no contacts/social/analytics).
        """
        perms = session.query(cls).filter_by(
            user_id=user_id,
            company_id=company_id
        ).first()

        if not perms:
            # Check if user has MANAGER+ role → grant full permissions
            user = session.query(User).filter_by(id=user_id).first()
            is_manager = False
            if user:
                role = user.get_company_role(company_id) if company_id else user.company_role_enum
                is_manager = role >= CompanyRole.MANAGER

            perms = cls(
                user_id=user_id,
                company_id=company_id,
                can_edit_contacts=is_manager,
                can_edit_social=is_manager,
                can_view_analytics=is_manager,
            )
            session.add(perms)
            session.flush()

        return perms


class UserCompany(Base):
    """
    Association between users and companies (multi-company support).

    Allows a user to be linked to multiple companies with different roles.
    One association per user can be marked as is_primary, which syncs
    to users.company_id via database trigger.
    """
    __tablename__ = 'user_companies'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    role = Column(String(20), nullable=False, default='MANAGER')
    is_primary = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    user = relationship('User', backref='company_associations')
    company = relationship('Company', backref='user_associations')

    __table_args__ = (
        UniqueConstraint('user_id', 'company_id', name='uq_user_company'),
    )

    @property
    def role_enum(self) -> CompanyRole:
        """Get the CompanyRole enum value."""
        return CompanyRole.from_string(self.role or 'NONE')

    def __repr__(self):
        return f'<UserCompany user={self.user_id} company={self.company_id} role={self.role} primary={self.is_primary}>'


# ============================================================
# COMPANY DIRECTORY (existing schema from SQL)
# ============================================================

class Category(Base):
    """Company categories with hierarchical structure"""
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False, unique=True)
    slug = Column(String(100), nullable=False, unique=True)
    description = Column(Text)
    icon = Column(String(50))
    sort_order = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.now)

    # Hierarchical structure
    parent_id = Column(Integer, ForeignKey('categories.id'), nullable=True)
    display_order = Column(Integer, default=0)

    # Relationships
    companies = relationship('Company', back_populates='category')
    parent = relationship('Category', remote_side=[id], backref='subcategories')

    @property
    def is_main_category(self):
        """Check if this is a main (parent) category"""
        return self.parent_id is None


class Company(Base):
    """Companies"""
    __tablename__ = 'companies'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    legal_name = Column(String(255))
    slug = Column(String(255), nullable=False, unique=True, index=True)
    category_id = Column(Integer, ForeignKey('categories.id'))

    # Descriptions
    description_short = Column(Text)
    description_full = Column(Text)

    # Legal
    nip = Column(String(10), unique=True)
    regon = Column(String(14))
    krs = Column(String(10))

    # Parent company relationship (for brands/divisions of the same legal entity)
    parent_company_id = Column(Integer, ForeignKey('companies.id'), nullable=True)

    # External registry slugs
    aleo_slug = Column(String(255))  # ALEO.com company slug for direct links

    # Contact
    website = Column(String(500))
    email = Column(String(255))
    phone = Column(String(50))

    # Address
    address_street = Column(String(255))
    address_city = Column(String(100))
    address_postal = Column(String(10))
    address_full = Column(Text)

    # Business data
    year_established = Column(Integer)
    employees_count = Column(Integer)
    capital_amount = Column(Numeric(15, 2))

    # Status (PostgreSQL uses ENUM types, no default here)
    status = Column(String(20))
    data_quality = Column(String(20))

    # Membership status: active, resigned, suspended, exempt
    membership_status = Column(String(20), default='active')
    resignation_date = Column(Date)
    fee_included_in_parent = Column(Boolean, default=False)

    # Extended company info
    legal_form = Column(String(100))
    parent_organization = Column(String(255))
    industry_sector = Column(String(255))
    services_offered = Column(Text)
    operational_area = Column(String(500))
    languages_offered = Column(String(200))
    technologies_used = Column(Text)
    founding_history = Column(Text)  # Historia firmy + właściciele
    core_values = Column(Text)  # Wartości firmy
    branch_count = Column(Integer)
    employee_count_range = Column(String(50))

    # === CEIDG DATA (API dane.biznes.gov.pl) ===
    # Podstawowe dane CEIDG
    ceidg_id = Column(String(50))  # GUID firmy w CEIDG
    ceidg_status = Column(String(50))  # AKTYWNY, ZAWIESZONY, WYKREŚLONY

    # PKD (kod działalności gospodarczej) - z CEIDG
    pkd_code = Column(String(10))  # np. "6201Z" (główny PKD)
    pkd_description = Column(Text)  # np. "Działalność związana z oprogramowaniem"
    ceidg_pkd_list = Column(PG_JSONB, default=[])  # Wszystkie PKD z CEIDG jako [{kod, nazwa}]

    # Data rozpoczęcia działalności - z CEIDG
    business_start_date = Column(Date)  # np. 2021-02-10

    # Właściciel JDG - z CEIDG (tylko dla jednoosobowych działalności)
    owner_first_name = Column(String(100))
    owner_last_name = Column(String(100))
    owner_citizenships = Column(PG_JSONB, default=[])  # [{symbol, kraj}]

    # Adres korespondencyjny (z CEIDG)
    correspondence_street = Column(String(255))
    correspondence_city = Column(String(100))
    correspondence_postal = Column(String(10))

    # Surowe dane z CEIDG API
    ceidg_raw_data = Column(PG_JSONB)
    ceidg_fetched_at = Column(DateTime)

    # === KRS DATA (API prs.ms.gov.pl) ===
    krs_raw_data = Column(PG_JSONB)  # Pełna odpowiedź z KRS API
    krs_fetched_at = Column(DateTime)
    krs_registration_date = Column(Date)  # Data rejestracji w KRS
    krs_representation = Column(Text)  # Sposób reprezentacji spółki
    krs_activities = Column(PG_JSONB, default=[])  # Przedmiot działalności

    # Admin notes (internal, not visible to users)
    admin_notes = Column(Text)

    # Logo display preference
    logo_dark_bg = Column(Boolean, default=False)  # True = show logo on dark background

    # Data source tracking
    data_source = Column(String(100))
    data_quality_score = Column(Integer)
    last_verified_at = Column(DateTime)
    norda_biznes_url = Column(String(500))
    norda_biznes_member_id = Column(String(50))
    member_since = Column(Date)  # Data przystąpienia do Izby NORDA
    admitted_at_meeting_id = Column(Integer, ForeignKey('board_meetings.id'), nullable=True)
    previous_years_debt = Column(Numeric(10, 2), default=0)  # Zaległości z lat poprzednich (ręcznie wpisane)

    # Metadata
    last_updated = Column(DateTime, default=datetime.now)
    created_at = Column(DateTime, default=datetime.now)

    # === DIGITAL MATURITY (added 2025-11-26) ===
    digital_maturity_last_assessed = Column(DateTime)
    digital_maturity_score = Column(Integer)  # 0-100 composite score
    digital_maturity_rank_category = Column(Integer)
    digital_maturity_rank_overall = Column(Integer)

    # AI Readiness
    ai_enabled = Column(Boolean, default=False)
    ai_tools_used = Column(ARRAY(String))  # PostgreSQL array (will be Text for SQLite)
    data_structured = Column(Boolean, default=False)

    # IT Management
    it_manager_exists = Column(Boolean, default=False)
    it_outsourced = Column(Boolean, default=False)
    it_provider_company_id = Column(Integer, ForeignKey('companies.id'))

    # Website tracking
    website_last_analyzed = Column(DateTime)
    website_status = Column(String(20))  # 'active', 'broken', 'no_website'
    website_quality_score = Column(Integer)  # 0-100

    # === KRS DATA (added 2026-01-13) ===
    krs_registration_date = Column(Date)      # Data wpisu do KRS
    krs_company_agreement_date = Column(Date) # Data umowy spółki
    krs_duration = Column(String(100))        # Czas trwania (NIEOZNACZONY lub data)
    krs_representation_rules = Column(Text)   # Sposób reprezentacji
    capital_currency = Column(String(3), default='PLN')
    capital_shares_count = Column(Integer)    # Liczba udziałów
    capital_share_value = Column(Numeric(15, 2))  # Wartość nominalna udziału
    is_opp = Column(Boolean, default=False)   # Czy OPP
    krs_last_audit_at = Column(DateTime)      # Data ostatniego audytu KRS
    krs_pdf_path = Column(Text)               # Ścieżka do pliku PDF

    # Board meeting where company was admitted
    admitted_at_meeting = relationship('BoardMeeting', foreign_keys=[admitted_at_meeting_id])

    # Relationships
    category = relationship('Category', back_populates='companies')
    services = relationship('CompanyService', back_populates='company', cascade='all, delete-orphan')
    competencies = relationship('CompanyCompetency', back_populates='company', cascade='all, delete-orphan')
    certifications = relationship('Certification', back_populates='company', cascade='all, delete-orphan')
    awards = relationship('Award', back_populates='company', cascade='all, delete-orphan')
    events = relationship('CompanyEvent', back_populates='company', cascade='all, delete-orphan')

    # Digital Maturity relationships
    digital_maturity = relationship('CompanyDigitalMaturity', back_populates='company', uselist=False)
    website_analyses = relationship('CompanyWebsiteAnalysis', back_populates='company', cascade='all, delete-orphan')
    maturity_history = relationship('MaturityAssessment', back_populates='company', cascade='all, delete-orphan')

    # Quality tracking
    quality_tracking = relationship('CompanyQualityTracking', back_populates='company', uselist=False)

    # Website scraping and AI analysis
    website_content = relationship('CompanyWebsiteContent', back_populates='company', cascade='all, delete-orphan')
    ai_insights = relationship('CompanyAIInsights', back_populates='company', uselist=False)
    ai_enrichment_proposals = relationship('AiEnrichmentProposal', back_populates='company', cascade='all, delete-orphan')

    # Multiple websites
    websites = relationship('CompanyWebsite', back_populates='company',
                            cascade='all, delete-orphan',
                            order_by='CompanyWebsite.is_primary.desc()')

    # Profile section visibility
    hidden_sections = Column(PG_JSONB, nullable=False, default=list, server_default='[]')

    # Company creation wizard
    wizard_step = Column(Integer, nullable=True)
    wizard_started_by = Column(Integer, nullable=True)
    wizard_audit_status = Column(PG_JSONB, nullable=True)

    def is_section_hidden(self, section_key):
        """Check if a profile section is hidden from visitors."""
        return section_key in (self.hidden_sections or [])


class Service(Base):
    """Services offered by companies"""
    __tablename__ = 'services'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False, unique=True)
    slug = Column(String(255), nullable=False, unique=True)
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.now)

    companies = relationship('CompanyService', back_populates='service')


class CompanyService(Base):
    """Many-to-many: Companies <-> Services"""
    __tablename__ = 'company_services'

    company_id = Column(Integer, ForeignKey('companies.id'), primary_key=True)
    service_id = Column(Integer, ForeignKey('services.id'), primary_key=True)
    is_primary = Column(Boolean, default=False)
    added_at = Column(DateTime, default=datetime.now)

    company = relationship('Company', back_populates='services')
    service = relationship('Service', back_populates='companies')


class Competency(Base):
    """Competencies/skills of companies"""
    __tablename__ = 'competencies'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False, unique=True)
    slug = Column(String(255), nullable=False, unique=True)
    category = Column(String(100))
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.now)

    companies = relationship('CompanyCompetency', back_populates='competency')


class CompanyCompetency(Base):
    """Many-to-many: Companies <-> Competencies"""
    __tablename__ = 'company_competencies'

    company_id = Column(Integer, ForeignKey('companies.id'), primary_key=True)
    competency_id = Column(Integer, ForeignKey('competencies.id'), primary_key=True)
    level = Column(String(50))
    added_at = Column(DateTime, default=datetime.now)

    company = relationship('Company', back_populates='competencies')
    competency = relationship('Competency', back_populates='companies')


class Certification(Base):
    """Company certifications"""
    __tablename__ = 'certifications'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'))
    name = Column(String(255), nullable=False)
    issuer = Column(String(255))
    certificate_number = Column(String(100))
    issue_date = Column(Date)
    expiry_date = Column(Date)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)

    company = relationship('Company', back_populates='certifications')


class Award(Base):
    """Company awards and achievements"""
    __tablename__ = 'awards'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'))
    name = Column(String(255), nullable=False)
    issuer = Column(String(255))
    year = Column(Integer)
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.now)

    company = relationship('Company', back_populates='awards')


class CompanyEvent(Base):
    """Company events and news"""
    __tablename__ = 'company_events'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'))
    event_type = Column(String(50), nullable=False)
    title = Column(String(500), nullable=False)
    description = Column(Text)
    event_date = Column(Date)
    source_url = Column(String(1000))
    created_at = Column(DateTime, default=datetime.now)

    company = relationship('Company', back_populates='events')


# ============================================================
# DIGITAL MATURITY ASSESSMENT PLATFORM
# ============================================================

class CompanyDigitalMaturity(Base):
    """Central dashboard for company digital maturity - composite scores and benchmarking"""
    __tablename__ = 'company_digital_maturity'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, unique=True, index=True)
    last_updated = Column(DateTime, default=datetime.now)

    # === COMPOSITE SCORES (0-100 each) ===
    overall_score = Column(Integer)
    online_presence_score = Column(Integer)
    social_media_score = Column(Integer)
    it_infrastructure_score = Column(Integer)
    business_applications_score = Column(Integer)
    backup_disaster_recovery_score = Column(Integer)
    cybersecurity_score = Column(Integer)
    ai_readiness_score = Column(Integer)
    digital_marketing_score = Column(Integer)

    # === GAPS & OPPORTUNITIES ===
    critical_gaps = Column(ARRAY(String))  # ['no_backup', 'no_firewall', etc.]
    improvement_priority = Column(String(20))  # 'critical', 'high', 'medium', 'low'
    estimated_investment_needed = Column(Numeric(10, 2))  # PLN

    # === BENCHMARKING ===
    rank_in_category = Column(Integer)  # position in category
    rank_overall = Column(Integer)  # overall position
    percentile = Column(Integer)  # top X% of companies

    # === SALES INTELLIGENCE ===
    total_opportunity_value = Column(Numeric(10, 2))  # potential sales value (PLN)
    sales_readiness = Column(String(20))  # 'hot', 'warm', 'cold', 'not_ready'

    # Relationship
    company = relationship('Company', back_populates='digital_maturity')


class CompanyWebsiteAnalysis(Base):
    """Detailed website and online presence analysis"""
    __tablename__ = 'company_website_analysis'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, index=True)
    company_website_id = Column(Integer, ForeignKey('company_websites.id', ondelete='SET NULL'), nullable=True, index=True)
    analyzed_at = Column(DateTime, default=datetime.now, index=True)

    # === BASIC INFO ===
    website_url = Column(String(500))
    final_url = Column(String(500))  # After redirects
    http_status_code = Column(Integer)
    load_time_ms = Column(Integer)

    # === TECHNICAL ===
    has_ssl = Column(Boolean, default=False)
    ssl_expires_at = Column(Date)
    ssl_issuer = Column(String(100))  # Certificate Authority (Let's Encrypt, DigiCert, etc.)
    is_responsive = Column(Boolean, default=False)  # mobile-friendly
    cms_detected = Column(String(100))
    frameworks_detected = Column(ARRAY(String))  # ['WordPress', 'Bootstrap', etc.]

    # === HOSTING & SERVER (from audit) ===
    last_modified_at = Column(DateTime)
    hosting_provider = Column(String(100))
    hosting_ip = Column(String(45))
    server_software = Column(String(100))
    site_author = Column(String(255))  # Website creator/agency
    site_generator = Column(String(100))
    domain_registrar = Column(String(100))
    is_mobile_friendly = Column(Boolean, default=False)
    has_viewport_meta = Column(Boolean, default=False)

    # === GOOGLE BUSINESS (from audit) ===
    google_rating = Column(Numeric(2, 1))
    google_reviews_count = Column(Integer)
    google_place_id = Column(String(100))
    google_business_status = Column(String(50))
    google_opening_hours = Column(JSONB)  # Opening hours from GBP
    google_photos_count = Column(Integer)  # Number of photos on GBP
    google_name = Column(String(255))  # Business name from Google
    google_address = Column(String(500))  # Formatted address from Google
    google_phone = Column(String(50))  # Phone from Google
    google_website = Column(String(500))  # Website from Google
    google_types = Column(ARRAY(Text))  # Business types/categories
    google_maps_url = Column(String(500))  # Google Maps URL

    # === AUDIT METADATA ===
    audit_source = Column(String(50), default='automated')
    audit_version = Column(String(20), default='1.0')
    audit_errors = Column(Text)

    # === CONTENT RICHNESS ===
    content_richness_score = Column(Integer)  # 1-10
    page_count_estimate = Column(Integer)
    word_count_homepage = Column(Integer)
    has_blog = Column(Boolean, default=False)
    has_portfolio = Column(Boolean, default=False)
    has_contact_form = Column(Boolean, default=False)
    has_live_chat = Column(Boolean, default=False)

    # === EXTRACTED CONTENT ===
    content_summary = Column(Text)  # AI-generated summary from website
    services_extracted = Column(ARRAY(String))  # Services mentioned on website
    main_keywords = Column(ARRAY(String))  # Top keywords

    # === SEO ===
    seo_title = Column(String(500))
    seo_description = Column(Text)
    has_sitemap = Column(Boolean, default=False)
    has_robots_txt = Column(Boolean, default=False)
    google_indexed_pages = Column(Integer)

    # === PAGESPEED INSIGHTS SCORES (0-100) ===
    pagespeed_seo_score = Column(Integer)  # Google PageSpeed SEO score 0-100
    pagespeed_performance_score = Column(Integer)  # Google PageSpeed Performance score 0-100
    pagespeed_accessibility_score = Column(Integer)  # Google PageSpeed Accessibility score 0-100
    pagespeed_best_practices_score = Column(Integer)  # Google PageSpeed Best Practices score 0-100
    pagespeed_audits = Column(JSONB)  # Full PageSpeed audit results as JSON

    # === ON-PAGE SEO DETAILS ===
    meta_title = Column(String(500))  # Full meta title from <title> tag
    meta_description = Column(Text)  # Full meta description from <meta name="description">
    meta_keywords = Column(Text)  # Meta keywords (legacy, rarely used)

    # Heading structure
    h1_count = Column(Integer)  # Number of H1 tags on homepage (should be 1)
    h2_count = Column(Integer)  # Number of H2 tags on homepage
    h3_count = Column(Integer)  # Number of H3 tags on homepage
    h1_text = Column(String(500))  # Text content of first H1 tag

    # Image analysis
    total_images = Column(Integer)  # Total number of images
    images_without_alt = Column(Integer)  # Images missing alt attribute - accessibility issue
    images_with_alt = Column(Integer)  # Images with proper alt text

    # Link analysis
    internal_links_count = Column(Integer)  # Links to same domain
    external_links_count = Column(Integer)  # Links to external domains
    broken_links_count = Column(Integer)  # Links returning 4xx/5xx

    # Structured data (Schema.org, JSON-LD, Microdata)
    has_structured_data = Column(Boolean, default=False)  # Whether page contains JSON-LD, Microdata, or RDFa
    structured_data_types = Column(ARRAY(String))  # Schema.org types found: Organization, LocalBusiness, etc.
    structured_data_json = Column(JSONB)  # Full structured data as JSON

    # === TECHNICAL SEO ===
    # Canonical URL handling
    has_canonical = Column(Boolean, default=False)  # Whether page has canonical URL defined
    canonical_url = Column(String(500))  # The canonical URL value

    # Indexability
    is_indexable = Column(Boolean, default=True)  # Whether page can be indexed (no noindex directive)
    noindex_reason = Column(String(200))  # Reason if page is not indexable: meta tag, robots.txt, etc.

    # Core Web Vitals
    viewport_configured = Column(Boolean)  # Whether viewport meta tag is properly configured
    largest_contentful_paint_ms = Column(Integer)  # Core Web Vital: LCP in milliseconds
    interaction_to_next_paint_ms = Column(Integer)  # Core Web Vital: INP in milliseconds (replaced FID March 2024)
    cumulative_layout_shift = Column(Numeric(5, 3))  # Core Web Vital: CLS score

    # Open Graph & Social Meta
    has_og_tags = Column(Boolean, default=False)  # Whether page has Open Graph tags
    og_title = Column(String(500))  # Open Graph title
    og_description = Column(Text)  # Open Graph description
    og_image = Column(String(500))  # Open Graph image URL
    has_twitter_cards = Column(Boolean, default=False)  # Whether page has Twitter Card meta tags

    # Language & International
    html_lang = Column(String(10))  # Language attribute from <html lang="...">
    has_hreflang = Column(Boolean, default=False)  # Whether page has hreflang tags

    # === CrUX FIELD DATA (Chrome User Experience Report) ===
    crux_lcp_ms = Column(Integer)  # Real user LCP p75 in milliseconds
    crux_inp_ms = Column(Integer)  # Real user INP p75 in milliseconds
    crux_cls = Column(Numeric(6, 4))  # Real user CLS p75
    crux_fcp_ms = Column(Integer)  # Real user FCP p75 in milliseconds
    crux_ttfb_ms = Column(Integer)  # Real user TTFB p75 in milliseconds
    crux_lcp_good_pct = Column(Numeric(5, 2))  # % of users with good LCP
    crux_inp_good_pct = Column(Numeric(5, 2))  # % of users with good INP
    crux_period_end = Column(Date)  # End date of CrUX data collection period

    # === SECURITY HEADERS ===
    has_hsts = Column(Boolean)  # Strict-Transport-Security header present
    has_csp = Column(Boolean)  # Content-Security-Policy header present
    has_x_frame_options = Column(Boolean)  # X-Frame-Options header present
    has_x_content_type_options = Column(Boolean)  # X-Content-Type-Options header present
    security_headers_count = Column(SmallInteger)  # Count of security headers (0-4)

    # === IMAGE FORMAT ANALYSIS ===
    modern_image_count = Column(Integer)  # WebP + AVIF + SVG images count
    legacy_image_count = Column(Integer)  # JPG + PNG + GIF images count
    modern_image_ratio = Column(Numeric(5, 2))  # % of images in modern formats

    # === GOOGLE PLACES API ENRICHMENT ===
    google_primary_type = Column(String(100))  # Places API: primary business type
    google_editorial_summary = Column(Text)  # Places API: Google's curated description
    google_price_level = Column(String(50))  # Places API: PRICE_LEVEL_* enum
    google_attributes = Column(JSONB)  # Places API: business attributes
    google_reviews_data = Column(JSONB)  # Places API: reviews with text/rating
    google_photos_metadata = Column(JSONB)  # Places API: photo references
    google_maps_links = Column(JSONB)  # Places API: directionsUri, writeAReviewUri, etc.
    google_open_now = Column(Boolean)  # Whether business is currently open (at audit time)

    # === GOOGLE SEARCH CONSOLE (OAuth) ===
    gsc_clicks = Column(Integer)  # Total clicks from Google Search in period
    gsc_impressions = Column(Integer)  # Total impressions in Google Search in period
    gsc_ctr = Column(Numeric(5, 2))  # Click-through rate as percentage
    gsc_avg_position = Column(Numeric(5, 1))  # Average position in search results
    gsc_top_queries = Column(JSONB)  # Top search queries with clicks/impressions
    gsc_period_days = Column(Integer, default=28)  # Data collection period in days

    # === GBP PERFORMANCE API ===
    gbp_impressions_maps = Column(Integer)  # Maps views (desktop + mobile) in period
    gbp_impressions_search = Column(Integer)  # Search views (desktop + mobile) in period
    gbp_call_clicks = Column(Integer)  # Phone call clicks in period
    gbp_website_clicks = Column(Integer)  # Website clicks in period
    gbp_direction_requests = Column(Integer)  # Direction requests in period
    gbp_conversations = Column(Integer)  # Conversations started in period
    gbp_search_keywords = Column(JSONB)  # Top search keywords with impression counts
    gbp_performance_period_days = Column(Integer, default=30)  # Performance data period

    # === GOOGLE SEARCH CONSOLE EXTENSIONS ===
    gsc_top_pages = Column(JSONB)  # Top pages with clicks/impressions
    gsc_device_breakdown = Column(JSONB)  # {desktop: {clicks, impressions}, mobile: {...}, tablet: {...}}
    gsc_index_status = Column(String(50))  # URL Inspection: PASS, NEUTRAL, FAIL, etc.
    gsc_last_crawl = Column(DateTime)  # URL Inspection: last crawl timestamp
    gsc_crawled_as = Column(String(50))  # URL Inspection: Googlebot type
    gsc_sitemaps = Column(JSONB)  # Sitemaps list with status/errors
    gsc_country_breakdown = Column(JSONB)  # Top countries with clicks/impressions
    gsc_search_type_breakdown = Column(JSONB)  # Web/image/video breakdown
    gsc_trend_data = Column(JSONB)  # Period-over-period comparison

    # === GBP OWNER DATA (Management API) ===
    google_owner_responses_count = Column(Integer)  # Number of owner responses to reviews
    google_review_response_rate = Column(Numeric(5, 1))  # % of reviews with owner response
    google_posts_data = Column(JSONB)  # Recent Google Posts data
    google_posts_count = Column(Integer)  # Total number of Google Posts

    # === SEO AUDIT METADATA ===
    seo_audit_version = Column(String(20))  # Version of SEO audit script used
    seo_audited_at = Column(DateTime)  # Timestamp of last SEO audit
    seo_audit_errors = Column(ARRAY(String))  # Errors encountered during SEO audit
    seo_overall_score = Column(Integer)  # Calculated overall SEO score 0-100
    seo_health_score = Column(Integer)  # On-page SEO health score 0-100
    seo_issues = Column(JSONB)  # List of SEO issues found with severity levels

    # === LOCAL SEO ===
    local_seo_score = Column(Integer)  # 0-100
    has_local_business_schema = Column(Boolean)
    local_business_schema_fields = Column(JSONB)
    nap_on_website = Column(JSONB)  # {"name": "...", "address": "...", "phone": "..."}
    has_google_maps_embed = Column(Boolean)
    has_local_keywords = Column(Boolean)
    local_keywords_found = Column(JSONB)

    # === CITATIONS ===
    citations_found = Column(JSONB)
    citations_count = Column(Integer, default=0)

    # === CONTENT FRESHNESS ===
    content_freshness_score = Column(Integer)  # 0-100
    last_content_update = Column(DateTime)

    # === SCORE HISTORY ===
    score_history = Column(JSONB)  # [{"date": "2026-02-01", "score": 72}]

    # === DOMAIN ===
    domain_registered_at = Column(Date)
    domain_expires_at = Column(Date)
    domain_age_years = Column(Integer)

    # === ANALYTICS ===
    has_google_analytics = Column(Boolean, default=False)
    has_google_tag_manager = Column(Boolean, default=False)
    has_facebook_pixel = Column(Boolean, default=False)

    # === OPPORTUNITY SCORING ===
    needs_redesign = Column(Boolean, default=False)
    missing_features = Column(ARRAY(String))  # ['blog', 'portfolio', 'ssl', etc.]
    opportunity_score = Column(Integer)  # 0-100
    estimated_project_value = Column(Numeric(10, 2))  # PLN
    opportunity_notes = Column(Text)

    # Relationship
    company = relationship('Company', back_populates='website_analyses')


class CompanyCitation(Base):
    """Tracks company presence in local business directories"""
    __tablename__ = 'company_citations'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    directory_name = Column(String(100), nullable=False)
    directory_url = Column(String(500))
    listing_url = Column(String(500))
    status = Column(String(20), default='unknown')  # found, not_found, incorrect
    nap_accurate = Column(Boolean)
    details = Column(JSONB)
    checked_at = Column(DateTime, default=datetime.now)
    created_at = Column(DateTime, default=datetime.now)

    # Relationship
    company = relationship('Company', backref='citations')

    __table_args__ = (
        UniqueConstraint('company_id', 'directory_name', name='uq_company_directory'),
    )


class CompanyQualityTracking(Base):
    """Quality tracking for company data - verification counter and quality score"""
    __tablename__ = 'company_quality_tracking'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, unique=True, index=True)
    verification_count = Column(Integer, default=0)
    last_verified_at = Column(DateTime)
    verified_by = Column(String(100))
    verification_notes = Column(Text)
    quality_score = Column(Integer)  # 0-100%
    issues_found = Column(Integer, default=0)
    issues_fixed = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationship
    company = relationship('Company', back_populates='quality_tracking')


class BulkEnrichmentJob(Base):
    """Tracks bulk enrichment jobs started from admin dashboard."""
    __tablename__ = 'bulk_enrichment_jobs'

    id = Column(Integer, primary_key=True)
    started_at = Column(DateTime, default=datetime.now)
    started_by = Column(Integer, ForeignKey('users.id'))
    status = Column(String(20), default='running')  # running, completed, failed
    total_companies = Column(Integer, default=0)
    processed_companies = Column(Integer, default=0)
    steps = Column(PG_JSONB)  # ['registry', 'seo', 'social', 'gbp', 'logo']
    results = Column(PG_JSONB, default={})  # {company_id: {step: result, ...}, ...}
    completed_at = Column(DateTime)

    # Relationship
    user = relationship('User')


class CompanyWebsiteContent(Base):
    """Scraped website content for companies"""
    __tablename__ = 'company_website_content'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, index=True)
    scraped_at = Column(DateTime, default=datetime.utcnow)
    url = Column(String(500))
    http_status = Column(Integer)
    raw_html = Column(Text)
    raw_text = Column(Text)
    page_title = Column(String(500))
    meta_description = Column(Text)
    main_content = Column(Text)
    email_addresses = Column(ARRAY(String))
    phone_numbers = Column(ARRAY(String))
    social_media = Column(JSONB)
    word_count = Column(Integer)

    # Relationship
    company = relationship('Company', back_populates='website_content')


class CompanyAIInsights(Base):
    """AI-generated insights from website analysis"""
    __tablename__ = 'company_ai_insights'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, unique=True, index=True)
    content_id = Column(Integer, ForeignKey('company_website_content.id'))
    business_summary = Column(Text)
    services_list = Column(ARRAY(String))
    target_market = Column(Text)
    unique_selling_points = Column(ARRAY(String))
    company_values = Column(ARRAY(String))
    certifications = Column(ARRAY(String))
    suggested_category = Column(String(100))
    category_confidence = Column(Numeric(3, 2))
    industry_tags = Column(ARRAY(String))
    ai_confidence_score = Column(Numeric(3, 2))
    processing_time_ms = Column(Integer)
    analyzed_at = Column(DateTime, default=datetime.utcnow)

    # Relationship
    company = relationship('Company', back_populates='ai_insights')


class AiEnrichmentProposal(Base):
    """
    Propozycje wzbogacenia danych przez AI - wymagają akceptacji właściciela/admina.

    Workflow:
    1. AI analizuje dane ze strony WWW
    2. Tworzy propozycję (status: pending)
    3. Właściciel/admin przegląda i akceptuje lub odrzuca
    4. Po akceptacji dane są dodawane do profilu firmy
    """
    __tablename__ = 'ai_enrichment_proposals'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, index=True)

    # Status: pending, approved, rejected, expired
    status = Column(String(20), default='pending', nullable=False, index=True)

    # Typ propozycji (website_extraction, krs_update, manual_suggestion)
    proposal_type = Column(String(50), default='website_extraction', nullable=False)

    # Źródło danych (URL strony, API, itp.)
    data_source = Column(String(500))

    # Proponowane dane jako JSON
    proposed_data = Column(JSONB, nullable=False)
    # Struktura proposed_data:
    # {
    #   "services": ["usługa 1", "usługa 2"],
    #   "products": ["produkt 1"],
    #   "keywords": ["keyword 1"],
    #   "specializations": ["spec 1"],
    #   "brands": ["marka 1"],
    #   "target_customers": ["klient 1"],
    #   "regions": ["region 1"],
    #   "summary": "Opis firmy..."
    # }

    # Komentarz AI wyjaśniający propozycję
    ai_explanation = Column(Text)

    # Wskaźnik pewności AI (0.0 - 1.0)
    confidence_score = Column(Numeric(3, 2))

    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    reviewed_at = Column(DateTime)
    applied_at = Column(DateTime)
    expires_at = Column(DateTime)  # Propozycje wygasają po X dniach

    # Kto przeglądał
    reviewed_by_id = Column(Integer, ForeignKey('users.id'))

    # Komentarz przy akceptacji/odrzuceniu
    review_comment = Column(Text)

    # Które pola zostały zaakceptowane (jeśli częściowa akceptacja)
    approved_fields = Column(JSONB)  # ["services", "keywords"]

    # Relationships
    company = relationship('Company', back_populates='ai_enrichment_proposals')
    reviewed_by = relationship('User', foreign_keys=[reviewed_by_id])

    def __repr__(self):
        return f"<AiEnrichmentProposal {self.id} for Company {self.company_id} ({self.status})>"


class MaturityAssessment(Base):
    """Historical tracking of digital maturity scores over time"""
    __tablename__ = 'maturity_assessments'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False, index=True)
    assessed_at = Column(DateTime, default=datetime.now, index=True)
    assessed_by_user_id = Column(Integer, ForeignKey('users.id'))
    assessment_type = Column(String(50))  # 'full', 'quick', 'self_reported', 'audit'

    # === SNAPSHOT OF SCORES ===
    overall_score = Column(Integer)
    online_presence_score = Column(Integer)
    social_media_score = Column(Integer)
    it_infrastructure_score = Column(Integer)
    business_applications_score = Column(Integer)
    backup_dr_score = Column(Integer)
    cybersecurity_score = Column(Integer)
    ai_readiness_score = Column(Integer)

    # === CHANGES SINCE LAST ASSESSMENT ===
    score_change = Column(Integer)  # +5, -3, etc.
    areas_improved = Column(ARRAY(String))  # ['cybersecurity', 'backup']
    areas_declined = Column(ARRAY(String))  # ['social_media']

    notes = Column(Text)

    # Relationship
    company = relationship('Company', back_populates='maturity_history')


# ============================================================
# AI CHAT
# ============================================================

class AIChatConversation(Base):
    """Chat conversations"""
    __tablename__ = 'ai_chat_conversations'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False, index=True)
    title = Column(String(255))
    conversation_type = Column(String(50), default='general')

    # Timestamps
    started_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    is_active = Column(Boolean, default=True)

    # Metrics
    message_count = Column(Integer, default=0)
    model_name = Column(String(100))

    # Pin & custom name
    is_pinned = Column(Boolean, default=False)
    pinned_at = Column(DateTime)
    custom_name = Column(String(255))

    # Relationships
    user = relationship('User', back_populates='conversations')
    messages = relationship('AIChatMessage', back_populates='conversation', cascade='all, delete-orphan', order_by='AIChatMessage.created_at')


class AIChatMessage(Base):
    """Chat messages"""
    __tablename__ = 'ai_chat_messages'

    id = Column(Integer, primary_key=True)
    conversation_id = Column(Integer, ForeignKey('ai_chat_conversations.id'), nullable=False, index=True)
    created_at = Column(DateTime, default=datetime.now)

    # Message
    role = Column(String(20), nullable=False)  # 'user' or 'assistant'
    content = Column(Text, nullable=False)

    # Metrics
    tokens_input = Column(Integer)
    tokens_output = Column(Integer)
    cost_usd = Column(Numeric(10, 6))
    latency_ms = Column(Integer)

    # Flags
    edited = Column(Boolean, default=False)
    regenerated = Column(Boolean, default=False)

    # Feedback (for assistant messages)
    feedback_rating = Column(Integer)  # 1 = thumbs down, 2 = thumbs up
    feedback_comment = Column(Text)  # Optional user comment
    feedback_at = Column(DateTime)

    # Quality metrics (for analytics)
    companies_mentioned = Column(Integer)  # Number of companies in response
    query_intent = Column(String(100))  # Detected intent: 'find_company', 'get_info', 'compare', etc.

    # Relationship
    conversation = relationship('AIChatConversation', back_populates='messages')
    feedback = relationship('AIChatFeedback', back_populates='message', uselist=False)


class AIChatFeedback(Base):
    """Detailed feedback for AI responses - for learning and improvement"""
    __tablename__ = 'ai_chat_feedback'

    id = Column(Integer, primary_key=True)
    message_id = Column(Integer, ForeignKey('ai_chat_messages.id'), nullable=False, unique=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    created_at = Column(DateTime, default=datetime.now)

    # Rating
    rating = Column(Integer, nullable=False)  # 1-5 stars or 1=bad, 2=good
    is_helpful = Column(Boolean)  # Was the answer helpful?
    is_accurate = Column(Boolean)  # Was the information accurate?
    found_company = Column(Boolean)  # Did user find what they were looking for?

    # Feedback text
    comment = Column(Text)
    suggested_answer = Column(Text)  # What should have been the answer?

    # Context for learning
    original_query = Column(Text)  # The user's question
    expected_companies = Column(Text)  # JSON list of company names user expected

    # Relationship
    message = relationship('AIChatMessage', back_populates='feedback')


# ============================================================
# FORUM
# ============================================================

class ForumTopic(Base):
    """Forum topics/threads"""
    __tablename__ = 'forum_topics'

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    content = Column(Text, nullable=False)
    author_id = Column(Integer, ForeignKey('users.id'), nullable=False)

    # Category and Status (for feedback tracking)
    category = Column(String(50), default='question')  # feature_request, bug, question, announcement
    status = Column(String(50), default='new')  # new, in_progress, resolved, rejected
    status_changed_by = Column(Integer, ForeignKey('users.id'))
    status_changed_at = Column(DateTime)
    status_note = Column(Text)

    # Moderation flags
    is_pinned = Column(Boolean, default=False)
    is_locked = Column(Boolean, default=False)
    is_ai_generated = Column(Boolean, default=False)
    views_count = Column(Integer, default=0)

    # Edit tracking
    edited_at = Column(DateTime)
    edited_by = Column(Integer, ForeignKey('users.id'))
    edit_count = Column(Integer, default=0)

    # Soft delete
    is_deleted = Column(Boolean, default=False)
    deleted_at = Column(DateTime)
    deleted_by = Column(Integer, ForeignKey('users.id'))

    # Reactions (JSONB: {"👍": [user_ids], "❤️": [user_ids], "🎉": [user_ids]})
    reactions = Column(PG_JSONB, default={})

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Constants for validation
    CATEGORIES = ['feature_request', 'bug', 'question', 'announcement']
    STATUSES = ['new', 'in_progress', 'resolved', 'rejected']

    CATEGORY_LABELS = {
        'feature_request': 'Propozycja funkcji',
        'bug': 'Błąd',
        'question': 'Pytanie',
        'announcement': 'Ogłoszenie'
    }

    STATUS_LABELS = {
        'new': 'Nowy',
        'in_progress': 'W realizacji',
        'resolved': 'Rozwiązany',
        'rejected': 'Odrzucony'
    }

    # Relationships
    author = relationship('User', foreign_keys=[author_id], back_populates='forum_topics')
    status_changer = relationship('User', foreign_keys=[status_changed_by])
    editor = relationship('User', foreign_keys=[edited_by])
    deleter = relationship('User', foreign_keys=[deleted_by])
    replies = relationship('ForumReply', back_populates='topic', cascade='all, delete-orphan', order_by='ForumReply.created_at')
    attachments = relationship('ForumAttachment', back_populates='topic', cascade='all, delete-orphan',
                               primaryjoin="and_(ForumAttachment.topic_id==ForumTopic.id, ForumAttachment.attachment_type=='topic')")
    subscriptions = relationship('ForumTopicSubscription', back_populates='topic', cascade='all, delete-orphan')

    @property
    def reply_count(self):
        return len(self.replies)

    @property
    def last_activity(self):
        if self.replies:
            return max(r.created_at for r in self.replies)
        return self.created_at

    @property
    def category_label(self):
        return self.CATEGORY_LABELS.get(self.category, self.category)

    @property
    def status_label(self):
        return self.STATUS_LABELS.get(self.status, self.status)


class ForumReply(Base):
    """Forum replies to topics"""
    __tablename__ = 'forum_replies'

    id = Column(Integer, primary_key=True)
    topic_id = Column(Integer, ForeignKey('forum_topics.id'), nullable=False)
    author_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    content = Column(Text, nullable=False)
    is_ai_generated = Column(Boolean, default=False)

    # Edit tracking
    edited_at = Column(DateTime)
    edited_by = Column(Integer, ForeignKey('users.id'))
    edit_count = Column(Integer, default=0)

    # Soft delete
    is_deleted = Column(Boolean, default=False)
    deleted_at = Column(DateTime)
    deleted_by = Column(Integer, ForeignKey('users.id'))

    # Reactions (JSONB: {"👍": [user_ids], "❤️": [user_ids], "🎉": [user_ids]})
    reactions = Column(PG_JSONB, default={})

    # Solution marking
    is_solution = Column(Boolean, default=False)
    marked_as_solution_by = Column(Integer, ForeignKey('users.id'))
    marked_as_solution_at = Column(DateTime)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    topic = relationship('ForumTopic', back_populates='replies')
    author = relationship('User', foreign_keys=[author_id], back_populates='forum_replies')
    editor = relationship('User', foreign_keys=[edited_by])
    deleter = relationship('User', foreign_keys=[deleted_by])
    solution_marker = relationship('User', foreign_keys=[marked_as_solution_by])
    attachments = relationship('ForumAttachment', back_populates='reply', cascade='all, delete-orphan',
                               primaryjoin="and_(ForumAttachment.reply_id==ForumReply.id, ForumAttachment.attachment_type=='reply')")


class ForumAttachment(Base):
    """Forum file attachments for topics and replies"""
    __tablename__ = 'forum_attachments'

    id = Column(Integer, primary_key=True)

    # Polymorphic relationship (topic or reply)
    attachment_type = Column(String(20), nullable=False)  # 'topic' or 'reply'
    topic_id = Column(Integer, ForeignKey('forum_topics.id', ondelete='CASCADE'))
    reply_id = Column(Integer, ForeignKey('forum_replies.id', ondelete='CASCADE'))

    # File metadata
    original_filename = Column(String(255), nullable=False)
    stored_filename = Column(String(255), nullable=False, unique=True)
    file_extension = Column(String(10), nullable=False)
    file_size = Column(Integer, nullable=False)  # in bytes
    mime_type = Column(String(100), nullable=False)

    # Uploader
    uploaded_by = Column(Integer, ForeignKey('users.id'), nullable=False)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    topic = relationship('ForumTopic', back_populates='attachments', foreign_keys=[topic_id])
    reply = relationship('ForumReply', back_populates='attachments', foreign_keys=[reply_id])
    uploader = relationship('User')

    # Allowed file types
    ALLOWED_EXTENSIONS = {'jpg', 'jpeg', 'png', 'gif'}
    MAX_FILE_SIZE = 5 * 1024 * 1024  # 5MB

    @property
    def url(self):
        """Get the URL to serve this file"""
        date = self.created_at or datetime.now()
        subdir = 'topics' if self.attachment_type == 'topic' else 'replies'
        return f"/static/uploads/forum/{subdir}/{date.year}/{date.month:02d}/{self.stored_filename}"

    @property
    def is_image(self):
        """Check if this is an image file"""
        return self.mime_type.startswith('image/')

    @property
    def size_display(self):
        """Human-readable file size"""
        if self.file_size < 1024:
            return f"{self.file_size} B"
        elif self.file_size < 1024 * 1024:
            return f"{self.file_size / 1024:.1f} KB"
        else:
            return f"{self.file_size / (1024 * 1024):.1f} MB"


class BoardDocument(Base):
    """Documents for Rada Izby (Board Council) - protocols, minutes, resolutions"""
    __tablename__ = 'board_documents'

    id = Column(Integer, primary_key=True)

    # Document metadata
    title = Column(String(255), nullable=False)
    description = Column(Text)
    document_type = Column(String(50), default='protocol')  # protocol, minutes, resolution, report, other

    # Meeting reference
    meeting_id = Column(Integer, ForeignKey('board_meetings.id'))
    meeting_date = Column(Date, nullable=False)
    meeting_number = Column(Integer)  # Sequential meeting number (optional)

    # File metadata
    original_filename = Column(String(255), nullable=False)
    stored_filename = Column(String(255), nullable=False, unique=True)
    file_extension = Column(String(10), nullable=False)
    file_size = Column(Integer, nullable=False)  # in bytes
    mime_type = Column(String(100), nullable=False)

    # Upload tracking
    uploaded_by = Column(Integer, ForeignKey('users.id'), nullable=False)
    uploaded_at = Column(DateTime, default=datetime.now)

    # Audit fields
    updated_at = Column(DateTime, onupdate=datetime.now)
    updated_by = Column(Integer, ForeignKey('users.id'))
    is_active = Column(Boolean, default=True)  # Soft delete

    # Relationships
    meeting = relationship('BoardMeeting', backref='documents')
    uploader = relationship('User', foreign_keys=[uploaded_by])
    editor = relationship('User', foreign_keys=[updated_by])

    # Constants
    DOCUMENT_TYPES = ['protocol', 'minutes', 'resolution', 'report', 'other']
    DOCUMENT_TYPE_LABELS = {
        'protocol': 'Protokół',
        'minutes': 'Notatki',
        'resolution': 'Uchwała',
        'report': 'Raport',
        'other': 'Inny'
    }
    ALLOWED_EXTENSIONS = {'pdf', 'docx', 'doc'}
    MAX_FILE_SIZE = 50 * 1024 * 1024  # 50MB

    @property
    def type_label(self):
        """Get Polish label for document type"""
        return self.DOCUMENT_TYPE_LABELS.get(self.document_type, 'Dokument')

    @property
    def size_display(self):
        """Human-readable file size"""
        if self.file_size < 1024:
            return f"{self.file_size} B"
        elif self.file_size < 1024 * 1024:
            return f"{self.file_size / 1024:.1f} KB"
        else:
            return f"{self.file_size / (1024 * 1024):.1f} MB"

    @property
    def file_path(self):
        """Get the full path to the stored file"""
        date = self.uploaded_at or datetime.now()
        return f"/data/board-docs/{date.year}/{date.month:02d}/{self.stored_filename}"


class BoardMeeting(Base):
    """
    Board Council meetings with agenda and protocol data.
    Stores meeting metadata, agenda items, attendance, and proceedings.
    """
    __tablename__ = 'board_meetings'

    id = Column(Integer, primary_key=True)

    # Meeting identification
    meeting_number = Column(Integer, nullable=False)  # Sequential number (e.g., 1 for first meeting of year)
    year = Column(Integer, nullable=False)  # Year for numbering (e.g., 2026)

    # Meeting schedule
    meeting_date = Column(Date, nullable=False)
    start_time = Column(Time)  # e.g., 16:00
    end_time = Column(Time)  # e.g., 18:30
    location = Column(String(255), default='Siedziba Izby')

    # Meeting roles
    chairperson_id = Column(Integer, ForeignKey('users.id'))  # Prowadzący
    secretary_id = Column(Integer, ForeignKey('users.id'))  # Protokolant

    # Guests (non-members attending)
    guests = Column(Text)  # Free text or JSON array

    # Agenda items - JSON array of objects:
    # [{"time_start": "16:00", "time_end": "16:10", "title": "...", "description": "..."}]
    agenda_items = Column(PG_JSONB)

    # Attendance records - JSON object:
    # {"user_id": {"present": true/false, "initials": "LG", "notes": ""}}
    attendance = Column(PG_JSONB)

    # Quorum info
    quorum_count = Column(Integer)  # Number of present members
    quorum_confirmed = Column(Boolean)  # Was quorum achieved?

    # Proceedings - JSON array of objects:
    # [{"agenda_item": 1, "discussed": "...", "decisions": "...", "votes": {...}}]
    proceedings = Column(PG_JSONB)

    # Status workflow
    status = Column(String(20), default='draft')
    # draft -> agenda_published -> protocol_draft -> protocol_published

    # Audit fields
    created_by = Column(Integer, ForeignKey('users.id'), nullable=False)
    created_at = Column(DateTime, default=datetime.now)
    updated_by = Column(Integer, ForeignKey('users.id'))
    updated_at = Column(DateTime, onupdate=datetime.now)
    agenda_published_at = Column(DateTime)
    protocol_published_at = Column(DateTime)

    # Relationships
    chairperson = relationship('User', foreign_keys=[chairperson_id])
    secretary = relationship('User', foreign_keys=[secretary_id])
    creator = relationship('User', foreign_keys=[created_by])
    editor = relationship('User', foreign_keys=[updated_by])

    # Status constants
    STATUS_DRAFT = 'draft'
    STATUS_AGENDA_PUBLISHED = 'agenda_published'
    STATUS_PROTOCOL_DRAFT = 'protocol_draft'
    STATUS_PROTOCOL_PUBLISHED = 'protocol_published'

    STATUS_LABELS = {
        'draft': 'Szkic',
        'agenda_published': 'Program opublikowany',
        'protocol_draft': 'Protokół w przygotowaniu',
        'protocol_published': 'Protokół opublikowany'
    }

    @property
    def status_label(self):
        """Get Polish label for status"""
        return self.STATUS_LABELS.get(self.status, 'Nieznany')

    @property
    def meeting_identifier(self):
        """Get meeting identifier like '1/2026'"""
        return f"{self.meeting_number}/{self.year}"

    def get_attendance_list(self, db_session):
        """Get attendance list with user details"""
        from sqlalchemy.orm import Session
        if not self.attendance:
            return []

        user_ids = [int(uid) for uid in self.attendance.keys()]
        users = db_session.query(User).filter(User.id.in_(user_ids)).all()
        user_map = {u.id: u for u in users}

        result = []
        for user_id_str, data in self.attendance.items():
            user_id = int(user_id_str)
            user = user_map.get(user_id)
            if user:
                result.append({
                    'user': user,
                    'present': data.get('present', False),
                    'initials': data.get('initials', ''),
                    'notes': data.get('notes', '')
                })
        return sorted(result, key=lambda x: x['user'].name or '')


class AdmissionWorkflowLog(Base):
    """Audit log for post-protocol admission workflow runs."""
    __tablename__ = 'admission_workflow_logs'

    id = Column(Integer, primary_key=True)
    meeting_id = Column(Integer, ForeignKey('board_meetings.id'), nullable=False, index=True)

    executed_at = Column(DateTime, default=datetime.now)
    executed_by = Column(Integer, ForeignKey('users.id'))

    extracted_companies = Column(PG_JSONB)   # [{"title", "extracted_name", "decision_text"}]
    matched_companies = Column(PG_JSONB)     # [{"extracted_name", "matched_id", "matched_name", "confidence"}]
    created_companies = Column(PG_JSONB)     # [{"name", "id", "slug"}]
    skipped = Column(PG_JSONB)               # [{"name", "reason"}]

    status = Column(String(20), default='completed')  # completed, partial_error, failed
    error_message = Column(Text)

    notifications_sent = Column(Integer, default=0)
    emails_sent = Column(Integer, default=0)

    meeting = relationship('BoardMeeting')
    executor = relationship('User', foreign_keys=[executed_by])


class ForumTopicSubscription(Base):
    """Forum topic subscriptions for notifications"""
    __tablename__ = 'forum_topic_subscriptions'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    topic_id = Column(Integer, ForeignKey('forum_topics.id', ondelete='CASCADE'), nullable=False)
    notify_email = Column(Boolean, default=True)
    notify_app = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)

    __table_args__ = (UniqueConstraint('user_id', 'topic_id', name='uq_forum_subscription_user_topic'),)

    # Relationships
    user = relationship('User', back_populates='forum_subscriptions')
    topic = relationship('ForumTopic', back_populates='subscriptions')


class ForumReport(Base):
    """Forum content reports for moderation"""
    __tablename__ = 'forum_reports'

    id = Column(Integer, primary_key=True)
    reporter_id = Column(Integer, ForeignKey('users.id'), nullable=False)

    # Polymorphic relationship (topic or reply)
    content_type = Column(String(20), nullable=False)  # 'topic' or 'reply'
    topic_id = Column(Integer, ForeignKey('forum_topics.id', ondelete='CASCADE'))
    reply_id = Column(Integer, ForeignKey('forum_replies.id', ondelete='CASCADE'))

    reason = Column(String(50), nullable=False)  # spam, offensive, off-topic, other
    description = Column(Text)

    status = Column(String(20), default='pending')  # pending, reviewed, dismissed
    reviewed_by = Column(Integer, ForeignKey('users.id'))
    reviewed_at = Column(DateTime)
    review_note = Column(Text)

    created_at = Column(DateTime, default=datetime.now)

    # Constants
    REASONS = ['spam', 'offensive', 'off-topic', 'other']
    REASON_LABELS = {
        'spam': 'Spam',
        'offensive': 'Obraźliwe treści',
        'off-topic': 'Nie na temat',
        'other': 'Inne'
    }
    STATUSES = ['pending', 'reviewed', 'dismissed']

    # Relationships
    reporter = relationship('User', foreign_keys=[reporter_id])
    reviewer = relationship('User', foreign_keys=[reviewed_by])
    topic = relationship('ForumTopic')
    reply = relationship('ForumReply')

    @property
    def reason_label(self):
        return self.REASON_LABELS.get(self.reason, self.reason)


class ForumEditHistory(Base):
    """Forum edit history for audit trail"""
    __tablename__ = 'forum_edit_history'

    id = Column(Integer, primary_key=True)

    # Polymorphic relationship (topic or reply)
    content_type = Column(String(20), nullable=False)  # 'topic' or 'reply'
    topic_id = Column(Integer, ForeignKey('forum_topics.id', ondelete='CASCADE'))
    reply_id = Column(Integer, ForeignKey('forum_replies.id', ondelete='CASCADE'))

    editor_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    old_content = Column(Text, nullable=False)
    new_content = Column(Text, nullable=False)
    edit_reason = Column(String(255))

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    editor = relationship('User')
    topic = relationship('ForumTopic')
    reply = relationship('ForumReply')


class ForumTopicRead(Base):
    """
    Śledzenie odczytów wątków forum (seen by).
    Zapisuje kto i kiedy przeczytał dany wątek.
    """
    __tablename__ = 'forum_topic_reads'

    id = Column(Integer, primary_key=True)
    topic_id = Column(Integer, ForeignKey('forum_topics.id', ondelete='CASCADE'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    read_at = Column(DateTime, default=datetime.now)

    # Relationships
    topic = relationship('ForumTopic', backref='readers')
    user = relationship('User')

    # Unique constraint
    __table_args__ = (
        UniqueConstraint('topic_id', 'user_id', name='uq_forum_topic_user_read'),
    )

    def __repr__(self):
        return f"<ForumTopicRead topic={self.topic_id} user={self.user_id}>"


class ForumReplyRead(Base):
    """
    Śledzenie odczytów odpowiedzi na forum (seen by).
    Zapisuje kto i kiedy przeczytał daną odpowiedź.
    """
    __tablename__ = 'forum_reply_reads'

    id = Column(Integer, primary_key=True)
    reply_id = Column(Integer, ForeignKey('forum_replies.id', ondelete='CASCADE'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    read_at = Column(DateTime, default=datetime.now)

    # Relationships
    reply = relationship('ForumReply', backref='readers')
    user = relationship('User')

    # Unique constraint
    __table_args__ = (
        UniqueConstraint('reply_id', 'user_id', name='uq_forum_reply_user_read'),
    )

    def __repr__(self):
        return f"<ForumReplyRead reply={self.reply_id} user={self.user_id}>"


class AIAPICostLog(Base):
    """API cost tracking"""
    __tablename__ = 'ai_api_costs'

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime, default=datetime.now, index=True)

    # API details
    api_provider = Column(String(50))  # 'gemini'
    model_name = Column(String(100))
    feature = Column(String(100))  # 'ai_chat', 'general', etc.

    # User context
    user_id = Column(Integer, ForeignKey('users.id'), index=True)

    # Token usage
    input_tokens = Column(Integer)
    output_tokens = Column(Integer)
    total_tokens = Column(Integer)

    # Costs
    input_cost = Column(Numeric(10, 6))
    output_cost = Column(Numeric(10, 6))
    total_cost = Column(Numeric(10, 6))

    # Status
    success = Column(Boolean, default=True)
    error_message = Column(Text)
    latency_ms = Column(Integer)

    # Privacy
    prompt_hash = Column(String(64))  # SHA256 hash, not storing actual prompts


# ============================================================
# CALENDAR / EVENTS
# ============================================================

class NordaEvent(Base):
    """Spotkania i wydarzenia Norda Biznes"""
    __tablename__ = 'norda_events'

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    description = Column(Text)
    event_type = Column(String(50), default='meeting')  # meeting, webinar, networking, other

    # Data i czas
    event_date = Column(Date, nullable=False)
    event_date_end = Column(Date)  # NULL dla wydarzeń jednodniowych
    time_start = Column(Time)
    time_end = Column(Time)

    # Lokalizacja
    location = Column(String(500))  # Adres lub "Online"
    location_url = Column(String(1000))  # Link do Google Maps lub Zoom

    # Prelegent (opcjonalnie)
    speaker_name = Column(String(255))
    speaker_company_id = Column(Integer, ForeignKey('companies.id'))

    # Metadane
    is_featured = Column(Boolean, default=False)
    is_ai_generated = Column(Boolean, default=False)
    max_attendees = Column(Integer)
    created_by = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.now)
    reminder_24h_sent_at = Column(DateTime)  # Cron znacznik — nie wysyłaj przypomnienia 24h dwa razy

    # Źródło danych (tracking)
    source = Column(String(255))  # np. 'kalendarz_norda_2026', 'manual', 'api'
    source_note = Column(Text)    # Pełna informacja o źródle

    # Kontrola dostępu
    access_level = Column(String(50), default='members_only')  # public, members_only, rada_only

    # Media
    image_url = Column(String(1000))  # Banner/header image URL

    # Organizer (for calendar exports)
    organizer_name = Column(String(255), default='Norda Biznes')
    organizer_email = Column(String(255), default='biuro@norda-biznes.info')

    # External event (ARP, KIG, etc.)
    is_external = Column(Boolean, default=False)
    external_url = Column(String(1000))          # Registration link at external organizer
    external_source = Column(String(255))        # Source name (e.g. "Agencja Rozwoju Pomorza")

    # Paid event
    is_paid = Column(Boolean, default=False)
    price_member = Column(Numeric(10, 2))       # Price for Izba members
    price_guest = Column(Numeric(10, 2))        # Price for non-members and guest companions

    # Attachment
    attachment_filename = Column(String(255))   # Original filename
    attachment_path = Column(String(1000))      # Server path (static/uploads/events/...)

    # Relationships
    speaker_company = relationship('Company')
    creator = relationship('User', foreign_keys=[created_by])
    attendees = relationship('EventAttendee', back_populates='event', cascade='all, delete-orphan')
    guests = relationship('EventGuest', back_populates='event', cascade='all, delete-orphan')

    @property
    def attendee_count(self):
        return len(self.attendees)

    @property
    def total_attendee_count(self):
        """Łączna liczba uczestników + gości (do sprawdzania limitu max_attendees)."""
        return len(self.attendees) + len(self.guests)

    @property
    def is_past(self):
        from datetime import date
        end = self.event_date_end or self.event_date
        return end < date.today()

    @property
    def is_multi_day(self):
        return bool(self.event_date_end and self.event_date_end > self.event_date)

    @property
    def date_range_display(self):
        """Zwraca 'DD.MM.YYYY' lub 'DD.MM.YYYY – DD.MM.YYYY' dla wielodniowych."""
        start = self.event_date.strftime('%d.%m.%Y')
        if self.is_multi_day:
            return f"{start} – {self.event_date_end.strftime('%d.%m.%Y')}"
        return start

    def can_user_view(self, user) -> bool:
        """Check if a user can view this event (title, date, location).

        All members can SEE all events including Rada Izby events.
        They just can't register or see attendee list for restricted events.
        """
        if not user or not user.is_authenticated:
            return False

        # Admins and office managers can see everything
        if user.has_role(SystemRole.OFFICE_MANAGER):
            return True

        access = self.access_level or 'members_only'

        if access == 'public':
            return True
        elif access == 'members_only':
            # Guests (UNAFFILIATED) can also VIEW events — but cannot register (see can_user_attend)
            return True
        elif access == 'rada_only':
            # Wszyscy członkowie WIDZĄ wydarzenia Rady Izby (tytuł, data, miejsce)
            # ale nie mogą dołączyć ani zobaczyć uczestników. Goście nie widzą.
            return user.is_norda_member or user.has_role(SystemRole.MEMBER) or user.is_rada_member
        else:
            return False

    def can_user_attend(self, user) -> bool:
        """Check if a user can register for this event.

        For Rada Izby events, only designated board members can register.
        UNAFFILIATED guests can view events but cannot register for any.
        """
        if not user or not user.is_authenticated:
            return False

        # Admins and office managers can attend everything
        if user.has_role(SystemRole.OFFICE_MANAGER):
            return True

        access = self.access_level or 'members_only'

        if access == 'public':
            # Guests can view but not register — only members can RSVP
            return user.is_norda_member or user.has_role(SystemRole.MEMBER)
        elif access == 'members_only':
            return user.is_norda_member or user.has_role(SystemRole.MEMBER)
        elif access == 'rada_only':
            # Tylko członkowie Rady Izby mogą się zapisać
            return user.is_rada_member
        else:
            return False

    def can_user_see_attendees(self, user) -> bool:
        """Check if a user can see the attendee list.

        For Rada Izby events, only board members can see who's attending.
        """
        if not user or not user.is_authenticated:
            return False

        # Admins and office managers can see attendees
        if user.has_role(SystemRole.OFFICE_MANAGER):
            return True

        access = self.access_level or 'members_only'

        if access == 'rada_only':
            # Tylko członkowie Rady Izby widzą listę uczestników
            return user.is_rada_member
        else:
            # Dla innych wydarzeń - wszyscy uprawnieni widzą uczestników
            return self.can_user_view(user)


class EventAttendee(Base):
    """RSVP na wydarzenia"""
    __tablename__ = 'event_attendees'

    id = Column(Integer, primary_key=True)
    event_id = Column(Integer, ForeignKey('norda_events.id'), nullable=False)
    # (właściwość poniżej)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    status = Column(String(20), default='confirmed')  # confirmed, maybe, declined
    registered_at = Column(DateTime, default=datetime.now)

    # Payment tracking (for paid events)
    payment_status = Column(String(20), default='unpaid')  # unpaid, paid, exempt
    payment_amount = Column(Numeric(10, 2))
    payment_confirmed_by = Column(Integer, ForeignKey('users.id'))
    payment_confirmed_at = Column(DateTime)

    event = relationship('NordaEvent', back_populates='attendees')
    user = relationship('User', foreign_keys=[user_id])
    payment_confirmer = relationship('User', foreign_keys=[payment_confirmed_by])


class EventGuest(Base):
    """Osoby towarzyszące na wydarzeniach (bez konta na portalu)"""
    __tablename__ = 'event_guests'

    id = Column(Integer, primary_key=True)
    event_id = Column(Integer, ForeignKey('norda_events.id', ondelete='CASCADE'), nullable=False, index=True)
    host_user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True)
    first_name = Column(String(100))
    last_name = Column(String(100))
    organization = Column(String(255))
    guest_type = Column(String(20), default='external')  # 'external' or 'member'
    created_at = Column(DateTime, default=datetime.now, nullable=False)

    # Payment tracking (for paid events)
    payment_status = Column(String(20), default='unpaid')  # unpaid, paid, exempt
    payment_amount = Column(Numeric(10, 2))
    payment_confirmed_by = Column(Integer, ForeignKey('users.id'))
    payment_confirmed_at = Column(DateTime)

    event = relationship('NordaEvent', back_populates='guests')
    host = relationship('User', foreign_keys=[host_user_id])
    payment_confirmer = relationship('User', foreign_keys=[payment_confirmed_by])

    @property
    def display_name(self):
        """Nazwa wyświetlana gościa — łączy dostępne pola."""
        parts = []
        if self.first_name:
            parts.append(self.first_name)
        if self.last_name:
            parts.append(self.last_name)
        return ' '.join(parts) if parts else '(brak danych)'


# ============================================================
# PRIVATE MESSAGES
# ============================================================

class PrivateMessage(Base):
    """Wiadomości prywatne między członkami"""
    __tablename__ = 'private_messages'

    id = Column(Integer, primary_key=True)
    sender_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    recipient_id = Column(Integer, ForeignKey('users.id'), nullable=False)

    subject = Column(String(255))
    content = Column(Text, nullable=False)

    is_read = Column(Boolean, default=False)
    read_at = Column(DateTime)

    # Dla wątków konwersacji
    parent_id = Column(Integer, ForeignKey('private_messages.id'))

    # Kontekst powiązania (np. ogłoszenie B2B, temat forum)
    context_type = Column(String(50))  # 'classified', 'forum_topic', etc.
    context_id = Column(Integer)       # ID powiązanego obiektu

    created_at = Column(DateTime, default=datetime.now)

    sender = relationship('User', foreign_keys=[sender_id], backref='sent_messages')
    recipient = relationship('User', foreign_keys=[recipient_id], backref='received_messages')
    parent = relationship('PrivateMessage', remote_side=[id])


class MessageAttachment(Base):
    """Załączniki do wiadomości prywatnych i grupowych"""
    __tablename__ = 'message_attachments'

    id = Column(Integer, primary_key=True)
    message_id = Column(Integer, ForeignKey('private_messages.id', ondelete='CASCADE'))  # nullable now
    group_message_id = Column(Integer, ForeignKey('group_message.id', ondelete='CASCADE'))  # NEW
    filename = Column(String(255), nullable=False)         # original filename
    stored_filename = Column(String(255), nullable=False)   # UUID-based on disk
    file_size = Column(Integer, nullable=False)             # bytes
    mime_type = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.now)

    conv_message_id = Column(Integer, ForeignKey('conv_messages.id', ondelete='CASCADE'), nullable=True)

    message = relationship('PrivateMessage', backref=backref('attachments', cascade='all, delete-orphan'))
    conv_message = relationship('ConvMessage', back_populates='attachments', foreign_keys=[conv_message_id])


# ============================================================
# GROUP MESSAGES
# ============================================================

class MessageGroup(Base):
    """Grupa czatowa — nazwana lub ad-hoc"""
    __tablename__ = 'message_group'

    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    description = Column(Text)
    owner_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    is_named = Column(Boolean, default=False, nullable=False)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    owner = relationship('User', foreign_keys=[owner_id])
    members = relationship('MessageGroupMember', backref='group', cascade='all, delete-orphan')
    messages = relationship('GroupMessage', backref='group', cascade='all, delete-orphan',
                           order_by='GroupMessage.created_at')

    @property
    def display_name(self):
        """Nazwa wyświetlana — nazwa grupy lub lista imion uczestników"""
        if self.name:
            return self.name
        names = [m.user.name or m.user.email.split('@')[0] for m in self.members if m.user]
        return ', '.join(sorted(names)[:4]) + (f' +{len(names)-4}' if len(names) > 4 else '')

    @property
    def member_count(self):
        return len(self.members)

    @property
    def last_message(self):
        """Ostatnia wiadomość w grupie"""
        if self.messages:
            return self.messages[-1]
        return None


class MessageGroupMember(Base):
    """Członkostwo w grupie czatowej"""
    __tablename__ = 'message_group_member'

    group_id = Column(Integer, ForeignKey('message_group.id', ondelete='CASCADE'), primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), primary_key=True)
    role = Column(String(20), nullable=False, default='member')  # owner, moderator, member
    last_read_at = Column(DateTime)
    joined_at = Column(DateTime, default=datetime.now)
    added_by_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))

    user = relationship('User', foreign_keys=[user_id])
    added_by = relationship('User', foreign_keys=[added_by_id])

    @property
    def is_owner(self):
        return self.role == 'owner'

    @property
    def is_moderator(self):
        return self.role == 'moderator'

    @property
    def can_manage_members(self):
        return self.role in ('owner', 'moderator')


class GroupMessage(Base):
    """Wiadomość w grupie czatowej"""
    __tablename__ = 'group_message'

    id = Column(Integer, primary_key=True)
    group_id = Column(Integer, ForeignKey('message_group.id', ondelete='CASCADE'), nullable=False)
    sender_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    content = Column(Text, nullable=False)
    created_at = Column(DateTime, default=datetime.now)

    sender = relationship('User', foreign_keys=[sender_id])
    attachments = relationship('MessageAttachment',
                              foreign_keys='MessageAttachment.group_message_id',
                              backref='group_message',
                              cascade='all, delete-orphan')


# ============================================================
# B2B CLASSIFIEDS
# ============================================================

class Classified(Base):
    """Ogłoszenia B2B - Szukam/Oferuję"""
    __tablename__ = 'classifieds'

    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    company_id = Column(Integer, ForeignKey('companies.id'))

    # Typ ogłoszenia
    listing_type = Column(String(20), nullable=False)  # 'szukam', 'oferuje'
    category = Column(String(50), nullable=False)  # uslugi, produkty, wspolpraca, praca, inne

    title = Column(String(255), nullable=False)
    description = Column(Text, nullable=False)

    # Opcjonalne szczegóły
    budget_info = Column(String(255))  # "do negocjacji", "5000-10000 PLN"
    location_info = Column(String(255))  # Wejherowo, Cała Polska, Online

    # Status
    is_active = Column(Boolean, default=True)
    is_ai_generated = Column(Boolean, default=False)
    is_test = Column(Boolean, default=False)  # Oznaczenie dla testowych ogłoszeń
    expires_at = Column(DateTime)  # Auto-wygaśnięcie po 30 dniach
    views_count = Column(Integer, default=0)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now)  # set manually in edit route only

    author = relationship('User', backref='classifieds')
    company = relationship('Company')
    attachments = relationship('ClassifiedAttachment', back_populates='classified', cascade='all, delete-orphan', order_by='ClassifiedAttachment.created_at')

    @property
    def is_expired(self):
        if self.expires_at:
            return datetime.now() > self.expires_at
        return False


class ClassifiedAttachment(Base):
    """File attachments for B2B classifieds"""
    __tablename__ = 'classified_attachments'

    id = Column(Integer, primary_key=True)
    classified_id = Column(Integer, ForeignKey('classifieds.id', ondelete='CASCADE'), nullable=False, index=True)

    # File metadata
    original_filename = Column(String(255), nullable=False)
    stored_filename = Column(String(255), nullable=False, unique=True)
    file_extension = Column(String(10), nullable=False)
    file_size = Column(Integer, nullable=False)  # in bytes
    mime_type = Column(String(100), nullable=False)

    # Uploader
    uploaded_by = Column(Integer, ForeignKey('users.id'), nullable=False)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    classified = relationship('Classified', back_populates='attachments')
    uploader = relationship('User')

    ALLOWED_EXTENSIONS = {'jpg', 'jpeg', 'png', 'gif'}
    MAX_FILE_SIZE = 5 * 1024 * 1024  # 5MB

    @property
    def url(self):
        date = self.created_at or datetime.now()
        return f"/static/uploads/classifieds/{date.year}/{date.month:02d}/{self.stored_filename}"

    @property
    def is_image(self):
        return self.file_extension.lower() in ('jpg', 'jpeg', 'png', 'gif')


class ClassifiedRead(Base):
    """
    Śledzenie odczytów ogłoszeń B2B (seen by).
    Zapisuje kto i kiedy przeczytał dane ogłoszenie.
    """
    __tablename__ = 'classified_reads'

    id = Column(Integer, primary_key=True)
    classified_id = Column(Integer, ForeignKey('classifieds.id', ondelete='CASCADE'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    read_at = Column(DateTime, default=datetime.now)

    # Relationships
    classified = relationship('Classified', backref='readers')
    user = relationship('User')

    # Unique constraint
    __table_args__ = (
        UniqueConstraint('classified_id', 'user_id', name='uq_classified_user_read'),
    )

    def __repr__(self):
        return f"<ClassifiedRead classified={self.classified_id} user={self.user_id}>"


class ClassifiedInterest(Base):
    """Zainteresowania użytkowników ogłoszeniami B2B"""
    __tablename__ = 'classified_interests'

    id = Column(Integer, primary_key=True)
    classified_id = Column(Integer, ForeignKey('classifieds.id', ondelete='CASCADE'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    message = Column(String(255))  # opcjonalna krótka notatka
    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    classified = relationship('Classified', backref='interests')
    user = relationship('User')

    # Unique constraint - użytkownik może być zainteresowany tylko raz
    __table_args__ = (
        UniqueConstraint('classified_id', 'user_id', name='uq_classified_interest'),
    )

    def __repr__(self):
        return f"<ClassifiedInterest classified={self.classified_id} user={self.user_id}>"


class ClassifiedQuestion(Base):
    """Publiczne pytania i odpowiedzi do ogłoszeń B2B"""
    __tablename__ = 'classified_questions'

    id = Column(Integer, primary_key=True)
    classified_id = Column(Integer, ForeignKey('classifieds.id', ondelete='CASCADE'), nullable=False)
    author_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    content = Column(Text, nullable=False)

    # Odpowiedź właściciela ogłoszenia
    answer = Column(Text)
    answered_by = Column(Integer, ForeignKey('users.id'))
    answered_at = Column(DateTime)

    # Widoczność
    is_public = Column(Boolean, default=True)

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    classified = relationship('Classified', backref='questions')
    author = relationship('User', foreign_keys=[author_id])
    answerer = relationship('User', foreign_keys=[answered_by])

    def __repr__(self):
        return f"<ClassifiedQuestion id={self.id} classified={self.classified_id}>"


class CompanyContact(Base):
    """Multiple contacts (phones, emails) per company with source tracking"""
    __tablename__ = 'company_contacts'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)

    # Contact type: 'phone', 'email', 'fax', 'mobile'
    contact_type = Column(String(20), nullable=False, index=True)

    # Contact value (phone number or email address)
    value = Column(String(255), nullable=False)

    # Purpose/description: 'Biuro', 'Sprzedaż', 'Właściciel', 'Transport', 'Serwis', etc.
    purpose = Column(String(100))

    # Is this the primary contact of this type?
    is_primary = Column(Boolean, default=False)

    # Source of this contact data
    source = Column(String(100))  # 'website', 'krs', 'google_business', 'facebook', 'manual', 'brave_search'
    source_url = Column(String(500))  # URL where the contact was found
    source_date = Column(Date)  # When the contact was found/verified

    # Validation
    is_verified = Column(Boolean, default=False)
    verified_at = Column(DateTime)
    verified_by = Column(String(100))

    # Metadata
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationship
    company = relationship('Company', backref='contacts')

    __table_args__ = (
        UniqueConstraint('company_id', 'contact_type', 'value', name='uq_company_contact_type_value'),
    )


class CompanySocialMedia(Base):
    """Social media profiles for companies with verification tracking"""
    __tablename__ = 'company_social_media'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    platform = Column(String(50), nullable=False, index=True)  # facebook, linkedin, instagram, youtube, twitter
    url = Column(String(500), nullable=False)

    # Tracking freshness
    verified_at = Column(DateTime, nullable=False, default=datetime.now, index=True)
    source = Column(String(100))  # website_scrape, brave_search, manual, facebook_api

    # Validation
    is_valid = Column(Boolean, default=True)
    last_checked_at = Column(DateTime)
    check_status = Column(String(50))  # ok, 404, redirect, blocked

    # Metadata from platform
    page_name = Column(String(255))
    followers_count = Column(Integer)

    # Profile completeness indicators
    has_profile_photo = Column(Boolean)
    has_cover_photo = Column(Boolean)
    has_bio = Column(Boolean)
    profile_description = Column(Text)

    # Activity metrics
    posts_count_30d = Column(Integer)
    posts_count_365d = Column(Integer)
    last_post_date = Column(DateTime)

    # Scoring & analytics
    posting_frequency_score = Column(Integer)  # 0-10
    engagement_rate = Column(Numeric(5, 2))  # percent
    content_types = Column(JSONB)  # {"photos": 12, "videos": 3, "text": 5}
    profile_completeness_score = Column(Integer)  # 0-100

    # Historical tracking
    followers_history = Column(JSONB)  # [{"date": "2026-02-01", "count": 150}, ...]

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationship
    company = relationship('Company', backref='social_media_profiles')

    __table_args__ = (
        UniqueConstraint('company_id', 'platform', 'url', name='uq_company_platform_url'),
    )


class CompanyWebsite(Base):
    """Multiple website URLs for companies"""
    __tablename__ = 'company_websites'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    url = Column(String(500), nullable=False)
    label = Column(String(100))  # optional: "Sklep", "Strona główna"
    website_type = Column(String(20), default='website')  # website, store, booking, blog, portfolio, other
    is_primary = Column(Boolean, default=False)
    source = Column(String(100))  # manual_edit, migration, website_scrape
    is_valid = Column(Boolean, default=True)
    last_checked_at = Column(DateTime)
    check_status = Column(String(50))  # ok, 404, redirect, blocked
    created_at = Column(DateTime, default=datetime.now)

    company = relationship('Company', back_populates='websites')
    analyses = relationship('CompanyWebsiteAnalysis', backref='company_website', foreign_keys='CompanyWebsiteAnalysis.company_website_id')


class CompanyRecommendation(Base):
    """Peer recommendations between NORDA BIZNES members"""
    __tablename__ = 'company_recommendations'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False, index=True)

    # Recommendation content
    recommendation_text = Column(Text, nullable=False)
    service_category = Column(String(200))  # Optional: specific service recommended for

    # Privacy settings
    show_contact = Column(Boolean, default=True)  # Show recommender's contact info

    # Moderation
    status = Column(String(20), default='pending', index=True)  # pending, approved, rejected
    moderated_by = Column(Integer, ForeignKey('users.id'), nullable=True)
    moderated_at = Column(DateTime)
    rejection_reason = Column(Text)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', backref='recommendations')
    user = relationship('User', foreign_keys=[user_id], backref='recommendations_given')
    moderator = relationship('User', foreign_keys=[moderated_by], backref='recommendations_moderated')

    __table_args__ = (
        UniqueConstraint('user_id', 'company_id', name='uq_user_company_recommendation'),
    )


class UserNotification(Base):
    """
    In-app notifications for users.
    Supports badges and notification center.
    """
    __tablename__ = 'user_notifications'

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

    # Notification content
    title = Column(String(255), nullable=False)
    message = Column(Text)
    notification_type = Column(String(50), default='info', index=True)
    # Types: news, system, message, event, alert

    # Related entity (optional)
    related_type = Column(String(50))  # company_news, event, message
    related_id = Column(Integer)

    # Status
    is_read = Column(Boolean, default=False, index=True)
    read_at = Column(DateTime)

    # Link
    action_url = Column(String(500))

    # Timestamps
    created_at = Column(DateTime, default=datetime.now, index=True)

    # Relationship
    user = relationship('User', backref='notifications')

    def mark_as_read(self):
        self.is_read = True
        self.read_at = datetime.now()


# ============================================================
# GOOGLE BUSINESS PROFILE AUDIT
# ============================================================

class GBPAudit(Base):
    """
    Google Business Profile audit results for companies.
    Tracks completeness scores and provides improvement recommendations.
    """
    __tablename__ = 'gbp_audits'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)

    # Audit timestamp
    audit_date = Column(DateTime, default=datetime.now, nullable=False, index=True)

    # Completeness scoring (0-100)
    completeness_score = Column(Integer)

    # Field-by-field status tracking
    # Example: {"name": {"status": "complete", "value": "Company Name"}, "phone": {"status": "missing"}, ...}
    fields_status = Column(JSONB)

    # AI-generated recommendations
    # Example: [{"priority": "high", "field": "description", "recommendation": "Add a detailed business description..."}, ...]
    recommendations = Column(JSONB)

    # Individual field scores (for detailed breakdown)
    has_name = Column(Boolean, default=False)
    has_address = Column(Boolean, default=False)
    has_phone = Column(Boolean, default=False)
    has_website = Column(Boolean, default=False)
    has_hours = Column(Boolean, default=False)
    has_categories = Column(Boolean, default=False)
    has_photos = Column(Boolean, default=False)
    has_description = Column(Boolean, default=False)
    has_services = Column(Boolean, default=False)
    has_reviews = Column(Boolean, default=False)

    # Photo counts
    photo_count = Column(Integer, default=0)
    logo_present = Column(Boolean, default=False)
    cover_photo_present = Column(Boolean, default=False)

    # Review metrics
    review_count = Column(Integer, default=0)
    average_rating = Column(Numeric(2, 1))

    # Google Place data
    google_place_id = Column(String(100))
    google_maps_url = Column(String(500))

    # Review management
    reviews_with_response = Column(Integer, default=0)
    reviews_without_response = Column(Integer, default=0)
    review_response_rate = Column(Numeric(5, 2))
    avg_review_response_days = Column(Numeric(5, 1))
    review_sentiment = Column(JSONB)
    reviews_30d = Column(Integer, default=0)
    review_keywords = Column(JSONB)

    # Content & activity
    has_posts = Column(Boolean)
    posts_count_30d = Column(Integer)
    has_products = Column(Boolean)
    has_qa = Column(Boolean)
    qa_count = Column(Integer)

    # Enhanced attributes
    attributes = Column(JSONB)
    special_hours = Column(JSONB)
    has_special_hours = Column(Boolean)

    # NAP consistency
    nap_consistent = Column(Boolean)
    nap_issues = Column(JSONB)

    # Keywords
    description_keywords = Column(JSONB)
    keyword_density_score = Column(Integer)

    # Photo analysis
    photo_categories = Column(JSONB)

    # Audit metadata
    audit_source = Column(String(50), default='manual')  # manual, automated, api
    audit_version = Column(String(20), default='1.0')
    audit_errors = Column(Text)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationship
    company = relationship('Company', backref='gbp_audits')

    def __repr__(self):
        return f'<GBPAudit company_id={self.company_id} score={self.completeness_score}>'

    @property
    def score_category(self):
        """Return score category: excellent, good, needs_work, poor"""
        if self.completeness_score is None:
            return 'unknown'
        if self.completeness_score >= 90:
            return 'excellent'
        elif self.completeness_score >= 70:
            return 'good'
        elif self.completeness_score >= 50:
            return 'needs_work'
        else:
            return 'poor'


class GBPReview(Base):
    """Individual Google Business Profile reviews for tracking and analysis"""
    __tablename__ = 'gbp_reviews'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    google_review_id = Column(String(255))
    author_name = Column(String(255))
    rating = Column(Integer, nullable=False)
    text = Column(Text)
    publish_time = Column(DateTime)
    has_owner_response = Column(Boolean, default=False)
    owner_response_text = Column(Text)
    owner_response_time = Column(DateTime)
    sentiment = Column(String(20))  # positive, neutral, negative
    keywords = Column(JSONB)
    created_at = Column(DateTime, default=datetime.now)

    # Relationship
    company = relationship('Company', backref='gbp_reviews')

    __table_args__ = (
        UniqueConstraint('company_id', 'google_review_id', name='uq_company_google_review'),
    )


# ============================================================
# COMPETITOR MONITORING
# ============================================================

class CompanyCompetitor(Base):
    """Tracked competitors for a company via Google Places"""
    __tablename__ = 'company_competitors'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    competitor_place_id = Column(String(255), nullable=False)
    competitor_name = Column(String(255))
    competitor_address = Column(String(500))
    competitor_rating = Column(Numeric(2, 1))
    competitor_review_count = Column(Integer)
    competitor_category = Column(String(255))
    competitor_website = Column(String(500))
    added_by = Column(String(20), default='auto')  # auto, manual
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', backref='competitors')
    snapshots = relationship('CompetitorSnapshot', backref='competitor', cascade='all, delete-orphan')

    __table_args__ = (
        UniqueConstraint('company_id', 'competitor_place_id', name='uq_company_competitor'),
    )


class CompetitorSnapshot(Base):
    """Periodic snapshot of competitor's Google Business Profile"""
    __tablename__ = 'competitor_snapshots'

    id = Column(Integer, primary_key=True)
    competitor_id = Column(Integer, ForeignKey('company_competitors.id', ondelete='CASCADE'), nullable=False, index=True)
    snapshot_date = Column(Date, nullable=False)
    rating = Column(Numeric(2, 1))
    review_count = Column(Integer)
    photo_count = Column(Integer)
    posts_count = Column(Integer)
    has_website = Column(Boolean)
    has_description = Column(Boolean)
    data = Column(JSONB)  # full snapshot
    changes = Column(JSONB)  # delta vs previous
    created_at = Column(DateTime, default=datetime.now)

    __table_args__ = (
        UniqueConstraint('competitor_id', 'snapshot_date', name='uq_competitor_snapshot_date'),
    )


# ============================================================
# UNIFIED AUDIT REPORTS
# ============================================================

class AuditReport(Base):
    """Unified audit report combining Social, GBP, and SEO data"""
    __tablename__ = 'audit_reports'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    report_type = Column(String(20), default='full')  # full, social, gbp, seo
    period_start = Column(Date)
    period_end = Column(Date)

    # Overall scores
    overall_score = Column(Integer)
    social_score = Column(Integer)
    gbp_score = Column(Integer)
    seo_score = Column(Integer)

    # Report sections
    sections = Column(JSONB)

    # Pre-rendered report data
    data = Column(JSONB)

    # Metadata
    custom_message = Column(Text)
    generated_by = Column(String(50), default='system')
    generated_at = Column(DateTime, default=datetime.now)
    status = Column(String(20), default='draft')

    created_at = Column(DateTime, default=datetime.now)

    # Relationship
    company = relationship('Company', backref='audit_reports')


# ============================================================
# IT INFRASTRUCTURE AUDIT
# ============================================================

class ITAudit(Base):
    """
    IT infrastructure audit for companies.
    Tracks IT infrastructure, security posture, and collaboration readiness.
    Used for cross-company collaboration matching.
    """
    __tablename__ = 'it_audits'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'),
                       nullable=False, index=True)

    # Audit timestamp and metadata
    audit_date = Column(DateTime, default=datetime.now, nullable=False, index=True)
    audit_source = Column(String(50), default='form')  # form, api_sync
    audited_by = Column(Integer, ForeignKey('users.id'))

    # === SCORES (0-100) ===
    overall_score = Column(Integer)
    completeness_score = Column(Integer)
    security_score = Column(Integer)
    collaboration_score = Column(Integer)
    maturity_level = Column(String(20))  # basic, developing, established, advanced

    # === SECTION 1: IT CONTACT ===
    has_it_manager = Column(Boolean, default=False)
    it_outsourced = Column(Boolean, default=False)
    it_provider_name = Column(String(255))
    it_contact_name = Column(String(255))
    it_contact_email = Column(String(255))

    # === SECTION 2: CLOUD & IDENTITY ===
    has_azure_ad = Column(Boolean, default=False)
    azure_tenant_name = Column(String(255))
    azure_user_count = Column(String(20))  # Range: 1-10, 11-50, 51-100, 100+
    has_m365 = Column(Boolean, default=False)
    m365_plans = Column(ARRAY(String))  # Business Basic, Business Standard, E3, E5, etc.
    teams_usage = Column(ARRAY(String))  # chat, meetings, files, phone
    has_google_workspace = Column(Boolean, default=False)

    # === SECTION 3: SERVER INFRASTRUCTURE ===
    server_count = Column(String(20))  # Range: 0, 1-3, 4-10, 10+
    server_types = Column(ARRAY(String))  # physical, vm_onprem, cloud_iaas
    virtualization_platform = Column(String(50))  # none, vmware, hyperv, proxmox, kvm
    server_os = Column(ARRAY(String))  # windows_server, linux_ubuntu, linux_debian, linux_rhel
    network_firewall_brand = Column(String(100))

    # === SECTION 4: ENDPOINTS ===
    employee_count = Column(String(20))  # Range: 1-10, 11-50, 51-100, 100+
    computer_count = Column(String(20))  # Range: 1-10, 11-50, 51-100, 100+
    desktop_os = Column(ARRAY(String))  # windows_10, windows_11, macos, linux
    has_mdm = Column(Boolean, default=False)
    mdm_solution = Column(String(50))  # intune, jamf, other

    # === SECTION 5: SECURITY ===
    antivirus_solution = Column(String(50))  # none, windows_defender, eset, kaspersky, other
    has_edr = Column(Boolean, default=False)
    edr_solution = Column(String(100))  # microsoft_defender_atp, crowdstrike, sentinelone, other
    has_vpn = Column(Boolean, default=False)
    vpn_solution = Column(String(50))  # ipsec, wireguard, openvpn, fortinet, other
    has_mfa = Column(Boolean, default=False)
    mfa_scope = Column(ARRAY(String))  # email, vpn, erp, all_apps

    # === SECTION 6: BACKUP & DISASTER RECOVERY ===
    backup_solution = Column(String(50))  # none, veeam, acronis, pbs, azure_backup, other
    backup_targets = Column(ARRAY(String))  # local_nas, offsite, cloud, tape
    backup_frequency = Column(String(20))  # daily, weekly, monthly, continuous
    has_proxmox_pbs = Column(Boolean, default=False)
    has_dr_plan = Column(Boolean, default=False)

    # === SECTION 7: MONITORING ===
    monitoring_solution = Column(String(50))  # none, zabbix, prtg, nagios, datadog, other
    zabbix_integration = Column(JSONB)  # {hostname: '', agent_installed: bool, templates: []}

    # === SECTION 8: BUSINESS APPS ===
    ticketing_system = Column(String(50))  # none, freshdesk, zendesk, jira_service, other
    erp_system = Column(String(50))  # none, sap, microsoft_dynamics, enova, optima, other
    crm_system = Column(String(50))  # none, salesforce, hubspot, pipedrive, other
    document_management = Column(String(50))  # none, sharepoint, google_drive, dropbox, other

    # === SECTION 9: ACTIVE DIRECTORY ===
    has_local_ad = Column(Boolean, default=False)
    ad_domain_name = Column(String(255))
    has_ad_azure_sync = Column(Boolean, default=False)  # Azure AD Connect / Cloud Sync

    # === COLLABORATION FLAGS (for matching algorithm) ===
    open_to_shared_licensing = Column(Boolean, default=False)
    open_to_backup_replication = Column(Boolean, default=False)
    open_to_teams_federation = Column(Boolean, default=False)
    open_to_shared_monitoring = Column(Boolean, default=False)
    open_to_collective_purchasing = Column(Boolean, default=False)
    open_to_knowledge_sharing = Column(Boolean, default=False)

    # === RAW DATA & METADATA ===
    form_data = Column(JSONB)  # Full form submission for reference
    recommendations = Column(JSONB)  # AI-generated recommendations
    audit_errors = Column(Text)  # Any errors during audit processing

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', backref='it_audits')
    auditor = relationship('User', foreign_keys=[audited_by])

    def __repr__(self):
        return f'<ITAudit company_id={self.company_id} score={self.overall_score}>'

    @property
    def maturity_label(self):
        """Return Polish label for maturity level"""
        labels = {
            'basic': 'Podstawowy',
            'developing': 'Rozwijający się',
            'established': 'Ugruntowany',
            'advanced': 'Zaawansowany'
        }
        return labels.get(self.maturity_level, 'Nieznany')

    @property
    def score_category(self):
        """Return score category: excellent, good, needs_work, poor"""
        if self.overall_score is None:
            return 'unknown'
        if self.overall_score >= 80:
            return 'excellent'
        elif self.overall_score >= 60:
            return 'good'
        elif self.overall_score >= 40:
            return 'needs_work'
        else:
            return 'poor'

    @property
    def collaboration_flags_count(self):
        """Count how many collaboration flags are enabled"""
        flags = [
            self.open_to_shared_licensing,
            self.open_to_backup_replication,
            self.open_to_teams_federation,
            self.open_to_shared_monitoring,
            self.open_to_collective_purchasing,
            self.open_to_knowledge_sharing
        ]
        return sum(1 for f in flags if f)


class ITCollaborationMatch(Base):
    """
    IT collaboration matches between companies.
    Stores potential collaboration opportunities discovered by the matching algorithm.
    Match types: shared_licensing, backup_replication, teams_federation,
                 shared_monitoring, collective_purchasing, knowledge_sharing
    """
    __tablename__ = 'it_collaboration_matches'

    id = Column(Integer, primary_key=True)
    company_a_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'),
                         nullable=False, index=True)
    company_b_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'),
                         nullable=False, index=True)

    # Match details
    match_type = Column(String(50), nullable=False, index=True)
    # Types: shared_licensing, backup_replication, teams_federation,
    #        shared_monitoring, collective_purchasing, knowledge_sharing
    match_reason = Column(Text)  # Human-readable explanation of why this is a match
    match_score = Column(Integer)  # 0-100 strength of the match

    # Status: suggested, contacted, in_progress, completed, declined
    status = Column(String(20), default='suggested', index=True)

    # Shared attributes that led to this match (JSONB for flexibility)
    # Example: {"m365_plans": ["E3", "E5"], "has_proxmox_pbs": true}
    shared_attributes = Column(JSONB)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now, index=True)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company_a = relationship('Company', foreign_keys=[company_a_id],
                            backref='collaboration_matches_as_a')
    company_b = relationship('Company', foreign_keys=[company_b_id],
                            backref='collaboration_matches_as_b')

    __table_args__ = (
        UniqueConstraint('company_a_id', 'company_b_id', 'match_type',
                        name='uq_it_collab_match_pair_type'),
    )

    def __repr__(self):
        return f'<ITCollaborationMatch {self.company_a_id}<->{self.company_b_id} type={self.match_type}>'

    @property
    def match_type_label(self):
        """Return Polish label for match type"""
        labels = {
            'shared_licensing': 'Współdzielone licencje',
            'backup_replication': 'Replikacja backupów',
            'teams_federation': 'Federacja Teams',
            'shared_monitoring': 'Wspólny monitoring',
            'collective_purchasing': 'Zakupy grupowe',
            'knowledge_sharing': 'Wymiana wiedzy'
        }
        return labels.get(self.match_type, self.match_type)

    @property
    def status_label(self):
        """Return Polish label for status"""
        labels = {
            'suggested': 'Sugerowane',
            'contacted': 'Skontaktowano',
            'in_progress': 'W trakcie',
            'completed': 'Zakończone',
            'declined': 'Odrzucone'
        }
        return labels.get(self.status, self.status)


# ============================================================
# MEMBERSHIP FEES
# ============================================================

class MembershipFee(Base):
    """
    Membership fee records for companies.
    Tracks monthly payments from Norda Biznes members.
    """
    __tablename__ = 'membership_fees'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)

    # Period identification
    fee_year = Column(Integer, nullable=False)  # e.g., 2026
    fee_month = Column(Integer, nullable=False)  # 1-12

    # Fee details
    amount = Column(Numeric(10, 2), nullable=False)  # Amount due in PLN
    amount_paid = Column(Numeric(10, 2), default=0)  # Amount actually paid

    # Payment status: pending, paid, partial, overdue, waived
    status = Column(String(20), default='pending', index=True)

    # Payment tracking
    payment_date = Column(Date)
    payment_method = Column(String(50))  # transfer, cash, card, other
    payment_reference = Column(String(100))  # Bank transfer reference

    # Admin tracking
    recorded_by = Column(Integer, ForeignKey('users.id'))
    recorded_at = Column(DateTime)

    notes = Column(Text)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', backref='membership_fees')
    recorded_by_user = relationship('User', foreign_keys=[recorded_by])

    __table_args__ = (
        UniqueConstraint('company_id', 'fee_year', 'fee_month', name='uq_company_fee_period'),
    )

    @property
    def is_fully_paid(self):
        return (self.amount_paid or 0) >= self.amount

    @property
    def outstanding_amount(self):
        return max(0, float(self.amount) - float(self.amount_paid or 0))


class MembershipFeeConfig(Base):
    """
    Configuration for membership fees.
    Allows variable amounts per company or category.
    """
    __tablename__ = 'membership_fee_config'

    id = Column(Integer, primary_key=True)

    # Scope: global, category, or company
    scope = Column(String(20), nullable=False)  # 'global', 'category', 'company'
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=True)

    monthly_amount = Column(Numeric(10, 2), nullable=False)

    valid_from = Column(Date, nullable=False)
    valid_until = Column(Date)  # NULL = currently active

    created_by = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.now)
    notes = Column(Text)

    # Relationships
    category = relationship('Category')
    company = relationship('Company')


# ============================================================
# ZIELONY OKRĘG PRZEMYSŁOWY KASZUBIA (ZOPK)
# ============================================================

class ZOPKProject(Base):
    """
    Sub-projects within ZOPK initiative.
    Examples: offshore wind, nuclear plant, data centers, hydrogen labs
    """
    __tablename__ = 'zopk_projects'

    id = Column(Integer, primary_key=True)
    slug = Column(String(100), unique=True, nullable=False, index=True)
    name = Column(String(255), nullable=False)
    description = Column(Text)

    # Project details
    project_type = Column(String(50))  # energy, infrastructure, technology, defense
    status = Column(String(50), default='planned')  # planned, in_progress, completed
    start_date = Column(Date)
    end_date = Column(Date)

    # Location info
    location = Column(String(255))
    region = Column(String(100))  # Wejherowo, Rumia, Gdynia, etc.

    # Key metrics
    estimated_investment = Column(Numeric(15, 2))  # Investment amount in PLN
    estimated_jobs = Column(Integer)  # Number of jobs created

    # Visual
    icon = Column(String(50))  # CSS icon class or emoji
    color = Column(String(20))  # HEX color for badges

    # Display order
    sort_order = Column(Integer, default=0)
    is_featured = Column(Boolean, default=False)
    is_active = Column(Boolean, default=True)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)


class ZOPKStakeholder(Base):
    """
    Key people and organizations involved in ZOPK.
    Politicians, coordinators, companies, institutions.
    """
    __tablename__ = 'zopk_stakeholders'

    id = Column(Integer, primary_key=True)

    # Person or organization
    stakeholder_type = Column(String(20), nullable=False)  # person, organization
    name = Column(String(255), nullable=False)

    # Role and affiliation
    role = Column(String(255))  # Koordynator, Minister, Starosta, etc.
    organization = Column(String(255))  # MON, Starostwo Wejherowskie, etc.

    # Contact (optional, public info only)
    email = Column(String(255))
    phone = Column(String(50))
    website = Column(String(500))

    # Social media
    linkedin_url = Column(String(500))
    twitter_url = Column(String(500))

    # Photo/logo
    photo_url = Column(String(500))

    # Description
    bio = Column(Text)

    # Categorization
    category = Column(String(50))  # government, local_authority, business, academic
    importance = Column(Integer, default=0)  # For sorting (higher = more important)

    is_active = Column(Boolean, default=True)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    project_links = relationship('ZOPKStakeholderProject', back_populates='stakeholder')

    __table_args__ = (
        UniqueConstraint('name', name='uq_zopk_stakeholder_name'),
    )


class ZOPKStakeholderProject(Base):
    """Link table between stakeholders and projects"""
    __tablename__ = 'zopk_stakeholder_projects'

    id = Column(Integer, primary_key=True)
    stakeholder_id = Column(Integer, ForeignKey('zopk_stakeholders.id', ondelete='CASCADE'), nullable=False)
    project_id = Column(Integer, ForeignKey('zopk_projects.id', ondelete='CASCADE'), nullable=False)
    role_in_project = Column(String(255))  # e.g., "Koordynator", "Inwestor"

    created_at = Column(DateTime, default=datetime.now)

    stakeholder = relationship('ZOPKStakeholder', back_populates='project_links')
    project = relationship('ZOPKProject', backref='stakeholder_links')

    __table_args__ = (
        UniqueConstraint('stakeholder_id', 'project_id', name='uq_stakeholder_project'),
    )


class ZOPKNews(Base):
    """
    News articles about ZOPK with approval workflow.
    Can be fetched automatically or added manually.
    """
    __tablename__ = 'zopk_news'

    id = Column(Integer, primary_key=True)

    # Source information
    title = Column(String(500), nullable=False)
    description = Column(Text)
    url = Column(String(1000), nullable=False)
    source_name = Column(String(200))  # Portal name: trojmiasto.pl, etc.
    source_domain = Column(String(200))  # Domain: trojmiasto.pl

    # Article details
    published_at = Column(DateTime)
    author = Column(String(255))
    image_url = Column(String(1000))

    # Categorization
    news_type = Column(String(50))  # news, announcement, interview, press_release
    project_id = Column(Integer, ForeignKey('zopk_projects.id'))  # Link to sub-project

    # AI Analysis
    relevance_score = Column(Numeric(3, 2))  # 0.00-1.00
    sentiment = Column(String(20))  # positive, neutral, negative
    ai_summary = Column(Text)  # AI-generated summary
    keywords = Column(StringArray)  # Extracted keywords

    # Cross-verification (multi-source confidence)
    confidence_score = Column(Integer, default=1)  # 1-5, increases with source confirmations
    source_count = Column(Integer, default=1)  # Number of sources that found this story
    sources_list = Column(StringArray)  # List of sources: ['brave', 'google_news', 'rss_trojmiasto']
    title_hash = Column(String(64), index=True)  # For fuzzy title matching (normalized)
    is_auto_verified = Column(Boolean, default=False)  # True if 3+ sources confirmed

    # AI Relevance Evaluation (Gemini)
    ai_relevant = Column(Boolean)  # True = relevant to ZOPK, False = not relevant, NULL = not evaluated
    ai_relevance_score = Column(Integer)  # 1-5 stars: 1=weak match, 5=perfect match
    ai_evaluation_reason = Column(Text)  # AI's explanation of relevance decision
    ai_evaluated_at = Column(DateTime)  # When AI evaluation was performed
    ai_model = Column(String(100))  # Which AI model was used (e.g., gemini-3-flash-preview)

    # Moderation workflow
    status = Column(String(20), default='pending', index=True)  # pending, approved, rejected, auto_approved
    moderated_by = Column(Integer, ForeignKey('users.id'))
    moderated_at = Column(DateTime)
    rejection_reason = Column(Text)

    # Source tracking
    source_type = Column(String(50), default='manual')  # manual, brave_search, rss, scraper
    fetch_job_id = Column(String(100))  # ID of the fetch job that found this

    # Deduplication
    url_hash = Column(String(64), unique=True, index=True)  # SHA256 of URL

    is_featured = Column(Boolean, default=False)
    views_count = Column(Integer, default=0)

    # Full content (scraped from source URL) - for knowledge extraction
    full_content = Column(Text)  # Full article text (without HTML, ads, navigation)
    content_scraped_at = Column(DateTime)  # When content was scraped
    scrape_status = Column(String(20), default='pending', index=True)  # pending, scraped, failed, skipped
    scrape_error = Column(Text)  # Error message if scraping failed
    scrape_attempts = Column(Integer, default=0)  # Number of scraping attempts
    content_word_count = Column(Integer)  # Word count of scraped content
    content_language = Column(String(10), default='pl')  # pl, en

    # Knowledge extraction status
    knowledge_extracted = Column(Boolean, default=False, index=True)  # True if chunks/facts/entities extracted
    knowledge_extracted_at = Column(DateTime)  # When knowledge was extracted

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    project = relationship('ZOPKProject', backref='news_items')
    moderator = relationship('User', foreign_keys=[moderated_by])


class ZOPKResource(Base):
    """
    Resources: documents, links, images, videos related to ZOPK.
    Knowledge base materials.
    """
    __tablename__ = 'zopk_resources'

    id = Column(Integer, primary_key=True)

    # Resource identification
    title = Column(String(255), nullable=False)
    description = Column(Text)

    # Resource type
    resource_type = Column(String(50), nullable=False)  # link, document, image, video, map

    # URL or file path
    url = Column(String(1000))
    file_path = Column(String(500))  # For uploaded files
    file_size = Column(Integer)
    mime_type = Column(String(100))

    # Thumbnail
    thumbnail_url = Column(String(1000))

    # Categorization
    category = Column(String(50))  # official, media, research, presentation
    project_id = Column(Integer, ForeignKey('zopk_projects.id'))

    # Tags for search
    tags = Column(StringArray)

    # Source
    source_name = Column(String(255))
    source_date = Column(Date)

    # Moderation
    status = Column(String(20), default='approved', index=True)  # pending, approved, rejected
    uploaded_by = Column(Integer, ForeignKey('users.id'))

    is_featured = Column(Boolean, default=False)
    sort_order = Column(Integer, default=0)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    project = relationship('ZOPKProject', backref='resources')
    uploader = relationship('User', foreign_keys=[uploaded_by])


class ZOPKCompanyLink(Base):
    """
    Links between ZOPK projects and Norda Biznes member companies.
    Shows which local companies can benefit or collaborate.
    """
    __tablename__ = 'zopk_company_links'

    id = Column(Integer, primary_key=True)

    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    project_id = Column(Integer, ForeignKey('zopk_projects.id', ondelete='CASCADE'), nullable=False)

    # Type of involvement
    link_type = Column(String(50), nullable=False)  # potential_supplier, partner, investor, beneficiary

    # Description of potential collaboration
    collaboration_description = Column(Text)

    # Relevance scoring
    relevance_score = Column(Integer)  # 1-100

    # Status
    status = Column(String(20), default='suggested')  # suggested, confirmed, active, completed

    # Admin notes
    admin_notes = Column(Text)
    created_by = Column(Integer, ForeignKey('users.id'))

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', backref='zopk_project_links')
    project = relationship('ZOPKProject', backref='company_links')
    creator = relationship('User', foreign_keys=[created_by])

    __table_args__ = (
        UniqueConstraint('company_id', 'project_id', 'link_type', name='uq_company_project_link'),
    )


class ZOPKNewsFetchJob(Base):
    """
    Tracking for automated news fetch jobs.
    Records when and what was searched.
    """
    __tablename__ = 'zopk_news_fetch_jobs'

    id = Column(Integer, primary_key=True)
    job_id = Column(String(100), unique=True, nullable=False, index=True)

    # Job configuration
    search_query = Column(String(500))
    search_api = Column(String(50))  # brave, google, bing
    date_range_start = Column(Date)
    date_range_end = Column(Date)

    # Results
    results_found = Column(Integer, default=0)
    results_new = Column(Integer, default=0)  # New (not duplicates)
    results_approved = Column(Integer, default=0)

    # Status
    status = Column(String(20), default='pending')  # pending, running, completed, failed
    error_message = Column(Text)

    # Timing
    started_at = Column(DateTime)
    completed_at = Column(DateTime)

    # Trigger
    triggered_by = Column(String(50))  # cron, manual, admin
    triggered_by_user = Column(Integer, ForeignKey('users.id'))

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    user = relationship('User', foreign_keys=[triggered_by_user])


# ============================================================
# ZOPK KNOWLEDGE BASE (AI-powered, with pgvector)
# ============================================================

class ZOPKKnowledgeChunk(Base):
    """
    Knowledge chunks extracted from approved ZOPK news articles.
    Each chunk is a semantically coherent piece of text with embedding vector
    for similarity search (RAG - Retrieval Augmented Generation).

    Best practices:
    - Chunk size: 500-1000 tokens with ~100 token overlap
    - Embedding model: gemini-embedding-001 (768 dimensions)
    """
    __tablename__ = 'zopk_knowledge_chunks'

    id = Column(Integer, primary_key=True)

    # Source tracking
    source_news_id = Column(Integer, ForeignKey('zopk_news.id'), nullable=False, index=True)

    # Chunk content
    content = Column(Text, nullable=False)  # The actual text chunk
    content_clean = Column(Text)  # Cleaned/normalized version for processing
    chunk_index = Column(Integer)  # Position in the original article (0, 1, 2...)
    token_count = Column(Integer)  # Approximate token count

    # Semantic embedding (pgvector)
    # Using 768 dimensions for Google gemini-embedding-001
    # Will be stored as: embedding vector(768)
    embedding = Column(Text)  # Stored as JSON string, converted to vector for queries

    # AI-extracted metadata
    chunk_type = Column(String(50))  # narrative, fact, quote, statistic, event, definition
    summary = Column(Text)  # 1-2 sentence summary
    keywords = Column(PG_ARRAY(String(100)) if not IS_SQLITE else Text)  # Extracted keywords
    language = Column(String(10), default='pl')  # pl, en

    # Context information
    context_date = Column(Date)  # Date the information refers to (not article date)
    context_location = Column(String(255))  # Geographic location if mentioned

    # Quality & relevance
    importance_score = Column(Integer)  # 1-5, how important this information is
    confidence_score = Column(Numeric(3, 2))  # 0.00-1.00, AI confidence in extraction

    # Moderation
    is_verified = Column(Boolean, default=False)  # Human verified
    verified_by = Column(Integer, ForeignKey('users.id'))
    verified_at = Column(DateTime)

    # Processing metadata
    extraction_model = Column(String(100))  # gemini-3-flash-preview, gpt-4, etc.
    extracted_at = Column(DateTime, default=datetime.now)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    source_news = relationship('ZOPKNews', backref='knowledge_chunks')
    verifier = relationship('User', foreign_keys=[verified_by])


class ZOPKKnowledgeEntity(Base):
    """
    Named entities extracted from ZOPK knowledge base.
    Entities are deduplicated and enriched across all sources.

    Types: company, person, place, organization, project, technology
    """
    __tablename__ = 'zopk_knowledge_entities'

    id = Column(Integer, primary_key=True)

    # Entity identification
    entity_type = Column(String(50), nullable=False, index=True)
    name = Column(String(255), nullable=False)
    normalized_name = Column(String(255), index=True)  # Lowercase, no special chars (for dedup)
    aliases = Column(PG_ARRAY(String(255)) if not IS_SQLITE else Text)  # Alternative names

    # Description
    description = Column(Text)  # AI-generated description
    short_description = Column(String(500))  # One-liner

    # Linking to existing data
    company_id = Column(Integer, ForeignKey('companies.id'))  # Link to Norda company if exists
    zopk_project_id = Column(Integer, ForeignKey('zopk_projects.id'))  # Link to ZOPK project
    external_url = Column(String(1000))  # Wikipedia, company website, etc.

    # Entity metadata (JSONB for flexibility)
    # Note: 'metadata' is reserved in SQLAlchemy, using 'entity_metadata'
    entity_metadata = Column(PG_JSONB if not IS_SQLITE else Text)  # {role: "CEO", founded: 2020, ...}

    # Statistics
    mentions_count = Column(Integer, default=0)
    first_mentioned_at = Column(DateTime)
    last_mentioned_at = Column(DateTime)

    # Embedding for entity similarity
    embedding = Column(Text)  # Entity description embedding

    # Quality
    is_verified = Column(Boolean, default=False)
    merged_into_id = Column(Integer, ForeignKey('zopk_knowledge_entities.id'))  # For deduplication

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', foreign_keys=[company_id])
    zopk_project = relationship('ZOPKProject', foreign_keys=[zopk_project_id])
    merged_into = relationship('ZOPKKnowledgeEntity', remote_side=[id], foreign_keys=[merged_into_id])


class ZOPKKnowledgeFact(Base):
    """
    Structured facts extracted from knowledge chunks.
    Facts are atomic, verifiable pieces of information.

    Examples:
    - "ZOPK otrzymał 500 mln PLN dofinansowania w 2024"
    - "Port Gdynia jest głównym partnerem projektu"
    - "Projekt zakłada utworzenie 5000 miejsc pracy"
    """
    __tablename__ = 'zopk_knowledge_facts'

    id = Column(Integer, primary_key=True)

    # Source
    source_chunk_id = Column(Integer, ForeignKey('zopk_knowledge_chunks.id'), nullable=False, index=True)
    source_news_id = Column(Integer, ForeignKey('zopk_news.id'), index=True)

    # Fact content
    fact_type = Column(String(50), nullable=False)  # statistic, event, statement, decision, milestone
    subject = Column(String(255))  # Who/what the fact is about
    predicate = Column(String(100))  # Action/relation type
    object = Column(Text)  # The actual information
    full_text = Column(Text, nullable=False)  # Complete fact as sentence

    # Structured data (for queryable facts)
    numeric_value = Column(Numeric(20, 2))  # If fact contains number
    numeric_unit = Column(String(50))  # PLN, EUR, jobs, MW, etc.
    date_value = Column(Date)  # If fact refers to specific date

    # Context
    context = Column(Text)  # Surrounding context for disambiguation
    citation = Column(Text)  # Original quote if applicable

    # Entities involved (denormalized for quick access)
    entities_involved = Column(PG_JSONB if not IS_SQLITE else Text)  # [{id: 1, name: "...", type: "company"}, ...]

    # Quality & verification
    confidence_score = Column(Numeric(3, 2))  # AI confidence
    is_verified = Column(Boolean, default=False)
    contradicts_fact_id = Column(Integer, ForeignKey('zopk_knowledge_facts.id'))  # If contradicted

    # Embedding for fact similarity
    embedding = Column(Text)

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    source_chunk = relationship('ZOPKKnowledgeChunk', backref='facts')
    source_news = relationship('ZOPKNews', backref='facts')
    contradicted_by = relationship('ZOPKKnowledgeFact', remote_side=[id], foreign_keys=[contradicts_fact_id])


class ZOPKKnowledgeEntityMention(Base):
    """
    Links between knowledge chunks and entities.
    Tracks where each entity is mentioned and in what context.
    """
    __tablename__ = 'zopk_knowledge_entity_mentions'

    id = Column(Integer, primary_key=True)

    chunk_id = Column(Integer, ForeignKey('zopk_knowledge_chunks.id'), nullable=False, index=True)
    entity_id = Column(Integer, ForeignKey('zopk_knowledge_entities.id'), nullable=False, index=True)

    # Mention details
    mention_text = Column(String(500))  # Exact text that matched the entity
    mention_type = Column(String(50))  # direct, reference, pronoun
    start_position = Column(Integer)  # Character position in chunk
    end_position = Column(Integer)

    # Context
    sentiment = Column(String(20))  # positive, neutral, negative
    role_in_context = Column(String(100))  # subject, object, beneficiary, partner

    confidence = Column(Numeric(3, 2))  # Entity linking confidence

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    chunk = relationship('ZOPKKnowledgeChunk', backref='entity_mentions')
    entity = relationship('ZOPKKnowledgeEntity', backref='mentions')

    __table_args__ = (
        UniqueConstraint('chunk_id', 'entity_id', 'start_position', name='uq_chunk_entity_position'),
    )


class ZOPKKnowledgeRelation(Base):
    """
    Relationships between entities discovered in the knowledge base.
    Forms a knowledge graph of ZOPK ecosystem.

    Examples:
    - Company A → "partner" → Company B
    - Person X → "CEO of" → Company Y
    - Project Z → "funded by" → Organization W
    """
    __tablename__ = 'zopk_knowledge_relations'

    id = Column(Integer, primary_key=True)

    # Entities involved
    entity_a_id = Column(Integer, ForeignKey('zopk_knowledge_entities.id'), nullable=False, index=True)
    entity_b_id = Column(Integer, ForeignKey('zopk_knowledge_entities.id'), nullable=False, index=True)

    # Relation definition
    relation_type = Column(String(100), nullable=False)  # partner, investor, supplier, competitor, subsidiary, employs
    relation_subtype = Column(String(100))  # More specific: strategic_partner, minority_investor
    is_bidirectional = Column(Boolean, default=False)  # True for "partners", False for "invests in"

    # Evidence
    source_chunk_id = Column(Integer, ForeignKey('zopk_knowledge_chunks.id'))
    source_fact_id = Column(Integer, ForeignKey('zopk_knowledge_facts.id'))
    evidence_text = Column(Text)  # Quote proving the relation

    # Temporal aspects
    valid_from = Column(Date)  # When relation started
    valid_until = Column(Date)  # When relation ended (NULL = still valid)
    is_current = Column(Boolean, default=True)

    # Strength & confidence
    strength = Column(Integer)  # 1-5, how strong the relation is
    confidence = Column(Numeric(3, 2))  # AI confidence in the relation
    mention_count = Column(Integer, default=1)  # How many times this relation was found

    # Quality
    is_verified = Column(Boolean, default=False)
    verified_by = Column(Integer, ForeignKey('users.id'))

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    entity_a = relationship('ZOPKKnowledgeEntity', foreign_keys=[entity_a_id], backref='relations_as_subject')
    entity_b = relationship('ZOPKKnowledgeEntity', foreign_keys=[entity_b_id], backref='relations_as_object')
    source_chunk = relationship('ZOPKKnowledgeChunk', backref='discovered_relations')
    source_fact = relationship('ZOPKKnowledgeFact', backref='relation_evidence')
    verifier = relationship('User', foreign_keys=[verified_by])

    __table_args__ = (
        UniqueConstraint('entity_a_id', 'entity_b_id', 'relation_type', name='uq_entity_relation'),
    )


class ZOPKKnowledgeExtractionJob(Base):
    """
    Tracks knowledge extraction jobs from approved articles.
    One job per article, tracks progress and results.
    """
    __tablename__ = 'zopk_knowledge_extraction_jobs'

    id = Column(Integer, primary_key=True)
    job_id = Column(String(100), unique=True, nullable=False, index=True)

    # Source
    news_id = Column(Integer, ForeignKey('zopk_news.id'), nullable=False, index=True)

    # Configuration
    extraction_model = Column(String(100))  # gemini-3-flash-preview
    chunk_size = Column(Integer, default=800)  # Target tokens per chunk
    chunk_overlap = Column(Integer, default=100)  # Overlap tokens

    # Results
    chunks_created = Column(Integer, default=0)
    entities_extracted = Column(Integer, default=0)
    facts_extracted = Column(Integer, default=0)
    relations_discovered = Column(Integer, default=0)

    # Costs
    tokens_used = Column(Integer, default=0)
    cost_cents = Column(Numeric(10, 4), default=0)

    # Status
    status = Column(String(20), default='pending')  # pending, running, completed, failed
    error_message = Column(Text)
    progress_percent = Column(Integer, default=0)

    # Timing
    started_at = Column(DateTime)
    completed_at = Column(DateTime)

    # Trigger
    triggered_by = Column(String(50))  # auto (on approval), manual, batch
    triggered_by_user = Column(Integer, ForeignKey('users.id'))

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    news = relationship('ZOPKNews', backref='extraction_jobs')
    user = relationship('User', foreign_keys=[triggered_by_user])


# ============================================================
# AI USAGE TRACKING MODELS
# ============================================================

class AIUsageLog(Base):
    """
    Individual AI API call logs.
    Tracks tokens, costs, and performance for each Gemini API request.
    """
    __tablename__ = 'ai_usage_logs'

    id = Column(Integer, primary_key=True)

    # Request info
    request_type = Column(String(50), nullable=False)  # chat, news_evaluation, user_creation, image_analysis
    model = Column(String(100), nullable=False)  # gemini-3-flash-preview, gemini-3-pro-preview, etc.

    # Token counts
    tokens_input = Column(Integer, default=0)
    tokens_output = Column(Integer, default=0)
    # Note: tokens_total is a generated column in PostgreSQL

    # Cost (in USD cents for precision)
    cost_cents = Column(Numeric(10, 4), default=0)

    # Context
    user_id = Column(Integer, ForeignKey('users.id'))
    company_id = Column(Integer, ForeignKey('companies.id'))
    related_entity_type = Column(String(50))  # zopk_news, chat_message, company, etc.
    related_entity_id = Column(Integer)

    # Request details
    prompt_length = Column(Integer)
    response_length = Column(Integer)
    response_time_ms = Column(Integer)  # How long the API call took

    # Status
    success = Column(Boolean, default=True)
    error_message = Column(Text)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    user = relationship('User', foreign_keys=[user_id])
    company = relationship('Company', foreign_keys=[company_id])


class AIUsageDaily(Base):
    """
    Pre-aggregated daily AI usage statistics.
    Auto-updated by PostgreSQL trigger on ai_usage_logs insert.
    """
    __tablename__ = 'ai_usage_daily'

    id = Column(Integer, primary_key=True)
    date = Column(Date, unique=True, nullable=False)

    # Request counts by type
    chat_requests = Column(Integer, default=0)
    news_evaluation_requests = Column(Integer, default=0)
    user_creation_requests = Column(Integer, default=0)
    image_analysis_requests = Column(Integer, default=0)
    other_requests = Column(Integer, default=0)
    total_requests = Column(Integer, default=0)

    # Token totals
    total_tokens_input = Column(Integer, default=0)
    total_tokens_output = Column(Integer, default=0)
    total_tokens = Column(Integer, default=0)

    # Cost (in USD cents)
    total_cost_cents = Column(Numeric(10, 4), default=0)

    # Performance
    avg_response_time_ms = Column(Integer)
    error_count = Column(Integer, default=0)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)


class AIRateLimit(Base):
    """
    Rate limit tracking for AI API quota management.
    """
    __tablename__ = 'ai_rate_limits'

    id = Column(Integer, primary_key=True)

    # Limit type
    limit_type = Column(String(50), nullable=False)  # daily, hourly, per_minute
    limit_scope = Column(String(50), nullable=False)  # global, user, ip
    scope_identifier = Column(String(255))  # user_id, ip address, or NULL for global

    # Limits
    max_requests = Column(Integer, nullable=False)
    current_requests = Column(Integer, default=0)

    # Reset
    reset_at = Column(DateTime, nullable=False)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    __table_args__ = (
        UniqueConstraint('limit_type', 'limit_scope', 'scope_identifier', name='uq_rate_limit'),
    )


# ============================================================
# KRS DATA - OSOBY POWIĄZANE Z FIRMAMI
# ============================================================

class Person(Base):
    """
    Osoby powiązane z firmami (zarząd, wspólnicy, prokurenci).
    Dane pobierane z odpisów KRS.
    """
    __tablename__ = 'people'

    id = Column(Integer, primary_key=True)
    pesel = Column(String(11), unique=True, nullable=True)  # NULL dla osób prawnych
    imiona = Column(String(255), nullable=False)
    nazwisko = Column(String(255), nullable=False)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company_roles = relationship('CompanyPerson', back_populates='person')

    def full_name(self):
        return f"{self.imiona} {self.nazwisko}"

    def __repr__(self):
        return f"<Person {self.full_name()}>"


class CompanyPerson(Base):
    """
    Relacja osoba-firma (zarząd, wspólnicy, prokurenci).
    Umożliwia śledzenie powiązań między osobami a firmami.
    """
    __tablename__ = 'company_people'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    person_id = Column(Integer, ForeignKey('people.id', ondelete='CASCADE'), nullable=False)

    # Rola w firmie
    role = Column(String(50), nullable=False)  # PREZES ZARZĄDU, CZŁONEK ZARZĄDU, WSPÓLNIK
    role_category = Column(String(20), nullable=False)  # zarzad, wspolnik, prokurent

    # Dane dodatkowe (dla wspólników)
    shares_count = Column(Integer)
    shares_value = Column(Numeric(12, 2))
    shares_percent = Column(Numeric(5, 2))

    # Źródło danych
    source = Column(String(100), default='ekrs.ms.gov.pl')
    source_document = Column(String(255))  # np. "odpis_pelny_0000725183.pdf"
    fetched_at = Column(DateTime)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', backref='people_roles')
    person = relationship('Person', back_populates='company_roles')

    __table_args__ = (
        UniqueConstraint('company_id', 'person_id', 'role_category', 'role', name='uq_company_person_role'),
    )

    def __repr__(self):
        return f"<CompanyPerson {self.person.full_name()} - {self.role} @ {self.company.name}>"


# ============================================================
# KRS AUDIT
# ============================================================

class KRSAudit(Base):
    """
    KRS audit history - tracks PDF downloads and data extraction.
    Each audit represents one extraction run from EKRS.
    """
    __tablename__ = 'krs_audits'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)

    # Audit timing
    audit_date = Column(DateTime, default=datetime.now, nullable=False, index=True)

    # PDF source info
    pdf_filename = Column(String(255))  # np. "odpis_pelny_0000882964.pdf"
    pdf_path = Column(Text)             # full path to stored PDF
    pdf_downloaded_at = Column(DateTime)

    # Extraction status
    status = Column(String(20), default='pending', index=True)  # pending, downloading, parsing, completed, error
    progress_percent = Column(Integer, default=0)
    progress_message = Column(Text)
    error_message = Column(Text)

    # Extracted data summary
    extracted_krs = Column(String(10))
    extracted_nazwa = Column(Text)
    extracted_nip = Column(String(10))
    extracted_regon = Column(String(14))
    extracted_forma_prawna = Column(String(255))
    extracted_data_rejestracji = Column(Date)
    extracted_kapital_zakladowy = Column(Numeric(15, 2))
    extracted_waluta = Column(String(3), default='PLN')
    extracted_liczba_udzialow = Column(Integer)
    extracted_sposob_reprezentacji = Column(Text)

    # Counts for quick stats
    zarzad_count = Column(Integer, default=0)
    wspolnicy_count = Column(Integer, default=0)
    prokurenci_count = Column(Integer, default=0)
    pkd_count = Column(Integer, default=0)

    # Full parsed data as JSON
    parsed_data = Column(JSONB)

    # Audit metadata
    audit_version = Column(String(20), default='1.0')
    audit_source = Column(String(50), default='ekrs.ms.gov.pl')

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationship
    company = relationship('Company', backref='krs_audits')

    def __repr__(self):
        return f'<KRSAudit company_id={self.company_id} status={self.status}>'

    @property
    def status_label(self):
        """Human-readable status label in Polish"""
        labels = {
            'pending': 'Oczekuje',
            'downloading': 'Pobieranie PDF',
            'parsing': 'Przetwarzanie',
            'completed': 'Ukończony',
            'error': 'Błąd'
        }
        return labels.get(self.status, self.status)


class CompanyPKD(Base):
    """
    PKD codes for companies (Polska Klasyfikacja Działalności).
    Multiple PKD codes per company allowed - one is marked as primary.
    """
    __tablename__ = 'company_pkd'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    pkd_code = Column(String(10), nullable=False, index=True)  # np. "62.03.Z"
    pkd_description = Column(Text)
    is_primary = Column(Boolean, default=False)  # przeważający PKD
    source = Column(String(50), default='ekrs')  # ekrs, ceidg

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationship
    company = relationship('Company', backref='pkd_codes')

    __table_args__ = (
        UniqueConstraint('company_id', 'pkd_code', name='uq_company_pkd'),
    )

    def __repr__(self):
        primary = ' (główny)' if self.is_primary else ''
        return f'<CompanyPKD {self.pkd_code}{primary}>'


class CompanyFinancialReport(Base):
    """
    Financial reports (sprawozdania finansowe) filed with KRS.
    Tracks report periods, filing dates, and key financial figures.
    """
    __tablename__ = 'company_financial_reports'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False, index=True)
    period_start = Column(Date)
    period_end = Column(Date)
    filed_at = Column(Date)
    report_type = Column(String(50), default='annual')  # annual, quarterly
    source = Column(String(50), default='ekrs')

    # Financial data (in PLN)
    revenue = Column(Numeric(15, 2))           # Przychody netto ze sprzedaży
    operating_profit = Column(Numeric(15, 2))   # Zysk/strata z działalności operacyjnej
    net_profit = Column(Numeric(15, 2))         # Zysk/strata netto
    total_assets = Column(Numeric(15, 2))       # Suma aktywów
    equity = Column(Numeric(15, 2))             # Kapitał własny
    liabilities = Column(Numeric(15, 2))        # Zobowiązania
    employees_count = Column(Integer)           # Średnia liczba zatrudnionych

    # Company size classification: micro, small, medium, large
    size_class = Column(String(20))

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationship
    company = relationship('Company', backref='financial_reports')

    __table_args__ = (
        UniqueConstraint('company_id', 'period_start', 'period_end', 'report_type', name='uq_company_financial_report'),
    )

    def classify_size(self):
        """Classify company size based on EU criteria (revenue + employees + assets).
        micro: <2M EUR revenue, <10 employees, <2M EUR assets
        small: <10M EUR revenue, <50 employees, <10M EUR assets
        medium: <50M EUR revenue, <250 employees, <43M EUR assets
        large: above medium
        EUR/PLN ~4.3
        """
        rev = float(self.revenue or 0)
        emp = self.employees_count or 0
        assets = float(self.total_assets or 0)

        # PLN thresholds (approx EUR * 4.3)
        if rev < 8_600_000 and emp < 10 and assets < 8_600_000:
            return 'micro'
        elif rev < 43_000_000 and emp < 50 and assets < 43_000_000:
            return 'small'
        elif rev < 215_000_000 and emp < 250 and assets < 185_000_000:
            return 'medium'
        else:
            return 'large'

    def __repr__(self):
        return f'<CompanyFinancialReport {self.period_start} - {self.period_end}>'


# ============================================================
# USER BLOCKS - BLOKOWANIE UŻYTKOWNIKÓW
# ============================================================

class UserBlock(Base):
    """
    Blokowanie użytkowników - zablokowany użytkownik nie może wysyłać wiadomości.
    """
    __tablename__ = 'user_blocks'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    blocked_user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    created_at = Column(DateTime, default=datetime.now)
    reason = Column(Text, nullable=True)  # optional reason

    # Relationships
    user = relationship('User', foreign_keys=[user_id], backref='blocks_created')
    blocked_user = relationship('User', foreign_keys=[blocked_user_id], backref='blocked_by')

    def __repr__(self):
        return f'<UserBlock {self.user_id} -> {self.blocked_user_id}>'


# ============================================================
# USER ANALYTICS - SESJE I AKTYWNOŚĆ
# ============================================================

class UserSession(Base):
    """
    Sesje użytkowników portalu.
    Śledzi czas trwania sesji, urządzenie, lokalizację i aktywność.
    """
    __tablename__ = 'user_sessions'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=True)
    session_id = Column(String(100), unique=True, nullable=False, index=True)

    # Czas sesji
    started_at = Column(DateTime, nullable=False, default=datetime.now)
    ended_at = Column(DateTime, nullable=True)
    last_activity_at = Column(DateTime, nullable=False, default=datetime.now)
    duration_seconds = Column(Integer, nullable=True)

    # Urządzenie
    ip_address = Column(String(45), nullable=True)
    user_agent = Column(Text, nullable=True)
    device_type = Column(String(20), nullable=True)  # desktop, mobile, tablet
    browser = Column(String(50), nullable=True)
    browser_version = Column(String(20), nullable=True)
    os = Column(String(50), nullable=True)
    os_version = Column(String(20), nullable=True)

    # Lokalizacja (z IP)
    country = Column(String(100), nullable=True)
    city = Column(String(100), nullable=True)
    region = Column(String(100), nullable=True)

    # Metryki sesji
    page_views_count = Column(Integer, default=0)
    clicks_count = Column(Integer, default=0)

    # Bot detection
    is_bot = Column(Boolean, default=False)

    # PWA detection
    is_pwa = Column(Boolean, default=False)

    # UTM Parameters (kampanie marketingowe)
    utm_source = Column(String(255), nullable=True)  # google, facebook, newsletter
    utm_medium = Column(String(255), nullable=True)  # cpc, email, social, organic
    utm_campaign = Column(String(255), nullable=True)  # nazwa kampanii
    utm_term = Column(String(255), nullable=True)  # słowo kluczowe (PPC)
    utm_content = Column(String(255), nullable=True)  # wariant reklamy

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    user = relationship('User', backref='analytics_sessions')
    page_views = relationship('PageView', back_populates='session', cascade='all, delete-orphan')

    def __repr__(self):
        return f"<UserSession {self.session_id[:8]}... user={self.user_id}>"


class PageView(Base):
    """
    Wyświetlenia stron przez użytkowników.
    Śledzi odwiedzone strony i czas spędzony na każdej z nich.
    """
    __tablename__ = 'page_views'

    id = Column(Integer, primary_key=True)
    session_id = Column(Integer, ForeignKey('user_sessions.id', ondelete='CASCADE'), nullable=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    # Strona
    url = Column(String(2000), nullable=False)
    path = Column(String(500), nullable=False, index=True)
    page_title = Column(String(500), nullable=True)
    referrer = Column(String(2000), nullable=True)

    # Czas
    viewed_at = Column(DateTime, nullable=False, default=datetime.now, index=True)
    time_on_page_seconds = Column(Integer, nullable=True)

    # Scroll depth (%)
    scroll_depth_percent = Column(Integer, nullable=True)  # 0-100

    # Performance metrics (Web Vitals)
    dom_content_loaded_ms = Column(Integer, nullable=True)
    load_time_ms = Column(Integer, nullable=True)
    first_paint_ms = Column(Integer, nullable=True)
    first_contentful_paint_ms = Column(Integer, nullable=True)

    # Kontekst
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='SET NULL'), nullable=True)

    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    session = relationship('UserSession', back_populates='page_views')
    clicks = relationship('UserClick', back_populates='page_view', cascade='all, delete-orphan')

    def __repr__(self):
        return f"<PageView {self.path}>"


class UserClick(Base):
    """
    Kliknięcia elementów na stronach.
    Śledzi interakcje użytkowników z elementami UI.
    """
    __tablename__ = 'user_clicks'

    id = Column(Integer, primary_key=True)
    session_id = Column(Integer, ForeignKey('user_sessions.id', ondelete='CASCADE'), nullable=True)
    page_view_id = Column(Integer, ForeignKey('page_views.id', ondelete='CASCADE'), nullable=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    # Element kliknięty
    element_type = Column(String(50), nullable=True)  # button, link, card, nav, form
    element_id = Column(String(100), nullable=True)
    element_text = Column(String(255), nullable=True)
    element_class = Column(String(500), nullable=True)
    target_url = Column(String(2000), nullable=True)

    # Pozycja kliknięcia
    x_position = Column(Integer, nullable=True)
    y_position = Column(Integer, nullable=True)

    clicked_at = Column(DateTime, nullable=False, default=datetime.now, index=True)

    # Relationships
    page_view = relationship('PageView', back_populates='clicks')

    def __repr__(self):
        return f"<UserClick {self.element_type} at {self.clicked_at}>"


class AnalyticsDaily(Base):
    """
    Dzienne statystyki agregowane.
    Automatycznie aktualizowane przez trigger PostgreSQL.
    """
    __tablename__ = 'analytics_daily'

    id = Column(Integer, primary_key=True)
    date = Column(Date, unique=True, nullable=False, index=True)

    # Sesje
    total_sessions = Column(Integer, default=0)
    unique_users = Column(Integer, default=0)
    new_users = Column(Integer, default=0)
    returning_users = Column(Integer, default=0)
    anonymous_sessions = Column(Integer, default=0)

    # Aktywność
    total_page_views = Column(Integer, default=0)
    total_clicks = Column(Integer, default=0)
    avg_session_duration_seconds = Column(Integer, nullable=True)
    avg_pages_per_session = Column(Numeric(5, 2), nullable=True)

    # Urządzenia
    desktop_sessions = Column(Integer, default=0)
    mobile_sessions = Column(Integer, default=0)
    tablet_sessions = Column(Integer, default=0)

    # Engagement
    bounce_rate = Column(Numeric(5, 2), nullable=True)

    # Nowe metryki (Analytics Expansion 2026-01-30)
    conversions_count = Column(Integer, default=0)
    searches_count = Column(Integer, default=0)
    searches_no_results = Column(Integer, default=0)
    avg_scroll_depth = Column(Numeric(5, 2), nullable=True)
    js_errors_count = Column(Integer, default=0)

    # Rozkłady (JSONB)
    utm_breakdown = Column(JSONBType, nullable=True)  # {"google": 10, "facebook": 5}
    conversions_breakdown = Column(JSONBType, nullable=True)  # {"register": 2, "contact_click": 15}

    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return f"<AnalyticsDaily {self.date}>"


class PopularPagesDaily(Base):
    """
    Popularne strony - dzienne agregaty.
    """
    __tablename__ = 'popular_pages_daily'

    id = Column(Integer, primary_key=True)
    date = Column(Date, nullable=False, index=True)
    path = Column(String(500), nullable=False)
    page_title = Column(String(500), nullable=True)

    views_count = Column(Integer, default=0)
    unique_visitors = Column(Integer, default=0)
    avg_time_seconds = Column(Integer, nullable=True)

    __table_args__ = (
        UniqueConstraint('date', 'path', name='uq_popular_pages_date_path'),
    )

    def __repr__(self):
        return f"<PopularPagesDaily {self.date} {self.path}>"


class SearchQuery(Base):
    """
    Historia wyszukiwań użytkowników w portalu.
    Śledzi zapytania, wyniki i interakcje.
    Created: 2026-01-30
    """
    __tablename__ = 'search_queries'

    id = Column(Integer, primary_key=True)
    session_id = Column(Integer, ForeignKey('user_sessions.id', ondelete='SET NULL'), nullable=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    # Zapytanie
    query = Column(String(500), nullable=False)
    query_normalized = Column(String(500), nullable=True)  # lowercase, bez znaków specjalnych

    # Wyniki
    results_count = Column(Integer, default=0)
    has_results = Column(Boolean, default=True)

    # Interakcja z wynikami
    clicked_result_position = Column(Integer, nullable=True)  # 1-based
    clicked_company_id = Column(Integer, ForeignKey('companies.id', ondelete='SET NULL'), nullable=True)

    # Kontekst
    search_type = Column(String(50), default='main')  # main, chat, autocomplete
    filters_used = Column(JSONBType, nullable=True)  # {"category": "IT", "city": "Wejherowo"}

    # Timing
    searched_at = Column(DateTime, nullable=False, default=datetime.now)
    time_to_click_ms = Column(Integer, nullable=True)

    created_at = Column(DateTime, default=datetime.now)

    def __repr__(self):
        return f"<SearchQuery '{self.query[:30]}...' results={self.results_count}>"


class ConversionEvent(Base):
    """
    Kluczowe konwersje: rejestracje, kontakty z firmami, RSVP na wydarzenia.
    Created: 2026-01-30
    """
    __tablename__ = 'conversion_events'

    id = Column(Integer, primary_key=True)
    session_id = Column(Integer, ForeignKey('user_sessions.id', ondelete='SET NULL'), nullable=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    # Typ konwersji
    event_type = Column(String(50), nullable=False)  # register, login, contact_click, rsvp, message, classified
    event_category = Column(String(50), nullable=True)  # engagement, acquisition, activation

    # Kontekst
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='SET NULL'), nullable=True)
    target_type = Column(String(50), nullable=True)  # email, phone, website, rsvp_event
    target_value = Column(String(500), nullable=True)

    # Źródło konwersji
    source_page = Column(String(500), nullable=True)
    referrer = Column(String(500), nullable=True)

    # Dodatkowe dane
    event_metadata = Column(JSONBType, nullable=True)

    # Timing
    converted_at = Column(DateTime, nullable=False, default=datetime.now)
    created_at = Column(DateTime, default=datetime.now)

    # Relationships
    company = relationship('Company', backref='conversion_events')

    def __repr__(self):
        return f"<ConversionEvent {self.event_type} at {self.converted_at}>"


class JSError(Base):
    """
    Błędy JavaScript zgłaszane z przeglądarek użytkowników.
    Created: 2026-01-30
    """
    __tablename__ = 'js_errors'

    id = Column(Integer, primary_key=True)
    session_id = Column(Integer, ForeignKey('user_sessions.id', ondelete='SET NULL'), nullable=True)

    # Błąd
    message = Column(Text, nullable=False)
    source = Column(String(500), nullable=True)  # URL pliku JS
    lineno = Column(Integer, nullable=True)
    colno = Column(Integer, nullable=True)
    stack = Column(Text, nullable=True)

    # Kontekst
    url = Column(String(2000), nullable=True)
    user_agent = Column(String(500), nullable=True)

    # Agregacja
    error_hash = Column(String(64), nullable=True)  # SHA256 dla grupowania

    occurred_at = Column(DateTime, nullable=False, default=datetime.now)
    created_at = Column(DateTime, default=datetime.now)

    def __repr__(self):
        return f"<JSError '{self.message[:50]}...'>"


class PopularSearchesDaily(Base):
    """
    Popularne wyszukiwania - dzienne agregaty.
    Created: 2026-01-30
    """
    __tablename__ = 'popular_searches_daily'

    id = Column(Integer, primary_key=True)
    date = Column(Date, nullable=False, index=True)
    query_normalized = Column(String(500), nullable=False)

    search_count = Column(Integer, default=0)
    unique_users = Column(Integer, default=0)
    click_count = Column(Integer, default=0)
    avg_results_count = Column(Numeric(10, 2), nullable=True)

    __table_args__ = (
        UniqueConstraint('date', 'query_normalized', name='uq_popular_searches_date_query'),
    )

    def __repr__(self):
        return f"<PopularSearchesDaily {self.date} '{self.query_normalized}'>"


class HourlyActivity(Base):
    """
    Aktywność wg godziny - dla analizy wzorców czasowych.
    Created: 2026-01-30
    """
    __tablename__ = 'hourly_activity'

    id = Column(Integer, primary_key=True)
    date = Column(Date, nullable=False, index=True)
    hour = Column(Integer, nullable=False)  # 0-23

    sessions_count = Column(Integer, default=0)
    page_views_count = Column(Integer, default=0)
    unique_users = Column(Integer, default=0)

    __table_args__ = (
        UniqueConstraint('date', 'hour', name='uq_hourly_activity_date_hour'),
    )

    def __repr__(self):
        return f"<HourlyActivity {self.date} {self.hour}:00>"


# ============================================================
# EMAIL LOGGING
# ============================================================

class EmailLog(Base):
    """
    Log wszystkich wysłanych emaili systemowych.

    Śledzi:
    - Emaile rejestracyjne (weryfikacja)
    - Emaile resetowania hasła
    - Powiadomienia systemowe
    - Status dostarczenia

    Created: 2026-01-14
    """
    __tablename__ = 'email_logs'

    id = Column(Integer, primary_key=True)

    # Dane emaila
    email_type = Column(String(50), nullable=False, index=True)  # welcome, password_reset, notification
    recipient_email = Column(String(255), nullable=False, index=True)
    recipient_name = Column(String(255), nullable=True)
    subject = Column(String(500), nullable=False)

    # Powiązanie z użytkownikiem (opcjonalne)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    # Status
    status = Column(String(20), default='pending', index=True)  # pending, sent, failed
    error_message = Column(Text, nullable=True)

    # Metadane
    sender_email = Column(String(255), nullable=True)
    ip_address = Column(String(45), nullable=True)  # IP requestu (jeśli dostępne)

    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow)
    sent_at = Column(DateTime, nullable=True)

    # Relacje
    user = relationship('User', backref='email_logs')

    def __repr__(self):
        return f"<EmailLog {self.id} {self.email_type} -> {self.recipient_email} ({self.status})>"


# ============================================================
# SECURITY & AUDIT
# ============================================================

class AuditLog(Base):
    """
    Audit log dla śledzenia działań administracyjnych.

    Śledzi wszystkie wrażliwe operacje wykonywane przez adminów:
    - Moderacja newsów (approve/reject)
    - Zmiany składek członkowskich
    - Edycja profili firm
    - Zmiany uprawnień użytkowników
    - Operacje na wydarzeniach

    Created: 2026-01-14
    """
    __tablename__ = 'audit_logs'

    id = Column(Integer, primary_key=True)

    # Kto wykonał akcję
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    user_email = Column(String(255), nullable=False)  # Zachowane nawet po usunięciu usera

    # Co zostało wykonane
    action = Column(String(100), nullable=False, index=True)  # np. 'news.approve', 'company.edit', 'user.ban'
    entity_type = Column(String(50), nullable=False, index=True)  # np. 'news', 'company', 'user', 'event'
    entity_id = Column(Integer, nullable=True)  # ID encji której dotyczy akcja
    entity_name = Column(String(255), nullable=True)  # Nazwa encji (dla czytelności)

    # Szczegóły
    details = Column(JSONBType, nullable=True)  # Dodatkowe dane: old_value, new_value, reason

    # Kontekst requestu
    ip_address = Column(String(45), nullable=True)
    user_agent = Column(String(500), nullable=True)
    request_path = Column(String(500), nullable=True)

    # Timestamp
    created_at = Column(DateTime, default=datetime.utcnow, index=True)

    # Relacje
    user = relationship('User', backref='audit_logs')

    def __repr__(self):
        return f"<AuditLog {self.id} {self.user_email} {self.action} on {self.entity_type}:{self.entity_id}>"


class SecurityAlert(Base):
    """
    Alerty bezpieczeństwa wysyłane emailem.

    Śledzi:
    - Zbyt wiele nieudanych logowań
    - Próby dostępu do honeypotów
    - Podejrzane wzorce aktywności
    - Blokady kont

    Created: 2026-01-14
    """
    __tablename__ = 'security_alerts'

    id = Column(Integer, primary_key=True)

    # Typ alertu
    alert_type = Column(String(50), nullable=False, index=True)
    # Typy: 'brute_force', 'honeypot_hit', 'account_locked', 'suspicious_activity', 'geo_blocked'

    severity = Column(String(20), nullable=False, default='medium')  # low, medium, high, critical

    # Kontekst
    ip_address = Column(String(45), nullable=True, index=True)
    user_email = Column(String(255), nullable=True)
    details = Column(JSONBType, nullable=True)  # Dodatkowe dane

    # Status alertu
    status = Column(String(20), default='new', index=True)  # new, acknowledged, resolved
    acknowledged_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    acknowledged_at = Column(DateTime, nullable=True)
    resolution_note = Column(Text, nullable=True)

    # Email notification
    email_sent = Column(Boolean, default=False)
    email_sent_at = Column(DateTime, nullable=True)

    # Timestamps
    created_at = Column(DateTime, default=datetime.utcnow, index=True)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relacje
    acknowledger = relationship('User', foreign_keys=[acknowledged_by])

    def __repr__(self):
        return f"<SecurityAlert {self.id} {self.alert_type} ({self.severity}) from {self.ip_address}>"


# ============================================================
# ANNOUNCEMENTS (Ogłoszenia dla członków)
# ============================================================

class Announcement(Base):
    """
    Ogłoszenia i aktualności dla członków Norda Biznes.
    Obsługuje różne kategorie: ogólne, wydarzenia, okazje biznesowe, od członków.
    """
    __tablename__ = 'announcements'

    id = Column(Integer, primary_key=True)
    title = Column(String(300), nullable=False)
    slug = Column(String(300), unique=True, index=True)
    excerpt = Column(String(500))  # Krótki opis do listy
    content = Column(Text, nullable=False)  # Pełna treść (HTML)

    # Kategoryzacja (obsługa wielu kategorii)
    categories = Column(ARRAY(String), default=[])  # Tablica kategorii
    # Wartości: internal, external, event, opportunity, partnership
    # Stare pole dla kompatybilności wstecznej (do usunięcia po migracji)
    category = Column(String(50), default='internal')

    # Media
    image_url = Column(String(1000))
    external_link = Column(String(1000))  # Link do zewnętrznego źródła

    # Publikacja
    status = Column(String(20), default='draft', index=True)
    # Wartości: draft, published, archived
    published_at = Column(DateTime)
    expires_at = Column(DateTime)  # Opcjonalne wygaśnięcie

    # Wyróżnienie
    is_featured = Column(Boolean, default=False)
    is_pinned = Column(Boolean, default=False)

    # Statystyki
    views_count = Column(Integer, default=0)

    # Audyt
    created_by = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    author = relationship('User', foreign_keys=[created_by])
    readers = relationship('AnnouncementRead', back_populates='announcement', cascade='all, delete-orphan')

    # Constants
    CATEGORIES = ['internal', 'external', 'event', 'opportunity', 'partnership', 'pej']
    CATEGORY_LABELS = {
        'internal': 'Wewnętrzne',
        'external': 'Zewnętrzne',
        'event': 'Wydarzenie',
        'opportunity': 'Okazja biznesowa',
        'partnership': 'Partnerstwo',
        'pej': 'PEJ / Energetyka jądrowa'
    }
    STATUSES = ['draft', 'published', 'archived']
    STATUS_LABELS = {
        'draft': 'Szkic',
        'published': 'Opublikowane',
        'archived': 'Zarchiwizowane'
    }

    @property
    def category_label(self):
        """Zwraca polską etykietę pierwszej kategorii (kompatybilność wsteczna)"""
        if self.categories:
            return self.CATEGORY_LABELS.get(self.categories[0], self.categories[0])
        return self.CATEGORY_LABELS.get(self.category, self.category)

    @property
    def categories_labels(self):
        """Zwraca listę polskich etykiet wszystkich kategorii"""
        if self.categories:
            return [self.CATEGORY_LABELS.get(cat, cat) for cat in self.categories]
        return [self.CATEGORY_LABELS.get(self.category, self.category)]

    def has_category(self, category):
        """Sprawdza czy ogłoszenie ma daną kategorię"""
        if self.categories:
            return category in self.categories
        return self.category == category

    @property
    def status_label(self):
        """Zwraca polską etykietę statusu"""
        return self.STATUS_LABELS.get(self.status, self.status)

    @property
    def is_active(self):
        """Sprawdza czy ogłoszenie jest aktywne (opublikowane i nie wygasło)"""
        if self.status != 'published':
            return False
        if self.expires_at and self.expires_at < datetime.now():
            return False
        return True

    def __repr__(self):
        return f"<Announcement {self.id} '{self.title[:50]}' ({self.status})>"


class AnnouncementRead(Base):
    """
    Śledzenie odczytów ogłoszeń (seen by).
    Zapisuje kto i kiedy przeczytał dane ogłoszenie.
    """
    __tablename__ = 'announcement_reads'

    id = Column(Integer, primary_key=True)
    announcement_id = Column(Integer, ForeignKey('announcements.id', ondelete='CASCADE'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    read_at = Column(DateTime, default=datetime.now)

    # Relationships
    announcement = relationship('Announcement', back_populates='readers')
    user = relationship('User')

    # Unique constraint
    __table_args__ = (
        UniqueConstraint('announcement_id', 'user_id', name='uq_announcement_user_read'),
    )

    def __repr__(self):
        return f"<AnnouncementRead announcement={self.announcement_id} user={self.user_id}>"


# ============================================================
# EXTERNAL CONTACTS (Kontakty zewnętrzne)
# ============================================================

class ExternalContact(Base):
    """
    Baza kontaktów zewnętrznych - urzędy, instytucje, partnerzy projektów.
    Dostępna dla wszystkich zalogowanych członków Norda Biznes.
    """
    __tablename__ = 'external_contacts'

    id = Column(Integer, primary_key=True)

    # Dane osobowe
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    position = Column(String(200))  # Stanowisko (opcjonalne)
    photo_url = Column(String(500))  # Zdjęcie osoby (opcjonalne)

    # Dane kontaktowe
    phone = Column(String(50))
    phone_secondary = Column(String(50))  # Drugi numer telefonu
    email = Column(String(255))
    website = Column(String(500))  # Strona osobista/wizytówka

    # Social Media
    linkedin_url = Column(String(500))
    facebook_url = Column(String(500))
    twitter_url = Column(String(500))

    # Organizacja
    organization_name = Column(String(300), nullable=False)
    organization_type = Column(String(50), default='other')
    # Typy: government (urząd), agency (agencja), company (firma), ngo (organizacja), university (uczelnia), other

    organization_address = Column(String(500))
    organization_website = Column(String(500))
    organization_logo_url = Column(String(500))

    # Kontekst/Projekt
    project_name = Column(String(300))  # Nazwa projektu (Tytani, EJ Choczewo, itp.)
    project_description = Column(Text)  # Krótki opis kontekstu

    # Źródło kontaktu
    source_type = Column(String(50))  # announcement, forum_post, manual
    source_id = Column(Integer)  # ID ogłoszenia lub wpisu (opcjonalne)
    source_url = Column(String(500))  # URL do źródła

    # Powiązane linki (artykuły, strony, dokumenty) - JSON array
    # Format: [{"title": "Artykuł o...", "url": "https://...", "type": "article"}, ...]
    related_links = Column(PG_JSONB, default=list)

    # Tagi do wyszukiwania
    tags = Column(String(500))  # Tagi oddzielone przecinkami

    # Notatki
    notes = Column(Text)

    # Audyt
    created_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Status
    is_active = Column(Boolean, default=True)
    is_verified = Column(Boolean, default=False)  # Zweryfikowany przez admina/moderatora

    # Relationships
    creator = relationship('User', foreign_keys=[created_by])

    # Constants
    ORGANIZATION_TYPES = ['government', 'agency', 'company', 'ngo', 'university', 'other']
    ORGANIZATION_TYPE_LABELS = {
        'government': 'Urząd',
        'agency': 'Agencja',
        'company': 'Firma',
        'ngo': 'Organizacja pozarządowa',
        'university': 'Uczelnia',
        'other': 'Inne'
    }
    SOURCE_TYPES = ['announcement', 'forum_post', 'manual']

    @property
    def full_name(self):
        return f"{self.first_name} {self.last_name}"

    @property
    def organization_type_label(self):
        return self.ORGANIZATION_TYPE_LABELS.get(self.organization_type, self.organization_type)

    @property
    def tags_list(self):
        """Zwraca tagi jako listę."""
        if not self.tags:
            return []
        return [tag.strip() for tag in self.tags.split(',') if tag.strip()]

    @property
    def has_social_media(self):
        """Sprawdza czy kontakt ma jakiekolwiek social media."""
        return bool(self.linkedin_url or self.facebook_url or self.twitter_url)

    @property
    def has_contact_info(self):
        """Sprawdza czy kontakt ma dane kontaktowe."""
        return bool(self.phone or self.email or self.website)

    def __repr__(self):
        return f"<ExternalContact {self.full_name} @ {self.organization_name}>"


# ============================================================
# ZOPK MILESTONES (Timeline)
# ============================================================

class ZOPKMilestone(Base):
    """
    Kamienie milowe projektu ZOPK dla wizualizacji timeline.
    """
    __tablename__ = 'zopk_milestones'

    id = Column(Integer, primary_key=True)
    title = Column(String(500), nullable=False)
    description = Column(Text)

    # Kategoria: nuclear, offshore, infrastructure, defense, other
    category = Column(String(50), default='other')

    # Daty
    target_date = Column(Date)  # Planowana data
    actual_date = Column(Date)  # Rzeczywista data (jeśli zakończone)

    # Status: planned, in_progress, completed, delayed, cancelled
    status = Column(String(20), default='planned')

    # Źródło informacji
    source_url = Column(String(1000))
    source_news_id = Column(Integer, ForeignKey('zopk_news.id'))

    # Wyświetlanie
    icon = Column(String(50))  # emoji lub ikona
    color = Column(String(20))  # kolor dla timeline
    is_featured = Column(Boolean, default=False)
    is_verified = Column(Boolean, default=True)  # Czy zatwierdzony do wyświetlenia

    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    source_news = relationship('ZOPKNews', backref='milestones')


# ============================================================
# MEMBERSHIP APPLICATION SYSTEM
# ============================================================

class MembershipApplication(Base):
    """
    Deklaracja przystąpienia do Izby NORDA.
    Pełny formularz online z workflow zatwierdzania.
    """
    __tablename__ = 'membership_applications'

    id = Column(Integer, primary_key=True)

    # Kto złożył
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)

    # Status workflow (max 50 znaków)
    # draft, submitted, under_review, pending_user_approval, changes_requested, approved, rejected
    status = Column(String(50), nullable=False, default='draft')

    # Dane firmy (strona 1 - Deklaracja)
    company_name = Column(String(255), nullable=False)
    nip = Column(String(10), nullable=False)
    address_postal_code = Column(String(6))
    address_city = Column(String(100))
    address_street = Column(String(200))
    address_number = Column(String(50))

    # Delegaci do Walnego Zgromadzenia
    delegate_1 = Column(String(150))
    delegate_2 = Column(String(150))
    delegate_3 = Column(String(150))

    # Kontakt (strona 2 - Karta Informacyjna)
    website = Column(String(255))
    email = Column(String(255), nullable=False)
    phone = Column(String(30))
    short_name = Column(String(100))

    # Informacje dodatkowe
    description = Column(Text)
    founded_date = Column(Date)
    employee_count = Column(Integer)
    show_employee_count = Column(Boolean, default=False)
    annual_revenue = Column(String(50))
    related_companies = Column(JSONBType)  # ["Firma A", "Firma B"]

    # Sekcje tematyczne
    sections = Column(JSONBType, nullable=False, default=[])
    sections_other = Column(String(200))

    # Typ działalności
    business_type = Column(String(50), nullable=False, default='sp_z_oo')
    business_type_other = Column(String(100))

    # Zgody RODO
    consent_email = Column(Boolean, nullable=False, default=False)
    consent_email_address = Column(String(255))
    consent_sms = Column(Boolean, default=False)
    consent_sms_phone = Column(String(30))

    # Oświadczenie końcowe
    declaration_accepted = Column(Boolean, nullable=False, default=False)
    declaration_accepted_at = Column(DateTime)
    declaration_ip_address = Column(String(45))

    # Dane z rejestru KRS/CEIDG
    registry_source = Column(String(20))  # 'KRS', 'CEIDG', 'manual'
    registry_data = Column(JSONBType)
    krs_number = Column(String(10))
    regon = Column(String(14))

    # Workflow timestamps
    created_at = Column(DateTime, nullable=False, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    submitted_at = Column(DateTime)
    reviewed_at = Column(DateTime)
    reviewed_by_id = Column(Integer, ForeignKey('users.id'))
    review_comment = Column(Text)

    # Proposed changes workflow (admin → user approval)
    proposed_changes = Column(JSONBType)  # {"field": {"old": x, "new": y}, ...}
    proposed_changes_at = Column(DateTime)
    proposed_changes_by_id = Column(Integer, ForeignKey('users.id'))
    proposed_changes_comment = Column(Text)

    # User response to proposed changes
    user_changes_accepted_at = Column(DateTime)  # When user accepted/rejected
    user_changes_action = Column(String(20))  # 'accepted' or 'rejected'

    # Full workflow history (for timeline display on both user and admin side)
    workflow_history = Column(JSONBType, default=[])  # [{event, timestamp, user_id, user_name, details}]

    # Po zatwierdzeniu
    company_id = Column(Integer, ForeignKey('companies.id'))
    member_number = Column(String(20))

    # Relationships
    user = relationship('User', foreign_keys=[user_id], backref='membership_applications')
    reviewed_by = relationship('User', foreign_keys=[reviewed_by_id])
    proposed_by = relationship('User', foreign_keys=[proposed_changes_by_id])
    company = relationship('Company')

    # Constants
    STATUS_CHOICES = [
        ('draft', 'Szkic'),
        ('submitted', 'Wysłane'),
        ('under_review', 'W trakcie rozpatrywania'),
        ('pending_user_approval', 'Oczekuje na akceptację użytkownika'),
        ('changes_requested', 'Prośba o poprawki'),
        ('approved', 'Zatwierdzone'),
        ('rejected', 'Odrzucone'),
    ]

    SECTION_CHOICES = [
        ('turystyka', 'Turystyka i hotelarstwo'),
        ('szkolnictwo', 'Szkolnictwo, praktyki, zatrudnienie'),
        ('budownictwo', 'Budownictwo'),
        ('produkcja', 'Produkcja'),
        ('handel', 'Handel'),
        ('uslugi', 'Usługi'),
        ('inna', 'Inna'),
    ]

    BUSINESS_TYPE_CHOICES = [
        ('jdg', 'Jednoosobowa działalność gospodarcza'),
        ('spolka_cywilna', 'Spółka cywilna'),
        ('spolka_jawna', 'Spółka jawna'),
        ('spolka_partnerska', 'Spółka partnerska'),
        ('spolka_komandytowa', 'Spółka komandytowa'),
        ('spolka_komandytowo_akcyjna', 'Spółka komandytowo-akcyjna'),
        ('sp_z_oo_komandytowa', 'Sp. z o.o. komandytowa'),
        ('spolka_akcyjna', 'Spółka akcyjna'),
        ('sp_z_oo', 'Spółka z o.o.'),
        ('inna', 'Inna'),
    ]

    @property
    def status_label(self):
        return dict(self.STATUS_CHOICES).get(self.status, self.status)

    @property
    def business_type_label(self):
        return dict(self.BUSINESS_TYPE_CHOICES).get(self.business_type, self.business_type)

    @property
    def sections_labels(self):
        section_map = dict(self.SECTION_CHOICES)
        if not self.sections:
            return []
        return [section_map.get(s, s) for s in self.sections]

    @property
    def full_address(self):
        parts = []
        if self.address_postal_code:
            parts.append(self.address_postal_code)
        if self.address_city:
            parts.append(self.address_city)
        if parts:
            address = ' '.join(parts)
            if self.address_street:
                address += f', {self.address_street}'
                if self.address_number:
                    address += f' {self.address_number}'
            return address
        return None

    def __repr__(self):
        return f"<MembershipApplication {self.id} [{self.status}] {self.company_name}>"


class CompanyDataRequest(Base):
    """
    Zgłoszenie uzupełnienia danych firmy.
    Prostsza funkcjonalność dla istniejących członków bez pełnych danych.
    """
    __tablename__ = 'company_data_requests'

    id = Column(Integer, primary_key=True)

    # Typ zgłoszenia
    request_type = Column(String(30), nullable=False, default='update_data')

    # Kto zgłasza
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)

    # Firma (jeśli istnieje)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='SET NULL'))

    # NIP wprowadzony przez użytkownika
    nip = Column(String(10), nullable=False)

    # Dane pobrane z KRS/CEIDG
    registry_source = Column(String(20))  # 'KRS', 'CEIDG'
    fetched_data = Column(JSONBType)

    # Status
    status = Column(String(20), nullable=False, default='pending')

    # Workflow
    created_at = Column(DateTime, nullable=False, default=datetime.now)
    reviewed_at = Column(DateTime)
    reviewed_by_id = Column(Integer, ForeignKey('users.id'))
    review_comment = Column(Text)

    # Notatka użytkownika
    user_note = Column(Text)

    # Które pola zostały zaktualizowane
    applied_fields = Column(JSONBType)

    # Relationships
    user = relationship('User', foreign_keys=[user_id], backref='company_data_requests')
    company = relationship('Company')
    reviewed_by = relationship('User', foreign_keys=[reviewed_by_id])

    # Constants
    REQUEST_TYPE_CHOICES = [
        ('update_data', 'Uzupełnienie danych'),
        ('claim_company', 'Przejęcie firmy'),
    ]

    STATUS_CHOICES = [
        ('pending', 'Oczekuje'),
        ('approved', 'Zatwierdzone'),
        ('rejected', 'Odrzucone'),
    ]

    @property
    def request_type_label(self):
        return dict(self.REQUEST_TYPE_CHOICES).get(self.request_type, self.request_type)

    @property
    def status_label(self):
        return dict(self.STATUS_CHOICES).get(self.status, self.status)

    def __repr__(self):
        return f"<CompanyDataRequest {self.id} [{self.status}] NIP:{self.nip}>"


# ============================================================
# MEMBER BENEFITS
# ============================================================

class Benefit(Base):
    """
    Korzyści członkowskie - oferty afiliacyjne dla członków Izby.
    Zniżki na licencje, subskrypcje, narzędzia SaaS itp.
    """
    __tablename__ = 'benefits'

    id = Column(Integer, primary_key=True)

    # Podstawowe info
    name = Column(String(100), nullable=False)
    slug = Column(String(100), unique=True, nullable=False)
    short_description = Column(String(200))  # Na kartę
    description = Column(Text)  # Pełny opis
    category = Column(String(50))  # np. 'productivity', 'ai', 'marketing'

    # Ceny i oferta
    regular_price = Column(String(50))  # np. "$10/mies"
    member_price = Column(String(50))  # np. "$8/mies" lub "10% zniżki"
    discount_description = Column(String(100))  # np. "10% zniżki dla członków"

    # Linki
    affiliate_url = Column(String(500))  # Link afiliacyjny
    product_url = Column(String(500))  # Link do strony produktu
    logo_url = Column(String(500))  # Logo produktu

    # Kod promocyjny (opcjonalny)
    promo_code = Column(String(50))
    promo_code_instructions = Column(Text)  # Jak użyć kodu

    # QR Code (generowany przez Dub)
    qr_code_url = Column(String(500))  # URL do obrazka QR

    # Prowizja (dla admina)
    commission_rate = Column(String(50))  # np. "25%"
    commission_duration = Column(String(50))  # np. "12 miesięcy"
    partner_platform = Column(String(100))  # np. "Dub Partners"
    partner_since = Column(Date)

    # Status
    is_featured = Column(Boolean, default=False)
    is_active = Column(Boolean, default=True)
    display_order = Column(Integer, default=0)

    # Statystyki
    click_count = Column(Integer, default=0)

    # Timestamps
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Constants
    CATEGORY_CHOICES = [
        ('productivity', 'Produktywność'),
        ('ai', 'Sztuczna Inteligencja'),
        ('marketing', 'Marketing'),
        ('finance', 'Finanse'),
        ('communication', 'Komunikacja'),
        ('design', 'Design'),
        ('development', 'Development'),
        ('other', 'Inne'),
    ]

    @property
    def category_label(self):
        return dict(self.CATEGORY_CHOICES).get(self.category, self.category)

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


class BenefitClick(Base):
    """
    Śledzenie kliknięć w linki afiliacyjne.
    """
    __tablename__ = 'benefit_clicks'

    id = Column(Integer, primary_key=True)
    benefit_id = Column(Integer, ForeignKey('benefits.id', ondelete='CASCADE'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))

    clicked_at = Column(DateTime, default=datetime.now)
    ip_address = Column(String(45))  # IPv6 support
    user_agent = Column(String(500))

    # Relationships
    benefit = relationship('Benefit', backref='clicks')
    user = relationship('User')

    def __repr__(self):
        return f"<BenefitClick {self.id} benefit={self.benefit_id}>"


# ============================================================
# AUDIT AI ACTIONS & CACHE
# ============================================================

class AuditAction(Base):
    """
    AI-generated action items from audit analysis.
    Tracks suggestions, their generated content, and implementation status.
    """
    __tablename__ = 'audit_actions'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    audit_type = Column(String(20), nullable=False)       # 'seo', 'gbp', 'social'
    action_type = Column(String(50), nullable=False)       # 'generate_schema_org', etc.
    title = Column(String(255), nullable=False)
    description = Column(Text)
    priority = Column(String(20), default='medium')        # 'critical', 'high', 'medium', 'low'
    impact_score = Column(Integer)                         # 1-10
    effort_score = Column(Integer)                         # 1-10
    ai_content = Column(Text)                              # Generated content
    ai_model = Column(String(50))
    status = Column(String(20), default='suggested')       # 'suggested', 'approved', 'implemented', 'dismissed'
    platform = Column(String(30))                          # 'google', 'facebook', etc.
    created_by = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.now)
    implemented_at = Column(DateTime)

    # Relationships
    company = relationship('Company', backref='audit_actions')
    creator = relationship('User', foreign_keys=[created_by])

    PRIORITY_ORDER = {'critical': 0, 'high': 1, 'medium': 2, 'low': 3}

    @property
    def priority_rank(self):
        return self.PRIORITY_ORDER.get(self.priority, 2)

    def __repr__(self):
        return f"<AuditAction {self.id} {self.audit_type}/{self.action_type} [{self.status}]>"


class AuditAICache(Base):
    """
    Cache for AI-generated audit analyses.
    Avoids regenerating analysis when audit data hasn't changed.
    """
    __tablename__ = 'audit_ai_cache'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    audit_type = Column(String(20), nullable=False)
    analysis_summary = Column(Text)
    actions_json = Column(JSONB)
    audit_data_hash = Column(String(64))
    generated_at = Column(DateTime, default=datetime.now)
    expires_at = Column(DateTime)
    previous_summary = Column(Text)
    previous_actions_json = Column(JSONB)
    previous_generated_at = Column(DateTime)

    # Relationships
    company = relationship('Company', backref='audit_ai_caches')

    __table_args__ = (
        UniqueConstraint('company_id', 'audit_type', name='uq_audit_ai_cache_company_type'),
    )

    def __repr__(self):
        return f"<AuditAICache {self.id} company={self.company_id} type={self.audit_type}>"


class SocialConnection(Base):
    """
    OAuth connections for social media publishing (Phase 2-3).
    Stores access/refresh tokens for GBP, Facebook, Instagram, LinkedIn APIs.
    """
    __tablename__ = 'social_connections'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='CASCADE'), nullable=False)
    platform = Column(String(30), nullable=False)
    access_token = Column(Text)
    refresh_token = Column(Text)
    token_expires_at = Column(DateTime)
    scope = Column(Text)
    external_account_id = Column(String(255))
    external_account_name = Column(String(255))
    connected_by = Column(Integer, ForeignKey('users.id'))
    connected_at = Column(DateTime, default=datetime.now)
    is_active = Column(Boolean, default=True)

    # Relationships
    company = relationship('Company', backref='social_connections')
    connector = relationship('User', foreign_keys=[connected_by])

    __table_args__ = (
        UniqueConstraint('company_id', 'platform', name='uq_social_connection_company_platform'),
    )

    def __repr__(self):
        return f"<SocialConnection {self.id} company={self.company_id} platform={self.platform}>"


class OAuthToken(Base):
    """OAuth tokens for external API integrations (Google, Meta)."""
    __tablename__ = 'oauth_tokens'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False)
    company = relationship('Company', backref='oauth_tokens')
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    user = relationship('User', backref='oauth_tokens')
    provider = Column(String(50), nullable=False)  # google, meta
    service = Column(String(50), nullable=False)   # gbp, search_console, facebook, instagram
    access_token = Column(Text, nullable=False)
    refresh_token = Column(Text)
    token_type = Column(String(50), default='Bearer')
    expires_at = Column(DateTime)
    scopes = Column(Text)
    account_id = Column(String(255))
    account_name = Column(String(255))
    metadata_json = Column('metadata', PG_JSONB)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    __table_args__ = (
        UniqueConstraint('company_id', 'provider', 'service', name='uq_oauth_company_provider_service'),
    )

    @property
    def is_expired(self):
        if not self.expires_at:
            return False
        return datetime.now() > self.expires_at

    def __repr__(self):
        return f'<OAuthToken {self.provider}/{self.service} for company_id={self.company_id}>'


# ============================================================
# SOCIAL MEDIA PUBLISHER
# ============================================================

class SocialPost(Base):
    """Social media posts for NORDA chamber page publishing."""
    __tablename__ = 'social_media_posts'

    id = Column(Integer, primary_key=True)

    # Typ i platforma
    post_type = Column(String(50), nullable=False)  # member_spotlight, regional_news, event_invitation, event_recap, chamber_news
    platform = Column(String(20), nullable=False, default='facebook')

    # Treść
    content = Column(Text, nullable=False)
    hashtags = Column(Text)
    image_path = Column(String(500))

    # Kontekst (opcjonalny)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=True)
    event_id = Column(Integer, ForeignKey('norda_events.id'), nullable=True)

    # Firma publikująca (której stroną FB publikujemy)
    publishing_company_id = Column(Integer, ForeignKey('companies.id'), nullable=True)

    # Workflow status
    status = Column(String(20), nullable=False, default='draft')

    # Scheduling
    scheduled_at = Column(DateTime, nullable=True)
    published_at = Column(DateTime, nullable=True)

    # Publish mode
    is_live = Column(Boolean, default=False)  # True = public, False = debug/draft on FB

    # Facebook response
    meta_post_id = Column(String(100))
    meta_response = Column(JSONBType)

    # Engagement (cache z FB API)
    engagement_likes = Column(Integer, default=0)
    engagement_comments = Column(Integer, default=0)
    engagement_shares = Column(Integer, default=0)
    engagement_reach = Column(Integer)
    engagement_updated_at = Column(DateTime)

    # AI metadata
    ai_model = Column(String(100))
    ai_prompt_template = Column(String(100))

    # Audyt
    created_by = Column(Integer, ForeignKey('users.id'), nullable=False)
    approved_by = Column(Integer, ForeignKey('users.id'))
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    # Relationships
    company = relationship('Company', foreign_keys=[company_id])
    publishing_company = relationship('Company', foreign_keys=[publishing_company_id])
    event = relationship('NordaEvent', foreign_keys=[event_id])
    creator = relationship('User', foreign_keys=[created_by])
    approver = relationship('User', foreign_keys=[approved_by])

    def __repr__(self):
        return f'<SocialPost {self.id} type={self.post_type} status={self.status}>'


class SocialMediaConfig(Base):
    """Configuration for social media platform connections per company."""
    __tablename__ = 'social_media_config'

    id = Column(Integer, primary_key=True)
    platform = Column(String(50), nullable=False)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=True)
    page_id = Column(String(100))
    page_name = Column(String(255))
    access_token = Column(Text)
    token_expires_at = Column(DateTime)
    is_active = Column(Boolean, default=True)
    debug_mode = Column(Boolean, default=True)
    config_data = Column(JSONBType)
    cached_posts = Column(JSONBType)
    posts_cached_at = Column(DateTime)
    updated_by = Column(Integer, ForeignKey('users.id'))
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    company = relationship('Company', foreign_keys=[company_id])
    updater = relationship('User', foreign_keys=[updated_by])

    __table_args__ = (
        UniqueConstraint('platform', 'company_id', name='uq_social_config_platform_company'),
    )

    def __repr__(self):
        return f'<SocialMediaConfig {self.platform} company_id={self.company_id} page={self.page_name}>'


class WebsiteDiscoveryCandidate(Base):
    """Website candidates found via Brave Search for companies missing website field."""
    __tablename__ = 'website_discovery_candidates'

    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable=False)
    discovered_at = Column(DateTime, default=datetime.now)
    search_query = Column(Text)
    candidate_url = Column(String(500), nullable=False)
    candidate_domain = Column(String(255))
    brave_title = Column(Text)
    brave_description = Column(Text)
    extracted_nips = Column(PG_ARRAY(Text))
    extracted_regons = Column(PG_ARRAY(Text))
    extracted_krs = Column(PG_ARRAY(Text))
    extracted_phones = Column(PG_ARRAY(Text))
    extracted_emails = Column(PG_ARRAY(Text))
    page_text_snippet = Column(Text)
    match_nip = Column(Boolean, default=False)
    match_regon = Column(Boolean, default=False)
    match_krs = Column(Boolean, default=False)
    match_phone = Column(Boolean, default=False)
    match_email = Column(Boolean, default=False)
    match_city = Column(Boolean, default=False)
    match_owner = Column(Boolean, default=False)
    confidence = Column(String(10), default='low')
    match_score = Column(Integer, default=0)
    status = Column(String(20), default='pending')
    reviewed_at = Column(DateTime)
    error_message = Column(Text)

    company = relationship('Company', foreign_keys=[company_id])

    __table_args__ = (
        UniqueConstraint('company_id', 'candidate_url', name='uq_wdc_company_url'),
    )

    def __repr__(self):
        return f'<WebsiteDiscoveryCandidate {self.id} company={self.company_id} confidence={self.confidence}>'


class PortalSEOAudit(Base):
    """SEO audit snapshots for nordabiznes.pl portal itself.
    Uses the same SEOAuditor as company audits for consistency."""
    __tablename__ = 'portal_seo_audits'

    id = Column(Integer, primary_key=True)
    audited_at = Column(DateTime, default=datetime.now, nullable=False, index=True)
    url = Column(String(500), nullable=False, default='https://nordabiznes.pl')

    # PageSpeed scores (0-100)
    pagespeed_performance = Column(Integer)
    pagespeed_seo = Column(Integer)
    pagespeed_accessibility = Column(Integer)
    pagespeed_best_practices = Column(Integer)

    # Core Web Vitals
    lcp_ms = Column(Numeric(10, 2))
    fcp_ms = Column(Numeric(10, 2))
    cls = Column(Numeric(6, 4))
    tbt_ms = Column(Numeric(10, 2))
    speed_index_ms = Column(Numeric(10, 2))

    # On-page SEO checks
    has_meta_title = Column(Boolean)
    has_meta_description = Column(Boolean)
    has_canonical = Column(Boolean)
    has_robots_txt = Column(Boolean)
    has_sitemap = Column(Boolean)
    has_structured_data = Column(Boolean)
    has_og_tags = Column(Boolean)
    has_ssl = Column(Boolean)
    is_mobile_friendly = Column(Boolean)

    # Security headers
    has_hsts = Column(Boolean)
    has_csp = Column(Boolean)
    has_x_frame = Column(Boolean)
    has_x_content_type = Column(Boolean)

    # Content metrics
    page_size_bytes = Column(Integer)
    image_count = Column(Integer)
    images_without_alt = Column(Integer)

    # Full audit results from SEOAuditor
    full_results = Column(JSONB)

    notes = Column(Text)
    created_by = Column(String(100))

    def __repr__(self):
        return f'<PortalSEOAudit {self.id} {self.audited_at} perf={self.pagespeed_performance}>'


# ============================================================
# UPTIME MONITORING
# ============================================================

class UptimeMonitor(Base):
    """Konfiguracja monitorów UptimeRobot"""
    __tablename__ = 'uptime_monitors'

    id = Column(Integer, primary_key=True)
    uptimerobot_id = Column(Integer, unique=True, nullable=False)
    name = Column(String(200), nullable=False)
    url = Column(String(500), nullable=False)
    check_interval_sec = Column(Integer, default=300)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.now)

    checks = relationship('UptimeCheck', backref='monitor', lazy='dynamic')
    incidents = relationship('UptimeIncident', backref='monitor', lazy='dynamic')

    def __repr__(self):
        return f'<UptimeMonitor {self.name} ({self.url})>'


class UptimeCheck(Base):
    """Wyniki sprawdzeń z UptimeRobot (synchronizowane co godzinę)"""
    __tablename__ = 'uptime_checks'

    id = Column(Integer, primary_key=True)
    monitor_id = Column(Integer, ForeignKey('uptime_monitors.id'), nullable=False, index=True)
    checked_at = Column(DateTime, nullable=False, index=True)
    status = Column(String(20), nullable=False)  # 'up', 'down', 'paused'
    response_time_ms = Column(Integer)
    status_code = Column(Integer)

    def __repr__(self):
        return f'<UptimeCheck {self.checked_at} {self.status}>'


class UptimeIncident(Base):
    """Okresy niedostępności z automatyczną diagnozą przyczyny"""
    __tablename__ = 'uptime_incidents'

    id = Column(Integer, primary_key=True)
    monitor_id = Column(Integer, ForeignKey('uptime_monitors.id'), nullable=False, index=True)
    started_at = Column(DateTime, nullable=False, index=True)
    ended_at = Column(DateTime)
    duration_seconds = Column(Integer)
    cause = Column(String(20), default='unknown')  # 'isp', 'server', 'infra', 'unknown'
    notes = Column(Text)
    auto_resolved = Column(Boolean, default=False)

    def __repr__(self):
        return f'<UptimeIncident {self.started_at} cause={self.cause} duration={self.duration_seconds}s>'


class InternalHealthLog(Base):
    """Wewnętrzny stan serwera (cron co 5 min)"""
    __tablename__ = 'internal_health_logs'

    id = Column(Integer, primary_key=True)
    checked_at = Column(DateTime, nullable=False, default=datetime.now, index=True)
    app_ok = Column(Boolean, nullable=False)
    db_ok = Column(Boolean, nullable=False)
    cpu_percent = Column(Numeric(5, 2))
    ram_percent = Column(Numeric(5, 2))
    disk_percent = Column(Numeric(5, 2))
    gunicorn_workers = Column(Integer)

    def __repr__(self):
        return f'<InternalHealthLog {self.checked_at} app={self.app_ok} db={self.db_ok}>'


# ============================================================
# UNIFIED CONVERSATIONS (messaging redesign)
# ============================================================

class Conversation(Base):
    """Zunifikowana konwersacja — 1:1 lub grupowa"""
    __tablename__ = 'conversations'

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=True)
    is_group = Column(Boolean, nullable=False, default=False)
    owner_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    last_message_id = Column(Integer, ForeignKey('conv_messages.id', name='fk_conversations_last_message', use_alter=True, ondelete='SET NULL'), nullable=True)

    owner = relationship('User', foreign_keys=[owner_id])
    members = relationship('ConversationMember', backref='conversation', cascade='all, delete-orphan')
    messages = relationship('ConvMessage', backref='conversation',
                            foreign_keys='ConvMessage.conversation_id',
                            cascade='all, delete-orphan',
                            order_by='ConvMessage.created_at')
    last_message = relationship('ConvMessage', foreign_keys=[last_message_id],
                                post_update=True)
    pins = relationship('MessagePin', backref='conversation', cascade='all, delete-orphan')

    @property
    def display_name(self):
        """Nazwa wyświetlana — nazwa grupy lub lista imion uczestników"""
        if self.name:
            return self.name
        names = [m.user.name or m.user.email.split('@')[0] for m in self.members if m.user]
        return ', '.join(sorted(names)[:4]) + (f' +{len(names)-4}' if len(names) > 4 else '')

    @property
    def member_count(self):
        return len(self.members)

    def __repr__(self):
        return f'<Conversation {self.id} group={self.is_group} members={self.member_count}>'


class ConversationMember(Base):
    """Członkostwo w konwersacji"""
    __tablename__ = 'conversation_members'

    conversation_id = Column(Integer, ForeignKey('conversations.id', ondelete='CASCADE'), primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), primary_key=True)
    role = Column(String(20), nullable=False, default='member')
    last_read_at = Column(DateTime, nullable=True)
    is_muted = Column(Boolean, nullable=False, default=False)
    is_archived = Column(Boolean, nullable=False, default=False)
    joined_at = Column(DateTime, default=datetime.now)
    added_by_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

    user = relationship('User', foreign_keys=[user_id])
    added_by = relationship('User', foreign_keys=[added_by_id])

    @property
    def is_owner(self):
        return self.role == 'owner'

    def __repr__(self):
        return f'<ConversationMember conv={self.conversation_id} user={self.user_id} role={self.role}>'


class ConvMessage(Base):
    """Wiadomość w konwersacji"""
    __tablename__ = 'conv_messages'

    id = Column(Integer, primary_key=True)
    conversation_id = Column(Integer, ForeignKey('conversations.id', ondelete='CASCADE'), nullable=False, index=True)
    sender_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    content = Column(Text, nullable=False)
    reply_to_id = Column(Integer, ForeignKey('conv_messages.id', ondelete='SET NULL'), nullable=True)
    edited_at = Column(DateTime, nullable=True)
    is_deleted = Column(Boolean, nullable=False, default=False)
    link_preview = Column(PG_JSONB, nullable=True)
    created_at = Column(DateTime, default=datetime.now, index=True)

    sender = relationship('User', foreign_keys=[sender_id])
    reply_to = relationship('ConvMessage', remote_side=[id], foreign_keys=[reply_to_id])
    reactions = relationship('MessageReaction', backref='message', cascade='all, delete-orphan')
    attachments = relationship('MessageAttachment',
                               foreign_keys='MessageAttachment.conv_message_id',
                               back_populates='conv_message',
                               cascade='all, delete-orphan')

    def __repr__(self):
        return f'<ConvMessage {self.id} conv={self.conversation_id} sender={self.sender_id}>'


class MessageReaction(Base):
    """Reakcja emoji na wiadomość"""
    __tablename__ = 'message_reactions'
    __table_args__ = (
        UniqueConstraint('message_id', 'user_id', 'emoji', name='uq_message_reaction'),
    )

    id = Column(Integer, primary_key=True)
    message_id = Column(Integer, ForeignKey('conv_messages.id', ondelete='CASCADE'), nullable=False, index=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    emoji = Column(String(10), nullable=False)
    created_at = Column(DateTime, default=datetime.now)

    user = relationship('User', foreign_keys=[user_id])

    def __repr__(self):
        return f'<MessageReaction {self.emoji} msg={self.message_id} user={self.user_id}>'


class MessagePin(Base):
    """Przypięta wiadomość w konwersacji"""
    __tablename__ = 'message_pins'

    id = Column(Integer, primary_key=True)
    conversation_id = Column(Integer, ForeignKey('conversations.id', ondelete='CASCADE'), nullable=False, index=True)
    message_id = Column(Integer, ForeignKey('conv_messages.id', ondelete='CASCADE'), nullable=False)
    pinned_by_id = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    created_at = Column(DateTime, default=datetime.now)

    message = relationship('ConvMessage', foreign_keys=[message_id])
    pinned_by = relationship('User', foreign_keys=[pinned_by_id])

    def __repr__(self):
        return f'<MessagePin msg={self.message_id} conv={self.conversation_id}>'


class AIUserMemory(Base):
    __tablename__ = 'ai_user_memory'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    fact = Column(Text, nullable=False)
    category = Column(String(50), default='general')
    source_conversation_id = Column(Integer, ForeignKey('ai_chat_conversations.id', ondelete='SET NULL'), nullable=True)
    confidence = Column(Float, default=1.0)
    created_at = Column(DateTime, default=datetime.utcnow)
    expires_at = Column(DateTime)
    is_active = Column(Boolean, default=True)

    user = relationship('User')
    source_conversation = relationship('AIChatConversation')

    def __repr__(self):
        return f'<AIUserMemory id={self.id} user={self.user_id} fact="{self.fact[:30]}">'


class AIConversationSummary(Base):
    __tablename__ = 'ai_conversation_summary'

    id = Column(Integer, primary_key=True)
    conversation_id = Column(Integer, ForeignKey('ai_chat_conversations.id', ondelete='CASCADE'), nullable=False, unique=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    summary = Column(Text, nullable=False)
    key_topics = Column(JSONBType, default=list)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow)

    user = relationship('User')
    conversation = relationship('AIChatConversation')

    def __repr__(self):
        return f'<AIConversationSummary conv={self.conversation_id}>'


# ============================================================
# DATABASE INITIALIZATION
# ============================================================

def init_db():
    """Initialize database - create all tables"""
    # Import all models to ensure they're registered
    # (already done at module level)

    # Create tables (only creates if they don't exist)
    Base.metadata.create_all(bind=engine)

    print("Database tables created successfully!")


def drop_all_tables():
    """Drop all tables - USE WITH CAUTION!"""
    Base.metadata.drop_all(bind=engine)
    print("All tables dropped!")


if __name__ == '__main__':
    # Test database connection
    try:
        init_db()
        print("✅ Database initialized successfully")

        # Test query
        db = SessionLocal()
        try:
            count = db.query(Company).count()
            print(f"✅ Database connected. Found {count} companies.")
        finally:
            db.close()

    except Exception as e:
        print(f"❌ Database error: {e}")