from typing import Optional
from pydantic import BaseModel, EmailStr, Field
from sqlalchemy import INTEGER, VARCHAR, ForeignKey
from sqlalchemy.orm import Mapped, Session, mapped_column, relationship
from app.models.main import Base
from app.models.main.student import TblStudent

class GroupBase(BaseModel):
   
    group_id: Optional[int] = Field(default=None)
    simulation_code: str | None = Field(default=None)
    group_name: Optional[str] = Field(default=None)
    group_email: Optional[EmailStr] = Field(default=None)
    category_assigned: Optional[str] = Field(default=None)
    number_of_members: Optional[int] = Field(default=0)
    category: Optional[str] = Field(default=None)
    group_code: Optional[str] = Field(default=None)

class TblGroup(Base):
    __tablename__ = "tbl_group"

    group_id: Mapped[int] = mapped_column("group_id", INTEGER, primary_key=True, autoincrement=True)
    simulation_code: Mapped[str] = mapped_column(VARCHAR(100), ForeignKey("tbl_simulation.simulation_code"), nullable=False)
    group_name: Mapped[str] = mapped_column("group_name", VARCHAR(255), nullable=False)
    group_email: Mapped[str] = mapped_column("group_email", VARCHAR(255), nullable=False)
    category_assigned: Mapped[Optional[str]] = mapped_column("category_assigned", VARCHAR(255), nullable=True)
    number_of_members: Mapped[int] = mapped_column("number_of_members", INTEGER, nullable=False, default=0)
    category: Mapped[Optional[str]] = mapped_column("category", VARCHAR(255), nullable=True)
    group_code: Mapped[str] = mapped_column("group_code", VARCHAR(100), nullable=False, unique=True)
    sample_excel_one: Mapped[Optional[str]] = mapped_column("sample_excel_one", VARCHAR(255), nullable=True)
    excel_pre_select: Mapped[Optional[str]] = mapped_column("excel_pre_select", VARCHAR(255), nullable=True) 
    corporate_and_concept_level_organisational_chart : Mapped[str] = mapped_column("corporate_and_concept_level_organisational_chart", VARCHAR(255), nullable=True)
    store_level_organisational_chart : Mapped[str] = mapped_column("store_level_organisational_chart", VARCHAR(255), nullable=True)
    category_definition_excel : Mapped[str] = mapped_column("category_definition_excel", VARCHAR(255), nullable=True)
    inventory_managemannt_excel : Mapped[str] = mapped_column("inventory_managemannt_excel", VARCHAR(255), nullable=True)
    online_policies_excel : Mapped[str] = mapped_column("online_policies_excel", VARCHAR(255), nullable=True)

    simulation = relationship("TblSimulation", back_populates="groups")
    students = relationship("TblStudent", back_populates="group")
    brands = relationship("TblBrand", back_populates="group")
    segment = relationship("TblSegment", back_populates="group")
    storeformate =relationship("TblStoreFormat", back_populates="group")
    customerlocation = relationship("TblCustomerLocation", back_populates="group")
    catchmentPotential = relationship("TblCatchmentPotential", back_populates="group")
    categories = relationship("TblCategory", back_populates="group")
    network_planning = relationship("TblNetworkPlanning", back_populates="group")
    civils = relationship("TblCivil",back_populates="group")
    display_racking_units = relationship("TblDisplayRackingUnit",back_populates="group")
    commercial_equipments = relationship("TblCommercialEquipment", back_populates="group")
    plumbing = relationship("TblPlumbing", back_populates="group")
    trading_month = relationship("TblTradingMonth", back_populates="group")
    pre_operating_expense = relationship("TblPreOperatingExpense", back_populates="group")
    competitor_analysis = relationship("TblCompetitorAnalysis", back_populates="group")
    display_board = relationship("TblDisplayBoard", back_populates="group")
    competitor_bench_marking = relationship("TblCompetitorBenchmarking", back_populates="group")
    carpentry = relationship("TblCarpentry", back_populates="group")
    info_tech = relationship("TblInfoTech", back_populates="group")
    visual_merchandising_elements = relationship("TblVisualMerchElements", back_populates="group")
    depreciation = relationship("TblDepreciation", back_populates="group")
    competitor_category_analyses = relationship("TblCompetitorCategoryAnalyses", back_populates="group")
    # consumer_preferences_research = relationship("TblConsumerPreferencesResearch", back_populates="group")
    gross_margin_contribution = relationship("TblGrossMarginContribution", back_populates="group")
    vendor_metrics_priority_list = relationship("TblVendorMetricsPriorityList", back_populates="group")
    pre_selected_category = relationship("TblPreSelectedCategory", back_populates="group")
    vendor_scorecard = relationship("TblVendorScorecard", back_populates="group")
    private_lablling = relationship("TblPrivateLablling", back_populates="group")
    category_captinship_planning = relationship("TblCategoryCaptinshipPlanning", back_populates="group")
    additional_installation = relationship("TblAdditionalInstallation", back_populates="group")
    sales_estimate = relationship("TblSalesEstimate", back_populates="group")
    rent_expenses = relationship("TblRentExpenses", back_populates="group")
    competitor_pricing_analysis = relationship("TblCompetitorPricingAnalysis", back_populates="group")
    store_formate_pricing_strategies = relationship("TblStoreFormatepricingStrategies", back_populates="group")
    competitor_analysis_comparison = relationship("TblCompetitorAnalysisComparison", back_populates="group")
    corporate_concept_office = relationship("TblCorporateConceptOffice", back_populates="group")
    utility = relationship("TblUtility", back_populates="group")
    operating_expenses = relationship("TblOperatingExpenses", back_populates="group")
    store_level_staff_needs = relationship("TblStoreLevelStaffNeeds", back_populates="group")
    centralised_direct = relationship("TblCentralisedAndDirect", back_populates="group")
    category_wish_inventory = relationship("TblCategoryWishInventory", back_populates="group")
    shrinkage_areas_reasons = relationship("TblShrinkageAreasReasons", back_populates="group")
    sales = relationship("TblSales", back_populates="group")
    recurring_operating = relationship("TblRecurringOperating", back_populates="group")
    digital_asset = relationship("TblDigitalAsset", back_populates="group")
    marketing_competitor = relationship("TblMarketingCompetitor", back_populates="group")
    
    

    @classmethod
    def create(cls, data: GroupBase, db: Session) -> "TblGroup":
        data_dict = data.model_dump()
        new_data = cls(**data_dict)
        db.add(new_data)
        db.flush()
        return new_data

    @classmethod
    def get_group(cls, group_id: int, db: Session) -> "TblGroup | None":
        return db.query(cls).filter(cls.group_id == group_id).first()
    
    @classmethod
    def update(cls, group_id: int, data: GroupBase, db: Session) -> "TblGroup | None":
        get_data = db.query(cls).filter(cls.group_id == group_id).first()
        if not get_data:
            return None  
        data_dict = data.model_dump()
        for key, value in data_dict.items():
            if value is not None:
                setattr(get_data, key, value)
        db.commit()
        db.refresh(get_data)
        return get_data
    
    @staticmethod
    def get_group_category(group_id: int, db: Session):
        return db.query(
            TblGroup.category_assigned,
            TblGroup.category
        ).filter(TblGroup.group_id == group_id).first()

    
    @staticmethod
    def get_students_by_group_id(group_id: int, db: Session):
        """gdhsas"""
        return db.query(TblStudent).filter(TblStudent.group_code == group_id).all()