from datetime import datetime
from app.extensions import db


class AdminAdAccount(db.Model):
    """Admin's ad accounts on each platform (tokens stored centrally)."""
    __tablename__ = "admin_ad_accounts"

    id = db.Column(db.Integer, primary_key=True)
    platform = db.Column(db.String(50), nullable=False, unique=True)  # facebook/tiktok/youtube/snapchat/twitter
    account_id = db.Column(db.String(255), nullable=True)  # ad account ID on the platform
    access_token = db.Column(db.Text, nullable=True)
    refresh_token = db.Column(db.Text, nullable=True)
    token_expires_at = db.Column(db.DateTime, nullable=True)
    is_active = db.Column(db.Boolean, default=False)
    daily_spend_limit = db.Column(db.Float, default=0.0)
    total_deposited = db.Column(db.Float, default=0.0)
    notes = db.Column(db.Text, nullable=True)
    updated_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class AdBudgetWallet(db.Model):
    """Global ad budget wallet managed by admin."""
    __tablename__ = "ad_budget_wallet"

    id = db.Column(db.Integer, primary_key=True)
    total_budget = db.Column(db.Float, default=0.0)
    total_spent = db.Column(db.Float, default=0.0)
    currency = db.Column(db.String(10), default="USD")
    updated_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    @property
    def remaining(self):
        return max(0, self.total_budget - self.total_spent)


class UserAdBudget(db.Model):
    """Per-user ad budget allocation set by admin."""
    __tablename__ = "user_ad_budgets"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, unique=True)
    allocated_budget = db.Column(db.Float, default=0.0)
    total_spent = db.Column(db.Float, default=0.0)
    is_active = db.Column(db.Boolean, default=True)
    set_by_admin_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    user = db.relationship("User", foreign_keys=[user_id], backref=db.backref("ad_budget", uselist=False))

    @property
    def remaining(self):
        return max(0, self.allocated_budget - self.total_spent)


class AdCampaign(db.Model):
    """Paid ad campaign created by user, approved by admin."""
    __tablename__ = "ad_campaigns"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    campaign_id = db.Column(db.Integer, db.ForeignKey("campaigns.id"), nullable=True)
    name = db.Column(db.String(500), nullable=False)
    platforms_json = db.Column(db.JSON, nullable=True)  # ["facebook","tiktok"]
    budget_amount = db.Column(db.Float, default=0.0)
    daily_budget = db.Column(db.Float, default=0.0)
    duration_days = db.Column(db.Integer, default=7)
    start_date = db.Column(db.DateTime, nullable=True)
    end_date = db.Column(db.DateTime, nullable=True)
    targeting_json = db.Column(db.JSON, nullable=True)  # {age_min, age_max, locations, interests, gender}
    ai_suggestions_json = db.Column(db.JSON, nullable=True)
    ad_caption = db.Column(db.Text, nullable=True)
    ad_cta = db.Column(db.String(100), nullable=True)  # Call to Action
    status = db.Column(db.String(50), default="draft")
    # draft -> pending_approval -> approved -> active -> paused -> completed -> rejected
    admin_notes = db.Column(db.Text, nullable=True)
    rejection_reason = db.Column(db.Text, nullable=True)
    reviewed_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    reviewed_at = db.Column(db.DateTime, nullable=True)
    platform_ids_json = db.Column(db.JSON, nullable=True)  # {facebook: "camp_123", tiktok: "camp_456"}
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    user = db.relationship("User", foreign_keys=[user_id], backref=db.backref("ad_campaigns", lazy="dynamic"))
    campaign = db.relationship("Campaign", backref=db.backref("ad_campaigns", lazy="dynamic"))
    reviewer = db.relationship("User", foreign_keys=[reviewed_by])
    metrics = db.relationship("AdCampaignMetrics", backref="ad_campaign", lazy="dynamic", cascade="all, delete-orphan")
    audit_logs = db.relationship("AdAuditLog", backref="ad_campaign", lazy="dynamic", cascade="all, delete-orphan")

    @property
    def total_spend(self):
        return sum(m.spend for m in self.metrics if m.spend) or 0.0

    @property
    def total_views(self):
        return sum(m.views for m in self.metrics if m.views) or 0

    @property
    def total_clicks(self):
        return sum(m.clicks for m in self.metrics if m.clicks) or 0

    @property
    def total_impressions(self):
        return sum(m.impressions for m in self.metrics if m.impressions) or 0

    @property
    def avg_ctr(self):
        total_imp = self.total_impressions
        if total_imp > 0:
            return round((self.total_clicks / total_imp) * 100, 2)
        return 0.0

    @property
    def platforms_list(self):
        return self.platforms_json if isinstance(self.platforms_json, list) else []


