# app/analytics/models.py
from __future__ import annotations
from datetime import datetime, date, time, timezone
from typing import Optional
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy import BigInteger, Integer, String, Text, Date, DateTime, JSON, ForeignKey, UniqueConstraint,Column, Float
from src.marketing.apps.Account.model import ConnectedAccount
from src.marketing.apps.post.model import CalendarPostType as PostType
from src.marketing.apps.Account.model import MasterAccount
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy import BigInteger, Float, String, DateTime, Boolean, Text, Integer
from src.utils.db import Base
# If you already have a global Base, import it instead.
# class Base(DeclarativeBase):
#     pass

# === Existing tables (light ORM stubs) =======================================



class PostMetricsDaily(Base):
    __tablename__ = "post_metrics_daily"
    id = Column(Integer, primary_key=True, index=True)
    post_type_id = Column(BigInteger, ForeignKey("calendar_post_types.id"), nullable=True)
    metric_date = Column(Date, nullable=True)

    # core metrics
    impressions = Column(Integer, nullable=True)
    reach = Column(Integer, nullable=True)
    likes = Column(Integer, nullable=True)
    comments = Column(Integer, nullable=True)
    shares = Column(Integer, nullable=True)
    saves = Column(Integer, nullable=True)
    link_clicks = Column(Integer, nullable=True)
    video_views = Column(Integer, nullable=True)
    video_completions = Column(Integer, nullable=True)
    unfollows = Column(Integer, nullable=True)
    hides = Column(Integer, nullable=True)
    reports = Column(Integer, nullable=True)

    external_post_id = Column(Text, nullable=True)
    permalink = Column(Text, nullable=True)

    post_type: Mapped[PostType] = relationship()

class AccountMetricsDaily(Base):
    __tablename__ = "account_metrics_daily"
    id = Column(Integer, primary_key=True, index=True)
    connected_account_id = Column(BigInteger, ForeignKey("connected_accounts.id"), nullable=True)
    metric_date = Column(Date, nullable=True)

    followers = Column(Integer, nullable=True)
    new_followers = Column(Integer, nullable=True)
    impressions = Column(Integer, nullable=True)
    profile_visits = Column(Integer, nullable=True)
    messages_received = Column(Integer, nullable=True)
    median_response_minutes = Column(Float, nullable=True)

    connected_account: Mapped[ConnectedAccount] = relationship()


class StorePerformanceScore(Base):
    """Model for storing monthly performance scores for stores."""
    __tablename__ = "store_performance_scores"
    
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    store_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
    branch_id: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True, index=True)
    
    # Score details
    overall_score: Mapped[float] = mapped_column(Float, nullable=False)
    engagement_rate: Mapped[float] = mapped_column(Float, nullable=False)
    reach_efficiency: Mapped[float] = mapped_column(Float, nullable=False)
    posting_consistency: Mapped[float] = mapped_column(Float, nullable=False)
    
    # Platform breakdown
    platform_scores: Mapped[Optional[str]] = mapped_column(Text, nullable=True)  # JSON string of platform-specific scores
    
    # Metrics used for calculation
    total_posts: Mapped[int] = mapped_column(Integer, default=0)
    total_engagement: Mapped[int] = mapped_column(BigInteger, default=0)
    total_impressions: Mapped[int] = mapped_column(BigInteger, default=0)
    total_followers: Mapped[int] = mapped_column(BigInteger, default=0)
    connected_accounts_count: Mapped[int] = mapped_column(Integer, default=0)
    
    # Time period
    score_month: Mapped[int] = mapped_column(Integer, nullable=False)  # 1-12
    score_year: Mapped[int] = mapped_column(Integer, nullable=False)   # e.g., 2024
    
    # Metadata
    created_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(timezone.utc))
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=lambda: datetime.now(timezone.utc))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, server_default='true')
    
    def __repr__(self):
        return f"<StorePerformanceScore(store_id={self.store_id}, score={self.overall_score}, month={self.score_month}/{self.score_year})>"