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")
    competitor_intensity = relationship("TblCompetitorIntensity", back_populates="group")
    projected_market_share = relationship("TblProjectedMarketShare", back_populates="group")
    location_spillage_factor = relationship("TblLocationSpillageFactor", back_populates="group")
    location_details = relationship("TblLocationDetails", back_populates="group")
    electrical_cabling = relationship("TblElectricalCabling", back_populates="group")
    category_pricing_strategis = relationship("TblCategoryPricingStrategis", back_populates="group")
    define_your_promotions = relationship("TblDefineYourPromotions", back_populates="group")
    promotion_competitor_analysis = relationship("TblPromotionCompetitorAnalysis", back_populates="group")
    recovery_of_promotional = relationship("TblRecoveryOfPromotional", back_populates="group")
    customer_engagement = relationship("TblCustomerEngagement", back_populates="group")
    competitor_loyalty = relationship("TblCompetitorLoyalty", back_populates="group")
    brand_attribute_assessment = relationship("TblBrandAttributeAssessment", back_populates="group")
    brand_attributes = relationship("TblBrandingAttributes", back_populates="group")
    integrated_marketing = relationship("TblIntegratedMarketing", back_populates="group")
    annexures = relationship("TblAnnexures", back_populates="group")
    optimising_assets = relationship("TblOptimisingAssets", back_populates="group")
    short_negotiation_final = relationship("TblShortNegotiationFinal", back_populates="group")
    margin_and_sales_contribution_analysis = relationship("TblMarginandSalesContributionAnalysis", back_populates="group")
    online_pre_selected_service = relationship("TblOnlinePreSelectService", back_populates="group")
    adapting_supply_chain = relationship("TblAdaptingSupplyChain", back_populates="group")
    critical_for_business = relationship("TblCriticalBusiness", back_populates="group")
    expanded_performance_metrics = relationship("TblExpandedPerformance", back_populates="group")
    categories_under_promotions = relationship("TblCategoriesUnderPromotions", back_populates="group")
    comparative_analysis = relationship("TblComparativeAnalysis", back_populates="group")
    measuring_sales_profitability = relationship("TblMeasuringSalesProfitability", back_populates="group")
    concept_office = relationship("TblConceptOffice", back_populates="group")
    summary = relationship("TblSummary", back_populates="group")
    cash_conversion = relationship("TblCashConversionCycle", back_populates="group")
    visual_merchandising_strategy = relationship("TblVisualMerchStrategy", back_populates="group")
    physical_store_formats = relationship("TblPhysicalStoreFormats", back_populates="group")
    cap_ex_refurbish = relationship("TblCapExRefurbish", back_populates="group")
    online_store_format = relationship("TblOnlineStoreFormat", back_populates="group")
    all_content = relationship("TblAllContent", 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()