class SponsorSocialAccount(db.Model):
    """Admin's social media accounts for free organic posting (sponsor/company accounts).
    Users can choose to publish videos on these accounts instead of their own."""
    __tablename__ = "sponsor_social_accounts"

    id = db.Column(db.Integer, primary_key=True)
    platform = db.Column(db.String(50), nullable=False)  # facebook/tiktok/youtube/instagram/twitter/snapchat
    account_name = db.Column(db.String(255), nullable=True)  # display name e.g. "حساب الشركة الرسمي"
    platform_username = db.Column(db.String(255), nullable=True)  # @username on the platform
    access_token = db.Column(db.Text, nullable=True)
    refresh_token = db.Column(db.Text, nullable=True)
    platform_user_id = db.Column(db.String(255), nullable=True)  # channel_id / page_id etc.
    token_expires_at = db.Column(db.DateTime, nullable=True)
    is_active = db.Column(db.Boolean, default=True)
    notes = db.Column(db.Text, nullable=True)
    updated_by = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    @staticmethod
    def get_active_platforms():
        """Return list of platform names that have active sponsor accounts."""
        accounts = SponsorSocialAccount.query.filter_by(is_active=True).all()
        return list(set(a.platform for a in accounts if a.access_token))

    @staticmethod
    def get_account_for_platform(platform_name):
        """Return the active sponsor account for a given platform."""
        return SponsorSocialAccount.query.filter_by(
            platform=platform_name, is_active=True
        ).first()


class AdCampaignMetrics(db.Model):
    """Daily metrics per platform for each ad campaign."""
    __tablename__ = "ad_campaign_metrics"

    id = db.Column(db.Integer, primary_key=True)
    ad_campaign_id = db.Column(db.Integer, db.ForeignKey("ad_campaigns.id"), nullable=False)
    platform = db.Column(db.String(50), nullable=False)
    date = db.Column(db.Date, nullable=False)
    impressions = db.Column(db.Integer, default=0)
    views = db.Column(db.Integer, default=0)
    clicks = db.Column(db.Integer, default=0)
    ctr = db.Column(db.Float, default=0.0)
    spend = db.Column(db.Float, default=0.0)
    cost_per_view = db.Column(db.Float, default=0.0)
    cost_per_click = db.Column(db.Float, default=0.0)
    likes = db.Column(db.Integer, default=0)
    comments = db.Column(db.Integer, default=0)
    shares = db.Column(db.Integer, default=0)
    conversions = db.Column(db.Integer, default=0)
    reach = db.Column(db.Integer, default=0)
    frequency = db.Column(db.Float, default=0.0)
    fetched_at = db.Column(db.DateTime, default=datetime.utcnow)

    __table_args__ = (
        db.UniqueConstraint('ad_campaign_id', 'platform', 'date', name='uq_ad_metrics_campaign_platform_date'),
    )


class AdAuditLog(db.Model):
    """Audit log for all ad campaign operations."""
    __tablename__ = "ad_audit_logs"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    admin_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    ad_campaign_id = db.Column(db.Integer, db.ForeignKey("ad_campaigns.id"), nullable=True)
    action = db.Column(db.String(50), nullable=False)
    # created/submitted/approved/rejected/paused/resumed/completed/budget_changed/modified
    details_json = db.Column(db.JSON, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    user = db.relationship("User", foreign_keys=[user_id])
    admin = db.relationship("User", foreign_keys=[admin_id])