from datetime import datetime
from app.extensions import db


# ═══════════════════════════════════════════════════════════════════════════════
#  Organization Model
# ═══════════════════════════════════════════════════════════════════════════════

class Organization(db.Model):
    """Organizations that users can belong to."""
    __tablename__ = "organizations"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False, index=True)
    name_ar = db.Column(db.String(255))
    slug = db.Column(db.String(255), unique=True, nullable=False, index=True)

    # Description
    description = db.Column(db.Text)
    description_ar = db.Column(db.Text)

    # Logo
    logo_url = db.Column(db.String(500))

    # Organization Type
    org_type = db.Column(db.String(50), default="company")  # company, incubator, factory, government, other

    # Contact
    website_url = db.Column(db.String(500))
    email = db.Column(db.String(255))
    phone = db.Column(db.String(50))

    # Address
    address = db.Column(db.String(500))
    city = db.Column(db.String(100))
    country = db.Column(db.String(100))

    # Sponsorship Tier (for landing page display)
    sponsor_tier = db.Column(db.String(20))  # platinum, gold, silver, bronze, none

    # Status
    is_active = db.Column(db.Boolean, default=True, index=True)
    is_verified = db.Column(db.Boolean, default=False)

    # Soft delete
    deleted_at = db.Column(db.DateTime, nullable=True)

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

    # Relationships
    users = db.relationship("User", backref="organization")
    sponsor_profile = db.relationship("SponsorProfile", backref="organization", uselist=False)

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

    @property
    def display_name(self):
        """Get appropriate name based on language."""
        return self.name_ar or self.name

    @property
    def display_description(self):
        """Get appropriate description based on language."""
        return self.description_ar or self.description

    def generate_slug(self):
        """Generate URL-safe slug from name."""
        from unidecode import unidecode
        import re
        base = unidecode(self.name or "")
        base = re.sub(r'[^\w\s-]', '', base)
        base = re.sub(r'[-\s]+', '-', base)
        slug = base.strip().lower()

        # Ensure uniqueness (check ALL records including soft-deleted since DB enforces unique constraint globally)
        counter = 1
        original_slug = slug
        while Organization.query.filter(
            Organization.slug == slug,
            Organization.id != self.id
        ).first():
            slug = f"{original_slug}-{counter}"
            counter += 1
        return slug


# ═══════════════════════════════════════════════════════════════════════════════
#  Sponsor Profile Model
# ═══════════════════════════════════════════════════════════════════════════════

class SponsorProfile(db.Model):
    """Sponsor profiles for organizations displayed on landing page."""
    __tablename__ = "sponsor_profiles"

    id = db.Column(db.Integer, primary_key=True)
    organization_id = db.Column(db.Integer, db.ForeignKey("organizations.id"), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)  # Admin contact

    # Display Settings (can override organization defaults)
    logo_url = db.Column(db.String(500))
    website_url = db.Column(db.String(500))
    tagline = db.Column(db.String(255))
    tagline_ar = db.Column(db.String(255))

    # Sponsorship Info
    tier = db.Column(db.String(20), default="bronze")  # platinum, gold, silver, bronze
    monthly_contribution = db.Column(db.Numeric(10, 2))

    # Display Control
    status = db.Column(db.String(20), default="pending", index=True)  # pending, approved, rejected, inactive
    display_on_landing = db.Column(db.Boolean, default=False)
    display_order = db.Column(db.Integer, default=0)

    # Notes
    notes = db.Column(db.Text)

    # Timestamps
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    approved_at = db.Column(db.DateTime)
    rejected_at = db.Column(db.DateTime)

    # Relationships
    user = db.relationship("User", backref="sponsor_profiles")

    def __repr__(self):
        return f"<SponsorProfile {self.id}: Org {self.organization_id} - {self.tier}>"

    @property
    def effective_logo(self):
        """Get sponsor logo or fall back to organization logo."""
        return self.logo_url or (self.organization.logo_url if self.organization else None)

    @property
    def effective_website(self):
        """Get sponsor website or fall back to organization website."""
        return self.website_url or (self.organization.website_url if self.organization else None)

    @property
    def display_tagline(self):
        """Get appropriate tagline based on language."""
        return self.tagline_ar or self.tagline

    @property
    def tier_label_ar(self):
        """Get Arabic tier label."""
        labels = {
            "platinum": "بلاتيني",
            "gold": "ذهبي",
            "silver": "فضي",
            "bronze": "برونزي"
        }
        return labels.get(self.tier, self.tier)

    @property
    def status_label_ar(self):
        """Get Arabic status label."""
        labels = {
            "pending": "قيد المراجعة",
            "approved": "مُعتمد",
            "rejected": "مرفوض",
            "inactive": "غير نشط"
        }
        return labels.get(self.status, self.status)